以下摘录总结来自mysql内核月报：http://mysql.taobao.org/monthly/2019/04/01/
在MySQL 5.7后，磁盘临时表的数据和undo都被独立出来，放在一个单独的表空间ibtmp1里面。
在MySQL 8.0后，磁盘临时表的数据单独放在Session临时表空间池(#innodb_temp目录下的ibt文件)里面，临时表的undo放在global的表空间ibtmp1里面。8.0的磁盘临时表数据占用的空间在连接断开后，就能释放给操作系统，而5.7的版本中需要重启才能释放。
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临时表空间
mysql> show variables like '%innodb_temp_tablespaces_dir%';
+-----------------------------+-----------------+
| Variable_name | Value |
+-----------------------------+-----------------+
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
+-----------------------------+-----------------+
存放undo的ibtmp叫做Global临时表空间
mysql> show variables like '%innodb_temp_data_file_path%';
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
查看临时表表名
select * from INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
一般稍微复杂一点的查询，包括且不限于order by, group by, distinct等，都会用到这种隐式创建的临时表。通过explain命令，在Extra列中，看是否有Using temporary这样的字样，如果有，就肯定要用临时表．
查询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，表示当前的大小
mysql> select * from INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES;
+----+------------+----------------------------+-------+----------+-----------+
| ID | SPACE | PATH | SIZE | STATE | PURPOSE |
+----+------------+----------------------------+-------+----------+-----------+
| 8 | 4294501266 | ./#innodb_temp/temp_10.ibt | 81920 | ACTIVE | INTRINSIC |
| 0 | 4294501257 | ./#innodb_temp/temp_1.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501258 | ./#innodb_temp/temp_2.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501259 | ./#innodb_temp/temp_3.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501260 | ./#innodb_temp/temp_4.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501261 | ./#innodb_temp/temp_5.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501262 | ./#innodb_temp/temp_6.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501263 | ./#innodb_temp/temp_7.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501264 | ./#innodb_temp/temp_8.ibt | 81920 | INACTIVE | NONE |
| 0 | 4294501265 | ./#innodb_temp/temp_9.ibt | 81920 | INACTIVE | NONE |
+----+------------+----------------------------+-------+----------+-----------+
在做online DDL的过程中，需要创建临时文件
innodb_tmpdir来指定这种排序文件的路径
-rw-r----- 1 my4999 mysql 8855 1月 12 21:28 '#sql-5299_3.frm'
-rw-r----- 1 my4999 mysql 1962934272 1月 12 21:41 '#sql-ib1280-3822660619.ibd'
order by操作，会调用filesort函数。这个函数也会先使用内存(sort_buffer_size)排序，如果不够，就会创建一个临时文件
文件名类似MYXXXXXX，其中MY是固定前缀，XXXXXX是大小写字母以及数字的随机组合
网友评论