企业官网建设流程全解析

我处理过不少PostgreSQL的脏数据问题，说句实话，“重复数据”这四个字背后藏着的坑，远比新手想象的多。很多人在第一步就搞错了，不是不会写SQL，而是没搞清楚“什么样的重复才算重复”，结果要么漏删，要么误删，要么把整张表锁死，生产环境直接告警。

这篇东西，我不打算给你讲那些教科书式的理论。我就结合我自己在项目里踩过的坑、补过的漏，把“PostgreSQL如何查找重复数据”和“如何安全删除重复数据”这两件事，拆开揉碎了讲清楚，从判断逻辑到实操SQL，再到生产环境的高危操作姿势，一次说透。

1. 动手之前，先搞清楚“重复数据”到底指什么

在写任何一条SQL之前，我们得先达成一个共识：什么样的数据算是重复？是某一行完全一样，还是某几个关键字段一样？这个定义没定清楚，后面所有操作都是盲人摸象。

1.1 重复的两种最常见形态

第一种是整行完全重复。这种通常是因为程序端重复提交、导入数据时脚本跑了两遍、或者两个接口同时写了一张表导致的。表结构有主键的话不太可能出现整行重复，但如果你建的表没有主键（别笑，生产环境里真的很多这种表），或者主键是自增ID，那么除了ID以外所有字段都一样的行就会堂而皇之的存在。

第二种是某个业务字段重复。这种更隐蔽，也更危险。比如用户表里的email字段，按理说一个邮箱只能注册一次，但由于代码里没加唯一约束、或者历史数据迁移过程中出了问题，同一邮箱对应了多个账号。这时候如果没有id字段，两行就是完全一样的，但如果有id，它们只是业务逻辑上重复，物理上并不相同。

这两种情况的处理逻辑完全不同。整行重复可以直接删除，最多就是留一行；而业务字段重复，你得先决定“保留哪一行”，是按最早创建时间留，还是按最新更新时间留，或者按ID最小留，这个规则必须提前定好，不能等SQL跑完才发现留错了数据。

提示：哪怕现在只是临时清理数据，也强烈建议先想清楚这两个问题再动手。

1.2 为什么PostgreSQL里没有现成的“去重”按钮

用过Excel的同学都知道，删除重复项就是一个按钮的事情。但到了PostgreSQL里，为什么没有类似的语法，比如DELETE DUPLICATE这样的东西？

原因在于关系型数据库的核心理论——集合操作。SQL处理数据是基于集合的，而集合本身是不允许重复元素的。理论上，一张设计合理的表就应该通过主键或唯一约束来保证数据的唯一性，重复数据本身就是一种“违反了设计约定”的异常产物。

所以数据库不会提供一个“默认去重”功能，因为它根本不知道你视作重复的依据是什么。它只能给你提供“分组、排序、窗口函数、行号”这些基础能力，由你自己组合出符合业务逻辑的去重方案。说白了，工具给你了，怎么用是你的事。

2. 查找重复数据：从笨办法到高效方法

既然要处理，第一件事肯定是先查出来。我把常见的情况分成三类，从单一字段查重、多字段联合查重、以及整行重复的查找，每一类都有对应的标准SQL，你可以直接拿去用，改一下表名和字段名就行。

2.1 单字段重复查找

比如现在有一张用户表users，里面有id, email, name三个字段，我想找出email重复的所有记录。

最简单粗暴的思路是GROUP BY ... HAVING COUNT(*) > 1：

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

这一步可以快速定位哪些邮箱重复了，但它只返回email和数量，并没有把这些重复行的完整信息展示出来。比如你想看看重复的都是谁、什么时候创建的，这个SQL就不够用。

所以更符合排查需求的写法是：用子查询查出重复的email，然后反查到完整行记录。

SELECT * FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ) ORDER BY email;

这个写法比较直观，性能也还可以，如果email字段建了索引，子查询的效率会很高，全表扫描的开销不会太大。如果表特别大，几十万上百万行的级别，建议加上索引再跑。

注意：子查询里的HAVING COUNT(*) > 1就是“重复”的定义——出现次数大于1。要根据业务调整这个条件，比如有些业务允许重复2次，超过3次才算异常，那就改成HAVING COUNT(*) > 2。

另一种更“高级”的查找方式是用窗口函数ROW_NUMBER()。我之所以说高级，不只是因为写法不同，而是它后续可以直接复用，删除重复数据也是基于同一套逻辑，查和删之间可以无缝衔接：

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users ) t WHERE t.rn > 1;

这条SQL的思路是：按email分组，组内按id从小到大排序，标上序号。序号为1的是每组里id最小的那条，也就是你预期保留的那条，rn大于1的都是重复的。它顺便就把“保留哪条”的规则一并实现了，非常漂亮。

2.2 多字段联合查重

很多时候单一字段无法判定重复，比如订单表里只有“用户ID + 商品ID + 下单时间”三个字段合在一起才能确定唯一性。这种就需要在GROUP BY中把所有判定字段都列出来，或者在PARTITION BY中把所有判定字段都加上。

举个例子，订单明细表order_items有order_id, product_id, quantity字段，一个订单里同一个商品理论上只应该出现一次，但由于接口重复推送，出现了同订单同商品多条记录：

SELECT order_id, product_id, COUNT(*) FROM order_items GROUP BY order_id, product_id HAVING COUNT(*) > 1;

多字段查重的核心注意点：字段顺序不影响结果，但所有参与判定的字段必须在GROUP BY中和SELECT的普通字段中一致，否则会报错——这是SQL标准的规定，PostgreSQL执行得尤其严格。

2.3 整行完全重复的查找

如果一张表连主键都没有，而且你想要找的是完全一模一样的行，那处理起来就很微妙。PostgreSQL里有个隐藏字段ctid，它表示每一行在物理存储上的位置。不同行即使内容完全一样，它们的ctid也一定不同。

所以查找整行重复的方式，就是按所有业务字段分组，然后看组内行数：

SELECT * FROM my_table t WHERE t.ctid IN ( SELECT ctid FROM ( SELECT ctid, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY ctid) AS rn FROM my_table ) s WHERE s.rn > 1 );

这里PARTITION BY后面要罗列这张表的全部字段。如果字段很多，写起来会有点痛苦，但这是唯一能准确判断“整行完全相同”的办法。因为只要有任何一个字段不一样，就不算重复——这就回到了我对开头那个问题的解释：重复的定义，完全取决于你SELECT了哪些字段。

3. 删除重复数据：不同的策略，不同的代价

查出来只是开始，删才是重头戏。很多初学者第一次删重复数据，直接就写DELETE FROM table WHERE 重复条件，结果发现把重复的行全删了，连保留的那一条也没了。正确的做法有多种，我把它们按适用场景排个序。

3.1 方法一：使用ctid保留一条（无主键表的救星）

如果你处理的表没有主键，也没有唯一约束，那最理想的删除定位方式就是使用ctid。ctid是PostgreSQL里的物理行标识符，只要记录在表里存在，它的ctid就是唯一的。所以对没有主键的表来说，ctid就是现成的“伪主键”。

删除逻辑一句话总结：找出每组重复数据中ctid最大的那条（或者最小的那条），只留下它，剩下的全部删除。

DELETE FROM my_table t USING my_table t2 WHERE t.ctid < t2.ctid AND t.col1 = t2.col1 AND t.col2 = t2.col2;

拆解一下这个SQL。DELETE ... USING是PostgreSQL特有的语法，允许在DELETE语句中引入另一张表的别名参与条件判断。意思就是：对于每一行t，如果存在另一行t2，它们的业务字段相同，而t的ctid小于t2的ctid，那就把t删掉。

打个比方，你有一堆重复的快递单，每一单都有一个包裹码（ctid），你只需要保留码最大的那一单，其他同地址同收件人的都扔掉。这样最终每组重复数据只会留下ctid最大的那条，也就是最后写入磁盘的那条。

这个方案的优势在于：无需主键、无需窗口函数、执行计划通常比较高效，因为ctid是物理定位的。但注意，它保留的是ctid最大的那条，如果业务上需要保留最早的那条，就把<改成>。

注意：ctid不是一成不变的。执行VACUUM FULL之后，表的物理存储会重组，ctid会改变。所以如果你在程序里长期存储ctid作为定位依据，那是不可靠的。但在清理任务这种一次性操作中，完全不用担心这个问题。

3.2 方法二：使用聚合函数保留特定一行

有时候业务上有明确要求，比如重复数据要保留注册时间最早的那条，或者保留订单金额最大的那条。这时候ctid这种物理位置的方法就不够语义化了，因为“最早”是按某个时间字段判断的，而不是按物理存储位置。

这种需求推荐用窗口函数来解决，分三步：

第一步，为每一行标号：

SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn FROM users;

PARTITION BY email表示按email分组，ORDER BY created_at ASC表示同一个email下，创建时间越早的排越前面。所以rn=1的就是创建时间最早的。

第二步，查出所有rn大于1的id：

SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn FROM users ) t WHERE t.rn > 1;

第三步，也是最爽的一步——PostgreSQL允许直接在DELETE里引用这个子查询：

DELETE FROM users WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn FROM users ) t WHERE t.rn > 1 );

这个删除方案可以灵活调整ORDER BY的字段和方向，比如改成ORDER BY created_at DESC，就变成保留创建时间最新的那条，删除更早的旧记录。又或者你想保留登录次数最高的那条，改成ORDER BY login_count DESC就可以。

3.3 方法三：使用DISTINCT重建表（只适合小表）

这个方法比较暴刀，适合数据量不大（几千到几万行）且表结构简单的场景。思路就是：把去重后的数据导出来，删掉原表，再插回去。

CREATE TABLE tmp_table AS SELECT DISTINCT * FROM my_table; TRUNCATE my_table; INSERT INTO my_table SELECT * FROM tmp_table; DROP TABLE tmp_table;

这样做确实能去重，而且是整行级别的去重，简单直接。但缺点也很致命：

第一，它会锁表。TRUNCATE和INSERT期间，应用对这张表的读写全部会被阻塞，如果是生产环境的活跃表，这会造成业务中断。第二，如果表上有外键关联、触发器或者依赖这张表结构的视图，重建表的操作很容易把这些依赖关系搞坏。第三，如果表数据量大，整个过程耗时很长，期间占用的磁盘空间也可能翻倍。

所以这个方法，我只建议用在一张小规模的、无依赖的临时表上。正经业务表千万别这么玩，否则数据库运维会找你算账的。

3.4 生产环境删除要注意什么

不管你用哪种方法，在删除之前，有四个点必须过一遍：

第一，先备份。不要嫌麻烦。哪怕是在测试环境操作，也建议先CREATE TABLE 表名_bak_日期 AS SELECT * FROM 原表;，花费的时间通常几秒到几分钟，但这是你的后悔药。

第二，先在SELECT里验证。删除之前，先用同款的SELECT查一下，看看你打算删掉的是多少行。如果这个数字跟你预估的偏差很大，先停下来排查原因。

第三，评估锁的影响。DELETE操作会对涉及的行加锁，如果表是热点表，删除大量数据时要分批进行，不要在高峰期一次性删除几十万行。

第四，清理Vacuum。PostgreSQL的DELETE并不会立即释放磁盘空间，只是给数据打上了删除标记。删除大量数据后，建议执行VACUUM ANALYZE 表名;，回收空间并更新统计信息。如果删了很大体量的数据，需要考虑VACUUM FULL，但那会把表锁住，只能用在维护窗口。

4. 高频场景实战：三个具体案例

前面讲了方法论，估计有些同学看完还是一头雾水。我干脆再给你放三个实战案例，从易到难，覆盖最常见的几个场景，你可以直接对标你自己的表。

4.1 场景一：单字段重复，保留ID最小的一条

比如users表 email重复，保留每个邮箱下ID最小的那条。这条SQL在生产环境里我用了很多次，稳得很。

DELETE FROM users WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS rn FROM users ) t WHERE t.rn > 1 );

执行完之后建议立刻检查一下：

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

如果这条查不出任何结果，就说明去重成功。

4.2 场景二：多字段联合重复，保留最新一条

比如订单表orders中，同一个用户下单同一个产品出现了多次，需要保留最新一条，删除旧的。

DELETE FROM orders WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( PARTITION BY user_id, product_id ORDER BY created_at DESC ) AS rn FROM orders ) t WHERE t.rn > 1 );

这里的ORDER BY created_at DESC就是保留最新记录的语义。如果反过来要保留最早的下单记录，改成ASC就行。

4.3 场景三：无主键表，全靠ctid拯救

我遇到过一个最头疼的情况：一张日志表，不仅没有主键，连唯一字段都找不到，整行完全一样，重复得清清楚楚。这种表没有任何业务字段可以区分哪条是“最早的”，也没法用ID，因为压根没有ID。

没办法，最后用的就是ctid方案：

DELETE FROM log_table t USING log_table t2 WHERE t.ctid < t2.ctid AND t.user_id = t2.user_id AND t.action = t2.action AND t.created_at = t2.created_at;

因为整行都一样，所以要把所有字段都列在AND条件里。ctid < t2.ctid表示把物理存储位置更靠前的删掉，保留最后写入的那条。如果物理位置上越靠前的反而是你不想留的（比如日志希望保留最早出现的），那就要想清楚保留规则再改。

注意：这种无主键表虽然能用ctid删除临时救火，但根因还是建表时没有遵守数据库设计规范。正常情况下，每张业务表都应该有主键，至少要有唯一约束。如果实在没有，也要加一个自增字段作为主键。

5. 常见问题与排查技巧实录

写SQL过程中你会遇到各种奇奇怪怪的状况，我挑几个出现频率最高的，直接给你录成速查表。

5.1 为什么不建议直接写DELETE FROM t WHERE 重复字段 IN (...)?

我见过不少新手写出这样的SQL：

DELETE FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 );

这个SQL执行完，所有email重复的用户全部被删得一干二净，一条都不剩。因为它没有“保留一条”的逻辑。如果你确实只想删除重复的多余数据，而不是把所有重复的连根拔起，这个方法就会翻车。我之所以强调这一点，是因为我见过有人这么干过，最后从备份恢复数据恢复了一下午。

如果想要用这种写法保留一条，就得手动加上“排除保留的那条”的条件，比如：

DELETE FROM users WHERE email IN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ) AND id NOT IN ( SELECT MIN(id) FROM users GROUP BY email HAVING COUNT(*) > 1 );

逻辑上没问题，但可读性和执行效率都不如窗口函数版本。而且在数据量大时，两个IN子查询的代价会非常高。

5.2 大批量删除时如何防止锁表和死锁

如果你要删除的数据量超过几万行甚至几十万行，不要试图一个DELETE搞定一切。长时间占用大量行锁，业务查询会被阻塞，严重时直接把数据库连接池耗尽。

一个稳妥的做法是分批删除。比如每次只删5000行：

DELETE FROM users WHERE id IN ( SELECT id FROM ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id ASC) AS rn FROM users ) t WHERE t.rn > 1 LIMIT 5000 );

然后循环执行，直到没有行被删除为止。在生产环境中，这个操作建议在维护窗口内使用事务包装执行，每批提交一次，避免长事务。

5.3 删除后别忘了处理索引和统计信息

大批量删除后，表上的索引可能变得空洞，统计信息也可能过期，导致查询计划变得很糟糕。这时候执行两件事：

第一步，更新统计信息：

ANALYZE users;

第二步，如果表的膨胀比较严重，可以考虑重建索引：

REINDEX TABLE users;

或者直接在维护窗口内：

VACUUM FULL ANALYZE users;

这条命令会重写整张表，回收所有碎片空间，并更新统计信息，是清完数据后的一剂良药。代价是执行期间会锁表，所以只能安排在业务低峰期。

6. 事后反思：如何从根源上避免重复数据

删数据删得再漂亮，也治标不治本。只要源头不堵住，过一段时间重复数据又会冒出来。在清理完现有数据之后，至少要补上下面几道防线。

第一道防线是数据库约束。如果email本来就该唯一，那就直接加上唯一索引：

CREATE UNIQUE INDEX idx_users_email ON users (email);

如果整张表基于多字段唯一，就建联合唯一索引：

ALTER TABLE order_items ADD CONSTRAINT unique_order_product UNIQUE (order_id, product_id);

不过要注意，在已有重复数据的表上添加唯一约束会失败。所以必须先清理，再建约束。

第二道防线是应用层校验。程序端在插入数据之前先查询一下是否已存在，虽然会有并发问题，不能完全杜绝重复，但至少能挡住大部分人为操作造成的重复。

第三道防线是定期巡检。写几个查重SQL，挂到定时任务里定期执行，有异常数据就告警。天底下没有一劳永逸的办法，保持警觉才是正道。

第四道防线是规范导入流程。很多重复数据都是数据迁移和导入时产生的，脚本跑了两次、导入文件重复读取，都会造成重复。批量导入前记得给临时表或源文件加去重处理，导入后立刻检查重复数量。

我喜欢把数据库清理比喻成打扫房间。今天你辛辛苦苦把地上的垃圾捡干净了，如果窗户不关好、垃圾不扔到桶里，明天又会满地都是。约束、约束、再约束，这才是治本。