本文是 group by 實(shí)現(xiàn)過程分析的第 2 篇文章，第 1 篇是 ??MySQL 怎么用索引實(shí)現(xiàn) group by??? <- 點(diǎn)擊閱讀

了解 MySQL 內(nèi)部臨時(shí)表中包含什么字段?為哪些字段建立索引?有助于理解使用臨時(shí)表和文件排序?qū)崿F(xiàn) group by，所以之前寫了一篇關(guān)于內(nèi)部臨時(shí)表的文章 ??你好奇過 MySQL 內(nèi)部臨時(shí)表存了什么嗎??? <- 點(diǎn)擊閱讀

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

1、 概述

查看 group by 語句的執(zhí)行計(jì)劃，在輸出結(jié)果的 Extra 列中，跟 group by 實(shí)現(xiàn)方式有關(guān)的信息有 4 種：

① Using index for group-by，表示使用松散索引掃描減少了需要掃描的記錄數(shù)量，節(jié)省了執(zhí)行時(shí)間。

② Using index for group-by(scanning) ，在松散索引掃描流程中使用順序掃描邏輯，避免了使用臨時(shí)表對記錄去重，這種方式是順序松散索引掃描(這名字不是來自于官方，是我根據(jù)這種實(shí)現(xiàn)方式的特點(diǎn)取的名字)。

③ Using temporary; Using filesort，表示使用臨時(shí)表 + 文件排序，先使用臨時(shí)表存儲(chǔ)分組數(shù)據(jù)，再對臨時(shí)表中記錄進(jìn)行排序。

④ Using filesort，表示只使用文件排序，先對 from 子句的表中記錄進(jìn)行排序，再對排好序的記錄進(jìn)行聚合操作。

還有一種實(shí)現(xiàn)方式是緊湊索引掃描，在輸出結(jié)果的 Extra 列中找不到它的蛛絲馬跡。

如果 Extra 列中沒有出現(xiàn)上面 4 種信息，并且 key 列的值不為 NULL，表示實(shí)現(xiàn) group by 時(shí)也用到了索引，這種實(shí)現(xiàn)方式就是緊湊索引掃描。

松散索引掃描、順序松散索引掃描、緊湊索引掃描 3 種實(shí)現(xiàn)方式，在這篇文章中都已經(jīng)有過介紹了：??MySQL 怎么用索引實(shí)現(xiàn) group by? ??<- 點(diǎn)擊閱讀

接下來，我們一起來看看 ③ ④ 兩種方式(臨時(shí)表 + 文件排序、文件排序)是怎么實(shí)現(xiàn) group by 的。

2、 準(zhǔn)備工作

本文示例 SQL 使用的表結(jié)構(gòu)如下：

CREATE TABLE `t_group_by` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `e1` enum('北京','上海','廣州','深圳','天津','杭州','成都','重慶','蘇州','南京','哈爾濱','沈陽','長春','廈門','福州','南昌','泉州','德清','長沙','武漢') DEFAULT '北京',
  `i1` int(10) unsigned DEFAULT '0',
  `c1` char(11) DEFAULT '',
  `d1` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_e1` (`e1`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、 臨時(shí)表 + 文件排序

在研究使用臨時(shí)表實(shí)現(xiàn) group by 之前，我一直有個(gè)疑問：使用了臨時(shí)表，為什么還要再進(jìn)行文件排序呢?

之所以有這樣的想法，是因?yàn)槲抑琅R時(shí)表中會(huì)為 group by 字段建立索引，既然建立了索引，那么臨時(shí)表中的記錄就已經(jīng)是排好序的了。

問題出現(xiàn)在我想當(dāng)然的認(rèn)為 group by 上建立的索引是 B-TREE 索引，而完全忽略了另一種索引，就是 HASH 索引。

HASH 索引中的記錄并不是排好序的，而包含 group by 的查詢語句，隱含了對查詢結(jié)果按照 group by 字段排序的邏輯，所以還需要使用文件排序。

臨時(shí)表中為 group by 字段建立索引的目的，是為了更快的找到要更新的記錄，而不是為了讓記錄有序。

因?yàn)榘?group by 的查詢語句中，一般都會(huì)有聚合函數(shù)，并且臨時(shí)表中保存的是聚合函數(shù)的計(jì)算結(jié)果，每從 from 子句的表中讀取一條記錄，進(jìn)行聚合函數(shù)計(jì)算之后，都會(huì)用 group by 字段作為條件，把聚合函數(shù)計(jì)算結(jié)果更新到臨時(shí)表中。

既然建立索引的目的是用于查找，HASH 索引的查找速度顯然要比 B-TREE 索引快，使用 HASH 索引也就成為了這種場景下最合適的選擇。

使用臨時(shí)表 + 文件排序?qū)崿F(xiàn) group by，臨時(shí)表和文件排序的用途總結(jié)如下：

• 臨時(shí)表，保存 group by 分組的結(jié)果記錄。
• 文件排序，所有分組的結(jié)果記錄都寫入臨時(shí)表之后，把臨時(shí)表中的記錄按照 group by 字段值排序。

接下來，我們用一個(gè)具體例子來分析使用臨時(shí)表 + 文件排序?qū)崿F(xiàn) group by 的過程，示例 SQL 如下：

select
    e1, count(i1) as sum_i1
from t_group_by
where d1 > 5452415
group by e1

示例 SQL 執(zhí)行過程中和 group by 相關(guān)的關(guān)鍵步驟如下：

詞法分析 & 語法分析階段 ，count(i1) as sum_i1 解析為 Item_sum_count 類的實(shí)例，其中 2 個(gè)實(shí)例屬性如下：

• args，count() 函數(shù)可以對多個(gè)字段聯(lián)合計(jì)數(shù)，args[0] ~ args[N] 保存著 count() 函數(shù)參數(shù)的字段引用。
• 示例 SQL 中，args[0] 保存著對 i1 字段的 Item_field 類實(shí)例的引用，此時(shí)，Item_field 類實(shí)例還沒有關(guān)聯(lián)到i1 字段的 Field 類實(shí)例。
• count，保存分組計(jì)數(shù)。e1 字段每一個(gè)不同的值就是一個(gè)分組，count 是分組中 i1 字段值不為 NULL 的記錄數(shù)量。

Item_field 未關(guān)聯(lián) Field

查詢準(zhǔn)備階段

第 1 步，i1 字段的 Item_field 類實(shí)例關(guān)聯(lián) t_group_by 表中 i1 字段的 Field 類實(shí)例。

Item_field 已關(guān)聯(lián) Field

第 2 步，創(chuàng)建臨時(shí)表。

臨時(shí)表包含 e1、sum_i1 字段，sum_i1 字段值是分組計(jì)數(shù)，也就是 Item_sum_count 類實(shí)例的 count 屬性的值。

臨時(shí)表中還會(huì)為 e1 字段建立唯一索引，索引方式(HASH 或 B-TREE)是臨時(shí)表存儲(chǔ)引擎默認(rèn)的。

對于 MEMORY 存儲(chǔ)引擎，索引方式默認(rèn)為HASH。

對于 MyISAM、InnoDB 存儲(chǔ)引擎，索引方式默認(rèn)為 B-TREE。

執(zhí)行階段

臨時(shí)表 + 文件排序執(zhí)行過程

第 1 步，讀取符合 where 條件的記錄。

server 層從存儲(chǔ)引擎讀取一條記錄，并進(jìn)行 where 條件判斷。

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

如果讀取出來的記錄符合條件，進(jìn)入第 2 步。

第 2 步，分組計(jì)數(shù)。

對 i1 字段值不為 NULL 的記錄進(jìn)行分組計(jì)數(shù)。

如果當(dāng)前讀取記錄的 e1 字段值和前一條記錄的 e1 字段值不一樣，說明要開始新分組。初始化分組計(jì)數(shù)，Item_sum_count 類的實(shí)例屬性 count 設(shè)置為 1。

如果當(dāng)前讀取記錄的 e1 字段值和前一條記錄的 e1 字段值一樣，說明還是同一個(gè)分組。增加分組計(jì)數(shù)，Item_sum_count 類的實(shí)例屬性 count 加 1。

第 3 步，更新分組計(jì)數(shù)到臨時(shí)表。

以 e1 字段值作為 where 條件，把 Item_sum_count 類的實(shí)例屬性 count 的值更新到臨時(shí)表中。

第 1 ~ 3 步是循環(huán)執(zhí)行的過程，直到已經(jīng)從存儲(chǔ)引擎讀取到所有符合 where 條件的記錄，這個(gè)循環(huán)執(zhí)行的過程才會(huì)結(jié)束。

第 4 步，對臨時(shí)表中的記錄進(jìn)行排序。

從存儲(chǔ)引擎讀取符合 where 條件的所有記錄之后，把數(shù)據(jù)發(fā)送給客戶端之前，需要按照臨時(shí)表中 e1 字段值對臨時(shí)表中的記錄進(jìn)行排序。

經(jīng)過上面的執(zhí)行過程分析之后，相信大家對于使用臨時(shí)表 + 文件排序?qū)崿F(xiàn) group by 的執(zhí)行過程能有更清晰的認(rèn)識(shí)了。

4、 只使用文件排序

使用臨時(shí)表 + 文件排序、只使用文件排序，這兩種方式中雖然都包含文件排序，但是它們的含義是不一樣的。

臨時(shí)表 + 文件排序，這里的文件排序，表示對臨時(shí)表中的記錄進(jìn)行排序。

只使用文件排序，這里的文件排序，表示對 from 子句的表中記錄進(jìn)行排序。例如 select e1, count(i1) from t_group_by group by e1 中的 t_group by 表。

為什么對 from 子句的表中記錄排序之后，group by 操作就不需要使用臨時(shí)表了?

要回答這個(gè)問題，我們先來看看包含 group by 的查詢語句通常要實(shí)現(xiàn)的兩個(gè)邏輯：分組、聚合。

分組，就是把 group by 字段值一樣的記錄緊挨著放到一起，這樣就能知道誰是分組的第一條記錄，誰是分組的最后一條記，判斷上一個(gè)分組結(jié)束和新分組開始就非常簡單了。

排好序的記錄方便判斷分組開始和結(jié)束

聚合，對分組中的記錄進(jìn)行計(jì)數(shù)、求和、求平均值等各種操作。

如果能夠使用索引(僅指 B-TREE 索引)實(shí)現(xiàn) group by，索引中的記錄已經(jīng)是排好序的了，實(shí)際上相當(dāng)于已經(jīng)分好組了，可以直接進(jìn)行聚合操作，而不需要借助臨時(shí)表進(jìn)行分組。

說到這里，關(guān)于問題的答案已經(jīng)呼之欲出了。

想必大家都已經(jīng)想到了，對 from 子句的表中記錄按照 group by 字段值排序之后，有點(diǎn)類似于為 group by 字段建立了索引，記錄排好序之后也就分好組了，可以直接進(jìn)行聚合，而不需要再借助臨時(shí)表進(jìn)行分組。

對于上面關(guān)于分組和聚合的描述，大家可能會(huì)有個(gè)疑問：想要聚合就一定要先進(jìn)行分組嗎?

這個(gè)當(dāng)然不是，從實(shí)現(xiàn)角度來說，不分組也可以聚合。但是，如果聚合之前不先分組，挨著的記錄可能屬于不同的分組，執(zhí)行過程中就需要記錄多個(gè)分組的聚合結(jié)果。

分組越多，用于記錄分組聚合結(jié)果消耗的內(nèi)存就越多，這顯示不是 MySQL 能夠接受的。所以，在 MySQL 中，要聚合，就要先分組。

接下來，我們一起來看看只使用文件排序?qū)崿F(xiàn) group by 的過程吧。

以一個(gè) SQL 為例來分析執(zhí)行過程，示例如下：

select
    e1, count(i1) as sum_i1
from t_group_by
where d1 > 5452415
group by e1

詞法分析 & 語法分析階段、查詢準(zhǔn)備階段和使用臨時(shí)表 + 文件排序方式一樣，同一篇文章中就不再贅述了。在此，僅對執(zhí)行階段進(jìn)行分析。

只使用文件排序的執(zhí)行過程

第 1 步，讀取 t_group_by 表中所有符合條件的記錄并進(jìn)行排序。

第 2 步，讀取一條已經(jīng)排好序的記錄，并判斷上一個(gè)分組是否結(jié)束。

如果當(dāng)前讀取記錄的 e1 字段值和前一條記錄的 e1 字段值不一樣，說明分組已經(jīng)發(fā)生變化，需要結(jié)束老分組，開始新分組，進(jìn)入第 3 步。

如果當(dāng)前讀取記錄的 e1 字段值和前一條記錄的 e1 字段值一樣，說明還是同一個(gè)分組，進(jìn)入第 4 步。

第 3 步，結(jié)束老分組，開啟新分組。

結(jié)束老分組，把 e1 字段值和分組計(jì)數(shù)發(fā)送給客戶端。

開啟新分組，如果 i1 字段值不為 NULL，把 Item_sum_count 類的實(shí)例屬性 count 設(shè)置為 1，否則設(shè)置為 0。

然后回到第 2 步，讀取下一條記錄。

第 4 步，更新當(dāng)前分組計(jì)數(shù)。

如果 i1 字段值不為 NULL，Item_sum_count 類的實(shí)例屬性 count 加 1，然后進(jìn)入第 1 步繼續(xù)執(zhí)行。

然后回到第 2 步，讀取下一條記錄。

第 2 ~ 4 步是循環(huán)執(zhí)行的過程，直到讀取完符合 where 條件的所有記錄、以及把所有分組數(shù)據(jù)發(fā)送給客戶端之后結(jié)束。

看過使用索引實(shí)現(xiàn) group by 那篇文章的小伙伴應(yīng)該能發(fā)現(xiàn)，上面的執(zhí)行過程中的第 2 ~ 4 步和緊湊索引掃描方式是一樣的。

5、 總結(jié)

第 3 小節(jié)，以一個(gè)具體 SQL 為例，分析了 group by 的具體執(zhí)行過程。

臨時(shí)表中會(huì)寫入分組數(shù)據(jù)，并且會(huì)為 group by 字段建立 HASH 索引。因?yàn)?HASH 索引中記錄不是有序的，所以，寫入所有分組數(shù)據(jù)到臨時(shí)表之后，需要對臨時(shí)表中的記錄按照 group by 字段進(jìn)行排序。

第 4 小節(jié)，介紹了只使用文件排序?qū)崿F(xiàn) group by 的過程。這種方式的執(zhí)行過程和緊湊索引掃描類似。

不同之處在于，多了一步對 from 子句的表中符合 where 條件的記錄進(jìn)行排序。排好序之后的執(zhí)行過程就和緊湊索引掃描一樣了。

本文轉(zhuǎn)載自微信公眾號(hào)「一樹一溪」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系一樹一溪公眾號(hào)。