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 子句含空值时行为与 EXISTS 不同，别盲目替换；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 可用 WITH CTE，但注意：非递归 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)），即使内层有索引也大概率失效——先确认是否能把计算移到外层或预计算列上。