1 臨時(shí)表

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

而使用臨時(shí)表的時(shí)候，Explain的Extra字段中具有Using temporary標(biāo)記。union、group by、distinct等等查詢(xún)都有可能使用到臨時(shí)表。

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

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

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

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

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

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

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

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

如下sql：

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

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

• 創(chuàng)建內(nèi)存臨時(shí)表，表里有兩個(gè)字段m和c，主鍵是m；
• 掃描表t1的索引a，依次取出葉子節(jié)點(diǎn)上的id值，計(jì)算id%10的結(jié)果，記為x；
  • 如果臨時(shí)表中沒(méi)有主鍵為x的行，就插入一個(gè)記錄(x,1);
  • 如果表中有主鍵為x的行，就將x這一行的c值加1；
  • 遍歷完成后，再根據(jù)字段m做排序，得到結(jié)果集返回給客戶(hù)端。

此時(shí)，Explain的Extra字段中具有Using temporary; Using filesort標(biāo)記。

如果并不需要對(duì)結(jié)果進(jìn)行排序，那可以在SQL語(yǔ)句末尾增加order by null，即：

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

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

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

對(duì)于Group By的臨時(shí)表的優(yōu)化，同樣是使用索引：因?yàn)槿绻M(jìn)行Group By字段是有序的，那么在處理時(shí)（比如計(jì)算每組數(shù)量、個(gè)數(shù)等等），因?yàn)楦淖侄斡兴饕敲聪嗤闹悼隙ㄊ窃谝黄鸬?、連續(xù)的，所以直接順序掃描輸入的數(shù)據(jù)即可，不需要臨時(shí)表，也不需要再額外排序。

總結(jié)：

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

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

另外，對(duì)于distinct查詢(xún)來(lái)說(shuō)，如果無(wú)法使用索引，則也會(huì)使用到臨時(shí)表，也會(huì)進(jìn)行分組，它和group by的區(qū)別是不需要排序。

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

最新評(píng)論