修复MySQL多重嵌套视图导致的查询计划退化

先抛一个核心结论：MySQL的嵌套视图一旦超过三层，优化器就会主动放弃代价估算和条件下推。这不是配置参数能调整的，而是结构性失效，必须从根本上拆解，不能硬扛。

很多人在遇到这类问题时，习惯性地盯着EXPLAIN的输出看。你看到的是Seq Scan on v_orders_summary，以为优化器只扫描了一张表，心里踏实。但实际背后可能是v_customers_active → v_region_map → customers三层视图层层展开，外层那个WHERE country = 'CN'根本没有下推到regions表的节点上。

要从EXPLAIN里找出问题，有几个关键的排查信号：

• 看rows列：EstimatedRows和ActualRows的差值如果超过三个数量级（比如预估120行，实际扫描150万行），那就是条件下推失败的铁证，基本不用怀疑。
• 看select_type：如果出现DEPENDENT SUBQUERY，并且rows值远高于基表统计行数，说明子查询被反复执行——这就是所谓的“每次扫描都跑一次”。
• 看Extra列：一旦出现Using temporary; Using filesort或者Using join buffer (Block Nested Loop)，基本可以判定优化器已经降级，查询计划出现明显退化。

用CTE替代嵌套视图时最容易踩的三个坑

许多人的第一反应是用CTE替代嵌套视图，这没错，但CTE不是语法糖那么简单。它会改写优化器的决策路径，一旦写错，就会强制物化、阻断下推，结果比原来的视图还慢。

• 在CTE里写SELECT *：看似省事，实际是大坑。多余的列会阻止外层WHERE条件下推到底层基表，拖慢I/O和page fault，得不偿失。
• 多个CTE交叉引用：比如a AS (SELECT * FROM b)，b AS (SELECT * FROM a)，这种写法会让优化器退化为全物化，失去线性执行的优势。本质上是把问题从视图嵌套变成了CTE死循环。
• 中间CTE加ORDER BY或LIMIT：这会触发无谓的排序或截断，后续无法复用结果集。在MySQL 8.0.23之前，默认情况下CTE不会被物化，可能导致同一段逻辑被重复执行两次。

什么时候该建临时表而不是继续扁平化

有些场景下，硬去扁平化反倒不如直接建临时表。当CTE的表现不稳定、中间结果超过1万行、或者需要多次JOIN和聚合时，临时表是更可控的选择——它能帮优化器获得准确的行数统计，且索引可以显式管理。

• 用CREATE TEMPORARY TABLE tmp_orders AS SELECT ...固化中间结果。
• 立刻执行ALTER TABLE tmp_orders ADD INDEX idx_user_id (user_id)——记住，没有索引的临时表做JOIN就是全表扫描，性能比嵌套视图还差。
• 避免用tmp_*这类泛化前缀，ORM可能生成同名表导致冲突，排查起来更麻烦。
• 如果子查询本身涉及窗口函数或GROUP BY，临时表比CTE更容易让优化器识别真实基数，执行计划会更准确。

JOIN能替代的嵌套，优先重写而非拆分

很多所谓的“嵌套”问题，本质上其实是半连接或存在性判断。硬写成子查询反而会触发DEPENDENT SUBQUERY，性能直接崩盘。此时优先尝试用JOIN来重写。

• 把WHERE id IN (SELECT user_id FROM logs WHERE status = 'success')改成INNER JOIN logs ON t.id = logs.user_id AND logs.status = 'success'——这条改写往往最简单也最有效。
• 必须给logs(status, user_id)建复合索引，等值条件放在最前面，这是执行计划正确的关键。
• 避免JOIN (SELECT ...) AS t这种派生表写法——它仍然是个黑盒，优化器不一定能看透里面的逻辑，未必比原写法好。
• 如果是NOT IN的逻辑，要特别注意NULL值会导致整行被丢弃，且无法走哈希连接。优先改用NOT EXISTS，同时确保logins(user_id, login_time)有复合索引。

说到底，真正的卡点不在语法怎么写，而在于你是否能人工验证每一层的行数分布与索引命中情况。预估和实际一旦偏差超过三个数量级，所有的优化都只是在补漏，不是在治病。这才是问题的核心。