您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL GROUP BY 優化深度解析：Loose Index Scan 條件、適用聚合函數、實戰驗證

編輯：MySQL 綜合教學

MySQL GROUP BY 優化：Loose Index Scan 全解析

一、背景：普通 GROUP BY 執行流程

全表掃描 / 索引全掃描
將符合條件的行按 GROUP BY 列排序（通常需臨時表 + filesort）
掃描排序後結果，進行分組聚合

缺點：臨時表佔用磁盤/內存、filesort 耗 CPU、掃描行數 = 滿足 WHERE 的所有行。

二、Loose Index Scan（鬆散索引掃描）原理

利用索引有序特性，**直接跳躍到每組的第一條記錄**，只掃描索引中極小部分頁面，避免臨時表與排序。執行計劃 Extra 顯示 Using index for group-by。

三、使用條件（官方文檔 5.7/8.0）

#	條件	說明
1	單表查詢	無 JOIN
2	GROUP BY 列為索引最左前綴	如 idx(a,b,c) → GROUP BY a / a,b / a,b,c
3	聚合函數僅限 MIN/MAX	且被聚合列必須在同一索引中
4	索引中未被 GROUP BY 引用的列必須為常量	WHERE 子句中對該列做等值條件
5	不支援前綴索引	如 idx(name(10)) 無法觸發

四、可觸發 Loose Index Scan 的 SQL 模式

-- 假設 t1 有索引 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;  -- c3 為常量等值

五、無法觸發的反例

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

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

-- 索引中間列無常量條件
SELECT c1, c3 FROM t1 GROUP BY c1, c2;  -- c2 在索引中但無 WHERE c2=const

六、支援的特殊聚合函數

AVG(DISTINCT), SUM(DISTINCT), COUNT(DISTINCT)
-- 示例
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

七、實戰驗證步驟（建議自測）

建表 t1(c1 INT, c2 INT, c3 INT, KEY idx(c1,c2,c3))
插入 10 萬行測試數據
EXPLAIN SELECT c1, MIN(c2) FROM t1 GROUP BY c1; 觀察 Extra
對比 SELECT c1, SUM(c2) FROM t1 GROUP BY c1; 的 Extra（應為 Using temporary; Using filesort）