MySQL GROUP BY 實現原理深度解析：鬆散索引掃描、緊湊索引掃描與臨時表回退

背景

GROUP BY 實質包含「排序 + 分組 + 聚合」。MySQL 優化器會嘗試利用索引避免額外排序與臨時表，分為三種執行策略，效能由高到低：

1. Loose Index Scan (鬆散索引掃描)
2. Tight Index Scan (緊湊索引掃描)
3. Temporary Table + Filesort (臨時表回退)

一、鬆散索引掃描

觸發條件 (必須同時滿足)

• GROUP BY 欄位為**同一複合索引的最左連續前綴** (如 idx(a,b,c) 群組 a,b 或 a)。
• 聚合函數**僅使用 MIN() 或 MAX()**。
• 查詢引用的其他索引欄位**必須為常數條件** (等值或 IN 列表)。
• 無 WHERE 條件，或條件僅涉及索引前綴常數。

執行計畫特徵

EXPLAIN SELECT user_id, MAX(gmt_create)
FROM group_message
WHERE group_id < 10
GROUP BY group_id, user_id;

-- Extra: Using where; Using index for group-by

Using index for group-by 即標識鬆散掃描。

原理圖解

索引 (group_id, user_id, gmt_create) 順序：
group_id=1, user_id=1, gmt_create=10:00  ← 讀取此行 (組 1 的 MAX)
group_id=1, user_id=1, gmt_create=10:05  ← 跳過
group_id=1, user_id=2, gmt_create=09:00  ← 讀取 (組 2 的 MAX)
group_id=2, user_id=1, gmt_create=11:00  ← 讀取 (組 3 的 MAX)
...
只需讀取每組的「第一筆」或「最後一筆」，跳過中間行，I/O 極少。

效能優勢

• 全索引掃描時：讀取鍵值數 = 分組數 ≪ 總行數。
• 帶範圍條件時：每組只定位第一筆滿足條件的鍵值。

二、緊湊索引掃描

觸發場景

• GROUP BY 欄位**不連續**或**非索引前綴** (如 GROUP BY user_id 但索引為 (group_id, user_id))，無法直接跳躍。
• 但 WHERE 提供了缺失前綴的**常數值** (如 WHERE group_id = 2)，補齊前綴 → 形成完整索引前綴 (2, user_id)。
• 聚合函數不限於 MIN/MAX (可用 COUNT, SUM 等)。

執行計畫特徵

EXPLAIN SELECT MAX(gmt_create)
FROM group_message
WHERE group_id = 2
GROUP BY user_id;

-- Extra: Using where; Using index
-- (無 Using index for group-by)

需掃描 group_id=2 範圍內**所有**索引鍵，再記憶體中分組聚合。

原理圖解

索引 (group_id, user_id, gmt_create)：
WHERE group_id=2 定位到範圍起始
掃描該範圍所有 user_id 的所有 gmt_create
在臨時記憶體結構中按 user_id 聚合 MAX(gmt_create)

三、臨時表 + Filesort 回退

觸發場景

• 無可用索引支援 GROUP BY 欄位順序。
• GROUP BY 欄位來自不同索引或非索引欄位。
• 使用 GROUP BY 欄位非索引前綴且無常數補齊 (如 WHERE group_id > 1 AND group_id < 10 GROUP BY user_id)。
• 使用 Hash 索引 (無序)。

執行計畫特徵

EXPLAIN SELECT MAX(gmt_create)
FROM group_message
WHERE group_id > 1 AND group_id < 10
GROUP BY user_id;

-- Extra: Using where; Using index; Using temporary; Using filesort

流程：索引範圍掃描 → 寫入臨時表 (記憶體/磁碟) → 排序 → 分組聚合。

原理圖解

1. 索引範圍掃描 group_id IN (2..9) 取出所有行
2. 寫入臨時表 (Memory -> MyISAM/InnoDB on disk)
3. 對臨時表按 user_id 排序
4. 掃描排序後臨時表執行分組聚合

優化器選擇邏輯

IF (滿足鬆散掃描條件) 
    USE Loose Index Scan
ELSE IF (可構建完整索引前綴 + 滿足緊湊掃描條件)
    USE Tight Index Scan
ELSE
    USE Temporary Table + Filesort

實戰優化清單

總結

MySQL GROUP BY 優化核心在於**索引欄位順序與常數條件的配合**。掌握三種策略觸發條件，針對性調整索引與 SQL 寫法，可將分組查詢效能提升數量級。