mysql 5.7 执行 truncate 导致夯住

故障描述

• 一台 4C-32GB-500GB 的机器，由于磁盘空间不足，经开发确认可以清空一张 2376w行、20GB 的大表，众所周知truncate最为擅长。但在truncate过程（整个过程耗时7分钟）中，出现 System lock，同时 CPU 升高、应用无法打开。

(root@localhost) [(none)]> select @@version;
+---------------+
| @@version     |
+---------------+
| 5.7.32-35-log |
+---------------+

(root@localhost) [(none)]> show processlist;
+--------+-----------------+-----------+-----------------+---------+----------+---------------------------------------+-----------------------------------+-----------+---------------+
| Id     | User            | Host      | db              | Command | Time     | State                                 | Info                              | Rows_sent | Rows_examined |
+--------+-----------------+-----------+-----------------+---------+----------+---------------------------------------+-----------------------------------+-----------+---------------+
|      1 | event_scheduler | localhost | NULL            | Daemon  | 66780572 | Waiting on empty queue                | NULL                              |         0 |             0 |
|   1610 | system user     |           | NULL            | Connect | 66542373 | System lock                           | NULL                              |         0 |             0 |
|   1611 | system user     |           | NULL            | Connect |        1 | Reading event from the relay log      | NULL                              |         0 |             0 |
|   1612 | system user     |           | NULL            | Connect |       34 | Waiting for an event from Coordinator | NULL                              |         0 |             0 |
|   1613 | system user     |           | NULL            | Connect |       40 | Waiting for an event from Coordinator | NULL                              |         0 |             0 |
|   1614 | system user     |           | NULL            | Connect |       63 | Waiting for an event from Coordinator | NULL                              |         0 |             0 |
|   1615 | system user     |           | NULL            | Connect |      639 | Waiting for an event from Coordinator | NULL                              |         0 |             0 |
|   1616 | system user     |           | NULL            | Connect |     5457 | Waiting for an event from Coordinator | NULL                              |         0 |             0 |
|   1617 | system user     |           | NULL            | Connect |    14361 | Waiting for an event from Coordinator | NULL                              |         0 |             0 |
|   1618 | system user     |           | NULL            | Connect |    15775 | Waiting for an event from Coordinator | NULL                              |         0 |             0 |
|   1619 | system user     |           | NULL            | Connect |    33095 | Waiting for an event from Coordinator | NULL                              |         0 |             0 |
| 460393 | root            | localhost | kids_attendance | Query   |       57 | System lock                           | truncate student_attendance_stats |         0 |             0 |
| 460415 | root            | localhost | NULL            | Query   |        0 | starting                              | show processlist                  |         0 |             0 |
+--------+-----------------+-----------+-----------------+---------+----------+---------------------------------------+-----------------------------------+-----------+---------------+

原理分析

• system lock 表示 TRUNCATE TABLE 正在等待释放相关资源（如 MDL 锁）
• 由于需要 TRUNCATE 的表可能有活跃事务在使用，导致 TRUNCATE 无法立即获取独占MDL锁，从而进入 system lock 状态

官方资料

• MySQL 5.7 的手册有这么一段描述，大致意思是，在具有大型 InnoDB 缓冲池并启用 innodb_adaptive_hash_index 的系统上，由于删除 InnoDB 表的自适应哈希索引条目时发生 LRU 扫描，TRUNCATE TABLE 操作可能会导致系统性能暂时下降。MySQL 5.5.23 中的 DROP TABLE 问题已得到解决（bug#13704145，bug#64284），但对于 TRUNCATE TABLE 仍然是一个已知问题（bug#68184）。

• 以下是 MySQL 8.0 的手册：“之前版本 truncate 的性能问题，8.0 已经将 truncate 映射成了 drop table + create table 避免了这个问题”。

• 一句话总结官方手册的意思：“MySQL 5.5.23 解决了 DROP TABLE 的性能问题，8.0 解决了 TRUNCATE TABLE 的性能问题”。也就是说，MySQL 5.7.x 确实存在 truncate table 导致 MySQL hang 甚至 crash 的问题。

解决方案

基于不同的 mysql 版本，删表的最佳方案可参考如下：

• 5.5.23 之前，建议采用 truncate + drop
• 5.5.23 之后，建议采用 drop
• 8.0 之后，建议采用 truncate

针对 mysql 5.7 大表执行 truncate 需要谨慎操作，务必放到业务低峰期操作，建议使用 drop+create 代替 trucnate。

附：TRUNCATE 与 DELETE 的区别