NOT IN查询性能优化 改写为NOT EXISTS、LEFT JOIN后结果行数不符排查

原NOT IN查询的实际执行逻辑基线 #

你最初编写的SQL是隐式内连接的老式写法，核心执行逻辑分两步，和你记忆中的逻辑可能存在偏差：

• 第一步先完成table1和table2的隐式内连接：连接条件为t1.lm_id = t2.lm_id，叠加两端的过滤规则：t1端需满足lm_id以B-开头、dt > '20201231'；t2端需满足int_val = 1。这一步会直接过滤掉所有t1中没有在当前时间窗口匹配到t2有效记录的lm_id，不会单独从table1捞取全量数据。
• 第二步执行NOT IN排除逻辑：将第一步捞取的lm_id集合中，剔除所有在20201231当天、满足「lm_id以B-开头、int_val=1」条件的table2历史记录中出现过的lm_id。
• 因为原查询没有加DISTINCT，同一个lm_id如果在当前时间窗口匹配到多条t2记录，会被重复带出。

两个改写版本的具体错误 #

NOT EXISTS版本的问题 #

• 完全丢失了原查询的前置内连接校验：当前写法直接从table1全量捞数据，没有校验「当前时间窗口下lm_id是否在table2存在int_val=1的匹配记录」，把所有仅满足t1条件、没有任何t2当前匹配记录的行也纳入了结果集，这是行数从500涨到811的核心原因。
• 字段名错配：原查询t1端过滤用的是t1.dt，你改写时写成了t1.dt_valid_to，两个字段的存储规则、数据范围完全不同，会进一步拉大结果偏差。

LEFT JOIN版本的问题 #

• 同样丢失了原查询的前置内连接校验：把原本应该做内连接的当前时间窗口t2匹配逻辑，全部放到了LEFT JOIN的ON子句里，没有强制要求lm_id必须存在t2的有效当前匹配记录。
• 条件逻辑矛盾：WHERE子句同时写了t2.lm_id is null和t2.int_val = 1，当左连接匹配失败时t2的所有字段都是null，不可能满足int_val=1的判断，逻辑完全失效。如果按你贴的代码实际执行，理论上应该返回0行，能跑出811行大概率是实际编码时把条件位置放错了。
• 存在和NOT EXISTS版本一样的字段错配问题，把t1.dt替换成了t1.dt_valid_to。

对齐原逻辑的高性能改写版本 #

NOT EXISTS和LEFT JOIN的性能优势是真实存在的：相比NOT IN需要全量聚合子查询结果集再做匹配，这两种写法是半连接模式，遇到第一条匹配记录就终止扫描，还能规避NOT IN子查询出现null值导致全量结果失效的问题，大数据量下性能提升非常明显。

校准后的NOT EXISTS写法 #

SELECT DISTINCT t1.lm_id 
FROM table1 t1
-- 保留原逻辑的前置内连接校验
INNER JOIN table2 t2_current
  ON t1.lm_id = t2_current.lm_id
WHERE t1.lm_id LIKE 'B-%'  
  AND t1.dt > '20201231'
  AND t2_current.int_val = 1
  -- 半连接排除历史记录，无null值风险
  AND NOT EXISTS (
    SELECT 1 
    FROM table2 t2_history
    WHERE t2_history.lm_id = t1.lm_id
      AND t2_history.dt = '20201231'
      AND t2_history.lm_id LIKE 'B-%' 
      AND t2_history.int_val = 1
  )

校准后的LEFT JOIN写法 #

SELECT DISTINCT t1.lm_id 
FROM table1 t1
-- 保留原逻辑的前置内连接校验
INNER JOIN table2 t2_current
  ON t1.lm_id = t2_current.lm_id
  AND t2_current.int_val = 1
-- 左连接关联历史记录，用于排除匹配项
LEFT JOIN table2 t2_history
  ON t2_history.lm_id = t1.lm_id
  AND t2_history.dt = '20201231'
  AND t2_history.lm_id LIKE 'B-%' 
  AND t2_history.int_val = 1
WHERE t1.lm_id LIKE 'B-%'  
  AND t1.dt > '20201231'
  -- 仅保留未匹配到历史记录的行
  AND t2_history.lm_id IS NULL

校准注意事项 #

• 如果需要和原查询的500行结果完全对齐，可根据重复值情况决定是否保留DISTINCT：原隐式内连接会因为t2的多匹配带出重复lm_id，去掉DISTINCT可以完全复刻原查询的行数表现。
• 所有t1端的过滤字段要换回原查询使用的t1.dt，不要误用t1.dt_valid_to。
• 相比原NOT IN写法，这两个校准后的版本执行计划会优先走t2表的索引扫描，避免全表聚合，千万级数据量下执行耗时通常能从分钟级降到秒级。

内容的提问来源于stack exchange，提问作者Atul Rai