SQL 面试题：如何安全删除重复数据并保留最新记录？

题干与适用场景

PostgreSQL 的 customercontacts 表有 2 亿行。旧导入程序的重试逻辑为同一个 (tenantid, externalid) 创建了多条记录。按 updatedat 判断，最新记录应当保留；时间相同时，id 最大的记录胜出。contactevents.contactid 外键可能指向任意一份副本，如果迁移引用前就删除淘汰行，操作会因外键失败或丢失原有关联。

customer_contacts(
    id bigint primary key,
    tenant_id bigint not null,
    external_id text not null,
    updated_at timestamptz not null,
    payload jsonb not null
)

contact_events(
    id bigint primary key,
    contact_id bigint not null references customer_contacts(id),
    event_type text not null,
    created_at timestamptz not null
)

请设计一套 PostgreSQL 修复方案：每个业务键只保留一条确定的规范记录，所有事件都能保留，破坏性操作使用有界事务，可审计、可重试，最终由数据库强制保证唯一性。修复期间读流量必须可用；允许在最终收口时受控暂停写入，但暂停时长必须通过测量确定，不能靠猜测。

2 亿行和最终写暂停是本题设定。这是一道高难度的数据工程与 SQL 题，因为窗口函数只负责选出保留行；完整回答还要处理引用完整性、并发、回滚、存储健康和后续写入。当前公开 SQL 面试资料仍把 ROW_NUMBER 去重和确定性并列规则作为明确的面试题型。题目没有可核验的公司归属，因此 companyName 为 null。

面试官考察点

第一项是候选人能否在写 DELETE 前定义“重复”和“最新”。业务键是 (tenantid, externalid)，id 标识的是物理行。完整顺序 updatedat DESC, id DESC 即使遇到相同时间，也只会产生一个胜出者。RANK 可能保留多条并列数据；ROWNUMBER 会把唯一一行编号为 1。

第二项是能否控制破坏性边界。生产级回答会先预览数量与样本，固化一份不可变的“淘汰行到保留行”映射，保留淘汰行或可恢复快照，再按主键删除。如果写入仍在继续，却在每个批次中独立重算排名，目标集合可能变化，整个修复也很难解释。

第三项是引用与事务正确性。父记录删除前，所有子表引用都必须从淘汰行迁移到保留行。子表自身的唯一规则可能让迁移发生冲突，因此只说“更新所有外键”并不完整，还需要引用清单和冲突策略。每个批次都应幂等、短小、可观测，并能安全停止。

最后看清理是否解决根因。只删除今天的重复行，明天旧导入程序仍可重新制造问题。持久不变量应落到唯一约束或唯一索引，并明确空值和规范化口径。候选人还应区分正确性证据和性能证据：重复业务键为零、孤儿引用为零证明语义；EXPLAIN、锁等待、WAL 速率、复制延迟、死元组和 autovacuum 状态决定运行速率是否可接受。

回答前需要澄清的问题

• 哪些列定义同一个业务实体？ 本题使用精确的 (tenantid, externalid)。大小写折叠、去除空格或 Unicode 规范化会形成另一套业务键，必须在修复前确认。
• 哪一行胜出？ updated_at 最大，其次 id 最大。两条数据时间相同时，单独使用时间戳不是完整顺序。
• 淘汰行是否可能含有独有数据？ 如果 payload 需要合并，“保留最新”就不够。本题把最新 payload 视为权威，同时归档淘汰行供复核。
• 哪些表引用 customercontacts.id？ 要检查已声明外键和应用层引用。题目展示了 contactevents，真实修复还要搜索系统目录和归属文档。
• 迁移引用会不会制造子表重复？ 如果子表有 UNIQUE(contactid, eventtype, created_at)，收口后两条等价事件可能冲突。更新前先决定合并、保留或拒绝策略。
• 修复期间是否允许写入？ 受保护的写入可与历史批次并行，但最终重复扫描和唯一性切换必须依赖经过验证的数据库级写保护，或经过测量的写暂停。有些写入方能绕过的应用约定不构成保证。
• 回滚要求是什么？ 在验证和回滚窗口结束前，保留备份或完整淘汰行归档，以及子表原始引用映射。只有淘汰行到保留行映射，无法恢复被丢弃的 payload。
• 允许多少运行负载？ 批量大小是控制量，不是固定答案。先从小批次开始，再根据锁等待、写入 p99、WAL、复制延迟、死元组和 autovacuum 进度限速。
• 空值如何处理？ 本题两个业务键字段都非空。如果允许空值且空值也应视为重复，PostgreSQL 唯一性需要 NULLS NOT DISTINCT；默认唯一语义允许多个空值。

30 秒回答框架

“我会先固定业务规则：(tenantid, externalid) 相同即重复，按 updatedat 降序、id 降序选胜出行。先预览排名结果，归档淘汰行，并按一个 runid 固化每个淘汰 ID 到胜出 ID 的不可变映射。接着用短事务幂等记录和迁移所有子表引用，确认没有引用再指向淘汰行后，才按主键分批删除。每个阶段都核对数量、payload 样本、重复业务键和孤儿引用。最后在已验证的写保护或测量过的写暂停下执行最终增量清理并建立唯一索引，让所有写入方遵守同一业务键规则。我会根据生产指标动态限速，并把归档保留到回滚窗口结束。”

这套框架涵盖选行、依赖顺序、破坏性控制、收口和预防。SQL 服务于这些决策，不能替代这些决策。

分步骤深入解答

第一步：先确定不变量、责任人与运行边界

修改数据前先写出修复后的条件：

• 每个 (tenantid, externalid) 只存在一条 contact；
• 按 (updated_at, id) 排序，它的 ID 最大；
• 修复前的每条 contact_events 记录仍存在，并指向胜出行；
• 没有已声明或应用层引用指向已删除 ID；
• 已完成批次再次执行时修改零行；
• 新写入无法再次创建重复业务键。

为本次运行分配 run_id、责任人、源快照或备份、开始时间、代码版本、批次游标、监控面板、暂停阈值和回滚截止时间。记录修复前总行数、不同业务键数、重复键数、淘汰行数和事件数，并按 tenant 保留控制总数，避免全局总数掩盖单个租户的数据丢失。

第二步：先预览确定性排名

先运行只读查询，并检查数量和 payload 差异：

WITH ranked AS (
    SELECT
        id,
        tenant_id,
        external_id,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY tenant_id, external_id
            ORDER BY updated_at DESC, id DESC
        ) AS rn
    FROM customer_contacts
)
SELECT tenant_id, external_id, COUNT(*) AS loser_count
FROM ranked
WHERE rn > 1
GROUP BY tenant_id, external_id
ORDER BY loser_count DESC, tenant_id, external_id
LIMIT 100;

本题要求只保留一个胜出者，因此适合 ROWNUMBER。如果不加入 id 形成确定性并列规则，重复执行可能选出不同胜出者；而 RANK 或 DENSERANK 在排序值相同时可能给多行相同名次。DISTINCT 只能去掉查询结果中完全相同的选定列，无法按业务偏好保留一整条规范记录。

第三步：固化不可变的淘汰行到保留行映射

不要为子表迁移和父表删除分别重算胜出行。在已确认的运行边界内只做一次决策并固化。以下代码中的 :name 表示由修复执行器传入的绑定参数：

WITH ranked AS (
    SELECT
        id,
        tenant_id,
        external_id,
        ROW_NUMBER() OVER w AS rn,
        FIRST_VALUE(id) OVER w AS winner_id
    FROM customer_contacts
    WINDOW w AS (
        PARTITION BY tenant_id, external_id
        ORDER BY updated_at DESC, id DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
)
INSERT INTO contact_dedup_map (
    run_id, loser_id, winner_id, tenant_id, external_id
)
SELECT :run_id, id, winner_id, tenant_id, external_id
FROM ranked
WHERE rn > 1;

contactdedupmap 应有 PRIMARY KEY(runid, loserid)、淘汰行与胜出行不能相同的检查，以及 (runid, winnerid) 索引。用同一 run_id 归档完整淘汰行，或保留经过恢复演练的时间点恢复路径。检查每个胜出行都存在，同一次运行中的胜出行不会同时成为淘汰行，每个淘汰行只映射一次，映射条数与测得的淘汰行数一致。

对 2 亿行执行排名可能需要大规模扫描和排序。先在接近生产的数据上运行 EXPLAIN，确认临时空间和工作负载影响，再安排执行与限速。覆盖索引也许能改善经过测量的计划，但索引本身同样昂贵，不能在没有证据时直接开药方。

第四步：删除父记录前先迁移所有引用

先盘点全部引用。对于 contact_events，先把原始映射写入审计表，再按有界 ID 区间更新：

UPDATE contact_events AS e
SET contact_id = m.winner_id
FROM contact_dedup_map AS m
WHERE m.run_id = :run_id
  AND e.contact_id = m.loser_id
  AND e.id > :after_event_id
  AND e.id <= :batch_end_event_id;

重试是幂等的：事件已经指向胜出行后，就不会再匹配 m.loser_id。每个有界批次单独提交，提交成功后才保存游标，并把更新行数与引用审计行数对账。如果子表唯一约束冲突，执行事先确认的合并或拒绝规则；不能关闭约束后期待最终状态自然正确。

删除父记录前，下面的查询对每一张子表都必须返回零：

SELECT COUNT(*) AS remaining_loser_references
FROM contact_events AS e
JOIN contact_dedup_map AS m
  ON m.run_id = :run_id
 AND m.loser_id = e.contact_id;

第五步：用短事务、可续跑地删除淘汰行

只删除固化映射中的 ID：

WITH batch AS (
    SELECT loser_id
    FROM contact_dedup_map
    WHERE run_id = :run_id
      AND loser_id > :after_loser_id
    ORDER BY loser_id
    LIMIT 10000
)
DELETE FROM customer_contacts AS c
USING batch AS b
WHERE c.id = b.loser_id
RETURNING c.id, c.tenant_id, c.external_id;

10000 只是面试中的初始值，不是通用最优值。要根据锁持有时间、p99、WAL、从库延迟、死元组和 autovacuum 调整批量大小与间隔。RETURNING 结果作为删除证据。若进程在事务提交后、游标保存前崩溃，再执行同一批只会遇到已经删除的 ID，仍然安全。

PostgreSQL 大批量删除会生成死元组和 WAL。应安排普通 VACUUM 并监控表和索引膨胀；不能默认使用 VACUUM FULL，它会重写表并持有强锁。读请求虽保持可用，资源耗尽仍可能违反服务 SLO。

第六步：让写入收敛，并安装持久防线

历史修复无法自动收敛一个持续移动的目标。在最终收口前，部署统一写入契约：同一个规范化业务键的创建必须串行化，并更新规范记录，不能继续插入副本。确认每个 API、导入程序、任务和直连数据库的写入方都遵守。无法证明时，就在最终增量清理与索引切换期间暂停写入。

最后一次重复扫描返回零后，在事务块外创建唯一索引：

CREATE UNIQUE INDEX CONCURRENTLY customer_contacts_business_key_uidx
ON customer_contacts (tenant_id, external_id);

CONCURRENTLY 避免普通建索引的锁阻塞写入，但工作量更大，不能放进事务块，也可能因唯一性冲突失败并留下无效索引。应检查有效性，定位数据或写入竞争，按运行手册删除无效索引后重试；不能把 IF NOT EXISTS 当成成功证明。索引有效后，如果模式治理需要约束语义，可在一个受控的短 DDL 步骤中把它挂接为有名称的唯一约束。

如果全部写入已暂停，而且测量证明普通构建能在允许窗口内完成，非并发唯一索引更简单、更快。只有经过验证的受保护写入仍要继续时，并发形式才是合适取舍；最终由 SLO 和演练结果决定。

最终写入链路要依赖数据库唯一性，并明确冲突策略。清理后只捕获唯一冲突并不等于完整的幂等设计；要决定同键写入是更新规范行、拒绝冲突 payload，还是进入人工复核。

第七步：验证、观察并关闭回滚窗口

至少完成以下对账：

• 修复后 contact 总数等于修复前总数减去已归档淘汰行数；
• GROUP BY tenantid, externalid HAVING COUNT(*) > 1 返回零；
• 每个映射中的胜出行存在，每个淘汰行都已消失；
• 子表事件总数不变，剩余淘汰行引用为零，孤儿引用为零；
• 抽样胜出行符合 (updated_at DESC, id DESC) 规则并能与归档 payload 对照；
• 重复插入会被拒绝，或按声明的更新策略处理；
• 已完成的映射、迁移和删除批次重跑时修改零行。

监控批次速率、错误、行锁等待、写入 p50/p95/p99、WAL 字节、复制延迟、死元组、autovacuum 进度、映射与归档增长、剩余淘汰行引用、剩余重复业务键和唯一索引构建进度。在经过测试的回滚窗口内保留归档和引用审计数据，之后再按保留策略移除临时写保护和修复表。

高质量示范回答

“我会把重复定义为 (tenantid, externalid) 相同，并按 updatedat DESC, id DESC 选规范行；唯一 ID 让相同时间也有确定结果。删除前先记录基线数量、检查 payload 差异、盘点所有引用、取得可恢复备份，再用 ROWNUMBER 和 FIRSTVALUE 为一次 runid 建立每个淘汰 ID 到胜出 ID 的不可变映射。

我会归档淘汰行和子表原始引用，再按主键短区间迁移 contact_events。每个批次提交成功后才推进游标；已经迁移的事件不再匹配淘汰 ID，所以重试幂等。子表约束始终启用，发生冲突时执行明确的合并或拒绝规则。只有所有子表都报告淘汰行引用为零，才按固化映射里的 ID 分批删除 contact，并把 RETURNING 作为操作证据。

运行速率依据锁时长、请求 p99、WAL、复制延迟、死元组和 autovacuum 调整，不能照搬固定批量。我会验证行数守恒、每个业务键仅一行、淘汰行与孤儿引用为零、事件数不变、抽样胜出规则正确，以及重试零变更。

为防止复发，所有写入方必须收敛到同一个规范化业务键。在已验证的写保护或测量过的暂停下，我会完成最终增量清理，并在事务外为 (tenantid, externalid) 创建并发唯一索引。并发构建失败可能留下无效索引，所以必须检查其有效性。永久写入链路再按既定规则更新、拒绝或复核冲突。归档会保留到回滚证据和观察窗口都完成。”

这段回答让每个不可逆操作都依赖明确闸门，并把 SQL、外键、写入行为与数据库约束统一在同一业务键契约下。

常见错误

• 发现重复后立刻执行 DELETE → 引用、payload 和回滚证据可能丢失 → 先预览、归档、建映射、迁移、验证，再删除。
• 只按 externalid 分区 → 不同租户的相同外部 ID 会被合并 → 使用完整业务键 (tenantid, external_id)。
• 只按 updated_at 排序 → 相同时间可能在另一次运行中选出不同胜出行 → 把唯一 id 作为最终并列规则。
• 使用 RANK 再删除 rn > 1 → 并列最新的多行都可能得到 1 → 需要恰好一个保留行时，用完整顺序的 ROW_NUMBER。
• 每个批次都重算胜出行 → 并发变化会移动目标并破坏审计性 → 固化一份有版本的淘汰行到胜出行映射。
• 迁移子表前删除父记录 → 外键会拒绝删除，或级联删除有效历史 → 先盘点并迁移全部引用。
• 为了速度关闭约束 → 修复可能静默制造孤儿或子表冲突 → 保持约束启用并定义冲突处理。
• 把 10000 当成正确批量 → 硬件与负载决定安全吞吐 → 从小批次开始，根据 SLO、WAL、延迟和 vacuum 指标调整。
• 清理后就结束 → 错误写入方会再次制造重复 → 收敛写入方，并由 PostgreSQL 强制唯一性。
• 假设 CREATE UNIQUE INDEX CONCURRENTLY 总会成功 → 数据竞争或重复行可能留下无效索引 → 检查有效性并执行明确的恢复流程。
• 把 VACUUM FULL 当成常规收尾 → 它会重写并强锁表 → 监控普通 vacuum 与膨胀，特殊重写另行排期。

追问及应对

追问一：为什么不使用一个 CTE，在单个事务中一次删完？

对于没有引用、暂停写入的小表，一个 CTE DELETE 可能足够。面对 2 亿行，单个事务可能长期保留锁和旧版本，产生很大的 WAL 突发，拖慢从库，增加 vacuum 难度，并形成一次全有或全无的恢复事件。固化映射加有界批次能提供进度、审计、限速与续跑能力。只有证明规模和依赖假设成立后，才选择更简单的一次性查询。

追问二：两行时间相同但 payload 不同怎么办？

题目规则选择 id 最大者，所以结果确定；但确定性不等于 payload 在业务上正确。修复前先统计冲突 payload，把高风险字段交给人工复核或领域合并规则，并归档两行。不能在删除已经开始后临时发明字段级合并逻辑。

追问三：如果 external_id 可以为空呢？

先确认空值代表“未知且互相独立”，还是一个应去重的值。PostgreSQL 唯一约束与索引默认把空值视为不同，因此允许多个空键。如果空值也应冲突，使用 NULLS NOT DISTINCT，排名也必须采用相同口径；如果未知 contact 应独立存在，就保持默认语义，或只对非空 ID 建立部分唯一规则。清理和约束必须一致。

追问四：能否完全在线修复，不暂停写入？

可以，但前提是所有写入方都已被证明使用数据库支持的业务键串行化或占位规则，然后才能做最终扫描。历史清理可以逐步收敛，再并发创建唯一索引。如果旧导入程序或直连写入能绕过保护，构建期间仍可能出现新重复并导致失败。影子表加受控切换也是一种方案，但会增加双写、回填、外键和回滚复杂度，只有停机 SLO 值得时才采用。

追问五：如何找到所有外键和隐藏引用？

查询 PostgreSQL 系统目录，找出所有引用 customer_contacts 的外键；再检查视图、触发器、CDC 消费者、搜索索引、数据导出和应用模式中保存的 contact ID。已声明外键提供可强制的证据，归属文档和代码搜索覆盖应用层引用。删除闸门要列出每个发现的消费方及其对账检查。

追问六：迁移事件时违反子表唯一约束怎么办？

先暂停更新。冲突说明两个子记录在父 ID 收口后变得相等。要确认它们是重复事件、需要新键的独立观测，还是数据冲突。先固化冲突组并归档，再按确定的合并或拒绝规则处理，然后迁移引用。删除子表约束会改变业务语义，不是修复方案。

追问七：长时间修复期间表又发生变化，怎样证明胜出行仍是最新的？

映射只能证明它在记录的快照或运行边界下胜出，不能涵盖无限期的未来写入。应保护写入方，让后续操作更新映射中的规范行，记录源版本，并在唯一性切换前做最终增量扫描。如果业务规则要求后来的更新替换 payload，就更新胜出行，不能再创建一个物理 contact。审计记录要明确这项时间保证。