如何高效删除 MySQL 中重复记录（保留每组第一条）-php教程-PHP中文网

如何高效删除 MySQL 中重复记录（保留每组第一条）

本文介绍在 laravel 项目中，针对千万级数据表（如 5000 万行）安全、高效地批量删除重复记录的完整方案：通过纯 sql 在数据库层完成去重，避免 php 循环与多次查询带来的性能灾难。

本文介绍在 laravel 项目中，针对千万级数据表（如 5000 万行）安全、高效地批量删除重复记录的完整方案：通过纯 sql 在数据库层完成去重，避免 php 循环与多次查询带来的性能灾难。

在处理大规模数据（如 5000 万行、140GB 表）时，使用 Laravel Eloquent 在 PHP 层逐组查找并删除重复记录——例如先查出所有 (codes, customer_id) 组合的重复项，再对每组执行 first() + delete() ——会引发严重性能瓶颈：单次操作涉及多次数据库往返、ORM 解析开销及大量小查询（可能超 20 万次），极易导致超时、锁表甚至服务中断。

根本解法：将去重逻辑完全下推至数据库层执行，杜绝 PHP/ORM 参与。 核心思路是：只保留每个 (codes, customer_id) 组合中 id 最小（即最早插入）的一条记录，其余全部删除。 这可通过“临时唯一表 + 反向过滤”策略实现，全程仅需数条 SQL，且可分批执行，兼顾安全性与可控性。

✅ 推荐方案：创建 `ids_to_keep` 临时表（安全、可控、空间友好）

-- 1. 创建轻量级临时表（仅存 id + 去重字段，无冗余数据）
CREATE TABLE ids_to_keep (
    id INT PRIMARY KEY,
    codes VARCHAR(50) NOT NULL,
    customer_id INT NOT NULL,
    UNIQUE KEY idx_codes_customer (codes, customer_id)
);

-- 2. 利用 INSERT IGNORE 的冲突忽略机制，自动保留每组首个插入的 id
INSERT IGNORE INTO ids_to_keep 
SELECT id, codes, customer_id FROM pizzas;

⚠️ 注意事项：

ids_to_keep 表结构必须与源表对应字段类型一致（如 codes 长度、customer_id 类型）；

INSERT IGNORE 会跳过违反 UNIQUE 约束的后续行，因此每组 (codes, customer_id) 仅保留 首次扫描到的 id（通常为最小 id，取决于 SELECT 扫描顺序；若需严格保证最小 id，见进阶优化）；

此表体积极小：假设原表单行约 3KB，5000 万行共 140GB；而 ids_to_keep 仅含 id（4B）、codes（≤50B）、customer_id（4–8B），加上索引，预估总大小

? 执行删除：精准移除非保留记录

-- 方案 A：一次性删除（适用于有足够事务日志空间的环境）
DELETE FROM pizzas 
WHERE id NOT IN (SELECT id FROM ids_to_keep);

-- 方案 B：分批删除（强烈推荐！避免长事务与锁表）
DELETE FROM pizzas 
WHERE id NOT IN (SELECT id FROM ids_to_keep)
  AND id BETWEEN 1 AND 100000; -- 每次处理 10 万 ID 区间

-- 执行后检查影响行数，逐步推进区间（如 100001–200000...）

? 性能提示：执行前务必运行 EXPLAIN DELETE ... 确认查询走 id 主键索引；若 NOT IN 效率低，可改用 LEFT JOIN：

MySQL
编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载
DELETE p FROM pizzas p
LEFT JOIN ids_to_keep k ON p.id = k.id
WHERE k.id IS NULL;

? 清理与防护

-- 删除临时表
DROP TABLE ids_to_keep;

-- 添加唯一约束，杜绝未来重复（关键！）
ALTER TABLE pizzas 
ADD CONSTRAINT uk_codes_customer UNIQUE (codes, customer_id);

? 进阶优化：确保保留最小 ID（精确控制）

若需 100% 保证每组保留 id 最小的记录（而非依赖 INSERT IGNORE 的扫描顺序），可改用窗口函数（MySQL 8.0+）：

-- 直接生成待保留 ID 列表（无需临时表）
DELETE p FROM pizzas p
LEFT JOIN (
    SELECT id, 
           ROW_NUMBER() OVER (PARTITION BY codes, customer_id ORDER BY id) AS rn
    FROM pizzas
) ranked ON p.id = ranked.id AND ranked.rn > 1
WHERE ranked.id IS NOT NULL;

此语句直接标记每组中 rn > 1 的行（即非首条），并一次性删除，更简洁且语义明确。