萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL GROUP BY 實現原理:鬆散索引掃描與緊湊索引掃描深度解析

GROUP BY 與 ORDER BY 的關係

GROUP BY 實質包含排序 + 分組 + 聚合。若可利用索引順序,可省去額外排序 (Using filesort)。

三種實現方式

  1. 鬆散索引掃描:最優,僅掃描索引中每組第一筆,直接得出聚合結果。
  2. 緊湊索引掃描:掃描滿足條件的所有索引項,再分組聚合。
  3. 臨時表 + 排序:無可用索引,建立臨時表排序再分組 (Using temporary; Using filesort)。

鬆散索引掃描觸發條件

  • 查詢單表,無 JOIN、WHERE 只含索引前綴列常數條件。
  • GROUP BY 列與索引前綴完全一致 (順序相同)。
  • 聚合函數為 MIN()/MAX() (其他聚合函數不支援)。
  • 索引為 B-Tree (非 Hash/Fulltext)。

實測案例

-- 表 group_message (group_id, user_id, gmt_create)
CREATE INDEX idx_gid_uid_gc ON group_message(group_id, user_id, gmt_create);

EXPLAIN SELECT user_id, MAX(gmt_create)
FROM group_message
WHERE group_id < 10
GROUP BY group_id, user_id\G

-- Extra: Using where; Using index for group-by
-- 表示觸發鬆散索引掃描

優化建議

  1. 設計複合索引順序 = WHERE 條件列 + GROUP BY 列 + 聚合列。
  2. 優先滿足 MIN/MAX 鬆散掃描條件。
  3. 避免 SELECT *,只選需要欄位以利用覆蓋索引 (Using index)。
  4. 大表分組聚合考慮匯總表/物化視圖預計算。

版本差異

MySQL 5.7+ 優化器增強,GROUP BY 不再隱含排序 (除非加 ORDER BY),但索引掃描邏輯不變。MySQL 8.0 引入 GROUPING SETS、CUBE、ROLLUP 進階聚合。

copyright © 萬盛學電腦網 all rights reserved