MySQL GROUP BY 優化實戰：鬆散與緊湊索引掃描完整指南

MySQL GROUP BY 優化實戰：鬆散與緊湊索引掃描完整指南

最通用做法：全表掃描建臨時表分組。但若滿足條件，優化器可直接用索引避免臨時表。關鍵前提：所有 GROUP BY 列來自同一有序索引前綴。

一、鬆散索引掃描——最優

定義：僅讀取索引中每組首鍵，跳過重複鍵值。
必要條件：

1. 單表查詢
2. GROUP BY 列為同一索引最左連續前綴
3. 聚合函數僅 MAX()/MIN() 且指向同一列
4. WHERE 引用索引中非分組列時，必須為常量（MIN/MAX 參數除外）
5. WHERE 條件全為索引列，不可含非索引列

EXPLAIN 識別：Extra: Using index for group-by

可用鬆散掃描的 SQL 模式（假設 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 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const 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;

-- 引用索引中間列無常量
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
-- 修正：WHERE c3 = 'const'

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

定義：掃描 WHERE 範圍內所有索引鍵再分組。
觸發條件：GROUP BY 列非連續前綴，但 WHERE 提供常量填補間隙形成完整前綴。
EXPLAIN 識別：Using where; Using index（無 for group-by）

緊湊掃描範例

-- group_id 常量填補前綴
SELECT MAX(gmt_create) FROM group_message WHERE group_id = 2 GROUP BY user_id;

-- WHERE 提供索引第一列常量
SELECT group_id, gmt_create FROM group_message WHERE user_id = 1 GROUP BY group_id, gmt_create;
SELECT group_id, gmt_create FROM group_message WHERE group_id = 1 GROUP BY user_id, gmt_create;

非緊湊掃描反例

SELECT group_id FROM group_message WHERE group_id BETWEEN 1 AND 4 GROUP BY user_id;
-- group_id 非常量，user_id 非索引前綴 → 臨時表

四、測試表結構與數據

CREATE TABLE group_message (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id INT UNSIGNED,
  user_id INT UNSIGNED,
  gmt_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  abc INT DEFAULT 0,
  KEY group_id_user_id_gmt_create (group_id, user_id, gmt_create)
) ENGINE=MyISAM;
-- 26 行測試數據（略）

五、優化決策樹

1. 能否調整索引/ SQL 走鬆散掃描？→ 最優
2. 否則能否用 WHERE 常量走緊湊掃描？→ 次優
3. 否則：確保 sort_buffer_size 充足、避免大結果集、防 tmp_table_size 溢出落盤

參考資料：MySQL 官方手冊「How MySQL Optimizes GROUP BY」、鬆散索引掃描文檔、本系列前文。