临时表与派生表是什么
临时表（CREATE TEMPORARY TABLE）是会话级的表，用于暂存中间结果，会话结束自动清理。派生表是把子查询写在 FROM 后面当作一张表参与后续查询，它的作用域只在当前语句内。8.0 的优化器默认会尝试把派生表「合并」进外层查询（derived merge），从而让外层条件能下推到底层表；无法合并时会物化成内部临时表，这就是派生表查询突然变慢的常见原因。
基本信息
| 临时表语法 | CREATE TEMPORARY TABLE 名 (列定义 ...) |
|---|---|
| 可见范围 | 仅创建它的会话可见，会话结束自动删除 |
| 派生表写法 | FROM (SELECT ...) AS 别名 |
| 优化行为 | derived_merge 开启时优先合并，否则物化为内部临时表 |
| 落盘条件 | 物化结果过大超过内存临时表上限时落磁盘 |
典型使用场景
- 多步数据处理：把第一步聚合结果放进临时表，后续多轮查询反复使用
- 大列表过滤：把一批 ID 先写入临时表，再用 JOIN 替代超长 IN 列表
- 分步排查：拆开复杂查询，逐步把中间结果物化以便对照行数
- 批量更新前筛选：先把目标主键集合落到临时表，再按集合更新，避免重复扫描
- 替代复杂派生表：当派生表被反复引用时改用临时表，语义更清晰且只计算一次
两种写法对照
两种写法对照
-- 临时表：会话级，可多次引用，用完显式删除
CREATE TEMPORARY TABLE tmp_top (
user_id BIGINT PRIMARY KEY, total DECIMAL(14,2)
) ENGINE=InnoDB;
INSERT INTO tmp_top
SELECT user_id, SUM(amount) FROM appdb.orders GROUP BY user_id ORDER BY SUM(amount) DESC LIMIT 100;
SELECT * FROM tmp_top ORDER BY total DESC LIMIT 10;
DROP TEMPORARY TABLE tmp_top;
-- 派生表：只在当前语句内有效
SELECT t.user_id, t.total FROM (
SELECT user_id, SUM(amount) AS total FROM appdb.orders GROUP BY user_id
) AS t WHERE t.total > 10000;选择建议
| 情形 | 建议写法 | 原因 |
|---|---|---|
| 中间结果被多次引用 | 临时表 | 只计算一次，后续直接读 |
| 子查询可被外条件下推 | 派生表 | 合并后等价于一次查询，更省 |
| 派生表需要聚合且外层条件多 | 改临时表或改写查询 | 合并失败会物化，代价高 |
| 需要索引或主键加速 | 临时表 | 派生表无法建索引 |
| 只是一次性过滤 | 子查询或 JOIN | 避免额外的建表与清理开销 |
临时表与真实表同名时，会话内优先访问临时表：这会制造「数据确实写进去了但别人查不到」的假象。命名统一加 tmp_ 前缀能避免这类混淆。
用 EXPLAIN 观察派生表是否被物化：出现物化相关提示说明未能合并，此时应考虑改写查询或改用临时表，并关注是否落磁盘。