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

需求：在千萬級大表中找出 name 重複的所有記錄 id。直接用 IN (子查詢) 效能極差 (MySQL 早期版本不優化子查詢、不建立臨時表)。本文提供**建立臨時表 + 連接查詢**的高效方案，並對比多種寫法效能。

問題分析

-- 寫法 1：直接 IN 子查詢 (慢，全表掃描 + 依賴子查詢優化器)
SELECT id, name FROM big_table
WHERE name IN (
    SELECT name FROM big_table
    GROUP BY name HAVING COUNT(*) > 1
);

-- 寫法 2：EXISTS 相關子查詢 (同樣慢)
SELECT id, name FROM big_table t1
WHERE EXISTS (
    SELECT 1 FROM big_table t2
    WHERE t2.name = t1.name AND t2.id <> t1.id
);

高效方案：臨時表物化 + JOIN

-- 步驟 1：建立臨時表物化重複 name (只掃描一次，利用索引)
CREATE TEMPORARY 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);

-- 步驟 2：內連接查詢完整記錄 (毫秒級)
SELECT b.id, b.name
FROM big_table b
INNER JOIN tmp_dup_names t ON b.name = t.name;

-- 步驟 3：去重 (如需唯一 name 列表)
SELECT DISTINCT b.id, b.name
FROM big_table b
INNER JOIN tmp_dup_names t ON b.name = t.name;

-- 步驟 4：用畢刪除臨時表 (會話結束自動刪除)
DROP TEMPORARY TABLE tmp_dup_names;

效能對比 (千萬級測試，name 有索引)

MySQL 8.0+ 現代寫法：CTE (Common Table Expression)

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

CTE 會自動物化 (或內聯)，無需手動建立臨時表，語法更清晰。

進階：直接獲取重複記錄的聚合資訊

-- 重複 name 及其計數、最小/最大 id
SELECT name, COUNT(*) AS cnt, MIN(id) AS min_id, MAX(id) AS max_id
FROM big_table
GROUP BY name HAVING COUNT(*) > 1
ORDER BY cnt DESC;

避坑指南

• 確保 name 有索引，否則 GROUP BY 也要全表掃描 (但只掃一次，可接受)。
• 臨時表命名衝突：同一會話多次執行先 DROP TEMPORARY TABLE IF EXISTS tmp_dup_names。
• 權限：需 CREATE TEMPORARY TABLES 權限。
• 大表 GROUP BY 記憶體：調大 tmp_table_size/max_heap_table_size (如 256MB) 避免臨時表落盤 (MyISAM)。
• 刪除重複資料：查詢確認後，可用 DELETE b FROM big_table b JOIN tmp_dup_names t ON b.name = t.name WHERE b.id > (SELECT MIN(id) FROM big_table WHERE name = t.name); 保留每組最小 id。