数据库大数据场景下SQL查询优化：筛选新层独有关键值

当数据量庞大时，NOT IN子查询的性能通常很差，甚至可能因为旧表中存在NULL值导致查询结果异常。以下是几种更高效的替代方案：

方案1：LEFT JOIN + IS NULL #

利用左连接筛选旧表中无匹配的数据，查询优化器对这类JOIN的处理效率远高于NOT IN子查询：

SELECT DISTINCT n.id_gs, n.nmbr_tusa
FROM "NEW_SLOY"."SHILPU" n
LEFT JOIN "OLD_SLOY"."SHILPU" o 
  ON n.id_gs = o.id_gs
WHERE n.date_contract >= '2023-05-01'
  AND o.id_gs IS NULL

关键优化点：确保新旧表的id_gs字段都创建了索引，这能大幅提升连接操作的速度。

方案2：NOT EXISTS子查询 #

NOT EXISTS的执行逻辑是找到匹配项就停止检查，性能优于NOT IN，且不受旧表中NULL值的影响：

SELECT DISTINCT id_gs, nmbr_tusa
FROM "NEW_SLOY"."SHILPU" n
WHERE n.date_contract >= '2023-05-01'
  AND NOT EXISTS (
      SELECT 1
      FROM "OLD_SLOY"."SHILPU" o
      WHERE o.id_gs = n.id_gs
  )

关键优化点：给OLD_SLOY"."SHILPU的id_gs字段加索引，能让子查询的匹配检查更快。

方案3：集合操作（EXCEPT） #

如果你的数据库支持集合操作（如PostgreSQL、SQL Server、MySQL 8.0+），EXCEPT是更简洁高效的选择，它会自动去重，无需额外的DISTINCT：

SELECT id_gs, nmbr_tusa
FROM "NEW_SLOY"."SHILPU"
WHERE date_contract >= '2023-05-01'
EXCEPT
SELECT id_gs, nmbr_tusa
FROM "OLD_SLOY"."SHILPU"

额外优化建议 #

• 给NEW_SLOY"."SHILPU的date_contract字段加索引，能快速过滤出目标日期范围内的数据，减少后续处理的数据量
• 如果id_gs和nmbr_tusa是一一对应的关系，可以去掉DISTINCT，避免不必要的排序去重开销

内容的提问来源于stack exchange，提问作者Felipe Felix