MySQL GROUP BY 優化全解析：鬆散索引掃描與緊湊索引掃描

優化原理概覽

執行 GROUP BY 的通用方式：全表掃描 → 創建臨時表 → 排序分組 → 應用聚合函數。MySQL 在滿足特定條件時可直接利用索引順序，避免臨時表，EXPLAIN 的 Extra 列顯示 Using index for group-by。

一、鬆散索引掃描

利用索引跳躍特性，僅讀取每組第一條記錄，極大減少訪問行數。

滿足條件（全部必須符合）

1. 單表查詢
2. GROUP BY 使用索引最左前綴（可為前 N 列）
3. 聚合函數僅允許 MIN()、MAX()，且指向同一列
4. 無 WHERE 條件，或 WHERE 僅涉及索引前綴列的常量等值比較

適用示例（索引 idx(c1,c2,c3)）

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < 100 GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > 100 GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < 100 GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = 100 GROUP BY c1, c2;

不適用反例

-- 1. 使用 SUM 等其他聚合
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

-- 2. GROUP BY 不符合最左前綴
SELECT c1, c2 FROM t1 GROUP BY c2, c3;

-- 3. 引用索引中間列無常量等值條件
SELECT c1, c3 FROM t1 GROUP BY c1, c2;

二、緊湊索引掃描

當不滿足鬆散掃描條件時，若 WHERE 有範圍條件，僅掃描滿足範圍的索引鍵；否則進行全索引掃描。雖仍需讀取所有符合條件的行，但避免臨時表創建與排序開銷。

典型適用場景

-- 1. GROUP BY 有漏洞，但 WHERE 以常量補齊（c2='a' 固定了 c2）
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

-- 2. GROUP BY 非最左前綴，但 WHERE 提供前綴常量（c1='a'）
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

三、實戰優化清單

1. 索引設計：GROUP BY 列建立聯合索引，順序匹配 GROUP BY 順序
2. 聚合函數選擇：優先用 MIN/MAX 觸發鬆散掃描
3. WHERE 下推：在索引前綴列加常量等值條件，啟用緊湊掃描
4. 避免臨時表：EXPLAIN 出現 Using temporary、Using filesort 需優化
5. 大數據分頁：GROUP BY + LIMIT 結合覆蓋索引

四、版本差異提示

• 5.6+：優化器成本模型更精準，鬆散掃描觸發更積極
• 8.0：GROUP BY 不再隱式排序，若需有序需顯式 ORDER BY
• MariaDB 10.2+：支援 WINDOW FUNCTION 替代部分 GROUP BY 場景