关于加索引把数据库搞崩这件事-每日运维

本文基于的数据库环境：MySQL 8.0.12，数据库引擎 InnoDB，MySQL 客户端 IntelliJ IDEA 2021.2.3 Database（重要）

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)，这两中锁都是由 Server层实现的。

表锁

表锁的语法是 lock tables table_name read/write。需要解锁的话，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。这里的锁有两种分别是读锁（read）和写锁（write），简单解释一下这里的读写分别指什么：

读：DML 中的 Select
写：所有的 DDL，和除 Select 以外的 DML

需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的读写。举个例子，如果在某个线程 A 中执行 lock tables t1 read, t2 write ：

线程 A：
- t1：只能执行 Select 操作
- t2：可以执行任意的 DML 和 DDL 操作
其他线程：
- t1：可以执行 Select 操作；可以加读锁（lock tables table_name read）
- t2：DML、DDL 和 lock tables table_name read/write 都会被阻塞

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。

元数据锁（meta data lock，MDL)

在 MySQL 5.5 版本中引入了 MDL，MDL 不需要显式使用，在访问一个表的时候会被自动加上。**MDL 作用是防止 DDL 和 DML 并发的冲突，用于解决或者保证 DDL 操作与 DML 操作之间的一致性。**你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。这里明确 MDL 的几个基本原则：

DML 加会加读锁，DDL 会加写锁

读锁之间共享，因此你可以有多个线程同时对一同张表增删改查。

读写锁之间、写锁之间是互斥的。因此当一个线程对一个表进行 DDL 操作时，其他表对该表的所有 DML 和 DDL 操作都会被阻塞

MySQL 对申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

场景演示

session A	session B	session C	session D
begin;select * from table1 limit 1;
	select * from table1 limit 1;
		alter table1 add column1 int;（阻塞）
			select * from table1 limit 1;（阻塞）

现象的解释：

session A 获取 MDL 读锁，因为开启了事务并且没有 commit，所以会一直持有读锁
session B 获取的 MDL 的读锁，读锁之间不互斥，所以 session B 不会被阻塞
session C 需要获取 MDL 的写锁，因为session A 还在持有读锁，读锁和写锁互斥，所以 session C 会被阻塞等待获取写锁
session D 需要获取读锁，因为 session C 在等待获取写锁，根据上面基本原则的第四条，在 session C 获取写锁之前 session D 也会被阻塞

所以这里就有一个非常值得注意的地方，当 DDL 一个查询 DML 非常频繁的热表时，如果该 DDL 操作被阻塞，那么后面的 DML 操作都会被阻塞，数据库线程可能会被瞬间打满，导致数据库不可用，这是非常危险的。

关于 Online DDL

由于 DDL 执行时如果锁表的话会严重影响性能，不锁表又难搞定操作期间 DML 语句的影响，于是 MySQL 推出了全新的 Online DDL概念， Online DDL 中的 ”Online“ 可以简单理解为，在 DDL 的过程中其他的线程是否可以进行 DML，如果可以那么就是 “Online” 的。

在执行 DDL 操作时，可以通过从句 ALGORITHM=? 指定 DDL 执行时使用的算法，自 MySQL 8.0.12 起，支持的算法有三种：COPY、INPLACE 和 INSTANT，当然如果不指定 ALGORITHM ，DDL 默认的算法就是 ALGORITHM=DEFAULT， MySQL按照 INSTANT、INPLACE 和 COPY 的顺序自动选择合适的算法，如果你通过 ALGORITHM 指定了 DDL 不支持的算法，MySQL 会抛出异常。

算法	说明
COPY	MySQL 5.6 之前使用的算法，所有 DDL 操作都支持该种算法，最原始的方式，操作时会创建临时表，执行全表拷贝和重建，如果采用这种算法的 DDL 期间不能进行并发的 DML，是三种算法中性能最低的，不是一种 “Online” 的算法。
INPLACE	INPLACE：MySQL 5.6 出现的新算法，该算法尽可能避免表拷贝和重建，而是在让引擎层就地重新生成表，该算法仅在 DDL 开始和结束获取写锁时阻塞其他线程，其他时候都是可以进行并发 DML 的，该算法是一种 “Online” 算法。
INSTANT	INSTANT：MySQL 8.0.12 提出的新算法，只需修改数据字典中的元数据，无需拷贝数据也无需重建整表，同样，也无需加排他 MDL 锁，原表数据也不受影响。整个DDL过程几乎是瞬间完成的，也不会阻塞 DML，目前只支持少数几种DDL 操作，是三种算法中效率性能最高的算法，是一种 “Online” 的算法。

COPY 执行的时候全程不能并发 DML，并且设计的表的拷贝和重建，效率最低。INSTANT 仅在最开始的时候会获取 DML 写锁但是在获取锁后仅仅只做元数据的修改，整个过程几乎时瞬间完成的，效率最高。而 INPLACE 算法比较特殊，这里着重说一下他是怎么实现“ Online” 的：实际上 DDL 操作在执行时，不管何种算法，都会经历三个阶段：准备阶段（prepare）、执行阶段（DDL）和提交阶段（commit），INPLACE 实现 Online DDL 的过程可以简单概况为一下这 6 步：

拿 MDL 读锁

升级成 MDL 写锁

降级成 MDL 读锁

执行 DDL（真正耗时的地方）

升级成 MDL 写锁

释放 MDL 写锁

可以看到当真正进行耗时的 DDL 操作时，降级成了读锁，其他线程可以对该表进行 DML，大大降低了 DDL 对该表的影响，提高该表的并发。

可是我怎么知道哪种 DDL 支持哪种算法，并且是否是 ”Online“ 的呢？

其实 MySQL 的官方给出所有 DDL 支持哪种算法，是否是 ”Online“ 的说明，以创建或者添加二级索引为例，这里可以看到，他是不支持 INSTANT 算法，但是支持 INPLACE 算法，并且他是 ”Online“ 的。

特别说明：

COPY 算法一定不是 Online 的

INPLACE 不一定是 Online 的（大部分都是 Online 的），比如说上图的 Adding a FULLTEXT index 操作，虽然支持 INPLACE ，但是他却不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况

INSTANT 算法一定是 Online 的

性能对比

这里我们对比了了给一张有 100 万条记录的表添加一列 3 种算法所花费的时间。

COPY – 10.34 sec

mysql> alter table test add co1 varchar(100) not null default 0, algorithm=copy;
Query OK, 1000000 rows affected (10.34 sec)

INPLACE 5.02 sec

mysql> alter table test add co1 varchar(100) not null default 0, algorithm=inplace;
Query OK, 0 rows affected (5.02 sec)

INSTANT – 0.06 sec

mysql> alter table test add co1 varchar(100) not null default 0, algorithm=instant;
Query OK, 0 rows affected (0.06 sec)

场景演示

这里以添加列演示一下 INSTANT 和 INPLACE 的区别。

根据官网的说明，Adding a column（添加列） DDL INSTANT 和 INPLACE 这两中算法都支持，并且是 Online 的。

下面的两个例子演示了，当 session A commit 后，session B 和 session C 的阻塞状态。

INSTANT

session A	session B	session C
begin;select * from table1 limit 1;
	alter table1 add column1 int;（阻塞）
		begin;select * from table1 limit 1;（阻塞）
commit;
	执行	执行

跟据上文可知，这里我们没有指定 ALGORITHM 从句，那么该条语句 ALGORITHM=DEFAULT，MySQL按照 INSTANT、INPLACE 和 COPY 的顺序自动选择合适的模式，所以这里使用的算法就是 INSTANT，可以看到的是，使用 INSTANT 算法的 DDL 过程几乎是瞬间完成的，也不会阻塞 DML。

INPLACE

TIME	session A	session B	session C
T1	begin;select * from table1 limit 1;
T2		alter table1 add column1 int, ALGORITHM=INPLACE;（阻塞）
T3			begin;select * from table1 limit 1;（阻塞）
T4	commit;
T5			执行
T6		阻塞

因为 Adding a column 操作默认会使用 INSTANT 算法，所以这里手动指定了 DDL 的算法为 INPLACE（ALGORITHM=INPLACE）。

这里解释一下为什么 session B 会被阻塞。

再 session A commit 后，因为写锁的优先级大于读锁，所以 session B 会先拿到 MDL 写锁

然后 session B 降级成 MDL 读锁

因为读锁共享，所以 session C 拿 MDL 读锁，执行查询

session B 真正做 DDL

session B 执行完 DDL 后需要升级成 MDL 写锁，但是因为 session C 的事务没有提交，所以没有放弃 MDL 读锁，又因为读写锁互斥，所以这里 session B 就被阻塞了

思考一下如果在 session B 被阻塞（T6 时刻）的时候，来了个 session D 他想要执行了一次 DML，会发生什么？答案是，他也会被阻塞，具体原因相信聪明的你通过上面的内容肯定知道答案了。

踩坑记录：

在我使用 Navicat 12 for MySQL 客户端测试 INSTANT 这个例子的时候，在 session A commit 后，session B 和 session C 还是阻塞状态（就好像发生了死锁），如果你也又这种情况，请更换客户端：）

一个 Online 的 DDL 总是 Online 吗？

这里以拓展 VARCHAR 列的大小（Extending VARCHAR column size）操作为例，这里可以看到他是支持 INPLACE 算法，并且是 Online 的，那么什么情况会导致该操作发生 “不再 Online ” 呢？

根据官方的说明，对于大小为 0 到 255字节的 VARCHAR 列，需要一个字节长度来编码值。对于大小为 256 字节或更大的 VARCHAR 列，需要两个长度字节。那么当你需要将 VARCHAR 列的大小由 0 ~ 255 拓展到 256 甚至更大（也就是需要改变该列的编码长度时)，该 DDL 的执行算法会由 INPLACE 退化成 COPY，也就是会导致该 DDL 变成一个非 Online 的操作，如果你强行指定 ALGORITHM=INPLACE ，那么 MySQL 会返回异常：ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

缩小 VARCHAR 列的大小的 DDL，只支持 COPY 算法。

常见的 DDL 操作 Online DDL 支持情况

更多请见官网：dev.mysql.com/doc/refman/…

二级索引

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
创建或者添加二级索引	❌	✅	❌	✅	❌
删除索引	❌	✅	❌	✅	✅
重命名索引	❌	✅	❌	✅	✅
添加 `FULLTEXT` 索引	❌	✅	❌	❌	❌
添加 `SPATIAL` 索引	❌	✅	❌	❌	❌
修改索引类型	✅	✅	❌	✅	✅

普通列

操作	INSTANT	INPLACE	重建表	并发 DML	只修改元数据
列添加	✅	✅	❌	✅	❌
列删除	❌	✅	✅	✅	❌
列重命名	❌	✅	❌	✅	✅
改变列的顺序	❌	✅	✅	✅	❌
设置默认值	✅	✅	❌	✅	✅
修改数据类型	❌	❌	✅	❌	❌
扩展 `VARCHAR` 长度	❌	✅	❌	✅	✅
删除列的默认值	✅	✅	❌	✅	✅
设置列为 NULL	❌	✅	✅	✅	❌
设置列为 NOT NULL	❌	✅	✅	✅	❌

如何安全地给小表加字段

长事务

首先长事务如果不提交，就会一直占着 MDL 锁，导致阻塞 DDL，进而阻塞后续的 DML 。在 MySQL 的 information_schema 库的 innodb_trx 表中，通过 SELECT * FROM information_schema.innodb_trx，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

热点表

首先肯定是尽量挑热点表业务低峰期时进行 DDL，但是有时虽然请求很频繁，而你又不得不加个字段，比较理想的机制是，在 alter table 时，设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

-- MySQL 的 MariaDB 分支版本才支持这种写法
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

也可以通过下面这种方式实现：

-- 单位 秒,只在当前会话中生效
set lock_wait_timeout=10
ALTER TABLE auth_account ADD ...

事务或者锁的相关排查命令：

SELECT * FROM information_schema.INNODB_TRX;

show processlist;

select * from performance_schema.metadata_locks;

SELECT * FROM sys.schema_table_lock_waits

KILL 120

SELECT version()

其他

其实上面的方法并没有完全解决给热表 DDL 的问题，现在有许多开源的可以在线更改 MySQL 表结构的工具，比如说 gh-ost、pt-osc 等，这里就不在赘述了，感兴趣的可以研究一下。

参考资料

dev.mysql.com/doc/refman/…
dev.mysql.com/doc/refman/…
dev.mysql.com/doc/refman/…
mydbops.wordpress.com/2020/03/04/…
www.cnblogs.com/zmc60/p/148…
juejin.cn/post/688780…
blog.csdn.net/weixin_4523…
time.geekbang.org/column/arti…
time.geekbang.org/column/arti…
blog.csdn.net/Hehuyi_In/a…

关于加索引把数据库搞崩这件事

场景演示

可是我怎么知道哪种 DDL 支持哪种算法，并且是否是 ”Online“ 的呢？

性能对比

场景演示

INSTANT

INPLACE

一个 Online 的 DDL 总是 Online 吗？

常见的 DDL 操作 Online DDL 支持情况

二级索引

普通列

长事务

热点表

相关文章

发布评论取消回复

宇宙之一粟

VirtualBox 7.1 Beta 发布：采用全新现代化 UI、更换 Logo

兼容MySQL程度最高的国产数据库来了

Llama 3.1 – 405B、70B 和 8B 的多语言与长上下文能力解析

关于加索引把数据库搞崩这件事

场景演示

可是我怎么知道哪种 DDL 支持哪种算法，并且是否是 ”Online“ 的呢？

性能对比

场景演示

INSTANT

INPLACE

一个 Online 的 DDL 总是 Online 吗？

常见的 DDL 操作 Online DDL 支持情况

二级索引

普通列

长事务

热点表

相关文章

发布评论 取消回复

宇宙之一粟

VirtualBox 7.1 Beta 发布：采用全新现代化 UI、更换 Logo

兼容MySQL程度最高的国产数据库来了

Llama 3.1 – 405B、70B 和 8B 的多语言与长上下文能力解析

发布评论取消回复