从大表中删除列

Question

我有一张大桌子，上面有三栏：

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

这将持续3800万行。问题是我想删除'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中是不可能的？

是否有其他方法来删除这个列--也许创建一个只有这两列的新表，或者在检查点中删除？

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;

但是，通常情况下，在加载一组数据之前删除表上的所有索引，然后再重新创建索引会更快。

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

免责声明:我不知道外键指向旧表会发生什么。

Answer 3

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

1)将表Engine更改为MyISAM

2)任意更改(删除列、更改数据类型等)

3)将其更改为InnoDB

在这种情况下，DBMS不会在每次记录迭代中锁定/解锁。

但是，请注意，如果您想在表/数据库中更改一些内容，则此解决方案将是很好的，因为一旦您将其还原回InnoDB，这将花费同样的时间删除一列。因此，只有当数据库中有多个事情要更改时，才考虑这个解决方案。