浅谈MySQL Online DDL

Online DDL是什么

1. 在MySQL5.5以及之前的版本，通常更改数据表结构操作(DDL)会阻塞对表数据的增删改操作(DML)

2. MySQL5.6提供Online DDL之后可支持DDL与DML操作同时执行，也就是降低了DDL期间对业务延迟带来的影响

Online DDL特点

1. DDL操作可与应用的DML操作并发执行，改进在繁忙生产环境的响应和可用性，因为对于这种业务系统几分钟或几小时不可用是难以忍受的
2. 可以通过调整DDL的lock模式来平衡与DML操作的性能问题
3. Online DDL使用的是In-place方式，相较于table-copy方式能更少的使用I/O资源，在DDL期间也能有较高的吞吐量
4. In-place相较于table-copy还有一个优点是：table-copy读取数据多，频繁的使用buffer pool导致有效缓存数据被调出，影响缓存击中率 降低效率

Online DDL原理

Metadata lock(MDL)

1. MDL 是一种表锁，不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。
2. 简单的理解就是用线程在访问一张表的数据(查询或者DML操作)时，是不允许其他线程修改此表的结构的，这个限制就是通过MDL来实现的
3. 对于表数据的操作(查询or DML)获取的是MDL读锁
4. 对于表结构的修改操作会有MDL写锁和读锁，主要是为了并发高效性和数据一致性，会有锁的降级和升级过程

Online DDL执行阶段

大致可分为三个阶段：初始化、执行和提交

Initialization阶段

1. 此阶段会使用MDL读锁，禁止其他并发线程修改表结构
2. 服务器将考虑存储引擎能力、语句中指定的操作以及用户指定的ALGORITHM 和 LOCK选项，确定操作期间允许的并发数

Execution阶段

1. 此阶段分为两个步骤 Prepared and Executed
2. 此阶段是否需要MDL写锁取决于Initialization阶段评估的因素，如果需要MDL写锁的话，仅在Prepared过程会短暂的使用MDL写锁
3. 其中最耗时的是Excuted过程

Commit Table Definition阶段

1. 此阶段会将MDL读锁升级到MDL写锁，此阶段一般较快，因此独占锁的时间也较短
2. 用新的表定义替换旧的表定义(如果rebuild table)

Online DDL 过程

1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁

1、2、4、5如果没有锁冲突，执行时间非常短。第3步占用了DDL绝大部分时间，这期间这个表可以正常读写数据，是因此称为“online ”

INPLACE or COPY究竟什么区别

1. ALGORITHM=COPY是MySQL5.5以及之前的方式
2. ALGORITHM=INPLACE是MySQL5.6引入的方式
3. COPY算法，由service层创建一个临时表用于copy数据，然后用新表替换旧表
4. INPLACE算法，“原位替换” 其实主要是指在InnoDB内部完成的DDL操作，在InnoDB内部创建临时文件。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。
5. 因此对于INPLACE其实分为非重建表和重建表两类方式，非重建表方式直接在原表基础上更新，效率最高；重建表同样需要copy数据(比如新增字段) 详情请参考mysql5.7

Inplace 与 Online 的关系

1. DDL 过程如果是 Online 的，就一定是 inplace 的；
2. 反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。

Online DDL执行例子

1. MDL写锁是一个比较昂贵的操作，因为不允许其他操作再进行读、写操作，因此Online DDL只会在某些关键时刻获取MDL写锁，其他时刻都降级为读锁
2. 如果另外一个session B的大事物获取了读锁(可以执行DML操作)，那么此时session A的DDL操作想要获取写锁就必须等到session B的事物提交或者回滚才能获取到，这个过程中session A的ddl可能会超时失败

例子：
session 1:

mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
mysql> START TRANSACTION;
mysql> SELECT * FROM t1;

session 1将会获取表t1的MDL读锁

session 2:

mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;

session 2是Online DDL操作，需要获取表t1的MDL写锁去提交表定义修改，会一直阻塞到session 事物提交或者回滚

session 3:

mysql> SELECT * FROM t1;

session 3会阻塞到session 2的写锁请求完成并释放后方可执行

通过SHOW FULL PROCESSLIST查看：

mysql> SHOW FULL PROCESSLIST\G
...
*************************** 2. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 44
  State: Waiting for table metadata lock
   Info: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
*************************** 4. row ***************************
     Id: 7
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 5
  State: Waiting for table metadata lock
   Info: SELECT * FROM t1
4 rows in set (0.00 sec)

Online DDL性能

1. DDL操作的性能很大程度上取决于该操作是否INPLACE执行以及它是否重新构建表。
2. 要评估DDL操作的相对性能，可以使用ALGORITHM=INPLACE和ALGORITHM=COPY比较结果
3. 对于修改表数据的DDL操作，可以通过查看命令结束后显示的“受影响的行”值来确定DDL操作是IN-PLACE，还是执行Table-COPY

例子

先创建表，添加100w行数据

create table t1(id int primary key, a int, b int, index(a));
drop procedure idata;
delimiter ;;
create procedure idata()
begin
    declare i int;
    set i=1;
while(i <= 1000000)do
    insert into t1 values(i, 1000001 - i, i);
    set i = i+1;
    end while;
end;;
delimiter ;
call idata();

例1：新增一个字段，即使是ALGORITHM=INPLACE也会重新建表，输出结果0行受影响，可推断是INPLACE

mysql> ALTER TABLE t1 ADD COLUMN c int, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (1.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

例2: 改变字段类型，使用ALGORITHM=INPLACE抛出异常；这种操作只能使用ALGORITHM=COPY，输出结果1000000受影响，可推断是COPY

mysql> ALTER TABLE t1 CHANGE c c BIGINT, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql> 
mysql> ALTER TABLE t1 CHANGE c c BIGINT, ALGORITHM=COPY;
Query OK, 1000000 rows affected (4.99 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

具体详细规则请参考mysql5.7

总结

• Online DDL 的方式是可以考虑在业务低峰期使用的，而 MySQL 5.5 及之前的版本，这个命令是会阻塞 DML 的，需要特别小心。
• Online DDL 使用的是INPLACE算法，有rebuild table 和 no-rebuild table两种方式
• INPLACE的rebuild table方式和COPY的rebuild table方式类似，都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，要很小心地控制操作时间。如果想要比较安全的操作的话，可以使用 GitHub 开源的 gh-ost 来做。
• INPLACE的rebuild table方式和COPY的rebuild table方式一个不同点在于 INPLACE的rebuild仍然可以达到与DML操作并发执行，从而达到"online"的状态
• Online DDL过程会使用到MDL的读锁和写锁

参考文档：

• mysql5.7
• 极客时间·MySQL实战45讲·作者 林晓斌(网名丁奇)