MySQL Loose Index Scan 深度解析：GROUP BY 優化的利器

前言

InnoDB 採用索引組織表（IOT），資料按主鍵順序存儲。當執行 SELECT COUNT(DISTINCT a) FROM t 且 a 有索引時，傳統做法是全索引掃描，效率低下。MySQL 5.0+ 引入 Loose Index Scan（鬆散索引掃描），可跳過重複索引值，大幅提升 GROUP BY、DISTINCT、MIN/MAX 查詢效能。

一、Loose Index Scan 觸發條件

執行計劃 Extra 顯示 Using index for group-by 即表示生效。需滿足：

1. 單表查詢
2. GROUP BY 使用索引最左前綴列
3. 僅支援 MIN()/MAX() 聚合函數
4. WHERE 條件僅包含 = constant 操作；未出現在 GROUP BY 的索引列必須為常數條件
5. 不支援前綴索引（如 INDEX(c1(10))）

二、實戰案例演示

建表與索引

CREATE TABLE log_table (
  id INT NOT NULL PRIMARY KEY,
  log_machine VARCHAR(20) NOT NULL,
  log_time DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE INDEX ix_log_machine_time ON log_table (log_machine, log_time);

場景 1：單值 MIN/MAX - 極速

SELECT MAX(log_time) FROM log_table;
SELECT MAX(log_time) FROM log_table WHERE log_machine IN ('Machine 1');

Extra: Select tables optimized away — 僅需一次 Index Seek，優化器識別出 MIN/MAX 位於索引最左/右端。

場景 2：多值 IN 查詢 - 預設走 Range Scan

SELECT MAX(log_time) FROM log_table 
WHERE log_machine IN ('Machine 1','Machine 2','Machine 3','Machine 4');

Type: range, Extra: Using where; Using index — 掃描所有滿足條件的索引記錄再聚合，rows=82636。

場景 3：加上 GROUP BY 觸發 Loose Index Scan

SELECT MAX(log_time) FROM log_table 
WHERE log_machine IN ('Machine 1','Machine 2','Machine 3','Machine 4')
GROUP BY log_machine ORDER BY 1 DESC LIMIT 1;

Extra: Using index for group-by — 對每個 log_machine 執行一次 Loose Index Scan，rows=16，效能提升 5000 倍！

等價於：

SELECT MAX(log_time) FROM log_table WHERE log_machine IN ('Machine 1')
UNION ALL
SELECT MAX(log_time) FROM log_table WHERE log_machine IN ('Machine 2')
...

三、可/不可使用 Loose Index Scan 的 SQL 模式

假設索引 idx(c1,c2,c3)：

四、Tight Index Scan（緊湊索引掃描）

當 Loose Index Scan 不滿足時，MySQL 可能退化為 Tight Index Scan：

• 需讀取所有滿足條件的索引鍵（全索引或範圍掃描）
• GROUP BY 列不連續、或非最左前綴開始但 WHERE 包含最左列

-- 典型 Tight Index Scan 場景
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

五、MySQL 5.6 Index Condition Pushdown (ICP) 補完

ICP 將部分 WHERE 條件下推到存儲引擎層過濾，減少回表次數，彌補 Loose Index Scan 覆蓋面不足的問題。

對比實驗

KEY(age, zip)
-- 5.5 僅用 age 列 (key_len=4)
SELECT name FROM people WHERE age BETWEEN 18 AND 20 AND zip IN (12345,12346,12347);
-- 1分56秒

-- 改寫 UNION ALL
SELECT ... WHERE age=18 AND zip IN (...) UNION ALL ...

-- 5.6 ICP 生效 (key_len=8)
SELECT name FROM people WHERE age=18 AND zip IN (...);
-- 0.06秒

ICP 讓複合索引的非引導列也能在索引層面過濾，大幅降低 Server 層處理行數。

六、總結與最佳實踐

• 設計複合索引時，將高基數、常用於 GROUP BY/DISTINCT 的列置於最左
• 避免在 WHERE 中對非最左列使用範圍條件（<, >, BETWEEN, IN），改用 = 或改寫為 UNION ALL
• 升級至 MySQL 5.6+ 享受 ICP 自動優化
• 使用 EXPLAIN 觀察 Extra 欄位：Using index for group-by = Loose Index Scan，Using where; Using index = Tight/Range Scan