MySQL 实战笔记 第03期：MySQL Online DDL 三阶段初探

作者简介

无为，多年 MySQL DBA 工作经验，现就职于某知名互联网公司，对 MySQL、 Redis、PostgrepSQL 等主流数据库有一定了解，拥有丰富的一线运维经验。

上一节咱们了解了元数据锁，但在 Online DDL 操作中具体是怎样加锁的呢？加几次锁呢？带着这些疑问，我们一起来学习 DDL 三阶段。

1 官方文档

阶段1：初始化

在初始化阶段，服务器将考虑存储引擎功能，语句中指定的操作以及用户指定的 ALGORITHM 和 LOCK 选项，以确定在操作期间允许多少并发 。在此阶段，将使用共享的元数据锁来保护当前表定义。

阶段2：执行

在此阶段，准备并执行该语句。元数据锁是否升级到排它锁取决于初始化阶段评估的因素。如果需要排他元数据锁，则仅在语句准备期间进行短暂锁定。

阶段3：提交

在提交表定义阶段，将元数据锁升级为排它锁，以退出旧表定义并提交新表定义，在获取排它锁的过程中，如果其他事务正在占有元数据的排它锁，那么本事务的提交操作可能会出现锁等待。

2 ALGORITHM 选择

我们先看下 ALTER TABLE 时 ALGORITHM 可以指定的几种方式：

• COPY ，是指 DDL 时，会生成（临时）新表，将原表数据逐行拷贝到新表中，在此期间会阻塞 DML；
• INPLACE，无需拷贝全表数据到新表，但可能还是需要 IN-PLACE 方式（原地，无需生成新的临时表）重建整表。这种情况下，在 DDL 的初始准备和最后结束两个阶段时通常需要加排他 MDL 锁（metadata lock，元数据锁），除此外，DDL 期间不会阻塞 DML；
• INSTANT，只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他MDL锁，原表数据也不受影响。整个DDL过程几乎是瞬间完成的，也不会阻塞DML。这个新特性是8.0.12引入。

3 各阶段详解

初始化

1. 创建新的临时 .frm 文件；
2. 持有排它元数据锁，禁止读写；
3. 根据 DDL 类型确定执行方式；
4. 更新数据字典；
5. 分配临时日志文件准备记录 DDL 过程中的 DML 增量，日志文件大小由 innodb_online_alter_log_max_size 参数控制，设置太小可能会导致某些情况下 DDL 失败；
6. 分配新的 ibd 文件（按需创建）。

执行

1. 降级 EXCLUSIVE-MDL 锁，允许读写；
2. 扫描 old_table 的聚集索引每一条记录 rec；
3. 遍历新表的聚集索引和二级索引，逐一处理；
4. 根据 rec 构造对应的索引项；
5. 将构造索引项插入 sort_buffer 块；
6. 将 sort_buffer 块插入新的索引 #顺序插入不做 insert buffer 写；
7. 处理 DDL 执行过程中产生的增量( 仅 rebuild 类型需要)。

提交

1. 升级到 EXCLUSIVE-MDL 锁，禁止读写；
2. 重做最后 row_log 中最后一部分增量；
3. 更新 innodb 的数据字典表；
4. 提交事务(刷事务的 redo 日志)；
5. 修改统计信息；
6. rename 临时 idb 文件，frm 文件；
7. 变更完成。

4 Online DDL 限制

• 在 alter table 时，如果涉及到 table copy 操作，要确保 datadir 目录有足够的磁盘空间，能够放的下整张表，因为拷贝表的的操作是直接在数据目录下进行的。
• 添加索引无需 table copy，但要确保 tmpdir 目录足够存下索引一列的数据（如果是组合索引，当前临时排序文件一合并到原表上就会删除）。
• 在主从环境下，主库执行 alter 命令在完成之前是不会进入 binlog 记录事件，如果允许 dml 操作则不影响记录时间，所以期间不会导致延迟。然而，由于从库是单个 SQL Thread 按顺序应用 relay log ，轮到 ALTER 语句时直到执行完才能下一条，所以从库会在master ddl 完成后开始产生延迟。（ pt-osc 可以控制延迟时间，所以这种场景下它更合适）。
• 在执行一个允许并发 DML 在线 ALTER TABLE 时，结束之前这个线程会应用 row_log 记录的增量修改，而这些修改是其它 thread 里产生的，所以有可能会遇到重复键值错误 ( ERROR 1062 (23000): Duplicate entry )。
• 涉及到 table copy 时，目前还没有机制限制暂停 ddl，或者限制 IO 阀值，在 MySQL 5.7.6 开始能够通过 performance_schema 观察 alter table 的进度。
• 一般来说，建议把多个 alter 语句合并在一起进行，避免多次 table rebuild 带来的消耗。但是也要注意分组，比如需要 copy table 和只需 inplace 就能完成的，应该分两个 alter 语句。
• 如果 DDL 执行时间很长，期间又产生了大量的 dml 操作，以至于超过了 innodb_online_alter_log_max_size 变量所指定的大小，会引起 DB_ONLINE_LOG_TOO_BIG 错误。默认为 128M，特别对于需要拷贝大表的 alter 操作，考虑临时加大该值，以此获得更大的日志缓存空间。
• 执行完 ALTER TABLE 之后，最好 ANALYZE TABLE tb1 去更新索引统计信息。