MySQL 怎么用索引實現 Group By？
我們用 explain 分析包含 group by 的 select 語句時，從輸出結果的 Extra 列經常可以看到 Using temporary; Using filesort。看到這個，我們就知道 MySQL 使用了臨時表來實現 group by。
使用臨時表實現 group by，成本高，執行慢。如果能夠利用索引中記錄已經排好序的特性，使用索引來實現 group by，那就是鳥槍換炮了。
本文我們一起來探尋 MySQL 使用索引實現 group by 的過程，使用臨時表實現 group by 會單獨用一篇文章來介紹。
本文內容基于 MySQL 5.7.35 源碼。
1、 引言
使用索引實現 group by，最簡單的方式，大概就是這樣了：
- 存儲引擎按順序一條一條讀取記錄，返回給 server 層。
- server 層判斷記錄是否符合 where 條件。
- server 層對符合條件的記錄進行聚合函數邏輯處理。
這種實現方式被稱為緊湊索引掃描。
緊湊索引掃描會對滿足 where 條件的所有記錄進行聚合函數處理，而對于 min()、max() 來說，實際需要的只有每個分組中聚合函數字段值最小或最大的那條記錄。
如果 server 層能直接從存儲引擎讀取到每個分組中聚合函數需要的那條記錄，而不必讀取每個分組中的所有記錄進行聚合函數處理，是不是就可以節省很多時間了?
是的，這種只讀取分組中部分記錄實現 group by 的方式，被稱為松散索引掃描。
為了方便描述，本文在需要的時候會以具體 SQL 作為示例說明，示例 SQL 的表結構如下：
CREATE TABLE `t_group_by` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`i1` int(10) unsigned DEFAULT '0',
`c1` char(11) DEFAULT '',
`e1` enum('北京','上海','廣州','深圳','天津','杭州','成都','重慶','蘇州','南京','洽爾濱','沈陽','長春','廈門','福州','南昌','泉州','德清','長沙','武漢') DEFAULT '北京',
`d1` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_e1_i1` (`e1`,`i1`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;1.2.3.4.5.6.7.8.9.
2、 緊湊索引掃描
group by 字段包含在索引中，并且滿足索引最左匹配原則，server 層就可以順序讀取索引中的記錄實現 group by，而不需要借助臨時表。
緊湊索引掃描中的緊湊，表示 server 層從存儲引擎讀取記錄時，以索引范圍掃描或全索引掃描方式，按順序一條一條讀取記錄，不會跳過中間的某條記錄，示意圖如下：
緊湊索引掃描
接下來，我們以 avg() 為例介紹緊湊索引掃描的執行過程，示例 SQL 如下：
select
e1, avg(i1) as t
from t_group_by
where d1 > 5452415
group by e11.2.3.4.5.
詞法分析 & 語法分析階段，avg(i1) 被解析為 Item_sum_avg 類，以下是該類的實例屬性的其中 3 個：
- sum，保存分組求和結果。
- count，保存分組計數。
- args，avg() 函數的參數，avg() 只能有一個參數。args[0] 為 i1 字段對應的 Item_field 類實例。
Item_sum_avg
avg() 只有一個參數，為什么參數屬性名是 args?
Item_sum_avg 類的實例屬性 args 是從父類 Item_sum 繼承得到的。
Item_sum_count 類(count() 對應的類)的實例屬性 args 也是從父類 Item_sum 繼承的，count() 可以有多個參數，所以，用 args 來表示聚合函數的參數。
查詢準備階段(prepare 階段)，i1 字段對應的 Item_field 類實例會關聯到表 t_group_by 的 i1 字段。
Item_sum_avg
執行階段，server 層從存儲引擎讀取到一條記錄之后，判斷記錄是否符合 where 條件(d1 > 5452415)。
記錄不符合 where 條件，繼續讀取下一條記錄。
記錄符合 where 條件，進行聚合函數邏輯處理。
如果當前記錄的分組前綴(示例 SQL 中 group by 的 e1 字段值)和上一條記錄的分組前綴不一樣，說明需要結束上一個分組，并開啟新分組。
- 結束上一個分組：通過 sum / count 計算得到分組平均值(即 avg(i1) 的結果)，把分組前綴及分組平均值發送給客戶端。
- 開啟新分組：Item_sum_avg 類的實例屬性 sum、count 清零，當前記錄的 e1 字段值作為新分組前綴，然后，新分組進行分組求和(sum 加上 i1 字段值)、分組計數(count 加 1)。
如果當前記錄的分組前綴和上一條記錄的分組前綴一樣，說明還是同一個分組，只需要進行分組求和、分組計數，不需要計算平均值。
分組求和、分組計數代碼如下：
bool Item_sum_avg::add()
{
// 分組求和
if (Item_sum_sum::add())
return TRUE;
// 分組計數（字段值不為 NULL 才進行計數）
if (!aggr->arg_is_null(true))
count++;
return FALSE;
}1.2.3.4.5.6.7.8.9.10.
只有字段值不為 NULL，分組計數(count)才會加 1。
了解 avg() 之后，count()、sum() 也就明白了。count()、sum() 和 avg() 的執行過程基本一樣，不同之處在于：
- count() 對應的類 Item_sum_count 只有 count 屬性，只需要進行分組計數，不需要分組求和、計算平均值。
- sum() 對應的類 Item_sum_sum 只有 sum 屬性，只需要進行分組求和，不需要分組計數、計算平均值。
3、 松散索引掃描
松散索引掃描，從存儲引擎讀取分組記錄時，會跳著讀，讀取分組前綴之后，直接通過分組前綴(group by 字段的值)定位到分組中符合 where 條件的第一條或最后一條記錄，而不需要讀取分組的所有記錄，然后就接著讀取下一個分組的分組前綴，這樣可以減少 select 語句執行過程中需要讀取的記錄數，從而比緊湊索引掃描更快(有例外情況，后面會介紹)。
松散索引掃描
如果 select 語句執行過程中使用了松散索引掃描實現 group by，explain 輸出結果的 Extra 列會顯示 Using index for group-by。
松散索引掃描用于 min()、max()，可以減少需要讀取的記錄數;用于 count(distinct)、sum(distinct)、avg(distinct) ，可以對記錄去重，避免使用臨時表去重。
我們以 min() 為例介紹松散索引掃描的執行過程，示例 SQL 如下：
select
e1, min(i1)
from t_group_by
group by e11.2.3.4.
詞法分析 & 語法分析階段，min(i1) 被解析為 Item_sum_min 類，以下是該類的實例屬性的其中 2 個：
- value，該屬性類型為 Item_cache，Item_cache 子類的實例屬性 value 保存分組最小值(分組記錄中 i1 字段的最小值)。
- args，min() 函數的參數，args[0] 為 i1 字段對應的 Item_field 類實例。
Item_sum_min
查詢準備階段，i1 字段對應的 Item_field 類實例會關聯到表 t_group_by 的 i1 字段。
Item_sum_min
執行階段，讀取分組最小值的過程分為兩步：
- 讀取分組前綴(示例 SQL 中 group by 的 e1 字段值)，從存儲引擎讀取分組的第一條記錄，得到分組前綴。
- 根據分組前綴讀取分組最小值(分組記錄中 i1 字段的最小值)，用前面得到的分組前綴限定索引掃描范圍，從存儲引擎讀取分組中 i1 字段的最小值，保存到 value 屬性中。
讀取分組最小值
4、 兩種索引掃描怎么選?
松散索引掃描雖然具備提升 select 語句執行效率的能力，但只有在適用的場景下才能發揮它的威力，因此，它的使用需要滿足以下條件：
條件 1，select 語句只能是單表查詢，不能是連接查詢。
條件 2，group by 字段必須滿足索引的最左匹配原則。例如：表中有一個索引包含 c1, c2, c3 三個字段，group by c1, c2 滿足最左匹配原則。
條件 3，如果 select 字段列表中包含聚合函數，聚合函數必須滿足這些條件：
- 所有聚合函數的參數都必須是同一個字段。
- 聚合函數字段必須是索引中的字段，并且 group by 字段 + 聚合函數字段也必須滿足索引最左匹配原則。
- 聚合函數要么是 min()、max() 中的 1 ~ 2 個;要么是 count(distinct)、sum(distinct)、avg(distinct) 中的 1 ~ 3個。
松散索引掃描中，兩類聚合函數不能同時存在，因為它們對于分組前綴處理邏輯不一樣。在讀取數據時，min()、max() 用 group by 字段值作為分組前綴;count(distinct)、sum(distinct)、avg(distinct) 用 group by 字段值 + 聚合函數中的字段值作為分組前綴。
條件 4，索引中所有字段必須是全字段索引，不能是前綴索引。
例如：有個字段 c1 varchar(20)，索引中該字段為 index(c1(10))，這樣的索引就不能用于松散索引掃描。
滿足以上條件，還只是站在了使用松散索引掃描的門外，想要登堂入室，還必須進行成本評估。
如果松散索引掃描的成本比緊湊索引掃描的成本低，自然就要用松散索引掃描來提升 select 語句的執行效率了。
（1） 松散索引掃描成本更高怎么辦?
松散索引掃描成本比緊湊索引掃描成本更高時，如果 select 語句中的聚合函數是 min()、max() 中的 1 ~ 2 個，就會使用緊湊索引掃描。
松散索引掃描自帶去重功能，不需要借助臨時表，和包含 distinct 關鍵字的聚合函數天生更匹配。緊湊索引掃描則需要借助臨時表對記錄進行去重。
如果聚合函數是 count(distinct)、sum(distinct)、avg(distinct) 中的 1 ~ 3 個，雖然緊湊索引掃描讀取記錄成本更低，但必須使用臨時表對記錄去重，這樣一來，緊湊索引掃描讀取數據 + 臨時表去重的成本就比松散索引掃描成本更高了。
這就很尷尬了，兩種方式各有優缺點，兩難之下，MySQL 要怎么辦?
兩難之下，最好的選擇就是找到第三個選項。為此，MySQL 祭出了一個大招，既要和緊湊索引掃描一樣順序讀取數據，又要用松散索引掃描自帶的去重能力。如果用了這個大招，在 explain 輸出結果的 Extra 列可以看到 Using index for group-by (scanning)。
MySQL 把緊湊索引掃描中使用的順序讀取記錄嵌入到松散索引掃描的邏輯里，當評估緊湊索引掃描成本比松散索引掃描低時，對于包含 distinct 關鍵字的聚合函數，就會用順序讀取記錄代替跳著讀取記錄，并且在順序讀取記錄的過程中完成記錄去重。
對于松散索引掃描的這個變種，到寫完本文為止，我還沒有在哪里看到官方有正式的命名，為了方便記憶，估且把它命名為順序松散索引掃描吧。
順序松散索引掃描
（2） 為什么松散索引掃描會比緊湊索引掃描成本高?
緊湊索引掃描，存儲引擎按順序一條一條讀取記錄，返回給 server 層，server 層判斷記錄是否符合 where 條件，然后對符合條件的記錄進行聚合函數邏輯處理。
松散索引掃描，對于每個分組，都會從存儲引擎讀取兩次數據，第一次是讀取分組的第一條記錄，得到分組前綴;第二次是根據分組前綴讀取分組中索引掃描范圍的第一條或最后一條記錄。
如果分組中的記錄數量多，第二次讀取記錄時，能跳過的記錄就多，節省的成本就多，松散索引掃描就會比緊湊索引掃描更快。
如果分組中的記錄數量少，第二次讀取記錄時，能跳過的記錄就少，每個分組讀取兩次記錄增加的成本超過了跳過記錄節省的成本，松散索引掃描就會比緊湊索引掃描更慢。
5、 總結
引言小節，介紹了 MySQL 實現 group by 的兩種索引掃描方式：緊湊索引掃描、松散索引掃描。
緊湊索引掃描小節，以 avg() 為例介紹了緊湊索引掃描的執行過程，avg() 在詞法分析 & 語法分析階段會被解析為 Item_sum_avg 類。該類的實例屬性 sum、count、args 分別用于保存分組求和結果、分組計數、avg() 函數的參數。
在執行階段，通過把 avg() 字段值累加到 sum 屬性進行分組求和;對 count 屬性進行自增實現分組計數;通過 sum / count 計算得到分組平均值。
Item_sum_count、Item_sum_sum、Item_sum_avg、Item_sum_min、Item_sum_max 類的實例屬性 args 都繼承自父類 Item_sum，用于保存聚合函數參數，count() 支持多個參數，所以，參數的屬性名為 args 而不是 arg。
松散索引掃描小節，以 min() 為例介紹了松散索引掃描的執行過程，執行階段，分為兩步讀取分組最小值：讀取分組前綴，根據分組前綴讀取分組最小值。
兩種索引掃描怎么選? 小節，介紹了使用松散索引掃描必須滿足的一系列條件。
當松散索引掃描比緊湊索引掃描成本高時，min()、max() 會選擇用緊湊索引掃描，MySQL 為 count(distinct)、sum(distinct)、avg(distinct) 引入松散索引掃描的變種，順序讀取索引記錄(和緊湊索引掃描一樣)+ 松散索引掃描自帶的記錄去重功能，避免了使用臨時表對記錄去重。
還介紹了松散索引掃描比緊湊索引掃描的成本高，是因為分組中記錄數量少時，兩次讀取存儲引擎數據增加的成本超過了跳著讀取索引記錄節省的成本。
本文轉載自微信公眾號「一樹一溪」，可以通過以下二維碼關注。轉載本文請聯系一樹一溪公眾號。
