MySQL GROUP BY 優化深度解析：鬆散索引掃描 vs 緊湊索引掃描 vs 臨時表

執行原理

優化器直接在索引樹上「跳躍」：定位到每個 GROUP 的第一筆滿足條件的記錄，讀取所需欄位後直接跳到下一組的第一筆，完全跳過組內其餘記錄。讀取的索引鍵數量 ≈ 分組數量 ≪ 總行數。

EXPLAIN 特徵

Extra: Using index for group-by

實戰範例

-- 索引：idx_gid_uid_gc (group_id, user_id, gmt_create)
EXPLAIN SELECT group_id, user_id, MAX(gmt_create)
FROM group_message
WHERE group_id BETWEEN 1 AND 4
GROUP BY group_id, user_id;
-- 滿足條件：GROUP BY 為索引前綴，聚合為 MAX()，WHERE 為範圍但 GROUP BY 欄位在索引最左
-- 實測 Extra: Using index for group-by

二、緊湊索引掃描：全掃描索引前綴再分組

觸發場景：鬆散掃描條件不滿足，但可構造完整索引前綴

• GROUP BY 欄位不連續或非最左，但 WHERE 提供常數等值填補缺口
• 聚合函數不限於 MIN/MAX

執行原理

優化器掃描所有滿足 WHERE 條件的索引記錄（而非跳躍），在記憶體中完成分組聚合。讀取鍵值數量 = 條件過濾後的行數。

EXPLAIN 特徵

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

實戰範例

-- 索引：(group_id, user_id, gmt_create)
-- GROUP BY user_id, gmt_create 缺失 group_id（索引最左）
-- WHERE group_id = 2 提供常數填補 → 構成完整前綴 (2, user_id, gmt_create)
EXPLAIN SELECT MAX(gmt_create)
FROM group_message
WHERE group_id = 2
GROUP BY user_id, gmt_create;
-- Extra: Using where; Using index

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

觸發場景

• 無滿足條件的索引
• GROUP BY 欄位來自不同索引或無索引
• WHERE 條件為範圍且無法構造索引前綴（如 group_id > 1 AND group_id < 10 配合 GROUP BY user_id）
• 使用 SUM()、COUNT()、AVG() 等非 MIN/MAX 聚合

執行流程

1. 全表/範圍掃描讀取滿足條件的行
2. 寫入內部臨時表（記憶體 → 超限轉 MyISAM/InnoDB 磁碟表）
3. 對臨時表執行 Filesort 排序
4. 掃描排序後臨時表輸出分組結果

EXPLAIN 特徵

Extra: Using where; Using temporary; Using filesort

效能殺手

• 磁碟臨時表 I/O 延遲（Created_tmp_disk_tables 計數器飆升）
• sort_buffer_size 不足導致多輪合併排序
• 大結果集記憶體壓力觸發 OOM 或 Swap

四、優化實戰決策樹

-- 1. 確認索引存在性
SHOW INDEX FROM group_message;

-- 2. 觀察 EXPLAIN Extra 欄位
-- 目標：Using index for group-by  >  Using index  >  Using temporary; Using filesort

-- 3. SQL 改寫策略
-- (A) 調整 GROUP BY 欄位順序匹配索引最左前綴
-- (B) 新增複合索引覆蓋 GROUP BY + WHERE 常數欄位
-- (C) 將範圍條件轉為等值（如業務允許分批處理）
-- (D) 強制索引：FORCE INDEX(idx_xxx) 避免優化器誤判

-- 4. 參數調優（臨時表回退場景）
SET SESSION sort_buffer_size = 16M;        -- 線程級，謹慎調大
SET SESSION tmp_table_size = 256M;         -- 內存臨時表上限
SET SESSION max_heap_table_size = 256M;    -- 配合 tmp_table_size

五、實測數據對比（參考環境：100 萬行，索引 (gid, uid, gc)）

六、黃金法則總結

1. 索引設計先行： 複合索引欄位順序 = WHERE 常數等值欄位 + GROUP BY 欄位 + 聚合函數欄位
2. 聚合函數選擇： 能用 MIN/MAX 就不用 SUM/COUNT/AVG（前者可走鬆散掃描）
3. 常數填補缺口： WHERE 提供常數等值可將「不連續 GROUP BY」升級為緊湊掃描
4. 參數托底： sort_buffer_size、tmp_table_size、max_heap_table_size 三聯調，防磁碟臨時表
5. 監控指標： Created_tmp_disk_tables / Created_tmp_tables < 25%，Sort_merge_passes = 0

掌握三種掃描機制的邊界條件，將 GROUP BY 優化從「玄學調參」轉化為「索引設計與 SQL 改寫的確定性工程」。