修改表添加列需要很长时间。

Question

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

ALTER TABLE main_table ADD COLUMN location varchar (256);

主表格包含超过2,000,000行数据。查询已经运行了超过2小时，但仍未完成。

我尝试使用 mytop 监控数据库的活动情况以确保查询没有被其他查询进程锁定，但似乎并不是这个原因导致的。这个查询应该需要那么长时间吗？实际上，在运行此命令之前我刚重启了机器。现在这个命令还在运行中。我不确定该怎么办。

- fanchyna

---

这会花费很长时间，因为索引和表中存在的行数。注意：Varchar(255)。 - Jauzsika

我认为你应该为它指定一个默认值。也许这就是为什么它需要时间的原因？ - Nilesh

在这种情况下，默认值为 NULL，绝对不是导致它花费很长时间的原因。 - Romain

MySQL 8.0.12有一个ALTER TABLE .. ADD COLUMN .. ALGORITHM=INSTANT。 - Rick James

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;

这种方式可以在空表上添加列，并在新表中编写数据，您可以确保没有其他人会在不锁定太多资源的情况下查看该表。

- Romain

---

即使是这样，最后一次有人使用非MySQL SQL RDBMS是什么时候？朋友们不要购买Oracle。 - Michael Lorton

动态地填充“主表中的字段”对于脚本使用来说是非常有用的。 - Marki

这种方法需要重写整个表格。我的理解是，这种方法的唯一优点是在添加新列时允许使用当前表格，对吗？ - Michael

我建议不要在有许多写操作的实际生产数据库中使用此解决方案。很明显，新表可能会缺少在交换过程中到达的数据，并且长时间锁定也可能发生。 - Raul R.

如果您有引用该表的外键，则此方法对您无效。 - Jonny

Answer 2

我认为，适当的解决方案是使用类似pt-online-schema-change或gh-ost的功能。

我们使用这种方法迁移了超过40亿行数据，虽然可能需要长达10天的时间，但只有不到一分钟的停机时间。

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

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

- Pratik Bothra

---

这是一种证明有时少得票或未被接受的解决方案才是最好或唯一正确的解决方案的问题。有多少人投票支持错误的另一个方案啊，天哪。 - Raul R.

@RaulR。这取决于使用情况。在我所遇到的使用情况中，另一种解决方案已经足够“正确”了，毕竟并不是每个人都需要24/7的生产环境。 - Austin Schmidt

请注意，pt-online-schema-change 可能会导致表阻塞。如果您的大表有指向它的外键，则可能会出现阻塞时间或丢失外键。您需要选择一个 alter-foreign-keys-method，rebuild_constraints 将需要重新构建一个外键，这将阻塞引用表。 - Gabriel Furstenheim

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：

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;

参考一下，使用原地算法，我的电脑花费了大约2分钟来修改包含2000万行的表格。如果您正在使用类似Workbench这样的程序，则可能需要在开始操作之前增加默认超时时间。

如果您发现操作无限期挂起，则可能需要查看进程列表并强制终止正在锁定表格的进程。您可以使用以下命令完成：

SHOW FULL PROCESSLIST;
KILL PROCESS_NUMBER_GOES_HERE;

- Pikamander2

---

即使在这些情况下，即使在空表上，它也花费了很长时间。我是在mysql-8.0的docker容器上运行它的。重启数据库docker容器有所帮助。 - MiroJanosik

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;

- ZORRO_BLANCO

Answer 5

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

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

然后运行这个

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

- Abid Ali

Answer 6

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

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

- Bill Hulsizer

---

你可能需要考虑，改用不同的数据库也会有其自身的困难，而且这个答案对于原问题的提问者来说几乎没有帮助，因为已经有近十年的时间过去了。 - BertD