SQL 相关子查询的性能问题分析
相关子查询比JOIN慢是因为每处理一行外层数据都要重新执行一次子查询，导致N×M级计算；而JOIN通常只需一次哈希构建和一次扫描。
相关子查询为什么比 JOIN 慢得多
因为每次外层行扫描时，WHERE 或 SELECT 中的子查询都要重新执行一次，数据量大时会形成 N×M 级计算。比如外层 10 万行、内层平均查 100 行，实际执行就是 1000 万次逻辑读——而等价的 JOIN 通常只需一次哈希构建 + 一次扫描。
常见错误现象：EXPLAIN 显示子查询被标记为 DEPENDENT SUBQUERY，且 rows 列数值随外层行数线性增长；在 MySQL 5.7+ 或 PostgreSQL 中，EXPLAIN ANALYZE 能直接看到子查询被调用次数。
- MySQL 8.0 默认仍不自动重写相关子查询为 JOIN，除非开启
optimizer_switch='semijoin=on,materialization=on' - PostgreSQL 对
IN和EXISTS相关子查询优化较好，但SELECT (SELECT ...)这类标量子查询仍大概率走嵌套循环 - SQL Server 在兼容级别 130+ 下会对部分相关子查询做自动 unnest，但遇到聚合或
TOP就失效
哪些场景下相关子查询无法避免
不是所有情况都能用 JOIN 替代。典型硬需求包括：
- 需要按外层每行动态计算一个标量值，比如
SELECT id, (SELECT COUNT(*) FROM logs l WHERE l.user_id = u.id AND l.created_at > u.last_login) AS recent_log_count FROM users u - 业务逻辑依赖外层字段参与子查询过滤，且该字段在 JOIN 后无法精确对齐（如多对多关系中需限制“最新一条”而非“任意一条”）
- 权限模型中逐行校验（如
WHERE EXISTS (SELECT 1 FROM acl WHERE acl.user_id = u.id AND acl.resource = 'report' AND acl.level >= 3)），此时EXISTS是语义必需，但可考虑物化权限视图缓解
注意： IN 子句含空值时行为与 EX 不同，别盲目替换；
NOT IN (subquery) 遇到 NULL 会整个返回空结果集，这是语义陷阱，不是性能问题。
用衍生表/CTE 预计算替代相关子查询
把子查询从“每行一次”变成“整体一次”，核心是提前聚合或去重。关键不是换语法，而是识别可提取的公共计算逻辑。
例如原查询：
SELECT u.name, (SELECT MAX(o.amount) FROM orders o WHERE o.user_id = u.id) AS max_order FROM users u;
可改写为：
SELECT u.name, agg.max_order FROM users u LEFT JOIN ( SELECT user_id, MAX(amount) AS max_order FROM orders GROUP BY user_id ) agg ON agg.user_id = u.id;
- 如果子查询含复杂条件（如“每个用户最近 30 天最大订单”），把时间条件提到预计算层，避免在 JOIN 后再
WHERE - PostgreSQL 可用
WITHCTE，但注意：非递归 CTE 在 12+ 版本默认被物化（即执行一次），而 MySQL 8.0 的 CTE 默认不物化，需加/*+ MATERIALIZE */提示 - 当预计算结果很大（如千万级用户对应千万级聚合结果），JOIN 可能引发笛卡尔积风险，此时应先用
WHERE user_id IN (SELECT DISTINCT user_id FROM orders)缩小外层范围
索引怎么建才真正生效
相关子查询的索引有效性极度依赖谓词结构。只建 (user_id) 单列索引往往不够，因为优化器仍需回表取值或过滤其他字段。
- 对于
SELECT (SELECT col FROM t WHERE t.a = u.x AND t.b > 10 ORDER BY t.c DESC LIMIT 1)，理想索引是(a, b, c)—— 把关联字段放最左，过滤字段居中，排序/返回字段放右 - MySQL 中若子查询含
GROUP BY或HAVING，单列索引基本无效，必须覆盖所有分组和过滤字段 - PostgreSQL 对表达式索引支持更好，比如子查询里有
LOWER(email)，可建CREATE INDEX idx_users_lower_email ON users (LOWER(email))
最容易被忽略的一点：相关子查询中的字段如果来自外层表的函数结果（如 DATE(created_at)），即使内层有索引也大概率失效——先确认是否能把计算移到外层或预计算列上。
