17370845950

子查询在驱动表极小、被关联字段有索引、仅需布尔判断时比JOIN快；典型场景为权限校验、白名单过滤等；NOT EXISTS可高效替代LEFT JOIN+IS NULL，IN子查询适用于结果集可控且无需关联表数据的情况。

什么时候子查询确实比 JOIN 快

MySQL 8.0+ 或 PostgreSQL 中，当驱动表极小（比如 EXISTS 子查询可能比 JOIN 更快。典型场景是权限校验、白名单过滤、状态兜底检查。

• 驱动表

  

  必须小：大表作为外层会导致子查询重复执行太多次，反而更慢
• 被关联字段要有索引：否则每次子查询都全表扫描，性能雪崩
• 不要返回子查询中的列：只用 EXISTS 或 IN 做布尔判断，避免 SELECT * 拉取冗余数据
• MySQL 5.7 及更早版本对 IN (subquery) 优化差，优先选 EXISTS

EXISTS 替换 LEFT JOIN + IS NULL 的真实写法

想查“没被分配任务的用户”，传统写法是 LEFT JOIN task ON user.id = task.user_id WHERE task.id IS NULL，但若 task 表巨大且 user 表仅几百行，改用 EXISTS 子查询往往更快：

SELECT u.* FROM user u
WHERE NOT EXISTS (
  SELECT 1 FROM task t WHERE t.user_id = u.id
);

• NOT EXISTS 能利用 task.user_id 索引快速短路，而 LEFT JOIN 会先生成中间结果再过滤
• SELECT 1 是惯用写法，语义清晰且避免优化器误判字段依赖
• 如果 task.user_id 没索引，加索引比换写法更重要

IN 子查询在什么条件下能安全替代 INNER JOIN

当只需要主表字段、不关心关联表内容，且子查询结果集可控（SELECT id FROM config WHERE type = 'active' 返回几十到几百个值），IN 子查询可读性更高、执行计划更稳定：

SELECT * FROM order 
WHERE status_id IN (SELECT id FROM status WHERE is_final = true);

• PostgreSQL 对 IN (subquery) 会自动内联并做哈希 Semi-Join，效果接近 INNER JOIN
• MySQL 8.0+ 同样支持物化子查询优化，但若子查询返回超 1000 行，仍建议改用临时表或 JOIN
• 绝对不要写 IN (SELECT ...) 且子查询含 NULL：结果永远为 UNKNOWN，整行被过滤掉

为什么有时候换了写法反而更慢

最常踩的坑不是语法，而是执行计划没变：

• 优化器判定子查询“不可推导”，退化成嵌套循环（DEPENDENT SUBQUERY），每行都重新执行
• EXISTS 子查询里用了函数或计算字段（如 WHERE YEAR(created_at) = 2025），导致索引失效
• 外层 WHERE 条件太松，驱动表实际扫描行数远超预期，放大子查询开销
• 使用了 IN 但子查询返回 NULL，触发三值逻辑陷阱，结果集异常缩水

真正决定快慢的是执行计划里的 type 字段——看到 eq_ref 或 range 才算有效利用索引；如果还是 ALL 或 index，换写法毫无意义。