MySQL ALTER TABLE ADD COLUMN 需要很长时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7599519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ALTER TABLE ADD COLUMN takes a long time
提问by fanchyna
I was just trying to add a column called "location" to a table (main_table) in a database. The command I run was
我只是想将一个名为“location”的列添加到数据库中的表 (main_table) 中。我运行的命令是
ALTER TABLE main_table ADD COLUMN location varchar (256);
The main_table contains > 2,000,000 rows. It keeps running for more than 2 hours and still not completed.
main_table 包含 > 2,000,000 行。它持续运行了2个多小时，仍然没有完成。
I tried to use mytopto monitor the activity of this database to make sure that the query is not locked by other querying process, but it seems not. Is it supposed to take that long time? Actually, I just rebooted the machine before running this command. Now this command is still running. I am not sure what to do.
我尝试使用mytop来监视此数据库的活动，以确保该查询未被其他查询进程锁定，但似乎没有。应该需要那么长时间吗？实际上，我只是在运行此命令之前重新启动了机器。现在这个命令仍在运行。我不知道该怎么做。
回答by Romain
Your ALTER TABLEstatement implies mysql will have to re-write every single row of the table including the new column. Since you have more than 2 million rows, I would definitely expect it takes a significant amount of time, during which your server will likely be mostly IO-bound. You'd usually find it's more performant to do the following:
您的ALTER TABLE语句意味着 mysql 将不得不重写表的每一行，包括新列。由于您有超过 200 万行，我肯定会期望它花费大量时间，在此期间您的服务器可能主要受 IO 限制。您通常会发现执行以下操作会更高效：
CREATE TABLE main_table_new LIKE main_table;
ALTER TABLE main_table_new ADD COLUMN location varchar(256);
INSERT INTO main_table_new (fields_in_main_table) SELECT * FROM main_table;
RENAME TABLE main_table TO main_table_old, main_table_new TO main_table;
DROP TABLE main_table_old;
This way you add the column on the empty table, and basically write the data in that new table that you are sure no-one else will be looking at without locking as much resources.
通过这种方式，您可以在空表上添加列，并基本上将数据写入该新表中，您确信没有其他人会在不锁定尽可能多的资源的情况下查看这些数据。
回答by Pratik Bothra
I think the appropriate answer for this is using a feature like pt-online-schema-changeor gh-ost.
我认为对此的适当答案是使用pt-online-schema-change或gh-ost 之类的功能。
We have done migration of over 4 billion rows with this, though it can take upto 10 days, with less than a minute of downtime.
我们已经用这个完成了超过 40 亿行的迁移，尽管它可能需要长达 10 天的时间，停机时间不到一分钟。
Percona works in a very similar fashion as above
Percona 的工作方式与上述非常相似
- Create a temp table
- Creates triggers on the first table (for inserts, updates, deletes) so that they are replicated to the temp table
- In small batches, migrate data
- When done, rename table to new table, and drop the other table
- 创建临时表
- 在第一个表上创建触发器（用于插入、更新、删除），以便将它们复制到临时表
- 小批量迁移数据
- 完成后，将表重命名为新表，并删除另一个表
回答by ZORRO_BLANCO
Alter table takes a long time with a big data like in your case, so avoid to use it in such situations, and use some code like this one:
像您这样的大数据，Alter table 需要很长时间，因此请避免在这种情况下使用它，并使用如下代码：
select main_table.*,
cast(null as varchar(256)) as null_location, -- any column you want accepts null
cast('' as varchar(256)) as not_null_location, --any column doesn't accept null
cast(0 as int) as not_null_int, -- int column doesn't accept null
into new_table
from main_table;
drop table main_table;
rename table new_table TO main_table;
