MySQL5.7DDL与GH-OST对比分析

王新法

MySQL 5.7 DDL 与 GH-OST 对比分析 存储研发团队 vivo互联网技术 vivo互联网技术 维沃移动通信有限公司 分享 vivo 互联网技术干货与沙龙活动，推荐最新行业动态与热门会议。 436篇内容 2024年08月07日 20:00 广东 作者：来自 vivo 互联网存储研发团队- Xia Qianyong本文首先介绍MySQL 5.7 DDL以及GH-OST的原理，然后从效率、空间占用、锁阻塞、binlog日志产生量、主备延时等方面，对比GH-OST和MySQL5.7 DDL的差异。一、背景介绍在 MySQL 数据库中，DDL（数据定义语言）操作包括对表结构、索引、触发器等进行修改、创建和删除等操作。由于 MySQL 自带的 DDL 操作可能会阻塞 DML（数据操作语言）写语句的执行，大表变更容易产生主备延时，DDL 变更的速度也不能控制，因此在进行表结构变更时需要非常谨慎。为了解决这个问题，可以使用 GitHub 开源的工具 GH-OST。GH-OST 是一个可靠的在线表结构变更工具，可以实现零宕机、低延迟、自动化、可撤销的表结构变更。相比于 MySQL 自带的 DDL 操作，GH-OST 可以在不影响正常业务运行的情况下进行表结构变更，避免了 DDL 操作可能带来的风险和影响。通过使用 GH-OST工具，可以对 MySQL 数据库中的表进行在线结构变更，而不会对业务造成太大的影响。同时，GH-OST 工具还提供了多种高级特性，如安全性检测、自动化流程等，可以帮助用户更加高效地进行表结构变更。二、MySQL5.7几种DDL介绍2.1 copyserver层触发创建临时表server层对源表加MDL锁,阻塞DML写、不阻塞DML读server层从源表中逐行读取数据，写入到临时表数据拷贝完成后,升级字典锁，禁止读写删除源表，把临时表重命名为源表MySQL copy方式的DDL变更，数据表的重建(主键、二级索引重建)，server层作为中转把从innodb读取数据表，在把数据写到innodb层临时表。简单示意图如下：2.2 inplace（1）rebuild table需要根据DDL语句创建新的表结构，根据源表的数据和变更期间增量日志，重建新表的主键索引和所有的二级索引。Prepare阶段：创建新的临时frm文件持有EXCLUSIVE-MDL锁，禁止读写根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)假如是Add Index，则选择online-norebuild更新数据字典的内存对象分配row_log对象记录增量生成新的临时ibd文件ddl执行阶段 :降级EXCLUSIVE-MDL锁，允许读写扫描old_table的聚集索引每一条记录rec遍历新表的聚集索引和二级索引，逐一处理各个索引根据rec构造对应的索引项将构造索引项插入sort_buffer块排序将sort_buffer块更新到新表的索引上记录ddl执行过程中产生的增量(记录主键和索引字段)重放row_log中的操作到新表索引商重放row_log间产生dml操作append到row_log最后一个Blockcommit阶段 :当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL锁重做row_log中最后一部分增量更新innodb的数据字典表rename临时idb文件，frm文件增量完成MySQL rebuild table方式的DDL，数据不需要通过sever层中转，innodb层自己完成数据表的重建。简单示意图如下：（2）build-index需要根据DDL语句创建新的表结构，根据源表的数据和变更期间增量日志，创建新的索引。Prepare阶段:持有EXCLUSIVE-MDL锁，禁止读写根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)假如是Add Index，则选择online-norebuild更新数据字典的内存对象分配row_log对象记录增量ddl执行阶段 :降级EXCLUSIVE-MDL锁，允许读写扫描old_table的聚集索引每一条记录rec遍历新表的聚集索引,根据rec构造新的索引数据将构造索引项插入sort_buffer块排序将sort_buffer块更新到新表的索引上记录ddl执行过程中产生的增量(仅记录主键和新索引字段)重放row_log中的操作到新表索引上重放row_log间产生dml操作append到row_log最后一个Blockcommit阶段 :当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL锁重做row_log中最后一部分增量更新innodb的数据字典表增量完成MySQL rebuild index方式的DDL，数据不需要通过sever层中转，innodb层只需要完成变更二级索引的创建。简单示意图如下：（3）only modify metadata只修改元数据(.frm文件和数据字典)，不需要拷贝表的数据。三、GH-OST在GH-OST端，根据DDL语句创建新的表结构，根据源表的数据和增量期间增量日志，重建新表的主键索引和所有的二级索引，最终完成DDL增量。主要流程如下：根据DDL语句和源表创建新的表结构根据唯一索引(主键索引或者其它唯一索引)- 优先应用新增量的binlog到新的表中，需要经过GH-OST把binlog日志转换为sql，然后回放到影子表- 其次拷贝源表中的数据到新的表中，表数据拷贝通过sql语句 insert ignore into (select .. from)直接在MySQL实例上执行，无需经过GH-OST中转数据拷贝完成并应用完binlog后，通过lock table write 锁住源表应用数据完成-获取到锁期间产生的增量binlogdelete源表，rename影子表为源表，完成数据增量GH-OST 进行DDL变更，GH-OST服务通知server层，server层作为中转把从innodb读取数据表，在把数据写到innodb层影子表。并且GH-OST作为中转读取DDL变更期间增量binlog解析成SQL写语句回放到影子表。简单示意图如下：四、对比分析DDL变更执行时长、对磁盘的额外占用(临时数据表+binlog)、锁阻塞时长、主备延时都是执行DDL变更人员比较关心的问题，本章将从从执行效率、占用表空间、锁阻塞、产生binlog日志量、主备延时等方面对MySQL原生的DDL和GH-OST进行对比分析。4.1 执行效率（1）only modify metadata(正常小于1S)（2）build-index: 数据条目越多、新索引字段越大耗时越多增量日志超过innodb_online_alter_log_max_size造成DDL失败（3）rebuild table: 数据条目越多、所有索引字段之和越大耗时越多增量日志超过innodb_online_alter_log_max_size造成DDL失败（4）copy：数据条目越多，所有索引字段之和越大耗时越多，相对于rebuild table，数据需要从server层中转，所以比rebuild table耗时多（5）GH-OST ：数据条目越多，所有索引字段之和越大耗时越多,相对于copy，增量日志数据需要从GH-OST中转，所以比copy耗时多有各种限流,(主备延时，threads超限延时…)，增加耗时增量期间应用binlog速度如果跟不上业务产生binlog日志的速度，将无法完成增量critical 参数还会导致主动退出，例如thread_running耗时：only modify metadata