MySQL 5.7 Online DDL 说明 -- cnDBA.cn

1 背景说明

MySQL 数据库对大表DDL变更（大于10G 以上的）比较头疼，这类操作时间久，对性能影响大，某些 DDL 会锁表，影响业务可持续性。

MySQL 对部分DDL操作是支持在线操作的，有些DDL则不行，关于这些操作的类型，MySQL 官网有说明。

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

在MySQL 5.5 以上的版本，对于这些不能在线操作的DDL，可以使用第三方的工具，可以避免锁带来的影响：

Percona 公司开源的 pt-osc 工具解决导致锁表的操作，
github 基于 go 语言开发的 gh-ost。

2 MySQL DDL 的方法

MySQL 自带三种DDL方法：copy、inplace、instant。

copy 算法为最古老的算法，在 MySQL 5.5 及以下为默认算法。
从 MySQL 5.6 开始，引入了 inplace 算法并且默认使用。inplace 算法还包含两种类型：rebuild-table 和 not-rebuild-table。MySQL 使用 inplace 算法时，会自动判断，能使用 not-rebuild-table 的情况下会尽量使用，不能的时候才会使用 rebuild-table。当 DDL 涉及到主键和全文索引相关的操作时，无法使用 not-rebuild-table，必须使用 rebuild-table。其他情况下都会使用 not-rebuild-table。
从 MySQL 8.0.12 开始，引入了 instant 算法并且默认使用。目前 instant 算法只支持增加列等少量 DDL 类型的操作，其他类型仍然会默认使用 inplace。

方法	copy	inplace not-rebuild-table	inplace rebuild-table	instant	pt-osc	gh-ost
DDL 过程中读取数据	允许	允许	允许	允许	允许	允许
DDL 过程中写入数据	不允许	允许	允许	允许	允许	允许
需要 MDL	需要	需要	需要	需要	需要	需要
需要额外空间	大	小	中	小	大	大
执行时间	非常长	短	非常长	非常短	长	长
IO 负载	大	小	大	非常小	非常大	大
导致主从同步延时	非常大	大	大	非常小	小	小
其他						支持临时暂停

一般情况下的建议：

如果使用的是 MySQL 5.5 或者 MySQL 5.6，推荐使用 gh-ost
如果使用的是 MySQL 5.7，索引等不涉及修改数据的操作，建议使用默认的 inplace 算法。如果涉及到修改数据（例如增加列），不关心主从同步延时的情况下使用默认的 inplace 算法，关心主从同步延时的情况下使用 gh-ost
如果使用的是 MySQL 8.0，推荐使用 MySQL 默认的算法设置，在语句不支持 instant 算法并且在意主从同步延时的情况下使用 gh-ost。

3 MySQL DDL 的注意事项

MySQL 在大型表上的 DDL 会带来耗时较久、负载较高、额外空间占用、MDL、主从同步延时等情况。需要特别引起重视。

3.1 DDL 的所需时间

DDL 的执行时间，和很多因素相关，如果需要比较精确的时间预估，建议在测试环境提前做测试。

可以新建一个测试实例，将备份数据导出到测试实例，执行 DDL 操作，判断执行时间，作为对线上执行的一个估计。但是请注意，该估计仍然可能不准确，因为线上实例的负载可能会比测试实例高。

如果使用的是 gh-ost，工具会反馈执行进度。如果使用的是 MySQL 自带的 DDL，MySQL 5.7 可以开启 DDL 监控，使用以下语句查看 DDL 执行进度：

select event_name, work_completed, work_estimated from performance_schema.events_stages_current;
http://www.cndba.cn/cndba/dave/article/131460

MySQL 自带的监控也是估算值，因此进可作参考。

3.2 负载

所有方式对大表做 DDL 都会增加负载，只是程度的不同，主要为 IO 的负载。如果是 IO 使用非常高的实例，建议在 IO 较小的时间段执行 DDL 操作。

3.3 额外空间占用

copy、inplace rebuild-table、gh-ost、pt-online-schema-change，都会将表完整复制一份出来再做 DDL 变更，因此会使用和原表空间一样大（甚至更大，如果是加列的操作的话）的额外空间，另外还会生成大量的临时日志。要特别注意剩余空间，确保空间充裕，不然可能导致 DDL 过程中磁盘写满。

3.4 主从同步延时

所有方式做 DDL 均会引发主从同步延时。其中 copy 和 inplace 算法，只有主完成了 DDL 操作之后，binlog 才会同步给从库，从库才能开始操作 DDL，从库操作完 DDL 之后才能开始操作其他语句，因此会造成巨大的（大概两倍 DDL 操作时间）的延时。其他方法产生的延时较小，但仍然可能有几秒的延时。

3.5 MDL

所有方式做 DDL 均会产生 MDL（metadata lock）。除了 copy 模式会有持续性的锁（DDL 的整个过程期间无法向该表写入任何数据）之外，其他方式的 MDL 均为短暂的锁。

除了 copy 模式之外的所有模式，MDL 如下：

在 DDL 的开始阶段，申请该表的 EXCLUSIVE-MDL 锁，禁止读写
降级 EXCLUSIVE-MDL 锁，允许读写
在 DDL 的最终 COMMIT 阶段，升级 EXCLUSIVE-MDL 锁，禁止读写

其中的阶段一和阶段三，其 MDL 的持续时间都是非常短暂的，也就是申请到了 MDL 锁之后会在很快的时间（一般小于一秒）处理完成相关操作并释放锁，一般情况下是不会影响业务的。只有阶段二是真正在处理数据，持续时间一般较长。

但是，有可能出现在阶段一和阶段三，无法申请到 MDL 的情况。这是因为 MDL 和所有的读写语句都可能会产生冲突，如果是在申请 MDL 的时候，之前有读写的事务一直没有执行完成（或者执行完成之后一直没有 COMMIT），MDL 就会无法立刻申请到，这个时候，DDL 语句，以及所有在该 DDL 语句之后的读写事务，都会阻塞并等待之前的读写事务完成，导致整个实例处于不可用状态。这个时候 SHOW PROCESSLIST 看到的语句状态为 waiting for metadata lock。

由于目前所有的 DDL 语句都会产生 MDL，无法避免，因此，在执行 DDL 操作期间，尽可能确保不要有未执行完成的长事务。如果发生了 warting for metadata lock 导致的阻塞，一般有以下三种处理方法：

耐心等待之前的事务全部执行完成
将之前未执行完成的事务全部 kill 掉
kill 掉 DDL 语句

3.6 其他

MySQL 的 inplace 算法虽然支持在 DDL 过程中间的读写，但是对写入的数据量有上限，不能超过 innodb_online_alter_log_max_size（默认为 128M）。如果超过上限可能导致执行失败。

4 MySQL DDL 的原理简析

4.1 copy 算法

较简单的实现方法，MySQL 会建立一个新的临时表，把源表的所有数据写入到临时表，在此期间无法对源表进行数据写入。MySQL 在完成临时表的写入之后，用临时表替换掉源表。这个算法主要被早期（<=5.5）版本所使用。

4.2 inplace 算法

从 5.6 开始，常用的 DDL 都默认使用这个算法。inplace 算法包含两类：inplace-no-rebuild 和 inplace-rebuild，两者的主要差异在于是否需要重建源表。

inplace 算法的操作阶段主要分为三个：

Prepare阶段：

创建新的临时 frm 文件(与 InnoDB 无关)。
持有 EXCLUSIVE-MDL 锁，禁止读写。
根据 alter 类型，确定执行方式（copy，online-rebuild，online-not-rebuild）。
更新数据字典的内存对象。
分配 row_log 对象记录数据变更的增量（仅 rebuild 类型需要）。
生成新的临时ibd文件 new_table（仅rebuild类型需要）。

Execute 阶段：

降级EXCLUSIVE-MDL锁，允许读写。
扫描old_table聚集索引（主键）中的每一条记录 rec。
遍历new_table的聚集索引和二级索引，逐一处理。
根据 rec 构造对应的索引项。
将构造索引项插入 sort_buffer 块排序。
将 sort_buffer 块更新到 new_table 的索引上。
记录 online-ddl 执行过程中产生的增量（仅 rebuild 类型需要）。
重放 row_log 中的操作到 new_table 的索引上（not-rebuild 数据是在原表上更新）。
重放 row_log 中的DML操作到 new_table 的数据行上。

Commit阶段：

当前 Block 为 row_log 最后一个时，禁止读写，升级到 EXCLUSIVE-MDL 锁。
重做 row_log 中最后一部分增量。
更新 innodb 的数据字典表。
提交事务（刷事务的 redo 日志）。
修改统计信息。
rename 临时 ibd 文件，frm文件。
变更完成，释放 EXCLUSIVE-MDL 锁。

4.3 instant 算法

MySQL 8.0.12 才提出的新算法，目前只支持添加列等少量操作，利用 8.0 新的表结构设计，可以直接修改表的 metadata 数据，省掉了 rebuild 的过程，极大的缩短了 DDL 语句的执行时间。

4.4 pt-online-schema-change（pt-osc）

借鉴了 copy 算法的思路，由外部工具来完成临时表的建立，数据同步，用临时表替换源表这三个步骤。其中数据同步是利用 MySQL 的触发器来实现的，会少量影响到线上业务的 QPS 及 SQL 响应时间。

pt-osc在数据库高负载执行时可能会导致死锁问题，建议业务低峰期或者使用gh-ost 工具。

pt-osc又又出现死锁了
https://www.cnblogs.com/juanmaofeifei/p/13647750.html

4.5 gh-ost

gh-ost是 Github 维护的 MySQL online DDL 工具，使用了镜像表的形式，但是放弃了使用低效的 trigger，而是从 binlog 中提取需要的增量数据来保持镜像表与源表的数据一致性。整个 Online DDL 操作仅在最终 rename 源表与镜像表时会阻塞几秒钟的读写。

4.5.1 工作原理

go-ost 的操作流程大致如下：

在 Master 中创建镜像表（_tablename_gho）和心跳表（_tablename_ghc）。
向心跳表中写入 Online-DDL 的进度以及时间。
在镜像表上执行 ALTER 操作。
伪装成 slave 连接到 Master 的某个 Slave 实例上获取 binlog 的信息（默认连接 Slave，也可以连 Master）。
在 Master 中完成镜像表的数据同步：
(1)从源表中拷贝数据到镜像表；
(2)依据 Binlog 信息完成增量数据的变更;
在源表上加锁;
确认心跳表中的时间，确保数据是完全同步的；
用镜像表替换源表。
Online DDL 完成。
未来考虑会支持的功能或特性：
(1)支持外键。
(2)gh-ost 进程意外中断以后，可以新启动一个进程继续进行 Online DDL。

4.5.2 使用限制

binlog 格式必须使用 row，且binlog_row_image必须是 FULL。
需求的权限为SUPER, REPLICATION CLIENT, REPLICATION SLAVE on . and ALL on dbname.*
(1)如果确认 binlog 的格式为 row，那么可以加上 -assume-rbr，则不再需要 super 权限。
(2)由于不支持 REPLICATION 相关的权限，TiDB 无法使用。
不支持外键。
(1)不论源表是主表还是子表，都无法使用。
不支持触发器。
不支持包含 JSON 列的主键。
迁移表需要有显示定义的主键，或者有非空的唯一索引。
迁移工具不区分大小写英文字母，如果存在同名，但是大小写不同的表则无法迁移。
迁移表的主键或者非空唯一索引包含枚举类型时，迁移效率会大幅度降低。

4.5.3 使用注意

如果源表有非常多的数据，尽量分批次删除。
(1)delete from table tablename_old limit 5000;
(2)或者在业务空闲时段用truncate table tablename_old清空表数据之后再 drop 表。
单个 MySQL 实例上启动多个 gh-ost 来进行多个表的 Online DDL 操作时要制定-replica-server-id参数
务必注意可用的磁盘空间，尤其是操作大表的时候。
(1)gh-ost 的镜像表包含源表的所有数据，会额外占用一倍的磁盘。
(2)gh-ost 在操作的过程中会产生大量的 binlog，且binlog_row_image必须为 FULL，会占用比较多的磁盘空间。
rename 列的操作可能会有问题，考虑 drop 和 add 的操作结合起来。
默认会用同样的账号名和密码同时连接 master 和 slave，因此方便起见，直接用高权限账号会比较好。

gh-ost 输出的信息，迁移数据的效率，以及支持的功能都比 pt-osc 等工具要优秀，而 gh-ost 工具的问题（例如磁盘空间）在其他工具也会遇到，因此在 DDL 操作又想避免延迟等问题时，推荐优先考虑 gh-ost。

签到成功

CNDBA社区