mysql优化器如何处理子查询嵌套_mysql子查询重写与物化

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 字段，比背口诀管用。