如何在一个巨大的MYSQL数据库表中插入新列？

Question

我在MYSQL数据库中有一张表，大约有1000万条记录/行。我想在这个表中插入一个新的列。然而一个简单的添加列的查询似乎对我来说不起作用。

这是我尝试过的， ALTER TABLE contacts ADD processed INT(11);

我等了大约5个小时，但什么都没有发生。有没有什么办法可以在这样一个巨大的表中插入新的列？

希望我的问题清楚明了。任何帮助都将不胜感激。

- user1518659

---

这是生产环境吗？你使用的CPU是什么？服务器负载过高吗？ - Roman Newaza

@RomanNewaza 这不是在生产环境中，只是在本地。我有4GB的内存和i5处理器。我认为服务器没有超载。 - user1518659

这是Linux吗？你能发布uptime; free -m的结果吗？ - Roman Newaza

抱歉，我不熟悉Windows。 - Roman Newaza

Answer 1

如果是生产环境：

建议使用Percona Toolkit中的pt-online-schema-change。

pt-online-schema-change模拟MySQL内部修改表的方式，但它在要修改的表的副本上工作。这意味着原始表不会被锁定，并且客户端可以继续读取和更改其中的数据。

pt-online-schema-change通过创建要修改的表的空副本、按需修改该副本，然后将原始表的行复制到新表中来工作。当复制完成后，它会移动原始表并用新表替换它。默认情况下，它也会删除原始表。

或者使用openark kit中的oak-online-alter-table。

oak-online-alter-table允许进行非阻塞的ALTER TABLE操作，重建表以及创建表的ghost。

修改表的速度会变慢，但不会锁定表。

如果不是生产环境，可以尝试下面的方法，即使需要停机也无妨：

CREATE TABLE contacts_tmp LIKE contacts;
ALTER TABLE contacts_tmp ADD COLUMN ADD processed INT UNSIGNED NOT NULL;
INSERT INTO contacts_tmp (contact_table_fields) SELECT * FROM contacts;
RENAME TABLE contacts_tmp TO contacts, contacts TO contacts_old;
DROP TABLE contacts_old;

- Roman Newaza

---

谢谢，但你能确保这些工具中的任何一个都能在更短的时间内完成所需的过程吗？如果可以，大约需要多长时间？你会建议使用哪一个？ - user1518659

另外我需要的是尽可能快地插入列。不知道是否有什么方法？ - user1518659

它将需要更多的CPU时间 - Roman Newaza

我也尝试过这种方法。但是它花费了太多时间。这就是我发帖提问的原因。 - user1518659

将数据插入到contacts_tmp表中(contact_table_fields)并从contacts表中选择所有数据。这可能会在没有锁定的情况下丢失一些数据。没有更好的方法来解决大表添加新列的问题。 - babaoqi

MySQL的模式更改能力与Postgres和其他数据库相比严重受限。许多操作都需要锁定和重建。 - tadman