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

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

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

![图片](https://img-
blog.csdnimg.cn/img_convert/c3b1de1b2ee555cfb86eb6f27f041e32.png)

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执行的大致流程如下：

![图片](https://img-
blog.csdnimg.cn/img_convert/5b20ba32b911b0747b33c112eea1e41d.png)

活动截止时间

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

数据源

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

![图片](https://img-
blog.csdnimg.cn/img_convert/a7a0bc05655b4ba621bd9f40e3d93778.png)

规范与流程

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

![图片](https://img-
blog.csdnimg.cn/img_convert/970253e42dbd46b0ccfcd02a8615f32d.png)

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

![图片](https://img-
blog.csdnimg.cn/img_convert/ff81cce459e1e8a3b76933e28e3e9748.png)

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

![图片](https://img-
blog.csdnimg.cn/img_convert/dcf081baf386c347a3ad808ab27e070f.png)

OnlineDDL自适应规则配置：

![图片](https://img-
blog.csdnimg.cn/img_convert/20eadaf87d7347c33b1408d77fefe8ce.png)

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

实战测试

1. 创建SQL任务：

![图片](https://img-
blog.csdnimg.cn/img_convert/ce9b3a545e5ce881f722c3e5902cf832.png)

2. 执行 DDL 语句：

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

![图片](https://img-
blog.csdnimg.cn/img_convert/f590ed27768f7627946f998b03b2fa31.png)

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

![图片](https://img-
blog.csdnimg.cn/img_convert/77f0f9ea04e0438d260734532d76f4c7.png)

3. 提交 SQL

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

![图片](https://img-
blog.csdnimg.cn/img_convert/787b25f367aada3381bb389085700b9c.png)

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

![图片](https://img-
blog.csdnimg.cn/img_convert/9e4270f84bc95b94c7939488b91f2fcc.png)

4. 执行信息

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

![图片](https://img-
blog.csdnimg.cn/img_convert/ac8173e4df8529002ee5da67be46687a.png)

从上面的例子看到，如果执行的 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，立即开始免费使用。

网络安全工程师(白帽子)企业级学习路线

第一阶段：安全基础（入门）

第二阶段：Web渗透（初级网安工程师）

第三阶段：进阶部分（中级网络安全工程师）

如果你对网络安全入门感兴趣，那么你需要的话可以点击这里👉网络安全重磅福利：入门&进阶全套282G学习资源包免费分享！

学习资源分享