本文为墨天轮数据库管理服务团队第43期技术分享，内容原创，如需转载请联系小墨（VX：modb666）并注明来源。
概念描述
到了mysql8.0版本，SQL运行过程中产生的内部临时表已经有了变化，存放位置由innodb\_temp\_tablespaces\_dir参数控制，默认放在 $DATADIR/#innodb\_temp/目录下，由多个.ibt文件构成。并且当数据库连接断开时，.ibt文件的大小会被重置(5个页80K)，以达到回收磁盘空间的目的。
但是，实际业务场景中，数据库连接均为长连接，当我们发现了*.ibt文件过大时，该如何定位到问题SQL呢？这里分2种情况：
- 第一种情况：发现*.ibt文件过大时，此文件大小正在持续增长。此类情况，可以直接查询innodb\_session\_temp_tablespaces表定位到相关SQL。
- 第二种情况：发现.ibt文件过大时，此文件大小已经不再增长。此类情况，需要结合.ibt文件的创建日期、最后修改日期、长连接的ID、slowlog等来定位到SQL。
测试验证
创建测试数据：
-- 测试数据
use modb;
drop table if exists temp_table;
CREATE TABLE temp_table (
id int NOT NULL AUTO_INCREMENT,
create_time datetime DEFAULT NULL,
code char(10) NOT NULL DEFAULT '0',
PRIMARY KEY (id)
) ENGINE=InnoDB;
insert into temp_table(create_time, code)
select '2025-01-09', 'c1234'
from information_schema.columns a, information_schema.columns b
limit 400000;
mysql> analyze table temp_table;
+-----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| modb.temp_table | analyze | status | OK |
+-----------------+---------+----------+----------+
1 row in set (0.19 sec)
mysql> show table status like 'temp_table';
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| temp_table | InnoDB | 10 | Dynamic | 398958 | 43 | 17317888 | 0 | 0 | 4194304 | 458746 | 2025-01-09 10:59:10 | 2025-01-09 11:12:25 | NULL | utf8mb4_0900_ai_ci | NULL | | |
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.09 sec)第一种情况，如果是当前运行的SQL导致的*.ibt文件过大，可以通过查询innodb\_session\_temp_tablespaces和processlist表直接定位。
-- 从SQL执行计划可以看出,会用到内部临时表（Using temporary）
mysql> explain select min(create_time) min_time from
-> (
-> select * from temp_table a union all
-> select * from temp_table a
-> ) t
-> group by create_time;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 797916 | 100.00 | Using temporary |
| 2 | DERIVED | a | NULL | ALL | NULL | NULL | NULL | NULL | 398958 | 100.00 | NULL |
| 3 | UNION | a | NULL | ALL | NULL | NULL | NULL | NULL | 398958 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
3 rows in set, 1 warning (0.02 sec)
-- 实际执行SQL，查看临时表空间使用情况：
-- session1: SQL执行中...
mysql> select min(create_time) min_time from
-> (
-> select * from temp_table a union all
-> select * from temp_table a
-> ) t
-> group by create_time;
-- session2: 查看临时表使用情况(temp_10.ibt被使用，大小已经涨至近18MB，SQL线程ID=7)
mysql> select * from information_schema.innodb_session_temp_tablespaces;
+----+------------+----------------------------+----------+----------+-----------+
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+----------+----------+-----------+
| 7 | 4243767290 | ./#innodb_temp/temp_10.ibt | 18874368 | ACTIVE | INTRINSIC |
| 8 | 4243767289 | ./#innodb_temp/temp_9.ibt | 81920 | ACTIVE | INTRINSIC |
| 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767288 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
+----+------------+----------------------------+----------+----------+-----------+
10 rows in set (0.00 sec)
-- session2: 根据SQL线程ID列获取SQL文本
mysql> select * from information_schema.processlist where id = 7\G
*************************** 1. row ***************************
ID: 7
USER: root
HOST: 192.168.199.132:45830
DB: modb
COMMAND: Query
TIME: 12
STATE: converting HEAP to ondisk
INFO: select min(create_time) min_time from
(
select * from temp_table a union all
select * from temp_table a
) t
group by create_time
1 row in set (0.02 sec)
-- session3: 查看\*.ibt文件大小(从操作系统层看,temp_10.ibt文件大小也在不断增长)
[root@vm-mysql-01 #innodb_temp]# ls -lht --full-time
total 23M
-rw-r-----. 1 mysql mysql 22M 2025-01-09 15:36:59.313599607 +0800 temp_10.ibt
-rw-r-----. 1 mysql mysql 96K 2025-01-09 15:36:57.534599633 +0800 temp_9.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.376602094 +0800 temp_8.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.374602094 +0800 temp_6.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.374602094 +0800 temp_7.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.372602094 +0800 temp_5.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.371602094 +0800 temp_4.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.370602094 +0800 temp_3.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.369602094 +0800 temp_2.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.368602094 +0800 temp_1.ibt如上所示，如果某个*.ibt文件正在持续增长，可以根据innodb\_session\_temp_tablespaces表直接定位到相关SQL，此表字段含义如下：
- ID：SQL线程ID
- SPACE：表空间ID。会话临时表保留了40万个表空间id
- PATH：表空间数据文件路径
- SIZE：表空间大小（单位：字节）
STATE：表空间的状态：
- ACTIVE-表空间当前正在被会话使用
- INACTIVE-表空间在临时表空间池中但未被使用
PURPOSE：表空间的用途：
- INTRINSIC-表空间用于优化器使用的优化的内部临时表
- SLAVE-分配该表空间用于存储复制从节点上用户创建的临时表
- USER-该表空间用于用户创建的临时表
- NONE-该表空间未被使用
第二种情况，如果发现.ibt文件过大时，此文件大小已不再变化，且由于占用它的连接是长连接，那么.ibt文件的大小就不会被重置为80K，这时我们就得结合*.ibt文件的创建日期、最后修改日期、长连接的ID、slowlog等来定位SQL了。以上述例子中temp_10.ibt为例：
-- 由于线程ID=7的长连接并未断开，并且SQL已经执行完，故查看temp_10.ibt文件的大小始终为64MB
[root@vm-mysql-01 #innodb_temp]# ls -lht --full-time
total 65M
-rw-r-----. 1 mysql mysql 64M 2025-01-09 15:37:39.524599032 +0800 temp_10.ibt
-rw-r-----. 1 mysql mysql 96K 2025-01-09 15:36:57.534599633 +0800 temp_9.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.376602094 +0800 temp_8.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.374602094 +0800 temp_6.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.374602094 +0800 temp_7.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.372602094 +0800 temp_5.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.371602094 +0800 temp_4.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.370602094 +0800 temp_3.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.369602094 +0800 temp_2.ibt
-rw-r-----. 1 mysql mysql 80K 2025-01-09 15:34:05.368602094 +0800 temp_1.ibt
-- 查看innodb_session_temp_tablespaces表，可以看出线程ID=7占用了temp_10.ibt空间
mysql> select * from information_schema.innodb_session_temp_tablespaces;
+----+------------+----------------------------+----------+----------+-----------+
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+----------+----------+-----------+
| 7 | 4243767290 | ./#innodb_temp/temp_10.ibt | 67108864 | ACTIVE | INTRINSIC |
| 8 | 4243767289 | ./#innodb_temp/temp_9.ibt | 98304 | ACTIVE | INTRINSIC |
| 0 | 4243767281 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767282 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767283 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767284 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767285 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767286 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767287 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4243767288 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
+----+------------+----------------------------+----------+----------+-----------+
10 rows in set (0.01 sec)
-- 尝试获取temp_10.ibt的创建时间
#获取temp_10.ibt的文件描述符(FD: 25uW) 及进程ID(48080)
[root@vm-mysql-01 #innodb_temp]# lsof temp_10.ibt
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 48080 mysql 25uW REG 253,0 67108864 40934343 temp_10.ibt
#获取temp_10.ibt的文件描述符的创建时间
[root@vm-mysql-01 #innodb_temp]# ls -lht --full-time /proc/48080/fd/25
lrwx------. 1 root root 64 2025-01-09 15:34:19.829601887 +0800 /proc/48080/fd/25 -> /data/mysql/mysql_3307/#innodb_temp/temp_10.ibt至此，我们知道了：
- SQL线程ID=7
- temp_10.ibt创建时间为：2025-01-09 15:34:19
- temp_10.ibt最后修改时间为：2025-01-09 15:37:39
最后再从slowlog中查找相关SQL：
# Time: 2025-01-09T15:37:39.116179+08:00
# User@Host: root[root] @ [192.168.199.132] Id: 7
# Query_time: 53.946563 Lock_time: 0.000520 Rows_sent: 1 Rows_examined: 800000 Thread_id: 7 Errno: 0 Killed: 0 Bytes_received: 137 Bytes_sent: 74 Read_first: 3 Read_last: 0 Read_key: 800021 Read_next: 13 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 2123782 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 1 Created_tmp_tables: 2 Start: 2025-01-09T15:36:45.169616+08:00 End: 2025-01-09T15:37:39.116179+08:00
use modb;
SET timestamp=1736408205;
select min(create_time) min_time from
(
select * from temp_table a union all
select * from temp_table a
) t
group by create_time;在slowlog中查找这样的SQL：
- 线程Id=7
- 时间在 2025-01-09 15:34:19~2025-01-09 15:37:39 之间
- Created\_tmp\_disk_tables > 0
- 执行时间较长
- 可能会产生临时表的SQL
但这里要注意一个问题，就是temp_10.ibt的创建时间，它可能会不准确，原因如下：
- temp\_1.ibt ~ temp\_10.ibt的的文件描述符创建日期，是在mysqld进程启动后就创建的，即使后来由于连接断开，*.ibt文件被重置成80K，这10个文件描述符的创建日期也不会变化，除非重启mysql。
- 如果并发使用临时空间的线程超过10个了，就会自动生成temp\_11.ibt、temp\_12.ibt…，这些文件描述符创建日期，相当于*.ibt文件的创建日期，有一定的参考价值。
- 如果操作系统支持，可以从系统层面获取*.ibt文件的创建日期，也有一定的参考价值。
另外要注意的问题，就是*.ibt文件的重建，具体以下特点：
- MySQL重启时删除并重建，包括文件描述符
- 数据库连接断开时，文件重置大小，但文件描述符不变
- 数据库连接不断开时，文件大小始终保留使用后的最大值
知识总结
本文总结了当mysql临时表空间文件变大时的定位方法。分两种情况：
- 临时文件(*.ibt)正在变大时，容易定位原因。此时使用innodb\_session\_temp_tablespaces表即可定位。
- 临时文件(.ibt)不再变大时，分析历史原因。此时可分析.ibt文件的创建日期、最后编辑日期、相关SQL线程ID去slowlog中定位相关SQL。
参考文档：
https://mp.weixin.qq.com/s/ctSAnSjQqC444WqJaeANNg
https://dev.mysql.com/doc/refman/8.0/en/information-schema-in...
https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tabl...
https://mysql.net.cn/doc/refman/8.0/en/innodb-temporary-table...
📣活动预告：3月13日20点，《数据库服务团队技术分享第十期直播》邀请到墨天轮服务团队总监杨明翰，探入探讨MySQL中需谨慎执行的命令，欢迎来【墨天轮】视频号看直播~
墨天轮从乐知乐享的数据库技术社区蓄势出发，全面升级，提供多类型数据库管理服务。墨天轮数据库管理服务旨在为用户构建信赖可托付的数据库环境，并为数据库厂商提供中立的生态支持。
服务官网：https://www.modb.pro/service
如需获取更多服务团队干货还可关注【墨天轮】公众号哟~
mysql
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。