ALTER TABLE ADD COLUMN需要很长时间 [英] ALTER TABLE ADD COLUMN takes a long time

问题描述

我只是试图在数据库中的表(main_table)中添加一个名为"location"的列.我运行的命令是

I was just trying to add a column called "location" to a table (main_table) in a database. The command I run was

ALTER TABLE main_table ADD COLUMN location varchar (256);

main_table包含> 2,000,000行.它持续运行超过2个小时，但仍未完成.

The main_table contains > 2,000,000 rows. It keeps running for more than 2 hours and still not completed.

我尝试使用mytop 监视此数据库的活动，以确保该查询未被其他查询过程锁定，但似乎没有被锁定.应该花那么长时间吗?实际上，我只是在运行此命令之前重新启动了计算机.现在此命令仍在运行.我不确定该怎么办.

I tried to use mytop to 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.

推荐答案

您的ALTER TABLE语句暗示mysql将不得不重写表的每一行，包括新列.由于您的行数超过200万，因此，我肯定会花费大量时间，在此期间您的服务器可能大部分都是IO绑定的.通常，您会发现执行以下操作更有效:

Your ALTER TABLE statement 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:

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.

这篇关于ALTER TABLE ADD COLUMN需要很长时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！