在没有表锁定的情况下在巨大的MySQL生产表上创建索引 [英] Create an index on a huge MySQL production table without table locking

问题描述

我需要在~5M行MySQL表上创建索引。这是一个生产表，如果我运行CREATE INDEX语句，我担心一切都完整...

I need to create an index on a ~5M rows MySQL table. It is a production table, and I fear a complete block of everything if I run a CREATE INDEX statement...

有没有办法创建该索引而不阻塞插入并选择？

Is there a way to create that index without blocking inserts and selects?

只是想知道我没有停下来，创建索引并重启我的系统！

Just wondering I have not to stop, create index and restart my system!

推荐答案

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

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview .html

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

In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Previously, modifying the table while an index is being created or dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on the table.

[2015]更新表指示在MySQL 5.5中阻止写入

从上面的答案：

[2015] Updating table indicies blocks writes in MySQL 5.5

From the answer above:

如果您在数据库联机时使用大于5.1索引的版本。那么不要担心您不会中断生产系统的使用。

这是**** FALSE **** （至少对于MyISAM / InnoDB表来说，这是什么99.999％的人使用。群集版本不同。）

This is ****FALSE**** (at least for MyISAM / InnoDB tables, which is what 99.999% of people out there use. Clustered Edition is different.)

在创建索引时，对表执行UPDATE操作将 BLOCK 。 MySQL真的非常愚蠢（以及其他一些事情）。

Doing UPDATE operations on a table will BLOCK while the index is being created. MySQL is really, really stupid about this (and a few other things).

测试脚本：

(   
  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）：

My Server (InnoDB):

Server version: 5.5.25a Source distribution

输出（注意第6次操作如何阻止〜400ms所需完成索引更新）：

Output (notice how the 6th operation blocks for the ~400ms it takes to finish the index update):

 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

对不阻止的Vs读取操作（交换脚本中的行注释）：

Vs read operations which don't block (swap the line comment in the script):

 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架构并且不会出现可用性中断。循环大师：

Updating MySQL's Schema without downtime

Thusfar, there's only one method I know of to update a MySql schema and not suffer an availability outage. Circular masters:

• Master A运行你的MySQL数据库


• 让B大师服务并让它复制来自Master A的写入（B是A的奴隶）


• 在Master B上执行架构更新。它将在升级期间落后


• 让B大师赶上来。不变：您的模式更改必须能够处理从反转模式复制的命令。索引更改符合条件。简单的列添加通常符合条件。删除列？可能不会。


• 原子地将所有客户从主A交换到主B.如果你想要安全（相信我，你这样做），你应该确保最后一次写入A被复制到B BEFORE B进行第一次写入。如果你允许并发写入2+主人，...你最好在DEEP级别理解MySQL复制，或者你正在走向痛苦的世界。极度痛苦。比如，你有一个是AUTOINCREMENT的列吗？你搞砸了（除非你在一个主人身上使用偶数，而在另一个主人身上使用赔率）。不要相信MySQL复制做正确的事情。它不聪明，不会救你。它比从命令行复制二进制事务日志并手动重放它的安全性稍差。尽管如此，将所有客户端与旧主服务器断开连接并将其翻转到新主服务器可以在几秒钟内完成，比等待多小时架构升级快得多。


• Now Master B是你的新主人。你有新的架构。生活很好。喝啤酒;最坏的结束了。


• 用A大师重复这个过程，升级他的架构，让他成为你的新的二级主人，准备好接替你的主要主人（现在是主人B） ）失去权力或只是失败而死亡。

• Master A has your MySQL database running on it
• Bring Master B into service and have it replicate writes from Master A ( B is a slave of A)
• Perform the schema update on Master B. It will fall behind during the upgrade
• Let Master B catch up. Invariant: Your schema change MUST be capable of processing commands replicated from a downversion schema. Indexing changes qualify. Simple column additions usually qualify. Removing a column? probably not.
• ATOMICALLY swap all clients from Master A to Master B. If you want to be safe (trust me, you do), you should ensure that the last write to A is replicated to B BEFORE B takes its first write. If you allow concurrent writes to 2+ masters, ... you better understand MySQL replication at a DEEP level or you are headed for a world of pain. Extreme pain. Like, do you have a column that is AUTOINCREMENT??? you are screwed (unless you use even numbers on one master and odds on the other). Do NOT trust MySQL replication to "do the right thing". It is NOT smart and will not save you. It's just slightly less safe than copying binary transaction logs from the command-line and replaying them by hand. Still, disconnecting all clients from the old master and flipping them to the new master can be done in a matter of seconds, vastly faster than waiting for a multi-hour schema upgrade.
• Now Master B is your new master. You have the new schema. Life is good. Have a beer; the worst is over.
• Repeat the process with Master A, upgrading his schema so that he becomes your new secondary master, ready to take over in the event that your primary master (master B now) loses power or just up and dies on you.

更新架构的一种简单方法不是。可在严峻的生产环境中使用;是的。拜托，请，如果有一种更简单的方法可以在不阻止写入的情况下将索引添加到MySQL表中，请告诉我。

An easy way to update schema this isn't. Workable in a serious production environment; yes, it is. Please, please, please, if there is an easier way to add an index to a MySQL table without blocking writes, let me know.

Google搜索引导我本文描述了一种类似的技术。更好的是，他们建议在进行中的同一时间饮酒（注意我在阅读文章之前写了我的答案）！

Googling lead me to this article which describes a similar technique. Even better, they advise drinking at the same point in the proceedure (Note that I wrote my answer before reading the article)!

文章上面我链接了一个工具， pt-online-schema-change ，其工作方式如下：

The article I linked above talks about a tool, pt-online-schema-change, that works as follows:

• 创建新表与原始结构相同。


• 更新新表上的模式。


• 在原始表上添加触发器，以使更改保持同步副本


• 从原始表中批量复制行。


• 将原始表移开并替换为新表。


• 删除旧表。

• Create new table with same structure as original.
• Update schema on new table.
• Add a trigger on the original table so that changes are kept in-sync with the copy
• Copy rows in batches from original table.
• Move original table out of the way and replace with new table.
• Drop old table.

我自己从未尝试过这个工具。 YMMV

I've never tried the tool myself. YMMV

我目前正在通过亚马逊的RDS 。这是一个非常好的服务，包装和管理MySQL，让您只需一个按钮添加新的只读副本，并透明地在硬件SKU上升级数据库。这真的很方便。您没有获得对数据库的SUPER访问权限，因此您无法直接使用复制（这是一种祝福还是诅咒？）。但是，您可以使用只读副本升级，使您的架构在只读从站上更改，然后将该从站升级为新的主站。与我上面描述的完全相同的技巧，只是更容易执行。他们仍然没有做太多帮助你进行切换。您必须重新配置并重新启动应用。

I'm currently using MySQL through Amazon's RDS. It's a really nifty service that wraps up and manages MySQL, letting you add new read replicas with a single button and transparently upgrade the database across hardware SKU's. It's really convenient. You don't get SUPER access to the database, so you can't screw with replication directly (is this a blessing or curse?). However, you can use Read Replica Promotion to make your schema changes on a read-only slave, then promote that slave to become your new master. Exactly the same trick as I described above, just vastly easier to execute. They still don't do much to help you with the cut-over. You have to reconfigure and restart your app.

这篇关于在没有表锁定的情况下在巨大的MySQL生产表上创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！