在我最近的项目中，经常会有给大表加字段的需求，这个过程非常耗时。文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
可以看到，900 万数据量的一张表，加一个字段就需要 3 个小时左右。文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
我们知道，给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。假设表数据量比较大，加字段的过程将会非常耗时。文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
不过我最关心的是，在加字段的过程中，会不会对业务的增删改查造成影响？在询问 DBA 后，他给出的答复是不会造成影响。这不禁让我思考这背后的实现原理。文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
下面，我们就来一探究竟。文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
一、MDL（metadata lock)
我们可以想象这样一个场景：一个线程正在遍历查询一个表中的数据，而执行期间另一个线程对这个表结构做变更，添加了一列，那么查询线程拿到的结果跟表结构对不上，这肯定是不行的。文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
MySQL 是如何解决这个问题的？答案是：在 5.5 版本中引入了 MDL（metadata lock) 元数据锁。文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
当对一个表做增删改查操作的时候，加 MDL 读锁；读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。文章源自灵鲨社区-https://www.0s52.com/bcjc/mysqljc/17485.html
当要对表做结构变更操作的时候，加 MDL 写锁。读写锁之间、写锁之间都是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
如果是在事务中，在语句执行开始时申请 MDL 锁，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。这点要特别注意！（本文第三部分有具体的案例说明）
知道了这个概念后，回到问题本身：给表加字段需要经过哪些过程？
二、给表加字段的流程
2.1 你会怎么做？
假设现在有一张表 A，需要加一个字段 d，你会怎么做呢？
你可以新建一个与表 A 结构相同的表 B，然后在表 B 加上字段 d，由于表 B 是新建的表，所以加字段耗时很小。接下来，你需要把数据一行一行地从表 A 里读出来再插入到表 B 中。最后，用表 B 替换 A，从效果上看，就起到了对表 A 加字段的作用。
在 MySQL 5.5 版本之前，alter table 命令的执行流程跟上述的差不多， MySQL 会创建好临时表 B，并自动完成转存数据、交换表名、删除旧表的操作。
显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有增删改查操作。
在 MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。
2.2 Online DDL
引入了 Online DDL 之后，加字段的流程如下：
- 建立一个临时文件；
- 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
- 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
- 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
- 用临时文件替换表 A 的数据文件。
流程优化后，由于日志文件记录和重放这两个功能的存在，这个方案在加字段的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。
执行 DDL 之前是要拿 MDL 写锁的，MDL 读锁会阻塞，这样还能叫 Online DDL 吗？
确实，上述流程中，alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。表 A 的 alter 语句，可以拆成以下三步来执行：
- 第一步，创建表 B；
- 第二步，读表 A 数据；
- 第三步，将数据写入表 B；
在第二步的时候，实际上表 A 的锁已经是 MDL 读锁了，MDL 读锁不会阻塞增删改操作。
对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。
需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。
三、给小表加字段
3.1 容易出现的问题
在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出问题。我们来看一下下面的操作序列，假设表 t 是一个小表。
备注：这里的实验环境是 MySQL 5.6。
我们可以看到 session A 在启动前开启了一个事务，在 session A 执行的时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。
之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。
如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁，且客户端还有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。
所以在事务中执行 DDL 语句要特别注意。
3.2 如何安全地给小表加字段？
首先我们要解决长事务，事务不提交，就会一直占着 MDL 写锁。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。
但如果你要变更的表无法避免地会有长事务，而你又不得不加个字段，你该怎么做呢？
如果这张表的请求很频繁，这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。
sql
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
之后开发人员或者 DBA 再通过重试命令重复这个过程。
MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
评论