MySQL GROUP BY 實現原理三大機制圖解：鬆散、緊湊索引掃描與臨時表回退

GROUP BY 本質上是「排序後分組」，MySQL 優化器提供三種執行路徑，效能呈指數級差異。本文通過實測 EXPLAIN、執行計劃圖解與真實數據表，完整還原三大機制的觸發邊界與優化實戰。

測試環境與基準表

CREATE TABLE `group_message` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `abc` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_gid_uid_gc` (`group_id`,`user_id`,`gmt_create`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- 約 26 筆測試資料，足以觀察執行計劃差異

機制一：鬆散索引掃描 —— 跳躍式極致優化

觸發 SQL

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

執行計劃

id: 1
select_type: SIMPLE
table: group_message
type: range
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 8
ref: NULL
rows: 4
Extra: Using where; Using index for group-by

執行流程圖解

索引樹 (group_id, user_id, gmt_create)
│
├─ group_id=1 → 定位第一筆 → 讀取 user_id, MAX(gmt_create) → 跳躍
├─ group_id=2 → 定位第一筆 → 讀取 ... → 跳躍
├─ group_id=3 → ...
└─ group_id=9 → 定位第一筆 → 讀取 ... → 結束
只訪問 4 個索引鍵（對應 4 個分組），完全跳過組內其餘行

核心條件回顧

• GROUP BY 為索引最左連續前綴
• 聚合僅 MIN/MAX
• WHERE 非 GROUP BY 索引欄位為常數/範圍

機制二：緊湊索引掃描 —— 全掃描前綴再分組

觸發 SQL

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

執行計劃

id: 1
select_type: SIMPLE
table: group_message
type: ref
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: const
rows: 4
Extra: Using where; Using index  (無 Using index for group-by)

執行流程圖解

WHERE group_id = 2 定位索引區間
│
├─ 掃描 group_id=2 所有葉子節點 (user_id=1, gmt_create=...)
├─ 掃描 group_id=2 所有葉子節點 (user_id=2, gmt_create=...)
├─ 掃描 group_id=2 所有葉子節點 (user_id=3, gmt_create=...)
└─ 在記憶體中按 user_id 分組、計算 MAX(gmt_create)
讀取鍵值數 = group_id=2 的總行數（此例 4 行）

觸發邊界：常數填補缺口

• 索引 (group_id, user_id, gmt_create)
• GROUP BY user_id, gmt_create 缺失 group_id（最左）
• WHERE group_id = 2 提供常數 → 構成完整前綴 (2, user_id, gmt_create)
• 優化器無法跳躍（因 GROUP BY 非最左），退而掃描完整前綴區間

緊湊掃描適用範例

-- GROUP BY 有缺口，WHERE 常數填補
SELECT group_id, gmt_create FROM group_message WHERE user_id = 1 GROUP BY group_id, gmt_create;
SELECT group_id, gmt_create FROM group_message WHERE group_id = 1 GROUP BY user_id, gmt_create;

-- 無法使用緊湊掃描（缺口非常數、或組合不符最左前綴）
SELECT group_id, gmt_create FROM group_message WHERE user_id = 1 GROUP BY gmt_create;
SELECT gmt_create FROM group_message WHERE group_id = 1 GROUP BY gmt_create;

機制三：臨時表 + Filesort —— 無索引可用的最後防線

觸發 SQL

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

執行計劃

id: 1
select_type: SIMPLE
table: group_message
type: range
possible_keys: idx_gid_uid_gc
key: idx_gid_uid_gc
key_len: 4
ref: NULL
rows: 32
Extra: Using where; Using index; Using temporary; Using filesort

執行流程圖解

1. 索引範圍掃描 group_id IN (2..9) → 讀取 32 行
2. 寫入內部臨時表 (user_id, MAX(gmt_create))
3. 臨時表超過 tmp_table_size → 轉磁碟 MyISAM 表
4. Filesort 對臨時表按 user_id 排序
5. 掃描排序後臨時表輸出分組結果
代價：磁碟 I/O + CPU 排序 + 臨時表管理開銷

觸發根因

• WHERE group_id BETWEEN ... 為範圍，非常數
• GROUP BY user_id 非索引最左（索引最左為 group_id）
• 無常數填補缺口 → 無法構造完整索引前綴 → 退回臨時表

優化器決策邏輯鏈

優化器嘗試順序：
1. 鬆散索引掃描（最優）→ 條件嚴格
2. 緊湊索引掃描（次優）→ 條件較寬
3. 臨時表 + Filesort（保底）→ 無索引可用

實戰優化對比表

黃金優化策略

1. 索引先行： 複合索引 = WHERE 常數等值 + GROUP BY + 聚合欄位
2. 常數填補： 將範圍條件拆分為等值 UNION ALL（如 group_id IN (2,3,4)）
3. 聚合選擇： 能用 MIN/MAX 就不用 SUM/COUNT
4. 參數托底： sort_buffer_size=16M、tmp_table_size=256M、max_heap_table_size=256M
5. 監控紅線： Created_tmp_disk_tables / Created_tmp_tables > 25% 或 Sort_merge_passes > 0 立即介入

從「跳躍讀取 4 鍵」到「掃描 32 行 + 磁碟排序」，三大機制的效能懸殊，印證了「索引設計決定查詢上限，SQL 改寫決定查詢下限」的鐵律。