在没有表锁定的大型MySQL生产表上创建索引。

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...

我需要在一个大约5M行的MySQL表上创建一个索引。它是一个生产表，如果我运行一个CREATE INDEX语句，我担心会有一个完整的块……

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!

我只是想知道我是否需要停止，创建索引并重新启动我的系统!

4 个解决方案

#1

[2017] Update: MySQL 5.6 has support for online index updates

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

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

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.

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

[2015] Updating table indicies blocks writes in MySQL 5.5

From the answer above:

从上面的回答:

"If your using a version greater than 5.1 indices are created while the database is online. So not to worry you won't interrupt production system use."

如果您使用的版本大于5.1，则在数据库联机时创建。所以不要担心你不会中断生产系统的使用。

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

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

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).

在创建索引时，对表执行更新操作将阻塞。MySQL真的很愚蠢(还有其他一些东西)。

Test Script:

测试脚本:

(   
  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;'

My Server (InnoDB):

我的服务器(InnoDB):

Server version: 5.5.25a Source distribution

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

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

 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 read operations which don't block (swap the line comment in the script):

Vs阅读不阻塞的操作(切换脚本中的行注释):

 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

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:

到目前为止，我只知道一种方法可以更新MySql模式，而不会出现可用性中断。循环大师:

• Master A has your MySQL database running on it
• Master A上运行MySQL数据库
• Bring Master B into service and have it replicate writes from Master A ( B is a slave of A)
• 将主B带入服务并让它复制主A的写操作(B是A的奴隶)
• Perform the schema update on Master B. It will fall behind during the upgrade
• 在主b上执行模式更新，它在升级过程中会落后
• 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.
• 让B大师跟上。不变量:您的模式更改必须能够处理从下版本模式复制的命令。索引的变化。简单的列添加通常是合格的。删除一个列?可能不会。
• 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.
• 原子化地将所有客户端从主A交换到主B(如果您希望安全(相信我，您确实需要)，您应该确保在B进行第一次写入之前，将最后一次对A的写入复制到B。如果您允许并发写到2+ master，……您最好深入了解MySQL复制，否则您将面临一个痛苦的世界。极端的痛苦。比如，你有一个列是自动递增的吗?你被搞砸了(除非你在一个主人身上使用偶数，另一个主人用的是奇数)。不要相信MySQL复制是“做正确的事情”。它不聪明也救不了你。它只是比从命令行复制二进制事务日志并手动重放它们要安全的多。尽管如此，将所有客户端与旧主服务器断开连接并将其转换为新主服务器只需几秒钟的时间，比等待几个小时的模式升级要快得多。
• Now Master B is your new master. You have the new schema. Life is good. Have a beer; the worst is over.
• 现在B大师是你的新主人。你有了新的模式。生活是美好的。有一个啤酒;最糟糕的时期已经过去。
• 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.
• 与大师A重复这个过程，升级他的模式，使他成为你的新二级大师，准备在你的主大师(大师B现在)失去力量或刚死在你身上时接管。

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.

更新模式的一种简单方法不是。在严重的生产环境中可行;是的,它是。求求你，求求你，如果有一种更简单的方法可以在不阻塞写的情况下向MySQL表添加索引，请告诉我。

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)!

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

Percona's pt-online-schema-change

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

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

• 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.
• 删除旧表。

I've never tried the tool myself. YMMV

我从来没试过这个工具。YMMV

RDS

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.

我目前正在通过Amazon的RDS使用MySQL。这是一个非常棒的服务，它封装并管理MySQL，允许您使用一个按钮添加新的读取副本，并透明地跨硬件SKU升级数据库。真的很方便。您无法获得对数据库的超级访问权限，因此无法直接使用复制(这是好事还是坏事?)但是，您可以使用Read Replica Promotion在只读从服务器上更改模式，然后将该从服务器升级为新主服务器。和我上面描述的完全一样，只是更容易执行。他们仍然不能帮你解决这个问题。你必须重新配置并重新启动你的应用程序。

#2

As this blog post outlines, the InnoDB ALTER TABLE mechanism has been completely redesigned for MySQL 5.6.

正如本文所述，InnoDB ALTER TABLE机制已经为MySQL 5.6进行了彻底的重新设计。

(For an exclusive overview of this topic, the MySQL documentation can provide an afternoon's worth of reading.)

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

To add an index to a table without a lock resulting on UPDATE/ INSERT, the following statement format can be used:

若要将索引添加到表中，而没有在更新/插入时产生锁，可以使用以下语句格式:

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

#3

MySQL 5.6 update (feb 2013): You can now perform read and write operations while an index is being created even with InnoDB tables - http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

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

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.

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

and:

和:

In MySQL 5.6, this feature becomes more general: you can read and write to tables while an index is being created, and many more kinds of ALTER TABLE operations can be performed without copying the table, without blocking DML operations, or both. Thus in MySQL 5.6 and higher, we typically refer to this set of features as online DDL rather than Fast Index Creation.

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

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

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

#4

pt-online-schema-change is the way to go if you really want to make sure that the migration will not bring down the site.

如果您真的想确保迁移不会导致站点宕机，那么可以使用pt-online-schema-change。

As I wrote in the above comment, I have several experiences with pt-online-schema-change in production. We have our main table of 20M+ records and a master -> 2 read-only replication slaves. I've done at least a dozens of migrations with pt-online-schema-change from adding a new column, changing charset, to adding several indices. We serve tons of traffic during the migration time as well and we have not had any hiccup. Of course you'd have to test all the scripts very thoroughly before running on production.

正如我在上面的评论中所写的，我在生产过程中有过几次ps -online-schema-change的经验。我们的主表有20M+记录和一个主-> 2只读复制从属。我已经使用pt-online模式进行了至少几十次迁移，从添加新列、更改字符集，到添加几个索引。我们在迁移期间也提供了大量的交通服务，而且我们没有遇到任何问题。当然，在运行产品之前，您必须对所有脚本进行彻底的测试。

I tried to batch up the changes into 1 script so that pt-online-schema-change only have to copy the data once. And be very careful with changing column name since you will loose your data. However, adding an index should be fine.

我试着将这些更改批处理为1个脚本，以便pt-online-schema-change只需复制一次数据。并且要非常小心的改变列名，因为你会丢失你的数据。不过，添加索引应该没问题。