相关子查询必然触发多次扫描，因其依赖外层行的列值（如u.department），无法提前物化，必须为外层每一行重新执行内层查询；若内层无索引，100行外层数据将引发100次全表扫描，性能线性恶化。
相关子查询为什么必然触发多次扫描
相关子查询不是“执行一次、复用结果”，而是每处理一行外部数据，就重新执行一遍内层查询。数据库无法提前物化它，因为子查询里引用了外部表的列（比如 u.department），每次都要代入新值重算。
常见错误现象包括：MySQL 执行计划中出现大量 DEPENDENT SUBQUERY，PostgreSQL 中显示 Correlated Subquery；外层返回 100 行，内层就可能扫 100 次全表——尤其当内层 WHERE 条件没走索引时。
- 若外部行数多、内层又无索引，性能呈线性恶化，不是“慢一点”，而是“数据翻倍，耗时翻倍”
- MySQL 5.7 及更早版本几乎不优化相关子查询；8.0 虽引入部分物化，但仅适用于简单聚合，且依赖统计信息准确度
- PostgreSQL 的
LATERAL语义等价，但执行器更倾向用索引嵌套循环，实际未必比 MySQL 更快
EXISTS 和 OR EXISTS 是隐性全表扫描放大器
OR EXISTS 这类结构在执行计划里常表现为 FILTER 节点带多个子路径，最小 ID 的子路径若返回行数多，会反复驱动更大 ID 的子路径执行——本质是嵌套循环的变体，极易放大 I/O。
典型表现：单独看 EXISTS 子句很快（Elapsed: 00:00:00.01），合到主查询后逻辑读暴涨到上万；EXPLAIN 显示 ID=6 的子路径被调用数千次。
- 不要假设“子句快，整体就快”——组合逻辑可能彻底改变执行顺序和代价评估
- 用
UNION拆分OR EXISTS通常比原写法快两个数量级，逻辑读可从 12000 降到 11 - 若不能改代码，可尝试加
/*+ USE_INDEX(...) */HINT 强制走索引，但效果不稳定，且不同版本语法差异大
怎么一眼识别子查询正在拖垮性能
别等用户投诉。直接看执行计划中最容易暴露问题的三个信号：
-
type列出现ALL或index（非覆盖索引），且对应行的Extra含Using where; Using join buffer - 有
DEPENDENT SUBQUERY或UNCACHEABLE SUBQUERY，且该行rows值远大于外层预估行数 -
key_len明显偏小（比如联合索引三列，只用了第一列），说明最左前缀没对齐，子查询条件根本没命中索引
执行 EXPLAIN FORMAT=JSON 比传统格式更能看清嵌套层级和实际驱动次数——尤其注意 dependent_contexts 字段是否为空。
替代方案选型：JOIN、窗口函数、还是临时表
90% 的相关子查询都可以不用子查询。关键看它想表达什么逻辑：
- “查每个部门平均年龄以上的人” → 改用
JOIN + GROUP BY子查询，内层聚合结果集小，外层能走哈希连接或索引嵌套循环 - “查每个用户最新一笔订单时间” → 用窗口函数
FIRST_VALUE(order_time) OVER (PARTITION BY user_id ORDER BY order_time DESC)，一次扫描+排序搞定，避免为每个 user_id 都扫一遍 orders 表 - 结果集较大（>10 万行）且需复用多次 → 显式建临时表或 CTE（带
MATERIALIZED提示），比让优化器猜更可控
真正难优化的是那些依赖当前行上下文、又无法归约为分组或排序的逻辑——比如“取上一条满足某条件的记录”，这时可能得接受部分扫描，或把计算下推到应用层缓存。
