你好奇過 MySQL 內部臨時表存了什麼嗎？

MySQL 臨時表分為兩種：外部臨時表、內部臨時表。使用者透過 CREATE TEMPORARY TABLE 建立的是外部臨時表。SQL 語句執行過程中 MySQL 自行建立的是內部臨時表，explain 輸出結果的 Extra 列出現了 Using temporary 就說明 SQL 語句執行時使用了內部臨時表。

為了描述方便，本文後續內容中臨時表和內部臨時表意思一樣，都表示 SQL 語句執行過程中 MySQL 自行建立的臨時表。

本文內容基於 MySQL 5.7.35 原始碼。

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 欄位不屬於執行計劃中第一個表。
• 包含 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 語句是否使用了臨時表，檢視執行計劃是最方便快捷的方法，只要 explain 輸出結果的 Extra 列出現了 Using temporary 那就是用了臨時表。

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

MySQL 臨時表可以選擇 3 種儲存引擎：MEMORY、MyISAM、InnoDB。MEMORY 是記憶體引擎，資料和索引都存放在記憶體中；MyISAM、InnoDB 是磁碟儲存引擎，資料和索引都存放在磁碟中。

SQL 執行過程中，如果需要使用臨時表，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 欄位。

記憶體臨時表已插入記錄佔用的空間，加上即將要插入的記錄佔用的空間，如果超過閾值，臨時表的儲存引擎會由記憶體儲存引擎變為磁碟儲存引擎。

臨時表佔用記憶體空間的閾值，由系統變數 tmp_table_size 和 max_heap_table_size 中較小的那個決定。

tmp_table_size 預設大小為 16M，最小可設定為 1K，最大值是個超級巨大的值。
max_heap_table_size 預設為大小為 16M，最小可設定為 16K，最大值也是超級巨大的值。

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

臨時表儲存引擎變為磁碟儲存引擎的過程如下：

• 建立一個 MyISAM 或 InnoDB 臨時表，選擇哪個儲存引擎由 internal_tmp_disk_storage_engine 控制。
• 把記憶體臨時表中的所有記錄逐條複製到磁碟臨時表。
• 把原計劃要插入記憶體臨時表但還沒插入的那條記錄插入磁碟臨時表。
• 刪除記憶體臨時表。

等記憶體臨時表寫滿，才知道需要建立磁碟臨時表，這樣成本太高了。如果一開始就知道 SQL 語句執行時需要使用臨時表，並且記憶體臨時表肯定存不下那麼多記錄，我們直接告訴 MySQL 使用磁碟臨時表豈不是能節省很多開銷？

是的，如果我們一開就知道 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 提示，查詢最佳化器按使用磁碟臨時表評估執行成本，也有可能會得出使用磁碟臨時表的成本比對 t_internal_tmp_table 表中的記錄排序之後再進行 group by 的成本更高的結論，就會選擇先對 t_internal_tmp_table 表中的記錄進行排序，然後再對已經排好序的記錄進行 group by 操作，這樣一來記憶體臨時表和磁碟臨時表都不需要了。

5. 寫入哪些欄位到臨時表？

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

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

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

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

示例 SQL 1，SQL 執行過程中，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 執行過程中，MySQL 會把 t_internal_tmp_table 表的 e1、i1 欄位值，t_recbuf 表中的 c1 欄位值寫入臨時表。

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

對於為單個聚合函式服務的臨時表，SQL 語句執行過程中，只會把聚合函式中的欄位寫入到臨時表，以一個 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），還有可能只是為了避免重複執行子查詢而存放子查詢的執行結果。

對於 group by 和 distinct，為了保證臨時表中 group by 的一個分組只有一條記錄，distinct 欄位內容相同的記錄只保留一條，臨時表中會為相應的欄位建立唯一索引。

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

6.1 group by

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

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

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

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

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

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

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

6.2 distinct

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

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

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

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

保證每個分組中 i1 欄位的唯一性，執行流程是這樣的：

前奏，寫入資料到臨時表之前，MySQL 就已經讀取了 t_internal_tmp_table 表中的所記錄，並且已經按照 e1 欄位排好了序。

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

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

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

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

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

如果一樣，說明是同一個分組，回到第 1 步繼續執行，寫入當前分組中下一條記錄的 i1 欄位值到臨時表。

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

第 3 步，獲取臨時表中的記錄數量，也就是分組中 i1 欄位值不為 NULL 並且已經去重的數量，傳送給客戶端。

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

第 4 步，分組資料傳送給客戶端之後，清空臨時表中的所有記錄，為下一個分組寫入 i1 欄位值到臨時表做準備。

6.3 hash 欄位

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

不過，世間事總有例外，儲存引擎對於索引中的欄位數量、單個欄位長度、索引記錄長度都是有限制的，一旦超過限制建立索引就會失敗，也就不能為 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 步，如果在臨時表中沒有找到記錄，說明記錄不存在，執行插入操作。

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

這時候還不能說明 group by、distinct 欄位對應的記錄在表中就是存在的，因為雜湊值有可能重複。

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

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

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

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

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

MySQL 每建立一個臨時表，狀態變數 created_tmp_tables 的值就加 1。

臨時表的儲存引擎由 MEMORY 替換為 MyISAM 或 InnoDB，狀態變數 created_tmp_disk_tables 的值就加 1。

created_tmp_disk_tables 除以 created_tmp_tables 得到的結果越大，說明建立的臨時表中，磁碟臨時表的比例越高。

減少記憶體臨時錶轉換為磁碟臨時表，有兩種可能的最佳化方案：

• 降低記憶體臨時錶轉換為磁碟臨時表的比例：修改系統變數 tmp_table_size 和 max_heap_table_size 的值，讓臨時表可以使用更多的記憶體，減少這種轉換。
• 強制臨時表使用磁碟儲存引擎：如果業務型別比較特殊，臨時表的資料不可避免的會很大，加大臨時表佔用記憶體的閾值效果不明顯的情況下，把系統變數 big_tables 的值設定為 ON，強制內部臨時表使用磁碟儲存引擎，可以避免不必要的記憶體臨時錶轉換為磁碟臨時表。

8. 總結

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

第 3 小節，介紹了臨時表的預設儲存引擎為 MEMORY，如果寫入臨時表的欄位包含大物件（BLOB）欄位，或者系統變數 big_tables 的值為 ON，會根據系統變數 internal_tmp_disk_storage_engine 的值選擇使用 MyISAM 或 InnoDB 作為臨時表的儲存引擎。

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

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

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

第 7 小節，介紹了 2 個系統變數 created_tmp_tables、created_tmp_disk_tables 可以用於檢視 MySQL 臨時表的使用情況，以及可以透過調整 tmp_table_size、max_heap_table_size、big_tables 這 3 個系統變數，減少或避免記憶體臨時錶轉換為磁碟臨時表。