phpmysqltruncate技巧_MySQL 中 TRUNCATE 语句到底因何而慢

作者：李锡超

一个爱笑的江苏苏宁银行 数据库工程师，紧张卖力数据库日常运维、自动化培植、DMP 平台运维。
善于 MySQL、Python、Oracle，爱好骑行、研究技能。

本文来源：原创投稿

解释

数据库版本

MySQL 5.7

参数 long_query_time <br/> 慢查询阈值，单位为秒

0.1（100 毫秒）

参数 innodb_adaptive_hash_index

ON

总结下来紧张有如何几个问题：

关键堆栈：

关键操作 debug：

为什么实行韶光长？

从以上堆栈可以看到，耗时过程紧张是 row_drop_table_for_mysql、os_file_delete_func。

个中：row_drop_table_for_mysql 紧张是调用 btr_drop_ahi_for_table 实行 AHI 的 page 页的删除。
os_file_delete_func 紧张调用 unlink 实行文件的清理。

如果须要 truncate 的表分配的 fd 为 43，truncate 过程中，会先将表 rename。
这个时候这个 fd 会被关闭，43 就被开释了。
然后实行 create table 操作。
一样平常这个间隙过程很短，因此新建立的表可以利用被开释的 43 了，以是会看到 fd 没有变革。

如果 rename 之后，在内部实行 create table 之前，又打开了新文件，那这时候 fd 43 就会被其它打开的文件持有，truncate 之后表的 fd 也就会发生变革。

把稳：MySQL 8.0 是真正利用 rename + create + drop实现的 truncate，但 MySQL 5.7 是通过文件的 truncate 实现的。

只能看到慢的结果，无法确认缘故原由。

不支持 truncate 语句。

从 profile 结果来看，对付 truncate 语句，只能看到耗时过程都在System lock 上，无法看到更近一步的缘故原由。

办法四：DEBUG

// 推举设置// 个中 T 实在是 MySQL 支持（在 trace 中打印韶光）的，但官方文档中短缺相识释。
已提交bug解释：Bug #111174set global debug='d:t:T:i:n:N:o,/tmp/debug_3306.trace.f';set global debug='';

// TRUNCATE// 默认规范配置// innodb_flush_method = on & innodb_flush_method = O_DIRECT(root@127.1) [eolbimsdb] 08:44:46 15> truncate table t5;Query OK, 0 rows affected (0.98 sec)// 设置 innodb_adaptive_hash_index = off(root@127.1) [eolbimsdb] 08:52:03 5> truncate table t5;Query OK, 0 rows affected (0.03 sec)// 设置 innodb_flush_method = fsync(root@127.1) [eolbimsdb] 09:03:34 28> truncate table t5;Query OK, 0 rows affected (1.04 sec)// 设置 innodb_adaptive_hash_index = off & innodb_flush_method = fsync(root@127.1) [eolbimsdb] 09:20:24 5> truncate table t5;Query OK, 0 rows affected (0.22 sec)// DROP// 默认规范配置// innodb_flush_method = on & innodb_flush_method = O_DIRECT(root@127.1) [eolbimsdb] 10:05:41 9> drop table t5;Query OK, 0 rows affected (0.94 sec)// 设置 innodb_adaptive_hash_index = off & innodb_flush_method = O_DIRECT(root@127.1) [eolbimsdb] 09:44:24 5> drop table t5;Query OK, 0 rows affected (0.01 sec)// 设置 innodb_flush_method = on & innodb_flush_method = fsync(root@127.1) [eolbimsdb] 09:32:15 13> drop table t5;Query OK, 0 rows affected (1.13 sec)// 设置 innodb_adaptive_hash_index = off & innodb_flush_method = fsync(root@127.1) [eolbimsdb] 09:25:10 14> drop table t5;Query OK, 0 rows affected (0.19 sec)Q3: 能否优化？慢在哪里？post_ddl 如何调用？

从 Q1 的结果中可以看出，实行的紧张耗时在 row_drop_table_for_mysql、os_file_delete_func：

MySQL 8.0 的优化方法row_drop_table_for_mysql 慢的问题，可以通过设置 innodb_adaptive_hash_index = off 进行优化；os_file_delete_func 慢的问题，可以设置 innodb_flush_method = O_DIRECT 或者配置表的 HARD LINK 进行优化。
MySQL 5.7 的优化方法

详见后面 3-Q1、3-Q4 部分。

MySQL 8.0 引入了 scope guard 功能：当定义了 scope guard 之后，会创建 Scope_guard 工具。
正常情形下，当实行 return 操作前，会实行 scope guard 定义的逻辑。
除非在函数结束前实行 Scope_guard 工具的 commit 操作。
文件的删除功能实在 scope guard 的 cleanup_base 阶段调用是现的。

从实现机制来看，紧张有以下风险：

当触发 truncate 操作后，须要在短韶光由数据库线程将文件 unlink 或 truncate，如果被处理的文件很大，做事器的 IO 压力可能会影响正常的数据库要求。

在实行 truncate、drop 的过程中，由于须要对内存的数据进行清理，特殊是对 LRU 和 flush_LRU 进行扫描，并开释对应的数据块。
这个过程是须要逐个根据 buffer pool instance 获取 mutex 资源的。
如果在业务高峰期，特殊是 buffer pool 较大时，可能会影响正常的业务情形。

同时，实行 create drop table 操作时须要 dict_operation_lock 的 X 锁（RW_X_LATCH），而一些其他后台线程，比如 Main Thread 检讨 dict cache 时，也须要获取 dict_operation_lock 的 X 锁，因此被壅塞。
然后用户线程可能由于获取不到锁而处于挂起状态，当无法急速得到锁时。
更多参考：《Drop Table 对 MySQL 的性能影响剖析》。

MySQL 8.0 的 truncate 实现办法基本和 drop 实现办法相同，包括紧张的耗时位置（都在 row_drop_table_for_mysql、os_file_delete_func）都是相同的。

MySQL 5.7 的 truncate 和 drop 实现差异较大，全体实现过程险些是完备独立的代码。
truncate 利用 row_truncate_table_for_mysql，drop 利用 row_drop_table_for_mysql；truncate 操作的紧张的耗时有 dict_drop_index_tree、os_file_truncate。

下面来看一个 MySQL 5.7 测试环境上线 DROP TABLE 优化方案失落败问题。

设置 innodb_flush_method=O_DIRECT 的优化操作，同样适用于 MySQL 8.0。

根据源码，MySQL 是否记录慢查询判断时，紧张有两个维度：一个是实行韶光（不包括 utime_alter_lock）；一个是实行扫描的行数，并对分外的语句（如 call）进行了忽略。
对付 truncate 操作而言，无论实行韶光是多少，扫描行数都是 0。
当配置了 min_examined_row_limit 大于 0 之后，一样平常 truncate 操作由于不知足该条件，都不会被记录到慢查询。

但是当 truncate 操作位于存储过程中时，在 truncate 操作之前有其它 DML 操作（如 insert selecct），这时候由于位于同一个 THD 下，在 MySQL 5.7 版本里面 thd->get_examined_row_count() 返回的结果实在是上一个 DML 语句的（这里该当是毛病）。
如果此时 truncate 操作的实行韶光又超过了 long_query_time，那么此时这个 truncate 语句就会被记录慢查询。

同时，在 MySQL 8.0 针对 call 的语句，将不在单独记录记录的语句。
而是记录为统一的 call 语句里面。
须要看存储过程里面的语句实行情形，可以用 show profiles 查看。

DROP PROCEDURE truncate_test;DELIMITER //CREATE PROCEDURE truncate_test()BEGIN insert into t1 select from t1_bak; truncate table t1;END//DELIMITER ;call truncate_test();mysql> call truncate_test();Query OK, 0 rows affected (1 min 59.58 sec)# Time: 2023-06-08T00:28:30.969993+08:00# User@Host: root[root] @ localhost [] Id: 2# Schema: db2 Last_errno: 0 Killed: 0# Query_time: 119.177518 Lock_time: 0.000233 Rows_sent: 0 Rows_examined: 131072 Rows_affected: 131072# Bytes_sent: 0# Stored_routine: db2.truncate_testuse db2;SET timestamp=1686155310;insert into t1 select from t1_bak;# Time: 2023-06-08T00:28:31.375873+08:00# User@Host: root[root] @ localhost [] Id: 2# Schema: db2 Last_errno: 0 Killed: 0# Query_time: 0.405734 Lock_time: 0.003310 Rows_sent: 0 Rows_examined: 131072 Rows_affected: 0# Bytes_sent: 0# Stored_routine: db2.truncate_testSET timestamp=1686155311;truncate table t1;

mysql> call truncate_test();Query OK, 0 rows affected (2 min 28.51 sec)# Time: 2023-06-07T17:18:39.215632Z# User@Host: root[root] @ localhost [] Id: 8# Query_time: 148.516478 Lock_time: 0.000372 Rows_sent: 0 Rows_examined: 172032use testdb;SET timestamp=1686158318;call truncate_test();MySQL 8.0 如何跟踪？

mysql> call truncate_test();Query OK, 0 rows affected (2 min 24.84 sec)mysql> mysql> mysql> show profiles;+----------+--------------+-----------------------------------------+| Query_ID | Duration | Query |+----------+--------------+-----------------------------------------+| 1 | 144.55113600 | insert into ltb2 select from ltb3_bak || 2 | 0.29312375 | truncate table ltb2 |+----------+--------------+-----------------------------------------+2 rows in set, 1 warning (0.00 sec)

以上包括 truncate 实行慢的剖析，如针对细节有任何疑问和建议，欢迎留言互换。

爱可生开源社区的 SQLE 是一款面向数据库利用者和管理者，支持多场景审核，支持标准化上线流程，原生支持 MySQL 审核且数据库类型可扩展的 SQL 审查对象。