MySQL中会话产生的临时表空间和磁盘空间的使用

本文主要关注会话临时表空间，当查询需要存储比tmp_table_size还多的数据或 TempTable引擎分配的内存映射临时文件比temptable_max_mmap还多时，InnoDB基于磁盘的内部临时表就会发挥作用。

如果以上情况发生，则需要为执行查询的会话分配临时表空间池中的表空间。临时表空间池最初包含10个在启动实例时创建的临时表空间。池的大小永远不会缩小，如果需要，表空间会自动添加到池中。表空间（IBT 文件）的默认大小为 5个页或80KB。

当会话断开连接时，它的临时表空间将被truncate并以其初始大小释放到池中。truncate操作仅在会话断开时发生；这也意味着只要会话还在连接着，表空间就会消耗磁盘空间。 MySQL可以为将来的查询重用该区域，但如果一个查询需要大量临时空间，则IBT文件将在会话的整个生命周期内保持很大。

我们演示一下这种行为。

首先，我们创建一个表并插入一百万行记录。附带说明一下，将SHA1校验和(checksums)存储在char(40)字段中并不完美，但它使测试更清晰。

# ls -la './#innodb_temp/temp_10.ibt'
-rw-r----- 1 mysql mysql 80K 05-29 14:10 temp_10.ibt

　

然后，将执行以下使用临时表的查询。由于临时表大小大于 tmp_table_size 的值（默认为16MB），因此该查询将不得不使用会话临时表空间。

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很少会从Session Temporary Tablespace中回收空间，因此，这会增加整体磁盘空间的利用。

这并不难出现。不时进行一次大负载的OLAP查询就足以产生滚雪球的效应了。

ProxySQL有一个强制后端连接重新初始化的选项——mysql-connection_max_age_ms（默认禁用）。但是，这只适用于ProxySQL，但是连接池有很多种，其中一些是直接在应用程序端实现的，这通常会增加整个问题的复杂性。

在理想情况下，MySQL应该通过更频繁地触发truncate过程来处理这个问题。已经有提交了相应的功能请求(https://bugs.mysql.com/bug.php?id=107372)