在没有表锁定的情况下，为一个巨大的 MySQL 生产表创建索引

我需要在一个 ~ 5M 行的 MySQL 表上创建一个索引。它是一个产品表，如果运行 CREATEINDEX 语句，我担心所有内容都会被完全阻塞..。

有没有一种不阻塞插入和选择就能创建索引的方法？

只是想知道我没有停止，创建索引和重新启动我的系统！

113556 次浏览
小开

[2017]更新: MySQL 5.6支持在线索引更新

Https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-syntax-notes

在 MySQL 5.6或更高版本中，当创建或删除索引时，表仍然可用于读写操作。CREATE INDEX 或 DROP INDEX 语句仅在访问该表的所有事务完成之后才结束，因此索引的初始状态反映该表的最新内容。以前，在创建或删除索引时修改表通常会导致死锁，从而取消表上的 INSERT、 UPDATE 或 DELETE 语句。

[2015]更新 MySQL 5.5中写入的表索引块

来自上面的答案:

”如果您使用的版本大于5.1的索引是在数据库在线时创建的。所以不用担心，你不会中断生产系统的使用。”

这是 * * * * FALSE * * * * (至少对于 MyISAM/InnoDB 表来说是这样，99.999% 的人使用这种表。集群版则不同。)

在创建索引时，对表执行 UPDATE 操作将 街区。MySQL 在这方面真的非常非常愚蠢(还有其他一些事情)。

测试脚本:

(
for n in {1..50}; do
#(time mysql -uroot -e 'select  * from website_development.users where id = 41225\G'>/dev/null) 2>&1 | grep real;
(time mysql -uroot -e 'update website_development.users set bio="" where id = 41225\G'>/dev/null) 2>&1 | grep real;
done
) | cat -n &
PID=$!
sleep 0.05
echo "Index Update - START"
mysql -uroot website_development -e 'alter table users add index ddopsonfu (last_name, email, first_name, confirmation_token, current_sign_in_ip);'
echo "Index Update - FINISH"
sleep 0.05
kill $PID
time mysql -uroot website_development -e 'drop index ddopsonfu on users;'

我的服务器(InnoDB) :

Server version: 5.5.25a Source distribution

输出(注意第6个操作是如何阻塞完成索引更新所需的 ~ 400ms 的) :

 1  real    0m0.009s
2  real    0m0.009s
3  real    0m0.009s
4  real    0m0.012s
5  real    0m0.009s
Index Update - START
Index Update - FINISH
6  real    0m0.388s
7  real    0m0.009s
8  real    0m0.009s
9  real    0m0.009s
10  real    0m0.009s
11  real    0m0.009s

相对于不会阻塞的读操作(交换脚本中的行注释) :

 1  real    0m0.010s
2  real    0m0.009s
3  real    0m0.009s
4  real    0m0.010s
5  real    0m0.009s
Index Update - START
6  real    0m0.010s
7  real    0m0.010s
8  real    0m0.011s
9  real    0m0.010s
...
41  real    0m0.009s
42  real    0m0.010s
43  real    0m0.009s
Index Update - FINISH
44  real    0m0.012s
45  real    0m0.009s
46  real    0m0.009s
47  real    0m0.010s
48  real    0m0.009s

不停机地更新 MySQL 的 Schema

到目前为止，据我所知，只有一种方法可以更新 MySql 模式而不会出现可用性中断。通告:

  • 主 A 上运行着您的 MySQL 数据库
  • 让主 B 服务，并让它从主 A 复制写操作(B 是 A 的奴隶)
  • 在主 B 上执行模式更新。它将在升级过程中落后
  • 让 B 少爷跟上。不变性: 您的模式更改必须能够处理从降级模式复制的命令。索引更改符合条件。简单的列添加通常符合条件。移除一根柱子？可能不会。
  • 将所有客户从 A 主机交换到 B 主机。如果你想要安全(相信我，你做的) ，你应该确保最后一次写到 A 被复制到 B 之前 B 采取它的第一次写。如果你允许对2 + master 进行并发写操作，... ... 那么你最好在深层次上理解 MySQL 复制，否则你将面临一个痛苦的世界。极度痛苦。比如，你有没有一个专栏是“自动增加”? ? ？你完蛋了(除非你在一个主人身上使用偶数，在另一个主人身上使用赔率)。不要信任 MySQL 复制来“做正确的事情”。它不聪明，也救不了你。与从命令行复制二进制事务日志并手动重放它们相比，这只是稍微不那么安全。尽管如此，断开所有客户机与旧主机的连接并将它们切换到新主机只需几秒钟的时间，这比等待数小时的模式升级要快得多。
  • 现在 B 师父是你们的新师父。您有了新的模式。生活真美好。喝杯啤酒吧，最糟糕的已经过去了。
  • 对主人 A 重复这个过程，升级他的模式，这样他就成为你的新的次级主人，准备好在你的主要主人(现在的主人 B)失去能量或者刚刚起死回生的情况下接管你的工作。

这不是更新模式的简单方法。在严肃的生产环境中是可行的; 是的，它是可行的。请，请，请，如果有一个更简单的方法添加一个索引到 MySQL 表不阻塞写，让我知道。

通过谷歌我找到了 这篇文章，它描述了一种类似的技术。更好的是，他们建议在同一时间喝酒(注意，我在阅读这篇文章之前写了我的答案) ！

Percona 的 pt-online-schema-change

上面我链接的 文章谈到了一个工具，Pt-online-schema-change，它的工作原理如下:

  • 创建结构与原始表相同的新表。
  • 更新新表上的架构。
  • 在原始表上添加触发器，使更改与副本保持同步
  • 从原始表中批量复制行。
  • 将原始表移开，并用新表替换。
  • 扔掉旧桌子。

我自己从来没有试过这个工具

RDS

我目前正在通过 亚马逊的 RDS使用 MySQL。这是一个非常漂亮的服务，它包装并管理 MySQL，允许您用一个按钮添加新的读取副本，并透明地跨硬件 SKU 升级数据库。真的很方便。您无法获得对数据库的超级访问权限，因此不能直接干扰复制(这是福还是祸?).但是，您可以使用 阅读复制品促销对只读从属服务器进行模式更改，然后将该从属服务器提升为您的新主服务器。和我上面描述的一模一样，只是更容易执行。他们还是不怎么帮你。你必须重新配置和重启你的应用程序。

小开

正如这个 博客文章所概述的，InnoDB ALTER TABLE机制已经为 MySQL 5.6完全重新设计。

(对于这个话题的独家概述，MySQL 文档可以提供一个下午的阅读价值。)

要向 UPDATE/INSERT产生的表 没有锁添加索引，可以使用以下语句格式:

ALTER TABLE my_table ADD INDEX my_table__idx (my_column), ALGORITHM=INPLACE, LOCK=NONE;
小开

如果您真的希望确保迁移不会导致站点崩溃，那么 pt-online-schema-change 是一种可行的方法。

正如我在上面的评论中所写的，我在生产环境中有一些 pt-online-schema-change 的经验。我们有一个包含20M 以上记录的主表和一个 master-> 2只读复制从表。我至少用 pt-online-schema-change 完成了几十次迁移，从添加新列、更改 charset 到添加几个索引。在迁徙期间，我们也提供大量的交通服务，我们没有遇到任何问题。当然，在生产环境下运行之前，您必须非常彻底地测试所有的脚本。

我尝试将更改批处理为1个脚本，这样 pt-online-schema-change 只需要复制数据一次。并且在更改列名时要非常小心，因为这样会导致数据丢失。但是，添加索引应该没问题。

小开

MySQL 5.6 update (2013年2月) : 您现在可以在创建索引时执行读写操作，即使使用 InnoDB 表 -http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

在 MySQL 5.6或更高版本中，当创建或删除索引时，表仍然可用于读写操作。CREATE INDEX 或 DROP INDEX 语句仅在访问该表的所有事务完成之后才结束，因此索引的初始状态反映该表的最新内容。以前，在创建或删除索引时修改表通常会导致死锁，从而取消表上的 INSERT、 UPDATE 或 DELETE 语句。

以及:

在 MySQL 5.6中，这个特性变得更加通用: 您可以在创建索引时对表进行读写操作，并且可以执行更多类型的 ALTER TABLE 操作，而无需复制表、无需阻塞 DML 操作，或者两者兼而有之。因此，在 MySQL 5.6或更高版本中，我们通常将这组特性称为在线 DDL，而不是快速索引创建。

从 http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_fast_index_creation


提交答案