MySQL 怎么用索引實(shí)現(xiàn) group by？

我們用 explain 分析包含 group by 的 select 語(yǔ)句時(shí)，從輸出結(jié)果的 Extra 列經(jīng)常可以看到 Using temporary; Using filesort。看到這個(gè)，我們就知道 MySQL 使用了臨時(shí)表來實(shí)現(xiàn) group by。

使用臨時(shí)表實(shí)現(xiàn) group by，成本高，執(zhí)行慢。如果能夠利用索引中記錄已經(jīng)排好序的特性，使用索引來實(shí)現(xiàn) group by，那就是鳥槍換炮了。

本文我們一起來探尋 MySQL 使用索引實(shí)現(xiàn) group by 的過程，使用臨時(shí)表實(shí)現(xiàn) group by 會(huì)單獨(dú)用一篇文章來介紹。

本文內(nèi)容基于 MySQL 5.7.35 源碼。

內(nèi)容目錄

1. 引言
2. 緊湊索引掃描
3. 松散索引掃描
4. 兩種索引掃描怎么選？
   4.1 松散索引掃描成本更高怎么辦？
   4.2 為什么松散索引掃描會(huì)比緊湊索引掃描成本高？
   
5. 總結(jié)

1. 引言

使用索引實(shí)現(xiàn) group by，最簡(jiǎn)單的方式，大概就是這樣了：

• 存儲(chǔ)引擎按順序一條一條讀取記錄，返回給 server 層。
• server 層判斷記錄是否符合 where 條件。
• server 層對(duì)符合條件的記錄進(jìn)行聚合函數(shù)邏輯處理。

這種實(shí)現(xiàn)方式被稱為緊湊索引掃描。

緊湊索引掃描會(huì)對(duì)滿足 where 條件的所有記錄進(jìn)行聚合函數(shù)處理，而對(duì)于 min()、max() 來說，實(shí)際需要的只有每個(gè)分組中聚合函數(shù)字段值最小或最大的那條記錄。

如果 server 層能直接從存儲(chǔ)引擎讀取到每個(gè)分組中聚合函數(shù)需要的那條記錄，而不必讀取每個(gè)分組中的所有記錄進(jìn)行聚合函數(shù)處理，是不是就可以節(jié)省很多時(shí)間了？

是的，這種只讀取分組中部分記錄實(shí)現(xiàn) group by 的方式，被稱為松散索引掃描。

為了方便描述，本文在需要的時(shí)候會(huì)以具體 SQL 作為示例說明，示例 SQL 的表結(jié)構(gòu)如下：

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('北京','上海','廣州','深圳','天津','杭州','成都','重慶','蘇州','南京','洽爾濱','沈陽(yáng)','長(zhǎng)春','廈門','福州','南昌','泉州','德清','長(zhǎng)沙','武漢')?DEFAULT?'北京',
??`d1`?decimal(10,2)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`idx_e1_i1`?(`e1`,`i1`)?USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

2. 緊湊索引掃描

group by 字段包含在索引中，并且滿足索引最左匹配原則，server 層就可以順序讀取索引中的記錄實(shí)現(xiàn) group by，而不需要借助臨時(shí)表。

緊湊索引掃描中的緊湊，表示 server 層從存儲(chǔ)引擎讀取記錄時(shí)，以索引范圍掃描或全索引掃描方式，按順序一條一條讀取記錄，不會(huì)跳過中間的某條記錄，示意圖如下：

接下來，我們以 avg() 為例介紹緊湊索引掃描的執(zhí)行過程，示例 SQL 如下：

select
??e1,?avg(i1)?as?t
from?t_group_by
where?d1?>?5452415
group?by?e1

詞法分析 & 語(yǔ)法分析階段，avg(i1) 被解析為 Item_sum_avg 類，以下是該類的實(shí)例屬性的其中 3 個(gè)：

• sum，保存分組求和結(jié)果。
• count，保存分組計(jì)數(shù)。
• args，avg() 函數(shù)的參數(shù)，avg() 只能有一個(gè)參數(shù)。args[0] 為 i1 字段對(duì)應(yīng)的 Item_field 類實(shí)例。

avg() 只有一個(gè)參數(shù)，為什么參數(shù)屬性名是 args？

Item_sum_avg 類的實(shí)例屬性 args 是從父類 Item_sum 繼承得到的。

Item_sum_count 類（count() 對(duì)應(yīng)的類）的實(shí)例屬性 args 也是從父類 Item_sum 繼承的，count() 可以有多個(gè)參數(shù)，所以，用 args 來表示聚合函數(shù)的參數(shù)。

查詢準(zhǔn)備階段（prepare 階段），i1 字段對(duì)應(yīng)的 Item_field 類實(shí)例會(huì)關(guān)聯(lián)到表 t_group_by 的 i1 字段。

執(zhí)行階段，server 層從存儲(chǔ)引擎讀取到一條記錄之后，判斷記錄是否符合 where 條件（d1 > 5452415）。

記錄不符合 where 條件，繼續(xù)讀取下一條記錄。

記錄符合 where 條件，進(jìn)行聚合函數(shù)邏輯處理。
如果當(dāng)前記錄的分組前綴（示例 SQL 中 group by 的 e1 字段值）和上一條記錄的分組前綴不一樣，說明需要結(jié)束上一個(gè)分組，并開啟新分組。

• 結(jié)束上一個(gè)分組：通過 sum / count 計(jì)算得到分組平均值（即 avg(i1) 的結(jié)果），把分組前綴及分組平均值發(fā)送給客戶端。
• 開啟新分組：Item_sum_avg 類的實(shí)例屬性 sum、count 清零，當(dāng)前記錄的 e1 字段值作為新分組前綴，然后，新分組進(jìn)行分組求和（sum 加上 i1 字段值）、分組計(jì)數(shù)（count 加 1）。

如果當(dāng)前記錄的分組前綴和上一條記錄的分組前綴一樣，說明還是同一個(gè)分組，只需要進(jìn)行分組求和、分組計(jì)數(shù)，不需要計(jì)算平均值。

分組求和、分組計(jì)數(shù)代碼如下：

bool?Item_sum_avg::add()
{
??//?分組求和
??if?(Item_sum_sum::add())
????return?TRUE;
??//?分組計(jì)數(shù)（字段值不為?NULL?才進(jìn)行計(jì)數(shù)）
??if?(!aggr->arg_is_null(true))
????count++;
??return?FALSE;
}

只有字段值不為 NULL，分組計(jì)數(shù)（count）才會(huì)加 1。

了解 avg() 之后，count()、sum() 也就明白了。count()、sum() 和 avg() 的執(zhí)行過程基本一樣，不同之處在于：

• count() 對(duì)應(yīng)的類 Item_sum_count 只有 count 屬性，只需要進(jìn)行分組計(jì)數(shù)，不需要分組求和、計(jì)算平均值。
• sum() 對(duì)應(yīng)的類 Item_sum_sum 只有 sum 屬性，只需要進(jìn)行分組求和，不需要分組計(jì)數(shù)、計(jì)算平均值。

3. 松散索引掃描

松散索引掃描，從存儲(chǔ)引擎讀取分組記錄時(shí)，會(huì)跳著讀，讀取分組前綴之后，直接通過分組前綴（group by 字段的值）定位到分組中符合 where 條件的第一條或最后一條記錄，而不需要讀取分組的所有記錄，然后就接著讀取下一個(gè)分組的分組前綴，這樣可以減少 select 語(yǔ)句執(zhí)行過程中需要讀取的記錄數(shù)，從而比緊湊索引掃描更快（有例外情況，后面會(huì)介紹）。

如果 select 語(yǔ)句執(zhí)行過程中使用了松散索引掃描實(shí)現(xiàn) group by，explain 輸出結(jié)果的 Extra 列會(huì)顯示 Using index for group-by。

松散索引掃描用于 min()、max()，可以減少需要讀取的記錄數(shù)；用于 count(distinct)、sum(distinct)、avg(distinct) ，可以對(duì)記錄去重，避免使用臨時(shí)表去重。

我們以 min() 為例介紹松散索引掃描的執(zhí)行過程，示例 SQL 如下：

select
??e1,?min(i1)
from?t_group_by
group?by?e1

詞法分析 & 語(yǔ)法分析階段，min(i1) 被解析為 Item_sum_min 類，以下是該類的實(shí)例屬性的其中 2 個(gè)：

• value，該屬性類型為 Item_cache，Item_cache 子類的實(shí)例屬性 value 保存分組最小值（分組記錄中 i1 字段的最小值）。
• args，min() 函數(shù)的參數(shù)，args[0] 為 i1 字段對(duì)應(yīng)的 Item_field 類實(shí)例。

查詢準(zhǔn)備階段，i1 字段對(duì)應(yīng)的 Item_field 類實(shí)例會(huì)關(guān)聯(lián)到表 t_group_by 的 i1 字段。

執(zhí)行階段，讀取分組最小值的過程分為兩步：

• 讀取分組前綴（示例 SQL 中 group by 的 e1 字段值），從存儲(chǔ)引擎讀取分組的第一條記錄，得到分組前綴。
• 根據(jù)分組前綴讀取分組最小值（分組記錄中 i1 字段的最小值），用前面得到的分組前綴限定索引掃描范圍，從存儲(chǔ)引擎讀取分組中 i1 字段的最小值，保存到 value 屬性中。

4. 兩種索引掃描怎么選？

松散索引掃描雖然具備提升 select 語(yǔ)句執(zhí)行效率的能力，但只有在適用的場(chǎng)景下才能發(fā)揮它的威力，因此，它的使用需要滿足以下條件：

條件 1，select 語(yǔ)句只能是單表查詢，不能是連接查詢。

條件 2，group by 字段必須滿足索引的最左匹配原則。例如：表中有一個(gè)索引包含 c1, c2, c3 三個(gè)字段，group by c1, c2 滿足最左匹配原則。

條件 3，如果 select 字段列表中包含聚合函數(shù)，聚合函數(shù)必須滿足這些條件：

• 所有聚合函數(shù)的參數(shù)都必須是同一個(gè)字段。
• 聚合函數(shù)字段必須是索引中的字段，并且 group by 字段 + 聚合函數(shù)字段也必須滿足索引最左匹配原則。
• 聚合函數(shù)要么是 min()、max() 中的 1 ~ 2 個(gè)；要么是 count(distinct)、sum(distinct)、avg(distinct) 中的 1 ~ 3個(gè)。
  

  松散索引掃描中，兩類聚合函數(shù)不能同時(shí)存在，因?yàn)樗鼈儗?duì)于分組前綴處理邏輯不一樣。在讀取數(shù)據(jù)時(shí)，min()、max() 用 group by 字段值作為分組前綴；count(distinct)、sum(distinct)、avg(distinct) 用 group by 字段值 + 聚合函數(shù)中的字段值作為分組前綴。

條件 4，索引中所有字段必須是全字段索引，不能是前綴索引。
例如：有個(gè)字段 c1 varchar(20)，索引中該字段為 index(c1(10))，這樣的索引就不能用于松散索引掃描。

滿足以上條件，還只是站在了使用松散索引掃描的門外，想要登堂入室，還必須進(jìn)行成本評(píng)估。

如果松散索引掃描的成本比緊湊索引掃描的成本低，自然就要用松散索引掃描來提升 select 語(yǔ)句的執(zhí)行效率了。

4.1 松散索引掃描成本更高怎么辦？

松散索引掃描成本比緊湊索引掃描成本更高時(shí)，如果 select 語(yǔ)句中的聚合函數(shù)是 min()、max() 中的 1 ~ 2 個(gè)，就會(huì)使用緊湊索引掃描。

松散索引掃描自帶去重功能，不需要借助臨時(shí)表，和包含 distinct 關(guān)鍵字的聚合函數(shù)天生更匹配。緊湊索引掃描則需要借助臨時(shí)表對(duì)記錄進(jìn)行去重。

如果聚合函數(shù)是 count(distinct)、sum(distinct)、avg(distinct) 中的 1 ~ 3 個(gè)，雖然緊湊索引掃描讀取記錄成本更低，但必須使用臨時(shí)表對(duì)記錄去重，這樣一來，緊湊索引掃描讀取數(shù)據(jù) + 臨時(shí)表去重的成本就比松散索引掃描成本更高了。

這就很尷尬了，兩種方式各有優(yōu)缺點(diǎn)，兩難之下，MySQL 要怎么辦？

兩難之下，最好的選擇就是找到第三個(gè)選項(xiàng)。為此，MySQL 祭出了一個(gè)大招，既要和緊湊索引掃描一樣順序讀取數(shù)據(jù)，又要用松散索引掃描自帶的去重能力。如果用了這個(gè)大招，在 explain 輸出結(jié)果的 Extra 列可以看到 Using index for group-by (scanning)。

MySQL 把緊湊索引掃描中使用的順序讀取記錄嵌入到松散索引掃描的邏輯里，當(dāng)評(píng)估緊湊索引掃描成本比松散索引掃描低時(shí)，對(duì)于包含 distinct 關(guān)鍵字的聚合函數(shù)，就會(huì)用順序讀取記錄代替跳著讀取記錄，并且在順序讀取記錄的過程中完成記錄去重。

對(duì)于松散索引掃描的這個(gè)變種，到寫完本文為止，我還沒有在哪里看到官方有正式的命名，為了方便記憶，估且把它命名為順序松散索引掃描吧。

4.2 為什么松散索引掃描會(huì)比緊湊索引掃描成本高？

緊湊索引掃描，存儲(chǔ)引擎按順序一條一條讀取記錄，返回給 server 層，server 層判斷記錄是否符合 where 條件，然后對(duì)符合條件的記錄進(jìn)行聚合函數(shù)邏輯處理。

松散索引掃描，對(duì)于每個(gè)分組，都會(huì)從存儲(chǔ)引擎讀取兩次數(shù)據(jù)，第一次是讀取分組的第一條記錄，得到分組前綴；第二次是根據(jù)分組前綴讀取分組中索引掃描范圍的第一條或最后一條記錄。

如果分組中的記錄數(shù)量多，第二次讀取記錄時(shí)，能跳過的記錄就多，節(jié)省的成本就多，松散索引掃描就會(huì)比緊湊索引掃描更快。

如果分組中的記錄數(shù)量少，第二次讀取記錄時(shí)，能跳過的記錄就少，每個(gè)分組讀取兩次記錄增加的成本超過了跳過記錄節(jié)省的成本，松散索引掃描就會(huì)比緊湊索引掃描更慢。

5. 總結(jié)

引言小節(jié)，介紹了 MySQL 實(shí)現(xiàn) group by 的兩種索引掃描方式：緊湊索引掃描、松散索引掃描。

緊湊索引掃描小節(jié)，以 avg() 為例介紹了緊湊索引掃描的執(zhí)行過程，avg() 在詞法分析 & 語(yǔ)法分析階段會(huì)被解析為 Item_sum_avg 類。該類的實(shí)例屬性 sum、count、args 分別用于保存分組求和結(jié)果、分組計(jì)數(shù)、avg() 函數(shù)的參數(shù)。

在執(zhí)行階段，通過把 avg() 字段值累加到 sum 屬性進(jìn)行分組求和；對(duì) count 屬性進(jìn)行自增實(shí)現(xiàn)分組計(jì)數(shù)；通過 sum / count 計(jì)算得到分組平均值。

Item_sum_count、Item_sum_sum、Item_sum_avg、Item_sum_min、Item_sum_max 類的實(shí)例屬性 args 都繼承自父類 Item_sum，用于保存聚合函數(shù)參數(shù)，count() 支持多個(gè)參數(shù)，所以，參數(shù)的屬性名為 args 而不是 arg。

松散索引掃描小節(jié)，以 min() 為例介紹了松散索引掃描的執(zhí)行過程，執(zhí)行階段，分為兩步讀取分組最小值：讀取分組前綴，根據(jù)分組前綴讀取分組最小值。

兩種索引掃描怎么選？ 小節(jié)，介紹了使用松散索引掃描必須滿足的一系列條件。

當(dāng)松散索引掃描比緊湊索引掃描成本高時(shí)，min()、max() 會(huì)選擇用緊湊索引掃描，MySQL 為 count(distinct)、sum(distinct)、avg(distinct) 引入松散索引掃描的變種，順序讀取索引記錄（和緊湊索引掃描一樣）+ 松散索引掃描自帶的記錄去重功能，避免了使用臨時(shí)表對(duì)記錄去重。

還介紹了松散索引掃描比緊湊索引掃描的成本高，是因?yàn)榉纸M中記錄數(shù)量少時(shí)，兩次讀取存儲(chǔ)引擎數(shù)據(jù)增加的成本超過了跳著讀取索引記錄節(jié)省的成本。