1. 项目概述从“查重”到“治重”的数据治理实战在数据库的日常运维和数据分析工作中重复数据就像隐藏在角落里的“数据幽灵”它们悄无声息地消耗着存储空间扰乱统计结果的准确性甚至可能引发业务逻辑的混乱。无论是用户表中的重复注册、订单表中的异常重复下单还是日志表中的冗余记录识别并处理这些重复项是每一位数据工程师、后端开发乃至数据分析师必须掌握的核心技能。今天我们就来深入聊聊MySQL中重复数据查询的那些SQL这不仅仅是写几条SELECT语句那么简单它背后关联着数据模型设计、查询性能优化以及最终的数据清洗策略。很多人一提到查重第一反应就是GROUP BY和HAVING COUNT(*) 1这没错但这是最基础的“诊断”环节。一个完整的“治重”流程应该包括“发现重复 - 分析重复原因 - 安全清理或合并”。我们将从最简单的单字段重复查起逐步深入到多字段组合重复、部分字段重复等复杂场景并重点探讨在千万级大表上如何高效查重而不拖垮数据库以及删除重复数据时如何确保“删得干净、不留后患”。无论你是正在处理一个棘手的脏数据问题还是想在面试中清晰阐述数据去重的方案这篇文章都能给你提供一套可直接落地的实战指南。2. 核心思路与方案选型如何定义“重复”动手写SQL之前我们必须先明确一个根本问题在你的业务场景中什么才算“重复数据”定义不清后续所有工作都可能南辕北辙。2.1 定义重复的三种常见维度1. 完全重复记录所有字段的值都一模一样的行。这种情况通常出现在没有主键约束或数据导入错误时。查询思路最简单但实际业务中较少见除非数据来源极度不规范。2. 业务键重复这是最常见的重复类型。即表中定义了唯一业务标识的字段或字段组合出现了重复。例如用户表中的手机号或邮箱商品表中的商品编码。即使其他字段如用户名、地址不同只要业务键相同从业务逻辑上看就是重复数据。处理这类重复是重点。3. 逻辑重复记录没有明确的唯一键但根据业务规则某些字段组合在一起标识了唯一性。例如在一个订单明细表中“订单号商品SKU”应该唯一如果出现了两行相同的“订单号SKU”可能就是重复添加了商品。2.2 查重方案的核心权衡精度 vs 性能明确了重复的定义后我们需要选择查询方法。不同的方法在结果精确度和执行性能上差异巨大。GROUP BYHAVING: 最经典、最直观的方法。通过分组和聚合计数找出重复组。优点是逻辑清晰适用于所有场景。缺点是在大表上如果分组字段没有索引性能可能成为瓶颈。自连接Self-Join: 通过将表与自身连接来比较行与行之间的关系。在处理复杂的、基于范围的重复如时间上非常接近的记录时很有用但SQL写法稍复杂性能也需谨慎评估。窗口函数Window Functions: MySQL 8.0及以上版本提供了强大的窗口函数如ROW_NUMBER()。这是目前处理“标记并保留一条”这类需求最优雅、最高效的方式特别适合在查询阶段就为删除操作做好准备。注意在选择方案前务必对目标表进行SELECT COUNT(*)操作并评估数据量。对于百万行以上的表盲目执行没有索引支持的GROUP BY查询可能导致数据库临时表过大严重消耗内存和磁盘I/O甚至引发线上服务卡顿。对于大表查重建议在业务低峰期进行或先在从库上执行。3. 核心SQL模式详解与实操要点下面我们以一张模拟的users表为例进行实战演练。表结构如下CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), email VARCHAR(100), phone VARCHAR(20), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );假设我们已经不慎导入了重复数据。3.1 基础篇单字段重复查询场景找出email字段重复的所有用户。方法1使用 GROUP BY 和 HAVING这是最标准的做法。SELECT email, COUNT(*) AS duplicate_count FROM users GROUP BY email HAVING COUNT(*) 1 ORDER BY duplicate_count DESC;执行过程与要点GROUP BY email数据库会扫描全表或使用email上的索引将所有email相同的行归到一组。COUNT(*)计算每一组的行数。HAVING COUNT(*) 1过滤掉行数为1的组只保留重复的组。结果展示了每个重复的邮箱及其出现的次数。方法2使用 EXISTS 子查询这种方法更适合当你需要查看重复记录的完整行信息时。SELECT * FROM users u1 WHERE EXISTS ( SELECT 1 FROM users u2 WHERE u1.email u2.email AND u1.id ! u2.id -- 确保不是同一条记录自身比较 );要点解析对于users表中的每一行u1子查询去检查是否存在另一行u2满足邮箱相同但ID不同。如果存在则u1就是重复记录之一。这种方法返回的是所有重复行的完整数据方便后续查看。但请注意如果email字段没有索引这个查询的性能会非常差因为它是O(n²)级别的复杂度。3.2 进阶篇多字段组合重复查询场景我们认为username和phone的组合才能唯一标识一个用户业务逻辑现在要找出这对组合重复的记录。SQL只需稍作修改将GROUP BY和WHERE条件中的单个字段改为多个字段即可。-- 使用 GROUP BY SELECT username, phone, COUNT(*) AS duplicate_count FROM users GROUP BY username, phone HAVING COUNT(*) 1; -- 使用 EXISTS SELECT * FROM users u1 WHERE EXISTS ( SELECT 1 FROM users u2 WHERE u1.username u2.username AND u1.phone u2.phone AND u1.id ! u2.id );实操心得多字段查重时字段顺序会影响使用索引的效率。如果为(username, phone)建立了联合索引那么GROUP BY username, phone的效率会很高。如果索引是(phone, username)则可能无法完全利用。在设计查询和索引时需要保持一致。3.3 高效篇使用窗口函数精准定位MySQL 8.0窗口函数是处理重复数据的“瑞士军刀”它不仅能找出重复还能直接为删除操作做好标记。场景对于email重复的记录我们只想保留id最小或创建时间最早的那一条删除其他重复项。步骤1使用ROW_NUMBER()标记重复行SELECT id, email, username, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS row_num FROM users;原理解读PARTITION BY email按照email字段进行分区相同的email会被分到同一个窗口内。ORDER BY id ASC在每个窗口内按照id升序排列。ROW_NUMBER()为窗口内的每一行分配一个唯一的连续序号从1开始。结果中对于每个重复的emailid最小的那行row_num为1第二小的为2以此类推。步骤2筛选出需要删除的行接下来我们可以很容易地找出所有row_num 1的行这些就是我们要删除的重复记录每个重复组里保留第一条。WITH duplicate_marked AS ( SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS row_num FROM users ) SELECT * FROM duplicate_marked WHERE row_num 1;为什么这种方法更优清晰直观逻辑上一步到位标记和筛选分离SQL易于理解和维护。性能更佳通常只需要对表进行一次扫描如果email有索引效率更高比某些自连接或子查询写法更高效。灵活性高通过修改ORDER BY子句如ORDER BY created_at DESC可以轻松实现“保留最新的一条”等其他业务规则。4. 从查询到删除安全数据清洗实操查出重复数据只是第一步安全地删除它们才是真正的挑战。直接DELETE风险极高务必遵循“先备份再验证后删除”的黄金法则。4.1 完整安全删除流程第1步创建备份表在操作前将可能受影响的数据完整备份。-- 备份整个表如果表不大 CREATE TABLE users_backup_20240517 AS SELECT * FROM users; -- 或者只备份即将被删除的重复数据 CREATE TABLE users_duplicates_to_delete AS WITH duplicate_marked AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS row_num FROM users ) SELECT u.* FROM users u INNER JOIN duplicate_marked dm ON u.id dm.id WHERE dm.row_num 1;第2步验证待删除数据执行删除前的查询仔细核对结果集确保没有误伤。-- 再次确认即将删除的数据 SELECT COUNT(*) FROM users_duplicates_to_delete; -- 抽样检查几条 SELECT * FROM users_duplicates_to_delete LIMIT 5;第3步执行删除操作推荐使用JOIN方式删除逻辑更清晰。-- 方法A使用子查询 (传统) DELETE FROM users WHERE id IN ( SELECT id FROM users_duplicates_to_delete ); -- 方法B使用JOIN (推荐尤其对于MySQL有时性能更好) DELETE u FROM users u INNER JOIN users_duplicates_to_delete d ON u.id d.id;第4步验证删除结果删除后立即检查数据状态。-- 检查是否还有重复 SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) 1; -- 检查总行数是否符合预期 SELECT COUNT(*) FROM users;4.2 高级场景有外键关联时的删除如果users表是主表被其他表如orders表其中有user_id字段外键关联直接删除用户会导致外键约束错误。解决方案级联删除如果业务允许可以在创建外键时设置ON DELETE CASCADE。这样删除用户时其所有订单也会被自动删除。此操作破坏性极大务必谨慎评估。先处理关联数据更安全的做法是先找出重复用户对应的订单将这些订单的user_id更新为要保留的那个用户ID然后再删除重复用户。-- 假设我们要保留每个重复邮箱中id最小的用户 WITH keep_user AS ( SELECT email, MIN(id) AS keep_id FROM users GROUP BY email HAVING COUNT(*) 1 ) -- 先更新关联表将重复用户的订单指向保留的用户 UPDATE orders o INNER JOIN users u ON o.user_id u.id INNER JOIN keep_user k ON u.email k.email AND u.id ! k.keep_id SET o.user_id k.keep_id; -- 然后再删除重复用户使用前面提到的方法这个过程需要在事务中完成以保证数据一致性。5. 性能优化与疑难问题排查5.1 大表查重性能优化指南当表数据量巨大时查重操作必须格外小心。索引是王道确保GROUP BY、PARTITION BY或WHERE JOIN条件中用到的字段已经建立了合适的索引。对于多字段查重考虑建立联合索引。分批处理对于亿级数据不要试图一次性处理。可以按时间范围如created_at或ID范围进行分批查重和删除。-- 按时间分批查询重复 SELECT email, COUNT(*) FROM users WHERE created_at BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY email HAVING COUNT(*) 1;使用临时表/汇总表如果重复判断逻辑复杂且需要频繁查询可以考虑定期将汇总结果如所有重复的邮箱列表计算出来存入一张小型的临时表或物化视图中后续操作直接查询这个小表。利用覆盖索引如果查询只需要重复字段和计数确保索引包含所有需要的字段这样数据库可以直接从索引中获取数据避免回表极大提升速度。-- 为email建立索引查询就很快 CREATE INDEX idx_email ON users(email); -- 如果查询是 SELECT email, COUNT(*)... GROUP BY email且email有索引这就是覆盖索引扫描5.2 常见问题与排查技巧实录问题1查询速度极慢数据库负载飙升。排查使用EXPLAIN分析查询计划。重点关注type列是否为ALL全表扫描rows列预估扫描行数是否巨大。解决为分组字段添加索引。增加WHERE条件限制数据范围。在从库或测试环境执行。优化服务器临时表空间配置tmp_table_size,max_heap_table_size防止分组时在磁盘创建临时表。问题2删除重复数据时报错“Lock wait timeout exceeded”。原因删除操作锁定了大量数据行与正在进行的其他事务产生锁竞争超时。解决在业务绝对低峰期操作。分批删除每次删除少量数据如1000条并提交事务。使用pt-archiver或gh-ost等在线DDL工具进行低影响删除对于超大规模表。问题3GROUP BY查询结果中COUNT数远大于预期。排查检查字段中是否包含大量NULL值。在SQL中NULL NULL的结果是UNKNOWN假但GROUP BY会将所有NULL值归为一组。验证SELECT COUNT(*) FROM users WHERE email IS NULL;处理根据业务逻辑决定是否将NULL视为有效值参与去重。如果不需要可以在查询中过滤掉NULLWHERE email IS NOT NULL。问题4使用窗口函数删除后自增ID不连续了。说明这是正常现象。MySQL的AUTO_INCREMENT机制不会因为数据删除而回溯填充已使用的ID值以保证性能和数据复制的一致性。ID的唯一性比连续性更重要。如果业务强依赖连续ID需要考虑其他方案如使用业务流水号而非技术主键。处理重复数据是一个系统工程从精准的查询到安全的删除每一步都需要对业务逻辑和数据特性有深刻理解。最好的“治重”永远是“防重于治”在应用层和数据库约束层UNIQUE KEY做好预防远比事后清洗要轻松和可靠得多。但在复杂的现实世界中掌握这套从诊断到治疗的完整方法无疑能让你在面对“数据幽灵”时更加从容。