1 臨時表

sort buffer、內(nèi)存臨時表和join buffer，這三個數(shù)據(jù)結(jié)構(gòu)都是用來存放語句執(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ù)比較，如果重復(fù)則不會插入，這樣就實現(xiàn)了去重的功能
• 從臨時表中按行取出數(shù)據(jù)，返回結(jié)果，并刪除臨時表。在最后這一步還可以對臨時表進行其他操作，比如limit、ORDER BY。

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

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

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

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

如下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的結(jié)果，記為x；
  • 如果臨時表中沒有主鍵為x的行，就插入一個記錄(x,1);
  • 如果表中有主鍵為x的行，就將x這一行的c值加1；
  • 遍歷完成后，再根據(jù)字段m做排序，得到結(jié)果集返回給客戶端。

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

如果并不需要對結(jié)果進行排序，那可以在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)存臨時表轉(zhuǎn)成磁盤臨時表，而磁盤臨時表默認使用的引擎是InnoDB，因此會按主鍵順序存儲數(shù)據(jù)，所以最終取出的結(jié)果還是默認有序的。

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

總結(jié)：

• 如果語句執(zhí)行過程可以一邊讀數(shù)據(jù)，一邊直接得到結(jié)果，是不需要額外內(nèi)存的，否則就需要額外的內(nèi)存，來保存中間結(jié)果；
• join_buffer是無序數(shù)組，sort_buffer是有序數(shù)組，臨時表是二維表結(jié)構(gòu)；

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

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

到此這篇關(guān)于MySQL 臨時表的原理以及優(yōu)化方法的文章就介紹到這了,更多相關(guān)MySQL 臨時表 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論