MySQL 臨時表分為兩種：外部臨時表、內部臨時表。用戶通過 CREATE TEMPORARY TABLE 創(chuàng)建的是外部臨時表。SQL 語句執(zhí)行過程中 MySQL 自行創(chuàng)建的是內部臨時表，explain 輸出結果的 Extra 列出現了 Using temporary 就說明 SQL 語句執(zhí)行時使用了內部臨時表。

為了描述方便，本文后續(xù)內容中臨時表和內部臨時表意思一樣，都表示 SQL 語句執(zhí)行過程中 MySQL 自行創(chuàng)建的臨時表。

1. 準備工作

本文使用了 2 個示例表：t_recbuf、t_internal_tmp_table，2 個表的結構完全一樣，以下列出 t_recbuf 的表結構：

CREATE TABLE `t_recbuf` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `i1` int(10) unsigned DEFAULT '0',
  `str1` varchar(32) DEFAULT '',
  `str2` varchar(255) DEFAULT '',
  `c1` char(11) DEFAULT '',
  `e1` enum('北京','上海','廣州','深圳','天津','杭州','成都','重慶','蘇州','南京','洽爾濱','沈陽','長春','廈門','福州','南昌','泉州','德清','長沙','武漢') DEFAULT '北京',
  `s1` set('吃','喝','玩','樂','衣','食','住','行','前后','左右','上下','里外','遠近','長短','黑白','水星','金星','地球','火星','木星','土星','天王星','海王星','冥王星') DEFAULT '',
  `bit1` bit(8) DEFAULT b'0',
  `bit2` bit(17) DEFAULT b'0',
  `blob1` blob,
  `d1` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8;

2. 哪些場景會用到臨時表？

MySQL 使用臨時表的場景很多，下面列舉出部分場景：

• order by 和group by 字段不一樣。
• join 語句中，order by 或group by 字段不屬于執(zhí)行計劃中第一個表。
• 包含 distinct 關鍵字的聚合函數，例如：count(distinct i1)、sum(distinct i1) 等。
• 使用 union 或 union distinct 關鍵字的 SQL 語句。
• 派生表（explain 輸出結果的 select_type 列的值為 DERIVED）。
• 子查詢半連接物化（把子查詢結果存到臨時表，然后和主查詢進行 join 連接）。
• 子查詢物化（除半連接物化之外的場景，如不相關子查詢，半連接重復值消除等）。
• insert ... select 語句的源表和目標表是同一個表，例如：insert into t_recbuf(i1, str1) select i1, str1 from t_recbuf）。

以上羅列的場景以官方文檔為基礎，做了些改動。

大家不用糾結于是不是記住了上面這些場景，確定 SQL 語句是否使用了臨時表，查看執(zhí)行計劃是最方便快捷的方法，只要 explain 輸出結果的 Extra 列出現了 Using temporary 那就是用了臨時表。

3. 臨時表用哪種存儲引擎？

MySQL 臨時表可以選擇 3 種存儲引擎：MEMORY、MyISAM、InnoDB。MEMORY 是內存引擎，數據和索引都存放在內存中；MyISAM、InnoDB 是磁盤存儲引擎，數據和索引都存放在磁盤中。

SQL 執(zhí)行過程中，如果需要使用臨時表，MySQL 默認使用 MEMORY 存儲引擎。

有 2 種情況會影響 MySQL 的默認行為，以下 2 種情況滿足其中任何一種，臨時表就會使用 MyISAM 或 InnoDB 存儲引擎。

• 情況 1，寫入臨時表的字段中包含大對象（BLOB）字段。

關于哪些類型的字段屬于大對象，可以看看這篇文章：MySQL 大對象(BLOB)和字符串的分身術。

• 情況 2，系統變量 big_tables 的值為 ON，表示如果要使用臨時表，就一定要用 MyISAM 或 InnoDB 存儲引擎。

不過，在 big_tables = ON 的前提下，如果我們能夠非常確定某條 SQL 語句寫入臨時表的數據會很小，MEMORY 存儲引擎完全夠用，可以對單條 SQL 進行特殊處理。

在 SQL 語句中加入 SQL_SMALL_RESULT 提示，告訴 MySQL：我只需要 MEMORY 存儲引擎。SQL_SMALL_RESULT 是這樣用的：

select SQL_SMALL_RESULT * from t_recbuf

前面已經介紹完了 MySQL 怎么選擇內存、磁盤存儲引擎，如果 MySQL 決定了要使用磁盤存儲引擎，用 MyISAM 還是 InnoDB ？

這個選擇很簡單，系統變量 internal_tmp_disk_storage_engine 值為 MyISAM 就選擇 MyISAM 存儲引擎，值為 InnoDB 就使用 InnoDB 存儲引擎。

internal_tmp_disk_storage_engine 的值只能從 MyISAM、InnoDB 中二選一，默認為 InnoDB。

選擇存儲引擎

4. 內存臨時表變磁盤臨時表

MEMORY 存儲引擎表的記錄為固定長度，不支持大對象（BLOB）字段。

變長類型字段（VARCHAR、VARBINARY）也會按照定義時的最大長度存儲，實際上相當于 CHAR、BINARY 字段。

內存臨時表已插入記錄占用的空間，加上即將要插入的記錄占用的空間，如果超過閾值，臨時表的存儲引擎會由內存存儲引擎變?yōu)榇疟P存儲引擎。

占用內存空間超過閾值

臨時表占用內存空間的閾值，由系統變量 tmp_table_size 和 max_heap_table_size 中較小的那個決定。

tmp_table_size 默認大小為 16M，最小可設置為 1K，最大值是個超級巨大的值。

max_heap_table_size 默認為大小為 16M，最小可設置為 16K，最大值也是超級巨大的值。

得益于 MEMORY 引擎的記錄長度固定，判斷內存臨時表占用的空間是否超過閾值就很簡單了。

臨時表存儲引擎變?yōu)榇疟P存儲引擎的過程如下：

• 創(chuàng)建一個 MyISAM 或 InnoDB 臨時表，選擇哪個存儲引擎由 internal_tmp_disk_storage_engine控制。
• 把內存臨時表中的所有記錄逐條拷貝到磁盤臨時表。
• 把原計劃要插入內存臨時表但還沒插入的那條記錄插入磁盤臨時表。
• 刪除內存臨時表。

創(chuàng)建磁盤臨時表

等內存臨時表寫滿，才知道需要創(chuàng)建磁盤臨時表，這樣成本太高了。如果一開始就知道 SQL 語句執(zhí)行時需要使用臨時表，并且內存臨時表肯定存不下那么多記錄，我們直接告訴 MySQL 使用磁盤臨時表豈不是能節(jié)省很多開銷？

是的，如果我們一開就知道 SQL 語句數據量大會導致使用磁盤臨時表，在 SQL 語句中加上 SQL_BIG_RESULT （MySQL 里把這個叫做 hint），MySQL 為臨時表選擇存儲引擎時，就會直接選擇磁盤存儲引擎。

SQL_BIG_RESULT 是這樣用的：

select
  SQL_BIG_RESULT e1, min(i1)
from t_internal_tmp_table
group by e1

如果我們在 SQL 語句中加入了 SQL_BIG_RESULT 提示，查詢優(yōu)化器按使用磁盤臨時表評估執(zhí)行成本，也有可能會得出使用磁盤臨時表的成本比對 t_internal_tmp_table 表中的記錄排序之后再進行 group by 的成本更高的結論，就會選擇先對 t_internal_tmp_table 表中的記錄進行排序，然后再對已經排好序的記錄進行 group by 操作，這樣一來內存臨時表和磁盤臨時表都不需要了。

5. 寫入哪些字段到臨時表？

從寫入哪些字段到臨時表這個角度看，臨時表可以分為兩類：

• 為整條 SQL 語句服務的臨時表。
• 為單個聚合函數服務的臨時表。

對于為整條 SQL 語句服務的臨時表，SQL 語句執(zhí)行過程中，存儲引擎返回給 server 層的字段都需要寫入到臨時表中。寫入到臨時表中的字段內容，可能是字段值，也可能是函數基于字段值計算的結果，以兩個 SQL 為例來說明。

select
  e1, count(i1)
from t_internal_tmp_table
group by e1

示例 SQL 1，SQL 執(zhí)行過程中，MySQL 會把 t_internal_tmp_table 表的 e1 字段值、count(i1) 的計算結果寫入到臨時表。

select 
  a.e1, b.c1, count(a.i1) as t 
from t_internal_tmp_table as a
inner join t_recbuf as b on a.id = b.id 
group by a.e1, b.c1
with rollup

示例 SQL 2，由于 rollup 的存在，不能把聚合函數的計算結果寫入到臨時表，而是要把聚合函數參數中的字段值寫入到臨時表。

SQL 執(zhí)行過程中，MySQL 會把 t_internal_tmp_table 表的 e1、i1 字段值，t_recbuf 表中的 c1 字段值寫入臨時表。

把 t_internal_tmp_table 和 t_recbuf 兩個表連接查詢得到的記錄全部寫入臨時表之后，再對臨時表中的記錄進行分組（group by）、聚合（count）操作。

對于為單個聚合函數服務的臨時表，SQL 語句執(zhí)行過程中，只會把聚合函數中的字段寫入到臨時表，以一個 SQL 為例說明。

select
  e1, count(distinct i1) as t
from t_internal_tmp_table
group by e1

示例 SQL 3，臨時表只用于為 count(distinct i1) 中的 i1 字段去重，所以臨時表中只會寫入 t_internal_tmp_table 表的 i1 字段值，并且會為臨時表中的 i1 字段建立唯一索引，實現對 i1 字段的去重。

6. 為哪些字段建立索引？

MySQL 使用臨時表，可能是為了 group by 分組、聚合，也可能是為了對記錄去重（distinct），還有可能只是為了避免重復執(zhí)行子查詢而存放子查詢的執(zhí)行結果。

對于 group by 和 distinct，為了保證臨時表中 group by 的一個分組只有一條記錄，distinct 字段內容相同的記錄只保留一條，臨時表中會為相應的字段創(chuàng)建唯一索引。

非常重要的說明：臨時表中最多只會有一個索引，要么是為 group by 建立的索引，要么是為 distinct 建立的索引。

6.1 group by

select
  e1, count(i1)
from t_internal_tmp_table
group by e1

這是上一小節(jié)（5. 寫入哪些字段到臨時表？）的示例 SQL 1，臨時表中寫入 e1 字段值、count(i1) 的計算結果（每個分組中 i1 字段值不為 NULL 的記錄數量）。

MySQL 為了保證 e1 字段的每個值在臨時表中只有一條記錄，會為 e1 字段建立唯一索引，索引名是 <group_key>。

臨時表 e1 字段上唯一索引的存在，就是為了保證每個分組中記錄的唯一性，保證唯一性的流程是這樣的：

第 1 步，從 t_internal_tmp_table 表中讀取一條記錄之后，用該記錄的 e1 字段值作為查詢條件，去臨時表中查詢是否有對應的記錄。

第 2 步，如果 e1 字段值對應的記錄在臨時表中已經存在，執(zhí)行 count(i1) 函數得到當前分組新計數，然后把分組新計數更新到臨時表。

第 3 步，如果 e1 字段值對應的記錄在臨時表中還不存在，執(zhí)行 count(i1) 函數初始化分組計數，然后把 e1 字段值和分組計數插入到臨時表中。

執(zhí)行流程示意圖

6.2 distinct

select
  e1, count(distinct i1) as t
from t_internal_tmp_table
group by e1

這是上一小節(jié)（5. 寫入哪些字段到臨時表？）的示例 SQL 3，和示例 SQL 1 不一樣的地方是 count() 函數多了個 distinct，表示統計每個分組中，不同的 i1 字段值的數量（不包含 NULL）。

臨時表中寫入的字段只有 i1，為了保證臨時表的每個分組中 i1 字段值是唯一的，MySQL 會為 i1 字段建立唯一索引，索引名是 <auto_key>。

distinct 唯一索引的名字看起來有點詞不達意，源碼中說以后會改成 <distinct_key>。

保證每個分組中 i1 字段的唯一性，執(zhí)行流程是這樣的：

前奏，寫入數據到臨時表之前，MySQL 就已經讀取了 t_internal_tmp_table 表中的所記錄，并且已經按照 e1 字段排好了序。

第 1 步，讀取已經排好序的一條記錄，把 i1 字段值寫入到臨時表中（i1 字段值為 NULL 則不寫入）。

如果寫入成功，說明臨時表中還沒有該 i1 字段值對應的記錄。

如果寫入失敗，說明臨時表中已經有該 i1 字段值對應的記錄了，此時，寫入失敗的錯誤會被忽略，因為這正是我們想要的結果：對 i1 字段值去重。

插入操作直接利用了唯一索引中記錄不能重復的特性，雖然有點簡單粗暴，但也方便快捷。

第 2 步，判斷第 1 步讀取到的記錄的 e1 字段值和上一條記錄的 e1 字段值是否一樣。

如果一樣，說明是同一個分組，回到第 1 步繼續(xù)執(zhí)行，寫入當前分組中下一條記錄的 i1 字段值到臨時表。

如果不一樣，說明當前分組結束，進入第 3 步處理分組結束邏輯。

第 3 步，獲取臨時表中的記錄數量，也就是分組中 i1 字段值不為 NULL 并且已經去重的數量，發(fā)送給客戶端。

這里獲取臨時表中的記錄數量很方便，不需要掃描臨時表中所有記錄進行計數，而是直接讀取臨時表的統計信息（stats.records）。

第 4 步，分組數據發(fā)送給客戶端之后，清空臨時表中的所有記錄，為下一個分組寫入 i1 字段值到臨時表做準備。

執(zhí)行流程示意圖

6.3 hash 字段

為 group by、distinct 字段建立唯一索引，能夠保證臨時表中記錄的唯一性，看起來已經很完美了。

不過，世間事總有例外，存儲引擎對于索引中的字段數量、單個字段長度、索引記錄長度都是有限制的，一旦超過限制創(chuàng)建索引就會失敗，也就不能為 group by、distinct 字段建立唯一索引了。

存儲引擎限制

不能為 group by、distinct 字段建立唯一索引，那怎么保證這兩種情況下記錄的唯一性？

別急，你永遠可以相信 MySQL 有大招。

如果因為超限問題，不能為 group by、distinct 字段建立唯一索引，MySQL 會在臨時表中增加一個哈希字段（字段名 <hash_field>），并為這個字段建立非唯一索引（因為不同內容計算得到的哈希值有可能重復）。

<hash_field> 字段值可能存在重復，那怎么保證臨時表中記錄的唯一性？流程是這樣的：

第 1 步，插入記錄到臨時表之前，計算 <hash_field> 字段值，計算過程是這樣的：

• 計算 group by、distinct 每一個字段的哈希值
• 所有字段哈希值再經過計算得到的結果，作為 <hash_field> 字段值。

第 2 步，用第 1 步中計算出來的 <hash_field> 字段值作為查詢條件，到臨時表中查找記錄。

第 3 步，如果在臨時表中沒有找到記錄，說明記錄不存在，執(zhí)行插入操作。

第 4 步，如果在臨時表中找到了記錄，把記錄讀取出來（存到 table->record[1] 中）。

這時候還不能說明 group by、distinct 字段對應的記錄在表中就是存在的，因為哈希值有可能重復。

第 5 步，把 group by 或 distinct 中的字段逐個和第 4 步讀出來的記錄中對應的字段進行比較。

如果有任何一個字段值不相等，說明 group by、distinct 字段對應的記錄在臨時表中不存在，執(zhí)行插入操作。

如果所有字段值都相等，才能說明 group by、distinct 字段對應的記錄在臨時表中已經存在。

對于 group by，更新臨時表中對應的記錄；對于 distinct，準備要插入的記錄就可以忽略了，不需要進行插入操作。

group by 執(zhí)行流程示意圖

distinct 執(zhí)行流程示意圖

7. 內部臨時表使用情況統計

MySQL 每創(chuàng)建一個臨時表，狀態(tài)變量 created_tmp_tables 的值就加 1。

臨時表的存儲引擎由 MEMORY 替換為 MyISAM 或 InnoDB，狀態(tài)變量 created_tmp_disk_tables 的值就加 1。

created_tmp_disk_tables 除以 created_tmp_tables 得到的結果越大，說明創(chuàng)建的臨時表中，磁盤臨時表的比例越高。

減少內存臨時表轉換為磁盤臨時表，有兩種可能的優(yōu)化方案：

• 降低內存臨時表轉換為磁盤臨時表的比例：修改系統變量 tmp_table_size 和 max_heap_table_size 的值，讓臨時表可以使用更多的內存，減少這種轉換。
• 強制臨時表使用磁盤存儲引擎：如果業(yè)務類型比較特殊，臨時表的數據不可避免的會很大，加大臨時表占用內存的閾值效果不明顯的情況下，把系統變量big_tables的值設置為ON，強制內部臨時表使用磁盤存儲引擎，可以避免不必要的內存臨時表轉換為磁盤臨時表。

8. 總結

第 2 小節(jié)，列出了 MySQL 使用臨時表的部分場景，這些場景反正也記不住，就不用記了，了解下就好。理解了臨時表的用途和 SQL 語句的執(zhí)行過程，大體上也能推斷出來是否會用到臨時表，再結合 explain 查看執(zhí)行計劃就能知道結果了。

第 3 小節(jié)，介紹了臨時表的默認存儲引擎為 MEMORY，如果寫入臨時表的字段包含大對象（BLOB）字段，或者系統變量 big_tables 的值為 ON，會根據系統變量 internal_tmp_disk_storage_engine 的值選擇使用 MyISAM 或 InnoDB 作為臨時表的存儲引擎。

第 4 小節(jié)，介紹了內存臨時表占用空間超過 tmp_table_size 和 max_heap_table_size 中較小的那個值時，會把內存臨時表替換為磁盤臨時表。如果想要指定單條 SQL 語句直接使用磁盤臨時表，可以在 SQL 語句中加入 SQL_BIG_RESULT 提示。

第 5 小節(jié)，介紹了臨時表中會寫入哪些字段。對于 group by，臨時表中會寫入存儲引擎返回給 server 層的所有字段，寫入臨時表的字段內容，可能是字段值，也可能是聚合函數基于字段值計算的結果；對于 distinct，臨時表中會寫入聚合函數中的字段。

第 6 小節(jié)，介紹了臨時表中會為 group by、distinct 字段建立唯一索引，如果 group by 或 distinct 索引字段數量、單個字段長度、索引記錄長度超過了限制，就不建立唯一索引了，會在臨時表中增加一個名為 <hash_field> 的字段，并在該字段上建立非唯一索引。

第 7 小節(jié)，介紹了 2 個系統變量 created_tmp_tables、created_tmp_disk_tables 可以用于查看 MySQL 臨時表的使用情況，以及可以通過調整 tmp_table_size、max_heap_table_size、big_tables 這 3 個系統變量，減少或避免內存臨時表轉換為磁盤臨時表。