MySQL EXPLAIN FORMAT=TREE 怎样识别物化子查询

在 EXPLAIN FORMAT=TREE 中，判断子查询是否被物化，最直接的办法是找 Materialize 节点，再看它的父节点是否通过 或物化结果做索引查找。子查询表扫描位于 Materialize 下面，外层查询对临时结果的访问位于它上面，这组父子关系比单看一行关键字更可靠。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/explain.html

先得到一份容易辨认的树形计划

下面用非相关的 IN 子查询做最小示例。MySQL 是否最终选择物化由成本模型和可用转换共同决定，所以同一条 SQL 在不同数据量、索引和版本上可能出现不同计划。

-- 外层查询读取订单，子查询给出已启用客户编号集合
EXPLAIN FORMAT=TREE
SELECT o.id, o.customer_id
FROM orders AS o
WHERE o.customer_id IN (
    -- DISTINCT 让“去重后的编号集合”更容易在计划中体现
    SELECT DISTINCT c.id
    FROM customers AS c
    WHERE c.enabled = 1
);

如果优化器采用物化，典型树形片段会出现类似以下结构。具体成本、行数、节点措辞会随版本和数据而变化，因此应关注层级和对象名，而不是照抄数值。

Nested loop inner join
  Table scan on orders
  Single-row index lookup on  using 
    Materialize with deduplication
      Filter: (customers.enabled = 1)
        Table scan on customers

Materialize with deduplication 表示子查询结果被写入内部临时表并去重； 是外层执行器看到的物化结果； 则说明优化器为快速查找创建了自动键。不是每次物化都会显示完全相同的文本，但“生产临时结果”和“消费临时结果”通常都能在树中找到。

沿缩进分清谁生产临时结果、谁消费结果

FORMAT=TREE 的缩进表达迭代器包含关系。阅读时可以把节点分成三层：

• 生产者：Materialize 下方的扫描、过滤、聚合或去重节点，它们决定临时表里放什么。
• 物化对象：、内部临时表以及可能出现的自动索引，它们承接一次生成、后续复用的结果。
• 消费者：外层的单行索引查找、表扫描、半连接或反连接节点，它们决定如何读取物化结果。

官方手册说明，子查询物化通常先把结果生成到内存临时表，过大时可转为磁盘存储；优化器还可能为临时表建立哈希索引，并用唯一值去重。因而看到自动键时，不要误以为业务表多出了一条永久索引。

三个信号组合起来才足够可靠

最稳妥的判断是：同一条分支上同时存在物化节点、子查询临时对象和外层访问方式。只看到 Nested loop semijoin，却没有物化子树，通常说明优化器采用了半连接的其他策略；只看到派生表名，也不代表它一定是谓词子查询的物化。

别把派生表物化和谓词子查询物化混在一起

FROM (SELECT ...) 形成的是派生表。优化器可以把它合并到外层查询，也可以物化为内部临时表。IN (SELECT ...)、NOT IN 或某些 EXISTS 谓词则可能在半连接、物化和 IN-to-EXISTS 等策略之间选择。两者都可能出现临时结果，但问题边界不同。

• 派生表是否物化，重点看派生表节点是否被合并，以及计划中是否保留独立的物化子树。
• 谓词子查询是否物化，重点看 、自动键和 Materialize 的组合。
• 相关子查询引用外层列时，常见风险是按外层不同值反复求值；非相关子查询更容易形成一次生成、重复查找的物化结果。

用提示词做一次对照，而不是直接改生产 SQL

排查时可以在测试环境用查询块名称和 SUBQUERY 提示比较策略。这样做的目的是确认计划变化，不是把提示词当成永久优化方案。

-- 强制该查询块优先尝试子查询物化，用于生成对照计划
EXPLAIN FORMAT=TREE
SELECT o.id
FROM orders AS o
WHERE o.customer_id IN (
    SELECT /*+ QB_NAME(active_customers) SUBQUERY(MATERIALIZATION) */ c.id
    FROM customers AS c
    WHERE c.enabled = 1
);

-- 改用 IN-to-EXISTS 方向，观察 Materialize 分支是否消失
EXPLAIN FORMAT=TREE
SELECT o.id
FROM orders AS o
WHERE o.customer_id IN (
    SELECT /*+ QB_NAME(active_customers) SUBQUERY(INTOEXISTS) */ c.id
    FROM customers AS c
    WHERE c.enabled = 1
);

如果两份计划结构不同，应继续比较外层扫描行数、子查询访问方式和索引条件。类型不匹配、内层表达式为 BLOB，或多列 NOT IN 中的空值语义无法把 UNKNOWN 等同于 FALSE 时，物化可能受限。

最后用 EXPLAIN ANALYZE 判断物化是否划算

EXPLAIN FORMAT=TREE 主要显示估算成本和迭代器结构。要观察实际时间、实际行数和循环次数，可在可控环境执行 EXPLAIN ANALYZE。它会真正执行语句，因此不要直接对高风险写操作或不可控的大查询使用。

-- 该语句会真实执行 SELECT，只应在可控环境验证
EXPLAIN ANALYZE FORMAT=TREE
SELECT o.id
FROM orders AS o
WHERE o.customer_id IN (
    SELECT c.id
    FROM customers AS c
    WHERE c.enabled = 1
);

重点对照三项：Materialize 子树的实际行数是否远高于估算、物化节点的循环次数是否符合“一次生成”的预期、外层对临时结果的查找次数是否过大。若临时结果很大、命中率又低，应先检查子查询过滤条件和索引，而不是仅凭“物化”二字判断好坏。

常见问题

看到 Materialize 就代表性能差吗？

不代表。物化可以避免非相关子查询被按外层行反复执行，还能借助自动索引加速查找。是否合适要结合临时结果大小、实际循环次数和替代计划判断。

为什么同一条 SQL 在另一台机器上没有 Materialize？

数据分布、统计信息、索引、优化器开关和 MySQL 版本都可能改变成本选择。先确认版本与会话级 optimizer_switch，再对比两边的统计信息和计划。

为什么只有 EXPLAIN ANALYZE 才看到实际时间？

普通 EXPLAIN FORMAT=TREE 不执行查询，只给出优化器估算；EXPLAIN ANALYZE 会执行并给出每个迭代器的实际时间、行数和循环次数。

该优先看 JSON 还是 TREE？

想快速理解父子迭代器与物化分支时，TREE 更直观；需要程序化采集字段时，JSON 更方便，其中 materialized_from_subquery 也能提供对应线索。

参考资料

• MySQL 8.4 EXPLAIN：https://dev.mysql.com/doc/refman/8.4/en/explain.html
• 子查询物化优化：https://dev.mysql.com/doc/refman/8.4/en/subquery-materialization.html
• 派生表合并与物化：https://dev.mysql.com/doc/refman/8.4/en/derived-table-optimization.html
• 优化器提示：https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html