MySQL在执行子查询时如何通过物化技术提升检索速度？-mysql教程-PHP中文网

物化仅在满足条件时自动触发，并非所有in子查询都启用；mysql 5.6+对非相关子查询默认启用，但遇order by、limit、相关列、group by等会失效，需explain format=tree确认materialize节点。

mysql在执行子查询时如何通过物化技术提升检索速度？

物化只在满足条件时自动触发，不是所有IN子查询都走这条路

MySQL 5.6+ 对非相关子查询（即子查询不引用外层表字段）默认启用物化，但前提是它能安全推断语义。比如 WHERE id IN (SELECT user_id FROM logs WHERE status = 'done') 会物化；而 WHERE id IN (SELECT user_id FROM logs WHERE status = 'done' ORDER BY created_at LIMIT 10) 就不会——因为 ORDER BY 和 LIMIT 让优化器放弃物化，退回到嵌套循环执行。

常见失效场景包括：

子查询含相关列，如 t1.id = t2.parent_id
用了 GROUP BY、DISTINCT、UNION 等改变结果确定性的操作
返回多列，触发错误 Operand should contain 1 column(s)
optimizer_switch 中关闭了 materialization=off

EXPLAIN FORMAT=TREE 是确认物化是否生效的唯一可靠方式

老版本靠 Extra: Using temporary 猜测，8.0.22+ 必须用 EXPLAIN FORMAT=TREE 查看输出里是否有 MATERIALIZE 节点。例如：

-> Materialize {
    -> Filter: (logs.status = 'done')
       -> Table scan on logs
}

如果没看到这个节点，哪怕 EXPLAIN 显示 type=ALL 或 rows 很小，也不能说明物化起了作用——可能走了半连接（FirstMatch 或 DuplicateWeedout），也可能根本没优化。

更进一步验证，可开启 optimizer_trace，检查 materialized_from_subquery 字段是否为 true。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

物化表本身不建索引，但优化器会自动加哈希索引加速查找

物化不是你手动 CREATE TEMPORARY TABLE，而是优化器内部行为：它把子查询结果写入内存临时表，并默认构建哈希索引（HASH），使 IN 判断变成 O(1) 查找。但如果结果集超过 tmp_table_size 或 max_heap_table_size，就会落盘并切换成 B+ 树索引，此时性能下降明显。

这意味着：

物化前子查询必须能走索引，否则第一次全表扫就拖慢整体
物化表无统计信息，若外层表很小、子查询很大，优化器可能误选驱动表
哈希索引不支持范围查询，所以 WHERE col IN (...) 高效，但 col > ANY(...) 不会受益于物化

当物化反而变慢时，用 NO_MATERIALIZATION() hint 或 JOIN 改写来绕过

物化省了重复执行，但代价是生成临时表。如果子查询返回几十万行，或外层表仅几百行，物化开销可能远超嵌套循环。典型现象是 SHOW PROFILE 显示大量 Creating tmp table 或 Copying to tmp table 时间。

干预手段有限但有效：

加优化器提示：SELECT * FROM t1 WHERE id IN (SELECT /*+ NO_MATERIALIZATION */ id FROM t2)
改写为 JOIN：把 IN 拆成 INNER JOIN + DISTINCT，让优化器有机会用 Index Nested-Loop Join
确保子查询字段有覆盖索引，避免物化前排序/去重强制落盘

物化是隐式机制，不暴露表名、不持久、每次查询独立生成——你没法 DESCRIBE 它，也没法给它加索引。真正可控的，只有输入（子查询写法）和环境（索引、配置、hint）。