mysql 8.4 的 explain format=tree 可直观展示优化器改写痕迹，如子查询物化、hash join、条件下推、union 合并等，结合 optimizer_trace 和 optimizer_switch 可深入分析与验证改写行为。
看懂 EXPLAIN FORMAT=TREE 输出里的“改写痕迹”
MySQL 8.4 的优化器在准备阶段会主动重写查询，比如把 LEFT JOIN 转成 INNER JOIN（当 WHERE 条件排除了 NULL）、把子查询上拉为 JOIN、合并重复的 UNION 分支。这些动作不会出现在旧版 EXPLAIN 的 Extra 字段里，但 FORMAT=TREE 会以缩进结构直观展示最终执行计划的逻辑层级，同时标注改写类型。
关键要看输出中是否出现以下字样：
-
/* select#1 */后紧跟-> Rewrite using materialization：说明子查询被物化（临时表）而非嵌套执行 -
-> Using join buffer (hash)出现在非驱动表节点下：表示启用了 Hash Join，不是传统 Nested Loop -
-> Filter: (t2.status = 'active')出现在 JOIN 节点内部而非最外层：说明条件被下推（ICP）或提前过滤，不是最后扫完才筛 - 没有
DEPENDENT SUBQUERY字样，但原 SQL 有相关子查询：大概率已被上拉或转为半连接（SEMI JOIN）
用 optimizer_trace 抓取改写全过程
仅靠 EXPLAIN 看不到“为什么改写”，必须开 trace。它会记录从语法解析、语义检查、等价变换到物理计划生成的每一步决策，尤其适合分析优化器为何放弃你写的 STRAIGHT_JOIN 或强制合并某个派生表。
操作步骤：
- 会话级开启：
SET SESSION optimizer_trace="enabled=on,one_line=off"; - 执行目标 SQL（注意：只对当前会话下一条 SELECT/UPDATE/DELETE 生效）
- 查结果：
SELECT * FROM information_schema.OPTIMIZER_TRACE; - 重点看
steps数组中的transformations_to_nested_joins、subquery_to_derived、table_elimination这几节
常见陷阱：optimizer_trace 默认只保留最近一次 trace，且内容是 JSON 格式，别直接复制粘贴到普通编辑器——缩进错乱会导致误读；建议用 VS Code 或 jq 格式化后再分析。
对比 optimizer_switch 开关组合验证改写行为
MySQL 8.4 默认启用很多新策略，但你可以通过关闭特定开关来“退化”行为，反向验证某次改写是否由某功能触发。例如：
- 想确认是不是 Hash Join 导致性能下降？临时关掉：
SET SESSION optimizer_switch='hash_join=off';再跑EXPLAIN FORMAT=TREE，看是否退回Block Nested Loop - 发现优化器总把你的
UNION ALL合并成单表扫描？试试：SET SESSION optimizer_switch='union_all_optimization=off'; - 怀疑物化子查询拖慢首次响应？禁用：
SET SESSION optimizer_switch='materialization=off';
注意：optimizer_switch 是会话级变量，不影响其他连接；但部分开关（如 derived_merge）关闭后，某些原本能走索引的 JOIN 可能退化为全表扫描，务必在测试库验证。
识别改写失败的典型信号
优化器不是万能的，遇到复杂表达式、用户自定义函数、或跨库视图时，常会放弃改写而保留原始结构，这时要警惕性能隐患：
-
EXPLAIN FORMAT=TREE中出现/* select#2 */深度嵌套且无任何->改写提示：大概率未被上拉或物化 -
optimizer_trace里message字段含"not suitable for semi-join"或"cannot pull out"：说明等价变换被拒绝 - 执行时间波动极大（比如第一次 5s，后续 50ms），且
SHOW STATUS LIKE 'Handler_read%'显示大量Handler_read_next：可能是优化器被迫回退到低效的 Nested Loop + 无索引查找
这类情况往往需要人工干预：加 /*+ NO_MERGE() */ hint 强制不合并，或拆成中间临时表，而不是指望优化器自动修复。
