MySQL GROUP BY 實現原理深度解析：鬆散索引掃描與 Using index for group-by

核心認知

GROUP BY 本質 = 排序 + 分組 + 聚合。與 ORDER BY 同樣可利用索引避免文件排序。MySQL 5.6+ 引入 鬆散索引掃描，僅掃描索引關鍵節點即可完成分組，大幅降低 I/O。

三種實現模式

1. 鬆散索引掃描：完全走索引，不回表，Extra 顯示 Using index for group-by
2. 緊湊索引掃描：走索引但需回表或掃描更多索引鍵
3. 臨時表 + 文件排序：無可用索引，Extra 顯示 Using temporary; Using filesort

鬆散索引掃描實戰演示

表結構與索引

CREATE TABLE group_message (
    id INT PRIMARY KEY AUTO_INCREMENT,
    group_id INT,
    user_id INT,
    gmt_create DATETIME,
    ...
);
-- 組合索引順序關鍵：分組列在前，聚合列在後
CREATE INDEX idx_gid_uid_gc ON group_message(group_id, user_id, gmt_create);

查詢與執行計劃

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

輸出關鍵行：

type: range
key: idx_gid_uid_gc
Extra: Using where; Using index for group-by

原理解析

• 索引順序 (group_id, user_id, gmt_create) 完美匹配 WHERE + GROUP BY + MAX()
• 優化器識別出可按 group_id, user_id 直接跳躍到每組最後一條記錄（MAX gmt_create），無需掃描組內所有行
• Using index for group-by 確認走鬆散掃描

觸發條件總結

1. 查詢為單表
2. GROUP BY 列構成索引最左前綴
3. 聚合函數為 MIN()/MAX()，且作用於索引中緊隨分組列之後的列
4. 無 DISTINCT、無 ORDER BY 衝突

優化建議

• 組合索引設計遵循 WHERE 篩選列 → GROUP BY 列 → 聚合列 順序
• 避免 SELECT *，只取需求列，增加覆蓋索引命中率
• 大表分組考慮分區表或匯總表預聚合