在第16和第34篇文章中，我分別和你介紹了sort buffer、內(nèi)存臨時表和join buffer。這三個數(shù)據(jù)結(jié)構(gòu)都是用來存放語句執(zhí)行過程中的中間數(shù)據(jù)，以輔助SQL語句的執(zhí)行的。其中，我們在排序的時候用到了sort buffer，在使用join語句的時候用到了join buffer。

然后，你可能會有這樣的疑問，MySQL什么時候會使用內(nèi)部臨時表呢？

今天這篇文章，我就先給你舉兩個需要用到內(nèi)部臨時表的例子，來看看內(nèi)部臨時表是怎么工作的。然后，我們再來分析，什么情況下會使用內(nèi)部臨時表。

union 執(zhí)行流程

為了便于量化分析，我用下面的表t1來舉例。

create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
  declare i int;

  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

然后，我們執(zhí)行下面這條語句：

(select 1000 as f) union (select id from t1 order by id desc limit 2);

這條語句用到了union，它的語義是，取這兩個子查詢結(jié)果的并集。并集的意思就是這兩個集合加起來，重復(fù)的行只保留一行。

下圖是這個語句的explain結(jié)果。

可以看到：

• 第二行的key=PRIMARY，說明第二個子句用到了索引id。
• 第三行的Extra字段，表示在對子查詢的結(jié)果集做union的時候，使用了臨時表(Using temporary)。

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

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

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

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

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

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

這個過程的流程圖如下所示：

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

順便提一下，如果把上面這個語句中的union改成union all的話，就沒有了“去重”的語義。這樣執(zhí)行的時候，就依次執(zhí)行子查詢，得到的結(jié)果直接作為結(jié)果集的一部分，發(fā)給客戶端。因此也就不需要臨時表了。

可以看到，第二行的Extra字段顯示的是Using index，表示只使用了覆蓋索引，沒有用臨時表了。

group by 執(zhí)行流程

另外一個常見的使用臨時表的例子是group by，我們來看一下這個語句：

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

這個語句的邏輯是把表t1里的數(shù)據(jù)，按照 id%10 進(jìn)行分組統(tǒng)計，并按照m的結(jié)果排序后輸出。它的explain結(jié)果如下：

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

• Using index，表示這個語句使用了覆蓋索引，選擇了索引a，不需要回表；
• Using temporary，表示使用了臨時表；
• Using filesort，表示需要排序。

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

1. 創(chuàng)建內(nèi)存臨時表，表里有兩個字段m和c，主鍵是m；

2. 掃描表t1的索引a，依次取出葉子節(jié)點上的id值，計算id%10的結(jié)果，記為x；

   • 如果臨時表中沒有主鍵為x的行，就插入一個記錄(x,1);
   • 如果表中有主鍵為x的行，就將x這一行的c值加1；

3. 遍歷完成后，再根據(jù)字段m做排序，得到結(jié)果集返回給客戶端。

這個流程的執(zhí)行圖如下：

圖中最后一步，對內(nèi)存臨時表的排序，在第17篇文章中已經(jīng)有過介紹，我把圖貼過來，方便你回顧。

其中，臨時表的排序過程就是圖6中虛線框內(nèi)的過程。

接下來，我們再看一下這條語句的執(zhí)行結(jié)果：

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

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

這樣就跳過了最后排序的階段，直接從臨時表中取數(shù)據(jù)返回。返回的結(jié)果如圖8所示。

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

這個例子里由于臨時表只有10行，內(nèi)存可以放得下，因此全程只使用了內(nèi)存臨時表。但是，內(nèi)存臨時表的大小是有限制的，參數(shù)tmp_table_size就是控制這個內(nèi)存大小的，默認(rèn)是16M。

如果我執(zhí)行下面這個語句序列：

set tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

把內(nèi)存臨時表的大小限制為最大1024字節(jié)，并把語句改成id % 100，這樣返回結(jié)果里有100行數(shù)據(jù)。但是，這時的內(nèi)存臨時表大小不夠存下這100行數(shù)據(jù)，也就是說，執(zhí)行過程中會發(fā)現(xiàn)內(nèi)存臨時表大小到達(dá)了上限（1024字節(jié)）。

那么，這時候就會把內(nèi)存臨時表轉(zhuǎn)成磁盤臨時表，磁盤臨時表默認(rèn)使用的引擎是InnoDB。 這時，返回的結(jié)果如圖9所示。

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

group by 優(yōu)化方法 --索引

可以看到，不論是使用內(nèi)存臨時表還是磁盤臨時表，group by邏輯都需要構(gòu)造一個帶唯一索引的表，執(zhí)行代價都是比較高的。如果表的數(shù)據(jù)量比較大，上面這個group by語句執(zhí)行起來就會很慢，我們有什么優(yōu)化的方法呢？

要解決group by語句的優(yōu)化問題，你可以先想一下這個問題：執(zhí)行g(shù)roup by語句為什么需要臨時表？

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

那么，如果掃描過程中可以保證出現(xiàn)的數(shù)據(jù)是有序的，是不是就簡單了呢？

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

可以看到，如果可以確保輸入的數(shù)據(jù)是有序的，那么計算group by的時候，就只需要從左到右，順序掃描，依次累加。也就是下面這個過程：

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

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

你一定想到了，InnoDB的索引，就可以滿足這個輸入有序的條件。

在MySQL 5.7版本支持了generated column機(jī)制，用來實現(xiàn)列數(shù)據(jù)的關(guān)聯(lián)更新。你可以用下面的方法創(chuàng)建一個列z，然后在z列上創(chuàng)建一個索引（如果是MySQL 5.6及之前的版本，你也可以創(chuàng)建普通列和索引，來解決這個問題）。

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

這樣，索引z上的數(shù)據(jù)就是類似圖10這樣有序的了。上面的group by語句就可以改成：

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

優(yōu)化后的group by語句的explain結(jié)果，如下圖所示：

從Extra字段可以看到，這個語句的執(zhí)行不再需要臨時表，也不需要排序了。

group by優(yōu)化方法 --直接排序

所以，如果可以通過加索引來完成group by邏輯就再好不過了。但是，如果碰上不適合創(chuàng)建索引的場景，我們還是要老老實實做排序的。那么，這時候的group by要怎么優(yōu)化呢？

如果我們明明知道，一個group by語句中需要放到臨時表上的數(shù)據(jù)量特別大，卻還是要按照“先放到內(nèi)存臨時表，插入一部分?jǐn)?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í)行流程就是這樣的：

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

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

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

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

根據(jù)有序數(shù)組，得到數(shù)組里面的不同值，以及每個值的出現(xiàn)次數(shù)。這一步的邏輯，你已經(jīng)從前面的圖10中了解過了。

下面兩張圖分別是執(zhí)行流程圖和執(zhí)行explain命令得到的結(jié)果。

從Extra字段可以看到，這個語句的執(zhí)行沒有再使用臨時表，而是直接用了排序算法。

基于上面的union、union all和group by語句的執(zhí)行過程的分析，我們來回答文章開頭的問題：MySQL什么時候會使用內(nèi)部臨時表？

1. 如果語句執(zhí)行過程可以一邊讀數(shù)據(jù)，一邊直接得到結(jié)果，是不需要額外內(nèi)存的，否則就需要額外的內(nèi)存，來保存中間結(jié)果；

2. join_buffer是無序數(shù)組，sort_buffer是有序數(shù)組，臨時表是二維表結(jié)構(gòu)；

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

小結(jié)

通過今天這篇文章，我重點和你講了group by的幾種實現(xiàn)算法，從中可以總結(jié)一些使用的指導(dǎo)原則：

1. 如果對group by語句的結(jié)果沒有排序要求，要在語句后面加 order by null；

2. 盡量讓group by過程用上表的索引，確認(rèn)方法是explain結(jié)果里沒有Using temporary 和 Using filesort；

3. 如果group by需要統(tǒng)計的數(shù)據(jù)量不大，盡量只使用內(nèi)存臨時表；也可以通過適當(dāng)調(diào)大tmp_table_size參數(shù)，來避免用到磁盤臨時表；

4. 如果數(shù)據(jù)量實在太大，使用SQL_BIG_RESULT這個提示，來告訴優(yōu)化器直接使用排序算法得到group by的結(jié)果。

最后，我給你留下一個思考題吧。

文章中圖8和圖9都是order by null，為什么圖8的返回結(jié)果里面，0是在結(jié)果集的最后一行，而圖9的結(jié)果里面，0是在結(jié)果集的第一行？