相关子查询是子查询引用外部查询列、需为外层每行重复执行的查询，易因内层无索引触发全表扫描；改写为JOIN+GROUP BY或窗口函数可避免线性性能恶化。
什么是相关子查询，为什么它容易触发全表扫描
相关子查询是指子查询中引用了外部查询的列，导致子查询无法独立执行，必须为外部查询的每一行重新计算。比如 SELECT name FROM users u WHERE age > (SELECT AVG(age) FROM users u2 WHERE u2.department = u.department) —— 这里子查询依赖 u.department，数据库无法提前物化结果，只能对每条 u 记录都执行一次内层扫描。
常见错误现象包括：执行计划中出现大量 DEPENDENT SUBQUERY（MySQL）或 Correlated Subquery（PostgreSQL），且外层行数越多，性能越线性恶化；即使外层只查 10 行，也可能引发 10 次全表扫描（尤其当内层没走索引时）。
- 子查询中若用到外部表的非索引列（如
u.department无索引），内层几乎必然全表扫描 - MySQL 5.7 及更早版本对相关子查询优化能力较弱，8.0 起引入了部分物化优化，但仍有局限
- PostgreSQL 的
LATERAL虽语义等价，但执行器更倾向使用嵌套循环+索引扫描，实际未必更慢
用 JOIN + GROUP BY 替代是最直接有效的改写方式
绝大多数相关子查询本质是“按某维度聚合后关联回原表”，完全可以用显式 JOIN 实现，且能利用索引和哈希聚合加速。
比如把上面的例子改写为：
SELECT u.name FROM users u INNER JOIN ( SELECT department, AVG(age) AS avg_dept_age FROM users GROUP BY department ) dept_avg ON u.department = dept_avg.department WHERE u.age > dept_avg.avg_dept_age;
关键点：
- 内层聚合结果集通常远小于原表，
GROUP BY后可走索引（如果department有索引） - 外层
JOIN可用Hash Join或Index Nested Loop，避免逐行重复执行 - 若只需单个聚合值（如最大订单金额），用
LEFT JOIN+COALESCE处理 NULL 更安全
用窗口函数替代（仅限需要当前行上下文的场景）
当相关子查询逻辑无法简单归约为分组聚合（例如“查每个用户最近一笔订单时间”），窗口函数往往比相关子查询高效得多。
SELECT user_id, order_time,
FIRST_VALUE(order_time) OVER (
PARTITION BY user_id ORDER BY order_time DESC
) AS latest_order_time
FROM orders;相比 SELECT user_id, order_time FROM orders o1 WHERE order_time = (SELECT MAX(order_time) FROM orders o2 WHERE o2.user_id = o1.user_id)：
- 窗口函数只需一次全表扫描 + 排序，而相关子查询在无索引时可能对每个
user_id都扫一遍全表 -
PARTITION BY列必须有索引才能高效分片，否则排序开销大 - PostgreSQL 和 MySQL 8.0+ 支持完整窗口函数；SQLite 3.25+ 也支持，但不支持
FIRST_VALUE的RANGE模式
实在无法改写时，如何缓解相关子查询的扫描压力
不是所有相关子查询都能优雅改写（比如含复杂条件、多层嵌套、或依赖运行时变量）。这时优先做三件事：
- 确保子查询中被关联的列（如
u.department）在内层表上有联合索引，且顺序匹配WHERE和GROUP BY字段 - 在 MySQL 中尝试加
/+ NO_MERGE /提示（8.0.22+），阻止优化器将子查询合并进主查询导致更差计划 - 对高频调用的相关子查询，考虑用物化临时表预计算：先
CREATE TEMPORARY TABLE dept_stats AS SELECT ... GROUP BY ...，再JOIN，尤其适合数据变更不频繁的报表场景
相关子查询的性能陷阱往往不在语法本身，而在执行路径是否可控——只要内层无法复用索引或缓存，就等于把 O(n) 操作变成了 O(n×m)。改写时别只盯着“像不像”，重点看执行计划里有没有 type: ALL 或 rows 列是否随外层行数暴涨。
