好文推荐 | MySQL 的 truncate/drop table 问题通关了吗？

• 以「官方手册」为引

• 从「历史bug」里找线索

• 小结-MySQL5.5.23惰性删除策略

• 未解决的疑问-MySQL5.7究竟还做了什么优化？

• 事情还没完，MySQL8.0.0-8.0.23：我胡汉三又回来了

• 8.0.23 帽子戏法：我还没浪够

• MySQL8.4：要不你别用AHI了

一句话总结官方手册的意思：“MySQL 5.5.23解决了 DROP TABLE 的性能问题，8.0 解决了 TRUNCATE TABLE 的性能问题”。但显然这不是事实，在我们的体验中，无论是MySQL5.7还是8.0都依然存在 truncate/drop table 导致的MySQL hang甚至crash。

那么问题来了，为什么官方手册会这么写？为什么性能问题依然存在？ 

1. bug#64284 & bug#51325

5.7手册里中正式提到的 bug 是：https://bugs.mysql.com/bug.php?id=64284，但更详细的讨论应该看这个 bug：https://bugs.mysql.com/bug.php?id=51325。

bug 指出 drop table 会做两次 LRU 扫描：一次是从 LRU list 中删除表的数据页，一次是删除表的 AHI 条目。

优化后变成：扫描一次 LRU list 来删除 AHI；扫描一次 flush list 来删除表的数据页；并且buffer pool mutex 会定期释放，不再是一直持有直到扫描完 LRU list（这样持有 mutex 的时间太长，会阻塞其他请求）。

其中 bug#51325 中有些讨论可以帮助我们理解这个bug。

这条评论的大意是对于 60G 的缓冲池和其中分配的约 320 万页，删除一个空的 innodb 表会将缓冲池互斥锁锁定约 0.5 秒，因为它会扫描 LRU 两次：

• buf_LRU_invalidate_tablespace 会扫描一次LRU list，需要从adaptive hash中删除对要删除的表的page的引用；

• buf_LRU_invalidate_tablespace 调用的 buf_LRU_drop_page_hash_for_tablespace 会扫描一次LRU list：如果是dirty block，需要从flush list remove掉，然后从page hash中删除，最后从LRU list中删除。

这条评论是关于优化方案：

• 采用了惰性删除的思路，通过扫描 flush list 来删除对应的 page（替换了扫描 LRU list，因为 flush list 通常比 LRU list 小的多）；
• 但是 LRU 的扫描只是减少了一次，并没有完全消除，删除AHI条目还是要扫一次LRU List；
• buffer pool mutex 会定期释放，不再是一直持有直到扫描完 LRU list（这样持有 mutex 的时间缩短，如果删除操作需要很长时间，其他线程可以同时工作）。

2. bug#61188

https://bugs.mysql.com/bug.php?id=61188，这个bug指出对于分区表，drop table 会删除多个分区，删除每个分区时都是扫描 LRU list 两次，放大了 bug#51325 的问题。

3. bug#68184

https://bugs.mysql.com/bug.php?id=68184，这个bug 说的是 truncate table 会扫描 LRU 来删除 AHI，导致性能下降；8.0 已修复，方法是将 truncate 映射成 drop table + create table。

注意：这个bug 的开头和结尾都说是因为扫描 LRU 删除 AHI 导致的，甚至官方文档中说的也是“由于删除 InnoDB 表的自适应哈希索引条目时发生 LRU 扫描，可能会导致系统性能暂时下降”。

这里存在疑问，因为从 bug#68284 和 bug#51325 我们得知其实主要还是因为扫描 LRU 删除 page，并且持有 buffer pool mutex 时间过长，5.5.23 修复后还是得扫描 LRU 删除 AHI。

其中有一个评论：truncate table 会重新为表分配一个 space id，因此这张表的 page 留在 LRU list 中是没有问题的（这些 page 的 space id 是旧的，不会被读取到），所以可以和 drop table 一样使用惰性删除进行优化。

备注：这个说法我怀疑是因果倒置了。要知道 MySQL5.7中 truncate table 是不会重新分配 space id 的，因此需要同步删除 LRU list 中的page。再来看 8.0 将 truncate 映射成 drop table + create table，这是为了解决同步删除 page 的问题，8.0 中 truncate table 会重新分配 space id 这件事只是一种修复后的结果。

4. bug#91977

https://bugs.mysql.com/bug.php?id=91977，这个bug说的是 drop table 扫描 LRU 删除 AHI 导致信号量等待，造成长时间的阻塞。这个其实就是 5.5.23 修复方案中没能解决的问题：LRU 的扫描只是减少了一次，并没有完全消除。

5. bug#98869

https://bugs.mysql.com/bug.php?id=98869，这个bug指出虽然 8.0 依旧修复了 truncate table 的问题，但是对于一些查询产生的磁盘临时表（innodb 表），在临时表被删除时，还是会有同样的问题。这个bug在8.0.23中得到修复。

当时 drop table 的实现中，需要同步删除 LRU list 中表的 page 和 AHI 条目，需要扫描两次 LRU list，持有 buffer pool mutex 直到结束。这在 innodb buffer pool 很大时会很慢，并且由于 buffer pool mutex 会导致其他线程基本上处于堵塞状态，MySQL hang。

在 5.5.23 中用惰性删除策略做了一次优化：

• 扫描 flush list（ 因为 flush list 通常比 LRU list 小的多）删除对应的 page，而 LRU list 上的 page 则依靠 LRU 算法慢慢失效；

• 但是 LRU 的扫描只是减少了一次，并没有完全消除，删除AHI条目还是要扫一次LRU List；

• buffer pool mutex 会定期释放，不再是一直持有直到扫描完 LRU list（这样持有 mutex 的时间缩短，如果删除操作需要很长时间，其他线程可以同时工作）。

对于分区表，删除每个分区时都会扫描 LRU List，问题更严重。

1. 删除 buffer pool 中表的索引页，以及索引页的 AHI
   a. 不扫描 LRU list，扫磁盘表空间可以得知索引页在 buffer pool 中的位置，如果索引页的记录在 AHI 中，继续到 AHI 中删除对应记录
2. 删除 buffer pool 中表的数据页
   a. 扫描 flush list，同步删除表的数据页
   b. LRU list 上的 page 通过 LRU 算法慢慢失效

在 MySQL5.7 中，drop table 确实不需要扫描 LRU。如果表小，扫磁盘表空间很快，比扫 LRU list 成本低很多；如果表很大，扫磁盘表空间也会很耗时，与直接扫LRU相比优势不大。所以其实问题并没有完全解决。

事情还没完-MySQL8.0.0-8.0.23：我胡汉三又回来了

MySQL8.0.0 确实将 truncate 映射成 drop table + create table 了，因此 truncate table 的问题终于也解决了。但是对 AHI 的处理上，又放弃了 5.7 的优化，还是要扫描 LRU list，如果索引页在 AHI 中，则删除对应的 AHI 记录。这个改动似乎回到了 5.5.23，对于小表来说，也要扫描整个 LRU list，成本比较高。