MySQL 重複數據處理全策略：防重約束、統計查重、去重刪除、業務級冪等設計

重複數據是數據質量頭號殺手。本文系統梳理 MySQL 層面的防重約束 (PK/UK)、應用層冪等設計、統計查重 SQL、多種去重刪除方案、大表去重線上操作，構建從源頭預防到存量治理的完整體系。

一、源頭防重：約束設計

1. 主鍵防重 (單列/複合)

CREATE TABLE person (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    sex CHAR(10),
    PRIMARY KEY (last_name, first_name)  -- 複合主鍵，隱含 NOT NULL
);

2. 唯一索引防重 (允許 NULL、多列)

CREATE TABLE person (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    UNIQUE KEY uk_email (email),           -- 單列唯一
    UNIQUE KEY uk_name (last_name, first_name)  -- 複合唯一
);

3. 插入策略對比

-- 範例：用戶登入次數冪等遞增
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (123, 1, NOW())
ON DUPLICATE KEY UPDATE 
    login_count = login_count + 1,
    last_login = VALUES(last_login);

二、統計查重：發現存量問題

1. 基礎查重 (單列/多列)

SELECT COUNT(*) AS repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;

2. 進階查重：定位具體重複行 (含主鍵)

SELECT a.*
FROM person_tbl a
JOIN (
    SELECT last_name, first_name
    FROM person_tbl
    GROUP BY last_name, first_name
    HAVING COUNT(*) > 1
) b ON a.last_name = b.last_name AND a.first_name = b.first_name
ORDER BY a.last_name, a.first_name, a.id;

3. 大表高效查重 (利用索引)

-- 僅掃描索引，不回表
SELECT last_name, first_name, COUNT(*)
FROM person_tbl
FORCE INDEX (uk_name)
GROUP BY last_name, first_name
HAVING COUNT(*) > 1;

三、去重刪除：保留一條，刪除其餘

方案 1：臨時表重建 (中小表、維護窗口)

CREATE TABLE tmp_person AS
SELECT * FROM person_tbl
GROUP BY last_name, first_name;  -- MySQL 允許非聚合列任意取值 (ONLY_FULL_GROUP_BY 關閉時)

DROP TABLE person_tbl;
ALTER TABLE tmp_person RENAME TO person_tbl;
-- 重建索引/約束

方案 2：自連接刪除 (保留最小 ID)

DELETE a FROM person_tbl a
JOIN person_tbl b 
  ON a.last_name = b.last_name 
 AND a.first_name = b.first_name 
 AND a.id > b.id;

方案 3：ROW_NUMBER 窗口函數刪除

WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY last_name, first_name ORDER BY id) AS rn
    FROM person_tbl
)
DELETE FROM person_tbl WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

方案 4：ALTER IGNORE TABLE (MySQL 5.7 已移除，僅歷史參考)

ALTER IGNORE TABLE person_tbl ADD UNIQUE KEY uk_name (last_name, first_name);

方案 5：大表線上去重 (pt-archiver / gh-ost)

# pt-archiver 歸檔重複行到備份表
pt-archiver --source h=localhost,D=db,t=person_tbl \
  --dest h=localhost,D=db,t=person_dup_archive \
  --where "id NOT IN (SELECT MIN(id) FROM person_tbl GROUP BY last_name, first_name)" \
  --limit 1000 --commit-each --no-check-charset --progress 10000

四、讀取去重：查詢層面

-- DISTINCT (全行去重)
SELECT DISTINCT last_name, first_name FROM person_tbl;

-- GROUP BY (可攜帶聚合)
SELECT last_name, first_name, MIN(id) AS first_id
FROM person_tbl
GROUP BY last_name, first_name;

五、業務級冪等設計 (分佈式系統)

1. 唯一業務鍵：訂單號、交易流水號、設備 ID + 時間戳
2. 冪等表：CREATE TABLE idempotency (biz_key VARCHAR(64) PRIMARY KEY, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
3. 流程：
     1. 檢查冪等表 `SELECT 1 FROM idempotency WHERE biz_key = ?`
     2. 不存在 → `INSERT INTO idempotency (biz_key) VALUES (?)`
     3. 成功 → 執行業務邏輯
     4. 失敗 → 刪除冪等記錄 (或標記失敗供重試)
4. Redis SETNX：高併發場景用 Redis 分佈式鎖 + 過期時間替代 DB 查詢

六、避坑指南

坑 1：INSERT IGNORE 吞沒非唯一鍵錯誤

外鍵違反、非空違反、分區鍵錯誤均會被忽略，**僅建議用於確定只有唯一鍵衝突的場景**

坑 2：REPLACE INTO 觸發自增 ID 跳躍

刪除+插入導致 AUTO_INCREMENT 遞增，大量 REPLACE 會耗盡 ID 空間

坑 3：複合唯一索引包含 NULL 列

MySQL 允許多個 NULL 組合，UNIQUE (a,b) 中 a=1,b=NULL 可插入多條

坑 4：大表 ALTER TABLE ADD UNIQUE 鎖表久

使用 pt-online-schema-change 或 gh-ost 線上加索引