删除重复数据的5种写法，第3种连资深工程师都容易出错（SQL面试避坑指南）-优快云博客

第一章：SQL面试高频题——删除重复数据的5种写法，第3种连资深工程师都容易出错

在数据库开发与维护中，删除重复记录是常见且关键的操作。尤其是在数据清洗和面试场景中，如何高效、安全地去重成为考察SQL能力的重要指标。以下是五种常见的实现方式，每一种都有其适用场景和潜在陷阱。

使用 ROW_NUMBER() 窗口函数

通过为每组重复数据分配行号，保留行号为1的记录，其余删除。

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

此方法逻辑清晰，适用于大多数支持窗口函数的数据库（如 PostgreSQL、SQL Server）。

基于自连接删除重复项

利用表的自连接，保留主键最小的记录。

DELETE e1 FROM employees e1
JOIN employees e2 
WHERE e1.email = e2.email AND e1.id > e2.id;

该写法在MySQL中高效，但需注意连接条件避免误删。

使用 GROUP BY 和子查询

先找出每组保留的最小ID，再删除不在该集合中的记录。

DELETE FROM employees 
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) AS min_id 
    FROM employees 
    GROUP BY email
  ) AS t
);

这是第三种写法，看似合理，但在MySQL中会报错：**“You can't specify target table for update in FROM clause”**，因为不能对同一表进行查改操作。必须嵌套一层临时表绕过限制。

不同方法对比

方法	可读性	兼容性	性能
ROW_NUMBER()	高	中（不支持老版本）	高
自连接	中	高	中
GROUP BY 子查询	高	低（MySQL受限）	低

第二章：基于主键与临时表的经典去重方案

2.1 利用ROW_NUMBER窗口函数识别重复记录

在处理数据清洗任务时，识别并剔除重复记录是关键步骤。`ROW_NUMBER()` 窗口函数能为每行分配唯一序号，结合 `PARTITION BY` 可对重复组内记录编号。

核心逻辑解析

通过按关键字段分组并排序，为每条记录打上行号，仅保留行号为1的记录即可去重。

SELECT *
FROM (
    SELECT id, name, email,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
) t
WHERE rn = 1;

上述语句中，`PARTITION BY email` 将相同邮箱的记录归为一组，`ORDER BY created_at DESC` 确保最新记录排在首位，`rn = 1` 保留每个邮箱最新的记录。

适用场景对比

数据仓库ETL过程中清洗源表重复数据
用户表中基于唯一标识合并多条冗余记录
日志表中去除因重试机制产生的重复写入

2.2 借助临时表实现安全数据清洗流程

在处理生产环境中的敏感数据时，直接操作源表可能带来不可逆的风险。使用临时表可在隔离环境中完成数据清洗，保障原始数据安全。

临时表的优势

避免对原始表的锁定和污染
支持多次迭代清洗逻辑
便于回滚与验证

典型清洗流程示例

-- 创建临时表存储待清洗数据
CREATE TEMPORARY TABLE temp_user_clean AS 
SELECT * FROM raw_users WHERE create_time > '2023-01-01';

-- 在临时表中执行标准化处理
UPDATE temp_user_clean 
SET email = LOWER(TRIM(email)), 
    phone = REGEXP_REPLACE(phone, '[^\d+]', '', 'g')
WHERE status = 'active';

上述SQL首先从原始表中筛选出指定时间后的用户记录并复制至临时表，随后对邮箱和手机号进行格式标准化。所有变更均不影响原表，确保操作可验证、可修正。

2.3 使用自连接删除冗余数据的执行逻辑

在处理数据库中的重复记录时，自连接是一种高效且直观的技术手段。通过将表与自身进行连接，可以识别并删除基于特定字段的冗余数据。

自连接去重的基本原理

自连接利用表的别名机制，将同一张表视为两个独立的数据源进行匹配。通常结合 DELETE 语句与 INNER JOIN 实现精准删除。

DELETE t1 FROM employees t1
INNER JOIN employees t2 
WHERE t1.id > t2.id 
  AND t1.email = t2.email;

上述语句中，t1 和 t2 是同一表的两个实例。条件 t1.id > t2.id 确保保留主键较小的记录，仅删除后续重复项。匹配依据为 email 字段，适用于去重邮箱相同的员工信息。

执行流程解析

数据库首先生成表的两个逻辑副本
根据指定条件进行行间比对
定位所有满足重复条件且ID较大的记录
执行删除操作，保留唯一性记录

2.4 DELETE + 子查询在大型表中的性能考量

在处理大型数据表时，使用 DELETE 配合子查询虽能实现复杂条件删除，但可能引发严重的性能瓶颈。数据库需执行子查询获取待删记录集，若未合理利用索引，将导致全表扫描和锁竞争。

执行计划分析

以以下语句为例：

DELETE FROM orders 
WHERE customer_id IN (
  SELECT id FROM customers 
  WHERE status = 'inactive'
);

该语句会先执行子查询获取非活跃客户ID列表，再匹配主表记录。若 orders.customer_id 或 customers.id 缺乏索引，性能急剧下降。

优化策略

确保子查询和外层查询字段均有索引支持
考虑分批删除，使用 LIMIT 控制每次操作行数
必要时改写为 JOIN 形式提升执行效率

2.5 主键辅助去重的通用模式与边界处理

在分布式数据处理场景中，基于主键的去重是保障数据一致性的关键手段。通过唯一主键识别重复记录，可在写入阶段实现“插入或更新”的原子操作。

通用去重模式

典型流程包括：提取主键 → 查询是否存在 → 执行INSERT或UPDATE。该逻辑可通过数据库的ON DUPLICATE KEY UPDATE（MySQL）或MERGE（PostgreSQL）语句高效实现。

MERGE INTO target_table AS t
USING (SELECT :id, :value) AS s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET value = s.value
WHEN NOT MATCHED THEN INSERT (id, value) VALUES (s.id, s.value);

上述SQL使用MERGE语句实现幂等写入，避免了先查后写的竞态条件。

边界情况处理

主键为空值时需预处理，防止索引失效
高并发下应结合数据库行锁或乐观锁控制版本
异构系统同步时，需统一主键生成策略（如UUIDv5）

第三章：无主键场景下的高风险去重策略

3.1 全字段匹配去重的适用条件与陷阱

全字段匹配去重是一种基于记录所有字段完全一致来判断重复数据的策略，适用于数据完整性要求高且源系统无唯一键的场景。

适用条件

数据源无法提供业务主键或时间戳
字段集合完整且稳定，结构变更频率低
可容忍较高计算资源消耗

典型陷阱

当浮点数精度、空值处理或字符编码不一致时，看似相同的记录可能被误判为不同。例如：

SELECT * FROM raw_data a
WHERE NOT EXISTS (
  SELECT 1 FROM cleaned_data b
  WHERE a.field1 = b.field1
    AND a.field2 = b.field2
    AND a.field3 = b.field3
);

该SQL在字段数量多时性能急剧下降，且未考虑NULL值相等性问题。建议结合哈希摘要（如MD5拼接字段）提升比对效率，并预处理标准化输入。

3.2 使用GROUP BY保留一条记录的实际副作用

在SQL查询中，使用GROUP BY通常用于聚合操作。然而，若仅用其“保留一条记录”而未配合聚合函数，可能引发非预期结果。

隐式分组的风险

MySQL在ONLY_FULL_GROUP_BY模式关闭时允许非聚合字段出现在SELECT中，但返回值具有不确定性：

SELECT user_id, name, created_at 
FROM orders 
GROUP BY user_id;

上述语句虽能执行，但name和created_at取自哪一行无法保证，导致数据不一致。

3.3 被忽视的事务隔离问题导致误删数据

在高并发系统中，事务隔离级别的设置直接影响数据一致性。默认的读已提交（Read Committed）级别可能引发不可重复读，进而导致业务逻辑判断失效。

典型误删场景

当多个事务同时检查某条记录是否存在，并据此决定是否删除时，缺乏串行化控制将导致重复删除或误删。

事务A与B同时查询某用户订单
均判断为“可删除”状态
先后执行删除操作，但未加锁
造成数据误删且难以追溯

解决方案：显式加锁

使用SELECT ... FOR UPDATE提升隔离安全性：

BEGIN;
SELECT status FROM orders WHERE id = 123 FOR UPDATE;
IF status = 'canceled' THEN
    DELETE FROM orders WHERE id = 123;
END IF;
COMMIT;

该语句在事务提交前对目标行加排他锁，防止其他事务修改或误判状态，有效避免并发下的逻辑冲突。

第四章：高级SQL技巧优化去重操作

4.1 CTE（公共表表达式）提升代码可读性

CTE（Common Table Expression）通过将复杂查询拆分为逻辑清晰的模块，显著提升SQL语句的可读性和维护性。

基本语法结构

WITH sales_summary AS (
    SELECT 
        employee_id,
        SUM(amount) AS total_sales
    FROM sales
    GROUP BY employee_id
)
SELECT * FROM sales_summary WHERE total_sales > 10000;

该CTE先计算每位员工的销售总额，再筛选出超过10000的记录。逻辑分层明确，便于理解与调试。

优势对比

相比嵌套子查询，CTE更易阅读和维护
支持递归查询，适用于层级数据处理
可在同一查询中多次引用，避免重复代码

4.2 窗口函数结合PARTITION高效定位重复项

在处理大规模数据时，识别并管理重复记录是数据清洗的关键步骤。使用窗口函数配合 `PARTITION BY` 可显著提升去重效率。

核心逻辑解析

通过 `ROW_NUMBER()` 窗口函数，在每个分组内为行分配唯一序号，当序号大于1时即为重复项。

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

上述查询按 `email` 分区，并按创建时间降序排列，保留最新记录，其余视为重复。`PARTITION BY` 将数据按指定列分组，窗口函数在每组内独立计数，避免全表扫描，极大提升性能。

应用场景扩展

数据库迁移前的数据清洗
用户信息去重合并
日志系统中异常重复事件检测

4.3 利用EXISTS替代IN提高删除效率

在处理大规模数据删除操作时，使用 EXISTS 替代 IN 能显著提升执行效率，尤其当子查询结果集较大或包含 NULL 值时。

性能差异分析

IN 需要完全执行子查询并构建结果列表，而 EXISTS 采用短路机制，只要找到匹配项即返回 true，减少不必要的扫描。

语法对比示例

-- 使用 IN（低效）
DELETE FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers WHERE status = 'inactive'
);

-- 使用 EXISTS（高效）
DELETE FROM orders o 
WHERE EXISTS (
    SELECT 1 FROM customers c 
    WHERE c.id = o.customer_id 
    AND c.status = 'inactive'
);

上述代码中，EXISTS 通过关联字段提前终止匹配，避免全表比对。同时支持早期过滤，优化器更易选择高效执行计划，从而降低 I/O 开销。

4.4 分批处理大规模重复数据的最佳实践

在处理海量重复数据时，分批处理能有效降低内存压力并提升系统稳定性。合理划分批次大小是关键。

批次大小的权衡

过小的批次增加调度开销，过大则易引发内存溢出。建议根据可用内存和数据特征动态调整，通常每批处理 1,000 至 10,000 条记录。

去重与批处理结合

使用唯一标识符进行哈希去重，可在写入前过滤冗余数据。

// Go 示例：分批处理并去重
func ProcessInBatches(data []string, batchSize int) {
    seen := make(map[string]bool)
    for i := 0; i < len(data); i += batchSize {
        end := i + batchSize
        if end > len(data) {
            end = len(data)
        }
        batch := data[i:end]
        filtered := []string{}
        for _, item := range batch {
            if !seen[item] {
                seen[item] = true
                filtered = append(filtered, item)
            }
        }
        // 处理去重后的批次
        processBatch(filtered)
    }
}

该函数通过维护一个全局哈希表 seen 实现跨批次去重，batchSize 控制每次处理的数据量，避免内存过载。

第五章：总结与面试避坑指南

常见陷阱：过度设计系统架构

面试中常被要求设计高并发系统，但许多候选人直接套用微服务、Kafka、Redis 集群等组件，忽视实际需求规模。例如，设计一个日活 1 万的博客平台却引入服务网格和分布式追踪，反而暴露对技术选型理解肤浅。

性能优化误区：盲目追求缓存命中率


// 错误示例：缓存穿透未处理
func GetUser(id int) (*User, error) {
    user, _ := cache.Get(id)
    if user == nil {
        user, _ = db.Query("SELECT * FROM users WHERE id = ?", id)
        cache.Set(id, user)
    }
    return user, nil
}
// 正确做法：使用布隆过滤器或空值缓存防止穿透

沟通问题：忽略边界条件与异常场景

未主动询问 QPS 预估、数据量级、一致性要求
忽略降级策略，如 Redis 宕机后是否允许查询直达数据库
未定义幂等性保障机制，导致重复操作风险

技术深度展示建议

考察点	低分回答	高分回应
数据库分库分表	“按用户 ID 取模”	“结合热点用户识别，动态调整分片键，避免数据倾斜”
消息积压	“增加消费者”	“先扩容消费者，再临时拆分 topic 并行消费，事后分析生产速率突增原因”