MySQL 变更 DDL 杂谈_ddl变更

MySQL 5.6 版本开始支持 Online DDL 即在进行 DDL 变更过程中允许并发 DML 操作，今天一起聊聊 DDL 那些事～

一、各版本对 DDL 支持

下面表格根据官方文档对 Online DDL 支持汇总，表格来源于《淘宝数据库内核月报》

可以看到 DDL 算法主要分三种 INSTANT、INPLACE、COPY（重建表）
INSTANT 算法影响最小，只需要修改元数据表即可，一般瞬间完成；
INPLACE 算法可以避免重建表带来的 IO 和 CPU 消耗，保证ddl期间依然有良好的性能和并发；
COPY 算法需要拷贝原始表，期间会带来额外的空间消耗，大表 COPY 也会对服务器的性能带来影响。

所以大表 DDL 需要先调研清楚影响，根据数据库目前的状态，决定是否可以执行。

记住一个原则：
COPY 算法执行的 DDL 肯定不是 Online 的；
INPLACE 算法执行的 DDL 不一定是 Online 的；

二、DDL 子句设置

我发现开发最关心的还是自己的 DDL 变更期间，DML 语句能否正常执行。业务比较重要且没有计划停机，就算 DBA 跟他们讲放心吧，加字段不会堵塞 DML 的，估计心里依然犯嘀咕的。所以介绍一下 online DDL 的子句。

-- 一个删除字段的变更，添加了 ALGORITHM 和 LOCK 子句
alter table sbtest1 drop column fantasy, ALGORITHM=INPLACE, LOCK=NONE;
1
2

ALGORITHM 使用原则：

使用 ALGORITHM 子句可以让我们选择 DDL 的算法可以选择的有三种：INSTANT、INPLACE、COPY 影响在上一单元已经介绍。可以通过查表得到变更支持的算法，然后加入 ALGORITHM 子句，指定 DDL 算法。一般原则支持 INPLACE 就不要选择 COPY 如果支持 INSTANT 那更好机会瞬间完成。如果不指定这些算法 MySQL 也会根据你的 DDL 变更来选择代价最小的算法去执行变更。

LOCK 使用原则：

使用 LOCK 子句可以让我们选择 DDL 是否锁表，共有四种选项：

1. NONE：允许并发 DML；
2. SHARED：允许并发查询，会堵塞 DML；
3. DEFAULT：让 MySQL 自己判断 LOCK 模式，原则是尽可能允许 DML；
4. EXCLUSIVE：DDL 期间表不可用，如果可以停业务，可以使用该选项；

注意⚠️：如果你选择的算法并不支持 LOCK 子句的选项则会报错，反过来想也是对变更的一种保护。

看上图，我们选择 COPY 算法执行变更 LOCK 选择的是允许并发 DML 则出现报错，不支持。

这个就回到了我刚开始说的，开发想要对大表进行变更，但是又怕锁表，我们可以将 DDL 子句介绍给他们，如果不希望锁表那么 LOCK=NONE 即可，如果出现报错那么就是不支持，相当于一个保障。

三、Online DDL 真的不锁表吗？

MySQL 的锁可以分为四类：MDL 锁、表锁、行锁、GAP 锁，其中除了 MDL 锁是在 Server 层加的之外，其它三种都是在 InnoDB 层加的。

下面主要介绍一下：MDL 锁，主要作用就是维护 DDL 数据的安全性 & 正确性。

当对一个表进行 DML 时，需要加 MDL 读锁，当需要对一张表结构进行变更时，需要加 MDL 写锁。读锁之间不互斥，即可以多个线程对一张表进行并发增删改。读写锁与写锁，之前是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。 – 这段来源《MySQL 45 讲》

从上面我们可以了解到，读操作与写操作，都需要加 MDL 读锁，而 DDL 需要加 MDL 写锁，两者互斥的，那么 DDL 如何保障并发 DML 呢，这里就要介绍 DDL 加锁过程：

1. 首先，在开始进行 DDL 时，需要拿到对应表的 MDL 写锁，然后进行一系列的准备工作；
2. 然后MDL 写锁降级为MDL 读锁，开始真正的 DDL；
3. 最后再次将 MDL 读锁升级为 MDL 写锁，完成 DDL 操作，释放 MDL 锁；

其中第二阶段占用了 DDL 整个过程的大量时间，在这段时间 DDL 才是真正的 online。

那么问题来了，如果有一个大查询持有 MDL 读锁，那么我们此时进行一个 DDL 操作后，因为查询持有读锁，DDL 需要加写锁，所以变更必须等待查询结束才能进行，此时再进行一个查询就会被卡住，请看案例