MySQL Online DDL 原理和踩坑

雨点打透心脏的1/2处 2022-10-12 00:50 154阅读 0赞

![0D5F937FE12312312D81F17F.jpg][]

程序员的成长之路

互联网/程序员/技术/资料共享

[关注][Link 1]

阅读本文大概需要 4 分钟。

来自：网络，侵删

导读：MySQL 的 DDL(Data Definition Language) 包括增减字段、增减索引等操作。在 MySQL 5.6 之前，MySQL 的 DDL 操作会按照原来的表复制一份，并做相应的修改。

例如，对表 A 进行 DDL 的具体过程如下：

*  按照表 A 的定义新建一个表 B
 *  对表 A 加写锁
 *  在表 B 上执行 DDL 指定的操作
 *  将 A 中的数据拷贝到 B
 *  释放 A 的写锁
 *  删除表 A
 *  将表 B 重命名为 A

在 2-4 的过程中，如果表 A 数据量比较大，拷贝到表 B 的过程会消耗大量时间，并占用额外的存储空间。此外，由于 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。

因此，MySQL 5.6 增加了 Online DDL，允许在不中断数据库服务的情况下进行 DDL 操作。

## **用法** ##

![22a07bdac5a720d38bb978b0eddc7964.png][]

ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的方式和 DDL 期间 DML 的兵法控制

*  ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）。  
    如果设置 ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的方式，采用表拷贝的方式进行，过程中会阻塞所有的DML。另外也可以设置 ALGORITHEM=DAFAULT，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。  
    
 *  LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）

不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行，具体的支持情况可以在 MySQL Reference Manual — Online DDL Operations) 中查看。

例如 Table 14.10 中显示修改列的数据类型不支持 INPLACE

![49f963e5574c0b01c0bceb525f036be1.png][]

这时尝试将原类型为 FLOAT 的 column\_name 改为 INT

![0810146f64e15f5d732b60976e41f7eb.png][]

会报错

![425e78a00810d2b53925175b31b3c711.png][]

## **执行过程** ##

*  **初始化**：根据存储引擎、用户指定的操作、用户指定的 ALGORITHM 和 LOCK 计算 DDL 过程中允许的并发量，这个过程中会获取一个 shared metadata lock，用来保护表的结构定义
 *  **执行 DDL**：根据第一步的情况决定是否将 shared metadata lock 升级为 exclusive metadata lock（仅在语句准备阶段），然后生成语句并执行。执行期间的 shared metadata lock 保证了不会同时执行其他的 DDL，但 DML 能可以正常执行
 *  **提交**：将 shared metadata lock 升级为 exclusive metadata lock，然后删除旧的表定义，提交新的表定义

![1b6b77b8449843469202c54c44a066b7.png][]

Online DDL 过程中占用 exclusive MDL 的步骤执行很快，所以几乎不会阻塞 DML 语句。

不过，在 DDL 执行前或执行时，其他事务可以获取 MDL。由于需要用到 exclusive MDL，所以必须要等到其他占有 metadata lock 的事务提交或回滚后才能执行上面两个涉及到 MDL 的地方。

## **踩坑** ##

前面提到 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 只有在事务结束后才会被释放。

![a42dc472439ad3ff857b260b568f1c58.png][]

这时 Session 2 想要执行 DML 操作也只需要获取 shared MDL，仍然可以正常执行。

![4fd13999a21e3543d840864888166602.png][]

但如果 Session 3 想执行 DDL 操作就会阻塞，因为此时 Session 1 已经占用了 shared MDL，而 DDL 的执行需要先获取 exclusive MDL，因此无法正常执行。

![e831cf464ceccf2e8d8d604da0c9a138.png][]

通过 show processlist可以看到 ALTER 操作正在等待 MDL。

由于 exclusive MDL 的获取优先于 shared MDL，后续尝试获取 shared MDL 的操作也将会全部阻塞

![7baf9d3123364062352b8c8a66d3d960.png][]

到这一步，后续无论是 DML 和 DDL 都将阻塞，直到 Session 1 提交或者回滚，Session 1 占用的 shared MDL 被释放，后面的操作才能继续执行。

![991cc8d561e560035d4de87fcc088184.png][]

上面这个问题主要有两个原因：

*  Session 1 中的事务没有及时提交，因此阻塞了 Session 3 的 DDL
 *  Session 3 Online DDL 阻塞了后续的 DML 和 DDL

**对于问题 1：**不少 ORM（例如 pymysql）都默认将用户语句封装成事务执行，如果客户端程序中断退出，还没来得及提交或者回滚事务，就会出现 Session 1 中的情况。这时可以在 infomation\_schema.innodb\_trx中找出未完成的事务对应的线程，并强制退出

可以看到 Session 1 正在执行的事务对应的 trx\_mysql\_thread\_id 为 9，然后执行 KILL 9即可中断 Session 1 中的事务。

**对于问题 2：**在查询很多的情况下，会导致阻塞的 session 迅速增多，对于这种情况，可以先中断 DDL 操作，防止对服务造成过大的影响。也可以尝试在从库上修改表结构后进行主从切换或者使用 pt-osc 等第三方工具。

<END>

**推荐阅读：**

[美女学霸考 692 分想当“程序媛”，网友：快劝劝孩子！][692]

[批处理框架 Spring Batch 这么强，你会用吗？][Spring Batch]

最近面试BAT，整理一份面试资料《Java面试BATJ通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。

**获取方式：点个「****在看****」，点击上方小卡片，进入公众号后回复「****面试题****」领取，更多内容陆续奉上。**

朕已阅 ![a761641b440ed7a336c1eceed082215a.gif][]

[0D5F937FE12312312D81F17F.jpg]: /images/20221005/fe82b11c95eb44a6b197313e6b5c9d3e.png
[Link 1]: https://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa24f0bacd5379ac93d74ffebac826d2401fd494f82e900e5102a8bf968f108e4ec72a4ad548&idx=3&lang=zh_CN&mid=2247487442&scene=21&sn=61e758d4bf9f15a3419fd028146469a3&token=1854383306#wechat_redirect
[22a07bdac5a720d38bb978b0eddc7964.png]: /images/20221005/7e51ac669ba94200a0cfce13437b11fa.png
[49f963e5574c0b01c0bceb525f036be1.png]: /images/20221005/2f1cdf9592734b9d8b7e47d6e451dd20.png
[0810146f64e15f5d732b60976e41f7eb.png]: /images/20221005/4d356075b1ab4aae8507cbd6f8f0280d.png
[425e78a00810d2b53925175b31b3c711.png]: /images/20221005/41f98608c9094b81ae2c758f31bbef81.png
[1b6b77b8449843469202c54c44a066b7.png]: /images/20221005/b3fa1cc6af0c4d388a7758c36dd02ee9.png
[a42dc472439ad3ff857b260b568f1c58.png]: /images/20221005/474ba2abd3f6452e86081e6bda943232.png
[4fd13999a21e3543d840864888166602.png]: /images/20221005/60eda9b12b5a42498f42d66927eecb60.png
[e831cf464ceccf2e8d8d604da0c9a138.png]: /images/20221005/a26d2cfedf424ee894c2e0138b9a2def.png
[7baf9d3123364062352b8c8a66d3d960.png]: /images/20221005/8e61927acf594c3da06882b671dcd39e.png
[991cc8d561e560035d4de87fcc088184.png]: /images/20221005/973b7f86c772406386bffbecf247c47b.png
[692]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa275cdfcd50d5c968ffd36a94da5fe6a5593b3c2f239eaa40701be5627dcf32159311935ab0&idx=1&mid=2247508919&scene=21&sn=b9873e437ce19a721f83da584dd19f69#wechat_redirect
[Spring Batch]: http://mp.weixin.qq.com/s?__biz=MzUyNDkzNzczNQ%3D%3D&chksm=fa275cdfcd50d5c90ccef4f8ce06231717bb08c167416ce5d6bfb3981cc25260e72db7709648&idx=2&mid=2247508919&scene=21&sn=62b170e5ccb4fd016f61ced3e9a4a8f3#wechat_redirect
[a761641b440ed7a336c1eceed082215a.gif]: /images/20221005/7b7927112df64fcbab3356643e960daf.png