mysql子查询物化是优化器将非相关in子查询结果缓存为带哈希索引的临时表，仅执行一次并支持o(1)查找；若含相关列、limit、order by等则退化为嵌套循环。
EXPLAIN 显示 type=ALL 或 rows 突然暴涨，但子查询单独跑很快
这不是数据量“变多”导致的自然变慢，而是优化器在某个临界点放弃了嵌套子查询的物化（materialization），转而对每一行外层记录都重复执行内层查询——也就是从「算一次、复用结果」退化成「算 N 次」。这个临界点往往藏在统计信息更新、索引失效或优化器成本估算跳变里。
常见诱因包括：
- 表统计信息过期，
ANALYZE TABLE后rows估算值翻倍，优化器误判子查询代价低，选择嵌套循环而非物化 - 外层 WHERE 条件过滤率下降（比如从 99% 降到 80%），导致驱动行数激增，放大相关子查询的重复执行开销
- MySQL 8.0+ 默认启用
optimizer_switch='semijoin=on,materialization=on'，但当子查询含LIMIT、GROUP BY或非确定性函数时，优化器会自动禁用物化
long_query_time 设为 0.5 秒后，原来不慢的嵌套查询开始进慢日志
阈值下调本身不会让 SQL 变慢，但它暴露了一个早已存在的执行计划劣化：原来 800ms 的查询在 1s 阈值下“不算慢”，现在被截住一看，发现 EXPLAIN ANALYZE 里子查询实际执行了 127 次，每次平均 3.2ms，叠加起来就是 406ms 外加调度开销——这正是“突然变慢”的感知来源。
关键要区分：
-
long_query_time是观测开关，不是性能开关 - 真正跳变的是优化器是否选择物化子查询，而这个决策依赖
optimizer_search_depth和range_optimizer_max_mem_size等隐藏参数的实际取值 - 当外层结果集超过 1000 行（MySQL 默认
max_join_size警戒线），优化器可能直接拒绝复杂嵌套，降级为全表扫描式执行
子查询被物化 vs 未被物化，在 EXPLAIN 中怎么一眼识别
看 EXPLAIN 输出的 select_type 和 Extra 字段最直接：
- 物化成功：出现
select_type=DERIVED或MATERIALIZED，且Extra含Using temporary（这是好事，说明建了临时表缓存结果） - 未物化/反复执行：出现
select_type=DEPENDENT SUBQUERY，且rows列数值与外层驱动行数接近（比如外层rows=500，子查询也标rows=480） - 特别危险信号：
Extra出现Start temporary+End temporary交替多次——代表优化器试图物化但失败后 fallback 到逐行执行
注意：MySQL 5.7 不显示 MATERIALIZED，只能靠 DEPENDENT SUBQUERY 频次和 rows 关系反推。
为什么加了索引，嵌套查询反而更慢了
索引不是万能加速器，它可能破坏优化器对子查询的物化判断。典型场景是给子查询里的 WHERE 字段加了索引，但外层关联字段没索引，导致优化器误以为「走索引查单行更快」，放弃物化，结果每行外层都触发一次索引查找+回表——IO 次数暴增。
验证方法很简单：
- 先
DROP INDEX回退，看EXPLAIN是否恢复MATERIALIZED - 再给外层 JOIN 或 WHERE 条件字段补索引，而不是只盯子查询内部
- 强制物化可用
/*+ MATERIALIZE */提示（MySQL 8.0.22+），但需确认执行计划里真出现了MATERIALIZED而非被忽略
真正的瓶颈往往不在“有没有索引”，而在“优化器信不信你能把子查询结果攒起来复用”。数据量一跨过某个规模，它的信任就崩了——这时候修索引不如改结构。
