安全、高效的MySQL DDL解决方案

在 MySQL5.5 及之前的版本，修改表结构操作(DDL)会阻塞对该表数据的读写操作，从MySQL5.6开始，提供Online DDL的能力，支持部分的 DDL语句在执行期间不阻塞该表的读写操作，大大降低了 DDL 操作对业务带来的影响。通过本篇文章的介绍说明，可以了解到当前MySQL在执行Online DDL的几种解决方案，而 NineData 的自适应 OnlineDDL，可以在减少人工判

NineData

3595人浏览 · 2023-12-22 10:04:58

NineData · 2023-12-22 10:04:58 发布

MySQL作为目前应用最广泛的开源关系型数据库，是许多网站、应用和商业产品的主要数据存储。在生产环境，线上数据库常常面临着持续的、不断变化的表结构修改（DDL），如增加、更改、删除字段和索引等等。其中一些DDL操作在MySQL中会锁表，影响线上服务，那该如何解决DDL期间导致业务不可用的问题呢？我们先来看看当前有哪些解决方案。

本文完整对比了业界常用的Online DDL 工具，并从产品体验、版本支持的完整度、云适配、易用性和性能等多个⻆度进行评估与分析，给出使用推荐：

NineData 是SaaS模式，开箱即用，很好的适配了各主流云的 MySQL 实例并覆盖了所有版本。而 pt-osc 和 gh-ost 都是以命令行方式执行，需要人工介入进行命令拼装，不仅容易出错，而且还需要关心和不同版本的 MySQL 适配，易用性不高。最后，在性能方面，pt-osc 相对最好，原因是和其 Online DDL实现的方式相关。本文在后面会展开对各工具的流程进行说明，方便大家进一步认识他们的实现方式。

常用 Online DDL 工具

1.1 pt-online-schema-change

由 Percona 公司开发的一种在线修改表结构的工具，该工具执行的大致流程如下：

1. 创建一张影子表（_table_new），结构和原表保持一致。

2. 在影子表（空表）上做DDL操作。

3. 在原表上创建三个触发器（insert、update、delete），通过这些触发器把增量数据写入到影子表中。

4. 进行全量数据复制，根据主键或唯一键进行分片读取写入（INSERT LOW_PRIORITY IGNORE ... SELECT ... LOCK IN SHARE MODE），循环直到全量数据读取写入完成。

5. RENAME TABLE，影子表和源表相互转换表名（原子性，持有短暂时间的排他锁，阻塞DML）。

6. 删除被改名后的原表和触发器。

整个过程中，通过使用触发器实现增量数据的同步，在数据同步期间，不阻塞该表的DML。但由于表上创建有触发器，如果该表的更新比较频繁很可能出现锁争用问题。

1.2 gh-ost

由 GitHub 开发提供的一种在线修改表结构的工具，该工具执行的大致流程如下：

1. 创建一张影子表（_table_gho），结构和原表保持一致。

2. 在影子表（空表）上做DDL操作。

3. 创建 BinLog Streamer，模拟从库读取实例的binlog，应用增量操作到影子表中执行。

4. 进行全量数据复制，根据主键或唯一键进行分片读取写入（INSERT IGNORE ... SELECT ... LOCK IN SHARE MODE），循环直到全量数据读取同步完毕。

5. RENAME TABLE，影子表和源表相互转换表名（原子性，持有短暂时间的排他锁，阻塞DML）。

6. 停止BinLog Streamer，并删除被改名后的原表。

整个过程中，通过读取binlog来实现增量数据的同步，在数据同步期间，不阻塞该表的DML。由于读取binlog是单线程，所以增量同步的效率不高，但开销最小。

1.3 MySQL Online DDL

在 MySQL5.5 及之前的版本，修改表结构操作(DDL)会阻塞对该表数据的读写操作，从MySQL5.6开始，提供Online DDL的能力，支持部分的 DDL语句在执行期间不阻塞该表的读写操作，大大降低了 DDL 操作对业务带来的影响。

MySQL DDL操作分为两种：一种是采用 copy table方式（MySQL5.5及之前的版本）的DDL，期间会阻塞该表的读写操作；另一种是采用 inplace 方式（Online，MySQL5.6及之后的版本），该方式分为两类情况：：一类是重建表（rebuild table），另一类是只修改表的元数据不需要重建表（no-rebuild table），具体可以查看官网中的「Online DDL Operations」章节。其中：

copy table：在 server 层生成一张临时表，复制原表数据到临时表（ibd、frm），完成后临时表替换原表。复制数据期间阻塞该表的读写操作。

rebuild table ：在 engine 层生成原表的临时转储文件（ibd、frm），复制原表数据，完成后临时表替换原表。复制数据期间不阻塞该表的读写操作。

no-rebuild table ：在 engine 层生成原表的临时转储文件（frm），不需要复制源表数据，完成后更新表的元数据信息，期间不阻塞该表的读写操作。

copy table 方式的大致流程：

1. 对表添加一个 Meta Data Lock（共享锁，持有短暂时间的共享锁）。

2. 共享锁升级到排他锁

3. 创建临时表并修改临时表结构

4. 复制数据（阻塞该表的读写操作）

5. 数据复制完成后，重命名替换表

6. 清理数据

7. 提交和释放锁

整个过程中，通过生成临时表的方式进行数据同步，源表的DML操作会被阻塞，直到全量数据复制完成。通过该方式修改表结构会阻塞读写（DML）操作，所以需要尽量避免该方式进行DDL操作。

inplace 方式的大致流程：

1. 对表添加一个 Meta Data Lock（共享锁，短暂的时间）

2. 共享锁升级到排他锁，判断是rebuild table还是no rebuild table

rebuild table，在 engine 层生成原表的临时转储文件（ibd、frm）
no reduild table，在 engine 层生成原表的临时转储文件（frm）

3. 排他锁降级为共享锁，进行数据复制，不阻塞读写操作（Online）

4. 共享锁升级为排它锁，应用 DDL 期间的增量数据，完成后原表删除，新表重命名为原表

5. 提交和释放锁

整个过程中，如果 DDL 是rebuild table方式：则需要同步复制源表的数据到临时的转储文件（ibd），在同步复制期间，不阻塞该表的DML操作。通过该方式修改表结构，需要保证参数innodb_online_alter_log_max_size的大小（增量期间DML的大小），并且该方式的DDL，在备库回放时间也会比较长，导致备库延迟过大。如果DDL是no-rebuild table方式：则只需要修改该表元数据信息，不需要复制源表数据，期间不阻塞该表的读写操作。

以上3种 Online DDL 方法都有各自的特点，但在执行 DDL 操作中，都需要人工判断DDL类型、表大小来选择相应的执行方法。现在，为了更安全、高效的让 DBA 和开发者进行 DDL 操作，NineData 结合了以上工具的优点，实现了自适应Online DDL的能力。

安全、高效的DDL解决方案

NineData SQL 开发专业版和企业版中的「SQL 任务」支持MySQL OnlineDDL 功能，可以在不阻塞表正常读写的情况下，对表执行结构变更。该NineData OnlineDDL执行的大致流程如下：

活动截止时间

无须关心 DDL 类型，只需几步就能完成Online DDL 的配置，实现完全智能化的Online DDL操作，配置过程：

数据源

首先，添加一个MySQL数据源，并选择「环境」，如：开发

规范与流程

然后，在规范与流程中，编辑刚录入数据源的「环境」：开发

在「结构」中，设置表变更的Online要求：

在「SQL 任务与窗口」中，可配置OnlineDDL的自适应规则：

OnlineDDL自适应规则配置：

配置完「规范与流程」之后，通过「SQL 开发」中的 SQL 任务就能实现完全智能化的Online DDL操作。

实战测试

1. 创建SQL任务：

2. 执行 DDL 语句：

1. 修改表 ddl_test 的 k 字段类型，把 int 类型改成 bigint，该操作MySQL不能Online DDL

2. 给表 ddl_test 新增字段，该操作MySQL能Online DDL

3. 提交 SQL

1. NineData 很好的自适应Online DDL，无需关心 DDL 类型。对于MySQL不支持Online DDL的使用NineData OnlineDDL

2. NineData 很好的自适应Online DDL，无需关心 DDL 类型，对于MySQL支持Online DDL的使用MySQL自己的 OnlineDDL

4. 执行信息

在进度页里，可以看到该Online DDL 执行的具体信息

从上面的例子看到，如果执行的 DDL语句不支持MySQL的 Online ，则会使用NineData本身的 OnlineDDL；如果支持MySQL的 Online，则会优先使用MySQL本身的 Online 方法，用户无需关心 DDL的类型是否支持Online，就能保障业务的稳定性。

总结

通过本篇文章的介绍说明，可以了解到当前MySQL在执行Online DDL的几种解决方案，而 NineData 的自适应 OnlineDDL，可以在减少人工判断和操作的前提下，实现了安全、高效的在线表结构无锁变更能力，进一步保障了业务的稳定。

此外，NineData 除了支持MySQL的无锁变更外。作为开箱即用的SQL开发工具，在支持多种数据库、多个云环境的前提下，推出了永久免费的个人版和带有企业级管控、安全协作的企业版，满足企业最高的数据安全合规要求。目前，NineData 已在运营商、金融、制造业、地产、电商等多个行业完成大规模应用实践。如果您感兴趣的话，可以登录官网 https://www.ninedata.cloud/sqldev，立即开始免费使用。