大表 table 需找出 name 重複的所有記錄 id,單純 GROUP BY ... HAVING COUNT > 1 只能得知「哪些值重複」,無法一次取出所有重複行的 id。
SELECT id, name FROM `table`
WHERE name IN (
SELECT name FROM `table`
GROUP BY name HAVING COUNT(name) > 1
);
MySQL 5.x 對 IN (子查詢) 優化差,常退化為全表掃描 + 依賴子查詢,效能極差。
-- 1. 建立重複值臨時表(僅幾千行,極快)
CREATE TABLE tmptable AS
SELECT name FROM `table`
GROUP BY name HAVING COUNT(name) > 1;
-- 2. 多表連接查詢(走索引,秒級返回)
SELECT a.id, a.name
FROM `table` a
JOIN tmptable t ON a.name = t.name;
-- 3. 若需去重(同一 name 多筆只取一筆)
SELECT DISTINCT a.id, a.name
FROM `table` a
JOIN tmptable t ON a.name = t.name;
| 方法 | 執行計劃 | 適用場景 |
|---|---|---|
| IN 子查詢 | DEPENDENT SUBQUERY,外表全掃描 | 小表(< 1 萬)可接受 |
| 臨時表 + JOIN | ref 訪問 tmptable,外表索引查找 | 大表、高頻、生產環境首選 |
| CTE (MySQL 8.0+) | 物化後同 JOIN | 語法更清晰,效能相當 |
WITH dup_names AS (
SELECT name FROM `table`
GROUP BY name HAVING COUNT(*) > 1
)
SELECT t.id, t.name
FROM `table` t
JOIN dup_names d ON t.name = d.name;
無需顯式建表,優化器自動物化 CTE,效能等同臨時表法。
DELETE t1 FROM `table` t1
JOIN `table` t2
ON t1.name = t2.name AND t1.id > t2.id;
自連接刪除 id 較大的重複行,保留最小 id。
「將『找出重複值』與『取出完整行』拆分為兩步,中間用臨時表/CTE 物化重複值集合,再 JOIN 回主表走索引。」
此模式適用於所有「先聚合篩選,再回表取明細」的大表場景。