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_normal\G
*************************** 1. row ***************************
       Table: t1_normal
Create 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 | <union1,2> | 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. 
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: 0

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       | 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中的临时表信息。
posted @ 2019-05-20 07:57  天士梦  阅读(29165)  评论(0编辑  收藏  举报