在SQL中使用LEFT JOIN配合IS NULL查找孤立记录究竟有什么性能隐患

NOT EXISTS通常更快，因其采用半连接机制，找到首个匹配即停止扫描，内存占用低；LEFT JOIN需完成全量连接再过滤，无索引时易退化为嵌套循环全表扫描。

LEFT JOIN + IS NULL 为什么在大表上会变慢

不是语法错，是执行逻辑天然吃资源：LEFT JOIN 必须先完成全量连接，再过滤出 c.id IS NULL 的行。这意味着数据库得为左表每一行，都去右表找一遍匹配——哪怕只想要“没匹配上的”，它也得把所有匹配尝试做完。当右表有千万级数据、又没索引时，这一步直接退化成嵌套循环+全表扫描。

• EXPLAIN 里看到 Type: ALL 或 Extra: Using where; Using join buffer 就是危险信号
• 内存压力大：JOIN 中间结果集大小至少等于左表行数，若右表字段多，实际内存占用可能翻倍
• MySQL 5.7 及更早版本对 IS NULL 条件走不了索引（即使字段有索引），必须靠 FORCE INDEX 或改写才能触发

NOT EXISTS 为什么通常更快

NOT EXISTS 是半连接（Semi Join），对左表每行只查“是否存在一个匹配”，找到第一个就停。它不构造中间结果，也不关心右表有多少匹配行——这对“找孤儿”场景是精准匹配。

• 执行计划中出现 NESTED LOOPS ANTI 或 INDEX RANGE SCAN 表示走了高效路径
• 子查询里 SELECT 1 比 SELECT * 轻量，且不会因右表字段变更导致隐式重编译
• PostgreSQL 和 SQL Server 对 NOT EXISTS 优化成熟；MySQL 8.0+ 也已支持等价转换，但旧版仍需手动改写

索引建不对，两种写法一样慢

孤立记录查询慢，90% 是索引问题，不是写法问题。关键不是“建不建索引”，而是建在哪、建什么类型。

• 右表关联字段（如 customers.id）必须有主键或唯一索引——这是 IS NULL 能走索引的前提
• 左表外键字段（如 orders.customer_id）也要单独建索引，否则 LEFT JOIN 时左表无法快速定位右表候选行
• 复合外键（如 (product_id, store_id)）必须建联合索引，顺序要和 ON 条件一致，不能只给单字段索引
• 用 EXPLAIN ANALYZE（PostgreSQL）或 EXPLAIN FORMAT=JSON（MySQL 8.0+）看实际是否用了索引，别只信 key 字段非空

WHERE 里写错条件会让 LEFT JOIN 彻底失效

把右表过滤条件塞进 WHERE，比如 WHERE c.status = 'active' AND c.id IS NULL，结果就是零行——因为 c.id IS NULL 和 c.status = 'active' 不可能同时成立。LEFT JOIN 的语义被悄悄覆盖成了 INNER JOIN。

• 业务过滤必须放 ON 子句： LEFT JOIN customers c ON o.customer_id = c.id AND c.status = 'active'
• 如果需要同时查“未匹配”和“匹配但状态不合法”的两类孤儿，得用 UNION ALL 拆开处理，不能硬塞在一个 WHERE 里
• 字段别名混淆也会触发类似问题：写成 WHERE id IS NULL 而没加表前缀，可能被解析成左表字段，永远不生效

真正上线时，最常被跳过的动作是验证右表字段是否真为 NOT NULL——如果 c.id 允许为空，c.id IS NULL 就分不清是“没匹配上”还是“匹配上了但存了 NULL”。这个点不确认，后面所有优化都是在跑偏。