概述
mysql中臨時表主要有兩類，包括外部臨時表和內(nèi)部臨時表。外部臨時表是通過語句create temporary table…創(chuàng)建的臨時表，臨時表只在本會話有效，會話斷開后，臨時表數(shù)據(jù)會自動清理。內(nèi)部臨時表主要有兩類，一類是information_schema中臨時表，另一類是會話執(zhí)行查詢時，如果執(zhí)行計劃中包含有“Using temporary”時，會產(chǎn)生臨時表。內(nèi)部臨時表與外部臨時表的一個區(qū)別在于，我們看不到內(nèi)部臨時表的表結(jié)構(gòu)定義文件frm。而外部臨時表的表定義文件frm，一般是以#sql{進程id}_{線程id}_序列號組成，因此不同會話可以創(chuàng)建同名的臨時表。
臨時表
臨時表與普通表的主要區(qū)別在于是否在實例，會話，或語句結(jié)束后，自動清理數(shù)據(jù)。比如，內(nèi)部臨時表，我們在一個查詢中，如果要存儲中間結(jié)果集，而查詢結(jié)束后，臨時表就會自動回收，不會影響用戶表結(jié)構(gòu)和數(shù)據(jù)。另外就是，不同會話的臨時表可以重名，所有多個會話執(zhí)行查詢時，如果要使用臨時表，不會有重名的擔憂。5.7引入了臨時表空間后，所有臨時表都存儲在臨時表空間(非壓縮)中，臨時表空間的數(shù)據(jù)可以復用。臨時表并非只支持Innodb引擎，還支持myisam引擎，memory引擎等。因此，臨時表我們看不到實體(idb文件)，但其實不一定是內(nèi)存表，也可能存儲在臨時表空間中。
臨時表 VS 內(nèi)存表
臨時表既可以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里面的臨時表就包含兩類：innodb引擎的臨時表和memory引擎的臨時表。比如TABLES表屬于memory臨時表，而columns,processlist,屬于innodb引擎臨時表。內(nèi)存表所有數(shù)據(jù)都在內(nèi)存中，在內(nèi)存中數(shù)據(jù)結(jié)構(gòu)是一個數(shù)組(堆表)，所有數(shù)據(jù)操作都在內(nèi)存中完成，對于小數(shù)據(jù)量場景，速度比較快(不涉及物理IO操作)。但內(nèi)存畢竟是有限的資源，因此，如果數(shù)據(jù)量比較大，則不適合用內(nèi)存表，而是選擇用磁盤臨時表(innodb引擎)，這種臨時表采用B+樹存儲結(jié)構(gòu)(innodb引擎)，innodb的bufferpool資源是共享的，臨時表的數(shù)據(jù)可能會對bufferpool的熱數(shù)據(jù)有一定的影響，另外，操作可能涉及到物理IO。memory引擎表實際上也是可以創(chuàng)建索引的，包括Btree索引和Hash索引，所以查詢速度很快，主要缺陷是內(nèi)存資源有限。
使用臨時表的場景
前面提到執(zhí)行計劃中包含有“Using temporary”時，會使用臨時表，這里列兩個主要的場景。
測試表結(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
場景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>?|?NULL?|?ALL?|?NULL?|?NULL?|?NULL?|?NULL?|?NULL?|?NULL?|?Using?temporary?|?+----+--------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+</union1>
union操作的含義是，取兩個子查詢結(jié)果的并集，重復的數(shù)據(jù)只保留一行，通過建立一個帶主鍵的臨時表，就可以解決“去重”問題，通過臨時表存儲最終的結(jié)果集，所以能看到執(zhí)行計劃中Extra這一項里面有“Using temporary”。與union相關(guān)的一個操作是union all，后者也是將兩個子查詢結(jié)果合并，但不解決重復問題。所以對于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中的數(shù)據(jù)按c1列的值分組，統(tǒng)計每種c1列值的記錄數(shù)目。從執(zhí)行計劃中我們看到了”Using temporary;Using filesort”，對于group by而言，我們首先需要統(tǒng)計每個值出現(xiàn)的數(shù)目，這就需要借助臨時表來快速定位，如果不存在，則插入一條記錄，如果存在，并累加計數(shù)，所以看到了”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”后，執(zhí)行計劃中，不再出現(xiàn)“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?|?+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
可以看到執(zhí)行計劃中已經(jīng)沒有了“Using temporary”，所以group by并非一定依賴臨時表，臨時表在group by中的作用主要是“去重”。所以，實際上有另外一種方式，不使用臨時表，直接利用sort_buffer排序(sort_buffer不夠時，進行文件排序，具體而言是每一個有序數(shù)組作為一個單獨文件，然后進行外排歸并)，然后再掃描得到聚合后的結(jié)果集。
3).SQL_BIG_RESULT
同時我們語句中用到了“SQL_BIG_RESULT”這個hint，正是因為這個hint導致了我們沒有使用臨時表，先說說SQL_BIG_RESULT和SQL_SMALL_RESULT的含義。
SQL_SMALL_RESULT：顯示指定用內(nèi)存表(memory引擎)
SQL_BIG_RESULT：顯示指定用磁盤臨時表(myisam引擎或innodb引擎)
兩者區(qū)別在于，使用磁盤臨時表可以借助主鍵做去重排序，適合大數(shù)據(jù)量；使用內(nèi)存表寫入更快，然后在內(nèi)存中排序，適合小數(shù)據(jù)量。下面是從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優(yōu)化器根據(jù)hint知道需要使用磁盤臨時表，而最終直接選擇了數(shù)組存儲+文件排序這種更輕量的方式。
如何避免使用臨時表
通常的SQL優(yōu)化方式是讓group by 的列建立索引，那么執(zhí)行g(shù)roup by時，直接按索引掃描該列，并統(tǒng)計即可，也就不需要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.
這個參數(shù)主要針對用戶創(chuàng)建的MEMORY表，限制內(nèi)存表最大空間大小，注意不是記錄數(shù)目，與單條記錄的長度有關(guān)。如果超出閥值，則報錯。ERROR 1114 (HY000): The table ‘xxx’ is full
tmp_table_size
The maximum size of internal in-memory temporary tables.
對于用戶手工創(chuàng)建的內(nèi)存表，只有參數(shù)max_heap_table_size起作用；對于內(nèi)部產(chǎn)生的內(nèi)存表，則參數(shù)max_heap_table_size和tmp_table_size同時起作用。對于內(nèi)部產(chǎn)生的內(nèi)存表(比如union，group by等產(chǎn)生的臨時表)，先是采用內(nèi)存表(memory表)，然后超過設(shè)置的閥值(max_heap_table_size,tmp_table_size)就會轉(zhuǎn)為磁盤表，使用innodb引擎或者myisam引擎，通過參數(shù)internal_tmp_disk_storage_engine指定。
tmpdir
如果內(nèi)存臨時表超出了限制，MySQL就會自動地把它轉(zhuǎn)化為基于磁盤的MyISAM表，存儲在指定的tmpdir目錄下
2.狀態(tài)監(jiān)控
Created_tmp_tables，內(nèi)部臨時表數(shù)目
Created_tmp_disk_tables，磁盤臨時表數(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é)
本文詳細介紹了MySQL中臨時表的核心特征，按需創(chuàng)建并且自動銷毀，對于純內(nèi)存的數(shù)據(jù)特別適合，但為了避免內(nèi)存不可控，實際上不僅僅有內(nèi)存臨時表，還有磁盤臨時表。臨時表和內(nèi)存表本沒有直接關(guān)聯(lián)，因為臨時表既可以是memory引擎，又可以innodb引擎將兩者聯(lián)系到了一起，實際上不同類別的臨時表也是用到了不同引擎的優(yōu)勢。臨時表使用的典型場景是union和group by。為了消除臨時表，我們需要對group by列添加索引，或者對于大結(jié)果集，使用SQL_BIG_RESULT等。最后本文介紹了臨時表相關(guān)的參數(shù)和狀態(tài)變量，以及information_schema中的臨時表信息。
推薦教程：《MySQL教程》
