拼多多网站建设框架图,怎么做贝店式的网站,东莞关键词排名快速优化,西安免费做网站多少钱MySQL作为目前应用最广泛的开源关系型数据库#xff0c;是许多网站、应用和商业产品的主要数据存储。在生产环境#xff0c;线上数据库常常面临着持续的、不断变化的表结构修改#xff08;DDL#xff09;#xff0c;如增加、更改、删除字段和索引等等。其中一些DDL操作在M…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 方式OnlineMySQL5.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立即开始免费使用。
