-- 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;
-- 標記重複行
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;
優勢: 無碎片、統計信息全新、無長事務、可驗證後切換。
注意: 需處理自增值、外鍵引用、權限、觸發器同步。
# 歸檔模式刪除重複 (保留最小 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 分批 | 極大表 / 業務不能停 | 極小 (分批提交) | 高 | ⭐⭐⭐⭐⭐ |
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;SELECT col_a, col_b, col_c, COUNT(*) FROM t GROUP BY col_a, col_b, col_c HAVING COUNT(*)>1; 應為 0 行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();