Mysql 临时表空间

一、前言

最近研究InnoDB的官方文档，看到了临时表空间一节，回想起之前碰到过客户执行SQL语句报ERROR-1114 The table ‘/tmp/xxxxxx’ is full的报错，当时也是查官方文档、查MOS才明白MySQL的临时表机制，而且从5.6-8.0.32，不同版本的临时表使用方法变化也是很大，所以决定梳理一下。

二、临时表

临时表空间可以简单的理解为存储临时表的，这种临时表更像是一种中间数据，比如我们在执行UNION、聚合函数或者insert into ...select这样的语句时会产生一些中间数据，这些数据会存放到临时表中，统称为内部临时表，如果内部临时表比较小，可以存放在内存中，称为in-memeory，但是很多情况下临时表会很大，当临时表超过配置的 参数阈值时，就会存放到磁盘上，称为on-disk，官方文档描述为溢出到磁盘。临时表空间就是存储on-disk的临时表。

内部临时表的使用场景参考官方文档：8.4.4 Internal Temporary Table Use in MySQL

三、临时表空间的演变

version 5.6：没有临时表空间的概念，如果启用innodb_file_per_table，内部临时表超出内存限制后在临时目录创建on-disk临时表；如果禁用innodb_file_per_table，则会在系统表空间ibdata1中创建on-disk临时表 ，这样会造成系统表空间过大无法释放，只能通过逻辑迁移实现空间释放

version5.7 ：临时表单独存放于临时表空间ibtmp1，这样就方便临时表空间的释放（重启就可释放），还能提高临时表的创建、删除效率

相关参数：

innodb_temp_data_file_path：默认ibtmp1:12M:autoextend，表空间名ibtmp1；初始大小12M；自动扩展，位于datadir目录下。

internal_tmp_disk_storage_engine：内部临时表转换为磁盘临时表时使用的存储引擎，默认为InnoDB，也可以指定为MyISAM。

tmp_table_size&max_heap_table_size：内存临时表阈值，超过这两个参数较小值，则溢出到磁盘

version 8.0：对内部临时表和临时表空间有了很大的优化。除了系统（全局）临时表空间ibtmp1外，新增会话临时表空间。

相关参数：

innodb_temp_tablespaces_dir ：会话临时表空间的位置，默认为datadir下的 #innodb_temp

internal_tmp_mem_storage_engine：默认为TempTable，也可以设置为MEMORY。

如果选用MEMORY选项，通过tmp_table_size（默认为16m）参数来控制驻存到内存的临时表的数据量大小，超过此值，则溢出到磁盘处理。

如果选用TempTable选项，通过temptable_max_ram（默认为1G）参数来控制驻存到内存的临时表数据量，超过此值，则溢出到磁盘。Temptable支持的数据类型更全，功能更强大。但是存在一些性能问题和BUG，慎用！（参考MOS文档：2400948.1）

internal_tmp_disk_storage_engine：在8.0.16被弃用，on-disk的临时表统一采用InnoDB存储引擎。

temptable_use_mmap&temptable_max_mmap：temptable_use_mmap参数是8.0.16引入，定义是否启用内存映射临时表，意思就是不用on-disk的临时表空间，超过tmp_table_size阈值，后先转为temptable存储引擎的内存映射的临时文件，超过temptable_max_ram阈值后再溢出到磁盘，但是temptable存储引擎的内存映射的临时文件本质还是内存，会影响性能，引发BUG，因此在8.0.26被弃用。emptable_max_mmap参数8.0.23引入，定义 TempTable 存储引擎在开始将数据存储到磁盘上的 InnoDB 内部临时表之前，被允许从内存映射的临时文件分配的最大内存量（以字节为单位）。设置为0将禁用从内存映射的临时文件分配内存。默认值1G。

四、会话临时表空间

当MySQL服务启动时，会创建一个临时表空间池用于会话临时表空间，临时表空间池由innodb_temp_tablespaces_dir参数指定，默认在datadir下的#innodb_temp目录下，一个临时表空间池会包含10个.ibt扩展的临时表空间数据文件，一个文件大小为5个page size（81920）。

 

当会话首次申请on-disk临时表时，mysql会分配给该会话2个表空间（数据文件），一个用于用户创建的临时表；另一个用户内部临时表。当会话断开连接，这俩临时表空间会被阶段并释放。

INNODB_SESSION_TEMP_TABLESPACES ：存放会话临时表空间的元数据。

INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO ：存放innodb实例活动的用户创建的临时表的元数据。

五、全局临时表空间

8.0版本之后全局临时表空间就只用于存放用户创建的临时表更改的回滚段。

由参数innodb_temp_data_file_path 配置，默认ibtmp1:12M:autoextend。在mysql服务启动时创建，关闭时截断并释放。

全局临时表空间不能存放在裸设备上。

为了防止全局表空间无限制增长可以配置最大值

innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

修改此参数需要重启服务器。