MySQL DDL 风险评估
一、前言
变更是数据库离不开的话题，从 MySQL 5.6 开始，推出 online DDL 即变更期间不锁表，本篇文章介绍 MySQL 变更对数据库的影响如何去判断。
二、DDL 风险提示
1. 变更速查表
可以参考官方文档，每个变更类型是否支持 online DDL
如何通过变更语句判断对数据库的影响？需要重点关注变更及版本是否支持对应的算法：
INSTANT：算法影响最小，只需要修改元数据表即可，一般瞬间完成；
INPLACE：算法可以避免重建表带来的 IO 和 CPU 消耗，保证 DDL 期间依然有良好的性能和并发；
COPY：算法需要拷贝原始表，期间会带来额外的空间消耗，大表 COPY 也会对服务器的性能带来影响。
一个原则：COPY 算法的 DDL 一定不是 online 的，INPLACE 算法不一定是 online 的。
2. 扩 varchar 长度
有两种场景，需要关注：
锁表场景：varchar 长度 <= 255 字节，扩到 > 255 字节时，不支持 online DDL 仅支持 COPY 算法，变更期间会全程锁表。
不锁表场景：因为 varchar 长度在小于 <= 255 字节时，只使用 1 个字节存储编码，但大于 255 字节时，就需要使用 2 字节存储编码。只要 varchar 长度变更 不涉及编码长度的变化，都支持 online DDL。
关于 MySQL 字符集最大占用多少字节，可参考 SHOW CHARSET;查询结果 Maxlen字段。
root@mysql 10:33: [(none)]>SHOW CHARSET like '%utf8%'; +---------+---------------+--------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+--------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | +---------+---------------+--------------------+--------+
三、DDL 子句设置
我发现开发最关心的还是自己的 DDL 变更期间，DML 语句能否正常执行。业务比较重要且没有计划停机，就算 DBA 跟他们讲放心吧，加字段不会堵塞 DML 的，估计心里依然犯嘀咕的。所以介绍一下 online DDL 的子句。
-- 一个删除字段的变更，添加了 ALGORITHM 和 LOCK 子句 alter table sbtest1 drop column fantasy, ALGORITHM=INPLACE, LOCK=NONE;
ALGORITHM 使用原则：
使用 ALGORITHM 子句可以让我们选择 DDL 的算法可以选择的有三种：INSTANT、INPLACE、COPY 影响在上一单元已经介绍。可以通过查表得到变更支持的算法，然后加入 ALGORITHM 子句，指定 DDL 算法。一般原则支持 INPLACE 就不要选择 COPY 如果支持 INSTANT 那更好机会瞬间完成。如果不指定这些算法 MySQL 也会根据你的 DDL 变更来选择代价最小的算法去执行变更。
LOCK 使用原则：
使用 LOCK 子句可以让我们选择 DDL 是否锁表，共有四种选项：
NONE：允许并发 DML；
SHARED：允许并发查询，
会堵塞 DML；DEFAULT：让 MySQL 自己判断 LOCK 模式，原则是尽可能允许 DML；
EXCLUSIVE：DDL 期间表不可用，如果可以停业务，可以使用该选项；
注意⚠️：如果你选择的算法并不支持 LOCK 子句的选项则会报错，反过来想也是对变更的一种保护。
看上图，我们选择 COPY 算法执行变更 LOCK 选择的是允许并发 DML 则出现报错，不支持。
这个就回到了我刚开始说的，开发想要对大表进行变更，但是又怕锁表，我们可以将 DDL 子句介绍给他们，如果不希望锁表那么 LOCK=NONE 即可，如果出现报错那么就是不支持。
四、MDL 锁
Online DDL 真的不锁表吗？
MySQL 的锁可以分为四类：MDL 锁、表锁、行锁、GAP 锁，其中除了 MDL 锁是在 Server 层加的之外，其它三种都是在 InnoDB 层加的。
下面主要介绍一下：MDL 锁，主要作用就是维护 DDL 数据的安全性 & 正确性。
当对一个表进行 DML 时，需要加 MDL 读锁，当需要对一张表结构进行变更时，需要加 MDL 写锁。读锁之间不互斥，即可以多个线程对一张表进行并发增删改。读写锁与写锁，之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
从上面我们可以了解到，读操作与写操作，都需要加 MDL 读锁，而 DDL 需要加 MDL 写锁，两者互斥的，那么 Online DDL 如何保障并发 DML 呢，这里就要介绍 Online DDL 加锁过程：
首先，在开始进行 DDL 时，需要拿到对应表的
MDL 写锁，然后进行一系列的准备工作；然后
MDL 写锁降级为MDL 读锁，开始真正的 DDL；最后再次将
MDL 读锁升级为MDL 写锁，完成 DDL 操作，释放 MDL 锁；
其中第二阶段占用了 DDL 整个过程的大量时间，在这段时间 DDL 才是真正的 online。
那么问题来了，如果有一个大查询持有 MDL 读锁，那么我们此时进行一个 DDL 操作后，因为查询持有读锁，DDL 需要加写锁，所以变更必须等待查询结束才能进行，此时再进行一个查询就会被卡住，请看案例👇
Session 1 | Session 2 | Session 3 | Session 4 |
begin;select * from sbtest1 limit 5; | |||
select * from sbtest1 limit 5; | |||
alter table sbtest1 drop column fantasy ,ALGORITHM=INPLACE, LOCK=NONE; ❌ | |||
select * from sbtest1 limit 5; ❌ |
Session 1 开启一个事物，执行一条查询，此时 Session 1 持有 MDL 读锁；Session 2 也执行一条查询，执行一条查询，正常执行；Session 3 执行一条 DDL 语句，因为需要 MDL 写锁 被 Session 1 读锁；Session 4 执行一条查询，因为需要读锁，但是因为 Session 3 也处于等待状态，后面的增删改查都处于等待状态。也就是说这张表完全不可用了。
结论：即使变更支持 online 也非常怕长事务，所以生产环境大表变更前，我们可以去查会话和 innodb_trx 表，确认没有长事务，避免变更被 MDL 读锁堵塞。
select * from information_schema.innodb_trx\G
查询活跃会话：
select proc.id, proc.user, proc.HOST, proc.DB, proc.COMMAND, proc.TIME, proc.STATE, proc.INFO, TIMESTAMPDIFF(SECOND, trx.trx_started, now()) as Trx_time from information_schema.INNODB_TRX as trx join information_schema.processlist proc on trx.trx_mysql_thread_id = proc.id;
