派生表是什么
派生表就是把子查询写在 FROM 里当成一个临时结果集来用，必须带别名，可以继续参与连接、过滤与聚合。它最常见的价值是「先算小结果集，再与主表连接」，减少连接输入与聚合中间结果。MySQL 8.0.22 起支持把外层条件下推到派生表内部；在此之前或嵌套较深时，优化器可能先把整个子查询物化再连接，性能差别显著。PostgreSQL 的等价物是 FROM 子查询，优化器通常直接内联展开，多数情况下不必加 NOT MATERIALIZED 之类的干预。
基本信息
| 别名要求 | MySQL 与 PostgreSQL 都强制派生表必须有别名 |
|---|---|
| MySQL 合并开关 | derived_merge=on 时能被合并的派生表直接展开进外层查询 |
| 条件下推 | MySQL 8.0.22 起支持 derived_condition_pushdown，外层条件可推进派生表 |
| PostgreSQL | FROM 子查询一般被内联展开，等价于直接改写 |
| 复用场景 | 同一子查询需要多次引用时改用 CTE，避免重复计算 |
典型使用场景
- 先聚合再连接：把聚合结果缩到很小，再与主表连接取名称等字段
- 多层处理：先过滤、再聚合、最后取前 N
- 把窗口函数结果当普通列过滤，绕开窗口函数不能写在 WHERE 的限制
- 拆解复杂 SQL：把一段计算隔离出来，提高可读性与可测试性
- 替换临时表：不需要跨语句复用、又不希望落盘时的轻量选择
基础写法
基础写法
SELECT t.city, t.avg_amount
FROM (
SELECT city, AVG(amount) AS avg_amount
FROM orders
GROUP BY city
) t # 派生表别名必须写
WHERE t.avg_amount > 500
ORDER BY t.avg_amount DESC;
WITH city_avg AS ( # 需要多次引用时用 CTE
SELECT city, AVG(amount) AS avg_amount FROM orders GROUP BY city
)
SELECT * FROM city_avg WHERE avg_amount > 500;窗口结果过滤
窗口结果过滤
SELECT *
FROM (
SELECT id, user_id, amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
) t
WHERE t.rn = 1; # 窗口函数不能直接写在 WHERE，靠派生表绕开
# MySQL 8 也可用 CTE 写法，读起来更清晰
WITH ranked AS (
SELECT id, user_id, amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
)
SELECT id, user_id, amount FROM ranked WHERE rn = 1;合并与物化怎么判断
| 计划现象 | 含义 | 处理方向 |
|---|---|---|
| 出现 Materialize / Derived Table 节点 | 派生表被整体物化 | 简化子查询，或提升数据库版本 |
| 外层条件出现在内层子查询里 | 条件下推生效 | 保持当前写法 |
| 临时关闭 derived_merge 后计划大变 | 合并开关影响明显 | 仅在诊断时临时关闭，生产保持默认 |
| CTE 被引用多次只计算一次 | 物化一次复用 | 复杂计算优先用 CTE |
MySQL 还要求派生表的每一列都有唯一名字：子查询里出现同名列（例如连接两张表都选了 id）时必须在外层显式起别名，否则报 Duplicate column name。
多层派生表嵌套会让条件下推变弱、优化器可选的改写空间变小；超过两层时考虑用 CTE 或临时表把中间结果显式落下来。