概述

MySQL中臨時(shí)表主要有兩類，包括外部臨時(shí)表和內(nèi)部臨時(shí)表。外部臨時(shí)表是通過語句create temporary table…創(chuàng)建的臨時(shí)表，臨時(shí)表只在本會(huì)話有效，會(huì)話斷開后，臨時(shí)表數(shù)據(jù)會(huì)自動(dòng)清理。內(nèi)部臨時(shí)表主要有兩類，一類是information_schema中臨時(shí)表，另一類是會(huì)話執(zhí)行查詢時(shí)，如果執(zhí)行計(jì)劃中包含有“Using temporary”時(shí)，會(huì)產(chǎn)生臨時(shí)表。內(nèi)部臨時(shí)表與外部臨時(shí)表的一個(gè)區(qū)別在于，我們看不到內(nèi)部臨時(shí)表的表結(jié)構(gòu)定義文件frm。而外部臨時(shí)表的表定義文件frm，一般是以#sql{進(jìn)程id}_{線程id}_序列號(hào)組成，因此不同會(huì)話可以創(chuàng)建同名的臨時(shí)表。

臨時(shí)表

臨時(shí)表與普通表的主要區(qū)別在于是否在實(shí)例，會(huì)話，或語句結(jié)束后，自動(dòng)清理數(shù)據(jù)。比如，內(nèi)部臨時(shí)表，我們?cè)谝粋€(gè)查詢中，如果要存儲(chǔ)中間結(jié)果集，而查詢結(jié)束后，臨時(shí)表就會(huì)自動(dòng)回收，不會(huì)影響用戶表結(jié)構(gòu)和數(shù)據(jù)。另外就是，不同會(huì)話的臨時(shí)表可以重名，所有多個(gè)會(huì)話執(zhí)行查詢時(shí)，如果要使用臨時(shí)表，不會(huì)有重名的擔(dān)憂。5.7引入了臨時(shí)表空間后，所有臨時(shí)表都存儲(chǔ)在臨時(shí)表空間(非壓縮)中，臨時(shí)表空間的數(shù)據(jù)可以復(fù)用。臨時(shí)表并非只支持Innodb引擎，還支持myisam引擎，memory引擎等。因此，臨時(shí)表我們看不到實(shí)體(idb文件)，但其實(shí)不一定是內(nèi)存表，也可能存儲(chǔ)在臨時(shí)表空間中。

臨時(shí)表 VS 內(nèi)存表

臨時(shí)表既可以innodb引擎表，也可以是memory引擎表。這里所謂的內(nèi)存表，是說memory引擎表，通過建表語句create table …engine=memory，數(shù)據(jù)全部在內(nèi)存，表結(jié)構(gòu)通過frm管理，同樣的內(nèi)部的memory引擎表，也是看不到frm文件中，甚至看不到information_schema在磁盤上的目錄。在MySQL內(nèi)部，information_schema里面的臨時(shí)表就包含兩類：innodb引擎的臨時(shí)表和memory引擎的臨時(shí)表。比如TABLES表屬于memory臨時(shí)表，而columns,processlist,屬于innodb引擎臨時(shí)表。內(nèi)存表所有數(shù)據(jù)都在內(nèi)存中，在內(nèi)存中數(shù)據(jù)結(jié)構(gòu)是一個(gè)數(shù)組(堆表)，所有數(shù)據(jù)操作都在內(nèi)存中完成，對(duì)于小數(shù)據(jù)量場(chǎng)景，速度比較快(不涉及物理IO操作)。但內(nèi)存畢竟是有限的資源，因此，如果數(shù)據(jù)量比較大，則不適合用內(nèi)存表，而是選擇用磁盤臨時(shí)表(innodb引擎)，這種臨時(shí)表采用B+樹存儲(chǔ)結(jié)構(gòu)(innodb引擎)，innodb的bufferpool資源是共享的，臨時(shí)表的數(shù)據(jù)可能會(huì)對(duì)bufferpool的熱數(shù)據(jù)有一定的影響，另外，操作可能涉及到物理IO。memory引擎表實(shí)際上也是可以創(chuàng)建索引的，包括Btree索引和Hash索引，所以查詢速度很快，主要缺陷是內(nèi)存資源有限。

使用臨時(shí)表的場(chǎng)景

前面提到執(zhí)行計(jì)劃中包含有“Using temporary”時(shí)，會(huì)使用臨時(shí)表，這里列兩個(gè)主要的場(chǎng)景。

測(cè)試表結(jié)構(gòu)如下：

mysql> show create table t1_normalG *************************** 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

場(chǎng)景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操作的含義是，取兩個(gè)子查詢結(jié)果的并集，重復(fù)的數(shù)據(jù)只保留一行，通過建立一個(gè)帶主鍵的臨時(shí)表，就可以解決“去重”問題，通過臨時(shí)表存儲(chǔ)最終的結(jié)果集，所以能看到執(zhí)行計(jì)劃中Extra這一項(xiàng)里面有“Using temporary”。與union相關(guān)的一個(gè)操作是union all，后者也是將兩個(gè)子查詢結(jié)果合并，但不解決重復(fù)問題。所以對(duì)于union all，沒有“去重”的含義，因此也就不需要臨時(shí)表了。

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

場(chǎng)景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的含義是按指定列分組，并默認(rèn)按照指定列有序。上面的SQL語句含義是將t1_normal中的數(shù)據(jù)按c1列的值分組，統(tǒng)計(jì)每種c1列值的記錄數(shù)目。從執(zhí)行計(jì)劃中我們看到了"Using temporary;Using filesort"，對(duì)于group by而言，我們首先需要統(tǒng)計(jì)每個(gè)值出現(xiàn)的數(shù)目，這就需要借助臨時(shí)表來快速定位，如果不存在，則插入一條記錄，如果存在，并累加計(jì)數(shù)，所以看到了"Using temporary"；然后又因?yàn)間roup by隱含了排序含義，所以還需要按照c1列進(jìn)行對(duì)記錄排序，所以看到了"Using filesort"。

1).消除filesort

實(shí)際上，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”后，執(zhí)行計(jì)劃中，不再出現(xiàn)“Using filesort”。

2).消除臨時(shí)表

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

可以看到執(zhí)行計(jì)劃中已經(jīng)沒有了“Using temporary”，所以group by并非一定依賴臨時(shí)表，臨時(shí)表在group by中的作用主要是“去重”。所以，實(shí)際上有另外一種方式，不使用臨時(shí)表，直接利用sort_buffer排序(sort_buffer不夠時(shí)，進(jìn)行文件排序，具體而言是每一個(gè)有序數(shù)組作為一個(gè)單獨(dú)文件，然后進(jìn)行外排歸并)，然后再掃描得到聚合后的結(jié)果集。

3).SQL_BIG_RESULT

同時(shí)我們語句中用到了“SQL_BIG_RESULT”這個(gè)hint，正是因?yàn)檫@個(gè)hint導(dǎo)致了我們沒有使用臨時(shí)表，先說說SQL_BIG_RESULT和SQL_SMALL_RESULT的含義。

SQL_SMALL_RESULT：顯示指定用內(nèi)存表(memory引擎)

SQL_BIG_RESULT：顯示指定用磁盤臨時(shí)表(myisam引擎或innodb引擎)

兩者區(qū)別在于，使用磁盤臨時(shí)表可以借助主鍵做去重排序，適合大數(shù)據(jù)量；使用內(nèi)存表寫入更快，然后在內(nèi)存中排序，適合小數(shù)據(jù)量。下面是從MySQL手冊(cè)中摘錄的說明。

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優(yōu)化器根據(jù)hint知道需要使用磁盤臨時(shí)表，而最終直接選擇了數(shù)組存儲(chǔ)+文件排序這種更輕量的方式。

如何避免使用臨時(shí)表

通常的SQL優(yōu)化方式是讓group by 的列建立索引，那么執(zhí)行g(shù)roup by時(shí)，直接按索引掃描該列，并統(tǒng)計(jì)即可，也就不需要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 | +----+-------------+-----------+------------+-------+---------------+--------+---------+------+--------+----------+-------------+

相關(guān)參數(shù)與狀態(tài)監(jiān)控

1).參數(shù)說明

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.

這個(gè)參數(shù)主要針對(duì)用戶創(chuàng)建的MEMORY表，限制內(nèi)存表最大空間大小，注意不是記錄數(shù)目，與單條記錄的長度有關(guān)。如果超出閥值，則報(bào)錯(cuò)。ERROR 1114 (HY000): The table 'xxx' is full

tmp_table_size

The maximum size of internal in-memory temporary tables.

對(duì)于用戶手工創(chuàng)建的內(nèi)存表，只有參數(shù)max_heap_table_size起作用；對(duì)于內(nèi)部產(chǎn)生的內(nèi)存表，則參數(shù)max_heap_table_size和tmp_table_size同時(shí)起作用。對(duì)于內(nèi)部產(chǎn)生的內(nèi)存表(比如union，group by等產(chǎn)生的臨時(shí)表)，先是采用內(nèi)存表(memory表)，然后超過設(shè)置的閥值(max_heap_table_size,tmp_table_size)就會(huì)轉(zhuǎn)為磁盤表，使用innodb引擎或者myisam引擎，通過參數(shù)internal_tmp_disk_storage_engine指定。

tmpdir

如果內(nèi)存臨時(shí)表超出了限制，MySQL就會(huì)自動(dòng)地把它轉(zhuǎn)化為基于磁盤的MyISAM表，存儲(chǔ)在指定的tmpdir目錄下

2.狀態(tài)監(jiān)控

Created_tmp_tables，內(nèi)部臨時(shí)表數(shù)目

Created_tmp_disk_tables，磁盤臨時(shí)表數(shù)目

3.information_schema相關(guān)

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

總結(jié)

本文詳細(xì)介紹了MySQL中臨時(shí)表的核心特征，按需創(chuàng)建并且自動(dòng)銷毀，對(duì)于純內(nèi)存的數(shù)據(jù)特別適合，但為了避免內(nèi)存不可控，實(shí)際上不僅僅有內(nèi)存臨時(shí)表，還有磁盤臨時(shí)表。臨時(shí)表和內(nèi)存表本沒有直接關(guān)聯(lián)，因?yàn)榕R時(shí)表既可以是memory引擎，又可以innodb引擎將兩者聯(lián)系到了一起，實(shí)際上不同類別的臨時(shí)表也是用到了不同引擎的優(yōu)勢(shì)。臨時(shí)表使用的典型場(chǎng)景是union和group by。為了消除臨時(shí)表，我們需要對(duì)group by列添加索引，或者對(duì)于大結(jié)果集，使用SQL_BIG_RESULT等。最后本文介紹了臨時(shí)表相關(guān)的參數(shù)和狀態(tài)變量，以及information_schema中的臨時(shí)表信息。

推薦教程：《MySQL教程》