MySQL GROUP BY 實現原理深度解析：鬆散/緊湊索引掃描與臨時表優化

前言：GROUP BY 背後的三種執行路徑

GROUP BY 本質上是「排序 + 分組 + 聚合」。MySQL 優化器根據索引可用性，會選擇三種實現方式：鬆散索引掃描、緊湊索引掃描、臨時表 + Filesort。理解其觸發條件與效能差異，是編寫高效聚合查詢的關鍵。

一、鬆散索引掃描 —— 效能王者

觸發條件（必須全滿足）

1. GROUP BY 欄位為同一複合索引的最左連續前綴（如 INDEX (a,b,c)，GROUP BY a,b 可，GROUP BY a,c 不可）。
2. 聚合函數僅限 MAX() / MIN()。
3. 查詢引用的其他索引欄位必須為常數條件（WHERE a=1 AND b=2）。
4. 無 DISTINCT、無 HAVING、無 ORDER BY（除非與 GROUP BY 順序一致）。

執行計畫標誌

Extra: Using index for group-by

原理與優勢

• 直接跳躍到各組的第一筆索引項，不掃描組內所有行。
• 無 WHERE 時，讀取鍵值數 = 分組數 ≪ 總行數；有範圍條件時，僅定位各組首鍵。
• 極致效能：O(分組數) 而非 O(總行數)。

二、緊湊索引掃描 —— 次優選擇

觸發場景

• GROUP BY 欄位在索引中但非最左前綴，或有範圍條件導致無法跳躍。
• 例如 INDEX (a,b,c)，WHERE a=1 GROUP BY b（a 為常數填補缺口，形成前綴）。

執行計畫標誌

Extra: Using where; Using index   -- 無 "for group-by"

原理

• 掃描滿足 WHERE 的所有索引鍵，再記憶體中分組聚合。
• 若 WHERE 條件可形成索引前綴，仍可避免額外排序（索引天然有序）。

三、臨時表 + Filesort —— 兜底方案

觸發場景

• 無可用索引（欄位不在同一索引、或為 Hash 索引）。
• 使用非 MAX/MIN 聚合函數（COUNT、SUM、AVG 等）。
• GROUP BY 與 ORDER BY 欄位不一致且無索引支援。

執行計畫標誌

Extra: Using temporary; Using filesort

原理與代價

1. 全表/索引掃描讀取符合條件的行。
2. 寫入內部臨時表（記憶體超限轉 MyISAM/InnoDB 磁碟表）。
3. 對臨時表排序（Filesort）再分組聚合。
4. 高 CPU、高 I/O、高記憶體，大數據集極慢。

四、實戰優化清單

五、進階技巧

• 常數填補法：WHERE a=5 GROUP BY b 可用 INDEX (a,b) 實現緊湊掃描；WHERE a IN (1,2) GROUP BY b 則不行（非常數）。
• 避免 SELECT *：只取必要欄位，增加覆蓋索引機會。
• SQL_SMALL_RESULT 提示：SELECT SQL_SMALL_RESULT ... GROUP BY 告知優化器結果集小，傾向記憶體臨時表。
• 監控指標：Created_tmp_tables、Created_tmp_disk_tables、Sort_merge_passes 暴增即為 GROUP BY 瓶頸信號。