在使用 explain 解析一個 sql 時，有時我們會發現在 extra 列上顯示 using temporary ，這表示這條語句用到了臨時表，那麼臨時表究竟是什麼？它又會對 sql 的效能產生什麼影響？又會在哪些場景中出現？本文根據 <<MySQL 實戰 45 講>> 學習整理。
出現場景
其實臨時表在之前的部落格就已經出現過了，在 MySQL 中的排序 一文中就說到如果 order by 的列上沒有索引，或者說沒有用到索引，那麼就需要進行額外排序（using filesort），而額外排序優先在一塊 sort_buffer 空間中進行，如果這塊空間大小小於要載入的欄位總長度，那麼就會用到臨時檔案輔助排序，這個臨時檔案就是臨時表。臨時表的作用就是作為中間表最佳化操作，比如 group by 作為分組的中間表， order by rand() （MySQL 中的排序 中的例子）作為中間表幫助運算等。
特點
1、建表語法是 create temporary table …。
3、臨時表可以與普通表同名。
4、session A 內有同名的臨時表和普通表的時候，show create 語句，以及增刪改查語句訪問的是臨時表。
5、show tables 命令不顯示臨時表。
種類
臨時表分為磁碟臨時表和記憶體臨時表。磁碟臨時表指的是儲存在磁碟上的臨時表，因為在磁碟上，所以執行效率比較低，優點結構可以是有序的，實現可以是 InnoDB（預設），MyISAM 引擎；記憶體臨時表就是儲存在記憶體中，執行效率高，常用的實現引擎是 Memory。
磁碟臨時表和記憶體臨時表的區別
1、相比於 InnoDB 表，使用記憶體表不需要寫磁碟，往表 temp_t 的寫資料的速度更快；
2、索引 b 使用 hash 索引，查詢的速度比 B-Tree 索引快；
3、臨時表資料只有 2000 行，佔用的記憶體有限。
Memory 引擎
與 InnoDB 的區別
1、InnoDB 表的資料總是有序存放的，而記憶體表的資料就是按照寫入順序存放的；關於這點可以透過建立 b+ 索引來進行排序，最佳化查詢。alter table t1 add index a_btree_index using btree (id);
2、當資料檔案有空洞的時候，InnoDB 表在插入新資料的時候，為了保證資料有序性，只能在固定的位置寫入新值，而記憶體表找到空位就可以插入新值；
3、資料位置發生變化的時候，InnoDB 表只需要修改主鍵索引，而記憶體表需要修改所有索引；
4、InnoDB 表用主鍵索引查詢時需要走一次索引查詢，用普通索引查詢的時候，需要走兩次索引查詢。而記憶體表沒有這個區別，所有索引的“地位”都是相同的。
5、InnoDB 支援變長資料型別，不同記錄的長度可能不同；記憶體表不支援 Blob 和 Text 欄位，並且即使定義了 varchar(N)，實際也當作 char(N)，也就是固定長度字串來儲存，因此記憶體表的每行資料長度相同。
6、記憶體表支援 hash 索引，並且資料儲存在記憶體中，所以執行比資料儲存在磁碟上的 Innodb 快。
缺點
1、鎖粒度大，只支援表級鎖，併發度低。
2、資料永續性差。因為是記憶體結構，所以在重啟後資料會丟失 。由此會導致備庫在硬體升級後資料就會丟失，並且如果主從庫互為 "主備關係" ，備庫在關閉後還會將刪除資料記錄進 binlog，重啟後主機會執行備庫傳送過來的 binlog ，導致主庫資料也會丟失。
雖然 Memory 引擎看起來缺點很多，但是因為其儲存在記憶體中，並且關機後會自動清除資料，所以其是作為臨時表的一個絕佳選擇。
常見的應用場景
分庫分表查詢
將一個大表 ht，按照欄位 f，拆分成 1024 個分表，然後分佈到 32 個數據庫例項上（水平分表）。一般情況下，這種分庫分表系統都有一箇中間層 proxy。不過，也有一些方案會讓客戶端直接連線資料庫，也就是沒有 proxy 這一層。假設分割槽鍵是 列 f 。
1、如果只使用分割槽鍵作為查詢條件如 select v from ht where f=N，那麼直接透過分表規則找到 N 所在的表，然後去該表上查詢就可以了。
2、如果使用其他欄位作為條件且需要排序如 select v from ht where k >= M order by t_modified desc limit 100，那麼非但不能確定要查詢的記錄在哪張表上，而且因為預設使用的是分割槽鍵排序，所以得到的結果還是無序的，需要額外排序。
1）在 proxy 層完成排序。優勢是速度快，缺點是開發工作量比較大，如果涉及複雜的操作如 group by，甚至 join 這樣的操作，對中間層的開發能力要求比較高。並且還容易出現記憶體不夠、CPU 瓶頸的問題。
2）將各個分割槽的查詢結果（未排序）總結到一張臨時表上進行排序。
Ⅰ、在彙總庫上建立一個臨時表 temp_ht，表裡包含三個欄位 v、k、t_modified； Ⅱ、在各個分庫上執行 select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100; Ⅲ、把分庫執行的結果插入到 temp_ht 表中； Ⅳ、執行 select v from temp_ht order by t_modified desc limit 100;
union 作為中間表
有表t1：create table t1(id int primary key, a int, b int, index(a)); 有記錄(1,1,1) 到 (1000,1000,1000) 執行 (select 1000 as f) union (select id from t1 order by id desc limit 2);
解析這條 sql：
可以知道：
1、左邊語句沒有進行查表操作 2、右邊語句使用了 id 索引 3、聯合時使用了臨時表
具體過程：
1、建立一個記憶體臨時表，這個臨時表只有一個整型欄位 f，並且 f 是主鍵欄位。2、執行第一個子查詢，得到 1000 這個值，並存入臨時表中。3、執行第二個子查詢：1）拿到第一行 id=1000，試圖插入臨時表中。但由於 1000 這個值已經存在於臨時表了，違反了唯一性約束，所以插入失敗，然後繼續執行；2）取到第二行 id=999，插入臨時表成功。4、從臨時表中按行取出資料，返回結果，並刪除臨時表，結果中包含兩行資料分別是 1000 和 999。
排序返回的欄位過大
舉一個在 MySQL中的排序 中提到過的例子。
select word from words order by rand() limit 3; 表資料有10000行 SQL是從10000行記錄中隨機獲取3條記錄返回。
這個執行過程因為涉及到 rand() 且資料量比較大，所以單靠 sort_buffer 排序空間不夠，所以還用到臨時表。
過程：
1、從緩衝池依次讀取記錄，每次讀取後都呼叫 rand() 函式生成一個 0-1 的數存入記憶體臨時表，W 是 word 值，R 是 rand() 生成的隨機數。到這掃描了 10000 行。2、初始化 sort_buffer，從記憶體臨時表中將 rowid（這張表自動生成的） 以及 排序資料 R 存入 sort_buffer。到這因為要遍歷記憶體臨時表所以又掃描了 10000 行。3、在 sort_buffer 中根據 R 排好序，然後選擇前三個記錄的 rowid 逐條去記憶體臨時表中查到 word 值返回。到這因為取了三個資料去記憶體臨時表去查詢所以又掃描了 3 行。總共 20003 行。
group by 作為中間表
執行：select id%10 as m, count(*) as c from t1 group by m;
首先解析 SQL：
可以看到使用了臨時表和額外排序，接下來來解析
執行過程：
1、建立記憶體臨時表，表裡有兩個欄位 m 和 c，主鍵是 m；2、掃描表 t1 的索引 a，依次取出葉子節點上的 id 值，計算 id%10 的結果，記為 x； 1）如果臨時表中沒有主鍵為 x 的行，就插入一個記錄 (x,1); 2）如果表中有主鍵為 x 的行，就將 x 這一行的 c 值加 1；遍歷完成後，再根據欄位 m 做排序，得到結果集返回給客戶端。
排序的過程就按照排序規則進行，用到 sort_buffer ，可能用到臨時表。
最佳化 BNL 排序
表結構：
CREATE TABLE `t2` (`id` int(11) NOT NULL,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `a` (`a`)) ENGINE=InnoDB;
t1、t2 結構相等，t2 100萬條資料，t1 1000行資料，t1 的資料在 t2 上都有對應，相等。執行語句：select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;
分析：因為欄位b 沒有建立索引，所以排序是屬於 BNL 排序，再加上資料量比較大，所以在比較時掃描的總行數就等於 100萬*1000，也就是10億次。
具體過程：
1、把表 t1 的所有欄位取出來，存入 join_buffer 中。這個表只有 1000 行，join_buffer_size 預設值是 256k，可以完全存入。2、掃描表 t2，取出每一行資料跟 join_buffer 中的資料進行對比， 1）如果不滿足 t1.b=t2.b，則跳過； 2）如果滿足 t1.b=t2.b, 再判斷其他條件，也就是是否滿足 t2.b 處於[1,2000]的條件，如果是，就作為結果集的一部分返回，否則跳過。
最佳化：
如果篩選欄位用的比較多，那麼可以為其建立索引，使 BNL 最佳化成 NLJ，但是如果這個欄位使用的不多，那麼為其建立索引反倒會因為多了不必要的維護成本而降低總體的效能。所以。針對於使用率不高的 BNL 篩選欄位的最佳化，可以建立一個臨時表，讓這個臨時表作為一個索引表，來最佳化成 NLJ，同時因為臨時表在會話結束後會自動刪除，省去了維護成本。
create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;insert into temp_t select * from t2 where b>=1 and b<=2000;select * from t1 join temp_t on (t1.b=temp_t.b);
這樣執行過程就變成：
1、執行 insert 語句構造 temp_t 表並插入資料的過程中，對錶 t2 做了全表掃描，這裡掃描行數是 100 萬。2、之後的 join 語句，掃描表 t1，這裡的掃描行數是 1000；join 比較過程中，做了 1000 次帶索引的查詢（因為t1 1000行，作為驅動表，t2作為被驅動表）。相比於最佳化前的 join 語句需要做 10 億次條件判斷來說，這個最佳化效果還是很明顯的。
為什麼臨時表可以重名
可以看到在 sessionA 在已經建立了一個名為 t1 的臨時表，並且 sessionA 未結束前，sessionB 也建立了一個名為 t1 的臨時表，沒有發生異常。這是為什麼？
首先要知道在 MySQL 啟動後每張表都會載入到記憶體中，所以每張表都分為記憶體表和磁碟表。
1、對於磁碟表：
1）普通表的表結構和資料檔案都是儲存在庫名資料夾下的，檔名就是表名。
2）結構檔案儲存在臨時資料夾下，檔案的字尾是 frm，字首是 "#sql{程序 id}_{執行緒id}_序列號"；
資料檔案在 5.6 及之前是儲存在臨時資料夾下的，5.7 開始存放在專門存放臨時檔案資料的臨時表空間。
2、對於記憶體表：
1）普通表的命名是 "庫名 + 表名"。
2）臨時表的命名則在 " 庫名 + 表名 " 的基礎上，加入了 " server_id + thread_id "。比如：
session A 的臨時表 t1，在備庫的 table_def_key 就是：庫名 +t1+“M 的 serverid”+“session A 的 thread_id”; session B 的臨時表 t1，在備庫的 table_def_key 就是 ：庫名 +t1+“M 的 serverid”+“session B 的 thread_id”。
綜上所述，因為臨時表在磁碟和記憶體中表的命名都取自具體的程序id、執行緒id、所以可以實現不同的會話建立相同的表名。
如果 binlog 的格式是 row，那麼是不會記錄臨時表的各個操作的，因為臨時表就是作為中間表來輔助各種操作的，所以在 row 格式下直接記錄的是經過臨時表得出的具體要操作的資料。
總結
臨時表是一種非常方便的結構，因為其會隨著會話結束而自動刪除，所以在一些查詢效率較低但篩選欄位使用很少的場景，就可以透過建立臨時表，然後在臨時表上建立索引來提高查詢效率，同時也避免了索引的後續維護，而在其他複雜操作中，臨時表也可以充當中間表的作用。所以臨時表廣泛出現在查詢（多表聯查）、分組、排序（排序返回的欄位總長度過大）等場景中。在某些複雜操作我們可以使用臨時表去最佳化，但是使用臨時表還是會消耗一定的效能，所以如果 explain 一條語句出現 using temporary，首要是想辦法去最佳化使其沒有用到臨時表，如果沒有更好的方法最佳化才放棄。
總結：
1、如果語句執行過程可以一邊讀資料，一邊直接得到結果，是不需要額外記憶體的，否則就需要額外的記憶體，來儲存中間結果；2、join_buffer 是無序陣列，專門用於多表聯查未用到索引時輔助查詢的，如果不足以裝下驅動表就會分多次進行；sort_buffer 是有序陣列，在排序時優先使用的區域，如果要排序返回的總欄位長度超過設定的區域就會用到記憶體臨時表，如果再超過 tmp_table_size 設定的值就會轉成磁碟臨時表；記憶體臨時表是二維表結構，無序；磁碟臨時表預設是B+結構，可以是陣列，有序。3、如果執行邏輯需要用到二維表特性，就會優先考慮使用臨時表。比如我們的例子中，union 需要用到唯一索引約束， group by 還需要用到另外一個欄位來存累積計數。