mysql 在线alter table要小心

mysql 5.6之前, alter table操作对可用性有巨大的冲击(除了纯改表名、不影响任何数据的alter table)。它的原理是,

0. alter table语句开始

1. 等待当前所有操作结束

2. 施加shared table lock,  能读不能写

3. 创建临时表,执行table alteration并将所有数据复制到临时表中

4. 抛弃旧表并把新表改名,完成操作; 在这个点上会对表施加exclusive table lock, 也就是说连读都不行了。

如果表很大,这个过程会导致比较严重的不可用(分钟级或以上). 

在5.6之前,有一个针对innodb index的优化:MySQL 5.5, and MySQL 5.1 with the InnoDB Plugin, optimized CREATE INDEX and DROP INDEX to avoid the table-copying behavior

到5.6时,innodb上了一个特性叫做"online ddl", 对于很多alter table操作,可以避免表复制、或/和  dml blocking等。 具体避免哪些,要看具体使用的是哪种alter table操作。请参见:

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html . 对于最常见的"add column"操作,虽然不会block dml, 但仍存在数据复制,代价依然不低。

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.