mysql 反转索引,indexing - 在没有表锁定的情况下在巨大的MySQL生产表上创建索引...

[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语句。

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

从上面的答案：

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

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

在创建索引时，对表执行UPDATE操作将为BLOCK。 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

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

无需停机即可更新MySQL的架构

因此，我知道只有一种方法可以更新MySql架构并且不会出现可用性中断。 圆形大师：

Master A在其上运行您的MySQL数据库

将B大师投入使用并让它复制来自Master A的写入(B是A的奴隶)

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

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

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

现在B大师是你的新主人。 你有新的架构。 生活很好。 喝啤酒; 最坏的结束了。

使用Master A重复此过程，升级他的架构，以便他成为您的新辅助主人，准备好在您的主要主人(现在的主人B)失去权力或仅仅因为你而死亡的情况下接管。

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

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

Percona的pt-online-schema-change

我上面链接的文章讨论了一个工具，pt-online-schema-change，其工作原理如下：

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

更新新表的架构。

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

从原始表批量复制行。

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

放旧桌子。

我自己从未尝试过这个工具。因人而异

RDS

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