MySQL 5.7内部临时表使用

一、MySQL中的两种临时表

外部临时表

通过CREATE TEMPORARY TABLE 创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。

内部临时表

内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW STATUS可以查看MySQL是否使用了内部临时表用来帮助完成某个操作。内部临时表在SQL语句的优化过程中扮演着非常重要的角色， MySQL中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。

内部临时表有两种类型：一种是HEAP临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要IO操作。另一种是OnDisk临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk临时表用来处理中间结果比较大的操作。如果HEAP临时表存储的数据大于MAX_HEAP_TABLE_SIZE（详情请参考MySQL手册中系统变量部分），HEAP临时表将会被自动转换成OnDisk临时表。OnDisk临时表在5.7中可以通过INTERNAL_TMP_DISK_STORAGE_ENGINE系统变量选择使用MyISAM引擎或者InnoDB引擎。

本篇文章主要介绍哪些操作可能会利用到内部临时表。如果用户在书写SQL语句的时候能够尽量少的使用内部临时表进行查询优化，将有效的提高查询执行的效率。

二、内部临时表使用

在某些情况下，服务器在处理语句时创建内部临时表，而用户无法直接控制临时表何时发生，完全由MySQL内部自行决定。

MySQL在以下几种情况会创建临时表：

1、UNION查询（MySQL 5.7起，执行UNION ALL不再产生临时表，除非需要额外排序。）；

2、用到TEMPTABLE算法或者是UNION查询中的视图；

3、ORDER BY和GROUP BY的子句不一样时；

4、表连接中，ORDER BY的列不是驱动表中的；

5、DISTINCT查询并且加上ORDER BY时；

6、SQL中用到SQL_SMALL_RESULT修饰符的查询；

7、FROM中的子查询（派生表）；

8、子查询或者semi-join时创建的表；

9、评估多表UPDATE语句；

10、评价GROUP_CONCAT()或COUNT(DISTINCT) 表达式计算；

要确定语句是否需要临时表，请使用EXPLAIN并检查Extra列以查看是否显示Using temporary。但对于派生或实物化的临时表EXPLAIN不一定会显示Using temporary。

MySQL内部参数tmp_table_size表示内部的临时表的最大值，其实生效的是tmp_table_size和max_heap_table_size这两个值之间的最小的那个值。当创建的临时表超过这个值(或者max_heap_table_size)时，MySQL将会在磁盘上创建临时表。

mysql> show global variables like '%table_size%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+
2 rows in set (0.00 sec)

mysql> show global variables like '%table_size%';

+---------------------+----------+

| Variable_name | Value |

+---------------------+----------+

| max_heap_table_size | 16777216 |

| tmp_table_size | 16777216 |

+---------------------+----------+

2 rows in set (0.00 sec)

当服务器创建内部临时表（在内存或磁盘上）时，会增加Created_tmp_tables状态变量（SHOW PROCESSLIST可以看到）。如果服务器在磁盘上创建表（最初或通过转换内存中的表），它会增加Created_tmp_disk_tables状态变量。

mysql> show global status like '%Created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 12495 |
| Created_tmp_files       | 12268 |
| Created_tmp_tables      | 39887 |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show global status like '%Created_tmp%';

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| Created_tmp_disk_tables | 12495 |

| Created_tmp_files | 12268 |

| Created_tmp_tables | 39887 |

+-------------------------+-------+

3 rows in set (0.00 sec)

通过检查Created_tmp_disk_tables和Created_tmp_tables这两个global状态值来判断在磁盘上创建临时表的次数来进行相应的调优。

某些查询条件阻止使用内存中临时表，在以下几种情况下，会创建磁盘临时表：

1、表中存在BLOB或TEXT列；

2、在SELECT列表中存在任何字符串列的最大长度大于512（二进制字符串的字节，非二进制字符的字符），如果被UNION或UNION ALL使用；

3、SHOW COLUMNS FROM DB和DESCRIBE语句中使用BLOB作为用于某些列的类型；

服务器不使用符合特定条件的UNION语句的临时表。相反, 它只保留临时表创建执行结果列类型转换所需的数据结构。该表没有完全实例化, 并且没有写入或读取任何行，行直接发送到客户端。结果是减少了内存和磁盘要求, 并且在第一行发送到客户端之前的延迟较小, 因为服务器不需要等到执行最后一个查询块。解释和优化器跟踪输出反映了此执行策略: UNION结果查询块不存在, 因为该块对应于从临时表中读取的部分。

三、用于临时表的存储引擎

内部临时表可以在内存中保持并且由MEMORY存储引擎处理，或者由存储在磁盘上的InnoDB或MyISAM存储引擎处理。

如果内部临时表被创建为内存中的表，但是变得太大后，MySQL会自动将其转换为磁盘表。内存中临时表的最大大小是由tmp_table_size和max_heap_table_size两个值中的较小值的决定。这与使用create table显式创建的内存引擎表不同，对于此类表，只有max_heap_table_size系统变量确定允许表增长的大小，并且不能转换为磁盘上的格式。

从MySQL 5.7.5开始，新增一个系统选项internal_tmp_disk_storage_engine可定义磁盘临时表的引擎类型为InnoDB，如下查看方式：

mysql> show global variables like '%internal_tmp_disk_storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
1 row in set (0.00 sec)

mysql> show global variables like '%internal_tmp_disk_storage_engine%';

+----------------------------------+--------+

| Variable_name | Value |

+----------------------------------+--------+

| internal_tmp_disk_storage_engine | InnoDB |

+----------------------------------+--------+

1 row in set (0.00 sec)

而在这以前，只能使用MyISAM存储引擎。这都是一些历史因素了，Oracle官方正在逐步重构MySQL各个模块，相信很快在MySQL内部就看不见除了InnoDB存储因为外的任何引擎了。MySQL 8.0开始已经把所有的元数据表从MyISAM替换为了InnoDB。

并且在MySQL 5.6.3以后新增的系统参数default_tmp_storage_engine，是用来控制CREATE TEMPORARY TABLE创建的外部临时表的引擎类型，在以前版本默认是MEMORY。查看方式如下：

mysql> show global variables like '%default_tmp_storage_engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB |
+----------------------------+--------+
1 row in set (0.00 sec)

mysql> show global variables like '%default_tmp_storage_engine%';

+----------------------------+--------+

| Variable_name | Value |

+----------------------------+--------+

| default_tmp_storage_engine | InnoDB |

+----------------------------+--------+

1 row in set (0.00 sec)

这里不要把这内部临时表存储引擎和外部临时表存储引擎二者混淆了，internal_tmp_disk_storage_engine是用来定义内部临时表存储引擎，而default_tmp_storage_engine是用来定义外部临时表存储引擎。

如下，创建一个外部临时表（当前线程退出临时表就会删除）：

mysql> CREATE TEMPORARY TABLE tt(id int);           
Query OK, 0 rows affected (0.00 sec)

1 2	mysql> CREATE TEMPORARY TABLE tt(id int); Query OK, 0 rows affected (0.00 sec)

在tmpdir参数控制存放磁盘路径的目录下有frm文件（表结构文件），如下：

mysql> show global variables like 'tmpdir'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'tmpdir';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| tmpdir | /tmp |

+---------------+-------+

1 row in set (0.00 sec)

$ ll /tmp/
total 12
-rw-r----- 1 mysql mysql 8632 Sep  5 23:59 #sql298d_5c2_0.frm

$ ll /tmp/

total 12

-rw-r----- 1 mysql mysql 8632 Sep 5 23:59 #sql298d_5c2_0.frm

但是这里只能看到frm文件（如果是MySQL 5.6还可以看见表空间文件），因为MySQL 5.7开始增加了临时表空间ibtmp1文件，因此对于InnoDB表数据都存储在ibtmp1文件中，包括外部临时表和内部临时表。

四、内部临时表存储格式

内存中临时表由MEMORY存储引擎管理，MEMORY存储引擎使用固定长度的行格式。并将VARCHAR和VARBINARY类型填充到最大列长度，实际上将它们存储为CHAR和BINARY类型。

在磁盘上的临时表由管理InnoDB或MyISAM存储引擎（取决于internal_tmp_disk_storage_engine设置）。两个引擎使用dynamic-width行格式存储临时表。列只需要尽可能多的存储空间，与使用固定长度行的磁盘表相比，减少了磁盘I/O和空间要求以及处理时间。

对于最初在内存中创建内部临时表的语句，然后将其转换为磁盘表，可能会通过跳过转换步骤并在磁盘上创建表来实现更好的性能。所述big_tables系统变量可以用来迫使内部临时表的磁盘存储。

mysql> show global variables like '%big_tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| big_tables    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show global variables like '%big_tables%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| big_tables | OFF |

+---------------+-------+

1 row in set (0.01 sec)

五、内部临时表空间使用

MySQL 5.7起，开始采用独立的临时表空间（和独立的undo表空间不是一回事哟），命名ibtmp1文件，初始化12M，且默认无上限。

选项innodb_temp_data_file_path可配置临时表空间相关参数，默认存储在datadir数据目录下。

mysql> show global variables like '%innodb_temp_data_file_path%';
+----------------------------+-----------------------+
| Variable_name              | Value                 |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)

mysql> show global variables like '%innodb_temp_data_file_path%';

+----------------------------+-----------------------+

| Variable_name | Value |

+----------------------------+-----------------------+

| innodb_temp_data_file_path | ibtmp1:12M:autoextend |

+----------------------------+-----------------------+

1 row in set (0.00 sec)

临时表空间的几点说明

1、临时表空间不像普通InnoDB表空间那样，不支持裸设备（raw device）。

2、临时表空间使用动态的表空间ID，因此每次重启时都会变化（每次重启时，都会重新初始化临时表空间文件）。

3、当选项设置错误或其他原因（权限不足等原因）无法创建临时表空间时，mysqld实例也无法启动。

4、临时表空间中存储这非压缩的InnoDB临时表，如果是压缩的InnoDB临时表，则需要单独存储在各自的表空间文件中，文件存放在 tmpdir（/tmp）目录下。

5、临时表元数据存储在INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO视图中。

临时表使用的几点建议

1、ibtmp1文件默认保存在InnoDB的数据目录，原本MyISAM临时表被放在MySQL的tmp目录，如若像MyISAM一样把临时表文件存储在MySQL的tmp目录，需要更改为innodb_temp_data_file_path=../../../tmp/ibtmp1:12M:autoextend。

2、设置innodb_temp_data_file_path选项，设定文件最大上限（innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:200M），超过上限时，需要生成临时表的SQL无法被执行（一般这种SQL效率也比较低，可借此机会进行优化）。

3、内部临时InnoDB表同样共享常规的InnoDB表的限制，如行或列的最大数量限制，超过最大值后，会返回Row size too large” or “Too many columns”的错误，遇到此种情况，可以将默认临时表引擎改回MyISAM。

4、检查INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO，找到最大的临时表对应的线程，kill之即可释放，但ibtmp1文件则不能释放（除非重启）。

5、择机重启实例，释放ibtmp1文件，和ibdata1不同，ibtmp1重启时会被重新初始化而ibdata1则不可以。

6、定期检查运行时长超过N秒（比如N=300）的SQL，考虑干掉，避免垃圾SQL长时间运行影响业务。

临时表测试案例

你可以往CREATE TEMPORARY TABLE里插入数据，ibtmp1会持续增长。另外你也可以产生一个union查询的慢SQL，MySQL 5.7起，执行UNION ALL不再产生临时表（除非需要额外排序）。

mysql> select * from sbtest1 union select * from sbtest2;

1	mysql> select * from sbtest1 union select * from sbtest2;

查看ibtmp1文件会持续增长。

$ du -sh /var/lib/mysql/ibtmp1 
7.8G    /var/lib/mysql/ibtmp1

1 2	$ du -sh /var/lib/mysql/ibtmp1 7.8G /var/lib/mysql/ibtmp1

有时执行SQL请求时会产生临时表，极端情况下，可能导致临时表空间文件暴涨，有案例中最高涨到快300G，比以前遇到的ibdata1文件暴涨还要猛。所以对于临时表空间的使用也是一定要多注意的。

在没有临时表空间之前，产生的临时表都在tmpdir目录下。当产生临时表过大时，如果你使用du -h命令去统计是看不到效果的，但df -h却看到磁盘剩余空间变小。使用lsof命令是可以看到对应的 deleted 文件。如果太大导致磁盘空间满，会造成 MySQL crash，这时候尝试kill查询进程或重启占用文件句柄的进程可以快速解决问题。

六、内部临时表性能测试

关于内部临时表从MyISAM换到InnoDB后，在性能方面怎么样呢？MySQL Server团队写过一篇关于MySQL 5.7 InnoDB原生表性能的文章，InnoDB的内部临时表的操作并不会记录在redo和undo中，一般情况下相比原来MyISAM引擎的临时表性能更好。

需要注意的，当所有的临时表都改成InnoDB引擎后，会增加引擎的负载，影响到其他的查询。例如：当所有的表都放入buffer_pool中，且临时表都不是InnoDB引擎，那么不会对InnoDB的内存占用造成任何影响，但是临时表改成InnoDB引擎后，会和普通InnoDB表一样占用InnoDB_buffer_pool的空间，而且可能因为临时表空间占用过大挤出真正的热数据，让某些高频查询变慢。

所以有时候从监控上可能会看到MySQL读写并不多，但是InnoDB Row发生性能尖刺的情形，如下图：