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

Question

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

mysqlindexingproductionalter-tabletable-locking

148

我需要在一张大约有5百万行的MySQL表上创建索引。这是一个生产表，如果我运行CREATE INDEX语句，我担心会完全阻塞一切...

有没有一种方法可以在不阻塞插入和选择的情况下创建该索引？

只是好奇我没有停下来，创建索引然后重新启动系统！

- n0cturnal

1

请确保您的myisam_sort_buffer_size和myisam_max_sort_file_size足够大。 - Jon Black

4个回答

89

根据这篇博客文章，InnoDB的ALTER TABLE机制已经在MySQL 5.6中进行了完全重新设计。

（有关此主题的独家概述，请参阅MySQL文档，可以提供一下午的阅读。）

要向表中添加索引而不锁定从而导致UPDATE/ INSERT，可以使用以下语句格式：

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

- Drew

6

注意：https://dba.stackexchange.com/questions/138363/mysql-create-index-with-lock-none-still-locks-a-table即使使用'LOCK=NONE'选项，MySQL中创建索引仍会锁定表。 - Alexander Torstling

另一个需要注意的是：运行此命令时，如果出现错误_"Cannot change column type INPLACE. Try ALGORITHM=COPY"_, 这是因为您的原始表是在MySQL 5.6之前创建的，尚不支持此操作: https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html - alexkb

20

MySQL 5.6更新（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，而不是Fast Index Creation。

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

- Eric Saboia

那么，如何解释戴夫的分析呢？ - Nikhil Sahu

2

@NikhilSahu Dave 明显没有在 MySQL 5.6 上进行测试，而是在一些旧版本上进行的。请注意，在 Dave 发布他的答案的初始修订时，5.6 还未发布。 - Mark Amery

1

+1. 我的分析是基于 MySQL 5.5（2013年最新版本）。我正在更新我的答案以反映 MySQL 5.6 的新功能。 - Dave Dopson

3

如果您确实希望迁移不会导致网站崩溃，那么pt-online-schema-change是一个不错的选择。

正如我在以上评论中所写，我有多次在生产��境中使用pt-online-schema-change的经验。我们有一个包含20M+记录的主表和一个主库-> 2个只读副本。我至少进行了数十次使用pt-online-schema-change的迁移，从添加新列、更改字符集到添加多个索引。我们在迁移期间也提供了大量流量，但我们没有遇到任何问题。当然，在生产环境运行之前，您必须非常彻底地测试所有脚本。

我尝试将更改批处理成1个脚本，以便pt-online-schema-change只需要复制数据一次。并且非常小心更改列名，因为您将会失去您的数据。但是，添加索引应该是没有问题的。

- Alex Le

我不同意你对pt-online-schema-change的不合格推荐。它很好，但在许多情况下是过度杀伤力的，因为MySQL 5.6+的在线DDL功能已经可以很好地工作了。它也有一些限制（比如与触发器不兼容），并且在模式更改正在进行时，需要将原始表中每个插入操作的写入量加倍。它会比普通的在线模式更改更大程度地占用您的磁盘，因此在只需以简单方式运行模式更改就可以正常工作的情况下，可能会“使您的网站崩溃”。 - Mark Amery

我根据自己当时使用pt-online-schema-change的实际经验写出了这篇文章，所以我不确定为什么你会称我的建议为“不合格”。当我运行模式更改时，我们的网站每时每刻至少有1000多名访问者，当然，磁盘IO是很吃紧的，但我们的网站没有崩溃。良好的缓存也有所帮助。我没有使用过MySQL 5.6+在线DDL，但从我的经验来看，在我们的情况下，pt-online-schema-change工作得很好。 - Alex Le

1

@AlexYe 哎呀，我的意思是“毫不保留”的意思，而不是“由不合格的人提供”的意思——后者的解释直到看到你的评论才想到，肯定不是我想要的！也就是说，虽然 pt-online-schema-change 是一个有用的工具，但在许多情况下，普通的在线 DDL 一样好，甚至更好，在少数情况下，它更好，因此对它的任何推荐都应该仔细说明，而不是普遍适用。 - Mark Amery

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Dave Dopson · Accepted Answer

[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的版本，则可以在数据库在线时创建索引。因此不要担心您不会中断生产系统使用。"

这是错误的（至少对于 MyISAM / InnoDB 表格，这是99.999%的人使用的）。Clustered Edition 不同。

在创建索引时，对表执行 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个操作阻塞了大约400毫秒，直到索引更新完成）：

 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 的模式更新方法

目前为止，我只知道一种在更新 MySQL 模式时不会导致不可用的方法：循环主服务器：

主服务器 A 上运行着您的 MySQL 数据库
启用主服务器 B，并使其复制来自主服务器 A 的写入操作（B 是 A 的从服务器）
在主服务器 B 上执行方案更新。在升级过程中，它将滞后。
让主服务器 B 追赶上来。不变量：您的模式更改必须能够处理从旧版本模式复制的命令。索引更改符合条件。通常可以添加简单列。删除一列吗？可能不行。
原子地将所有客户端从主服务器 A 切换到主服务器 B。如果您想保险（相信我，您需要这样做），您应确保 A 的最后一次写入在 B 第一次写入之前被复制。如果允许 2 个或更多主服务器并发写入，则您最好深入了解 MySQL 复制，否则您将面临极大的痛苦。极大的痛苦。例如，您有一个是 AUTOINCREMENT 的列吗？除非您在其中一个主服务器上使用偶数，在另一个主服务器上使用奇数，否则您就会遭殃。不要相信 MySQL 复制会“做正确的事情”。它不是很聪明，也不会拯救您。它只比通过命令行复制二进制事务日志并手动重放它们稍微安全一点。但是，与等待多小时的模式更新相比，将所有客户端从旧主服务器断开并切换到新主服务器可以在几秒钟内完成，速度远远快于等待时间。
现在，主服务器 B 是您的新主服务器。您有新的模式了。生活美好。来杯啤酒吧；最糟糕的已经过去了。
使用相同的方法对主服务器 A 进行操作，升级其模式，以便成为新的辅助主服务器，在您的主服务器（现在是主服务器 B）失电或突然停机时接管控制权。

这不是一个简单的更新模式的方法。但它在严肃的生产环境中可行。如果有更容易添加索引到 MySQL 表而不阻止写入的方法，请告诉我。

通过 Google 我找到了这篇文章，其中描述了类似的技术。更好的是，在程序的相同点，他们建议一起喝酒（请注意，我在阅读该文章之前写了我的答案）！

Percona 的 pt-online-schema-change 工具

我链接的文章谈到了一个名为pt-online-schema-change的工具，它的工作原理如下：

创建与原始表具有相同结构的新表。
在新表上更新模式。
在原始表上添加触发器，以便更改与副本保持同步。
按批次从原始表中复制行。
将原始表移开并替换为新表。
删除旧表。

我自己从未尝试过这个工具。你的体验可能会有所不同。

RDS

我目前通过Amazon的RDS使用MySQL。它是一个非常方便的服务，可以封装和管理MySQL，让您只需单击一个按钮即可添加新的只读副本，并在硬件SKU之间透明地升级数据库。你不能直接干涉数据库复制（这是福还是祸？）。但是，您可以使用只读副本提升来对只读副本进行模式更改，然后将该副本提升为新主节点。完全与我上面描述的技巧相同，只是执行起来非常容易。他们仍然没有为您提供太多关于切换的帮助。你必须重新配置和重启应用程序。