mysql中的临时表与派生表的执行流程分析

CREATE TEMPORARY TABLE的执行阶段在语句执行前即完成建表，属于显式创建、会话绑定、不参与优化器重写，后续操作按普通表处理。

临时表（CREATE TEMPORARY TABLE）的执行阶段在哪

MySQL 中的 CREATE TEMPORARY TABLE 是在语句执行前就完成建表动作的，属于「显式临时表」，它不参与查询优化器的子查询展开或物化决策，而是由用户主动创建、显式使用。它的生命周期绑定会话，语句执行时可直接读写，不会被优化器重写或合并。

常见误判是认为它和派生表一样走“物化 → 扫描”流程——其实不是。临时表一旦建好，后续 INSERT、SELECT 都按普通表处理，走标准的存储引擎访问路径（如 InnoDB 的 B+ 树查找或全表扫描）。

• 建表语句本身不触发任何数据计算，只分配内存或磁盘结构（取决于 ENGINE 和数据量）
• 如果用 ENGINE=MEMORY 且数据超 tmp_table_size，会自动降级为 MyISAM（5.7）或 InnoDB（8.0+），这个切换发生在首次 INSERT 时，不是建表时
• 没有统计信息，优化器对它的行数预估固定为 1000（除非你手动 ANALYZE TABLE，但临时表不支持该命令）

派生表（Derived Table）何时被物化

派生表指 FROM 子句中的子查询，例如 SELECT * FROM (SELECT id, name FROM u

ser WHERE age > 25) AS t。它是否物化、何时物化，取决于优化器的判断，不是固定行为。

MySQL 8.0.23+ 默认启用 derived_merge=ON，此时只要满足合并条件（无聚合、无去重、无外部引用等），优化器会把派生表“展平”进外层查询，根本不生成临时结构；只有无法合并时，才会走物化流程。

• 物化动作发生在执行器初始化阶段，即第一条记录被请求前，整个子查询结果被计算并存入内部临时表
• 物化表默认使用 MEMORY 引擎，但若含 BLOB/TEXT 列、或超出 tmp_table_size，会退化为磁盘表（InnoDB 或 MyISAM）
• EXPLAIN 中看到 表名 + Type: ALL，说明已物化；若显示为普通表名或 ref/range 访问，大概率已被合并

如何强制派生表不合并（禁用 derived_merge）

当需要确保子查询独立执行（比如调试中间结果、避免谓词下推干扰逻辑），可关闭合并优化。这不是调优首选，但对分析执行流程很关键。

SET SESSION optimizer_switch = 'derived_merge=off';

之后再执行含派生表的查询，EXPLAIN 必定出现 ，且其 rows 值反映物化后的估算行数。注意：该设置仅对当前会话有效，且会影响所有后续派生表，不能只针对某一条语句。

• 也可在语句级加提示：SELECT /*+ NO_MERGE(t) */ * FROM (SELECT ... ) AS t（8.0.14+ 支持）
• 加 UNION ALL SELECT 1 FROM DUAL 这类“破坏合并条件”的写法已不推荐，不可靠且影响可读性
• 禁用后若子查询结果很大，可能显著增加内存/磁盘开销，甚至触发 max_heap_table_size 限制导致错误

临时表与派生表的磁盘落地行为差异

两者都可能落磁盘，但触发条件和机制不同。临时表是否落盘，取决于建表时指定的 ENGINE 和后续插入数据特征；而派生表是否落盘，完全由优化器在物化瞬间根据结果集大小和列类型决定，用户无法直接控制引擎。

• 显式临时表若指定 ENGINE=InnoDB，则始终走 InnoDB 文件系统，不受 tmp_table_size 限制
• 派生表永远不接受 ENGINE 指定，其底层引擎由 MySQL 内部策略选定：小结果用 MEMORY，大结果或含大字段时切到 InnoDB（8.0.13+）
• 可通过 SHOW STATUS LIKE 'Created_tmp_disk_tables' 观察磁盘临时表次数，但该计数同时包含显式临时表和派生表的磁盘实例，无法区分来源

真正影响执行流程的，不是“有没有临时结构”，而是“这个结构在哪个阶段产生、由谁控制、是否可预测”。派生表的物化时机藏在优化器决策里，而临时表的行为几乎全部暴露在 SQL 显式操作中。