MySQL 臨時表的原理以及優化方法

1 臨時表

sort buffer、記憶體臨時表和join buffer，這三個資料結構都是用來存放語句執行過程中的中間資料，以輔助SQL語句的執行的。其中，在排序的時候用到了sort buffer，在使用join語句的時候用到了join buffer。

而使用臨時表的時候，Explain的Extra欄位中具有Using temporary標記。union、group by、distinct等等查詢都有可能使用到臨時表。

2 union臨時表優化

使用union的時候，就需要用到記憶體臨時表來進行去重。

union語句的執行流程為：

• 建立一個記憶體臨時表。
• 執行第一個子查詢，得到值，並存入臨時表中。
• 執行第二個子查詢：依次拿每一行資料和臨時表中的每一行資料比較，如果重複則不會插入，這樣就實現了去重的功能
• 從臨時表中按行取出資料，返回結果，並刪除臨時表。在最後這一步還可以對臨時表進行其他操作，比如limit、ORDER BY。

如果使用union all，則不需要去重，也就不需要臨時表了。在執行的時候，就依次執行子查詢，得到的結果直接作為結果集的一部分，發給使用者端。因此，除非確實需要伺服器消除重複的行，否則就一定要使用UNION ALL，這一點很重要。如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，這會導致對整個臨時我的資料做唯一性檢查。這樣做的代價非常高。如果不需要這些檢查，那麼甚至都不需要臨時表。

另外，避免對於union之後的結果集進行操作，也能避免臨時表的使用，通常需要手工地將MHERE、LIMIT、ORDER BY等子句“下推”到UNION的各個子查詢中，以便優化器可以充分利用這些條件進行優化，使得union的結果就是最終的結果（例如，直接將這些子句冗餘地寫一份到各個子查詢)。

3 group by臨時表優化

另外一個使用臨時表的例子是group by，group by還具有隱藏的排序的語句，即在對某些欄位進行分組之後，將資料再根據這些欄位進行排序，最後返回排序後的結果。

如下sql：

  select id%10 as m, count(*) as c from t1 group by m;  

這個語句的執行流程是這樣的：

• 建立記憶體臨時表，表裡有兩個欄位m和c，主鍵是m；
• 掃描表t1的索引a，依次取出葉子節點上的id值，計算id%10的結果，記為x；
  • 如果臨時表中沒有主鍵為x的行，就插入一個記錄(x,1);
  • 如果表中有主鍵為x的行，就將x這一行的c值加1；
  • 遍歷完成後，再根據欄位m做排序，得到結果集返回給使用者端。

此時，Explain的Extra欄位中具有Using temporary; Using filesort標記。

如果並不需要對結果進行排序，那可以在SQL語句末尾增加order by null，即：

  select id%10 as m, count(*) as c from t1 group by m order by null;  

這樣就跳過了最後排序的階段，直接從臨時表中取資料返回。

記憶體臨時表的大小是有限制的，引數tmp_table_size就是控制這個記憶體大小的，預設是16M。如果要處理的資料超過了最大大小，那麼MySQL會把記憶體臨時錶轉成磁碟臨時表，而磁碟臨時表預設使用的引擎是InnoDB，因此會按主鍵順序儲存資料，所以最終取出的結果還是預設有序的。

對於Group By的臨時表的優化，同樣是使用索引：因為如果進行Group By欄位是有序的，那麼在處理時（比如計算每組數量、個數等等），因為跟著的欄位有索引，那麼相同的值肯定是在一起的、連續的，所以直接順序掃描輸入的資料即可，不需要臨時表，也不需要再額外排序。

總結：

• 如果語句執行過程可以一邊讀資料，一邊直接得到結果，是不需要額外記憶體的，否則就需要額外的記憶體，來儲存中間結果；
• join_buffer是無序陣列，sort_buffer是有序陣列，臨時表是二維表結構；

如果執行邏輯需要用到二維表特性，就會優先考慮使用臨時表。比如我們的例子中，union需要用到唯一索引約束， group by還需要用到另外一個欄位來存累積計數。

另外，對於distinct查詢來說，如果無法使用索引，則也會使用到臨時表，也會進行分組，它和group by的區別是不需要排序。

到此這篇關於MySQL 臨時表的原理以及優化方法的文章就介紹到這了,更多相關MySQL 臨時表 內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！