萬盛學電腦網

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

SQL 刪除表重複記錄保留一條:多種去重刪除方案性能對比

場景:表中存在完全重複或業務鍵重複行,需保留 1 條

方案一:GROUP BY + 子查詢 (通用、易懂)


-- 1. 找出重複組及保留的主鍵 (如保留最小 ID)
SELECT MIN(id) AS keep_id, col_a, col_b, col_c
FROM t_table
GROUP BY col_a, col_b, col_c
HAVING COUNT(*) > 1;

-- 2. 刪除非保留行
DELETE FROM t_table
WHERE id NOT IN (
    SELECT MIN(id) FROM t_table GROUP BY col_a, col_b, col_c
);
-- 或更安全的 JOIN 寫法 (避免 NOT IN NULL 陷阱)
DELETE t1 FROM t_table t1
LEFT JOIN (
    SELECT MIN(id) AS keep_id FROM t_table GROUP BY col_a, col_b, col_c
) t2 ON t1.id = t2.keep_id
WHERE t2.keep_id IS NULL;

方案二:ROW_NUMBER 窗口函數 (MySQL 8.0+,單掃描、最高效)


-- 標記重複行
WITH ranked AS (
    SELECT id, 
           ROW_NUMBER() OVER (PARTITION BY col_a, col_b, col_c ORDER BY id) AS rn
    FROM t_table
)
DELETE FROM t_table 
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- 或直接多表 DELETE (MySQL 支持)
DELETE t1 FROM t_table t1
JOIN (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY col_a, col_b, col_c ORDER BY id) AS rn
    FROM t_table
) t2 ON t1.id = t2.id
WHERE t2.rn > 1;

方案三:臨時表重建 (極大表、重複率高、一次性清理)


-- 1. 創建新表結構
CREATE TABLE t_table_new LIKE t_table;

-- 2. 導入去重數據
INSERT INTO t_table_new
SELECT * FROM t_table
WHERE id IN (
    SELECT MIN(id) FROM t_table GROUP BY col_a, col_b, col_c
);
-- 或窗口函數
INSERT INTO t_table_new
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY col_a, col_b, col_c ORDER BY id) AS rn
    FROM t_table
) t WHERE rn = 1;

-- 3. 原子切換 (需短暫鎖表)
RENAME TABLE t_table TO t_table_old, t_table_new TO t_table;

-- 4. 驗證無誤後刪舊表
DROP TABLE t_table_old;

優勢: 無碎片、統計信息全新、無長事務、可驗證後切換。
注意: 需處理自增值、外鍵引用、權限、觸發器同步。

方案四:pt-archiver / pt-online-schema-change (線上無鎖清理)


# 歸檔模式刪除重複 (保留最小 id)
pt-archive --source h=localhost,D=db,t=t_table \
  --purge --where "id NOT IN (SELECT MIN(id) FROM t_table GROUP BY col_a, col_b, col_c)" \
  --limit 1000 --sleep 0.1 --no-check-charset --statistics

性能對比與選型

方案適用規模鎖影響複雜度推薦指數
GROUP BY + NOT IN< 10 萬行表鎖/行鎖長低⭐⭐
GROUP BY + LEFT JOIN< 100 萬行行鎖低⭐⭐⭐
ROW_NUMBER (8.0+)任意 (建議 < 500 萬)行鎖中⭐⭐⭐⭐⭐
臨時表重建> 100 萬 / 重複率高短暫元數據鎖高⭐⭐⭐⭐
pt-archiver 分批極大表 / 業務不能停極小 (分批提交)高⭐⭐⭐⭐⭐

實戰檢查清單

  1. ✅ 確定**業務去重鍵** (哪幾列組合唯一)
  2. ✅ 確定**保留策略** (最小 ID / 最大 ID / 最新時間 / 狀態優先)
  3. ✅ **備份原表** (或快照)
  4. ✅ 統計重複分佈:SELECT col_a, col_b, col_c, COUNT(*) FROM t GROUP BY col_a, col_b, col_c HAVING COUNT(*)>1 ORDER BY COUNT(*) DESC LIMIT 20;
  5. ✅ 小表驗證邏輯 → 生產執行
  6. ✅ 執行後核對:SELECT col_a, col_b, col_c, COUNT(*) FROM t GROUP BY col_a, col_b, col_c HAVING COUNT(*)>1; 應為 0 行
  7. ✅ ANALYZE TABLE t; 更新統計信息

防止重複再入:唯一約束 + 應用層冪等


-- 數據庫層硬約束 (最強保障)
ALTER TABLE t_table ADD UNIQUE INDEX uk_biz_key (col_a, col_b, col_c);

-- 應用層: INSERT IGNORE / ON DUPLICATE KEY UPDATE / 先查後插 (分布式鎖)
INSERT IGNORE INTO t_table (col_a, col_b, col_c) VALUES (?, ?, ?);
-- 或
INSERT INTO t_table (col_a, col_b, col_c) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE updated_at = NOW();
copyright © 萬盛學電腦網 all rights reserved