MySQL对派生表优先尝试合并策略，满足无GROUP BY/DISTINCT/LIMIT等、无聚合函数、外层无聚合排序且单数据源等条件时自动打平；否则强制物化，支持索引优化并可手动干预。
MySQL 对派生表（即子查询生成的临时表）主要采用两种执行策略：合并（Merge）和物化（Materialization）。选对策略能显著提升查询性能，关键在于理解什么情况下会触发哪种机制，以及如何主动引导优化器做出更优选择。
什么时候会自动合并派生表？
合并是优化器优先尝试的策略，前提是子查询结构足够简单。满足以下全部条件时，MySQL 通常会把派生表“打平”进外层查询，不建临时表：
- 子查询不含 GROUP BY、HAVING、DISTINCT、LIMIT、ORDER BY 等无法下推的操作
- 子查询没有聚合函数（如 SUM、COUNT、MAX）
- 外层查询未使用 DISTINCT、GROUP BY、HAVING 或 ORDER BY
- 外层 FROM 子句中只有该派生表一个数据源（或与它直接 JOIN 的简单表）
- 整个查询涉及的表总数未超过优化器阈值（默认 61 张）
为什么有时必须物化？
当合并不可行时，MySQL 只能先执行子查询、把结果存成内部临时表，再参与后续逻辑。常见强制物化场景包括：
- 子查询含 DISTINCT、GROUP BY、窗口函数或 LIMIT
- 外层有聚合、排序或去重需求，导致无法将条件安全下推
- 派生表被多次引用（如 CTE 被反复 JOIN），物化一次可复用
- 子查询结果集较大，但外层 JOIN 条件能大幅过滤主表——此时优化器可能推迟物化，先处理主表再决定是否真要物化
物化虽慢，但 MySQL 会在临时表上自动为 JOIN 字段添加索引（例如 ON t1.f1 = dt.f1 中的 f1），缓解部分性能损失。
如何干预优化器的选择？
你不需要被动等待优化器决策，可通过以下方式主动控制：
- 启用/禁用合并：用系统变量
SET optimizer_switch='derived_merge=on'（默认开启）；临时关闭可加/*+ NO_MERGE(dt) */提示 - 强制物化：在子查询中加入一个“破坏合并”的结构，比如
SELECT ... FROM (SELECT * FROM t2 ORDER BY id LIMIT 1000000) AS dt—— 即便 LIMIT 实际无作用，也能阻止合并 - 改写替代方案：把易被物化的派生表，重构成 JOIN + 条件下推形式。例如将
(SELECT DISTINCT f1 FROM t2)改为t2直接 JOIN，并用GROUP BY或EXISTS控制语义
特别注意 ORDER BY 在派生表中的行为
很多人误以为 (SELECT * FROM t ORDER BY x) AS dt 一定能保证顺序，但合并后 ORDER BY 很可能被忽略。只有同时满足：外层无 GROUP BY/DISTINCT/ORDER BY，且派生表是唯一数据源，这个排序才有效。否则应改用窗口函数或显式 LIMIT 配合应用层处理。
