MySQL臨時表深入理解-mysql教程-PHP中文網

概述

#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 |
+----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+

登入後複製