MySQL5.6 在线DDL更改表测试

zhouerla

先在这里普及下基础知识：

在之前的版本，在线更改表，比如增加一个字段，其内部原理是这样的：

1、create table tmp like t1（创建一个临时表）

2、insert into tmp select * from t1（一行行的把原表数据拷贝到临时表里，且更新索引）

3、drop table t1;rename table tmp to t1（删除原表并把临时表改名为原表t1）

在这个过程中会对t1表加S锁（共享锁），所以这个代价是很高的。

而在MySQL5.6里，对其进行了优化，当会话一增加字段时，其他会话增，删，改，查，均不受影响，不会锁表。

比如这样测试，用Sysbench生成一张1000万行的数据，数据大一些，可以看出效果来。

• sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=10000000 --max-requests=100 --num-threads=16
• --mysql-host=192.168.110.140 --mysql-port=3306 --mysql-user=admin --mysql-password=123456 --mysql-db=test
• --mysql-socket=/tmp/mysql.sock prepare
  

然后在会话一，执行：

• alter table sbtest add name varchar(10) after pad;
  

会话二，执行：

• delete from sbtest where id=1000;
  
  

• insert into sbtest values(1000,1,'abc','abc','abc');  
  
  

• update sbtest set k=11 where id=1000;
  

这时你会发现并没有锁表，顺利执行完毕。

但在MySQL5.5里，这样的操作是会锁表的，如图所示：

那么MySQL5.6 在线DDL就一定不锁表吗？我们再这样测试：

在会话一：

• select * from sbtest;
  

故意执行一条大结果的查询，然后再执行删除刚才增加的字段name

• alter table sbtest drop name;
  

这时就会把表给锁了，如图所示：

也就是说，在执行alter table表时，对该表的增、删、改、查均不会锁表。而在这之前，该表有被访问时，需要等其执行完毕后，才可以执行alter table。

所以在凌晨上线时，一定要观察下，此时此刻，是否有某个慢SQL对该表进行操作，以免改表时出现锁等待现象。

参考手册：

xxggmmxx

所有的男人生来平等，结婚的除外。

zz22

死亡教会人一切，如同考试之后公布的结果——虽然恍然大悟，但为时晚矣~！

yp1234

吃饭与ml是第一生产力。

cheun

死亡教会人一切，如同考试之后公布的结果——虽然恍然大悟，但为时晚矣~！

zyh3033

沒有激情的亲吻，哪來床上的翻滾？

Izhuceul

昨天，系花对我笑了一下，乐得我晚上直数羊，一只羊，两只羊，三只羊……