子查询效率更低主因是物化或索引下推失效，导致退化为全表扫描或落盘；而JOIN可利用Index Nested-Loop及复合索引高效定位，EXISTS/IN在满足条件时亦可被优化为semi-join。
子查询 JOIN 效率更低，不是因为“子查询”本身慢，而是它大概率触发物化（materialization）或失去索引下推能力，让原本能走 Index Nested-Loop 的路径被迫退化成全表扫描或临时表落盘。
子查询物化后丢失 NLJ 索引优势
物理表 JOIN 时，若被驱动表连接字段有索引，MySQL 可用 Index Nested-Loop Join（NLJ）：驱动表每行取值，直接用索引定位匹配行，I/O 极小。但子查询一旦被物化（比如 EXPLAIN FORMAT=JSON 中出现 "materialization": true），优化器就无法把外层条件“下推”进子查询执行过程——它得先完整算出子查询结果（哪怕只要一行），再拿这个结果集去跟外层匹配。此时子查询字段有没有索引，对外层 NLJ 完全没用。
- 典型场景：
SELECT * FROM users u JOIN (SELECT user_id, status FROM orders WHERE status = 'paid') o ON u.id = o.user_id - 问题：子查询里
WHERE status = 'paid'若没走orders(status, user_id)复合索引，就会全表扫 orders；物化后，u.id也用不上索引去反查 - 对比物理表 JOIN：
users JOIN orders ON ... AND orders.status = 'paid'，只要orders(user_id, status)有索引，就能高效定位
EXISTS/IN 被重写为 semi-join 时反而更轻量
真正要警惕的不是“子查询语法”，而是“子查询是否可提升”。PostgreSQL 和 MySQL 8.0+ 都会尝试把简单 IN 或 EXISTS 提升为 semi-join（如 Hash Semi Join），这时它和物理表 JOIN 执行路径几乎一致。但一旦子查询含 GROUP BY、LIMIT、ORDER BY 或聚合函数，提升失败，只能物化——这就是效率断崖的起点。
- 安全写法：
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid')，配合orders(user_id, status)索引 - 危险写法：
SELECT * FROM users u JOIN (SELECT user_id, MAX(created_at) FROM orders GROUP BY user_id) o ON u.id = o.user_id，GROUP BY 强制物化整张 orders 表 - 验证方法：看
EXPLAIN FORMAT=JSON里有没有"semi_join"节点，以及"key"是否命中预期索引
子查询嵌套层级导致执行计划不可控
物理表 JOIN 的执行顺序由优化器基于统计信息估算代价决定，相对稳定。但子查询嵌套越深，优化器越难准确估算中间结果集大小，容易误判策略。比如三层子查询中某一层返回 10 行，另一层返回 10 万行，物化后内存撑爆，自动转成磁盘临时表，Using temporary 就出现了——而同样逻辑用物理表 JOIN + 合理索引，可能全程走内存哈希。
- 常见陷阱：
(SELECT ... FROM (SELECT ... FROM t1) t2) t3这类嵌套，每一层都可能独立物化 - 性能拐点：子查询结果集 >
tmp_table_size或max_heap_table_size，立刻落盘 - 排查重点：
EXPLAIN中type是ALL还是ref，Extra是否含Using filesort或Using join buffer
真正卡住性能的，往往不是语法选 JOIN 还是子查询，而是物化与否、索引能否下推、中间结果集是否失控——这些在 EXPLAIN FORMAT=JSON 里才看得清，光看文本 EXPLAIN 容易误判。
