本文是 group by 實現過程分析的第 2 篇文章，第 1 篇是 MySQL 怎麼用索引實現 group by？

了解 MySQL 内部臨時表中包含什麼字段？為哪些字段建立索引？有助于理解使用臨時表和文件排序實現 group by，所以之前寫了一篇關于内部臨時表的文章 你好奇過 MySQL 内部臨時表存了什麼嗎？

本文内容基于 MySQL 5.7.35 源碼。

1. 概述

查看 group by 語句的執行計劃，在輸出結果的 Extra 列中，跟 group by 實現方式有關的信息有 4 種：

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

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

③ Using temporary; Using filesort，表示使用臨時表 文件排序，先使用臨時表存儲分組數據，再對臨時表中記錄進行排序。

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

還有一種實現方式是緊湊索引掃描，在輸出結果的 Extra 列中找不到它的蛛絲馬迹。

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

松散索引掃描、順序松散索引掃描、緊湊索引掃描 3 種實現方式，在這篇文章中都已經有過介紹了：MySQL 怎麼用索引實現 group by？ <- 點擊閱讀

接下來，我們一起來看看 ③ ④ 兩種方式（臨時表 文件排序、文件排序）是怎麼實現 group by 的。

2. 準備工作

本文示例 SQL 使用的表結構如下：

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. 臨時表 文件排序

在研究使用臨時表實現 group by 之前，我一直有個疑問：使用了臨時表，為什麼還要再進行文件排序呢？

之所以有這樣的想法，是因為我知道臨時表中會為 group by 字段建立索引，既然建立了索引，那麼臨時表中的記錄就已經是排好序的了。

問題出現在我想當然的認為 group by 上建立的索引是 B-TREE 索引，而完全忽略了另一種索引，就是 HASH 索引。

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

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

因為包含 group by 的查詢語句中，一般都會有聚合函數，并且臨時表中保存的是聚合函數的計算結果，每從 from 子句的表中讀取一條記錄，進行聚合函數計算之後，都會用 group by 字段作為條件，把聚合函數計算結果更新到臨時表中。

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

使用臨時表 文件排序實現 group by，臨時表和文件排序的用途總結如下：

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

接下來，我們用一個具體例子來分析使用臨時表 文件排序實現 group by 的過程，示例 SQL 如下：

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

示例 SQL 執行過程中和 group by 相關的關鍵步驟如下：

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

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

Item_field 未關聯 Field

查詢準備階段

第 1 步，i1 字段的 Item_field 類實例關聯 t_group_by 表中 i1 字段的 Field 類實例。

Item_field 已關聯 Field

第 2 步，創建臨時表。

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

臨時表中還會為 e1 字段建立唯一索引，索引方式（HASH 或 B-TREE）是臨時表存儲引擎默認的。

對于 MEMORY 存儲引擎，索引方式默認為HASH。對于 MyISAM、InnoDB 存儲引擎，索引方式默認為 B-TREE。

執行階段

臨時表 文件排序執行過程

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

server 層從存儲引擎讀取一條記錄，并進行 where 條件判斷。

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

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

第 2 步，分組計數。

對 i1 字段值不為 NULL 的記錄進行分組計數。

如果當前讀取記錄的 e1 字段值和前一條記錄的 e1 字段值不一樣，說明要開始新分組。初始化分組計數，Item_sum_count 類的實例屬性 count 設置為 1。

如果當前讀取記錄的 e1 字段值和前一條記錄的 e1 字段值一樣，說明還是同一個分組。增加分組計數，Item_sum_count 類的實例屬性 count 加 1。

第 3 步，更新分組計數到臨時表。

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

第 1 ~ 3 步是循環執行的過程，直到已經從存儲引擎讀取到所有符合 where 條件的記錄，這個循環執行的過程才會結束。

第 4 步，對臨時表中的記錄進行排序。

從存儲引擎讀取符合 where 條件的所有記錄之後，把數據發送給客戶端之前，需要按照臨時表中 e1 字段值對臨時表中的記錄進行排序。

經過上面的執行過程分析之後，相信大家對于使用臨時表 文件排序實現 group by 的執行過程能有更清晰的認識了。

4. 隻使用文件排序

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

臨時表 文件排序，這裡的文件排序，表示對臨時表中的記錄進行排序。

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

為什麼對 from 子句的表中記錄排序之後，group by 操作就不需要使用臨時表了？

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

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

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

聚合，對分組中的記錄進行計數、求和、求平均值等各種操作。

如果能夠使用索引（僅指 B-TREE 索引）實現 group by，索引中的記錄已經是排好序的了，實際上相當于已經分好組了，可以直接進行聚合操作，而不需要借助臨時表進行分組。

說到這裡，關于問題的答案已經呼之欲出了。

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

對于上面關于分組和聚合的描述，大家可能會有個疑問：想要聚合就一定要先進行分組嗎？

這個當然不是，從實現角度來說，不分組也可以聚合。但是，如果聚合之前不先分組，挨着的記錄可能屬于不同的分組，執行過程中就需要記錄多個分組的聚合結果。

分組越多，用于記錄分組聚合結果消耗的内存就越多，這顯示不是 MySQL 能夠接受的。所以，在 MySQL 中，要聚合，就要先分組。

接下來，我們一起來看看隻使用文件排序實現 group by 的過程吧。

以一個 SQL 為例來分析執行過程，示例如下：

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

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

隻使用文件排序的執行過程

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

第 2 步，讀取一條已經排好序的記錄，并判斷上一個分組是否結束。

如果當前讀取記錄的 e1 字段值和前一條記錄的 e1 字段值不一樣，說明分組已經發生變化，需要結束老分組，開始新分組，進入第 3 步。

如果當前讀取記錄的 e1 字段值和前一條記錄的 e1 字段值一樣，說明還是同一個分組，進入第 4 步。

第 3 步，結束老分組，開啟新分組。

結束老分組，把 e1 字段值和分組計數發送給客戶端。

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

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

第 4 步，更新當前分組計數。

如果 i1 字段值不為 NULL，Item_sum_count 類的實例屬性 count 加 1，然後進入第 1 步繼續執行。

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

第 2 ~ 4 步是循環執行的過程，直到讀取完符合 where 條件的所有記錄、以及把所有分組數據發送給客戶端之後結束。

看過使用索引實現 group by 那篇文章的小夥伴應該能發現，上面的執行過程中的第 2 ~ 4 步和緊湊索引掃描方式是一樣的。

5. 總結

第 3 小節，以一個具體 SQL 為例，分析了 group by 的具體執行過程。

臨時表中會寫入分組數據，并且會為 group by 字段建立 HASH 索引。因為 HASH 索引中記錄不是有序的，所以，寫入所有分組數據到臨時表之後，需要對臨時表中的記錄按照 group by 字段進行排序。

第 4 小節，介紹了隻使用文件排序實現 group by 的過程。這種方式的執行過程和緊湊索引掃描類似。

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

以上就是本文的全部内容了，如果本文對你有所幫助，還請幫忙 轉發、點贊，謝謝 ^_^

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部