MySQL 百萬級重複數據去重實戰：中間表法與窗口函數法雙軌解決方案

問題背景

Linux 性能採集項目，表 perf_linux 含 427 萬行，但業務主鍵 (conf_id, insert_time) 僅 4.2 萬組唯一值，**冗餘率 10 倍**，導致寫入極慢。

-- 表結構
CREATE TABLE perf_linux (
    id VARCHAR(255) PRIMARY KEY,      -- UUID
    conf_id VARCHAR(255),             -- 設備ID
    insert_time DATETIME,             -- 採集時間點
    cpu_usage FLOAT(11,2),
    memory_usage FLOAT(11,2),
    io_usage_write FLOAT(11,2),
    io_usage_read FLOAT(11,2),
    INDEX idx_conf_time (conf_id, insert_time)
) ENGINE=InnoDB;

方案一：中間表 + 自增列去重（兼容 MySQL 5.6+）

核心邏輯

1. 創建結構同樣的中間表
2. 添加自增列 auto_id 作為物理順序標識
3. 按業務鍵分組，取每組 MIN(auto_id) 保留
4. 通過「新表插入保留行」避開 DELETE ... WHERE ... IN (subquery) 限制

完整執行腳本

-- 1. 創建中間表並拷貝數據
CREATE TABLE perf_linux_t LIKE perf_linux;
INSERT INTO perf_linux_t SELECT * FROM perf_linux;

-- 2. 添加自增列並重置主鍵
ALTER TABLE perf_linux_t 
    ADD COLUMN auto_id INT NOT NULL AUTO_INCREMENT FIRST,
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (auto_id);

-- 3. 創建結果表
CREATE TABLE perf_linux_dedup LIKE perf_linux;

-- 4. 插入去重後數據（核心步驟）
INSERT INTO perf_linux_dedup 
SELECT * FROM perf_linux_t 
WHERE auto_id IN (
    SELECT MIN(auto_id) 
    FROM perf_linux_t 
    GROUP BY conf_id, insert_time
);

-- 5. 清理臨時表
DROP TABLE perf_linux_t;

-- 6. 替換原表（業務低峰期執行）
RENAME TABLE perf_linux TO perf_linux_old, 
             perf_linux_dedup TO perf_linux;

-- 7. 重建索引
ALTER TABLE perf_linux ADD INDEX idx_conf_time (conf_id, insert_time);

關鍵點解析

• GROUP BY conf_id, insert_time：業務主鍵維度去重
• MIN(auto_id)：保留最早寫入的記錄（可改 MAX 保留最新）
• INSERT ... SELECT ... WHERE IN (subquery)：繞過 MySQL 「不能 update 同表子查詢」限制
• RENAME TABLE：原子級切換，零停機（需元數據鎖，極短）

方案二：窗口函數 ROW_NUMBER() 去重（MySQL 8.0+ 推薦）

-- 單條 CTE 完成去重，無需中間表
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY conf_id, insert_time ORDER BY id) AS rn
    FROM perf_linux
)
DELETE FROM perf_linux 
WHERE id IN (
    SELECT id FROM ranked WHERE rn > 1
);

-- 或創建新表（大表推薦，避免長事務）
CREATE TABLE perf_linux_dedup AS
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY conf_id, insert_time ORDER BY id) AS rn
    FROM perf_linux
) t WHERE rn = 1;

• 性能：單掃描、單排序、無臨時表開銷，比中間表法快 3-5 倍
• 靈活：ORDER BY 可指定保留邏輯（最早/最新/最大 CPU 等）
• 要求：MySQL 8.0.2+

方案三：唯一索引強制去重（極簡但有風險）

-- 嘗試添加唯一索引，重複行報錯
ALTER IGNORE TABLE perf_linux ADD UNIQUE INDEX uk_conf_time (conf_id, insert_time);
-- MySQL 5.7+ 移除 IGNORE 子句，此法失效

❌ **不推薦**：不可控保留哪行、報錯中斷、鎖表時間長、8.0 不支持。

大表去重性能優化策略

根因治理：防止重複寫入

去重是治標，**業務層面幂等性設計** 才是治本：

-- 1. 數據庫層：業務鍵建立唯一約束
ALTER TABLE perf_linux ADD UNIQUE INDEX uk_conf_time (conf_id, insert_time);
-- 應用捕獲 Duplicate Key Error (1062) 視為成功

-- 2. 應用層：INSERT IGNORE / ON DUPLICATE KEY UPDATE
INSERT INTO perf_linux (conf_id, insert_time, cpu_usage, ...)
VALUES (?, ?, ?, ...)
ON DUPLICATE KEY UPDATE 
    cpu_usage = VALUES(cpu_usage),
    memory_usage = VALUES(memory_usage),
    ...;

-- 3. 採集端：本地去重 + 批量發送
# 進程內用 dict[(conf_id, ts)] = metrics 合併，定時 flush

驗證結果

-- 去重前
SELECT COUNT(*) FROM perf_linux;           -- 4,273,660
SELECT COUNT(DISTINCT conf_id, insert_time) FROM perf_linux;  -- 42,387

-- 去重後
SELECT COUNT(*) FROM perf_linux;           -- 42,387
SELECT COUNT(DISTINCT conf_id, insert_time) FROM perf_linux;  -- 42,387
-- 數據量縮減 99%，寫入延遲從 100ms 降至 <5ms

總結

MySQL 去重核心模式：**分組標識 + 保留策略 + 原子切換**。

• MySQL 5.6/5.7：中間表 + 自增列 + MIN/MAX 分組，穩健通用
• MySQL 8.0+：ROW_NUMBER() OVER (PARTITION BY ...) 一步到位，性能最優
• 超大表：分批、並行、在線 DDL 組合拳
• 根治：業務鍵唯一約束 + 應用層幂等寫入，杜絕源頭重複

去重後務必 ANALYZE TABLE 更新統計信息，重建必要索引，驗證業務查詢計劃是否走索引。