mysql子查询物化是优化器自动将非相关in子查询结果缓存为带哈希索引的临时表，仅执行一次并支持o(1)查找；若子查询含相关列、limit、order by等则退化为嵌套循环。
MySQL子查询物化不是你手动建临时表，而是优化器在执行计划里自动“缓存结果”的机制——它把子查询算一次、存成带索引的临时表，后续全走查表+索引匹配，避免重复执行。
IN子查询为什么默认会触发物化
当写 WHERE col IN (SELECT ...) 且子查询不依赖外层（即无相关列），MySQL 5.6+ 默认启用物化。因为逐行执行子查询成本太高：外层 10 万行，子查询就得跑 10 万次。
- 物化只在首次需要结果时执行子查询，生成一个去重后的临时表（
SELECT DISTINCT效果已内置） - 该临时表默认用内存哈希索引，查找
col IN (物化表)是 O(1) 时间 - 若结果集超
tmp_table_size或max_heap_table_size，自动落盘并切为 B+ 树索引 - EXPLAIN 中看到
Extra列出现Start materialize（8.0.22+）或Using temporary（老版本），基本就是它在起作用
物化不生效的常见原因
不是所有 IN 子查询都会被物化，以下情况会退回到嵌套循环（Nesting Loop）执行：
- 子查询含相关列（比如
WHERE t1.id = t2.parent_id），无法提前计算结果 - 子查询用了
LIMIT、ORDER BY、GROUP BY或DISTINCT（MySQL 认为语义不可预测，主动禁用物化） - 子查询返回多列（
IN只接受单列，多列直接报错Operand should contain 1 column(s)） - MySQL 版本低于 5.6，或优化器开关被关：检查
optimizer_switch是否包含materialization=on
如何确认物化是否真正起效
光看 EXPLAIN 不够，得结合执行过程验证：
- 执行
EXPLAIN FORMAT=TREE（8.0+），搜索关键词MATERIALIZE，能看到物化表构建和连接路径 - 开启
optimizer_trace，查materialized_from_subquery字段是否为true - 对比加
/*+ NO_MATERIALIZATION() */hint 后的执行时间——如果差异显著（比如从 2s 降到 200ms），说明原计划确实在用物化 - 注意：物化表本身不持久，每次查询独立生成；也不会出现在
SHOW PROCESSLIST的表名里，它是完全隐式的
物化与半连接优化的关系
物化只是 MySQL 处理子查询的其中一种策略，常和半连接（semi-join）优化协同工作：
- 当物化表作为驱动表参与连接时，优化器可能选
FirstMatch或DuplicateWeedout等半连接算法加速 - 但若子查询表本身有唯一索引，优化器更倾向用
Table Pullout（把子查询表直接拉到外层做 JOIN），而不是物化 - 也就是说：
MATERIALIZE和SEMIJOIN不是互斥选项，而是一组可组合的优化策略；最终走哪个，取决于成本估算，不是人为指定
真正容易被忽略的是：物化表默认去重，但如果你的业务逻辑依赖重复值（比如统计频次），就不能靠 IN + 物化来实现——得换 EXISTS 或显式 JOIN，否则语义就变了。
