萬盛學電腦網

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

MySQL 刪除重復數據完整指南:四大核心方案、索引優化、數據保真與生產級操作規範

數據去重是數據治理的高頻剛需。MySQL 中刪除重復記錄的方法主要有四類:**自聯結刪除、唯一索引去重、新建表去重、窗口函數去重 (MySQL 8.0+)**。本文通過標準化演示數據,詳細對比四大方案的適用場景、執行原理、性能特徵、數據保真風險 (索引/約束/自增 ID 丟失),並提供生產級操作規範 (備份、驗證、回滾、監控)。

一、演示數據準備

CREATE TABLE `demo` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `site` VARCHAR(100) NOT NULL DEFAULT '',
  KEY `idx_site` (`site`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `demo` (`site`) VALUES
('https://www.example.com'),
(''),
('https://www.example.com'),
('https://www.example.com'),
('https://www.example.com');

-- 數據狀態
-- id=1, site='https://www.example.com'
-- id=2, site=''
-- id=3, site='https://www.example.com'  -- 重複
-- id=4, site='https://www.example.com'  -- 重複
-- id=5, site='https://www.example.com'  -- 重複

二、方案一:自聯結刪除 (無索引權限、保留最早/最晚記錄)

核心原理

表別名自聯結,比較主鍵大小決定保留哪條:a.id > b.id 刪除較新 (保留最早),a.id < b.id 刪除較舊 (保留最新)。

刪除較舊重複 (保留最新,即最大 id)

DELETE a
FROM `demo` AS a
INNER JOIN `demo` AS b ON a.site = b.site
WHERE a.id < b.id;

刪除較新重複 (保留最早,即最小 id)

DELETE a
FROM `demo` AS a
INNER JOIN `demo` AS b ON a.site = b.site
WHERE a.id > b.id;

先驗證將被刪除的記錄 (安全第一)

SELECT a.* 
FROM `demo` a
INNER JOIN `demo` b ON a.site = b.site
WHERE a.id > b.id;  -- 保留最早,查看將刪除的較新記錄

優缺點

優點缺點
無需索引/建表權限大表性能差 (O(n²) 自聯結),需分批處理
精確控制保留策略 (最早/最新)無法處理「完全重複行」(無主鍵表)
原地刪除,無數據遷移風險長事務、鎖升級、主從延遲風險

三、方案二:唯一索引去重 (有索引權限、極速、自動保留首行)

核心原理

ALTER IGNORE TABLE ... ADD UNIQUE INDEX:構建唯一索引過程中自動刪除重複鍵行,**保留第一行 (按聚簇索引順序,通常是最小主鍵)**。

操作步驟

-- 1. 添加唯一索引 (IGNORE 關鍵字關鍵!)
ALTER IGNORE TABLE `demo` ADD UNIQUE INDEX `uk_site` (`site`);

-- 2. 驗證結果
SELECT * FROM `demo` ORDER BY id;
-- 保留 id=1,2,3 (site='' 和 site='http...' 首次出現)

-- 3. (可選) 刪除索引恢復原狀
ALTER TABLE `demo` DROP INDEX `uk_site`;

關鍵限制 (MySQL 5.7.4+ / 8.0+)

  • IGNORE 子句已移除 (5.7.4 棄用,8.0 移除)。新版本無法直接使用此法。
  • 替代:創建新表加唯一索引 → INSERT IGNORE INTO new SELECT * FROM old → 重命名。

四、方案三:新建表去重 (有建表權限、大表首選、可保留索引結構)

簡易版 (丟失索引/約束/自增屬性)

CREATE TABLE `demo_new` AS 
SELECT * FROM `demo` GROUP BY `site`;  -- 隱式分組去重,保留每組第一行

-- 風險:丟失 PK、UK、FK、AUTO_INCREMENT、索引、分區等元數據!

標準版 (保留完整表結構) —— 生產環境標準做法

-- 1. 獲取原表完整建表語句
SHOW CREATE TABLE `demo`;
-- 輸出: CREATE TABLE `demo` (...) ENGINE=InnoDB AUTO_INCREMENT=6 ...

-- 2. 按原結構建新表 (複製結構不複製數據)
CREATE TABLE `demo_new` LIKE `demo`;

-- 3. 去重插入 (保留最小 id)
INSERT INTO `demo_new` 
SELECT * FROM `demo` 
WHERE id IN (
    SELECT MIN(id) FROM `demo` GROUP BY `site`
);
-- 或使用 LEFT JOIN 反模式 (舊版本優化)
INSERT INTO `demo_new` 
SELECT d1.* FROM `demo` d1
LEFT JOIN `demo` d2 ON d1.site = d2.site AND d1.id > d2.id
WHERE d2.id IS NULL;

-- 4. 原子化切換 (RENAME 原子操作,無鎖阻塞)
RENAME TABLE `demo` TO `demo_old`, `demo_new` TO `demo`;

-- 5. 驗證無誤後清理舊表
DROP TABLE `demo_old`;

優缺點

優點缺點
大表高性能 (單次全掃描 + 批量插入)需建表權限、臨時佔用雙倍空間
完整保留表結構 (PK、UK、FK、索引、AUTO_INCREMENT)切換瞬間極短暫寫入阻塞 (RENAME 元數據鎖)
可驗證新表數據後再切換外鍵引用表需同步處理 (禁用 FK 或級聯)
無長事務、鎖競爭小

五、方案四:窗口函數去重 (MySQL 8.0+、語義最清晰、靈活保留策略)

-- 保留每組 site 的最小 id (最早記錄)
WITH ranked AS (
    SELECT 
        id, site,
        ROW_NUMBER() OVER (PARTITION BY site ORDER BY id ASC) AS rn
    FROM `demo`
)
DELETE FROM `demo` 
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- 保留每組 site 的最大 id (最新記錄)
WITH ranked AS (
    SELECT 
        id, site,
        ROW_NUMBER() OVER (PARTITION BY site ORDER BY id DESC) AS rn
    FROM `demo`
)
DELETE FROM `demo` 
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- 保留每組 site 的最新更新時間 (假設有 updated_at)
WITH ranked AS (
    SELECT 
        id, site,
        ROW_NUMBER() OVER (PARTITION BY site ORDER BY updated_at DESC) AS rn
    FROM `demo`
)
DELETE FROM `demo` 
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

優勢:標準 SQL、語義顯式、支持任意排序規則 (最新、最舊、優先級)、單條 SQL 完成、優化器友好。

六、大表分批刪除通用模板 (避免長事務)

<?php
// 適用於方案一、四 的大表分批執行
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", "user", "pass", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);

$batchSize = 1000;
$deletedTotal = 0;

while (true) {
    // 使用 LIMIT 限制影響行數 (MySQL 支持 DELETE ... LIMIT)
    $sql = "DELETE a FROM `demo` a
            INNER JOIN `demo` b ON a.site = b.site
            WHERE a.id > b.id
            LIMIT $batchSize";
    
    $affected = $pdo->exec($sql);
    $deletedTotal += $affected;
    echo "Batch deleted: $affected, Total: $deletedTotal\n";
    
    if ($affected < $batchSize) break; // 刪完了
    usleep(100000); // 0.1秒間隙,緩解主從壓力
}
echo "Done. Total deleted: $deletedTotal\n";
?>

七、生產級操作規範 (SOP)

  1. 備份先行:全量備份 + Binlog 位置記錄 (或快照)。
  2. 只讀驗證:在只讀副本/測試庫跑去重邏輯,對比行數、業務關鍵指標。
  3. 分批執行:單批 < 5000 行,間隔 100-500ms,監控 Threads_running、Innodb_row_lock_waits、主從延遲 Seconds_Behind_Master。
  4. 業務窗口:低峰期執行,通知下游系統。
  5. 驗證清單:總行數、去重鍵唯一性、關鍵業務聚合指標 (金額、用戶數)、應用讀寫煙測。
  6. 回滾預案:保留 demo_old 表 24-48h,確認無誤再 Drop。
  7. 事後複盤:記錄耗時、影響行數、主從延遲峰值、業務影響評估。

結語

MySQL 去重無銀彈,只有場景適配:

  • 小表/無索引權限/臨時任務 → 自聯結 DELETE (分批)。
  • 舊版本/MyISAM/有索引權限 → ALTER IGNORE ADD UNIQUE (最快)。
  • 大表/生產環境/需保留結構 → 新建表 + RENAME 切換 (標準 SOP)。
  • MySQL 8.0+ / 靈活保留策略 → 窗口函數 ROW_NUMBER() (現代標準)。

核心原則:**備份優先、分批執行、驗證可逆、監控全程**。數據無價,謹慎為上。

copyright © 萬盛學電腦網 all rights reserved