SQL派生表优化实战：物化机制与LATERAL JOIN进阶指南

时间：2026-08-17 | 作者：星河游者 | 阅读：0

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

你有没有写过这种SQL？

SELECT * FROM (SELECT user_id, order_date, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rnFROM orders) tWHERE rn = 1;

逻辑简单，意思清楚。

但跑了三分钟还没出来。你试了改JOIN、加索引、调参数，效果都不明显。

这不是你SQL写得不对，而是派生表（Derived Table）的物化机制在背后“搞事情”。

今天就把派生表的性能陷阱彻底拆开讲一遍。

一、先搞懂派生表是什么

派生表，就是FROM子句里的子查询。

它本质上是一个“临时结果集”。数据库会先执行子查询，把结果存到一个临时表里，然后外层查询再去读这个临时表。

SELECT *FROM (SELECT user_id, order_amount FROM orders WHERE order_date > '2026-01-01') AS dtWHERE dt.user_id = 12345;

这个写法有两大潜在问题：

问题一：物化（Materialization）

数据库会先把子查询的结果“物化”成一个临时表，存到内存或磁盘里。

然后，外层查询再扫描这个临时表。

问题在于，子查询的WHERE order_date > '2026-01-01'可能返回50万行。

即使外层只需要user_id = 12345的那一条，数据库也要先物化50万行，然后再过滤。该扫的行数一行没少。

问题二：临时表没有索引

物化出来的临时表默认没有索引。

外层查询在临时表上做过滤时，只能全表扫描。

如果临时表有几十万行，这个全表扫描的代价会非常可观。

即使外层有WHERE user_id = 12345这种高选择性的条件，也只能硬扫。

一句话总结：派生表的问题不在于“子查询”，而在于“先把所有数据算出来，再取我需要的”。

二、一个真实案例

某电商平台的订单表orders有2000万行。

业务需求：查询每个用户最近一笔订单的金额和日期。原SQL长这样：

SELECT t.user_id, t.order_date, t.amountFROM (SELECT user_id, order_date, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rnFROM orders) tWHERE t.rn = 1;

执行时间：12秒。

执行计划显示：派生表物化了约1800万行数据。

临时表写到了磁盘，然后外层查询再全表扫描这个临时表，过滤出rn=1的行。

根因很简单：内层窗口函数要处理全表数据。

但外层只需要每个用户的最新一条。派生表不会“偷懒”，它会老老实实把全表跑一遍。

三、三种优化方案对比

方案一：直接改JOIN（不总是有效）

SELECT o1.user_id, o1.order_date, o1.amountFROM orders o1INNER JOIN (SELECT user_id, MAX(order_date) AS max_dateFROM ordersGROUP BY user_id) o2 ON o1.user_id = o2.user_id AND o1.order_date = o2.max_date;

这个写法里，派生表o2仍然需要物化。

GROUP BY user_id的结果集可能依然很大。

如果用户数量很多，比如几百万，物化代价依然不小。

核心问题没变：派生表仍然要先算完，再关联。

方案二：用CTE（本质上一样）

WITH latest AS (SELECT user_id, MAX(order_date) AS max_dateFROM ordersGROUP BY user_id)SELECT o1.user_id, o1.order_date, o1.amountFROM orders o1JOIN latest o2 ON o1.user_id = o2.user_id AND o1.order_date = o2.max_date;

CTE在MySQL 8.0中默认也是物化的。

对于这个查询，CTE和派生表的执行方式一样。

也就是先物化latest，再和orders做JOIN。

方案三：LATERAL JOIN（8.0.14 ）

这才是真正的解法。

SELECT o1.user_id, o2.order_date, o2.amountFROM orders o1JOIN LATERAL (SELECT order_date, amountFROM orders o2WHERE o2.user_id = o1.user_idORDER BY order_date DESCLIMIT 1) o2 ON TRUE;

LATERAL JOIN真正带来的变化在于：派生表不再是先整体物化一次。

它会随着外层查询的每一行，逐行执行。

乍一听，“每一行都执行一次”似乎成本很高。

但关键恰恰在这里：配合LIMIT 1和合适的索引后，每次执行往往只需扫过极少几行就能拿到结果。

哪怕外层有100万行，本质上也只是做了100万次索引查找。

相比之下，先物化2000万行再做全表扫描，整体代价要高得多。