【科普】MySQL中DDL操作背后的并发原理
一. 简介
DQL：指数据库中的查询（select）操作。
DML：指数据库中的插入（insert）、更新（update）、删除（delete）等行数据变更操作。
DDL：指数据库中加列（add column）、修改列（change column）、创建索引（create index）、删除索引（drop index）、删除表（drop table）、清理表（truncate table）等表结构定义操作。
经常有同学会碰到索引加不上，或者drop table卡住等DDL执行问题，很想和他们解释背后原理，但是三言两语解释不通，所以写了这篇文档。
本篇主要介绍下MySQL中DDL操作背后的锁原理，希望通过阅读这篇文档，你可以掌握如下两点：
-
了解MySQL中的DDL锁机制，理解
不要在业务期间进行DDL操作这句话的背后原理。 -
知道如何去查看DDL操作的实时状态及如何去解决MDL锁争用的问题。
二. MDL锁介绍
从MySQL5.5版本开始引入了MDL锁，全称为metadata
lock，即元数据锁。MDL锁的主要作用是维护表元数据的数据一致性，当表上有活动事务（注意MDL锁伴随事务提交而释放，而不是SQL结束而释放）的时候，不可以对元数据（即表结构）进行任何修改操作。
PS：MDL共享锁
= MDL读锁 = MDL S锁， MDL排他锁
= MDL写锁 = MDL X锁，下文中叫法不同，但是含义一致。
粗略画了下DDL与DML（这里其实也包括DQL）的锁申请过程，大家可以对照着这幅图来理解MDL锁，简单来说，DDL操作会申请对应表上的的MDL
X锁，这把锁是排他锁，一旦申请成功，该表上的其他所有操作都无法进行（包括DDL、DML、DQL），因为无法再申请到该表上的MDL锁，直到DDL操作申请的MDL
X锁释放为止。
而DML或DQL操作都只会申请MDL
S锁，而S锁为共享锁，可以支持并发访问，因此大量相同表上的增删改查操作是可以并发执行的。
这里还需要了解的一点是，MDL锁申请遵循一个队列机制，即先到先得，因此如果一个DDL操作一直无法得到MDL
X锁，那么后续所有该表上的SQL都会等待这个DDL操作拿到MDL X锁并且释放为止，这也是为啥我们经常听到不要在业务期间进行DDL操作的原因之一，DDL操作很容易因为某个慢SQL导致后续所有的SQL都被卡住（等待MDL锁）。
三. Online DDL
MySQL
5.6 Online DDL推出以前，执行DDL主要有两种方式copy方式和inplace方式（只支持添加、删除索引），DDL执行期间会全程锁表，无法同时进行DML，实用性很低。
-
copy：建一张新表结构的临时表，锁原表禁止DML，然后拷贝数据到临时表，升级字典锁，禁止原表读写，进行rename操作，完成DDL。
-
inplace（fast index creation）：新建索引的数据字典，锁原表禁止DML，然后在原表基础上读取数据添加索引，等待表上所有只读事务提交，完成DDL。
MySQL
5.6 版本发布了Online
DDL功能，顾名思义，就是在DDL执行期间，也可以同时进行表上的DML操作，并不会全程锁表，实用性加强了很多。
目前我们将MySQL DDL操作划分为三种执行方式：
-
copy：创建新表结构的临时表，锁原表禁止DML，将数据copy到临时表，完成后删除原表，重命名新表，需要拷贝原始表，执行过程中源表不允许写但可读。 -
inplace：在进行DDL操作时，MDL写锁会降级为MDL读锁，这样就可以支持并发DML，然后通过row_log记录原表上的DML增量操作，最后通过回放增量数据保证数据一致性。-
rebuild table：部分DDL操作类型在inplace模式下，需要进行
重建表（原表基础上进行更新），往往表越大越费时。 -
no rebuild table：部分DDL操作类型在inplace模式下，不需要重建表，往往只需要修改元数据，因此
速度比较快。
-
-
instant：从MySQL 8.0.12才开始引入，加列操作可以不需要重建表，只需要修改元数据，可以实现秒加列。
下面列举一些常见的DDL操作（红色标记代表不支持online DDL，只支持copy）：
| DDL操作类型 | DDL执行速度 | 是否支持inplace模式（Online DDL） | rebuild table(是否需要重建表) | 是否支持并发读写 | 只需要修改元数据 |
| 增加列 | 表越大速度越慢 | Yes | Yes | Yes | No |
| 删除列 | 表越大速度越慢 | Yes | Yes | Yes | No |
修改列类型 |
表越大速度越慢 | No |
Yes | No |
No |
| 扩展varchar列长度（255字节以下或255字节以上区间内调整） | 秒级完成 | Yes | No | Yes | Yes |
扩展varchar列长度（从255字节内到255字节外） |
表越大速度越慢 | No |
Yes | No |
No |
| 创建二级索引 | 表越大速度越慢 | Yes | No | Yes | No |
| 删除二级索引 | 秒级完成 | Yes | No | Yes | Yes |
表字符集转换 |
表越大速度越慢 | No |
yes | No |
No |
| drop or truncate table（比较特殊，申请到MDL写锁就可以快速完成） | 秒级完成 | NULL | NULL | NULL | NULL |
关于Online DDL的实现原理，大概如下：
这幅图比较繁琐，但是很详细，我们只需要关注红框的步骤，整个DDL主要划分为3个步骤：
-
PREPARE：会
申请MDL X锁，然后更新数据字典并分配row_log开始记录表上的DML增量数据，这个过程如果没有被MDL锁阻塞，那么是非常快的。 -
DDL：将所持有的
MDL X锁降级为MDL S锁。-
一方面进行数据拷贝，比如建二级索引或者重建原表。
-
另一方面记录这期间产生的DML日志，写到row_log中后进行数据重放。
-
-
COMMIT：等到重放至最后一个block时，从
MDL S锁升级到MDL X锁，回放最后一个block，最后更新数据字典，完成DDL，释放MDL锁。
整个Online的实现主要依赖于row_log记录DDL期间的DML增量日志，这样就可以不用一直占用MDL X锁，而只需要占用一瞬间，期间主要持有MDL S锁即可。
值得注意的是，Online DDL前后需要申请两次MDL X锁，虽然持有时间非常短，如果存在慢SQL的话，还是会引起大量MDL锁等待的问题。
四. MDL锁实验
为了方便大家更好的理解MDL锁，我在dbeaver中进行了如下几个MDL锁模拟实验。
4.1. 实验一
模拟过程：模拟执行一个慢查询（通过sleep函数），然后进行表上的加列操作，查看DDL状态。
-
通过dbeaver中的会话管理窗口可以很方便的看到MySQL中的SQL运行状态。
-
我们先后对
emp表执行了慢查询与加列操作，通过会话窗口可以看到加列的DDL操作处于Waiting for table metadata lock状态，这个状态其实就是处于MDL锁等待，DDL操作因为没有申请到MDL X锁（因为慢查询占了MDL S锁并且一直没释放所致），所以一直处于等待状态，并不在真正运行。
-
这个MDL 锁等待状态不会一直持续下去，MySQL中通过
lock_wait_timeout参数控制这个超时的时间，bin包中默认配置为120秒，超过120秒还没申请到MDL锁就会抛出错误，让用户感知到。
-
如果想要快速解决DDL无法得到MDL写锁的问题，就可以手动KILL目前占用MDL锁的会话，让DDL获取MDL写锁，比如这里就可以通过
KILL 会话ID的命令或者右击慢查询会话选择结束会话的方式进行KILL。
4.2. 实验二
模拟过程：模拟执行一个慢查询（通过sleep函数），然后进行表上的加列操作，再对这张表进行SELECT查询。
-
当DDL加列操作处于
Waiting for table metadata lock状态时，我们接着去查询emp表，可以看到这个查询也会处于等待MDL锁，虽然这个查询只是申请MDL读锁，并且DDL操作并没有申请到MDL写锁，但还是会处于等待状态，其原理就如之前所说的，MDL锁申请遵循一个队列机制，即使DDL操作并未获取MDL写锁，后续表上的其他操作也会认为DML写锁已经被获取，要等待这个DDL操作结束才可以获取到MDL锁。
-
当DDL加列操作因为长时间未获取MDL X锁而超时后，后面的查询也就可以获取到MDL S锁，然后可以进行查询。
4.3. 实验三
模拟过程：模拟Online
DDL的过程，首先对一张大表进行DDL加列操作，然后对该表进行SELECT慢查询（通过sleep函数延长SQL执行时间）。
-
DDL快速的申请到了MDL写锁后降级为MDL读锁，然后开始重建表过程，这时候可以看到DDL状态为
altering table即正在进行DDL操作的一种正常SQL状态，SELECT查询也处于正常状态，并没有等待MDL锁，因为这时候大家都是持有的MDL读锁，并不存在冲突。
-
过了一会当DDL操作完成后，想要从MDL读锁升级到MDL写锁进行COMMIT时，发现无法申请到，因为这时候SELECT查询还占用着MDL读锁，所以处于
Waiting for table metadata lock状态。这时候如果没有外界干预，要么等待120秒后DDL超时回滚，要么等待SELECT在超时期间内结束，释放MDL锁，这样就可以成功完成DDL操作。
五. 总结
MySQL中DDL操作通过MDL这把锁来保证了表结构与表数据的一致性，而Online DDL特性则使得DDL使用更加方便与轻量。
大家在进行DDL操作后，一定要确认DDL是否处于真正的ALTER状态，还是等待MDL锁的状态，如果是等待MDL锁，则需要找到对应占用MDL锁的会话（通常都是一个或多个对应表上执行很慢的SELECT查询），这时候可以判断是否进行KILL来让DDL正常执行。
最后提醒大家，业务高峰期千万不要进行核心业务表的DDL操作，保不齐MySQL里就存在该表上的一条运行比较慢的SQL或挂起的事务，那么就非常容易引起连锁的MDL锁争用问题，对应表上的业务均会瘫痪掉，连接数暴涨，最后连接池连接达到上限，整个系统也会瘫痪掉。
六. 写在最后
【科普】系列文章主要内容为总结整理数据库方面的常用知识及背后原理，面向所有开发与运维人员，希望以此来增加数据库的知识网络，更好的在项目上使用与管理好数据库。
