从大表中删除列

Question

我有一个比较大的表格，包含三列，如下所示：

+-----+-----+----------+
| id1 | id2 | associd  |
+-----+-----+----------+
|   1 |  38 | 73157604 |
|   1 | 112 | 73157605 |
|   1 | 113 | 73157606 |
|   1 | 198 | 31936810 |
|   1 | 391 | 73157607 |
+-----+-----+----------+

这个问题涉及38m行数据。问题在于我想要删除“associd”列，但运行ALTER TABLE table_name DROP COLUMN associd;太慢了。我想做的是：ALTER TABLE table_name SET UNUSED associd;和ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;，这样可以加速处理过程，但在MySQL中似乎不可能？。

是否有其他方法来删除此列 - 可能是创建仅包含两列的新表或使用具有检查点的删除？

- nico

---

我删除了sql-server标签，因为问题明确涉及MySQL。 - Gordon Linoff

Answer 1

任何你要做的事情都需要读写3800万行数据，所以速度不会很快。可能最快的方法是将数据放入一个新表中：

create table newTable as
    select id1, id2
    from oldTable;

或者，如果你想确保保留类型和索引：

create table newTable like oldTable;

alter table newTable drop column assocId;

insert into newTable(id1, id2)
    select id1, id2
    from oldTable;

然而，在加载大量数据之前，通常更快的方法是先删除表上的所有索引，然后在加载完数据后重新创建索引。

- Gordon Linoff

---

这只用了17分09秒，比删除列（我在三个小时后停止）要快得多，我很惊讶但也很感激你的帮助！ - nico

Gordon，我想知道为什么复制两列比删除一行更快。你能告诉我原因吗？ - Erran Morad

对一个已存在的表进行修改需要大量的开销，需要锁定表的部分内容并记录所有的更改。创建一个新表是更为优化的选择。 - Gordon Linoff

@GordonLinoff - 谢谢。我在哪里可以获取更多关于这个的信息？这是所有关系型数据库管理系统的挑战还是只有MySQL？ - Erran Morad

@BoratSagdiyev . . . 这是所有数据库中的一个问题，而且文档记录得相当不好。基本想法是引擎可以识别“批量插入”类型的操作，但更新实际上是逐行进行的（在某种意义上，尽管更新可以并行运行）。快速谷歌一下，在 SQL Server 上找到了这个例子：http://blogs.msdn.com/b/sqlperf/archive/2007/02/16/high-volume-update-performance.aspx。 - Gordon Linoff

你必须记住外键的可能后果。它们不会在create table like语句中创建，如果有任何外部外键指向旧表，我不确定它们是否会重新连接到新表...此外，作为最后一步，您需要将新表重命名为旧表的名称...同时删除旧表。 - fguillen

Answer 2

声明：本答案基于MySQL，可能不适用于其他数据库。

我认为接受的答案中缺少一些东西，我试图在这里介绍一种我在生产环境中使用的通用序列，不仅用于添加/删除列，还可用于添加索引等操作。

我们称之为印第安纳琼斯式移动。

创建一个新表格

使用旧表格作为模板创建一个新表格：

create table my_table_new like my_table;

移除新表中的列

在新表中：

alter table my_table_new drop column column_to_delete;

向新表中添加外键

这些不会在create table like命令中自动生成。

您可以检查实际的外键：

mysql> show create table my_table;

然后将它们应用到新表中：

alter table my_table_new
  add constraint my_table_fk_1 foreign key (field_1) references other_table_1 (id),
  add constraint my_table_fk_2 foreign key (field_2) references other_table_2 (id)

克隆表格

复制除需要删除的字段之外的所有字段。

我使用 where 语句，以便如果需要，可以多次运行此命令。

由于我假设这是一个生产环境，因此 my_table 将不断有新记录，所以我们必须保持同步，直到我们能够更改名称。

此外，我添加了一个 limit，因为如果表太大且索引太重，则一次性克隆可能会关闭数据库的性能。而且，如果在过程中想要取消操作，则必须回滚所有已完成的插入，这意味着你的数据库无法立即恢复 (https://dba.stackexchange.com/questions/5654/internal-reason-for-killing-process-taking-up-long-time-in-mysql)

insert my_table_new select field_1, field_2, field_3 from my_table 
where id > ifnull((select max(id) from my_table_new), 0)
limit 100000; 

我多次执行此操作后创建了一个过程：https://gist.github.com/fguillen/5abe87f922912709cd8b8a8a44553fe7

更改名称

请确保在复制表中的最后记录后立即运行此命令。理想情况下，一次运行所有命令。

rename table my_table to my_table_3;
rename table my_table_new to my_table;

删除旧表格

在进行此操作之前，请确保已备份 ;)

drop table my_table_3

免责声明：我不确定指向旧表的外键会发生什么。

- fguillen

---

谢谢！我在一个MySQL 5.5服务器上的几个非常大的表（5000万条记录）上进行在线DDL更改时遇到了问题，该服务器尚不支持ALGORITHM=INPLACE。在执行此操作之前应删除指向旧表的外键，并在重命名后重新创建它们。这也需要一些时间。RENAME TABLE是原子性的，因此这是真正的在线DDL更改。 - otherguy

2022年的一个小补充，MySQL 8（可能还包括一些旧版本）支持使用一个语句重命名多个表：RENAME TABLE tbl TO tbl_old, tbl_new TO tbl;。 - Sergey Kudriavtsev

很想知道如何处理仍然指向旧表的外键。我们应该重新创建所有的外键吗？如果表与其他表有很多关联，那可能比直接删除列本身需要更多时间。 - Aidas

Answer 3

您可以通过暂时关闭唯一性检查和外键检查来加快进程。您还可以更改使用的算法。

SET unique_checks=0;
SET foreign_key_checks=0;
ALTER TABLE table_name DROP COLUMN column_name, algorithm=inplace;
SET unique_checks=1;
SET foreign_key_checks=1;

使用上述代码，我的电脑花了大约2分钟的时间从一张2000万行的表中删除一列。

如果您正在使用像Workbench这样的程序，则在开始操作之前可能需要增加默认超时时间设置。

如果您发现操作无限期地挂起，则可能需要查看进程列表并杀死锁定表的任何进程。您可以使用以下命令执行此操作：

SHOW FULL PROCESSLIST;
KILL PROCESS_NUMBER_GOES_HERE;

- Pikamander2

---

这是使用InnoDB还是MyISAM？ - user1111929

@user1111929 - InnoDB - Pikamander2

Answer 4

在这种情况下，MySQL的最佳解决方案是：

1）将表格Engine更改为MyISAM 2）更改您想要执行的任何操作（删除列、更改数据类型等）

3）再将其更改回InnoDB 在这种情况下，DBMS不会在每个记录迭代时锁定/解锁。

但请注意，如果您在表/数据库中有多个要更改的内容，那么此解决方案将非常有效，因为一旦将其还原回InnoDB，删除一个列需要相同的时间。因此，只有在数据库中有多个要更改的内容时才考虑使用此解决方案。

- Alan Deep