向大表添加列的 MySQL 性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24660456/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL performance of adding a column to a large table
提问by Artem
I have MySQL 5.5.37 with InnoDB installed locally with apt-get on Ubuntu 13.10. My machine is i7-3770 + 32Gb memory + SSD hard drive on my desktop. For a table "mytable" which contains only 1.5 million records the following DDL query takes more than 20 min (!):
我在 Ubuntu 13.10 上使用 apt-get 在本地安装了带有 InnoDB 的 MySQL 5.5.37。我的机器是i7-3770+32Gb内存+台式机的SSD硬盘。对于仅包含 150 万条记录的“mytable”表，以下 DDL 查询需要超过 20 分钟（！）：
ALTER TABLE mytable ADD some_column CHAR(1) NOT NULL DEFAULT 'N';
Is there a way to improve it? I checked
有没有办法改善它？我检查了
show processlist;
and it was showing that it is copying my table for some reason. It is disturbingly inconvenient. Is there a way to turn off this copy? Are there other ways to improve performance of adding a column to a large table?
它表明它出于某种原因正在复制我的表。这是令人不安的不方便。有没有办法关闭这个副本？还有其他方法可以提高向大表添加列的性能吗？
Other than that my DB is relatively small with only 1.3Gb dump size. Therefore it should (in theory) fit 100% in memory.
除此之外，我的数据库相对较小，转储大小只有 1.3Gb。因此，它（理论上）应该 100% 适合内存。
Are there settings which can help? Would migration to Precona change anything for me?
有没有可以帮助的设置？迁移到 Precona 会对我有什么改变吗？
Add: I have
补充：我有
innodb_buffer_pool_size = 134217728
回答by Pedro Werneck
Are there other ways to improve performance of adding a column to a large table?
还有其他方法可以提高向大表添加列的性能吗？
Short answer: no. You may add ENUM and SET values instantly, and you may add secondary indexes while locking only for writes, but altering table structure always requires a table copy.
简短的回答：没有。您可以立即添加 ENUM 和 SET 值，并且您可以在仅针对写入锁定的同时添加二级索引，但更改表结构始终需要表副本。
Long answer: your real problem isn't really performance, but the lock time. It doesn't matter if it's slow, it only matters that other clients can't perform queries until your ALTER TABLE is finished. There are some options in that case:
长答案：您真正的问题不是真正的性能，而是锁定时间。它是否慢并不重要，重要的是在您的 ALTER TABLE 完成之前其他客户端无法执行查询。在这种情况下有一些选择：
You may use the pt-online-schema-change, from Percona toolkit. Backup your data first! This is the easiest solution, but may not work in all cases.
If you don't use foreign keys and it's slow because you have a lot of indexes, it might be faster for you to create a copy of the table with the changes you need but no secondary indexes, populate it with the data, and create all indexes with a single alter table at the end.
If it's easy for you to create replicas, like if you're hosted at Amazon RDS, you may create a master-master replica, run the alter table there, let it get back in sync, and switch instances after finished.
您可以使用Percona 工具包中的pt-online-schema-change。先备份数据！这是最简单的解决方案，但可能不适用于所有情况。
如果您不使用外键并且速度很慢，因为您有很多索引，那么您可以更快地创建表的副本，其中包含您需要的更改但没有二级索引，用数据填充它，然后创建所有索引末尾都有一个变更表。
如果您可以轻松创建副本，例如您托管在 Amazon RDS 上，您可以创建一个主-主副本，在那里运行更改表，让它恢复同步，并在完成后切换实例。
UPDATE
更新
As others mentioned, MySQL 8.0 INNODB added support for instant column adds. It's not a magical solution, it has limitations and side-effects -- it can only be the last column, the table must not have a full text index, etc -- but should help in many cases.
正如其他人提到的，MySQL 8.0 INNODB 添加了对即时列添加的支持。这不是一个神奇的解决方案，它有局限性和副作用——它只能是最后一列，表不能有全文索引等——但在许多情况下应该会有所帮助。
You can specify explicit ALGORITHM=INSTANT LOCK=NONEparameters, and if an instant schema change isn't possible, MySQL will fail with an error instead of falling back to INPLACEor COPY. Example:
您可以指定显式ALGORITHM=INSTANT LOCK=NONE参数，如果无法立即更改架构，MySQL 将失败并显示错误而不是回退到INPLACE或COPY。例子：
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/
https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/
回答by ColinM
MariaDb 10.3, MySQL 8.0 and probably other MySQL variants to follow have an "Instant ADD COLUMN" feature whereby most columns (there are a few constraints, see docs) can be added instantly with no table rebuild.
MariaDb 10.3、MySQL 8.0 和可能要遵循的其他 MySQL 变体具有“即时添加列”功能，其中大多数列（有一些约束，请参阅文档）可以立即添加而无需重建表。
- MariaDb: https://mariadb.com/resources/blog/instant-add-column-innodb
- MySQL: https://mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/
- MariaDb：https://mariadb.com/resources/blog/instant-add-column-innodb
- MySQL：https: //mysqlserverteam.com/mysql-8-0-innodb-now-supports-instant-add-column/
回答by Hymanson Cassimiro
What about Online DDL?
在线 DDL 怎么样？
http://www.tocker.ca/2013/11/05/a-closer-look-at-online-ddl-in-mysql-5-6.html
http://www.tocker.ca/2013/11/05/a-closer-look-at-online-ddl-in-mysql-5-6.html
Maybe you would use TokuDB instead:
也许你会改用 TokuDB：
回答by Marcus Adams
There is no way to avoid copying the table when adding or removing columns because the structure changes. You can add or remove secondary indexes without a table copy.
由于结构发生变化，在添加或删除列时无法避免复制表。您可以在没有表副本的情况下添加或删除二级索引。
Your table data doesn't reside in memory. The indexes can reside in memory.
您的表数据不驻留在内存中。索引可以驻留在内存中。
1.5 million records is not a lot of rows, and 20 minutes seems quite long, but perhaps your rows are large and you have many indexes.
150 万条记录并不是很多行，20 分钟似乎很长，但也许您的行很大并且您有很多索引。
While the table is being copied, you can still select rows from the table. However, if you try to do any updates, they will be blocked until the ALTERis complete.
在复制表时，您仍然可以从表中选择行。但是，如果您尝试进行任何更新，它们将被阻止，直到ALTER完成。
回答by Nebu
I know this is a rather old question but today i encountered a similar problem. I decided to create a new table and to import the old table in the new table. Something like:
我知道这是一个相当古老的问题，但今天我遇到了类似的问题。我决定创建一个新表并将旧表导入新表中。就像是：
CREATE TABLE New_mytable LIKE mytable ;
ALTER TABLE New_mytable ADD some_column CHAR(1) NOT NULL DEFAULT 'N';
insert into New_mytable select * from mytable ;
Then
然后
START TRANSACTION;
insert into New_mytable select * from mytable where id > (Select max(id) from New_mytable) ;
RENAME TABLE mytable TO Old_mytable;
RENAME TABLE New_mytable TO mytable;
COMMIT;
This does not make the update process go any faster, but it does minimize downtime.
这不会使更新过程更快，但确实可以最大限度地减少停机时间。
Hope this helps.
希望这可以帮助。
