【阿里MySQL面試題】內(nèi)部臨時表

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

union 執(zhí)行流程

示例表

創(chuàng)建數(shù)據(jù)

執(zhí)行如下SQL：

這條語句用到了union，它的語義是，取這兩個子查詢結(jié)果的并集。重復的行只保留一行。

key=PRIMARY，說明第二個子句用索引id。

Extra字段，表示在對子查詢的結(jié)果集做union時，使用了臨時表(Using temporary)

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

創(chuàng)建一個內(nèi)存臨時表，該臨時表只有一個整型字段f，并且f是主鍵字段

執(zhí)行第一個子查詢，得到1000這個值，并存入臨時表中

執(zhí)行第二個子查詢：

拿到第一行id=1000，試圖插入臨時表中。但由于1000這個值已經(jīng)存在于臨時表了，違反了唯一性約束，所以插入失敗，然后繼續(xù)執(zhí)行

取到第二行id=999，插入臨時表成功。

從臨時表中按行取出數(shù)據(jù)，返回結(jié)果，并刪除臨時表，結(jié)果中包含兩行數(shù)據(jù)分別是1000和999。

union 執(zhí)行流程

這里內(nèi)存臨時表用于暫存數(shù)據(jù)，而且計算過程還用上了臨時表主鍵id的唯一性約束，實現(xiàn)了union語義。

若把上面語句的union改成union all，就失去了“去重”語義。執(zhí)行時，就依次執(zhí)行子查詢，得到的結(jié)果直接作為結(jié)果集的一部分，發(fā)給客戶端。也就不需要臨時表了。

union all的執(zhí)行計劃

Extra=Using index，只使用了覆蓋索引，沒有用臨時表。

group by 執(zhí)行流程

把t1里的數(shù)據(jù)，按照 id%10 進行分組統(tǒng)計，并按m的結(jié)果排序后輸出。

group by 的執(zhí)行計劃

在Extra字段里面，我們可以看到三個信息：

Using index，使用覆蓋索引，選擇了索引a，不需回表

Using temporary，使用臨時表

Using filesort，需要排序

group by執(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é)果集返回給客戶端

圖中最后一步，對內(nèi)存臨時表的排序

內(nèi)存臨時表排序流程

臨時表的排序過程就是圖中虛線框

如果你的需求并不需要對結(jié)果進行排序，那你可以在SQL語句末尾增加order by null，也就是改成：

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

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

group + order by null 的結(jié)果（內(nèi)存臨時表）

由于t1中的id值從1開始，因此返回的結(jié)果集中第一行是id=1；掃描到id=10的時候才插入m=0

由于臨時表只有10行，內(nèi)存可以放得下，因此全程只使用內(nèi)存臨時表。

內(nèi)存臨時表的大小是有限制的，參數(shù)tmp_table_size就是控制這個內(nèi)存大小的，默認16M。

若執(zhí)行

把內(nèi)存臨時表的大小限制為最大1024K，并把語句改成id % 100，這樣返回結(jié)果里有100行數(shù)據(jù)。但這時內(nèi)存臨時表大小存不下這100行。

此時會把內(nèi)存臨時表轉(zhuǎn)成磁盤臨時表，磁盤臨時表默認使用的引擎是InnoDB。 這時，返回的結(jié)果如圖：

group + order by null 的結(jié)果（磁盤臨時表）

若t1的數(shù)據(jù)量很大，可能該查詢需要的磁盤臨時表就會占用大量磁盤空間。

優(yōu)化group by

索引

無論內(nèi)存臨時表還是磁盤臨時表，group by都需要構(gòu)造一個帶唯一索引的表，執(zhí)行代價較高。若表數(shù)據(jù)量較大，上面這個group by執(zhí)行就很慢。

group by是統(tǒng)計不同的值出現(xiàn)的個數(shù)。但由于每行的id%100結(jié)果無序，所以需要有一個臨時表，來記錄并統(tǒng)計結(jié)果。

若掃描過程可保證出現(xiàn)的數(shù)據(jù)有序，是不是簡單了？

假設，現(xiàn)在有一個類似如下這么一個數(shù)據(jù)結(jié)構(gòu)，我們來看看group by可以怎么做。

group by算法優(yōu)化-有序輸入

所以，若確保輸入數(shù)據(jù)有序，則計算group by時，就只需從左到右，順序掃描，依次累加：

當碰到第一個1時，已經(jīng)知道累積了X個0，結(jié)果集里的第一行就是(0,X)

當碰到第一個2的時候，已經(jīng)知道累積了Y個1，結(jié)果集里的第二行就是(1,Y);

按照這個邏輯執(zhí)行的話，掃描到整個輸入的數(shù)據(jù)結(jié)束，即可拿到group by的結(jié)果，無需臨時表，也無需額外排序。

InnoDB索引剛好滿足這個輸入有序。

Mysql 5.7支持generated column，以實現(xiàn)列數(shù)據(jù)的關聯(lián)更新。

創(chuàng)建一個列z，然后在z創(chuàng)建索引（≤5.6，也可以創(chuàng)建普通列和索引）。

alter table t1 add column z int generated always as (id % 100), add index (z);

這樣，索引z上的數(shù)據(jù)就有序了。上面的group by即可改成：

select z, count(*) as c from t1 group by z;

group by 優(yōu)化的執(zhí)行計劃

從 Extra 可知該語句不再需要臨時表，也無需排序。

直接排序

若可以通過加索引完成group by自然很棒。但若碰上不適合創(chuàng)建索引的場景，還是要做排序。

此時group by怎么優(yōu)化？

若我們明知道，一個group by需要放到臨時表上的數(shù)據(jù)量很大，卻還是要“先放到內(nèi)存臨時表，插入一部分數(shù)據(jù)后，發(fā)現(xiàn)內(nèi)存臨時表不夠用了再轉(zhuǎn)成磁盤臨時表”，就很蠢了

那這Mysql有無直接走磁盤臨時表的方法？

有的。

在group by加入SQL_BIG_RESULT這個提示（hint），就可以告訴優(yōu)化器：這個語句涉及的數(shù)據(jù)量很大，請直接用磁盤臨時表。

MySQL的優(yōu)化器一看，磁盤臨時表是B+樹存儲，存儲效率不如數(shù)組。所以，既然你告訴我數(shù)據(jù)量很大，那從磁盤空間考慮，還是直接用數(shù)組存。

因此，下面這個語句

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

執(zhí)行流程：

初始化sort_buffer，確定放入一個整型字段，記為m

掃描t1的索引a，依次取出里面的id值, 將 id%100值存入sort_buffer

掃描完成后，對sort_buffer的字段m做排序（如果sort_buffer內(nèi)存不夠用，就會利用磁盤臨時文件輔助排序）

排序完成后，就得到了一個有序數(shù)組。

根據(jù)有序數(shù)組，得到數(shù)組里不同值，以及每個值的出現(xiàn)次數(shù)。

使用 SQL_BIG_RESULT的執(zhí)行流程

SQL_BIG_RESULT的explain

該語句沒有使用臨時表，而直接用排序算法。

所以

MySQL什么時候會使用內(nèi)部臨時表？

若語句執(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ù)

MySQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。