mysql online ddl
所有数据库操作中DDL的锁粒度是最大的,通过包括元数据锁和表对象锁。常见的DDL包括alter,create,drop等,对于create,drop而言,通常执行过程很快,因此影响比较少,而对于alter操作,尤其是对大表的alter,这个过程可能持续时间很长,由于变更过程中,表对象的DML操作会阻塞,因此一个alter操作很有可能导致前台的网站应用出现大量的数据库访问超时情况。那么怎么解呢?第一种是alter操作不上锁,从而不影响写操作,若不行退而求其次,将alter操作的时间想办法缩短,减少不可访问表的时间。
对于mysql数据库而言,解决alter的问题也有一个过程,直到5.6才推出了online ddl功能。5.5版本通过FIC(fast index creation),提高了alter操作中加索引和删索引的速度,5.6的online ddl则优化更多,增加了更多的“在线”操作。在介绍FIC和online ddl的原理之前,我们先来看看有哪些常见的alter操作,参见表1
alter动作 |
说明 |
Add index,drop index |
增加、删除、修改二级索引 |
Add column,drop column |
增加、删除、修改列 |
Add primary key,drop primary key |
增加、删除、修改主键索引 |
Set character set utf8/gbk |
修改字符集、修改存储引擎 |
Optimize table |
重组表 |
表1
那么针对以上几种常见的场景,我们看到FIC和online ddl到底做了什么,它们实现的原理是怎样的,下文的分析都是基于innodb表。
对于一般的alter操作而言,它的原理基本是这样的,假设需要对A表做表结构变更,首先创建一个目的表结构的临时表B;其次是锁表,将数据从A表拷贝到B表;最后是将B表rename成A表,释放锁。
FIC针对加索引和删索引做了优化,因为这种情景下,innodb的表存储结构没有变,只是多了或少了索引,因此没有必要进行全表拷贝,直接增加或删除索引即可,这样就减少了拷贝表的时间,同时也减少了锁表时间。对于需要该表存储结构的alter操作,FIC则无能无力。由于mysql迟迟不出现Online ddl的版本,FIC的场景不通用,并且依然会阻塞写,业务不可接受。没有办法,很多时候做表结构变更需要在业务低峰期(凌晨),通过主备库切换的方式去做,真是苦了DBA的童鞋们。
还好,在mysql5.6出现之前,percona公司提供了“在线”表结构变更的工具pt-online-schema-change,这个工具给dba童鞋们带来了福音。工具的核心原理是通过insert… select…语句进行一次全量拷贝,通过触发器记录表结构变更过程中产生的增量,从而达到表结构变更的目的。假设对A表进行变更,主要步骤如下:
- 创建目的表结构的空表,A_gst;
- 在A表上创建触发器,包括增、删、改触发器;
- 通过insert…select…limit N 语句分片拷贝数据到目的表
- Copy完成后,将A_gst表rename到A表
通过这个方式后,执行alter操作时,不再阻塞读和写,而且支持的alter语句也更广泛,比如表1列出来的几种情况都可以支持,除了Optimize table以外。
Mysql online ddl的原理实质与pt-online-schema-change工具原理相同,只不过将这一过程封装在mysql内部了。虽然如此,这种方式也有一定的弊端和限制,比如需要有主键,拷贝表速度不如源生锁表拷贝表快等。
最后,举一个例子说明alter操作在5.5和5.6对于DML的影响。从表2可以看到,5.5和5.6中,查询和更新都会阻塞alter操作;若有alter操作,5.5版本中,alter不会阻塞读,但会阻塞写;5.6版本中,alter不会阻塞读和写。
时间点 |
会话A(5.6) |
会话A(5.5) |
会话B |
会话C |
1 |
set autocommit=0; update t set c2='9999' where c1=4; |
set autocommit=0; update t set c2='9999' where c1=4; |
||
2 |
alter table t drop column c3; |
|||
3 |
Show processlist; B:Waiting for table metadata lock |
|||
4 |
A:提交事务 commit |
|||
5 |
Show processlist; B:copy to tmp table |
|||
6 |
B:继续执行 |
|||
7 |
Select count(*) from t; 正常执行 |
Select count(*) from t; 正常执行 |
||
8 |
update t set c2='9999' where c1=4; 正常执行 |
update t set c2='9999' where c1=4;阻塞 |
||
8 |
Show processlist; A(5.5): Waiting for table metadata lock B: copy to tmp table |
|||
9 |
B执行完毕 |
|||
10 |
A执行完毕 |
表2