MySQL · 引擎特性 · 临时表那些事儿

前言

相比于普通的用户数据表，MySQL/InnoDB中的临时表，大家应该会陌生很多。再加上不同的临时表创建的时机和创建的位置都不固定，这也进一步加大神秘感。最让人捉摸不透的是，临时表很多时候会先创建文件，然后什么都不做，就把文件删除，留一个句柄读写，给人的感觉是神龙见首不见尾。本文分析了详细MySQL各个版本临时表的处理方式，希望对大家有所帮助。

综述

准确的说，我们常说的临时表分为两种，一种真的是表，用来存储用户发送的数，读写走的是表读写接口，读写的时候表一定在文件系统上存在，另外一种，应该是一种临时文件，用来存储SQL计算中间过程的数据，读写走的是文件读写接口，读写的时候文件可能已经被删除了，留一个文件句柄进行操作。

临时表

临时表可以分为磁盘临时表和内存临时表，而临时文件，只会存在于磁盘上，不会存在于内存中。具体来说，临时表的内存形态有Memory引擎和Temptable引擎，主要区别是对字符类型(varchar, blob，text类型)的存储方式，前者不管实际字符多少，都是用定长的空间存储，后者会用变长的空间存储，这样提高了内存中的存储效率，有更多的数据可以放在内存中处理而不是转换成磁盘临时表。Memory引擎从早期的5.6就可以使用，Temptable是8.0引入的新的引擎。另外一方面，磁盘临时表也有三种形态，一种是MyISAM表，一种是InnoDB临时表，另外一种是Temptable的文件map表。其中最后一种方式，是8.0提供的。

在5.6以及以前的版本，磁盘临时表都是放在数据库配置的临时目录，磁盘临时表的undolog都是与普通表的undo放在一起(注意由于磁盘临时表在数据库重启后就被删除了，不需要redolog通过奔溃恢复来保证事务的完整性，所以不需要写redolog，但是undolog还是需要的，因为需要支持回滚)。

在MySQL 5.7后，磁盘临时表的数据和undo都被独立出来，放在一个单独的表空间ibtmp1里面。之所以把临时表独立出来，主要是为了减少创建删除表时维护元数据的开销。

在MySQL 8.0后，磁盘临时表的数据单独放在Session临时表空间池(#innodb_temp目录下的ibt文件)里面，临时表的undo放在global的表空间ibtmp1里面。另外一个大的改进是，8.0的磁盘临时表数据占用的空间在连接断开后，就能释放给操作系统，而5.7的版本中需要重启才能释放。

目前有以下两种情况会用到临时表：

用户显式创建临时表

这种是用户通过显式的执行命令create temporary table创建的表，引擎的类型要么显式指定，要么使用默认配置的值(default_tmp_storage_engine)。内存使用就遵循指定引擎的内存管理方式，比如InnoDB的表会先缓存在Buffer Pool中，然后通过刷脏线程写回磁盘文件。

在5.6中，磁盘临时表位于tmpdir下，文件名类似#sql4d2b_8_0.ibd，其中#sql是固定的前缀，4d2b是进程号的十六进制表示，8是MySQL线程号的十六进制表示(show processlist中的id)，0是每个连接从0开始的递增值，ibd是innodb的磁盘临时表(通过参数default_tmp_storage_engine控制)。在5.6中，磁盘临时表创建好后，对应的frm以及引擎文件就在tmpdir下创建完毕，可以通过文件系统ls命令查看到。在连接关闭后，相应文件自动删除。因此，我们如果在5.6的tmpdir里面看到很多类似格式文件名，可以通过文件名来判断是哪个进程，哪个连接使用的临时表，这个技巧在排查tmpdir目录占用过多空间的问题时，尤其适用。用户显式创建的这种临时表，在连接释放的时候，会自动释放并把空间释放回操作系统。临时表的undolog存在undo表空间中，与普通表的undo放在一起。有了undo回滚段，用户创建的这种临时表也能支持回滚了。

在5.7中，临时磁盘表位于ibtmp文件中，ibtmp文件位置及大小控制方式由参数innodb_temp_data_file_path控制。显式创建的表的数据和undo都在ibtmp里面。用户连接断开后，临时表会释放，但是仅仅是在ibtmp文件里面标记一下，空间是不会释放回操作系统的。如果要释放空间，需要重启数据库。另外，需要注意的一点是，5.6可以在tmpdir下直接看到创建的文件，但是5.7是创建在ibtmp这个表空间里面，因此是看不到具体的表文件的。如果需要查看，则需要查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO这个表，里面有一列name，这里可以看到表名。命名规格与5.6的类似，因此也可以快速找到占用空间大的连接。

在8.0中，临时表的数据和undo被进一步分开，数据是存放在ibt文件中(由参数innodb_temp_tablespaces_dir控制)，undo依然存放在ibtmp文件中(依然由参数innodb_temp_data_file_path控制)。存放ibt文件的叫做Session临时表空间，存放undo的ibtmp叫做Global临时表空间。这里介绍一下这个存放数据的Session临时表空间。Session临时表空间，在磁盘上的表现是一组以ibt文件组成的文件池。启动的时候，数据库会在配置的目录下重新创建，关闭数据库的时候删除。启动的时候，默认会创建10个ibt文件，每个连接最多使用两个，一个给用户创建的临时表用，另外一个给下文描述的优化器创建的隐式临时表使用。当然只有在需要临时表的时候，才会创建，如果不需要，则不会占用ibt文件。当10个ibt都被使用完后，数据库会继续创建，最多创建四十万个。当连接释放时候，会自动把这个连接使用的ibt文件给释放，同时回收空间。如果要回收Global临时表空间，依然需要重启。但是由于已经把存放数据的文件分离出来，且其支持动态回收(即连接断开即释放空间)，所以5.7上困扰大家多时的空间占用问题，已经得到了很好的缓解。当然，还是有优化空间的，例如，空间需要在连接断开后，才能释放，而理论上，很多空间在某些SQL(如用户drop了某个显式创建的临时表)执行后，即可以释放。另外，如果需要查看表名，依然查看INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO这个表。需要注意的是，8.0上，显式临时表不能是压缩表，而5.6和5.7可以。

优化器隐式创建临时表

这种临时表，是数据库为了辅助某些复杂SQL的执行而创建的辅助表，是否需要临时表，一般都是由优化器决定。与用户显式创建的临时表直接创建磁盘文件不同，如果需要优化器觉得SQL需要临时表辅助，会先使用内存临时表，如果超过配置的内存(min(tmp_table_size, max_heap_table_siz))，就会转化成磁盘临时表，这种磁盘临时表就类似用户显式创建的，引擎类型通过参数internal_tmp_disk_storage_engine控制。一般稍微复杂一点的查询，包括且不限于order by, group by, distinct等，都会用到这种隐式创建的临时表。用户可以通过explain命令，在Extra列中，看是否有Using temporary这样的字样，如果有，就肯定要用临时表。

在5.6中，隐式临时表依然在tmpdir下，在复杂SQL执行的过程中，就能看到这临时表，一旦执行结束，就被删除。值得注意的是，5.6中，这种隐式创建的临时表，只能用MyISAM引擎，即没有internal_tmp_disk_storage_engine这个参数可以控制。所以，当我们的系统中只有innodb表时，也会看到MyISAM的某些指标在变动，这种情况下，一般都是隐式临时表的原因。

在5.7中，隐式临时表是创建在ibtmp文件中的，SQL结束后，会标记删除，但是空间依然不会返还给操作系统，如果需要返还，则需要重启数据库。另外，5.7支持参数internal_tmp_disk_storage_engine，用户可以选择InnoDB或者MYISAM表作为磁盘临时表。

在8.0中，隐式临时表是创建在Session临时表空间中的，即与用户显式创建的临时表的数据放在一起。如果一个连接第一次需要隐式临时表，那么数据库会从ibt文件构成的池子中取出一个给这个连接使用，直到连接释放。上文中，我们也提到过，在8.0中，用户显式创建的临时表也会从池子中分配一个ibt来使用，每个连接最多使用两个ibt文件用来存储临时表。我们可以查询INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES来确定ibt文件的去向。这个表中，每个ibt文件是一行，当前系统中有几个ibt文件就有几行。有一列叫做ID，如果此列为0，表示此ibt没有被使用，如果非0，表示被此ID的连接在用，比如ID为8，则表示process_id为8的连接在用这个ibt文件。另外，还有一列purpose，值为INTRINSIC表示是隐式临时表在用这个ibt，USER则表示是显示临时表在用。此外，还有一列size，表示当前的大小。用户可以查询这个表来确定整个数据库临时表的使用情况，十分方便。

在5.6和5.7中，内存临时表只能使用Memory引擎，到了8.0，多了一种Temptable引擎的选择。Temptable在存储格式有采用了变长存储，可以节省存储空间，进一步提高内存使用率，减少转换成磁盘临时表的次数。如果设置的磁盘临时表是InnoDB或者MYISAM，则需要一个转换拷贝的消耗。为了尽可能减少消耗，Temptable提出了一种overflow机制，即如果内存临时表超过配置大小，则使用磁盘空间map的方式，即打开一个文件，然后删除，留一个句柄进行读写操作。读写文件格式和内存中格式一样，这样就略过了转换这一步，进一步提高性能。注意，这个功能是在还没发布的8.0.16版本中才有的，因为还看不到代码，只能通过文档猜测其实现。在8.0.16中，参数internal_tmp_disk_storage_engine已经被去掉，磁盘临时表只能使用InnoDB形式或者TempTable的这种overflow形式。从文档中，我们似乎看出官方比较推荐使用TempTable这个新的引擎。具体性能提升情况，还需要等代码发布后，测试过才能得出结论。

临时文件

相比临时表，临时文件对大家可能更加陌生，临时文件更多的被使用在缓存数据，排序数据的场景中。一般情况下，被缓存或者排序的数据，首先放在内存中，如果内存放不下，才会使用磁盘临时文件的方式。临时文件的使用方式与一般的表也不太一样，一般的表创建完后，就开始读写数据，使用完后，才把文件删除，但是临时文件的使用方式不一样，在创建完后(使用mkstemp系统函数)，马上调用unlink删除文件，但是不close文件，后续使用原来的句柄操作文件。这样的好处是，当进程异常crash，不会有临时文件因为没被删除而残留，但是坏处也是明显的，我们在文件系统上使用ls命令就看不到这个文件，需要使用lsof +L1来查看这种deleted属性的文件。

目前，我们主要在一下场景使用临时文件：

DDL中的临时文件

在做online DDL的过程中，很多操作需要对原表进行重建，对表重建前，需要对各种二级索引排序，而大量数据的排序，不太可能在内存中完成，需要依赖外部排序算法，MySQL使用了归并排序。这个过程中就需要创建临时文件。一般需要的空间大小与原表差不多。但是在使用完之后，会马上清理，所以在做DDL的时候，需要保留出足够的空间。用户可以通过指定innodb_tmpdir来指定这种排序文件的路径。这个参数可以动态修改，一般把他设置在有足够磁盘空间的路径上。临时文件的名字一般是类似ibXXXXXX，其中ib是固定前缀，XXXXXX是大小写字母以及数字的随机组合。

在做online DDL中，我们是允许用户对原表做DML操作的，即增删改查。我们不能直接插入原表中，因此需要一个地方记录对原表的修改操作，在DDL结束后，再应用在新表上。这个记录的地方就是online log，当然如果改动少的话，直接存在内存里(参数innodb_sort_buffer_size可控制，同时这个参数也控制online log每个读写块的大小)面即可。这个onlinelog也是用临时文件存，创建在innodb_tmpdir，最大大小为参数innodb_online_alter_log_max_size控制，如果超过这个大小了，DDL就会失败。临时文件的名字也类似上述的排序临时文件的名字。

在online DDL的最后阶段，需要把排序完的文件和中途产生的DML全都应用到一个中间文件上，中间文件文件名类似#sql-ib53-522550444.ibd，其中#sql-ib是固定的前缀，53是InnoDB层的table id，522550444是随机生成的数字。同时，在server层也会生成一个frm文件(8.0中没有)，文件名类似#sql-4d2b_2a.frm，其中#sql是固定前缀，4d2b是进程号的十六进制表示，2a是线程号的十六进制表示(show processlist中的id)。因此我们也可以通过这个命名规则来找到哪个线程在做DDL。这里需要注意一点，这里说的中间文件，其实算是一个临时表，并不是上文说中临时文件，这些中间文件可以通过ls来查看。当在DDL中的最后一步，会把这两个临时文件命名回原来的表名。正因为这个特性，所以当数据库中途crash的时候，可能会在磁盘上留下残余无用的文件。遇到这种情况，可以先把frm文件重命名成与ibd文件一样的名字，然后使用DROP TABLE #mysql50##sql-ib53-522550444`来清理残余的文件。注意，如果不用drop命令，直接删除ibd文件，可能会导致数据字典里面依然有残余的信息，做法不太优雅。当然，在8.0中，由于使用了原子的数据字典，就不会出现这种残余文件了。

BinLog中的缓存操作

BinLog只有在事务提交的时候才会写入到文件中，在没提交前，会先放在内存中(由参数binlog_cache_size控制)，如果内存放慢了，就会创建临时文件，使用方法也是先通过mkstemp创建，然后直接unlink，留一个句柄读写。临时文件名类似MLXXXXXX，其中ML是固定前缀，XXXXXX是大小写字母以及数字的随机组合。单个事务的BinLog太大，可能会导致整个BinLog的大小也过大，从而影响同步，因此我们需要尽可能控制事务大小。

优化创建的临时文件

有些操作，除了在引擎层需要依赖隐式临时表来辅助复杂SQL的计算，在Server层，也会创建临时文件来辅助，比如order by操作，会调用filesort函数。这个函数也会先使用内存(sort_buffer_size)排序，如果不够，就会创建一个临时文件，辅助排序。文件名类似MYXXXXXX，其中MY是固定前缀，XXXXXX是大小写字母以及数字的随机组合。

Load data中用的临时文件

在BinLog复制中，如果在主库上使用了Load Data命令，即从文件中导数据，数据库会把整个文件写入到RelayLog中，然后传到备库，备库解析RelayLog，从中抽取出对应的Load文件，然后在备库上应用。备库上这个文件存储的位置由参数slave_load_tmpdir控制。文档中建议这个目录不要配置在物理机的内存目录或者重启后会删除的目录。因为复制依赖这个文件，如果意外被删除，会导致复制中断。

其他

除了上文所述的几个地方外，还有其他几个地方也会用到临时文件：

• 在InnoDB层，启动的时候会创建多个临时文件用来存储：最后一次外键或者唯一键错误; 最后一次死锁的信息; 最后的innodb状态信息。用临时文件而不用内存的原因猜测是，内存使用率不会因为写这些指标而波动。
• 在Server层，分区表使用show create table时，会用到临时文件。另外在MYISAM表内部排序的时候也会用到临时文件。

相关参数

*** tmpdir: *** 这个参数是临时目录的配置，在5.6以及之前的版本，临时表/文件默认都会放在这里。这个参数可以配置多个目录，这样就可以轮流在不同的目录上创建临时表/文件，如果不同的目录分别指向不同的磁盘，就可以达到分流的目的。
*** innodb_tmpdir: *** 这个参数只要是被DDL中的排序临时文件使用的。其占用的空间会很大，建议单独配置。这个参数可以动态设置，也是一个Session变量。
*** 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机制。

总结建议

MySQL的临时表以及临时文件其实是一个比较复杂的话题，涉及的模块比较多，出现的时机比较难把握，导致排查问题相比普通表也难不少。建议读者结合代码细细研究，这样才能定位在线上可能出现的棘手问题。