关于mysql:TRUNCATE-语句到底因何而慢

作者通过源码剖析 truncate 语句造成慢 SQL 的起因和解决方案，并与 MySQL 5.7 就相干实现逻辑进行比照。

作者：李锡超

一个爱笑的江苏苏宁银行 数据库工程师，次要负责数据库日常运维、自动化建设、DMP 平台运维。善于 MySQL、Python、Oracle，喜好骑行、钻研技术。

本文起源：原创投稿

• 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

问题景象

收到反馈某测试环境执行批量操作时，有 truncate 语句存在于慢查问日志中。放心上线后可能影响数据库，申请 DBA 配合剖析。

要害配置

问题剖析总结

总结下来次要有如何几个问题：

Q1: TRUNCATE 语句是如何执行的？fd 句柄不变动？为什么执行工夫长？

TRUNCATE 语句如何执行？

要害堆栈：

要害操作 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 实现的。

Q2: 如何剖析 TRUNCATE 慢的问题？

形式一：慢日志？

只能看到慢的后果，无奈确认起因。

形式二：执行打算？

不反对 truncate 语句。

形式三：profile

从 profile 后果来看，对于 truncate 语句，只能看到耗时过程都在 System lock 上，无奈看到更近一步的起因。

形式四：DEBUG

// 举荐设置
// 其中 T 其实是 MySQL 反对（在 trace 中打印工夫）的，但官网文档中短少了阐明。已提交 bug 阐明：Bug #111174

set global debug='d:t:T:i:n:N:o,/tmp/debug_3306.trace.f';
set global debug='';

• ① 示意 show processlist 的线程 ID
• ② 执行工夫
• ③ 函数调用层级
• ④ 函数名称

MySQL 8.0 切换比照

// 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 的优化措施

1. row_drop_table_for_mysql 慢的问题，能够通过设置 innodb_adaptive_hash_index = off 进行优化；
2. os_file_delete_func 慢的问题，能够设置 innodb_flush_method = O_DIRECT 或者配置表的 HARD LINK 进行优化。

MySQL 5.7 的优化措施

详见前面 3-Q1、3-Q4 局部。

post_ddl 如何调用？

MySQL 8.0 引入了 scope guard 性能：当定义了 scope guard 之后，会创立 Scope_guard 对象。失常状况下，当执行 return 操作前，会执行 scope guard 定义的逻辑。除非在函数完结前执行 Scope_guard 对象的 commit 操作。文件的删除性能切实 scope guard 的 cleanup_base 阶段调用是现的。

Q4: 生产执行 TRUNCATE 是否存在隐患？

从实现机制来看，次要有以下危险：

IO 压力

当触发 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 的性能影响剖析》。

Q5: 不同版本对于 TRUNCATE 的实现是否存在差别？

通过比照 2-Q1 与 3-Q4:

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。

DROP TABLE 优化失败剖析

上面来看一个 MySQL 5.7 测试环境上线 DROP TABLE 优化计划失败问题。

Q1：上线为什么会失败？HARD LINK 为什么不失效？AHI 为什么不失效?

• 当 MySQL 5.7 应用标准配置启动时，从 debug-trace 过程来看，在 row_drop_single_table_tablespace、row_drop_table_from_cache 函数执行期间基本没有耗时，所以施行优化计划后，没有成果；
• 耗时的过程在 que_eval_sql: query: PROCEDURE DROP_TABLE_PROC ---> dict_drop_index_tree；
• row_drop_single_table_tablespace 的耗时被 MySQL 5.7 配置 innodb_flush_method=O_DIRECT 优化了。

Q2：该优化是否实用于 MySQL 8.0？

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

Q3：MySQL 8.0 如何解决 DROP TABLE 时执行 DROP_TABLE_PROC 慢的问题？

• WL#9536: InnoDB_New_DD: Support crash-safe DDL；
• 依赖于自 Version 8.0.3 的 NEW DD；
• 整个 drop 慢的 que_eval_sql、DROP_TABLE_PROC 被整体砍掉；
• 包含 dict_drop_index_tree 在内的整个函数，都被砍了；
• 具体实现机制，参考剖析 NEW DD 实现办法。

Q4：MySQL 5.7 DROP TABLE 和 TRUNCATE 在实现机制、优化措施有何区别呢？

• 执行 truncate 操作的耗时，依然是在 dict_drop_index_tree、os_file_truncate 这两个阶段；
• os_file_truncate 的耗时：能够通过设置 innodb_flush_method=O_DIRECT 工夫进行优化（不能够通过 hard link 进行优化）；
• dict_drop_index_tree 的耗时，临时没有优化思路。理解更多：InnoDB 文件系统之文件物理构造。

Q5：5.7 慢查问为什么有时记录 TRUNCATE 执行慢，有时不记录?

依据源码，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_test
use 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_test
SET timestamp=1686155311;
truncate table t1;

与 MySQL 8.0 比照

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: 172032
use 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

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

SQLE 获取