【科普】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操作背后的锁原理，希望通过阅读这篇文档，你可以掌握如下两点：

  1. 了解MySQL中的DDL锁机制，理解不要在业务期间进行DDL操作这句话的背后原理。

  2. 知道如何去查看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个步骤：

  1. PREPARE：会申请MDL X锁，然后更新数据字典并分配row_log开始记录表上的DML增量数据，这个过程如果没有被MDL锁阻塞，那么是非常快的。

  2. DDL：将所持有的MDL X锁降级为MDL S锁。

    • 一方面进行数据拷贝，比如建二级索引或者重建原表。

    • 另一方面记录这期间产生的DML日志，写到row_log中后进行数据重放。

  3. 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状态。

  1. 通过dbeaver中的会话管理窗口可以很方便的看到MySQL中的SQL运行状态。

  1. 我们先后对emp表执行了慢查询与加列操作，通过会话窗口可以看到加列的DDL操作处于Waiting for table metadata lock状态，这个状态其实就是处于MDL锁等待，DDL操作因为没有申请到MDL X锁（因为慢查询占了MDL S锁并且一直没释放所致），所以一直处于等待状态，并不在真正运行。

  1. 这个MDL 锁等待状态不会一直持续下去，MySQL中通过lock_wait_timeout参数控制这个超时的时间，bin包中默认配置为120秒，超过120秒还没申请到MDL锁就会抛出错误，让用户感知到。

  1. 如果想要快速解决DDL无法得到MDL写锁的问题，就可以手动KILL目前占用MDL锁的会话，让DDL获取MDL写锁，比如这里就可以通过KILL 会话ID的命令或者右击慢查询会话选择结束会话的方式进行KILL。

4.2. 实验二

模拟过程：模拟执行一个慢查询（通过sleep函数），然后进行表上的加列操作，再对这张表进行SELECT查询。

  1. 当DDL加列操作处于Waiting for table metadata lock状态时，我们接着去查询emp表，可以看到这个查询也会处于等待MDL锁，虽然这个查询只是申请MDL读锁，并且DDL操作并没有申请到MDL写锁，但还是会处于等待状态，其原理就如之前所说的，MDL锁申请遵循一个队列机制，即使DDL操作并未获取MDL写锁，后续表上的其他操作也会认为DML写锁已经被获取，要等待这个DDL操作结束才可以获取到MDL锁。

  1. 当DDL加列操作因为长时间未获取MDL X锁而超时后，后面的查询也就可以获取到MDL S锁，然后可以进行查询。

4.3. 实验三

模拟过程：模拟Online DDL的过程，首先对一张大表进行DDL加列操作，然后对该表进行SELECT慢查询（通过sleep函数延长SQL执行时间）。

  1. DDL快速的申请到了MDL写锁后降级为MDL读锁，然后开始重建表过程，这时候可以看到DDL状态为altering table即正在进行DDL操作的一种正常SQL状态，SELECT查询也处于正常状态，并没有等待MDL锁，因为这时候大家都是持有的MDL读锁，并不存在冲突。

  1. 过了一会当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锁争用问题，对应表上的业务均会瘫痪掉，连接数暴涨，最后连接池连接达到上限，整个系统也会瘫痪掉。

六. 写在最后

【科普】系列文章主要内容为总结整理数据库方面的常用知识及背后原理，面向所有开发与运维人员，希望以此来增加数据库的知识网络，更好的在项目上使用与管理好数据库。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/timePasser-leoli/p/14845168.html