MySQL 內部將 DISTINCT 轉換為 GROUP BY 執行,三種掃描模式:
Extra: Using index for group-by,最快Extra: Using WHERE; Using indexExtra: Using temporary; Using filesort,最慢EXPLAIN SELECT DISTINCT group_id FROM group_message;
-- Extra: Using index for group-by ← 理想
EXPLAIN SELECT DISTINCT user_id FROM group_message WHERE group_id = 2;
-- Extra: Using WHERE; Using index ← 良好
EXPLAIN SELECT DISTINCT user_id FROM group_message WHERE group_id > 1 AND group_id < 10;
-- Extra: Using WHERE; Using index; Using temporary ← 需優化
EXPLAIN SELECT DISTINCT MAX(user_id) FROM group_message
WHERE group_id > 1 AND group_id < 10 GROUP BY group_id;
-- Extra: Using WHERE; Using index; Using temporary; Using filesort ← 有排序開銷
-- 表:id, name
-- 1 a
-- 2 b
-- 3 c
-- 4 c
-- 5 b
SELECT DISTINCT name FROM t; -- a,b,c ✓
SELECT DISTINCT name, id FROM t; -- 全部 5 行 ✗(組合去重)
SELECT id, DISTINCT name FROM t; -- 語法錯誤
SELECT id, name FROM (
SELECT id, name, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
FROM t
) x WHERE rn = 1;
SELECT MIN(id) AS id, name FROM t GROUP BY name;
-- 或
SELECT id, name, COUNT(DISTINCT name) FROM t GROUP BY name; -- 文中技巧,但 COUNT 列多餘
COUNT(DISTINCT col)GROUP BY + 聚合,或窗口函數WHERE/GROUP BY/ORDER BY 順序的複合索引SELECT DISTINCT *,明確列出欄位DISTINCT 本質是 GROUP BY。看 EXPLAIN 的 Extra 欄位判斷效能:Using index for group-by 最佳,Using temporary 需優化。多欄位去重用 GROUP BY 或窗口函數,別濫用 DISTINCT。