MySQL DDL 执行方式

本篇文章主要是由于近期组内线上的 MySQL 变更，导致主从延迟，产生业务问题，而引发的思考，重新复习了一下 MySQL 锁机制，以及主从延迟和 DDL 语句的执行方式。

DDL 执行原理

Copy

在MySQL 5.6.7 版本之前，DDL 操作采用 Copy 方式执行:

1. 创建新的临时表，复制原表结构，并执行 DDL 语句（如新增表字段、索引）
2. 把原表中数据导入到临时表
3. 删除原表
4. 最后重命名临时表为原表名

COPY 操作由 Server 层实现，当采用 COPY 模式时，一旦 DDL 操作开始，原表仅能进行只读操作，其它 DML 会被堵塞。并且，在清理旧表结构和表定义缓存时还会阻塞只读操作。可以看出，5.6.7 之前的 MySQL 增添索引、增删字段都会加元数据排他锁，阻塞表的写操作。

Online DDL - In Place

MySQL 在 5.6.7 至 8.0.12 版本之间，使用 In Place 代替了 Copy 方式，减少了对业务的影响。In Place 主要是通过临时文件重建原表：

1. 新建临时文件，.frm和.ibd，临时文件的表结构包含新增的列
2. 扫描原表的所有数据页，基于原表生成新的 B+树结构，存储到临时文件中
3. 在重建临时文件时，原表DML 语句会记录到 日志文件中（row log）
4. 原表数据全部重建完成后，将日志文件（row log）应用到临时文件中。此时该日志文件相比原表增加了一列
5. 使用临时文件替换原表的数据文件和表结构文件

In Place 操作位于 InnoDB 引擎层，执行会申请 MDL 共享锁，阻止其它 DDL 语句，但不阻止 DML 语句，原表可并发写入数据。但在最后提交阶段，会短暂申请 MDL 排他锁，阻止 DML 语句。

In Place 存在的问题：

1. 主从延迟：长时间运行的 DDL 操作可能会导致主从复制滞后。DDL 操作必须在主服务器上完成，然后才能在从服务器上执行。此外，主服务器上并发处理的 DML 仅在从服务器上的 DDL 操作完成后才会在从服务器上处理。
2. 无法对 DDL 限速
3. DDL 期间如果出现唯一键冲突，会导致 DDL 失败

Online DDL - Instant

MySQL 8.0.12 版本提供了 Instant 方式执行 DDL，并且在 8.0.29 得到了进一步的完善。Instant方式只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，整个DDL过程几乎是瞬间完成的，也不会阻塞DML，解决了上述 In Place 存在的问题。（由腾讯游戏 DBA 团队贡献的特性）

需要注意的是，并不是所有 DDL 语句都支持 Instant 方式，Instant 的实现是基于 MySQL 8.0 引入的数据字典实现的。一个原子DDL语句将与DDL操作相关的数据字典更新、存储引擎操作和二进制日志写入组合成一个单独的原子操作。该操作要么提交，将更改持久保存到数据字典、存储引擎和二进制日志中；要么回滚，即使在操作过程中服务器停机。