如何在MySQL中为大表添加列

Question

如何在MySQL中为大表添加列

mysqlinnodb

18

我是一名PHP开发者，所以别太严格。我有一个大表，约5.5GB的数据。我们的项目经理决定在表中添加一个新列来实现新功能。这个表是InnoDB引擎的，所以我尝试了以下方法：

在屏幕上使用表锁进行表的修改。花了大约30个小时，但没有任何结果。所以我停止了操作。第一次我犯了个错误，没有结束所有的事务，但第二次没有多重锁。状态显示为“复制到临时表”。
由于我还需要对这个表应用分区，我们决定先导出数据，然后重命名表并创建具有相同名称和新结构的新表。但是导出的数据是严格拷贝（至少我没有找到其他方法）。所以我使用sed命令向导出的数据中添加了一个新列，并进行了查询。但是出现了一些奇怪的错误。我相信这是由字符集引起的。表是utf-8编码的，而文件在使用sed命令后变成了us-ascii编码。所以在30%的数据上出现了错误（未知命令'\'）。所以这也不是一个好方法。

还有其他的选项可以完成这个任务并提高性能吗（我可以使用PHP脚本来完成，但会花费很长时间）？在这种情况下，使用INSERT SELECT语句的性能如何？感谢您的提前帮助。

- ineersa

6个回答

17

alter table add column, algorithm=inplace, lock=none会在不复制表格和不影响锁定的情况下修改MySQL 5.6表。

昨天刚测试过，将70K行数据批量插入到一个包含7个分区、共280K行的表中，每个分区插入10K行，之间有5秒的延迟以允许其他吞吐量。

开始批量插入后，在另一个会话中使用MySQL Workbench启动上述在线alter语句，alter在插入操作之前完成，添加了两列，并且没有任何行受到修改的影响，这意味着MySQL没有复制任何行。

- SAK

4为什么这个答案没有得到更多的投票？它不起作用吗？ - fguillen

6

您的sed想法是一个不错的方法，但是没有错误或您运行的命令，我们无法帮助您。

然而，一个众所周知的在线更改大型表格的方法是pt-online-schema-change。这个工具所做的简单概述来自文档：

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

这种方法可能需要一些时间才能完成，但在过程中原始表格将完全可用。

- Derek Downey

今晚稍后我会尝试批量加载。如果不起作用，可能需要这个工具。错误是由于在使用sed命令后识别某些符号引起的。例如'D\'agostini'会导致错误unknown command '\''。但并非总是如此，大约30%的情况下会出现这种情况。这很奇怪也很有bug。同样的问题甚至在十六进制blob转储中也存在。谢谢你，Derek。 - ineersa

1

目前，修改大型表的最佳选择可能是https://github.com/github/gh-ost

gh-ost是一个无触发器的MySQL在线模式迁移解决方案。它可进行测试，并提供暂停、动态控制/重新配置、审计和许多操作优势。

gh-ost在迁移过程中对主服务器产生轻负载，与已迁移表上的现有工作负载分离。

它基于多年的现有解决方案经验进行设计，并改变了表迁移的范式。

- iJanki

1

我认为Mydumper / Myloader 是一个很好的工具，适合这样的操作：它每天都在变得更好。你可以利用你的CPU并且可以并行加载数据：http://www.percona.com/blog/2014/03/10/new-mydumper-0-6-1-release-offers-several-performance-and-usability-features/

我已经成功地在几个小时内加载了数百GB的MySQL表。

现在，当涉及到添加新列时，由于MySQL会将整个表复制到内存中的TMP区域，情况就变得棘手了，使用ALTER TABLE...语句。虽然MySQL 5.6称它可以进行在线模式下的模式更改，但我尚未成功地实现对大型表进行无锁争用的在线模式更改。

- Kubilay

-2

我刚遇到了同样的问题。有一个小的解决办法：

创建新表：CREATE TABLE new_table SELECT * FROM oldtable;

删除新表中的数据：DELETE FROM new_table

向新表中添加新列：ALTER TABLE new_table ADD COLUMN new_column int(11);

将旧表中的数据插入到新表中：INSERT INTO new_table select *, 0 from old_table

删除旧表：drop table old_table; 将新表重命名为旧表：rename table new_table TO old_table;

- AirCoder

为什么不在创建表语句中添加一个where子句，这样就不会选择任何数据了呢？此外，清空表格比删除数据更高效。 - Joe W

为什么要删除，然后又必须再插入呢？可以在“ADD COLUMN”本身定义默认值为0。 - user195280

- user23243 · Accepted Answer

使用MySQL Workbench。您可以右键单击表格，然后选择“发送到SQL编辑器”-->“创建语句”。这样就不会忘记添加任何表格的“属性”（包括CHARSET或COLLATE）。对于这么大量的数据，我建议清理表格或者您使用的数据结构（一个好的数据库管理员会很有帮助）。如果不可能的话：

重命名表（使用ALTER命令），并使用从Workbench获取的CREATE脚本创建一个新表。您还可以在查询中添加所需的新字段。
将数据从旧表批量加载到新表：
```
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;
INSERT INTO new_table (fieldA, fieldB, fieldC, ..., fieldN)
   SELECT fieldA, fieldB, fieldC, ..., fieldN
   FROM old_table
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
```
这样可以避免逐条记录运行索引等操作。对表进行“更新”仍然会很慢（因为数据量很大），但这是我能想到的最快的方法。

编辑：阅读this文章，了解上述示例查询中使用的命令的详细信息；）