更改大型 MySQL InnoDB 表

Changing Large MySQL InnoDB Tables

提问by 0x4a6f4672

Adding a new column or adding a new index can take hours and days for large innodb tables in MySQL with more than 10 million rows. What is the best way to increase the performance on large innodb tables in these two cases? More memory, tweaking the configuration (for example increasing the sort_buffer_sizeor innodb_buffer_pool_size), or some kind of trick? Instead of altering a table directly, one could create a new one, change it, and copy the old data the new, like this which is useful for ISAM tablesand multiple changes:

对于 MySQL 中超过 1000 万行的大型 innodb 表，添加新列或添加新索引可能需要数小时和数天。在这两种情况下，提高大型 innodb 表性能的最佳方法是什么？更存储器，调整上述结构（例如增加sort_buffer_size的值或innodb_buffer_pool_size），或某种特技的？可以创建一个新表，更改它，然后将旧数据复制到新表中，而不是直接更改表，这对ISAM 表和多次更改很有用：

CREATE TABLE tablename_tmp LIKE tablename;
ALTER TABLE tablename_tmp ADD fieldname fieldtype;
INSERT INTO tablename_tmp SELECT * FROM tablename;
ALTER TABLE tablename RENAME tablename_old;
ALTER TABLE tablename_tmp RENAME tablename;

Is it recommendable for innodb tables, too, or is it just what the ALTER TABLE command does anway?

它是否也可用于 innodb 表，或者它只是 ALTER TABLE 命令的作用？

回答by Shlomi Noach

Edit 2016:we've recently (August 2016) released gh-ost, modifying my answer to reflect it.

2016 年编辑：我们最近（2016 年 8 月）发布了gh-ost，修改了我的答案以反映它。

Today there are several tools which allow you to do online alter table for MySQL. These are:

今天有几种工具可以让您为 MySQL 进行在线更改表。这些是：

• edit 2016:gh-ost: GitHub's triggerless schema migration tool (disclaimer: I am author of this tool)
• oak-online-alter-table, as part of the openark-kit(disclaimer: I am author of this tool)
• pt-online-schema-change, as part of the Percona Toolkit
• Facebook's online schema change for MySQL

• 2016 年编辑：gh-ost：GitHub 的无触发模式迁移工具（免责声明：我是该工具的作者）
• Oak-online-alter-table，作为openark-kit 的一部分（免责声明：我是这个工具的作者）
• pt-online-schema-change，作为Percona 工具包的一部分
• Facebook对 MySQL的在线架构更改

Let's consider the "normal" `ALTER TABLE`:

让我们考虑“正常”的`ALTER TABLE`：

A large table will take long time to ALTER. innodb_buffer_pool_sizeis important, and so are other variables, but on very large table they are all negligible. It just takes time.

一张大桌子需要很长时间ALTER。innodb_buffer_pool_size很重要，其他变量也很重要，但在非常大的表上，它们都可以忽略不计。只是需要时间。

What MySQL does to ALTERa table is to create a new table with new format, copy all rows, then switch over. During this time the table is completely locked.

MySQL 对ALTER一个表所做的就是创建一个新格式的新表，复制所有行，然后进行切换。在此期间，表完全锁定。

Consider your own suggestion:

考虑你自己的建议：

It will most probably perform worst of all options. Why is that? Because you're using an InnoDB table, the INSERT INTO tablename_tmp SELECT * FROM tablenamemakes for a transaction. a hugetransaction. It will create even more load than the normal ALTER TABLE.

它很可能在所有选项中表现最差。这是为什么？因为您使用的是 InnoDB 表，所以INSERT INTO tablename_tmp SELECT * FROM tablename这是一个事务。一笔巨大的交易。它会产生比正常情况更多的负载ALTER TABLE。

Moreover, you will have to shut down your application at that time so that it does not write (INSERT, DELETE, UPDATE) to your table. If it does - your whole transaction is pointless.

此外，您必须在那时关闭您的应用程序，以便它不会将 ( INSERT, DELETE, UPDATE) 写入您的表。如果是这样 - 你的整个交易都是毫无意义的。

What the online tools provide

在线工具提供什么

The tools do not all work alike. However, the basics are shared:

这些工具的工作方式不尽相同。但是，基础知识是共享的：

• They create a "shadow" table with altered schema
• They create and use triggers to propagate changes from original table to ghost table
• They slowlycopy all the rows from your table to shadow table. They do so in chunks: say, 1,000 rows at a time.
• They do all the above while you are still able to access and manipulate the original table.
• When satisfied, they swap the two, using a RENAME.

• 他们创建了一个具有更改架构的“影子”表
• 他们创建并使用触发器将更改从原始表传播到幽灵表
• 他们慢慢地将表中的所有行复制到影子表中。它们分块进行：比如说，一次 1,000 行。
• 当您仍然能够访问和操作原始表时，它们会完成上述所有操作。
• 当满意时，他们交换两者，使用RENAME.

The openark-kittool has been in use for 3.5 years now. The Percona tool is a few months old, but possibly more tested then the former. Facebook's tool is said to work well for Facebook, but does not provide with a general solution to the average user. I haven't used it myself.

该openark-KIT工具已使用3.5年。Percona 工具已经有几个月的历史了，但可能比前者经过更多的测试。据说 Facebook 的工具适用于 Facebook，但并未为普通用户提供通用解决方案。我自己没用过。

Edit 2016:gh-ostis a triggerless solution, which significantly reduces master write-load on the master, decoupling the migration write load from the normal load. It is auditable, controllable, testable. We've developed it internally at GitHub and released it as open source; we're doing all our production migrations via gh-osttoday. See more here.

Edit 2016：gh-ost是一种无触发器的解决方案，它显着减少了 master 上的 master 写入负载，将迁移写入负载与正常负载分离。它是可审计的、可控的、可测试的。我们在 GitHub 内部开发了它并作为开源发布；我们正在通过gh-ost今天进行所有的生产迁移。在此处查看更多信息。

Each tool has its own limitations, look closely at documentation.

每个工具都有自己的局限性，请仔细查看文档。

The conservative way

保守的方式

The conservative way is to use an Active-Passive Master-Master replication, do the ALTERon the standby (passive) server, then switch roles and do the ALTERagain on what used to be the active server, now turned passive. This is also a good option, but requires an additional server, and deeper knowledge of replication.

保守的方法是使用主动-被动主-主复制，ALTER在备用（被动）服务器上执行，然后切换角色并ALTER在以前是主动服务器，现在变成被动服务器上再次执行。这也是一个不错的选择，但需要额外的服务器和更深入的复制知识。

回答by Lokesh

Rename screws up referenced tables.

重命名搞砸了引用的表。

If you have say table_2which is child to tablename, on ALTER TABLE tablename RENAME tablename_old;table_2will start pointing to tablename_old.

如果你有说table_2哪个是孩子tablename，就ALTER TABLE tablename RENAME tablename_old;table_2开始指指点点tablename_old。

Now without altering table_2 you cannt point it back to tablename. You have to keep on going making alters in every child and referenced table.

现在不改变 table_2 你不能将它指向tablename. 您必须继续在每个子表和参考表中进行更改。