MySQL临时表深入理解

概述

MySQL中临时表主要有两类，包括外部临时表和内部临时表。外部临时表是通过语句create temporary table…创建的临时表，临时表只在本会话有效，会话断开后，临时表数据会自动清理。内部临时表主要有两类，一类是information_schema中临时表，另一类是会话执行查询时，如果执行计划中包含有“Using temporary”时，会产生临时表。内部临时表与外部临时表的一个区别在于，我们看不到内部临时表的表结构定义文件frm。而外部临时表的表定义文件frm，一般是以#sql{进程id}_{线程id}_序列号组成，因此不同会话可以创建同名的临时表。

临时表

临时表与普通表的主要区别在于是否在实例，会话，或语句结束后，自动清理数据。比如，内部临时表，我们在一个查询中，如果要存储中间结果集，而查询结束后，临时表就会自动回收，不会影响用户表结构和数据。另外就是，不同会话的临时表可以重名，所有多个会话执行查询时，如果要使用临时表，不会有重名的担忧。5.7引入了临时表空间后，所有临时表都存储在临时表空间(非压缩)中，临时表空间的数据可以复用。临时表并非只支持Innodb引擎，还支持myisam引擎，memory引擎等。因此，临时表我们看不到实体(idb文件)，但其实不一定是内存表，也可能存储在临时表空间中。

临时表 VS 内存表

临时表既可以innodb引擎表，也可以是memory引擎表。这里所谓的内存表，是说memory引擎表，通过建表语句create table …engine=memory，数据全部在内存，表结构通过frm管理，同样的内部的memory引擎表，也是看不到frm文件中，甚至看不到information_schema在磁盘上的目录。在MySQL内部，information_schema里面的临时表就包含两类：innodb引擎的临时表和memory引擎的临时表。比如TABLES表属于memory临时表，而columns,processlist,属于innodb引擎临时表。内存表所有数据都在内存中，在内存中数据结构是一个数组(堆表)，所有数据操作都在内存中完成，对于小数据量场景，速度比较快(不涉及物理IO操作)。但内存毕竟是有限的资源，因此，如果数据量比较大，则不适合用内存表，而是选择用磁盘临时表(innodb引擎)，这种临时表采用B+树存储结构(innodb引擎)，innodb的bufferpool资源是共享的，临时表的数据可能会对bufferpool的热数据有一定的影响，另外，操作可能涉及到物理IO。memory引擎表实际上也是可以创建索引的，包括Btree索引和Hash索引，所以查询速度很快，主要缺陷是内存资源有限。

使用临时表的场景

前面提到执行计划中包含有“Using temporary”时，会使用临时表，这里列两个主要的场景。

测试表结构如下：

mysql> show create table t1_normalG*************************** 1. row ***************************       Table: t1_normalCreate Table: CREATE TABLE `t1_normal` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `c1` int(11) DEFAULT NULL,  `c2` int(11) DEFAULT NULL,  `c3` int(11) DEFAULT NULL,  `c4` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=770023 DEFAULT CHARSET=utf8

场景1：union

mysql> explain select * from t1_normal union select * from t1_normal; +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+ | 1 | PRIMARY | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | 2 | UNION | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | NULL | | NULL | UNION RESULT |  | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

union操作的含义是，取两个子查询结果的并集，重复的数据只保留一行，通过建立一个带主键的临时表，就可以解决“去重”问题，通过临时表存储最终的结果集，所以能看到执行计划中Extra这一项里面有“Using temporary”。与union相关的一个操作是union all，后者也是将两个子查询结果合并，但不解决重复问题。所以对于union all，没有“去重”的含义，因此也就不需要临时表了。

mysql> explain select * from t1_normal  union  all select * from t1_normal;+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+|  1 | PRIMARY     | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  ||  2 | UNION       | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | NULL  |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+

场景2：group by

mysql> explain select c1,count(*) as count from t1_normal group by c1;+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                           |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+|  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary; Using filesort |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+

group by的含义是按指定列分组，并默认按照指定列有序。上面的SQL语句含义是将t1_normal中的数据按c1列的值分组，统计每种c1列值的记录数目。从执行计划中我们看到了”Using temporary;Using filesort”，对于group by而言，我们首先需要统计每个值出现的数目，这就需要借助临时表来快速定位，如果不存在，则插入一条记录，如果存在，并累加计数，所以看到了”Using temporary”；然后又因为group by隐含了排序含义，所以还需要按照c1列进行对记录排序，所以看到了”Using filesort”。

1).消除filesort

实际上，group by也可以显示消除“排序含义”。

mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null;+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+|  1 | SIMPLE      | t1_normal | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 523848 |   100.00 | Using temporary |+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-----------------+

可以看到，语句中加上“order by null”后，执行计划中，不再出现“Using filesort”。

2).消除临时表

mysql> explain select SQL_BIG_RESULT c1,count(*) as count from t1_normal group by c1; +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | t1_normal | NULL | ALL | NULL | NULL | NULL | NULL | 523848 | 100.00 | Using filesort | +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+

可以看到执行计划中已经没有了“Using temporary”，所以group by并非一定依赖临时表，临时表在group by中的作用主要是“去重”。所以，实际上有另外一种方式，不使用临时表，直接利用sort_buffer排序(sort_buffer不够时，进行文件排序，具体而言是每一个有序数组作为一个单独文件，然后进行外排归并)，然后再扫描得到聚合后的结果集。

3).SQL_BIG_RESULT

同时我们语句中用到了“SQL_BIG_RESULT”这个hint，正是因为这个hint导致了我们没有使用临时表，先说说SQL_BIG_RESULT和SQL_SMALL_RESULT的含义。

SQL_SMALL_RESULT：显示指定用内存表(memory引擎)

SQL_BIG_RESULT：显示指定用磁盘临时表(myisam引擎或innodb引擎)

两者区别在于，使用磁盘临时表可以借助主键做去重排序，适合大数据量；使用内存表写入更快，然后在内存中排序，适合小数据量。下面是从MySQL手册中摘录的说明。

SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. 

Modoer多功能点评系统2.5 精华版 Build 20110710 GBK

Modoer 是一款以本地分享，多功能的点评网站管理系统。采用 PHP+MYSQL 开发设计，开放全部源代码。因具有非凡的访问速度和卓越的负载能力而深受国内外朋友的喜爱，不局限于商铺类点评，真正实现了多类型的点评，可以让您的网站点评任何事与物，同时增加产品模块，也更好的网站产品在网站上展示。Modoer点评系统 2.5 Build 20110710更新列表1.同步 旗舰版系统框架2.增加 限制图片

0 查看详情

For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements. 

For SQL_SMALL_RESULT, MySQL uses fast temporary tables to store the resulting table instead of using sorting. 

This should not normally be needed.

回到问题本身，这里MySQL优化器根据hint知道需要使用磁盘临时表，而最终直接选择了数组存储+文件排序这种更轻量的方式。

如何避免使用临时表

通常的SQL优化方式是让group by 的列建立索引，那么执行group by时，直接按索引扫描该列，并统计即可，也就不需要temporary和filesort了。

mysql> alter table t1_normal add index idx_c1(c1);Query OK, 0 rows affected (1 min 23.82 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> explain select c1,count(*) as count from t1_normal group by c1 order by null;+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+| id | select_type | table     | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra       |+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+|  1 | SIMPLE      | t1_normal | NULL       | index | idx_c1        | idx_c1 | 5       | NULL | 523848 |   100.00 | Using index |+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+

相关参数与状态监控

1).参数说明

max_heap_table_size

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow,The value of the variable is used to calculate MEMORY table MAX_ROWS values.

这个参数主要针对用户创建的MEMORY表，限制内存表最大空间大小，注意不是记录数目，与单条记录的长度有关。如果超出阀值，则报错。ERROR 1114 (HY000): The table ‘xxx’ is full

tmp_table_size

The maximum size of internal in-memory temporary tables.

对于用户手工创建的内存表，只有参数max_heap_table_size起作用；对于内部产生的内存表，则参数max_heap_table_size和tmp_table_size同时起作用。对于内部产生的内存表(比如union，group by等产生的临时表)，先是采用内存表(memory表)，然后超过设置的阀值(max_heap_table_size,tmp_table_size)就会转为磁盘表，使用innodb引擎或者myisam引擎，通过参数internal_tmp_disk_storage_engine指定。

tmpdir

如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下

2.状态监控

Created_tmp_tables，内部临时表数目

Created_tmp_disk_tables，磁盘临时表数目

3.information_schema相关

mysql> create temporary table t1_tmp(id int primary key,c1 int); Query OK, 0 rows affected (0.02 sec) mysql> SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO; +----------+---------------+--------+-------+----------------------+---------------+ | TABLE_ID | NAME | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED | +----------+---------------+--------+-------+----------------------+---------------+ | 10063 | #sql693d_29_0 | 5 | 45 | FALSE | FALSE | +----------+---------------+--------+-------+----------------------+---------------+

总结

本文详细介绍了MySQL中临时表的核心特征，按需创建并且自动销毁，对于纯内存的数据特别适合，但为了避免内存不可控，实际上不仅仅有内存临时表，还有磁盘临时表。临时表和内存表本没有直接关联，因为临时表既可以是memory引擎，又可以innodb引擎将两者联系到了一起，实际上不同类别的临时表也是用到了不同引擎的优势。临时表使用的典型场景是union和group by。为了消除临时表，我们需要对group by列添加索引，或者对于大结果集，使用SQL_BIG_RESULT等。最后本文介绍了临时表相关的参数和状态变量，以及information_schema中的临时表信息。

推荐教程：《MySQL教程》

以上就是MySQL临时表深入理解的详细内容，更多请关注创想鸟其它相关文章！