SQL EXISTS 与 JOIN 优化案例

exists 比 in 更快因其可短路、不回表、NULL 安全；in 遇 null 结果为 unknown，且需物化子查询结果，而 exists 仅判断存在性并常优化为半连接。

EXISTS 为什么比 IN 更快？关键在执行计划和 NULL 处理

EXISTS 不是“天生快”，而是它能短路、不回表、且对 NULL 安全。当你写 WHERE id IN (select id FROM t2)，如果子查询返回 NULL，整个条件直接变 UNKNOWN，结果为空——这常被忽略，但线上查不到数据往往就卡在这儿。而 EXISTS 只关心“是否存在一行”，压根不取值，也不怕 NULL。

• IN 会把子查询结果全拉出来建哈希集（或走嵌套循环），内存占用高，还可能触发隐式类型转换
• EXISTS 通常转成半连接（semi-join），优化器更容易下推条件，尤其外层有 WHERE 过滤时
• 如果子查询里有 ORDER BY 或 LIMIT，IN 会报错，EXISTS 完全不受影响

SELECT * FROM orders o  WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.status = 'active');

JOIN 改写 EXISTS 的常见翻车点

想用 JOIN 替代 EXISTS？小心重复行和性能倒退。很多同学看到“能 JOIN 就 JOIN”，结果一上线发现订单数翻倍——因为一个订单对应多个客户地址、多条日志，JOIN 直接把主表撑开。

• JOIN 是全连接语义，只要匹配就保留，天然带去重成本；EXISTS 是布尔判断，无此问题

• 如果只是检查存在性，JOIN 后还得加 DISTINCT 或 GROUP BY，反而增加排序/聚合开销

• 唯一安全的等价改写是 LEFT JOIN ... ON ... WHERE right_table.id IS NOT NULL，但得确保 right_table.id 非空且有索引

• 外键字段没索引？JOIN 和 EXISTS 都慢，但 EXISTS 至少不放大结果集

• 子查询里用了 SELECT *？优化器可能无法识别为半连接，强制走嵌套循环

EXISTS 子查询里该写 SELECT 1 还是 SELECT *？

写 SELECT 1。不是玄学，是优化器识别信号。mysql 和 postgresql 都会把 SELECT 1 明确标记为“只判存在”，而 SELECT * 可能让优化器误以为你要取字段，进而拒绝半连接优化。

• 即使子查询只有 WHERE 条件，也别省略 SELECT 子句，否则语法错误
• SELECT NULL 也行，但 SELECT 1 最通用、最无歧义
• 不要写 SELECT id，除非你真要用那个值——它可能触发额外列读取，尤其当 id 不在索引覆盖范围内时

WHERE EXISTS (SELECT 1 FROM logs l WHERE l.order_id = o.id AND l.type = 'pay')

EXISTS 跟 LEFT JOIN + IS NOT NULL 性能差多少？看执行计划，别猜

差别可能为零，也可能差三倍——取决于优化器是否能把 LEFT JOIN ... IS NOT NULL 识别为半连接。PostgreSQL 12+、MySQL 8.0.20+ 都支持这种转换，但前提是：

• JOIN 条件必须是等值，且字段类型严格一致（比如 int 对 BIGINT 就可能失败）

• WHERE 中的 IS NOT NULL 必须作用于右表的非空约束列（如主键或定义了 NOT NULL 的字段）

• 右表不能有 WHERE 独立过滤（比如 WHERE r.status = 'done'），否则优化器大概率放弃转换

• 用 EXPLAIN ANALYZE 对比两者实际 Rows Removed by Filter 和 Loops 次数，比看文档靠谱得多

• 如果右表很小（

事情说清了就结束