MySQL 向大表中添加一列的性能

Question

我在 Ubuntu 13.10 上使用 apt-get 在本地安装了 MySQL 5.5.37 和 InnoDB。我的机器是 i7-3770 + 32Gb 内存 + SSD 硬盘在我的桌面上。对于仅包含 150 万条记录的表“mytable”，以下 DDL 查询需要超过 20 分钟(!):

ALTER TABLE mytable ADD some_column CHAR(1) NOT NULL DEFAULT 'N';

有什么办法可以改善吗？我检查了

show processlist;

它表明它出于某种原因正在复制我的表格。这是令人不安的不便。有没有办法关闭这个副本？是否有其他方法可以提高向大型表添加列的性能？

除此之外，我的数据库相对较小，只有 1.3Gb 的转储大小。因此它应该(理论上)100% 适合内存。

是否有可以提供帮助的设置？迁移到 Precona 会对我有什么改变吗？

添加:我有

innodb_buffer_pool_size = 134217728

Answer 1

Are there other ways to improve performance of adding a column to a large table?

简短的回答:不。您可以立即添加 ENUM 和 SET 值，并且可以添加二级索引，同时锁定仅用于写入，但更改表结构始终需要表副本。

长答案:您真正的问题不是性能，而是锁定时间。如果它很慢并不重要，重要的是其他客户端在您的 ALTER TABLE 完成之前无法执行查询。在这种情况下有一些选择:

1. 您可以使用 pt-online-schema-change ，来自 Percona 工具包。首先备份您的数据!这是最简单的解决方案，但可能不适用于所有情况。

2. 如果您不使用外键并且速度很慢，因为您有很多索引，那么您创建一个包含您需要的更改但没有二级索引的表副本可能会更快，填充它使用数据，并在最后使用单个更改表创建所有索引。

3. 如果您可以轻松创建副本，例如您在 Amazon RDS 上托管，您可以创建一个主-主副本，在那里运行 alter table，让它重新同步，然后切换实例完成后。

更新

正如其他人提到的，MySQL 8.0 INNODB 添加了对即时列添加的支持。这不是一个神奇的解决方案，它有局限性和副作用——它只能是最后一列，表不能有全文索引等——但在许多情况下应该会有所帮助。

您可以指定显式 ALGORITHM=INSTANT LOCK=NONE 参数，如果无法立即更改架构，MySQL 将失败并返回错误而不是回退到 INPLACE 或 复制。示例:

ALTER TABLE mytable
ADD COLUMN mycolumn varchar(36) DEFAULT NULL,
ALGORITHM=INPLACE, LOCK=NONE;

https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/