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

作者：操盛春 2022-04-18 08:33:07

SQL 語句執行過程中 MySQL 自行創建的是內部臨時表，explain 輸出結果的 Extra 列出現了 Using temporary 就說明 SQL 語句執行時使用了內部臨時表。

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 建立的索引。

（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 字段建立唯一索引，索引名是。

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

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

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

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

執行流程示意圖

（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 字段建立唯一索引，索引名是。

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

保證每個分組中 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 字段值到臨時表做準備。

執行流程示意圖

（3） hash 字段

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

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

存儲引擎限制

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

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

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

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

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

計算 group by、distinct 每一個字段的哈希值

所有字段哈希值再經過計算得到的結果，作為字段值。

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

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

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

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

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

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

如果所有字段值都相等，才能說明 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 索引字段數量、單個字段長度、索引記錄長度超過了限制，就不建立唯一索引了，會在臨時表中增加一個名為的字段，并在該字段上建立非唯一索引。

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

本文轉載自微信公眾號「一樹一溪」，可以通過以下二維碼關注。轉載本文請聯系一樹一溪公眾號。

責任編輯：姜華來源：一樹一溪

MySQL SQL 語句