前面我們介紹了 sort buffer、內存臨時表和 join buffer呆馁。這三個數(shù)據(jù)結構都是用來存放語句執(zhí)行過程中的中間數(shù)據(jù)桐经，以輔助 SQL 語句的執(zhí)行的。其中智哀，我們在排序的時候用到了 sort buffer次询，在使用 join 語句的時候用到了 join buffer。那這里我們看看MySQL 什么時候會使用內部臨時表呢瓷叫？
union 執(zhí)行流程
- 為了便于量化分析，我們使用如下準備的數(shù)據(jù)：
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送巡，它的語義是摹菠，取這兩個子查詢結果的并集。并集的意思就是這兩個集合加起來骗爆，重復的行只保留一行次氨。
union語句explain結果
- 第二行的 key=PRIMARY，說明第二個子句用到了索引 id摘投。
- 第三行的 Extra 字段煮寡，表示在對子查詢的結果集做 union 的時候虹蓄，使用了臨時表 (Using temporary)。
- 這個語句的執(zhí)行流程是這樣的：
- 創(chuàng)建一個內存臨時表幸撕，這個臨時表只有一個整型字段 f薇组，并且 f 是主鍵字段。
- 執(zhí)行第一個子查詢坐儿，得到 1000 這個值律胀，并存入臨時表中。
- 執(zhí)行第二個子查詢：
- 拿到第一行 id=1000貌矿，試圖插入臨時表中炭菌。但由于 1000 這個值已經(jīng)存在于臨時表了，違反了唯一性約束逛漫，所以插入失敗黑低，然后繼續(xù)執(zhí)行；
- 取到第二行 id=999酌毡，插入臨時表成功克握。
- 從臨時表中按行取出數(shù)據(jù)，返回結果阔馋，并刪除臨時表玛荞，結果中包含兩行數(shù)據(jù)分別是 1000 和 999。
union 執(zhí)行流程
- 可以看到呕寝，這里的內存臨時表起到了暫存數(shù)據(jù)的作用勋眯，而且計算過程還用上了臨時表主鍵 id 的唯一性約束，實現(xiàn)了 union 的語義下梢。
- 順便提一下客蹋，如果把上面這個語句中的 union 改成 union all 的話，就沒有了“去重”的語義孽江。這樣執(zhí)行的時候讶坯，就依次執(zhí)行子查詢，得到的結果直接作為結果集的一部分岗屏，發(fā)給客戶端辆琅。因此也就不需要臨時表了。
union all 的 explain 結果
- 可以看到这刷，第二行的 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 進行分組統(tǒng)計，并按照 m 的結果排序后輸出昙衅。它的 explain 結果如下：
group by explain結果
- 在 Extra 字段里面扬霜，我們可以看到三個信息：
- Using index，表示這個語句使用了覆蓋索引而涉，選擇了索引 a著瓶，不需要回表；
- Using temporary婴谱，表示使用了臨時表蟹但；
- Using filesort，表示需要排序谭羔。
- 這個語句的執(zhí)行流程是這樣的：
- 創(chuàng)建內存臨時表华糖，表里有兩個字段 m 和 c，主鍵是 m瘟裸；
- 掃描表 t1 的索引 a客叉，依次取出葉子節(jié)點上的 id 值，計算 id%10 的結果话告，記為 x兼搏；
- 如果臨時表中沒有主鍵為 x 的行，就插入一個記錄 (x,1);
- 如果表中有主鍵為 x 的行沙郭，就將 x 這一行的 c 值加 1佛呻；
- 遍歷完成后，再根據(jù)字段 m 做排序病线，得到結果集返回給客戶端吓著。
group by 執(zhí)行流程
- 圖中最后一步，對內存臨時表的排序送挑。
內存臨時表排序過程
- 接下來绑莺，我們再看一下這條語句的執(zhí)行結果：
group by 執(zhí)行結果
- 如果你的需求并不需要對結果進行排序，那你可以在 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 的結果（內存臨時表）
- 由于表 t1 中的 id 值是從 1 開始的司澎，因此返回的結果集中第一行是 id=1欺缘；掃描到 id=10 的時候才插入 m=0 這一行，因此結果集里最后一行才是 m=0挤安。
- 這個例子里由于臨時表只有 10 行浪南，內存可以放得下，因此全程只使用了內存臨時表漱受。但是，內存臨時表的大小是有限制的，參數(shù) tmp_table_size 就是控制這個內存大小的昂羡，默認是 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;
- 把內存臨時表的大小限制為最大 1024 字節(jié)，并把語句改成 id % 100虐先，這樣返回結果里有 100 行數(shù)據(jù)怨愤。但是，這時的內存臨時表大小不夠存下這 100 行數(shù)據(jù)蛹批，也就是說撰洗，執(zhí)行過程中會發(fā)現(xiàn)內存臨時表大小到達了上限（1024 字節(jié)）。
- 那么腐芍，這時候就會把內存臨時表轉成磁盤臨時表差导，磁盤臨時表默認使用的引擎是 InnoDB。 這時猪勇，返回的結果如下圖所示设褐。
group + order by null 的結果（磁盤臨時表）
- 如果這個表 t1 的數(shù)據(jù)量很大，很可能這個查詢需要的磁盤臨時表就會占用大量的磁盤空間泣刹。
group by 優(yōu)化方法 -- 索引
- 可以看到助析，不論是使用內存臨時表還是磁盤臨時表，group by 邏輯都需要構造一個帶唯一索引的表椅您，執(zhí)行代價都是比較高的外冀。如果表的數(shù)據(jù)量比較大，上面這個 group by 語句執(zhí)行起來就會很慢掀泳，我們有什么優(yōu)化的方法呢雪隧？
- 要解決 group by 語句的優(yōu)化問題，你可以先想一下這個問題：執(zhí)行 group by 語句為什么需要臨時表开伏？
- group by 的語義邏輯膀跌，是統(tǒng)計不同的值出現(xiàn)的個數(shù)。但是固灵，由于每一行的 id%100 的結果是無序的捅伤，所以我們就需要有一個臨時表，來記錄并統(tǒng)計結果巫玻。
- 那么丛忆，如果掃描過程中可以保證出現(xiàn)的數(shù)據(jù)是有序的，是不是就簡單了呢仍秤？
假設熄诡，現(xiàn)在有一個類似下圖的這么一個數(shù)據(jù)結構，我們來看看 group by 可以怎么做诗力。
group by 算法優(yōu)化 - 有序輸入
- 可以看到凰浮，如果可以確保輸入的數(shù)據(jù)是有序的，那么計算 group by 的時候，就只需要從左到右袜茧，順序掃描菜拓，依次累加。也就是下面這個過程：
- 當碰到第一個 1 的時候笛厦，已經(jīng)知道累積了 X 個 0纳鼎，結果集里的第一行就是 (0,X);
- 當碰到第一個 2 的時候，已經(jīng)知道累積了 Y 個 1裳凸，結果集里的第二行就是 (1,Y);
- 按照這個邏輯執(zhí)行的話贱鄙，掃描到整個輸入的數(shù)據(jù)結束，就可以拿到 group by 的結果姨谷，不需要臨時表逗宁，也不需要再額外排序。InnoDB 的索引菠秒，就可以滿足這個輸入有序的條件疙剑。
- 在 MySQL 5.7 版本支持了 generated column 機制，用來實現(xiàn)列數(shù)據(jù)的關聯(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ù)就是類似上圖這樣有序的了弄捕。上面的 group by 語句就可以改成：
select z, count(*) as c from t1 group by z;
- 優(yōu)化后的 group by 語句的 explain 結果僻孝，如下圖所示：
group by 優(yōu)化的 explain 結果
- 從 Extra 字段可以看到，這個語句的執(zhí)行不再需要臨時表守谓，也不需要排序了穿铆。
group by 優(yōu)化方法 -- 直接排序
- 所以，如果可以通過加索引來完成 group by 邏輯就再好不過了斋荞。但是荞雏，如果碰上不適合創(chuàng)建索引的場景，我們還是要老老實實做排序的平酿。那么凤优，這時候的 group by 要怎么優(yōu)化呢？
- 如果我們明明知道蜈彼，一個 group by 語句中需要放到臨時表上的數(shù)據(jù)量特別大筑辨，卻還是要按照“先放到內存臨時表，插入一部分數(shù)據(jù)后幸逆，發(fā)現(xià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 內存不夠用，就會利用磁盤臨時文件輔助排序）娇斩；
- 排序完成后仁卷，就得到了一個有序數(shù)組。
- 根據(jù)有序數(shù)組犬第，得到數(shù)組里面的不同值锦积，以及每個值的出現(xiàn)次數(shù)，這個前面圖中已有示例
使用 SQL_BIG_RESULT 的執(zhí)行流程圖
使用 SQL_BIG_RESULT 的 explain 結果
- 從 Extra 字段可以看到歉嗓，這個語句的執(zhí)行沒有再使用臨時表丰介，而是直接用了排序算法。
- 基于上面的 union遥椿、union all 和 group by 語句的執(zhí)行過程的分析基矮，我們來回答文章開頭的問題：MySQL 什么時候會使用內部臨時表？
- 如果語句執(zhí)行過程可以一邊讀數(shù)據(jù)冠场，一邊直接得到結果家浇，是不需要額外內存的，否則就需要額外的內存碴裙，來保存中間結果钢悲；
- join_buffer 是無序數(shù)組点额，sort_buffer 是有序數(shù)組，臨時表是二維表結構莺琳；
- 如果執(zhí)行邏輯需要用到二維表特性还棱，就會優(yōu)先考慮使用臨時表。比如我們的例子中惭等，union 需要用到唯一索引約束珍手， group by 還需要用到另外一個字段來存累積計數(shù)。
小結
本文重點講了 group by 的幾種實現(xiàn)算法辞做，從中可以總結一些使用的指導原則：
- 如果對 group by 語句的結果沒有排序要求琳要，要在語句后面加 order by null；
- 盡量讓 group by 過程用上表的索引秤茅，確認方法是 explain 結果里沒有 Using temporary 和 Using filesort稚补；
- 如果 group by 需要統(tǒng)計的數(shù)據(jù)量不大，盡量只使用內存臨時表框喳；也可以通過適當調大 tmp_table_size 參數(shù)课幕，來避免用到磁盤臨時表；
- 如果數(shù)據(jù)量實在太大五垮，使用 SQL_BIG_RESULT 這個提示乍惊，來告訴優(yōu)化器直接使用排序算法得到 group by 的結果。
