一、背景
最近在使用MySQL5.6和5.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之前，产生的临时表是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下，磁盘临时表的undolog都是与普通表的undo放在一起(注意由于磁盘临时表在数据库重启后就被删除了，不需要redolog通过奔溃恢复来保证事务的完整性，所以不需要写redolog，但是undolog还是需要的，因为需要支持回滚)。文件名类似#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目录占用过多空间的问题时尤其适用。用户显式创建的这种临时表，在连接释放的时候，会自动释放并把空间释放回操作系统。
阿里云RDS有个loose_rds_max_tmp_disk_space参数控制最大磁盘临时表空间，开源MySQL没有这个参数。RDS这个参数也是为了避免磁盘临时表过大导致磁盘空间被打满了导致实例锁定等问题。
2.3.2 MySQL5.7中的临时表
在MySQL5.7中，临时磁盘表位于ibtmp1文件中，ibtmp1文件位置及大小控制方式由参数innodb_temp_data_file_path控制，阿里云RDS默认ibtmp1可以无限扩展。显式创建的表的数据和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中的临时文件
参数：innodb_sort_buffer_size、innodb_online_alter_log_max_size
2、BinLog中的缓存操作
参数：binlog_cache_size
3、优化器创建的临时文件
参数：sort_buffer_size
4、Load data中用的临时文件
5、MYISAM表内部排序的临时文件
参数：internal_tmp_disk_storage_engine
临时文件名字：
1、执行计划filesort 文件名字MY开头
2、大事物binary log缓存 文件名字ML开头
3、压缩的tempory table #sql
4、online DDL 涉及排序#ib
5、ALGORITHM copy 名字为 #sql-
6、ALGORITHM inplace 名字为 #sql-ib
三、相关参数
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