一、背景
最近在使用MySQL5.7的过程中，碰到了一个问题，问题是这样的：由于一个大查询导致临时表空间ibtmp1暴涨，从而导致磁盘打满，数据库无法响应。但是在相同配置的8.0中却无法复现，为此我分析了MySQL各版本对临时表的处理方式，特此分享。
二、MySQL的临时表和临时文件
2.1 临时表分类
MySQL的临时表分为两种，一种是用户创建的临时表，另一种是由优化器创建的内部临时表；临时表同时又可以分为内存临时表和磁盘临时表。
2.2 内存临时表
内存临时表有Memory引擎和Temptable引擎，Memory引擎从MySQL5.6开始可以使用，Temptable引擎是8.0引入的新的引擎。Memory引擎不管实际字符多少，都是用定长的空间存储，Temptable引擎会用变长的空间存储，提高了内存中的存储效率，有更多的数据可以放在内存中处理而不是转换成磁盘临时表。
2.3 磁盘临时表
磁盘临时表分为MyISAM临时表、InnoDB临时表。在MySQL5.6以及以前的版本，磁盘临时表和临时文件都是放在临时目录tmpdir下的，磁盘临时表的undolog都是与普通表的undo放在一起(由于磁盘临时表在数据库重启后就被删除了，不需要redolog通过崩溃恢复来保证事务的完整性，所以不需要写redolog，但是undolog还是需要的，因为需要支持回滚)。在MySQL 5.7之前，这个SQL 运行中产生的临时表是MYISAM，而且只能是MYISAM。从5.7开始提供了参数internal_tmp_disk_storage_engine来定义磁盘临时表引擎，可选值为MYISAM和INNODB，并且把内部的临时表默认保存在临时表空间ibtmp1(可以用参数innodb_temp_data_file_path 设置初始大小，最大大小和步长)下，推荐设置最大,否则可能磁盘空间会因为大查询打满，出现文章开头的问题。
但是在MySQL 5.7中没有解决如下问题:
1、VARCHAR的变长存储
如果临时表的字段定义是VARCHAR(200)，那么映射到内存里处理的字段变为CHAR(200)，造成浪费；
2、大对象的内存存储
比如 TEXT，BLOB， JSON等，都会直接转化为磁盘存储。 从MySQL8.0开始，临时表可以使用特有的引擎TempTable,解决了VARCHAR字段的变长存储以及大对象的内存存储问题。由变量internal_tmp_mem_storage_engine来控制，可选值为TempTable和Memory；新引擎的大小由参数temptable_max_ram来控制，默认为1G。超过了则存储在磁盘上。并且计数器由表performance_schema.memory_summary_global_by_event_name来存储。如果设置的磁盘临时表是InnoDB或者MYISAM，则需要一个转换拷贝的消耗。为了尽可能减少消耗，Temptable提出了一种overflow机制，即如果内存临时表超过配置大小，则使用磁盘空间map的方式，即打开一个文件，然后删除，留一个句柄进行读写操作。读写文件格式和内存中格式一样，这样就略过了转换这一步，进一步提高性能。这个功能是在MySQL8.0.16版本中才有的。
在MySQL5.7中，磁盘临时表的数据和undo都被独立出来，放在临时表空间ibtmp1中。之所以把临时表独立出来，主要是为了减少创建删除表时维护元数据的开销。
在MySQL8.0中，磁盘临时表的数据单独放在会话临时表空间池(#innodb_temp目录下的ibt文件)里面，临时表的undo放在全局表空间ibtmp1里面。另外一个大的改进是，8.0的磁盘临时表数据占用的空间在连接断开后，就能释放给操作系统，而5.7的版本中需要重启才能释放。
2.3.1 MySQL5.6中的临时表
在MySQL5.6中，磁盘临时表位于tmpdir下，文件名类似#sql_4d2b_8_0，其中#sql是固定的前缀，4d2b是进程号的十六进制表示，8是MySQL线程号的十六进制表示(show processlist中的id)，0是每个连接从0开始的递增值，ibd是innodb的磁盘临时表(通过参数default_tmp_storage_engine控制)。在5.6中，磁盘临时表创建好后，对应的frm以及引擎文件就可以在tmpdir下查看到。在连接断开后，相应文件会自动删除。因此，在5.6的tmpdir里面看到很多类似格式文件名，可以通过文件名来判断是哪个进程，哪个连接使用的临时表，这个技巧在排查tmpdir目录占用过多空间的问题时尤其适用。用户显式创建的这种临时表，在连接释放的时候，会自动释放并把空间释放回操作系统。临时表的undolog存在undo表空间中。
2.3.2 MySQL5.7中的临时表
在MySQL5.7中，临时磁盘表位于ibtmp1文件中，ibtmp1文件位置及大小控制方式由参数innodb_temp_data_file_path控制。显式创建的表的数据和undo都在ibtmp1里面。用户连接断开后，临时表会释放，但是仅仅是在ibtmp1文件里面标记一下，空间是不会释放回操作系统的。如果要释放空间，需要重启数据库。另外，需要注意的一点是，5.6可以在tmpdir下直接看到创建的文件，但是5.7是创建在ibtmp1这个表空间里面，因此是看不到具体的表文件的。如果需要查看，则需要查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO这个表，里面有一列name，这里可以看到表名。
2.3.3 MySQL8.0中的临时表
在MySQL8.0中，临时表的数据和undo被进一步分开，数据存放在ibt文件中(由参数innodb_temp_tablespaces_dir控制)，undo依然存放在ibtmp1文件中(由参数innodb_temp_data_file_path控制)。存放ibt文件的叫做会话临时表空间，存放undo的ibtmp1叫做全局临时表空间。会话临时表空间，在磁盘上的表现是一组以ibt文件组成的文件池。启动的时候，数据库会在配置的目录下重新创建，关闭数据库的时候删除。启动的时候，默认会创建10个ibt文件，每个连接最多使用两个，一个给用户创建的临时表用，另外一个给优化器创建的隐式临时表使用。当然只有在需要临时表的时候，才会创建，如果不需要，则不会占用ibt文件。当10个ibt都被使用完后，数据库会继续创建，最多创建四十万个。当连接释放时候，会自动把这个连接使用的ibt文件给释放，同时回收空间。如果要回收全局临时表空间，依然需要重启。但是由于已经把存放数据的文件分离出来，且其支持动态回收，所以在5.7中的空间占用问题，已经得到了很好的缓解。在理论上，很多空间在某些SQL(如用户drop了某个显式创建的临时表)执行后，即可以释放。另外，如果需要查看表名，依然查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO这个表。但是在MySQL8.0中显式临时表不能是压缩表，而在5.6和5.7中可以。
2.4 优化器隐式创建临时表
优化器隐式创建临时表是数据库为了辅助某些复杂SQL的执行而创建的。与用户显式创建的临时表直接创建磁盘文件不同，如果优化器觉得SQL需要临时表辅助，会先使用内存临时表，如果超过配置的内存((tmp_table_size、max_heap_table_size)二者最小值)，就会转化成磁盘临时表，这种磁盘临时表就类似用户显式创建的，引擎类型通过参数internal_tmp_disk_storage_engine控制。
SQL中存在下列操作会使用到临时表：
union查询
对于视图的操作，比如使用一些TEMPTABLE算法、union或aggregation
子查询
semi-join 包括not in、exist等
查询产生的派生表
复杂的group by 和 order by
Insert select 同一个表，mysql会产生一个临时表缓存select的行
多个表更新
GROUP_CONCAT()或者COUNT(DISTINCT)语句SQL在下列情况会直接使用磁盘临时表：
表中含有BLOB或者TEXT列
使用union或者union all时，select子句有大于512字节的列
Show columns或者 desc 表的时候，有BLOB或者TEXT
GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列2.5 临时文件
临时文件更多的被使用在缓存数据，排序数据的场景中。一般情况下，被缓存或者排序的数据，首先放在内存中，如果内存放不下，才会使用磁盘临时文件的方式。临时文件的使用方式与一般的表也不太一样，一般的表创建完后，就开始读写数据，使用完后，才把文件删除，但是临时文件的使用方式不一样，在创建完后，马上调用unlink删除文件，但是不close文件，后续使用原来的句柄操作文件。这样的好处是，当进程异常crash，不会有临时文件因为没被删除而残留，这种文件需要使用lsof +L1来查看。
目前，主要在一下场景会使用到临时文件：
1、DDL中的临时文件
2、BinLog中的缓存操作
3、优化创建的临时文件
4、Load data中用的临时文件
5、MYISAM表内部排序的临时文件
三、相关参数
tmpdir:这个参数是临时目录的配置，在5.6以及之前的版本，临时表/文件默认都会放在这里。这个参数可以配置多个目录，这样就可以轮流在不同的目录上创建临时表/文件，如果不同的目录分别指向不同的磁盘，就可以达到分流的目的。
innodb_tmpdir: 这个参数只要是被DDL中的排序临时文件使用的。其占用的空间会很大，建议单独配置。这个参数可以动态设置，也是一个会话变量。
slave_load_tmpdir:这个参数主要是给BinLog复制中Load Data时，配置备库存放临时文件位置时使用。因为数据库Crash后还需要依赖Load数据的文件，建议不要配置重启后会删除数据的目录。
internal_tmp_disk_storage_engine: 当隐式临时表被转换成磁盘临时表时，使用哪种引擎，默认只有MyISAM和InnoDB。5.7及以后的版本才支持。8.0.16版本后取消的这个参数。
internal_tmp_mem_storage_engine: 隐式临时表在内存时用的存储引擎，可以选择Memory或者Temptable引擎。建议选择新的Temptable引擎。
default_tmp_storage_engine: 默认的显式临时表的引擎，即用户通过SQL语句创建的临时表的引擎。
tmp_table_size: min(tmp_table_size,max_heap_table_size)是隐式临时表的内存大小，超过这个值会转换成磁盘临时表。
max_heap_table_size:用户创建的Memory内存表的内存限制大小。
big_tables:内存临时表转换成磁盘临时表需要有个转化操作，需要在不同引擎格式中转换，这个是需要消耗的。如果我们能提前知道执行某个SQL需要用到磁盘临时表，即内存肯定不够用，可以设置这个参数，这样优化器就跳过使用内存临时表，直接使用磁盘临时表，减少开销。
temptable_max_ram: 这个参数是8.0后才有的，主要是给Temptable引擎指定内存大小，超过这个后，要么就转换成磁盘临时表，要么就使用自带的overflow机制。
temptable_use_mmap:是否使用Temptable的overflow机制，temptable引擎是否磁盘数据转换成Innodb存储，还是内存映射文件。四、总结
MySQL的临时表以及临时文件在不同版本中变化较大，只有清除原理才能更方便的去处理问题。
本文参考：
https://www.cnblogs.com/coderyuhui/p/10773143.html