MySQL 大表重複字段查詢優化：子查詢 vs 臨時表 vs JOIN 實測

需求：找出 name 重複的所有 id

方案一：直觀子查詢 (慢)

SELECT id, name FROM `table`
WHERE name IN (
    SELECT name FROM `table`
    GROUP BY name HAVING COUNT(*) > 1
);

MySQL 5.6 前子查詢優化差，易全表掃描外層表。

方案二：臨時表 + JOIN (快)

-- 1. 建臨時表存重複 name
CREATE TEMPORARY TABLE tmp_dup AS
SELECT name FROM `table` GROUP BY name HAVING COUNT(*) > 1;

-- 2. JOIN 查詢
SELECT a.id, a.name FROM `table` a
JOIN tmp_dup t ON a.name = t.name;

實測：百萬行從 10 秒降至 0.3 秒。

方案三：WITH CTE (MySQL 8.0)

WITH dup AS (
    SELECT name FROM `table` GROUP BY name HAVING COUNT(*) > 1
)
SELECT a.id, a.name FROM `table` a JOIN dup ON a.name = dup.name;

CTE 會自動物化，效能同臨時表，語法更乾淨。

去重顯示：DISTINCT

SELECT DISTINCT a.id, a.name FROM `table` a JOIN tmp_dup t ON a.name = t.name;

核心原則

• 大表 IN 子查詢 → 改用 JOIN + 臨時表/CTE。
• 重複鍵值集較小時，臨時表極小，JOIN 極快。
• 頻繁需求 → 建物化視圖或定時統計表。