# Online DDL使用指导 #### 操作场景 在数据库的日常运维与使用中，数据定义语言（DDL）操作，如为表添加字段、增删索引，是常见的需求。然而，对于大型表而言，这些操作可能持续数小时甚至数天，并在此期间阻塞对原表的所有DML（INSERT/UPDATE/DELETE等）操作，严重影响业务的连续性和可用性。 为了解决这一核心问题，MySQL Online DDL功能在实现表结构变更的同时，允许对该表进行并发的数据读写操作，从而最大限度地保证业务的连续性和可用性。本章节将详细介绍MySQL官方基于InnoDB存储引擎的Online DDL实现机制，以及gh-ost工具的实现原理，帮助您更安全、高效地执行DDL操作。 #### MySQL Online DDL介绍 在 MySQL Online DDL 操作中，通过显式指定ALGORITHM可以对表结构变更的行为进行精细控制。以添加索引为例： ``` ALTER TABLE table_name ADD INDEX index_name(column_name), ALGORITHM=INPLACE; ``` 其中，ALGORITHM 用于指定 DDL 执行的算法，可选取值如下： - COPY：会在整个DDL操作过程中阻塞所有并发DML操作，可能导致长时间的数据库锁定，因此不推荐使用。 - INPLACE：允许在不阻塞并发DML的情况下执行DDL操作，适用于大多数DDL操作。DDL支持情况请参考[如何确定DDL是否支持instant或inplace算法？] - INSTANT：通过修改数据字典中的元数据来完成DDL操作，无需复制数据或重建表，因此通常可以在秒级完成，对原表数据几乎无影响，不阻塞并发DML，但仅支持少数DDL操作，**推荐使用** 。DDL支持情况请参考[如何确定DDL是否支持instant或inplace算法？] #### gh-ost工具介绍 若DDL操作只能采用COPY算法（全程锁表），且原表是大表/频繁执行DML的表，对性能和稳定性要求高的场景，可以考虑使用gh-ost。 gh-ost是一款开源工具，专为MySQL数据库设计，用于执行Online DDL操作，避免传统DDL操作导致的长时间锁表，从而减少对业务的影响。 #### 免责声明 gh-ost是第三方开源工具，并非华为云产品。为了帮助您更好地使用gh-ost，本文介绍了gh-ost的使用约束、建议、工作原理等内容，内容仅供参考。更多详细内容请参见[官方文档](https://github.com/github/gh-ost)。 #### gh-ost使用约束和建议 - 表必须存在唯一键。 - 必须开启Binlog，binlog格式必须是row，且binlog_row_image=FULL。Binlog是否开启请参见[查询和下载Binlog日志](https://support.huaweicloud.com/usermanual-taurusdb/taurusdb_03_0400.html)。 - 不支持临时表，不支持触发器。 - 请在业务低峰期使用，使用期间禁止执行其他的DDL操作。 - 使用gh-ost过程中，如果遇到gh-ost进入throttled状态，可以将参数-max-lag-millis（默认值1500，单位为ms）的值上调，比如设置-max-lag-millis=10000。 - gh-ost不支持外键，建议使用MySQL官方DDL。如果必须使用gh-ost，可以提前删除外键，或者加上gh-ost的参数--skip-foreign-key-checks，此时可以使用gh-ost执行DDL，但是结束后会丢失表上的外键，需要手动添加外键。 - gh-ost的工作原理中，会创建一个影子表，该影子表的结构与原表相同。gh-ost会通过解析Binlog，将原表的数据全量同步到影子表。同步完成之后，会采用**RENAME TABLE** 的方式将原表和影子表的表名互换。 由于gh-ost的已知问题，偶现**RENAME TABLE**长时间无法完成的情况，报错"ERROR Error 1205: Lock wait timeout exceeded; try restarting transaction"。 解决/规避方法： - 将gh-ost的参数--cut-over-lock-timeout-seconds（设置**RENAME TABLE**操作的超时时间，单位为秒）的值上调，比如设置--cut-over-lock-timeout-seconds=10。取值范围1-10s，超出范围则取默认值3s。 - 设置gh-ost的参数--cut-over=two-step，采用非原子的方式切换表，即首先**RENAME** 原表为临时表，再**RENAME TABLE**影子表为原表，但是切换过程中如果失败的话，可能存在原表丢失的情况，需慎重选择，建议优选第一种方式。 #### gh-ost使用示例 将 sbtest.sbtest1 表的存储引擎改为InnoDB。 ``` gh-ost -user="temp" -password="test" -host=**.*.*.* \ -database="sbtest" -table="sbtest1" \ -alter="engine=innodb" \ -chunk-size=2000 \ -allow-on-master \ -cut-over=default \ -default-retries=120 \ -panic-flag-file=/tmp/ghost.panic.flag \ -execute \ -debug \ -max-load=Threads_running=20 \ -critical-load=Threads_running=100 ``` 表1参数解释 | 参数示例 | 解释 | 示例含义 | |:---|:---|:---| | -user="temp" -password="test" -host=\*\*.\*.\*.\* | 数据库连接凭据。 | 使用用户temp和密码test连接到指定IP的实例。 | | -database="sbtest" -table="sbtest1" | 指定要操作的数据和表。 | 对sbtest数据库中的sbtest1表进行操作 | | -alter="engine=innodb" | 要执行的表结构变更。 | 将表的存储引擎改为 InnoDB。 说明： 不需要ALTER TABLE语句。 | | -chunk-size=2000 | gh-ost分批次将整个表的数据全部拷贝，批次大小由-chunk-size参数决定（默认1000行）。 | 每次从原表拷贝2000行到影子表，影响迁移速度和数据库负载 | | -allow-on-master | 允许在主节点上直接执行。 | 此参数明确允许gh-ost在主节点执行。 | | -cut-over=default | 设置表切换方式。 | 使用默认的原子切换方式，通过**RENAME TABLE**完成最终表交换。 | | -default-retries=120 | 设置默认重试次数。 | 对于可重试的操作，最多重试120 次。 | | -panic-flag-file=/tmp/ghost.panic.flag | 设置紧急中止标志文件。 | 如果此文件存在，gh-ost会立即中止操作。 | | -execute | 实际执行操作。 | 不指定此参数时，gh-ost 只做测试。 指定此参数才真正执行迁移。 | | -debug | 启用调试模式。 | 输出更详细的日志信息，用于问题排查。 | | -max-load=Threads_running=20 | 设置最大负载阈值。 | 当 Threads_running（正在运行的线程数）超过20时，gh-ost 会暂停操作以降低负载。 | | -critical-load=Threads_running=100 | 设置紧急负载阈值。 | 当 Threads_running 超过100时，gh-ost会中止操作，防止数据库过载。 | 更多参数介绍请参见[官网](https://github.com/github/gh-ost/blob/master/doc/command-line-flags.md)介绍**。** #### MySQL Online DDL 与gh-ost的区别 下面对MySQL官方DDL和gh-ost进行简单对比。 表2MySQL Online DDL 与gh-ost的区别 | **方案** | MySQL Online DDL | **gh-ost** | |:---|:---|:---| | 适用场景 | - 若DDL操作支持INSTANT或INPLACE，推荐使用。 - 若DDL操作仅支持COPY算法，不推荐使用（尤其是大表）。 | 若DDL操作只能采用COPY算法，原表又是大表/频繁执行DML的表，对性能和稳定性要求高的场景，推荐使用gh-ost。 | | 原理 | - INSTANT 算法：仅修改元数据。 - INPLACE 算法：在现有表空间内修改元数据和数据页，并通过row Log同步增量数据。 - COPY 算法：创建临时表并拷贝数据。 | 1. 创建影子表。 2. 消费Binlog同步增量数据。 3. 分批拷贝原表数据到影子表。 4. 原子交换表名。 5. 清理资源，执行结束。 | | 约束限制 | - 删除主键、新增全文索引/空间索引、更改列类型等少数操作，仅支持COPY算法。 - 不支持临时表。 | - 表必须存在唯一键。 - 必须开启 binlog，且binlog格式必须是row，binlog_row_image必须是FULL。 - 不支持临时表、外键、触发器。 | | DML阻塞时长 | - INSTANT：阻塞时间短。 - INPLACE：仅开始和结束阶段短暂阻塞。 - COPY：全程阻塞。 | 1. 数据同步期间，分批次锁定行数据，仅阻塞批次内的行数据的DML操作，不阻塞批次外的行数据。 2. 原子表名切换时会阻塞，时间极短。 | | 额外空间占用 | - INSTANT：极小。 - INPLACE：小(需要重建表时略大) 。 - COPY：至少占用和原表空间一样大小。 | 至少占用和原表空间一样大小。 | | 复制延迟 | - INSTANT：极小。 - INPLACE：小 。 - COPY：大 。 | 中。 | #### 常见问题 #### 如何预估DDL的执行时长？ DDL的执行时长由执行的算法、表的数据量、实例规格、业务负载等综合决定，建议您在**业务低峰期**执行。 建议执行DDL之前，开启[DDL快速超时](https://support.huaweicloud.com/kerneldesc-taurusdb/taurusdb_20_0014.html)和[非阻塞DDL](https://support.huaweicloud.com/kerneldesc-taurusdb/taurusdb_20_0015.html)功能（需要注意实例内核版本等是否满足要求）。 #### 如何确定DDL是否支持instant或inplace算法？ - 方式一：执行SQL语句。 1. 设置algorithm=instant。如果不支持会报错。 2. 再设置algorithm=inplace。如果不支持会报错。 例如： ``` --不支持instant算法 alter table t drop primary key,algorithm=instant; ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY/INPLACE. ``` ``` --不支持inplace算法 alter table t drop primary key,algorithm=inplace; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY. ``` ``` --仅支持copy算法 alter table t drop primary key,algorithm=copy; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` - 方式二：查看[MySQL官网](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html)。 #### DDL执行过程中，如何查看执行进度？ 目前仅支持[创建二级索引进度查询](https://support.huaweicloud.com/kerneldesc-taurusdb/taurusdb_20_0016.html)。 其他DDL可执行如下SQL进行查询（需要performance_schema参数值为ON）。 ``` SELECT EVENT_NAME AS '当前执行阶段',WORK_COMPLETED AS '已完成工作量',WORK_ESTIMATED AS '预估总工作量', ROUND(IF(WORK_ESTIMATED = 0, 0, (WORK_COMPLETED / WORK_ESTIMATED) * 100), 2) AS '进度(%)' FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE 'stage/sql/copy to tmp table'OR EVENT_NAME LIKE 'stage/innodb/alter table%'; ```