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

2023-12-22 10:40:31

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,立即开始免费使用。

文章来源:https://blog.csdn.net/NineData/article/details/135146439
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。