MySQL 8.0子查询未必更快，因semi-join或物化转换依赖前提条件；失效场景包括配置禁用、含聚合/窗口函数、STRAIGHT_JOIN、空结果物化开销等，且NLJ索引优势可能丧失。
子查询在MySQL 8.0里常被转成semi-join或物化，但不是总更快
MySQL 8.0优化器确实会对很多IN、EXISTS子查询尝试自动重写，比如转成semi-join或MATERIALIZATION策略。但这不等于“子查询一定变快了”——实际执行效率取决于是否满足转换前提，以及转换后是否真能用上索引或减少扫描行数。
常见失效场景包括：
-
subquery_to_derived=off或optimizer_switch中禁用了semijoin（如semijoin=off） - 子查询含
GROUP BY、HAVING、WINDOW函数或UNION，直接跳过semi-join转换 - 外层查询用了
STRAIGHT_JOIN，导致优化器无法自由重排连接顺序 - 子查询返回结果为空或极小，但物化表仍触发全量构建+去重，反而多一次临时表开销
JOIN天然支持Index Nested-Loop，子查询重写后未必能复用
当t1 JOIN t2 ON t1.a = t2.a且t2.a有索引时，MySQL可直接走Index Nested-Loop Join：驱动表每行取a值，用索引快速定位被驱动表匹配行，I/O和CPU都可控。
而等价的子查询SELECT * FROM t1 WHERE a IN (SELECT a FROM t2)，即使被转成semi-join，也要求t2.a索引可用且统计信息准确；否则可能退化为MATERIALIZATION，先全扫t2建临时哈希表，再逐行查t1——此时t1的a字段有没有索引就无关紧要了，反而丧失NLJ的局部性优势。
关键差异点：
- NLJ依赖被驱动表**连接字段的索引**；semi-join物化依赖**子查询结果集大小与内存容纳能力**
-
EXPLAIN中看到Using join buffer或Using temporary，基本说明子查询没走预期路径 - 对
NOT IN，MySQL 8.0仍大概率拒绝semi-join，强制走DEPENDENT SUBQUERY，逐行执行
EXPLAIN FORMAT=JSON里找hash_join和semi_join节点才知真实策略
光看EXPLAIN文本输出容易误判。比如type: eq_ref看起来像走了索引，但若子查询被物化，实际执行时仍可能扫描整个子查询结果集。
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
必须用EXPLAIN FORMAT=JSON确认优化器最终决策：
- 搜索
"hash_join": true→ 确认启用了Hash Join（仅8.0+，且需等值连接） - 搜索
"materialization": true→ 子查询被物化，注意"rows":值是否远超预期 - 搜索
"semi_join": { "strategy": "DUPS_WEEDOUT"或"firstmatch"→ semi-join生效，但需结合"key": "a"判断是否命中索引 - 若出现
"select_type": "DEPENDENT SUBQUERY"，说明彻底放弃重写，每次外层行都重新执行子查询
复杂WHERE条件+子查询时，驱动表选择权可能被架空
JOIN语句中，优化器能综合WHERE过滤性、表大小、索引选择性选驱动表。但子查询嵌套深时，比如WHERE x IN (SELECT y FROM t2 WHERE z > 100 AND w = ?)，优化器可能被迫先执行子查询部分，失去对外层表访问方式的全局控制。
典型后果：
- 本该用
t1小表驱动t2，却因子查询先执行，t2成了事实上的“主表”，导致t1被全扫 -
push_conditions_to_derived_tables阶段失败（比如子查询含聚合），外层WHERE条件无法下推，物化表体积暴涨 - 多层嵌套子查询触发多次
resolve_subquery，每个层级都增加解析开销，而JOIN是一次性确定连接树
真正影响性能的往往不是“子查询语法本身”，而是优化器在约束条件下被迫放弃最优路径时，留下的那些不可见的执行退化点——比如物化临时表没走内存而落盘，或者semi-join的DUPS_WEEDOUT策略在高重复值场景下反复去重。这些在EXPLAIN里只显一层，得靠optimizer_trace深挖才能暴露。
