mysql执行计划中的物化(Materialization)机制_mysql子查询优化

物化是mysql优化器将子查询结果自动存为临时表再参与外层操作的执行策略，常见于in/any/some子查询，8.0.22+可通过explain format=tree显式识别materialize节点。

什么是物化（Materialization）在 MySQL 执行计划里

物化是 MySQL 优化器对某些子查询采取的一种执行策略：把子查询结果先算出来、存成临时表（内存或磁盘），再拿这个临时表去和外层表做连接或判断。它不是手动建表，而是优化器自动触发的“隐式临时表”。是否走物化，取决于子查询类型、数据量、索引、MySQL 版本（5.6+ 引入，8.0 改进较多）。

常见触发场景：IN 子查询、ANY/SOME、部分 EXISTS（尤其当子查询无相关列时）。你看到执行计划中 Extra 列出现 using temporary 或更明确的 Start materialize/End materialize（8.0.22+），基本就是它在干活。

怎么确认子查询被物化了

别只看 EXPLAIN 的 type 或 rows，重点盯 Extra 和 filtered 字段：

• MySQL 5.7：查 Extra 是否含 Using temporary，再结合子查询结构推测——但不够直接
• MySQL 8.0.22+：用 EXPLAIN FORMAT=TREE，输出里会显式出现 -> Materialize 节点，最可靠
• 开启 optimizer_trace（SET optimizer_trace="enabled=on"），查 materialized_from_subquery 字段是否为 true
• 注意：即使没显示 Materialize，也可能用了物化逻辑（比如转成 semi-join），得结合 trace 看实际执行路径

为什么有时候物化反而变慢

物化不是银弹。它省了重复计算，但代价是生成临时表的开销。以下情况容易翻车：

• 子查询结果集太大（比如返回几十万行），临时表写磁盘 + 排序 + 全表扫描外层，比嵌套循环（NLJ）还慢
• 子查询本身没走索引，物化前就得全表扫一次，放大性能问题
• 外层表很小，而子查询很大——此时更适合把外层值传进去做多次单值查询（即“反向驱动”），但优化器可能误判
• 物化表没统计信息（尤其临时表），导致后续连接选择错误的驱动表或算法

典型错误现象：EXPLAIN 显示 rows 很小，但实际执行秒级变分钟级；SHOW PROFILE 显示大量 Creating tmp table 或 Copying to tmp table 时间。

怎么干预或绕过物化

不能直接关物化，但可以引导优化器选别的路：

• 加提示：select * FROM t1 WHERE id IN (SELECT /*+ NO_MATERIALIZE */ id FROM t2)（8.0.22+）
• 改写为 JOIN：把 IN 子查询转成 LEFT JOIN ... IS NOT NULL 或 INNER JOIN，通常能避开物化并更好利用索引
• 确保子查询字段有索引：物化前若要排序/去重，没索引会强制磁盘临时表
• 控制子查询返回量：加 LIMIT（慎用，语义可能变）、或提前用 WHERE 过滤掉无效数据
• 降级兼容性：5.7 下物化能力弱，有时反而更“老实”地走 NLJ；8.0 默认激进，可通过 optimizer_switch='materialization=off' 临时关闭（不推荐长期用）

真正难处理的是那些“看起来该物化，但数据分布极端”的 case——比如子查询返回 99% 的主键值，物化后变成大临时表全扫，这时候改写 SQL 或补业务层缓存，比调参数更治本。