MySQL 派生表物化是什么？复杂子查询如何优化？

简化版

派生表是 FROM (SELECT ...) t 这种子查询结果。MySQL 处理派生表时可能把它合并到外层查询，也可能先物化成临时表再继续查询。物化会带来中间结果、临时表和索引缺失等成本。优化复杂子查询时，要关注派生表能否下推条件、是否产生大结果集、是否可改写为 JOIN、CTE 或拆分成临时汇总表。

详细版

派生表能让 SQL 更清晰，但复杂派生表可能让优化器难以下推外层条件。例如外层只需要某个用户的数据，派生表却先把全表聚合完再过滤，就会浪费大量资源。

现代 MySQL 对派生表合并和条件下推做了很多优化，但不是所有场景都能自动处理。包含聚合、DISTINCT、LIMIT、窗口函数等操作时，更可能物化。

面试中要说明：子查询不是一定慢，关键看执行计划中是否物化、物化结果多大、条件是否能提前过滤。

完整版教学

一、派生表是什么

派生表是出现在 FROM 子句中的子查询。

它必须有别名。

外层查询把它当成一张临时结果表使用。

它常用于分步表达复杂逻辑。

但表达清晰不等于执行成本低。

二、示例 SQL

SELECT t.user_id, t.total_amount
FROM (
  SELECT user_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
) t
WHERE t.user_id = 1001;

如果优化器不能把 user_id = 1001 下推。

就可能先聚合所有用户订单。

这会产生很大的中间结果。

三、合并和物化

处理方式	含义	性能特点
merge	合并到外层查询	更容易整体优化
materialization	先生成临时结果	可能产生临时表成本
condition pushdown	外层条件下推	减少中间结果
手工改写	开发者调整 SQL	可控但要验证

四、改写示例

SELECT user_id, SUM(amount) AS total_amount
FROM orders
WHERE user_id = 1001
GROUP BY user_id;

如果业务只查一个用户，把过滤条件提前通常更高效。

这能减少扫描和聚合的数据量。

五、哪些操作容易阻碍优化

聚合可能需要先分组。

DISTINCT 需要去重。

LIMIT 会改变结果语义。

窗口函数依赖排序和窗口范围。

复杂表达式可能限制条件下推。

派生表优化的核心问题是：能不能尽早过滤，能不能避免巨大的中间结果。

六、优化步骤

查看 EXPLAIN。

确认是否出现 derived、temporary、materialized。

观察派生表估算行数。

尝试把外层条件提前。

比较 JOIN、CTE、临时汇总表等写法。

对稳定报表可以考虑预聚合。

七、误区和追问

误区：所有子查询都比 JOIN 慢。 现代优化器能优化很多子查询，关键看计划。
误区：CTE 一定更快。 CTE 也是表达方式，不同数据库和版本处理不同。
误区：派生表让 SQL 清晰就没有成本。 如果物化大结果，中间成本可能很高。
追问：怎么判断是否物化？ 看执行计划中的 derived、materialized、temporary 等信息。
追问：外层 WHERE 能否自动下推？ 有些场景能，有些被聚合、LIMIT、DISTINCT 等限制。
追问：复杂报表怎么优化？ 预聚合、汇总表、离线计算或拆分查询，避免在线大物化。

八、面试收束

回答时先定义派生表和物化。

再说明条件下推、中间结果和执行计划。

最后强调通过改写和预聚合控制成本。

简化版

详细版