| |
|
开发:
C++知识库
Java知识库
JavaScript
Python
PHP知识库
人工智能
区块链
大数据
移动开发
嵌入式
开发工具
数据结构与算法
开发测试
游戏开发
网络协议
系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程 数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁 |
-> 大数据 -> MySQL DDL执行方式-Online DDL介绍 -> 正文阅读 |
|
[大数据]MySQL DDL执行方式-Online DDL介绍 |
1 引言大家好,今天与大家一起分享一下 mysql DDL执行方式。
我们可以认为:
DDL、DML区别:
日常开发我们对一条DML语句较为熟悉,很多开发人员都了解sql的执行过程,比较熟悉,但是DDL是如何执行的呢,大部分开发人员可能不太关心,也认为没必要了解,都交给DBA吧。 其实不然,了解一些能尽量避开一些ddl的坑,那么下面带大家一起了解一下DDL执行的方式,也算抛砖引玉吧。如有错误,还请各位大佬们指正。 2 概述在MySQL使用过程中,根据业务的需求对表结构进行变更是个普遍的运维操作,这些称为DDL操作。常见的DDL操作有在表上增加新列或给某个列添加索引。 我们常用的易维平台提供了两种方式可执行DDL,包括MySQL原生在线DDL(online DDL)以及一种第三方工具pt-osc。 下图是执行方式的性能对比及说明: 本文将对DDL的执行工具之Online DDL进行简要介绍及分析,pt-osc会专门再进行介绍。 3 介绍MySQL Online DDL 功能从 5.6 版本开始正式引入,发展到现在的 8.0 版本,经历了多次的调整和完善。其实早在 MySQL 5.5 版本中就加入了 INPLACE DDL 方式,但是因为实现的问题,依然会阻塞 INSERT、UPDATE、DELETE 操作,这也是 MySQL 早期版本长期被吐槽的原因之一。 在MySQL 5.6版本以前,最昂贵的数据库操作之一就是执行DDL语句,特别是ALTER语句,因为在修改表时,MySQL会阻塞整个表的读写操作。例如,对表 A 进行 DDL 的具体过程如下:
在以上 2-4 的过程中,如果表 A 数据量比较大,拷贝到表 B 的过程会消耗大量时间,并占用额外的存储空间。此外,由于 DDL 操作占用了表 A 的写锁,所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。 如果遇到巨大的表,可能需要几个小时才能执行完成,势必会影响应用程序,因此需要对这些操作进行良好的规划,以避免在高峰时段执行这些更改。对于那些要提供全天候服务(24*7)或维护时间有限的人来说,在大表上执行DDL无疑是一场真正的噩梦。 因此,MySQL官方不断对DDL语句进行增强,自MySQL 5.6 起,开始支持更多的 ALTER TABLE 类型操作来避免数据拷贝,同时支持了在线上 DDL 的过程中不阻塞 DML 操作,真正意义上的实现了 Online DDL,即在执行 DDL 期间允许在不中断数据库服务的情况下执行DML(insert、update、delete)。然而并不是所有的DDL操作都支持在线操作。到了 MySQL 5.7,在 5.6 的基础上又增加了一些新的特性,比如:增加了重命名索引支持,支持了数值类型长度的增大和减小,支持了 VARCHAR 类型的在线增大等。但是基本的实现逻辑和限制条件相比 5.6 并没有大的变化。 4 用法
ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的算法模式和 DDL 期间 DML 的锁控制模式。
5 两种算法第一种 Copy:
第二种 Inplace: 在原表上进行更改,不需要生成临时表,不需要进行数据copy的过程。根据是否行记录格式,又可分为两类:
对于 rebuild 方式实现 Online 是通过缓存 DDL 期间的 DML,待 DDL 完成之后,将 DML 应用到表上来实现的。例如,执行一个 alter table A engine=InnoDB; 重建表的 DDL 其大致流程如下:
说明:
使用Inplace方式执行的DDL,发生错误或被kill时,需要一定时间的回滚期,执行时间越长,回滚时间越长。 以下是常见DDL操作: 官网支持列表: 6 执行过程Online DDL主要包括3个阶段,prepare阶段,ddl执行阶段,commit阶段。下面将主要介绍ddl执行过程中三个阶段的流程。 1)Prepare阶段:初始化阶段会根据存储引擎、用户指定的操作、用户指定的 ALGORITHM 和 LOCK 计算 DDL 过程中允许的并发量,这个过程中会获取一个 shared metadata lock,用来保护表的结构定义。
注:Row log是一种独占结构,它不是redo log。它以Block的方式管理DML记录的存放,一个Block的大小为由参数innodb_sort_buffer_size控制,默认大小为1M,初始化阶段会申请两个Block。 2)DDL执行阶段:执行期间的 shared metadata lock 保证了不会同时执行其他的 DDL,但 DML 能可以正常执行。
3)Commit阶段:将 shared metadata lock 升级为 exclusive metadata lock,禁止DML,然后删除旧的表定义,提交新的表定义。
Online DDL 过程中占用 exclusive MDL 的步骤执行很快,所以几乎不会阻塞 DML 语句。 7 踩坑前面提到 Online DDL 执行过程中需要获取 MDL,MDL (metadata lock) 是 MySQL 5.5 引入的表级锁,在访问一个表的时候会被自动加上,以保证读写的正确性。当对一个表做 DML 操作的时候,加 MDL 读锁;当做 DDL 操作时候,加 MDL 写锁。 为了在大表执行 DDL 的过程中同时保证 DML 能并发执行,前面使用了 ALGORITHM=INPLACE 的 Online DDL,但这里仍然存在死锁的风险,问题就出在 Online DDL 过程中需要 exclusive MDL 的地方。 例如,Session 1 在事务中执行 SELECT 操作,此时会获取 shared MDL。由于是在事务中执行,所以这个 shared MDL 只有在事务结束后才会被释放。
这时 Session 2 想要执行 DML 操作也只需要获取 shared MDL,仍然可以正常执行。
但如果 Session 3 想执行 DDL 操作就会阻塞,因为此时 Session 1 已经占用了 shared MDL,而 DDL 的执行需要先获取 exclusive MDL,因此无法正常执行。
通过 show processlist 可以看到 ALTER 操作正在等待 MDL。 由于 exclusive MDL 的获取优先于 shared MDL,后续尝试获取 shared MDL 的操作也将会全部阻塞
到这一步,后续无论是 DML 和 DDL 都将阻塞,直到 Session 1 提交或者回滚,Session 1 占用的 shared MDL 被释放,后面的操作才能继续执行。 上面这个问题主要有两个原因:
对于问题 1,有些ORM框架默认将用户语句封装成事务执行,如果客户端程序中断退出,还没来得及提交或者回滚事务,就会出现 Session 1 中的情况。那么此时可以在 infomation_schema.innodb_trx 中找出未完成的事务对应的线程,并强制退出。
可以看到 Session 1 正在执行的事务对应的 trx_mysql_thread_id 为 9,然后执行 KILL 9 即可中断 Session 1 中的事务。 8 限制
官方参考资料:MySQL :: MySQL 5.7 Reference Manual :: 14.13.6 Online DDL Limitations 9 总结本次和大家一起了解SQL的DDL、DML及区别,也介绍了Online DDL的执行方式。 目前可用的DDL操作工具包括pt-osc,github的gh-ost,以及MySQL提供的在线修改表结构命令Online DDL。pt-osc和gh-ost均采用拷表方式实现,即创建个空的新表,通过select+insert将旧表中的记录逐次读取并插入到新表中,不同之处在于处理DDL期间业务对表的DML操作。 到了MySQL 8.0 官方也对 DDL 的实现重新进行了设计,其中一个最大的改进是 DDL 操作支持了原子特性。另外,Online DDL 的 ALGORITHM 参数增加了一个新的选项:INSTANT,只需修改数据字典中的元数据,无需拷贝数据也无需重建表,同样也无需加排他 MDL 锁,原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的,也不会阻塞 DML,不过目前8.0的INSTANT使用范围较小,后续再对8.0的INSTANT做详细介绍吧。 另外,易维平台也提供了pt-osc的执行方式,下次再与大家一起分享pt-osc的执行方式吧,敬请期待! |
|
|
上一篇文章 下一篇文章 查看所有文章 |
|
开发:
C++知识库
Java知识库
JavaScript
Python
PHP知识库
人工智能
区块链
大数据
移动开发
嵌入式
开发工具
数据结构与算法
开发测试
游戏开发
网络协议
系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程 数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁 |
360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年11日历 | -2024/11/23 10:13:41- |
|
网站联系: qq:121756557 email:121756557@qq.com IT数码 |