【华为云MySQL技术专栏】MySQL Online DDL机制解析

3.4 提交阶段

|->wait_while_table_is_used
|   |->thd->mdl_context.upgrade_shared_lock(MDL_EXCLUSIVE) //进入提交阶段前，MDL锁升级为<TABLE,MDL_EXCLUSIVE>锁
|->THD_STAGE_INFO(thd, stage_alter_inplace_commit)//进入提交阶段
|->handler::ha_commit_inplace_alter_table
|    |->ha_innobase::commit_inplace_alter_table
|         |->ha_innobase::commit_inplace_alter_table_impl
|         |->rollback_inplace_alter_table // 回滚alter ddl操作(若执行失败或用户手动终止)
|         |->ddl::lock_table(LOCK_X) //获取排他表锁
|         |->row_mysql_lock_data_dictionary // 获取数据字典排他锁
|         |->commit_try_rebuild      //将准备阶段和执行阶段所做的修改写入数据字典（需要重建表时执行）
|            |-> row_log_table_apply   //第二次回放：回放第一次回放row log过程中记录的row log
|         |->commit_try_norebuild   //将准备阶段和执行阶段所做的修改写入数据字典（无需重建表时执行）
|         |->innobase_online_rebuild_log_free  //清理row log
|         |->row_mysql_unlock_data_dictionary(trx)  //释放数据字典排他锁
|    |->dd_commit_inplace_no_change   //提交数据字典的变更（无需重建表时执行）
|          |->dd_copy_private(Table &new_table, const Table &old_table) //将innod的私有数据（se_private_data）从旧的dd::Table拷贝到新的dd::Table
|    |->dd_commit_inplace_alter_table   //提交数据字典的变更（需要重建表时执行)

若选择INPLACE算法，在提交阶段主要会做如下事情：

• 若执行失败或用户手动终止，则回滚DDL操作。  

• 获取排他表锁。

• 获取数据字典排他锁。

• 将准备阶段和执行阶段所做的修改写入数据字典。

• 第二次回放row log。(从第一次回放row log开始，到获取排他表锁之间写入的row log)

• 回放完后清理row log。
• 释放数据字典排他锁。
• 提交数据字典的变更，将innodb的私有数据（se_private_data）从旧的dd::Table拷贝到新的dd::Table中。

 一共有2次锁升级，1次锁降级，整个过程的MDL锁状态演变过程如下：

• 初始化阶段，打开表时获取<TABLE,MDL_SHARED_UPGRADABLE>，允许读写。

• 进入准备阶段前，将<TABLE,MDL_SHARED_UPGRADABLE>升级为<TABLE,MDL_EXCLUSIVE>，阻塞读写。

• 进入执行阶段前，若手动设置了LOCK=SHARED或DDL操作为添加FULLTEXT/SPATIAL索引，则<TABLE,MDL_EXCLUSIVE>降级为<TABLE,MDL_SHARED_NO_WRITE>（可读不可写），否则降级为<TABLE,MDL_SHARED_UPGRADABLE>（允许读写）。

• 进入提交阶段前，MDL锁再次升级为<TABLE,MDL_EXCLUSIVE>，阻塞读写。

• 执行结束，释放MDL锁。

4、第三方Online DDL解决方案

目前，第三方Online DDL解决方案也有较为广泛的使用，尤其是一些只能用官方的COPY算法的DDL操作，为了避免长时间锁表，采用第三方Online DDL解决方案也是一个不错的选择。下面介绍其中比较流行的两款工具：gh-ost和pt-osc。 

gh-ost 是Github维护的一款开源工具，专为MySQL数据库设计，用于执行Online DDL操作，避免长时间锁表，从而减少对业务的影响。

gh-ost执行Online DDL的实现流程大致分为四个阶段：环境检查与初始化、同步数据、原子切换表名、清理资源。

1.环境检查与初始化

• gh-ost首先会检查数据库连接权限，数据库、表、唯一键是否存在，binlog已开启且格式必须为row等。

• 创建心跳表，记录gh-ost执行日志。

• 创建与原表结构一致的影子表。

• 在影子表上执行用户下发的DDL语句。 

2. 同步数据

2.1 存量数据同步

通过如下SQL将原表数据同步到影子表中。

INSERT /* gh-ost `databaseName`.`原表` */  IGNORE INTO `databaseName`.`影子表`(id,col1,col2,.... ) 
( SELECT  `id`, `col1`, `col2`, ... FROM `databaseName`.`原表` FORCE INDEX (PRIMARY) 
WHERE (rangeStartComparison and rangeEndComparison) LOCK IN SHARE MODE;)

gh-ost 通过主键将原表数据划分为多个chunk(由参数--chunk-size设置，默认1000行)，分批插入到影子表中。

2.2. 增量数据同步

存量数据同步期间，gh-ost允许对原表执行并发DML操作，并发DML操作产生的增量数据的同步，gh-ost通过消费DML操作产生的binlog并在影子表上重放来实现。

2.3. 存量数据同步和增量数据同步的协同

gh-ost采用单线程同时进行存量数据和增量数据的同步，进入数据同步阶段后：

• 优先消费binlog：优先消费并发DML操作产生的binlog做增量数据同步。

• 同步存量数据：当 binlog 暂无事件时，按照 chunk 同步原表的存量数据到影子表。

• 消费同步期间的binlog：在同步存量数据期间，可能产生新的 binlog事件，因此之后立即消费这些事件。

• 循环：重复上述过程，直到所有存量数据同步完成。

3. 原子切换表名

• 锁原表：增量数据和存量数据均同步完成后，gh-ost会锁住原表以阻止新的 DML 操作，直到原子切换表名完成之后释放锁。

• 插入靶点事件：往心跳表（gh-ost创建）插入记录，产生一个 binlog 事件（靶点）。

• 增量数据同步完成：继续消费 binlog 直到看到靶点事件，由于靶点事件是锁原表之后产生的，所以靶点事件一定在所有DML操作的binlog事件之后，于是在gh-ost看到靶点时，便可确认增量数据也同步完成。

• 原子切换：通过SQL：RENAME TABLE `原表` TO `临时表`,`影子表` TO `原表`; 原子性切换表名，原子性操作，要么全部成功，要么全部失败。如果失败，原表仍存在，不影响业务，影子表会残留，需要手动清理。

4. 清理资源

删除临时表、心跳表等资源（也可选择保留），迁移完成。

gh-ost原理流程图

4.2 pt-osc

pt-osc是Percona Toolkit中的一款开源工具，专为MySQL数据库设计，用于执行Online DDL操作，避免长时间锁表，从而减少对业务的影响。

pt-osc执行Online DDL的流程大致分为四个阶段：环境检查与初始化、同步数据、原子切换表名、清理资源。

1. 环境检查与初始化

• pt-osc首先会检查数据库连接权限，数据库、表、唯一键是否存在等。

• 创建与原表结构一致的影子表，在影子表上执行用户下发的DDL语句。

• 创建触发器：在原表上创建三个AFTER类型的触发器，分别捕获并发的INSERT、UPDATE、DELETE操作。

2.数据同步

2.1 存量数据同步

通过下面的SQL将原表数据同步到影子表中：

INSERT LOW_PRIORITY IGNORE INTO `databaseName`.`影子表` (`id`, `col1`,.....) SELECT `id`, `a` 
FROM  `databaseName`.`原表` LOCK IN SHARE MODE /*pt-online-schema-change xxx copy table*/

pt-osc通过主键/唯一键将原表数据划分为多个chunk(由参数--chunk-size设置，默认1000行)，分批插入到影子表中。 

2.2 增量数据同步

从触发器创建成功开始，触发器会实时将作用于原表上的并发DML操作产生的增量数据应用到影子表，确保数据一致性。

2.3 存量数据同步和增量数据同步的协同

存量数据和增量数据分别在不同的线程中进行同步，所以存在行锁竞争，建议在业务低峰期执行，如果并发DML操作还是很频繁的话，建议调小--chunk-size。

3. 原子切换表名

存量数据和增量数据均同步完成后，通过SQL：RENAME TABLE `原表` TO `临时表`,`影子表` TO `原表`; 原子性切换表名，原子性操作，要么全部成功，要么全部失败。

4. 清理阶段

删除临时表和三个触发器等，释放资源。

pt-osc原理流程图

5、MySQL Online DDL vs gh-ost vs pt-osc

下面对MySQL Online DDL(8.0.41)、gh-ost以及pt-osc三者进行简单对比。

6、现网运维经验

最后，总结一些关于DDL的运维经验，希望对大家有帮助。

阶段一：事前准备。（最重要！）

1）全面评估。

• 操作内容：明确要执行的DDL语句是否支持INSTANT或INPLACE。

• 表大小：准确评估表行数和表空间大小。

• 业务影响：评估操作耗时（测试环境验证）以及对业务峰值时段的影响。

• 磁盘空间：确保实例有足够的空闲磁盘空间，以容纳临时日志文件、临时中间表和排序数据文件等（INSTANT算法除外）。 

• 确保没有其他事务持有原表的MDL锁：长时间未提交的事务（即使只是一个简单的查询）是导致DDL操作被阻塞的最常见原因，被阻塞的DDL操作本身又会成为新的阻塞源，阻塞后续所有访问该表的事务，对业务造成严重影响。所以建议在执行DDL之前，使用show full processlist、information_schema.innodb_trx表、performance_schema.metadata_locks表、information_schema.metadata_lock_info表（仅华为云的TaurusDB和RDS for MySQL）来监控和诊断元数据锁问题，确保没有其他事务持有原表的MDL锁。

2）选择时机：必须在业务低峰期进行。

3）提前备份：确保操作失败可及时回退。

4）在从库演练：在相同规格的从库（或测试环境），用完整的生产数据副本进行全流程演练。可以准确估算执行时间，检查是否有潜在错误（如数据类型不兼容、重复键等），观察对服务器性能（IO、CPU、内存）的影响程度等。

5）合理设置工具的参数。

• pt-osc：合理设置--max-load、--max-lag、--critical-load、--chunk-size 等参数。

• gh-ost：合理设置--max-load、--critical-load--cut-over-lock-timeout-seconds、--max-lag-millis、--dml-batch-size等参数。

阶段二：执行过程中实时监控，监控到异常情况可适时终止DDL操作，并进行残余数据的清理。

• 数据库监控：密切关注数据库的 Threads_running、Threads_connected、CPU使用率、IOPS、网络流量和主从延迟。

• ddl进度监控：pt-osc 和 gh-ost都会输出详细的进度信息，包括当前拷贝了多少行、预计剩余时间等，TaurusDB提供了INFORMATION_SCHEMA.INNODB_ALTER_TABLE_PROGRESS查询进度信息。

• 业务监控：关注业务系统的错误日志、响应时间和QPS是否有异常波动。

阶段三：事后验证。

• 验证表结构：操作完成后，立即检查表结构是否正确变更。

• 数据校验：对于修改数据的操作（如MODIFY COLUMN），抽样检查一些核心数据是否正确。

• 观察业务：操作结束后，不要立即离开，持续观察业务监控，确保没有隐形问题（如因为新索引导致某些查询执行计划变化）。

• 清理：如果使用第三方工具，确认临时表和心跳表等已被成功删除，释放磁盘空间。

遵循以上建议，可以极大降低DDL操作的风险，保障数据库的稳定运行。

7、总结

本文主要从原理和源码角度分别介绍了MySQL官方的Online DDL，pt-osc和gh-ost等工具的实现机制，并对三者的机制进行对比。希望可以帮助大家更好的理解目前主流的MySQL Online DDL的解决方案，从而可以更安全、更高效的在MySQL上执行DDL操作。