mysql 解决session临时占用空间大问题

mysql中，有2种类型的临时表空间，一个是全局临时表空间ibtmp1,另一个是会话级表空间。

会话级表空间：
当第一次请求创建磁盘上的临时表时，会话临时表空间被分配给临时表空间池中的会话。一个会话最多分配两个表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表。分配给会话的临时表空间用于该会话创建的所有磁盘上的临时表。当一个会话断开连接时，它的临时表空间会被截断并释放回池中。当服务器空闲时，会创建一个由10个临时表空间组成的池。池的大小永远不会缩小，如果需要，表空间会自动添加到池中。表空间(IBT文件)的默认大小为5页或80 KB。
会话临时表空间保留了40万个空间id。由于每次服务器启动时都会重新创建会话临时表空间池，所以当服务器关闭时，会话临时表空间的空间id不会被持久化，可能会被重用。
在基于语句的复制(SBR)模式下，在副本上创建的临时表位于单个会话临时表空间中，只有在MySQL服务器关闭时才会被截断。

全局临时表空间：
全局临时表空间(ibtmp1)存储对用户创建的临时表所做的更改的回滚段。
innodb_temp_data_file_path变量定义了全局临时表空间数据文件的相对路径、名称、大小和属性。如果innodb_temp_data_file_path没有指定值，默认的行为是在innodb_data_home_dir目录下创建一个名为ibtmp1的自动扩展数据文件。初始文件大小略大于12MB。
全局临时表空间在正常关闭或中止初始化时被删除，并在每次服务器启动时重新创建。全局临时表空间在创建时接收一个动态生成的空间ID。如果全局临时表空间无法创建，启动将被拒绝。如果服务器意外停止，全局临时表空间不会被删除。
在这种情况下，dba可以手动删除全局临时表空间或重新启动MySQL服务器。重启MySQL服务器删除并重新创建全局临时表空间.
INFORMATION_SCHEMA.FILES提供关于全局临时表空间的元数据.

关于变量tmp_table_size 定义内存存储引擎和TempTable存储引擎(从MySQL 8.0.28开始)创建的内存中临时表的最大大小。如果内存中的内部临时表超过这个大小，它会自动转换为磁盘上的内部临时表。

以下主要测试使用会话级临时表空间
数据库初始状态会话级表空间如下
mysql [localhost:8026] {root} ((none)) > SELECT * FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES;
±—±-----------±---------------------------±------±---------±----------+
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
±—±-----------±---------------------------±------±---------±----------+
| 8 | 4243767290 | ./#innodb_temp/temp_10.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 |
| 0 | 4243767289 | ./#innodb_temp/temp_9.ibt | 81920 | INACTIVE | NONE |
±—±-----------±---------------------------±------±---------±----------+
10 rows in set (0.00 sec)

执行SQL测试

SQL执行计划
mysql [localhost:8026] {root} (sbtest) > explain SELECT * FROM ( SELECT * FROM sbtest1 UNION SELECT * FROM sbtest1 ORDER BY pad DESC) t LIMIT 1;
±—±-------------±-----------±-----------±-----±--------------±-----±--------±-----±--------±---------±--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±-------------±-----------±-----------±-----±--------------±-----±--------±-----±--------±---------±--------------------------------+
| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 4670440 | 100.00 | NULL |
| 2 | DERIVED | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 2335220 | 100.00 | NULL |
| 3 | UNION | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 2335220 | 100.00 | NULL |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort |
±—±-------------±-----------±-----------±-----±--------------±-----±--------±-----±--------±---------±--------------------------------+
4 rows in set, 1 warning (0.00 sec)

执行SQL

mysql [localhost:8026] {root} (sbtest) > SELECT * FROM ( SELECT * FROM sbtest1 UNION SELECT * FROM sbtest1 ORDER BY pad DESC) t LIMIT 1;
±--------±-----±----±----+
| id | k | c | pad |
±--------±-----±----±----+
| 2718577 | 5001 | ddd | ddd |
±--------±-----±----±----+
1 row in set (27.14 sec)

查看会话临时表空间

mysql [localhost:8026] {root} (sbtest) > SELECT PATH, format_bytes(SIZE), STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES WHERE id = CONNECTION_ID();
±---------------------------±-------------------±-------±----------+
| PATH | format_bytes(SIZE) | STATE | PURPOSE |
±---------------------------±-------------------±-------±----------+
| ./#innodb_temp/temp_10.ibt | 1.98 GiB | ACTIVE | INTRINSIC |
±---------------------------±-------------------±-------±----------+
可以看到，占用1.98G

查看实际文件在OS系统中占用，显示为2G，与SQL查出来的差不多。
[root@testdb #innodb_temp]# ll -h
total 2.0G
-rw-r----- 1 root root 2.0G Jun 7 15:39 temp_10.ibt

当这个session不断开连接时，这个会话临时表空间不截断，也就一直占用空间，OS也不能回收空间。一般数据库不会让会话一直占用连接，如果是连接池，那这个会话临时表空间只会增加，不会截断，也就不能回收空间。假如用proxysql作为应用与数据库连接的中间件，这可能导致mysql不会回收临时表空间，这会增加整个磁盘空间利用率。

mysql [localhost:8026] {root} (sbtest) > exit
Bye

[root@testdb #innodb_temp]# ll -h
total 800K
-rw-r----- 1 root root 80K Jun 7 15:53 temp_10.ibt

目前要解决临时会话表空间利用率，退出session，即可触发回收空间。