mysql del table_MySQL truncate table 与 delete 清空表的区别和坑

truncate 的问题

以前使用 delete from，感觉耗时较多，所以就采用了 truncate。

后来发现阻塞读，本篇简单记录下采坑之路。

truncate 的阻塞原理

truncate 应慎重，它属于ddl，会lock table meta data，甚至可能由锁表升级为锁库

5.7 及其以前

In MySQL 5.7 and earlier, on a system with a large buffer pool and innodb_adaptive_hash_index enabled, a TRUNCATE TABLE operation could cause a temporary drop in system performance due to an LRU scan that occurred when removing the table's adaptive hash index entries (Bug #68184). The remapping of TRUNCATE TABLE to DROP TABLE and CREATE TABLE in MySQL 8.0 avoids the problematic LRU scan.

这个会导致阻塞

8.0

参考文档，会将 truncate 调整为 drop+create。

中间过程会导致问题。

实际上 drop 也是有会阻塞查询的。

drop 阻塞读

drop table引起的MySQL 短暂hang死的问题，是由于drop 一张使用AHI空间较大的表时，调用执行AHI的清理动作，会消耗较长时间，执行期间长时间持有dict_operation_lock的X锁，阻塞了其他后台线程和用户线程;

drop table执行结束锁释放，MySQL积压的用户线程集中运行，出现了并发线程和连接数瞬间上升的现象。规避问题的方法，可以考虑在drop table前关闭AHI。

ps: AHI 是自适应 HASH 索引，一种优化策略。

参考资料