技术分享 | 浅谈 MySQL 的临时表和临时文件

1. 哪些 SQL 会产生临时表/临时文件

2. 如何查看已有的临时表

3. 如何控制临时表/临时文件的总大小

说明：

以下测试都是在 MySQL 8.0.21 版本中完成，不同版本可能存在差异，可自行测试；

首先，让我们了解下什么是临时表|临时文件？

临时表和临时文件都是用于临时存放数据集的地方；

一般情况下，需要临时存放在临时表或临时文件中的数据集应该符合以下特点：

• 数据集较小(较大的临时数据集一般意味着 SQL 较烂，当然有例外项)
• 用完就清理(因为是临时存储数据集的地方，所以生命周期和 SQL 或者会话生命周期相同)
• 会话隔离(因为是临时数据集，不涉及到与其他会话交互)
• 不产生 GTID

从临时表|临时文件产生的主观性来看，分为2类：

• 用户创建的临时表
• SQL 产生的临时表|临时文件

用户创建临时表：

用户创建临时表(只有创建临时表的会话才能查看其创建的临时表的内容)

create database if not exists db_test ;use db_test ;CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;select * from db_test.t1 ;

注意：

可以创建和普通表同名临时表，其他会话可以看到普通表(因为看不到其他会话创建的临时表)；

创建临时表的会话会优先看到临时表；

同名表的创建的语句如下

CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;insert into  t1 values(1);

当存在同名的临时表时，会话都是优先处理临时表(而不是普通表)，包括：select、update、delete、drop、alter 等操作；

查看用户创建的临时表：

SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G

用户创建的临时表的回收：

1. 会话断开，自动回收用户创建的临时表；
2. 可以通过 drop table 删除用户创建的临时表，例如：drop table t1;

用户创建的临时表的的其他信息&参数：

select * from information_schema.innodb_session_temp_tablespaces ;

SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZEAS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILESWHERE TABLESPACE_NAME = 'innodb_temporary'\G

SQL 什么时候产生临时表|临时文件呢？

下面列举一些 server 在处理 SQL 时，可能会创建内部临时表的 SQL ：

SQL 包含 union | union distinct 关键字

SQL 中存在派生表

SQL 中包含 with 关键字

SQL 中的order by 和 group by 的字段不同

SQL 为多表 update

SQL 中包含 distinct 和 order by 两个关键字

我们可以通过下面两种方式判断 SQL 语句是否使用了临时表空间：

explain xxx ;

select * from information_schema.innodb_session_temp_tablespaces ;

SQL 创建的内部临时表的存储信息：

当 temptable_max_mmap=N 时，N为正整数，包含了 temptable_use_mmap=ON 以及声明了允许为内存映射文件分配的最大内存量。

监控 TempTable 从内存和磁盘上分配的空间：

select * from performance_schema.memory_summary_global_by_event_name \where event_name in('memory/temptable/physical_ram','memory/temptable/physical_disk') \G

监控内部临时表的创建：

例外项：

1. load data local 语句，客户端读取文件并将其内容发送到服务器，服务器将其存储在 tmpdir 参数指定的路径中；

2. 在 replica 中，回放 load data 语句时，需要将从 relay log 中解析出来的数据存储在slave_load_tmpdir(replica_load_tmpdir)指定的目录中，该参数默认和 tmpdir 参数指定的路径相同；

3. 需要 rebuild table 的在线 alter table 需要使用 innodb_tmpdir 存放排序磁盘排序文件，如果 innodb_tmpdir 未指定，则使用 tmpdir 的值；

因为这些例外项一般需要较大的空间，所以需要考虑是否要将其存放在独立的挂载点上。

其他：

show extended tables ;

cd proc/${pid}/fd   # ${pid} 表示你想释放的delete状态的文件持有者的进程号ls -al | grep '${file_name}'    # 假设${file_name}是/opt/mysql/tmp/3306/ibBATOn8 (deleted)echo "" > ${fd_number}    # ${fd_number} 表示你想释放的delete状态的文件的fd，倒数第三个字段，如echo "" > 6

总结：

普通的磁盘临时表|临时文件(一般需要较小的空间)：

例外项(一般需要较大的空间)：

若用户判断产生的临时表|临时文件一定会转换为磁盘临时表|临时文件，那么可以设置 set session big_tables=1;让产生的临时表|临时文件直接存放在磁盘上；

猜测其设计：

当前只有 innodb_temp_data_file_path 参数可以限制 用户创建的临时表使用的回滚段的存储文件的大小，无其他参数可以限制临时表|临时文件可使用的磁盘空间；