一、背景案例在 Magellan 数据压缩场景中需要删除旧批次olderBatch中与新批次newerBatch重叠的数据。IN 写法DELETEFROMmagellan_cn_order_inboundWHEREversion_number20260521172049432ANDorder_idIN(SELECTorder_idFROMmagellan_cn_order_inbound bWHEREb.version_number20260522000707011)EXISTS 写法DELETEFROMmagellan_cn_order_inboundWHEREversion_number20260521172049432ANDEXISTS(SELECT1FROMmagellan_cn_order_inbound bWHEREb.version_number20260522000707011ANDb.order_idmagellan_cn_order_inbound.order_id)二、执行逻辑的本质区别IN 的执行逻辑第一步执行子查询物化整个结果集到内存 SELECT order_id FROM ... WHERE version_number newerBatch → 结果集[id_1, id_2, id_3, ... id_500万] 全部加载到内存 第二步对外层每一行在结果集中做查找 WHERE order_id IN [id_1, id_2, ... id_500万]问题子查询必须全部执行完才能开始外层过滤500 万条record_id全部物化到内存内存压力大如果子查询结果集非常大可能触发 Hash 构建占用大量工作内存work_memEXISTS 的执行逻辑第一步外层取一行olderBatch 中的某条记录 第二步拿这行的 record_id 去子查询中查找 SELECT 1 FROM ... WHERE version_number newerBatch AND order_id 当前行的id 第三步找到第一条匹配 → 立即返回 TRUE短路退出子查询 没找到 → 返回 FALSE 第四步重复处理外层下一行优势子查询不需要物化全部结果找到即停每次子查询都能命中主键索引(version_number, record_id)极快内存占用极低三、结合本案例的索引分析表主键(version_number, order_id)操作INEXISTS子查询索引使用全扫 newerBatch 所有行物化每次用主键精确查(newerBatch, 当前id)外层索引使用用version_numberolderBatch命中同左内存使用高物化整个子查询极低逐行匹配短路优化❌ 无✅ 找到即停四、执行计划对比示意IN 的典型执行计划Delete on magellan_cn_order_inbound - Hash Semi Join Hash Cond: (a.record_id b.record_id) - Index Scan on magellan_cn_order_inbound (versionolderBatch) - Hash - Index Scan on magellan_cn_order_inbound b (versionnewerBatch) ★ 先把 newerBatch 全部扫出来构建 Hash 表EXISTS 的典型执行计划Delete on magellan_cn_order_inbound - Nested Loop Semi Join - Index Scan on magellan_cn_order_inbound (versionolderBatch) - Index Scan on magellan_cn_order_inbound b Index Cond: (version_numbernewerBatch AND record_id外层当前id) ★ 每次用主键精确查找到即停PostgreSQL 实际上很智能对IN也可能优化为 Hash Semi Join但当子查询结果集超过work_mem时性能会急剧下降。五、什么时候 IN 反而更快EXISTS 也不是万能的以下场景 IN 可能更优场景推荐子查询结果集很小几十条IN结果集小物化代价低逻辑简单子查询结果集很大百万级EXISTS避免物化逐行匹配更省内存外层表数据量远小于子查询IN外层循环少Hash 查找快需要去重语义IN自动去重六、本案例结论magellan_cn_order_inbound 数据量千万级 newerBatch 数据量可能 200万500万条推荐使用 EXISTSDELETEFROMmagellan_cn_order_inboundWHEREversion_number#{olderBatch}ANDEXISTS(SELECT1FROMmagellan_cn_order_inbound bWHEREb.version_number#{newerBatch}ANDb.order_idmagellan_cn_order_inbound.order_id)原因newerBatch 数据量大IN 会物化数百万record_id到内存主键(version_number, record_id)可被 EXISTS 子查询精确命中EXISTS 短路特性在重叠率高时效果更明显找到即停七、一句话总结IN是先把答案全算出来再去对照EXISTS是对照一条有结果立刻告诉我不用全算完。数据量越大EXISTS 的优势越明显。
