MySQL DISTINCT 與 GROUP BY 差異對比、用法詳解與效能分析

實測對比 DISTINCT 與 GROUP BY 在去重、排序、聚合、LIMIT 行為上的異同，並給出效能選擇建議。

核心差異

• 排序：DISTINCT 保持資料存放順序；GROUP BY 隱式排序（通常 ASC）。
• 實現：DISTINCT ≈ GROUP BY + 取每組第一行，底層均可用鬆散/緊湊索引掃描或臨時表。
• 效能：DISTINCT 僅找不同值；GROUP BY 需為聚合函數準備。但 GROUP BY 讀取行數 = 分組數，往往遠小於總行數，**實測 GROUP BY 通常更快**。

實測案例

資料準備

CREATE TABLE student (name VARCHAR(20), age INT, height INT);
INSERT INTO student VALUES ('a',5,123),('a',5,456),('b',20,222);

1. DISTINCT 多欄位去重

SELECT DISTINCT name, age FROM student;  -- 僅 (a,5) (b,20)

2. GROUP BY 多欄位分組 + 聚合

SELECT name, age, SUM(height) FROM student GROUP BY name, age;
-- (a,5,579) (b,20,222)

3. HAVING 過濾分組

SELECT name, age, SUM(height) AS n FROM student GROUP BY name, age HAVING n > 500;

4. LIMIT 作用於分組後結果

SELECT songname, sengerid, COUNT(*) AS n FROM t_song
GROUP BY songname, sengerid HAVING n > 1
ORDER BY n DESC, songid ASC LIMIT 10;

驗證：LIMIT 截取的是聚合排序後的結果集，非分組前行數。

5. COUNT(DISTINCT) 兩種寫法等價

SELECT COUNT(DISTINCT(songid)) FROM feedback;
SELECT COUNT(DISTINCT songid) FROM feedback;

6. MAX(字串) 取非空最大值

SELECT feedback_id, songid, songname, MAX(singername), MAX(time) AS new_time
FROM feedback GROUP BY songid ORDER BY new_time DESC;

子句執行順序

WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

避坑：COUNT(*) 轉換陷阱

帶 GROUP BY 查詢改寫 SELECT COUNT(*) FROM ... GROUP BY ... 會因欄位不匹配報錯（如 ORDER BY new_time 找不到別名）。