mysql 8.0+子查询默认物化但非总生效：仅限只读、非相关子查询；相关子查询、含limit/group by/窗口函数等则退化为嵌套循环，性能骤降；物化临时表无索引且可能静默落盘，需结合explain format=tree与慢日志诊断。
MySQL 8.0+ 里子查询默认走物化，但不是所有情况都生效
MySQL 优化器对子查询的处理逻辑在 5.6 到 8.0 间变化很大，现在默认倾向物化（materialization），但前提是子查询满足可物化条件。不满足时，它会退回到传统的嵌套循环（NLJ）或依赖查询（dependent subquery），性能可能断崖式下跌。
常见错误现象：EXPLAIN 显示 select_type=DEPENDENT SUBQUERY，且 Extra 列出现 Using temporary; Using filesort 或反复扫描外层表——这说明物化没触发，子查询被反复执行。
- 子查询必须是“只读”、无引用外层列的（即非相关子查询），才可能被物化；一旦用了
t1.id = t2.parent_id这类关联条件，就变成相关子查询，无法物化 - 含
LIMIT、GROUP BY、窗口函数或用户变量的子查询，MySQL 通常放弃物化 - 物化结果默认不建索引，如果后续要 join 或 in 查找，大结果集下
IN (SELECT ...)可能比等值 join 慢得多
用 /*+ NO_MATERIALIZATION() */ 强制禁用物化，有时反而更快
物化听起来高级，但实际开销不小：临时表创建、数据拷贝、内存/磁盘管理。当子查询结果很小（比如几十行），而外层表极大时，让优化器改用半连接（semi-join）+ 索引查找，往往比先物化再 hash 匹配更高效。
使用场景：你发现 IN (SELECT id FROM small_table) 执行慢，但把子查询单独跑很快，且 small_table.id 有索引。
- 加 hint：
SELECT * FROM big_table WHERE id IN (/*+ NO_MATERIALIZATION() */ SELECT id FROM small_table) - 对比
EXPLAIN中select_type是否从MATERIALIZED变成PRIMARY+SUBQUERY，同时Extra出现Using join buffer (hash join)或Using index condition - 注意：该 hint 仅在 MySQL 8.0.20+ 支持，5.7 及更早版本无效
IN vs EXISTS 在子查询重写中不是性能开关，而是语义分水岭
很多人以为把 IN 换成 EXISTS 就能“优化”，其实两者语义不同：前者对 NULL 敏感（NULL IN (1,2,NULL) 返回 NULL），后者返回布尔值。优化器在多数情况下会自动等价重写，但前提是子查询不包含外部列引用或聚合。
容易踩的坑：WHERE col IN (SELECT x FROM t2 WHERE t2.a = t1.b) 和 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a = t1.b AND t2.x = t1.col) 看似等价，但若 t2.x 允许 NULL，结果可能不一致。
- 优先用
EXISTS表达“是否存在匹配”，逻辑清晰且通常能触发 semi-join 优化 - 用
IN仅当你真需要集合成员判断，且已确认子查询结果不含 NULL 或业务可接受三值逻辑 - 别手动重写来“猜优化器”，先看
EXPLAIN FORMAT=TREE（8.0+）里的实际执行路径
物化临时表默认用 MEMORY 引擎，但超限后 silently 切到 MyISAM，容易卡住
MySQL 物化子查询时，先尝试用 MEMORY 存储结果，由 tmp_table_size 和 max_heap_table_size 控制上限。一旦超限，它会自动转存到磁盘（MyISAM 或 InnoDB 临时表），这个过程不报错，但可能引发 IO 突增、锁等待甚至 Query execution was interrupted。
典型信号：慢日志里出现 Copying to tmp table on disk，或 SHOW PROCESSLIST 显示状态长期卡在 Creating tmp table。
- 查当前设置：
SELECT @@tmp_table_size, @@max_heap_table_size - 若子查询结果常超 64MB，建议调高二者至相同值（如
256M），避免频繁切换 - 更稳妥的做法是提前预估子查询结果大小，用
LIMIT截断（如果业务允许），或拆成两步：先CREATE TEMPORARY TABLE+INSERT SELECT，显式控制引擎和索引
事情说清了就结束。真正卡住的，往往是物化没发生、或者发生了却没索引、或者切到磁盘还不知道。看 EXPLAIN FORMAT=TREE 和慢日志里的 Extra 字段，比背口诀管用。
