truncate语法在大buffer pool下面的优化思路
mysql5.7版本仍然是线上普及程度最广泛的版本,在实际的操作过程中,经常会出现truncate table引起的系统TPS、QPS不稳定的现象。
究其原因,truncate操作会删除buffer pool的数据页,在这个过程中,会对buffer pool加锁,从而影响buffer pool的读写访问。相比而言,drop table会比truncate table的操作快很多,其本质原因是truncate table 需要复用数据页的space id, 这导致必须把buffer pool中的老的表中的页全部删除,而drop table因为新旧表的页可用通过space id区分,只需要把flush list中的脏页删除就可以了,也就是可以用drop+create代替truncate来解决大buffer pool下的数据清理问题。
在MySQL8.0中,默认是使用drop+create的代码来实现truncate函数的,在MySQL5.7中,仍然是使用原生truncate方法,这里可以参考MySQL官网的一个bug讨论查看细节:
http://218.206.242.148:12345/images/20_tencent/20220917/uhngzzkoodp.php
在开发的过程中,遇到大表的truncate的时候,不能简单的让DBA将truncate语法转换成drop + create,这两种方式在实现上还是有细微的不同的。表现在:
1、drop + create是2条SQL,中间存在时间间隔,可能导致业务报错
2、这条语句不是statement的
只有业务对于执行中间状态不敏感的时候,才可以使用drop+create的方法来代替truncate。
在MySQL5.7中,如果想要实现truncate的优化,同时为了保证truncate操作的安全,可以在源码中将truncate语句替换成rename+create,rename操作之后再将rename的临时表进行删除,这样可以保证truncate的执行效率。