MySQL GROUP BY 基本實現原理：鬆散索引掃描、緊湊索引掃描與臨時表三種模式詳解

MySQL GROUP BY 基本實現原理詳解

核心觀點

GROUP BY 實際上包含排序操作，與 ORDER BY 類似，故可利用索引優化。MySQL 有三種實現方式，前兩種利用索引，第三種退化為臨時表+排序。

方式一：鬆散索引掃描

條件：查詢欄位為索引前綴，且無 WHERE / JOIN / 子查詢干擾。

實驗環境

CREATE INDEX idx_gid_uid_gc ON group_message(group_id, user_id, gmt_create);
DROP INDEX idx_group_message_gid_uid ON group_message;

執行計劃分析

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

-- 關鍵輸出
Extra: Using where; Using index for group-by

原理

• 索引 (group_id, user_id, gmt_create) 已按分組鍵排序
• MySQL 直接跳躍讀取每組第一筆 (MAX) 或最後一筆 (MIN)
• 不掃描所有滿足條件的索引鍵，故稱「鬆散」
• 適用聚合函數：MIN()、MAX()、COUNT(*) (特定條件)

方式二：緊湊索引掃描

• 掃描所有滿足條件的索引項
• 適用於無法使用鬆散掃描但索引覆蓋查詢欄位的場景
• Extra 顯示：Using index (無 for group-by)

方式三：臨時表 + Filesort (退化模式)

• 無可用索引或查詢過於複雜 (含 JOIN、子查詢、非索引欄位分組)
• 建立內部臨時表 → 複製資料 → 排序 → 分組聚合
• Extra 顯示：Using temporary; Using filesort
• 效能最差，大數據量易導致磁碟臨時表

優化指南

驗證技巧

EXPLAIN SELECT ... GROUP BY ...;
-- 關注 Extra 欄位：
-- Using index for group-by  → 鬆散掃描 (最佳)
-- Using index               → 緊湊掃描 (良好)
-- Using temporary; Using filesort → 需優化 (最差)