1. 项目概述为什么删重不是“点一下就完事”的技术活在真实的数据工程现场我见过太多人把“删重”当成一个三分钟能搞定的 SQL 小技巧——写个DELETE ... WHERE ID NOT IN (SELECT MIN(ID) ...)回车一敲然后心满意足地去喝咖啡。结果呢十次里有七次要么删错了关键业务数据要么锁表两小时导致下游报表全挂要么删完发现主键冲突、外键断裂、历史订单关联丢失……最后还得从备份里捞数据加班到凌晨三点。这不是危言耸听这是我过去十年在金融、电商、SaaS 三类系统里亲手踩过的坑摞起来能当板凳坐。“SQL Remove Duplicates”这个标题听起来像教科书里的标准操作但现实远比语法复杂。它根本不是一个孤立的 SQL 语句问题而是一整套数据治理决策链你得先判断“什么是重复”——是全字段完全一致才算还是只要姓名手机号相同就算身份证号为空时怎么比时间戳毫秒级差异要不要忽略接着要问“删谁留谁”——按 ID 最小按创建时间最新按状态优先级比如“已付款”优先于“待支付”再往下是“怎么删才安全”——是直接 DELETE 还是先导出备份是单条执行还是分批处理会不会触发千万级大表的全表扫描有没有事务隔离级别导致的幻读风险最后还有“删完怎么验”——用 COUNT(*) 对不上怎么办关联表数据断链了怎么补这些环节任何一个没想透都可能让一次本该轻描淡写的清理变成一场生产事故。所以这篇内容不是给你罗列八种写法让你抄代码而是还原一个资深数据工程师面对真实脏数据时的完整思考路径。我会拆解每种方法背后的数据库引擎行为逻辑比如为什么 MySQL 5.7 不支持 CTE 删除而 8.0 可以、执行代价的量化估算比如NOT IN子查询在百万级数据上实际会生成多少临时表、跨数据库的兼容性陷阱比如 PostgreSQL 的USING语法在 SQL Server 里根本不存在以及最关键的——我在客户现场手把手带团队做数据清洗时总结出的六条铁律式操作守则。无论你是刚学完SELECT DISTINCT的新人还是正在为银行核心库设计去重方案的 DBA这里没有“通用万能解”只有基于场景、数据量、DBMS 版本和业务容忍度的务实选择。2. 核心思路拆解删重的本质是“可控的数据裁剪”不是暴力删除2.1 为什么不能只看语法必须理解数据库的“行生命周期”很多初学者以为DELETE FROM t WHERE id IN (...)就是简单地把匹配的行从磁盘上抹掉。错。在现代关系型数据库中删除操作背后是一整套复杂的存储引擎协作机制。以最常用的 InnoDBMySQL和 Heap TablePostgreSQL为例InnoDB 的行删除本质是“标记删除”它不会立即擦除磁盘块而是将该行的DB_ROW_ID标记为已删除并加入 purge 线程队列。这意味着① 删除后立刻SELECT COUNT(*)可能仍显示旧数量需等 purge 完成② 大量删除会导致 undo log 膨胀可能撑爆ibdata1③ 如果事务未提交其他会话在REPEATABLE READ隔离级别下仍能看到“已删”的行MVCC 快照。我曾在一个电商订单表上误删 50 万行结果 purge 线程卡住导致后续所有 INSERT 都被阻塞因为 undo log 满了无法分配新空间。PostgreSQL 的 VACUUM 机制更隐蔽它的DELETE是给行打上xmax时间戳标记为“过期”真正的物理回收要靠后台VACUUM进程。如果VACUUM没及时运行比如 autovacuum 被禁用表体积不减反增——因为新插入的行会追加到表尾而旧的“死亡行”还占着空间。我们有个客户报表库pg_total_relation_size(orders)显示 120GB但VACUUM FULL后只剩 35GB差额全是未清理的死亡行。SQL Server 的锁升级策略当删除行数超过 5000 行默认阈值SQL Server 会自动将行锁升级为页锁甚至表锁。这意味着一个DELETE FROM customers WHERE name IN (...)在 10 万行数据上执行可能瞬间锁死整个customers表导致所有查询排队等待。我们曾因此让一个实时风控系统中断服务 17 分钟。所以当你选择一种删重方法时你选的不仅是语法更是选择了某种存储引擎压力模型。ROW_NUMBER() CTE DELETE在 PostgreSQL 上是行级锁但在 SQL Server 上DELETE FROM CTE实际会转换为对底层表的扫描锁行为取决于查询优化器——这解释了为什么同一段代码在不同版本 SQL Server 上表现迥异。2.2 “保留哪一行”不是技术问题而是业务契约问题技术文档总说“保留最小 ID 的那行”但现实中ID 往往只是自增序列和业务意义零相关。我处理过一个医疗挂号系统patient_id是业务主键但表里有created_at挂号时间、updated_at最后修改时间、status预约/取消/完成三个关键字段。当时发现同patient_id有 3 条记录一条statuscancelled且updated_at是昨天一条statusconfirmed且updated_at是今天上午一条statusconfirmed且updated_at是今天下午。按 ID 最小留会留下已取消的脏数据。按created_at最小会留下过期的确认单。正确答案是按status优先级排序confirmed cancelled同状态则按updated_at最新。这需要ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY CASE status WHEN confirmed THEN 1 ELSE 2 END, updated_at DESC)—— 把业务规则硬编码进排序逻辑。另一个经典案例是用户资料表。字段有email,phone,name,last_login_time。重复判定逻辑是email相同即视为同一人强唯一但phone可能因换号变更。那么“保留哪一行”就变成优先保留last_login_time最近的那条因为它代表用户当前活跃状态若last_login_time相同则保留id最大的假设新注册用户信息更完整。这种规则无法用MIN(ID)或MAX(created_at)一句话概括它要求你把业务域知识翻译成 SQL 的ORDER BY子句。提示永远先和业务方确认“重复”的定义和“保留”的规则。我坚持一条铁律任何删重脚本上线前必须提供一份“拟删除行样本报告”包含至少 10 条将被删除的原始数据及对应保留行由业务负责人签字确认。这看似繁琐却避免了 90% 的事后扯皮。2.3 方法论分层查询去重 vs. 数据库去重是两种完全不同的工程目标这是最常被混淆的核心概念。原文提到“分两块检索去重和数据库去重”但没点透本质区别维度查询去重如SELECT DISTINCT数据库去重如DELETE作用对象结果集Result Set物理存储Data Pages影响范围仅本次查询可见所有后续查询、应用、ETL 流程永久生效性能开销内存排序/哈希通常 1s可能触发索引重建、undo log 写入、锁竞争耗时从秒到小时不等可逆性100% 可逆不改数据100% 不可逆除非有备份适用场景报表、BI 展示、临时分析数据治理、合规审计、存储优化举个实例某 SaaS 公司的用户行为日志表events每天新增 2000 万行其中约 0.3% 是前端重复上报双击触发两次埋点。如果用DELETE FROM events WHERE ...清理意味着每天要扫描 2000 万行、删除 6 万行、更新索引——这对 OLAP 场景是灾难。正确做法是在查询层用DISTINCT ON (user_id, event_type, timestamp::date)去重或在 ETL 入仓时用 Kafka Streams 做实时 dedup。数据库层只保留原始事实这是数据保真原则。反过来如果是一个客户主数据表customers因 ETL 故障导致同一客户被插入 5 次这时就必须数据库层删重——因为下游所有订单、发票、营销活动都依赖这个表的主键留着重复行会导致关联爆炸一个客户产生 5 倍订单量。所以选择哪种方法首先要回答“这个重复是数据使用过程中的视图失真还是数据本身存在结构性缺陷”前者交给查询层后者才动数据库。3. 实操细节解析每种方法的引擎级原理与致命陷阱3.1SELECT DISTINCT最安全的“假删重”但也是最容易被滥用的SELECT DISTINCT的原理极其简单数据库在内存中维护一个哈希表Hash Table对每一行计算所有 SELECT 字段的哈希值若哈希值已存在则跳过否则插入并返回。它的优势是零风险、零锁、零 IO 写入但陷阱藏在细节里。陷阱一DISTINCT作用域是“整行”不是“逻辑实体”看这个例子SELECT DISTINCT name, email, phone FROM customers;如果存在两条记录(张三, zhangx.com, 138****1234)和(张三, zhangx.com, NULL)DISTINCT会认为这是两行不同数据因为phone值不同全部返回。但业务上phone为空很可能表示“未知”应视为同一人。此时DISTINCT就失效了。解决方案是用COALESCE(phone, )统一空值或改用GROUP BY name, email配合MAX(COALESCE(phone, ))聚合。陷阱二DISTINCT在大数据集上内存爆炸InnoDB 默认sort_buffer_size2MB当 DISTINCT 字段组合的唯一值超过内存容量时会退化为外部排序External Sort将中间结果写入磁盘临时文件。我测试过对 1 亿行用户表SELECT DISTINCT city, province在 4GB 内存服务器上tmpdir目录瞬间生成 12GB 临时文件IO 等待时间飙升。此时GROUP BY配合索引往往更快——因为GROUP BY可利用索引有序性避免全量哈希。实操心得永远给DISTINCT字段建联合索引。例如CREATE INDEX idx_name_email ON customers(name, email);这样SELECT DISTINCT name, email可走索引覆盖扫描Index Covering无需回表。对超大表用LIMIT加采样验证SELECT DISTINCT name, email FROM customers LIMIT 1000;先看去重效果是否符合预期再执行全量。DISTINCT无法处理“部分字段重复”的场景。比如要查“所有有重复邮箱的客户”必须用GROUP BY email HAVING COUNT(*) 1DISTINCT在这里完全无用。3.2GROUP BY HAVING识别重复的黄金标准但HAVING不是万能钥匙GROUP BY是识别重复的基石其原理是数据库按指定字段分组每组生成一个聚合上下文COUNT(*)统计每组行数。HAVING COUNT(*) 1则筛选出重复组。这是最可靠、最通用的识别方式。关键原理HAVING过滤的是分组结果WHERE过滤的是原始行新手常犯错误是写WHERE COUNT(*) 1这会报错因为COUNT(*)是聚合函数只能在HAVING子句中使用。正确写法-- ✅ 正确先分组再过滤组 SELECT email, COUNT(*) as cnt FROM customers GROUP BY email HAVING COUNT(*) 1; -- ❌ 错误WHERE 不能用聚合函数 SELECT email FROM customers WHERE COUNT(*) 1 GROUP BY email;致命陷阱GROUP BY的 NULL 处理在 SQL 标准中NULL NULL为UNKNOWN但GROUP BY规定所有NULL值属于同一组。这意味着如果表中有 100 行email IS NULLGROUP BY email会把它们全归为一组HAVING COUNT(*) 1会命中。但业务上“邮箱为空”和“邮箱为 null”可能是完全不同的含义比如“未填写” vs “拒绝提供”。解决方案是显式分离-- 将 NULL 单独分组非 NULL 正常分组 SELECT CASE WHEN email IS NULL THEN NULL_EMAIL ELSE email END as email_group, COUNT(*) as cnt FROM customers GROUP BY CASE WHEN email IS NULL THEN NULL_EMAIL ELSE email END HAVING COUNT(*) 1;性能优化用EXISTS替代IN子查询当需要“找出所有重复邮箱的客户详情”时常见写法是-- ❌ 低效子查询可能多次执行 SELECT * FROM customers WHERE email IN ( SELECT email FROM customers GROUP BY email HAVING COUNT(*) 1 );更好的方式是EXISTS-- ✅ 高效半连接找到即停 SELECT c1.* FROM customers c1 WHERE EXISTS ( SELECT 1 FROM customers c2 WHERE c2.email c1.email AND c2.id ! c1.id );EXISTS在找到第一个匹配行后立即返回TRUE而IN子查询需生成完整结果集。在 1000 万行表上后者可能慢 3 倍以上。3.3ROW_NUMBER() CTE精准控制的利器但版本和锁是隐形杀手ROW_NUMBER()是现代删重的首选因为它允许你精确指定“保留哪一行”。其原理是PARTITION BY定义分组边界ORDER BY定义组内排序规则ROW_NUMBER()为每行分配 1,2,3... 序号。版本陷阱MySQL 5.7 不支持DELETE FROM CTEMySQL 在 8.0 才引入 CTE 和窗口函数。在 5.7 中以下写法会报错-- ❌ MySQL 5.7 报错You cant specify target table customers for update in FROM clause WITH CTE AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM customers ) DELETE FROM customers WHERE id IN (SELECT id FROM CTE WHERE rn 1);正确解法是用派生表Derived Table-- ✅ MySQL 5.7 兼容 DELETE FROM customers WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM customers ) t WHERE rn 1 );锁陷阱DELETE FROM CTE在 SQL Server 上可能锁全表SQL Server 的 CTEDELETE实际执行计划中如果ORDER BY字段无索引优化器可能选择聚集索引扫描Clustered Index Scan导致全表锁定。我遇到过一个案例PARTITION BY user_id ORDER BY created_at DESC但created_at无索引100 万行表锁了 47 秒。解决方案确保ORDER BY字段有高效索引。对上面的例子建INDEX idx_user_created ON customers(user_id, created_at DESC)。实操心得ORDER BY中慎用函数。ORDER BY UPPER(name)会导致索引失效必须建函数索引如 PostgreSQL 的CREATE INDEX idx_name_upper ON customers(UPPER(name))。PARTITION BY字段越多内存消耗越大。对PARTITION BY a,b,c,d数据库需为每个唯一组合维护一个排序缓冲区。建议最多 3 个字段。测试时永远加AND rn 10限制SELECT * FROM CTE WHERE rn 10先看前 10 行序号分配是否符合预期再删。3.4DELETE Subquery兼容性之王但NOT IN是深渊DELETE ... WHERE id NOT IN (SELECT MIN(id) ...)是最古老也最广泛兼容的方法但它有一个致命缺陷NOT IN遇到NULL会返回空结果集。看这个经典陷阱-- 假设 customers 表中有一行 emailNULL SELECT MIN(id) FROM customers GROUP BY email; -- 返回结果可能包含 NULL因为 GROUP BY 时 NULL 被分到一组MIN(id) 对该组计算 -- 但如果该组只有一行MIN(id) 就是那个 id如果多行MIN(id) 是最小 id -- 关键是如果子查询结果中 ANY 一行是 NULL整个 NOT IN 判断为 UNKNOWNDELETE 不生效更危险的是这个错误不会报错只会静默失败。我亲眼见过一个 DBA 用此法删重执行后SELECT COUNT(*)显示行数没变排查半小时才发现子查询里有NULL。绝对安全的替代方案NOT EXISTS-- ✅ 安全不受 NULL 影响 DELETE FROM customers c1 WHERE NOT EXISTS ( SELECT 1 FROM customers c2 WHERE c2.email c1.email AND c2.id c1.id ); -- 解释对 c1 的每一行检查是否存在 c2 行满足 email 相同且 id c1.id -- 如果存在且 c2.id c1.id说明 c1 是该 email 组中 id 最小的保留 -- 如果不存在这样的 c2说明 c1 的 id 比所有同 email 行都小不可能所以逻辑是反的 -- 正确理解保留的是“不存在比自己 id 更小的同 email 行”的行即最小 id 行性能真相NOT IN子查询在大表上可能比JOIN慢 10 倍原因在于NOT IN无法使用哈希连接Hash Join只能走嵌套循环Nested Loop。对 100 万行主表和 10 万行子查询结果需执行 100 万 × 10 万次比较。而LEFT JOIN ... WHERE right.id IS NULL可启用哈希连接-- ✅ 推荐哈希连接O(nm) 复杂度 DELETE c1 FROM customers c1 LEFT JOIN ( SELECT email, MIN(id) as min_id FROM customers GROUP BY email ) c2 ON c1.email c2.email AND c1.id c2.min_id WHERE c2.min_id IS NULL;4. 完整实操流程从识别到验证的七步军规4.1 第一步环境准备与风险评估30 分钟决定成败在任何删重操作前必须完成这三项检查缺一不可备份验证不是“有没有备份”而是“备份能否恢复”。执行一次mysqldump --single-transaction导出然后用mysql -e SHOW TABLE STATUS LIKE customers对比源库和备份库的Rows、Data_length是否一致。我见过备份脚本因权限问题静默失败备份文件是空的。锁粒度预估用EXPLAIN FORMATJSON分析你的删重语句。重点关注key是否用索引、rows扫描行数、typeALL表示全表扫描危险。对DELETE FROM t WHERE x IN (...)如果x无索引rows会显示表总行数。业务影响评估查information_schema.INNODB_TRXMySQL或pg_stat_activityPostgreSQL确认当前是否有长事务。删重期间禁止任何INSERT/UPDATE操作否则可能产生新的重复。我们曾因未暂停上游 ETL删重脚本执行中又插入了重复数据导致二次清理。注意永远在业务低峰期操作如凌晨 2-4 点并提前邮件通知所有相关方明确告知“XX 表将不可写 X 分钟”。4.2 第二步精准识别重复15 分钟拒绝模糊不要只用GROUP BY email要构建业务语义化的重复判定规则。以电商用户表为例-- ✅ 业务规则邮箱相同 OR 手机相同 OR 姓名相同且身份证号相同 SELECT COALESCE(email, ) as email_key, COALESCE(phone, ) as phone_key, CONCAT(COALESCE(name, ), |, COALESCE(id_card, )) as name_id_key, COUNT(*) as dup_cnt FROM customers GROUP BY COALESCE(email, ), COALESCE(phone, ), CONCAT(COALESCE(name, ), |, COALESCE(id_card, )) HAVING COUNT(*) 1;关键技巧用GROUP_CONCAT查看重复组详情-- 查看每个重复组的具体是哪些 ID方便人工复核 SELECT GROUP_CONCAT(id ORDER BY id) as ids, GROUP_CONCAT(CONCAT(name, , email, ) ORDER BY id) as details, COUNT(*) as cnt FROM customers GROUP BY COALESCE(email, ) HAVING COUNT(*) 1 LIMIT 10;输出类似ids: 1001,1005,1009, details: 张三zx.com,李四zx.com,王五zx.com一目了然。4.3 第三步小批量测试20 分钟血的教训永远不要直接全量执行。取一个最小可行集MVP测试-- 创建测试副本不锁原表 CREATE TABLE customers_test AS SELECT * FROM customers LIMIT 1000; -- 在副本上执行删重 WITH CTE AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC, id DESC) rn FROM customers_test ) DELETE FROM customers_test WHERE id IN (SELECT id FROM CTE WHERE rn 1); -- 验证重复数应为 0 SELECT email, COUNT(*) FROM customers_test GROUP BY email HAVING COUNT(*) 1;必须验证的三个指标SELECT COUNT(*)前后对比应减少SELECT COUNT(DISTINCT email)前后对比应不变或微增SELECT MIN(id), MAX(id)确认 ID 连续性删重不应影响 ID 序列4.4 第四步分批执行核心步骤时间取决于数据量对百万级以上数据必须分批。单次删除 10 万行是安全阈值InnoDB 默认innodb_lock_wait_timeout5010 万行删除通常 30s。推荐分批策略按主键范围切片-- 获取最小和最大 ID SELECT MIN(id), MAX(id) FROM customers; -- 分批删除每次处理 10 万 ID 范围 DELETE FROM customers WHERE id BETWEEN 100000 AND 199999 AND email IN ( SELECT email FROM ( SELECT email FROM customers WHERE id BETWEEN 100000 AND 199999 GROUP BY email HAVING COUNT(*) 1 ) t ) AND id NOT IN ( SELECT MIN(id) FROM customers WHERE id BETWEEN 100000 AND 199999 GROUP BY email );自动化脚本模板Bash MySQL#!/bin/bash START_ID1 END_ID1000000 BATCH_SIZE100000 while [ $START_ID -le $END_ID ]; do echo Processing batch: $START_ID to $(($START_ID $BATCH_SIZE - 1)) mysql -u root -p$PASS db_name -e DELETE FROM customers WHERE id BETWEEN $START_ID AND $(($START_ID $BATCH_SIZE - 1)) AND email IN ( SELECT email FROM ( SELECT email FROM customers WHERE id BETWEEN $START_ID AND $(($START_ID $BATCH_SIZE - 1)) GROUP BY email HAVING COUNT(*) 1 ) t ) AND id NOT IN ( SELECT MIN(id) FROM customers WHERE id BETWEEN $START_ID AND $(($START_ID $BATCH_SIZE - 1)) GROUP BY email ); START_ID$(($START_ID $BATCH_SIZE)) sleep 1 # 避免瞬时压力 done4.5 第五步索引重建与碎片整理10 分钟常被忽视删重后索引会产生大量“空洞”。OPTIMIZE TABLEMySQL或VACUUM FULLPostgreSQL是必需的。MySQL InnoDBOPTIMIZE TABLE customers会重建聚簇索引释放空间。注意它会锁表需在维护窗口执行。PostgreSQLVACUUM FULL customers会重写整个表消除碎片。但它是排他锁且会阻塞所有操作。更温和的方式是VACUUM customers清理死亡行CLUSTER customers USING idx_email按索引物理重排。验证效果-- MySQL 查看碎片率 SELECT table_name, round(((data_length index_length) / 1024 / 1024), 2) as size_mb, round((data_free / 1024 / 1024), 2) as free_mb FROM information_schema.tables WHERE table_schema db_name AND table_name customers; -- free_mb 0 表示有碎片4.6 第六步交叉验证20 分钟建立信任删重不是结束而是验证的开始。必须用三种独立方法确认结果一致性验证方法SQL 示例通过标准方法一COUNT(*) 对比SELECT COUNT(*) FROM customers_old; SELECT COUNT(*) FROM customers_new;差值 重复行数从第二步识别结果得出方法二DISTINCT COUNT 对比SELECT COUNT(DISTINCT email) FROM customers_old; SELECT COUNT(DISTINCT email) FROM customers_new;两者应相等去重后唯一值不变方法三抽样比对SELECT * FROM customers_old WHERE email IN (ax.com,by.com) ORDER BY id; SELECT * FROM customers_new WHERE email IN (ax.com,by.com) ORDER BY id;新表中每个 email 只有一行且是旧表中updated_at最新的那一行终极验证业务逻辑回归跑一个核心业务 SQL比如“昨日新增付费用户数”-- 删重前 SELECT COUNT(*) FROM orders o JOIN customers c ON o.customer_id c.id WHERE o.status paid AND DATE(o.created_at) 2023-10-01; -- 删重后 -- 结果必须完全一致如果变化说明外键关联被破坏。4.7 第七步预防机制落地长期价值删重是救火预防才是防火。必须立即部署以下三道防线唯一约束Immediate Protection-- 对邮箱加唯一索引允许 NULL但非 NULL 值唯一 CREATE UNIQUE INDEX uk_email ON customers(email) WHERE email IS NOT NULL; -- 对手机号同理 CREATE UNIQUE INDEX uk_phone ON customers(phone) WHERE phone IS NOT NULL;应用层校验Defense in Depth在用户注册 API 中增加幂等性检查# 伪代码 def register_user(email, phone): if db.exists(SELECT 1 FROM customers WHERE email %s OR phone %s, email, phone): raise DuplicateError(Email or phone already exists) # 插入逻辑定期巡检脚本Proactive Monitoring每日凌晨执行-- 将重复检测结果写入监控表 INSERT INTO data_quality_alerts (table_name, check_type, alert_level, details, created_at) SELECT customers, duplicate_email, HIGH, CONCAT(Found , COUNT(*), duplicate emails), NOW() FROM customers GROUP BY email HAVING COUNT(*) 1;配合企业微信/钉钉机器人告警。5. 常见问题与排查技巧实录那些文档里不会写的坑5.1 问题速查表高频故障与根因定位现象可能根因排查命令解决方案删重后COUNT(*)不变NOT IN子查询含NULL或DELETE语句WHERE条件写错SELECT * FROM (子查询) t WHERE t.xxx IS NULL;改用NOT EXISTS用EXPLAIN确认WHERE条件是否命中索引执行超时被 kill大表无索引导致全表扫描或锁等待超时SHOW PROCESSLIST;查看State是否为Sending data或Locked添加PARTITION BY字段的索引分批执行调大innodb_lock_wait_timeout删重后关联查询变慢删除操作导致索引统计信息过期ANALYZE TABLE customers;执行ANALYZE TABLE更新统计信息出现主键冲突错误删重脚本误删了外键引用的主表行SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME customers;检查外键约束删重前先备份关联表ROW_NUMBER()序号分配异常ORDER BY字段有重复值导致序号随机分配SELECT email, updated_at, COUNT(*) FROM customers GROUP BY email, updated_at HAVING COUNT(*) 1;在ORDER BY中加入唯一字段如ORDER BY updated_at DESC, id DESC5.2 独家避坑技巧来自血泪现场的经验技巧一用SELECT ... INTO OUTFILE做“可逆删重”对于不敢直接DELETE的核心表用导出-清理-导入三步法-- 1. 导出所有“保留行” SELECT * FROM customers WHERE id IN ( SELECT MIN(id) FROM customers GROUP BY email ) INTO OUTFILE /tmp/customers_clean.csv FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n; -- 2. 创建新表结构相同 CREATE TABLE customers_new LIKE customers; -- 3. 导入干净数据 LOAD DATA INFILE /tmp/customers_clean.csv INTO TABLE customers_new FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n; -- 4. 原子化切换零停机 RENAME TABLE customers TO customers_old, customers_new TO customers;全程不锁原表且customers_old保留作回滚依据。技巧二pt-duplicate-key-checker工具的神级用法Percona Toolkit 的pt-duplicate-key-checker不仅能找重复还能生成修复 SQL# 找出所有重复索引冗余索引 pt-duplicate-key-checker --hostlocalhost --userroot hlocal_db # 生成删除重复行的 SQL需配合 --dry-run 测试 pt-duplicate-key-checker --hostlocalhost --userroot --drop-duplicate-rows hlocal_db,Dyour_db,tcustomers它比手写 SQL 更可靠因为内置了 MySQL 版本兼容性判断。技巧三用BINLOG追踪误删最后救命稻草如果DELETE误操作且无备份可从 binlog 恢复# 解析 binlog找到误
