ALTER TABLE ADD COLUMN 需要很长的时间

我只是尝试将一个名为“ location”的列添加到数据库中的一个表(main _ table)中。我运行的命令是

ALTER TABLE main_table ADD COLUMN location varchar (256);

Main _ table 包含 > 2,000,000行，它运行了2个多小时仍然没有完成。

我试过用 mytop 监视此数据库的活动，以确保查询没有被其他查询进程锁定，但似乎没有。需要这么长时间吗？事实上，我只是在运行这个命令之前重启了机器。现在这个命令仍在运行。我不知道该怎么办。

85825 次浏览
小开

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 SELECT *, NULL 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.

小开

I think the appropriate answer for this is using a feature like pt-online-schema-change or 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.

Percona works in a very similar fashion as above

  • 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
小开

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:

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;
小开

DB2 z/OS does a virtual add of the column instantly. And puts the table into Advisory-Reorg status. Anything that runs before the reorg gets the default value or null if no default. When updates are done, they expand the rows updated. Inserts are done expanded. The next reorg expands every unexpanded row and assigns the default value to anything it expands.

Only a real database handles this well. DB2 z/OS.

小开

You can speed up the process by temporarily turning off unique checks and foreign key checks. You can also change the algorithm that gets used.

If you want the new column to be at the end of the table, use algorithm=instant:

SET unique_checks = 0;
SET foreign_key_checks = 0;
ALTER TABLE main_table ADD location varchar(256), algorithm=instant;
SET unique_checks = 1;
SET foreign_key_checks = 1;

Otherwise, if you need the column to be in a specific location, use algorithm=inplace:

SET unique_checks = 0;
SET foreign_key_checks = 0;
ALTER TABLE main_table ADD location varchar(256) AFTER othercolumn, algorithm=inplace;
SET unique_checks = 1;
SET foreign_key_checks = 1;

For reference, it took my PC about 2 minutes to alter a table with 20 million rows using the inplace algorithm. If you're using a program like Workbench, then you may want to increase the default timeout period in your settings before starting the operation.

If you find that the operation is hanging indefinitely, then you may need to look through the list of processes and kill whatever process has a lock on the table. You can do that using these commands:

SHOW FULL PROCESSLIST;
KILL PROCESS_NUMBER_GOES_HERE;

提交答案