更好的閱讀體驗，請點擊 YinKai 's Blog。

? 在 MySQL 中 group by 用于按照一個或多個列對結果集進行分組。在討論 group by 怎么優化之前，我們先來看看 group by 的執行流程，這樣我們才能對癥下藥。

group by 執行流程

? 我們先用下面的 sql 語句創建一個表，并輸入一些數據，模擬真實環境。

create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begindeclare i int;set i=1;while(i<=1000)doinsert into t1 values(i, i, i);set i=i+1;end while;
end;;
delimiter ;
call idata();

? 然后我們執行下面的語句：

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

? 這個語句的邏輯是把表 t1 里的數據，按照 id%10 進行分組統計，并按照 m 的結果排序后輸出。它的 explain 結果如下：

? 在 Extra 字段我們可以看到三個信息：

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

? 這個語句的執行過程是：

1. 創建內存臨時表，表里有兩個字段 m 和 c，主鍵是 m
2. 掃描表 t1 的索引 a，依次取出葉子結點上的 id 值，計算 id%10 的結果，記為 x；
   • 如果臨時表中沒有主鍵為 x 的行，就插入一個記錄 (x, 1)；
   • 如果臨時表中有主鍵為 x 的行，就將 x 這一行的 c 值加 1；；
3. 遍歷完成后，再根據字段 m 做排序，得到的結果返回給客戶端。

? 這個流程的執行圖如下：

? 圖中最后一步，對內存臨時表的排序過程如下：

? 上面的例子，由于臨時表只有 10 行，內存可以放得下，因此只使用了內存臨時表。但內存臨時表是有大小限制的，可以通過參數 tmp_table_size 修改，默認是 16M。

? 如果我執行下面這個語句序列：

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

? 把內存臨時表的大小限制為最大 1024 字節，并把語句改成 id % 100，這樣返回結果里有 100 行數據。但是，這時的內存臨時表大小不夠存下這 100 行數據，也就是說，執行過程中會發現內存臨時表大小到達了上限（1024 字節）。

? 那么這時候就會把內存臨時表轉成磁盤臨時表，磁盤臨時表默認使用的是 InnoDB，結果如下：

? 如果這個表 t1 的數據量很大，很可能這個查詢的磁盤臨時表需要用到很大的磁盤空間，查詢生成大型臨時表，占用大量磁盤空間可能導致查詢變慢，引起磁盤空間不足，影響系統穩定性。

? 因此，這就是為什么我們需要去優化 group_by 的原因。

group by 優化方法——索引

? 要解決 group by 的優化問題，我們需要從根本上去解決問題，即執行 group by 語句創建的臨時表。

? group by 的語義邏輯，是統計不同的值出現的個數。但是由于每一行的 id%100 的結果是無序的，所以我們就需要有一個臨時表，來記錄并統計結果。

? 那我們假想出現的數據都是有序的，看看 group by 會怎么做。

? 如果我們可以確保輸入的數據都是有序的，那么計算 group by 的時候，就只需要從左往右順序掃描，依次累加，即：

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

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

? 不難想到，InnoDB 的索引就可以滿足這個輸入有序的條件。

? 我們可以 MySQL5.7 版本的 generated column 機制，用來實現列數據的關聯更新。你可以用下面的方法創建一個列 z，然后在 z 列上創建一個索引

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

? 這樣，索引 z 上的數據就是類似上圖那樣有序的了。上面的 group by 語句就可以改成：

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

? 優化后的 group by 語句的 explain 結果，如下圖所示：

? 從 Extra 字段可以看出，這個語句的執行不再需要臨時表了，也不需要排序了。

group by 優化方法 – 直接排序

? 如果能使用創建索引的方式來優化那再好不過，萬一要是遇到了不適合創建索引的創建，我們又該怎么辦呢？

? 當我們明確知道一個 GROUP BY 語句中涉及的數據量非常大，而 MySQL 的默認行為是首先嘗試在內存中創建臨時表，然后在內存不足的情況下將其轉為磁盤臨時表，我們可能希望直接走磁盤臨時表的方式，以避免不必要的內存消耗。MySQL 提供了一個查詢提示 SQL_BIG_RESULT 來實現這一點。

具體而言，你可以在 GROUP BY 語句中加入 SQL_BIG_RESULT 提示，告訴優化器：由于數據量較大，請直接使用磁盤臨時表。這樣，優化器會考慮在磁盤上存儲臨時表，而不是首先嘗試在內存中完成這一操作。

以下是使用 SQL_BIG_RESULT 提示的一個示例：

SELECT SQL_BIG_RESULT id % 100 AS m, COUNT(*) AS c FROM t1 GROUP BY m;

這個查詢的執行流程可以描述為：

1. 初始化 sort_buffer，確定放入一個整型字段 m。
2. 掃描表 t1 的索引 a，依次取出其中的 id 值，將 id % 100 的值存入 sort_buffer 中。
3. 掃描完成后，對 sort_buffer 的字段 m 進行排序。如果 sort_buffer 內存不足，將會利用磁盤臨時文件輔助排序。
4. 排序完成后，得到一個有序數組。
5. 根據有序數組，獲取數組中的不同值以及每個值的出現次數。

? 這樣，通過使用 SQL_BIG_RESULT 提示，你可以明確告知 MySQL 優化器，考慮到數據量很大，直接使用磁盤臨時表。

? 執行 explain 的結果如下圖：

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

小結

? 現在我們來總結一下使用 group by 需要注意的一些點：

1. 如果對 group by 語句的結果沒有排序要求，要在語句后面加 order by null；
2. 使用 group by 的時候，盡可能用上表的索引，確認的方法是查看 explain 結果里有沒有 Using temporary 和 Using filesort；
3. 如果 gruop by 需要統計的數據量不大，盡量只使用內存臨時表；也可以通過適當調大 tmp_table_size 參數避免使用磁盤臨時表；
4. 如果數據量實在太大，使用 SQL_BIG_RESULT 這個提示，來告訴優化器直接使用排序算法得到 group by 的結果。

? 最后，我們來看一看文章開頭的問題：

? MySQL中 group by 怎么優化？

1. 盡可能保證 group by 語句上存在索引，這樣有助于數據引擎更有效地執行分組操作，我們可以通過查看執行計劃 explain 的輸出，來確認是否使用了索引。
2. 如果內存臨時表足夠容納 group by 的結果集的話，可以適當增加內存臨時表的參數大小，使 MySQL 更傾向于使用內存臨時表，因為內存的讀寫速度遠高于磁盤，這樣可以顯著提高查詢性能。
3. 如果 GROUP BY 的字段是通過某個表達式計算而來，考慮使用生成列，并在生成列上創建索引。
4. 在 GROUP BY 的數據量非常大且無法通過其他手段優化時，可以使用 SQL_BIG_RESULT 提示，**讓優化器直接使用排序算法而不是創建臨時表，**這樣 MySQL 就可以直接通過遍歷數組獲取我們想要的結果。