MySQL 大表重複值查詢高效方案：臨時表 + 連接查詢避開子查詢效能陷阱

問題場景

百萬級大表 big_table 需找出 name 重複的所有記錄 id，直接用 IN 子查詢極慢：

-- 效能極差，MySQL 不會將子查詢物化為臨時表
SELECT id, name FROM big_table 
WHERE name IN (SELECT name FROM big_table GROUP BY name HAVING COUNT(*) > 1);

優化演進過程

階段 1：僅找出重複名稱（快）

SELECT name, COUNT(*) AS cnt 
FROM big_table 
GROUP BY name 
HAVING cnt > 1 
ORDER BY cnt DESC;

階段 2：建立重複名稱臨時表（核心技巧）

CREATE TABLE tmp_dup_names AS
SELECT name FROM big_table 
GROUP BY name 
HAVING COUNT(*) > 1;
-- 可加索引加速後續 JOIN
ALTER TABLE tmp_dup_names ADD INDEX idx_name (name);

階段 3：主表 JOIN 臨時表（秒級完成）

SELECT a.id, a.name 
FROM big_table a 
INNER JOIN tmp_dup_names t ON a.name = t.name;

階段 4：去重輸出（如需）

SELECT DISTINCT a.id, a.name 
FROM big_table a 
INNER JOIN tmp_dup_names t ON a.name = t.name;

為何有效？

• 物化子查詢：手動建立臨時表等同於強制 MySQL 物化子查詢結果，避免相關子查詢重複掃描主表。
• 索引加速 JOIN：臨時表加索引後，INNER JOIN 走 ref 存取類型，複雜度從 O(N×M) 降為 O(N+M)。
• 統計資訊準確：臨時表行數少，優化器易生成最優計畫。

進階：單陳述式 CTE 寫法

MySQL 8.0+ 支援 CTE，可簡化為單一 SQL：

WITH dup_names AS (
  SELECT name FROM big_table GROUP BY name HAVING COUNT(*) > 1
)
SELECT a.id, a.name 
FROM big_table a 
JOIN dup_names d ON a.name = d.name;

優化器會自動物化 CTE，效能等同手動臨時表，碼更乾淨。

清理收尾

DROP TABLE tmp_dup_names;

總結

大表重複值查詢，拒絕直接 IN 子查詢。採用「建立重複值集合臨時表 → 加索引 → 主表 JOIN 臨時表」三步驟，或 MySQL 8.0 CTE 寫法，可將分鐘級查詢降為秒級，是 DBA 必備技巧。