人人都会AI编程

10.5 临时表、派生表、物化表

更新时间：2026-07-11

在写复杂 SQL 时，你经常需要把中间结果暂存一下，或者把一个子查询当作临时数据源来用。MySQL 提供了几种不同的“临时数据”形式，它们的名字听起来相似，但底层机制和使用场景差别很大。理解这些区别，可以帮助你写出更高效、更可控的 SQL。

10.5.1 临时表：会话级别的私人存储区

临时表是通过 CREATE TEMPORARY TABLE 显式创建的表，它只在当前会话中可见，会话结束时自动删除。你可以把它当作一个只在当前连接里存在的普通表，对它做增删改查、建索引等操作。

创建方式

-- 创建一个临时表
CREATE TEMPORARY TABLE tmp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 从现有表复制结构或数据
CREATE TEMPORARY TABLE tmp_orders AS
SELECT * FROM orders WHERE status = 'pending';

核心特点

  • 会话隔离：不同会话可以创建同名的临时表而互不干扰。即使你创建的临时表名称与数据库中的正式表同名，在当前会话内访问该表名时，会优先使用临时表，原表被“屏蔽”，直到临时表被删除。
  • 自动清理：会话断开后，临时表被自动删除。也可以手动 DROP TEMPORARY TABLE。
  • 支持索引和约束：你可以像操作正常表一样，在临时表上建索引，提高后续查询效率。
  • 存储与性能：临时表的结构和数据既可以存储在内存中（使用 Memory 引擎），也可以存储在磁盘上（InnoDB 引擎）。MySQL 8.0 默认使用 InnoDB 引擎存放临时表，避免了之前版本因 Memory 引擎不支持 BLOB/TEXT 导致回退到磁盘的尴尬。

典型使用场景

  • 多步骤数据处理：比如先查出满足条件的用户 ID 放入临时表，再和订单表做关联，比嵌套子查询更清晰，也方便调试。
  • 存储过程或函数内部：存储过程中需要暂存中间结果集时，临时表是最自然的选择。
  • 避免重复计算：如果一个复杂查询需要多次扫描同一份中间结果，先物化到临时表并建索引，可能比重跑子查询更快。

注意事项

  • 临时表是受事务控制的吗？如果使用 InnoDB 引擎的临时表，它是受事务控制的（但临时表的元数据在回滚时不会被删除）。但以前版本默认的 Memory 临时表不支持事务。
  • 临时表虽然方便，但创建和删除有额外开销，频繁创建大量临时表可能影响性能。
  • 在 MySQL 8.0 中，临时表的默认引擎是 InnoDB，可以通过 default_tmp_storage_engine 变量查看或修改。

10.5.2 派生表：FROM 子句中的子查询

派生表是出现在 SQL 语句的 FROM 子句中的子查询。它不是预先创建的表，而是查询执行过程中临时生成的结果集，拥有别名，并且可以作为外部查询的数据源。

示例

SELECT dept_id, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) AS dept_avg
WHERE avg_salary > 10000;

这里的 (SELECT ... GROUP BY dept_id) 就是一个派生表，它必须被赋予别名（AS dept_avg），否则语法报错。

核心特点

  • 临时性：派生表只在当前查询执行期间存在，查询结束即消失，不会持久化。
  • 可能被优化器合并：在 MySQL 5.7 及之前，派生表通常会被直接“物化”成一个临时中间结果，即使它很小。从 MySQL 5.7 开始，优化器会尝试将派生表与外层查询合并（Derived Merge Optimization），避免不必要的物化。
  • 限制：派生表必须要有别名，且列名必须唯一；在旧版本中，派生表无法使用索引（因为它是临时生成的），但优化器合并后可以消除这个限制。

何时被物化？
当 MySQL 无法将派生表合并到外部查询时（例如，派生表中有 GROUP BY、DISTINCT、UNION、聚合函数、LIMIT 等），或者优化器判断物化成本更低时，就会将派生表的结果暂存在内存或磁盘的临时表中，然后再进行外部查询。

实际使用建议

  • 尽量让派生表的逻辑简单，以便优化器合并。
  • 派生表和 CTE（公用表表达式）在许多场景可以互换，但派生表只能引用一次；如果需要在同一个查询中多次引用同一个子查询结果，应该用 CTE 或临时表。
  • 派生表的别名在本查询外部是无效的。

10.5.3 物化表：优化器的自动决策

物化（Materialization）并不是一个你可以直接写出的语法对象，它是 MySQL 优化器在处理子查询或派生表时采取的一种策略：将子查询的结果集创建成一个内部临时表（即物化表），并为这个临时表建立适当的索引，从而加速外部查询的执行。

物化的典型场景

  1. IN 子查询的优化

对于类似 SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2 WHERE ...) 的查询，优化器可能会选择将子查询 (SELECT t2.a FROM t2 ...) 的结果值拿出来存入一个带有哈希索引的临时表，然后让 t1 与这个临时表做半连接（semi-join）。这比逐行执行子查询通常快得多。

  1. 派生表（FROM 子查询）无法合并时

如前文所述，如果派生表无法被合并到外层查询，优化器就会将其结果物化。例如：

   SELECT * FROM (SELECT * FROM orders ORDER BY create_time LIMIT 100) AS recent_orders
   WHERE user_id = 123;

这里派生表使用了 ORDER BY ... LIMIT，无法简单合并，优化器会先执行子查询，将结果存入临时表，然后外部查询再基于这个临时表筛选。

物化表的关键特性

  • 优化器自动在物化表上创建索引以加速后续连接。对于 IN 子查询物化，会基于子查询中的列创建哈希索引（或 B+ 树索引）。
  • 物化表的大小受 tmp_table_size 或 max_heap_table_size 限制，如果超过了限制，会转为磁盘临时表。
  • 物化是优化器成本估算后的选择，不一定每次都发生。你可以通过 EXPLAIN 查看执行计划，如果看到了 materialization 或 using temporary 等信息，说明使用了物化。

物化表的利弊

  • 利：将子查询结果先算出来，避免多次扫描原表，尤其是子查询非常复杂或外层结果集很大时，物化能显著减少重复计算。
  • 弊：物化本身需要额外的内存/磁盘空间和时间。如果子查询结果集非常大，物化可能导致性能急剧下降。你的任务是通过良好的索引和 SQL 重写，尽力使优化器不需要物化或物化很小的结果集。

10.5.4 三种方式的对比与选择

| 特性 | 临时表 (Temporary Table) | 派生表 (Derived Table) | 物化表 (Materialized Table) |
| ------------ | -------------------------------------- | ----------------------------------------- | ------------------------------------ |
| 定义方式 | CREATE TEMPORARY TABLE 显式创建 | FROM 子句中的子查询，必须取别名 | 优化器自动生成的内部临时表 |
| 生命周期 | 当前会话期间存在或手动删除 | 单条查询执行期间 | 单条查询执行期间 |
| 是否持久化 | 关闭会话前可重复使用 | 不可重复使用，查询结束即释放 | 不可重复使用，查询结束即释放 |
| 索引支持 | 支持，可手动建索引 | 原派生表本身不能手动加索引，物化时自动建 | 优化器根据情况自动建索引 |
| 适用场景 | 多步骤处理、存储过程、避免重复扫描 | 单次查询内嵌套逻辑，替代临时表简化写法 | 优化器自动选择，用于优化子查询、派生表 |
| 是否受事务控 | InnoDB 临时表支持事务 | 不涉及 | 不涉及 |

选择建议

  • 如果逻辑特别复杂，需要多步加工，或者同一个中间结果要在多个 SQL 语句中使用，用临时表。它提供了最大的控制权和灵活性。
  • 如果只是单条查询内需要用一个子查询的结果，且子查询逻辑不复杂，用派生表（或 CTE）就够了，代码更紧凑。
  • 当你发现某条 SQL 执行计划中出现 Materialize 且性能不佳时，说明优化器选择了物化，可以尝试人工用索引覆盖子查询条件、或者改写为 JOIN，避免产生巨大的物化表。反之，如果执行计划中物化后性能很好，无需干预。

理解这三种机制，你能更好地读懂执行计划，也能在性能调优时做出更精准的判断。它们都是 SQL 编写中“分而治之”思维的不同实现，合理使用能让复杂查询既清晰又高效。