MySQL?臨時表的原理以及優(yōu)化方法

更新時間：2022年08月22日 10:26:40 作者：劉Java

這篇文章主要介紹了MySQL?臨時表的原理以及優(yōu)化方法，文章圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下

1 臨時表

sort buffer、內(nèi)存臨時表和join buffer，這三個數(shù)據(jù)結構都是用來存放語句執(zhí)行過程中的中間數(shù)據(jù)，以輔助SQL語句的執(zhí)行的。其中，在排序的時候用到了sort buffer，在使用join語句的時候用到了join buffer。

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

2 union臨時表優(yōu)化

使用union的時候，就需要用到內(nèi)存臨時表來進行去重。

union語句的執(zhí)行流程為：

創(chuàng)建一個內(nèi)存臨時表。
執(zhí)行第一個子查詢，得到值，并存入臨時表中。
執(zhí)行第二個子查詢：依次拿每一行數(shù)據(jù)和臨時表中的每一行數(shù)據(jù)比較，如果重復則不會插入，這樣就實現(xiàn)了去重的功能
從臨時表中按行取出數(shù)據(jù)，返回結果，并刪除臨時表。在最后這一步還可以對臨時表進行其他操作，比如limit、ORDER BY。

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

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

3 group by臨時表優(yōu)化

另外一個使用臨時表的例子是group by，group by還具有隱藏的排序的語句，即在對某些字段進行分組之后，將數(shù)據(jù)再根據(jù)這些字段進行排序，最后返回排序后的結果。

如下sql：

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

這個語句的執(zhí)行流程是這樣的：

創(chuàng)建內(nèi)存臨時表，表里有兩個字段m和c，主鍵是m；
掃描表t1的索引a，依次取出葉子節(jié)點上的id值，計算id%10的結果，記為x；
- 如果臨時表中沒有主鍵為x的行，就插入一個記錄(x,1);
- 如果表中有主鍵為x的行，就將x這一行的c值加1；
- 遍歷完成后，再根據(jù)字段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;

這樣就跳過了最后排序的階段，直接從臨時表中取數(shù)據(jù)返回。

內(nèi)存臨時表的大小是有限制的，參數(shù)tmp_table_size就是控制這個內(nèi)存大小的，默認是16M。如果要處理的數(shù)據(jù)超過了最大大小，那么MySQL會把內(nèi)存臨時表轉成磁盤臨時表，而磁盤臨時表默認使用的引擎是InnoDB，因此會按主鍵順序存儲數(shù)據(jù)，所以最終取出的結果還是默認有序的。

對于Group By的臨時表的優(yōu)化，同樣是使用索引：因為如果進行Group By字段是有序的，那么在處理時（比如計算每組數(shù)量、個數(shù)等等），因為跟著的字段有索引，那么相同的值肯定是在一起的、連續(xù)的，所以直接順序掃描輸入的數(shù)據(jù)即可，不需要臨時表，也不需要再額外排序。

總結：