ALTER TABLE ADD COLUMN 需要很长时间

Question

我只是想将一个名为“location”的列添加到数据库中的表（main_table）中。我运行的命令是

ALTER TABLE main_table ADD COLUMN location varchar (256);

The main_table contains > 2,000,000 行。持续运行了2个多小时，仍未完成。

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

Answer 1

您的 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 SELECT *, NULL FROM main_table;
RENAME TABLE main_table TO main_table_old, main_table_new TO main_table;
DROP TABLE main_table_old;

通过这种方式，您可以在空表上添加列，并且基本上将数据写入该新表中，您确信没有其他人会在不锁定尽可能多的资源的情况下查看该数据。

Answer 2

我认为正确的答案是使用类似 pt-online-schema-change 或 gh-ost。

我们已经使用此方法完成了超过 40 亿行的迁移，尽管这可能需要长达 10 天的时间，并且停机时间不到一分钟。

Percona 的工作方式与上面非常相似

• 创建临时表
• 在第一个表上创建触发器（用于插入、更新、删除），以便将它们复制到临时表
• 小批量迁移数据
• 完成后，将表重命名为新表，然后删除另一个表

Answer 3

您可以通过暂时关闭唯一检查和外键检查来加快该过程。您还可以更改使用的算法。

如果您希望新列位于表格末尾，请使用 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;

否则，如果您需要将该列位于特定位置，请使用 algorithm=inplace code>：

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;

仅供参考，我的电脑使用就地算法更改具有 2000 万行的表大约需要 2 分钟。如果您使用的是 Workbench 等程序，那么您可能需要在开始操作之前增加设置中的默认超时期限。

如果您发现操作无限期地挂起，那么您可能需要查看进程列表并终止任何在表上拥有锁的进程。您可以使用以下命令来执行此操作：

SHOW FULL PROCESSLIST;
KILL PROCESS_NUMBER_GOES_HERE;

Answer 4

对于像您的情况这样的大数据，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;

Answer 5

尝试将表的引擎从 InnoDB 更改为 MyISAM，然后将其恢复回 InnoDB 并尝试。

ALTER TABLE `tablename` ENGINE = MyISAM ; 
ALTER TABLE `tablename` ENGINE = InnoDB;

然后运行这个

ALTER TABLE `tablename` ADD COLUMN `columnname` `datatype` null;

Answer 6

DB2 z/OS 立即虚拟添加列。并将该表置于 Advisory-Reorg 状态。重组之前运行的任何内容都会获取默认值，如果没有默认值，则为 null。更新完成后，它们会展开更新的行。插入已完成扩展。下一个重组将展开每个未展开的行，并将默认值分配给它展开的任何内容。

只有真正的数据库才能很好地处理这个问题。 DB2 z/OS。