既然修改表结构的时候,MySQL会自动添加表锁,并且是写锁,会阻塞后续的所有读写请求,造成非常严重的后果。还有没有办法能优雅的给线上表添加字段呢?当然有,从MySQL5.6版本开始增加了Online DDL,作用就是在执行DDL的时候,允许并发执行DML。简单翻译就是修改表结构的时候,也能同时支持并发执行增删查改操作。从MySQL8.0版本开始又优化了Online DDL,支持快速添加列,可以实现给大表秒级加字段。具体用法就是在DDL语句后面增加两个参数ALGORITHM和LOCK。比如下面这样:

ALTER TABLE `user` ADD `age` int NOT NULL DEFAULT '0' COMMENT '年龄', 
ALGORITHM=Inplace, 
LOCK=NONE;

这两个参数分别是干嘛用的?有哪些选项呢?

ALGORITHM可以指定使用哪种算法执行DDL,可选项有:

  • Copy:拷贝方式,MySQL5.6 之前 DDL 的执行方式,过程就是先创建新表,修改新表结构,把旧表数据复制到新表,删除旧表,重命名新表。执行过程非常耗时,产生大量的磁盘IO和占用CPU,还有使Buffer poll失效,而且需要锁住旧表,性能较差,现在基本很少使用。
  • Inplace:原地修改,MySQL5.6开始引入的,优点是不会在Server层发生表数据拷贝,过程中允许并发执行DML操作。过程就是先添加MDL写锁,执行初始化操作,然后降级为MDL读锁,执行DDL操作(比较耗时,允许并发执行DML操作),升级为MDL写锁,完成DDL操作。
  • Instant:快速修改,MySQL8.0开始引入的,可以实现快速给大表添加字段。

性能依次是,Instant > Inplace > Copy。

LOCK可以指定执行过程中,是否加锁,可选项有:

  • NONE:不加锁,允许DML操作。
  • SHARED:加读锁,允许读操作,禁止DML操作。
  • DEFAULT:默认锁模式,在满足DDL操作前提下,默认锁模式会允许尽可能多的读操作和DML操作。
  • EXCLUSIVE:加写锁,禁止读操作和DML操作。

Online DDL并不是支持所有DDL操作,看一下到底支持哪些操作?
image.png

像最常见的添加列就可以使用Instant,而像删除列、重命名列、更改列数据类型就只能使用Inplace了。

Q.E.D.