MySQL暂时表深入了解-百分百源码网

MySQL暂时表深入了解

发布时间：09/01 来源：未知浏览：关键词：

概述

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中的暂时表信息。

引荐教程：《MySQL教程》

以上就是MySQL暂时表深入懂得的具体内容，更多请关注百分百源码网其它相关文章！

打赏