GROUP BY 默認隱式排序，大數據量下排序開銷巨大。MySQL 5.7+ 引入 Loose Index Scan（鬆散索引掃描）與 Tight Index Scan（緊湊索引掃描）兩種無臨時表優化路徑。本文系統梳理觸發條件、EXPLAIN 識別、實戰 SQL 改寫與避坑指南。

默認行為與抑制排序

-- 隱式排序（等同 ORDER BY col1, col2...）
SELECT a, COUNT(*) FROM t GROUP BY a, b;

-- 顯式禁止排序（MySQL 特有）
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

執行路徑三層次

1. 鬆散索引掃描：直接跳躍索引葉子節點，零臨時表、零排序，最快
2. 緊湊索引掃描：掃描符合 WHERE 條件的索引範圍，無臨時表但需掃描，次之
3. 臨時表+文件排序：全表掃描建臨時表排序，最慢

鬆散索引掃描：觸發條件嚴格

硬性條件（缺一不可）

1. 單表查詢
2. GROUP BY 使用索引最左前綴，且僅包含前綴列
3. 聚合函數僅允許 MIN()/MAX()，且作用於同一列（該列需在索引中）
4. 索引中GROUP BY 之後的列必須為常量條件（WHERE col = const）
5. 不支援前綴索引（如 INDEX(name(10))）

可觸發示例（索引 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;

不可觸發反例

-- 聚合函數非 MIN/MAX
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

-- GROUP BY 非最左前綴
SELECT c1, c2 FROM t1 GROUP BY c2, c3;

-- 引用 GROUP BY 後的索引列但無常量條件
SELECT c1, c3 FROM t1 GROUP BY c1, c2;  -- c3 在 c2 後，無 c2=const

特例：DISTINCT 聚合可觸發

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

緊湊索引掃描：條件放寬

當鬆散掃描條件不滿足時，若 WHERE 條件提供常量填補索引空隙，可退化為緊湊掃描：

案例 1：GROUP BY 有空隙但 WHERE 填補

-- 索引 idx(c1,c2,c3)
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
-- c2 由 WHERE 常量填補，滿足緊湊掃描

案例 2：GROUP BY 非最左但 WHERE 提供最左常量

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
-- c1 由 WHERE 常量固定，從 c2 開始掃描

EXPLAIN 識別三種路徑

路徑	Extra 欄位	key 欄位	rows 特徵
鬆散索引掃描	Using index for group-by	使用索引	極小（僅掃描組數）
緊湊索引掃描	Using where; Using index	使用索引	符合 WHERE 條件的行數
臨時表+排序	Using temporary; Using filesort	可能為 NULL	全表行數

實戰改寫清單

1. 補齊常量條件：將 GROUP BY c1, c3 改為 WHERE c2=const GROUP BY c1, c3
2. 調整索引列序：將高基數、常用分組列前置
3. 避免非 MIN/MAX 聚合：若需 SUM/COUNT，考慮應用層聚合或物化視圖
4. 禁用前綴索引：全列索引支援鬆散掃描
5. 大表分區：分區裁剪配合索引掃描，變相實現鬆散效果

版本差異提示

• MySQL 5.7：完整支援鬆散/緊湊掃描
• MySQL 8.0：優化器增強，EXPLAIN FORMAT=TREE 可見更詳細執行路徑
• MariaDB 10.2+：支援 HASH GROUP BY 避免排序

GROUP BY 優化的核心是索引設計與查詢寫法的共舞。讀懂 Using index for group-by，就掌握了通往極致聚合性能的鑰匙。