从大表中删除列 [英] Drop Column from Large Table

问题描述

我有一张大表，上面有三列:

I have this largish table with three columns as such:

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

这继续进行了3,800万行.问题是我想删除'associd'列，但是运行ALTER TABLE table_name DROP COLUMN associd;只是花费太长时间.我想做类似的事情:ALTER TABLE table_name SET UNUSED associd;和ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;然后显然可以加快此过程，但是

This continues for 38m rows. The problem is I want to remove the 'associd' column but running ALTER TABLE table_name DROP COLUMN associd; simply takes too long. I wanted to do something like: ALTER TABLE table_name SET UNUSED associd; and ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250; then which apparently speeds up the process but it isn't possible in MySQL?

是否有删除此列的替代方法-可能仅用两列创建一个新表，或者使用检查点删除该表?

Is there an alternative to remove this column-- maybe creating a new table with only the two columns or getting a drop with checkpoints?

推荐答案

您要做的任何事情都需要读写38m行，因此没有什么事情会很快实现的.最快的方法可能是将数据放入新表中:

Anything that you do is going to require reading and writing 38m rows, so nothing is going to be real fast. Probably the fastest method is probably to put the data into a new table:

create table newTable as
    select id1, id2
    from oldTable;

或者，如果您想确保保留类型和索引:

Or, if you want to be sure that you preserve types and indexes:

create table newTable like oldTable;

alter table newTable drop column assocId;

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

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

However, it is usually faster to drop all index on a table before loading a bunch of data and then recreate the indexes afterwards.

这篇关于从大表中删除列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！