萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 大表重複字段高效查詢:臨時表 + 多表連接法

問題

大表 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 (子查詢) 優化差,常退化為全表掃描 + 依賴子查詢,效能極差。

高效寫法:臨時表 + JOIN

-- 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 萬)可接受
臨時表 + JOINref 訪問 tmptable,外表索引查找大表、高頻、生產環境首選
CTE (MySQL 8.0+)物化後同 JOIN語法更清晰,效能相當

MySQL 8.0 現代寫法

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 回主表走索引。」

此模式適用於所有「先聚合篩選,再回表取明細」的大表場景。

copyright © 萬盛學電腦網 all rights reserved