相关子查询逐行执行是因为依赖外层字段，MySQL 5.7及更早版本无法物化，导致主表每行都重执行子查询；而非相关子查询可能被优化器自动物化。
为什么相关子查询会逐行执行
MySQL 5.7 及更早版本对 WHERE id IN (SELECT user_id FROM logs WHERE status = 'error' AND log_time > o.created_at) 这类相关子查询基本无法物化，优化器判断它依赖外层字段 o.created_at，于是对主表每行都重新执行一次子查询。10 万行主表 → 子查询执行 10 万次 → EXPLAIN 显示 type 为 ALL 或 index，rows 列远超实际结果数。
PostgreSQL 默认走 Correlated Nested Loop，SQL Server 执行计划里反复出现同一 SELECT 节点，也都是这个信号。
- 非相关子查询（如
WHERE id IN (SELECT user_id FROM users WHERE status = 'active')）MySQL 5.6+ 可能自动物化，但不保证 -
EXISTS和IN在语义上不同：前者只关心是否存在，后者要枚举全部匹配值；但两者在相关场景下都难逃逐行探查 - 哪怕子查询带
LIMIT 1，优化器也不一定转成半连接（semi-join），需显式重写或加 hint
哪些子查询能直接改写为 JOIN
不是所有子查询都能无脑替换，关键看语义是否等价、是否引入重复行、NULL 处理逻辑是否一致。
可安全改写的典型场景：
-
IN/EXISTS子查询，且子查询只返回单列、无聚合、无GROUP BY：如SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE role = 'vip')→ 改为SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.role = 'vip' -
NOT IN→ 改为LEFT JOIN ... WHERE right.id IS NULL，但必须确认子查询结果不含NULL（否则逻辑错误） - 子查询用于字段补全：如
(SELECT name FROM users u WHERE u.id = o.user_id)→ 改为JOIN users u ON o.user_id = u.id，并把SELECT中的子查询替换成u.name
不能直接改写的情况：
- 子查询含
ORDER BY + LIMIT（如取每个用户的最新订单），需先用窗口函数或派生表聚合 - 子查询有聚合（
COUNT(*)）且未关联分组键，改 JOIN 后必须加GROUP BY并小心去重 - 主表与子查询表是一对多关系（如订单 → 订单项），直接 JOIN 会放大主表行数，应加
DISTINCT或改用EXISTS
改写 JOIN 前必须检查的三件事
漏掉任一细节，结果可能错、性能可能更差。
- 连接字段是否有索引：
orders.user_id和users.id都要有单独索引或联合索引；若缺索引，JOIN 反而比子查询还慢 - NULL 逻辑是否一致：
NOT EXISTS对 NULL 安全，但LEFT JOIN ... IS NULL要求右表连接字段非 NULL；若users.id允许 NULL，得额外加AND u.id IS NOT NULL - 是否控制行数膨胀：例如
SELECT o.* FROM orders o JOIN order_items i ON o.id = i.order_id会为每个订单项返回一行订单；若只需订单信息，必须加DISTINCT o.*或改回EXISTS
EXPLAIN 看不出问题？试试动态视图和日志
EXPLAIN 只反映预估执行计划，真实慢点可能藏在运行时。尤其在 SQL Server 和 Hive 中，瓶颈常不在 SQL 逻辑本身。
- SQL Server：查
sys.dm_exec_query_stats找耗时最高的query_hash，再用sys.dm_exec_sql_text拿出具体语句，比对着执行计划定位哪段子查询拖慢整体 - Hive on Tez：看日志里
MAP1或某个 reducer 的 task 数是否过少（如只有 8 个），小文件太多会导致并行度不足，此时调tez.grouping.split-count和hive.exec.orc.split.strategy比改 SQL 更有效 - 所有数据库：确认子查询里有没有硬编码时间表达式（如
NOW() - INTERVAL 1 DAY），这种值每次不同，预编译（PreparedStatement）无法缓存执行计划，白搭
真正容易被忽略的是：改写前没验证结果一致性，尤其是涉及 NULL、一对多、去重逻辑时，一行数据差，后面全错。
