原文链接：https://www.percona.com/blog/session-temporary-tablespaces-and-disk-space-usage/
原文作者：IP
在MySQL中处理临时表的历史有些曲折。不久前，我的同事Przemek写了一篇关于MySQL不同版本实现差异的文章。如果您正在寻找实现上的差异，这是一个检查的完美地方。
在这篇文章中，我将关注Session临时表空间，InnoDB磁盘上的内部临时表，当查询需要存储比tmp_table_size更多的数据，或者TempTable引擎分配的内存映射临时文件超过temptable_max_mmap时，这些临时表就会出现。
如果出现这种情况，执行查询的会话需要从临时表空间池中分配一个表空间。池最初包含10个临时表空间，这些表空间是在启动实例时创建的。池的大小永远不会缩小，如果需要，表空间会自动添加到池中。表空间(IBT文件)的默认大小为5页或80 KB。
当一个会话断开连接时，它的临时表空间会被截断，并以初始大小释放到池中。截断操作只在会话断开时发生,这就意味着，只要连接了会话，表空间就会占用磁盘空间。MySQL可以在以后的查询中重用该区域，但是如果一个查询需要大量的临时空间，那么IBT在整个会话的生命周期中都将是巨大的。
让我们来演示一下这个行为。首先，让我们创建一个表，并在其中填充100万行。附带说明一下，将SHA1校验和存储在char(40)字段中并不完美，但它使测试更清晰。
准备测试数据集：
mysql> CREATE TABLE table01 (id int unsigned primary key auto\_increment, s char(40));
mysql> INSERT INTO table01 (s) SELECT sha1(FLOOR(RAND() \* 100)) from sequence\_table(1000000) t;
然后，将执行下面使用临时表的查询。由于临时表的大小大于tmp_table_size的值，tmp_table_size默认为16 MB，因此该查询必须使用会话临时表空间。
执行查询：
mysql> pager pt-visual-explain
mysql> explain SELECT \* FROM ( SELECT \* FROM table01 UNION SELECT \* FROM table01 ORDER BY s DESC) t LIMIT 1;
Table scan
rows 1991860
+- DERIVED
table derived(temporary(union(table01,table01)))
+- Table scan
+- TEMPORARY
table temporary(union(table01,table01))
+- Filesort
+- Table scan
+- UNION
table union(table01,table01)
+- Table scan
| rows 995930
| +- Table
| table table01
+- Table scan
rows 995930
+- Table
table table01
mysql> SELECT \* FROM ( SELECT \* FROM table01 UNION SELECT \* FROM table01 ORDER BY s DESC) t LIMIT 1;
+--------+------------------------------------------+
| id | s |
+--------+------------------------------------------+
| 999145 | fe5dbbcea5ce7e2988b8c69bcfdfde8904aabc1f |
+--------+------------------------------------------+
让我们检查执行查询后附加到该会话的临时表空间:
mysql> 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 | 392.00 MiB | ACTIVE | INTRINSIC |
+----------------------------+--------------------+--------+-----------+
\# ls -la './#innodb\_temp/temp\_10.ibt'
\-rw-r----- 1 mysql mysql 392M 05-29 14:10 temp\_10.ibt
关闭会话后，文件大小恢复到默认值:
\# ls -la './#innodb\_temp/temp\_10.ibt'
\-rw-r----- 1 mysql mysql 80K 05-29 14:10 temp\_10.ibt
解决方案如MySQL手册中描述的那样工作，然而，这也意味着它可能会有问题。长连接会话在数据库世界中并不少见，主要是在应用程序使用连接池的情况下。此外，连接池正是为此目的而设计的，以减轻每次应用程序需要新连接时创建新连接的开销，因为重用到数据库的现有连接比打开新连接更有效。
例如，如果在应用程序和MySQL之间使用ProxySQL作为中间件，那么应用程序就缺乏对后端连接的大部分控制。这意味着将使用到后端的平衡连接，而且连接很可能永远存在。由于这一事实，MySQL很少会从会话临时表空间中回收空间，这将增加总体磁盘空间利用率。
这并不难遇到。一个沉重的OLAP查询就足以开始滚雪球了。
ProxySQL有一个选项，强制重新初始化后端连接—mysql-connection_max_age_ms(默认禁用)。然而，这只适用于ProxySQL，而且还有很多可用的连接池，其中一些是直接在应用程序端实现的，这通常会增加整个问题的复杂性。
在理想情况下，MySQL应该通过更频繁地触发截断进程来处理这个问题。我提交了一份功能请求，目标是移除“惊喜”因素，并最终使它变得对用户更加友好。
