SQL派生表优化实战：从物化机制到LATERAL JOIN的完整进阶

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

你有没有写过这种SQL？

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

逻辑简单，意思清楚。但跑了三分钟还没出来。你试了改JOIN、加索引、调参数——效果都不明显。

这不是你SQL写得不对，是派生表（Derived Table）的物化机制在背后偷偷“搞事情”。今天把派生表的性能陷阱彻底拆开讲一遍。

一、先搞懂派生表是什么

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

SELECT *
FROM (SELECT user_id, order_amount FROM orders WHERE order_date > '2026-01-01') AS dt
WHERE 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.amount
FROM (
    SELECT user_id, order_date, amount,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
    FROM orders
) t
WHERE t.rn = 1;

执行时间：12秒。

执行计划显示：派生表物化了约1800万行数据，临时表写到了磁盘，然后外层查询再全表扫描这个临时表，过滤出rn=1的行。

根因很简单：内层窗口函数要处理全表数据，但外层只需要每个用户的最新一条。派生表不会“偷懒”，它老老实实把全表跑了一遍。

三、三种优化方案对比

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

SELECT o1.user_id, o1.order_date, o1.amount
FROM orders o1
INNER JOIN (
    SELECT user_id, MAX(order_date) AS max_date
    FROM orders
    GROUP 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_date
    FROM orders
    GROUP BY user_id
)
SELECT o1.user_id, o1.order_date, o1.amount
FROM orders o1
JOIN 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.amount
FROM orders o1
JOIN LATERAL (
    SELECT order_date, amount
    FROM orders o2
    WHERE o2.user_id = o1.user_id
    ORDER BY order_date DESC
    LIMIT 1
) o2 ON TRUE;

LATERAL JOIN的核心改变是：派生表不再一次性物化，而是对外层查询的每一行执行一次。听起来“每一行执行一次”好像很慢，但关键在于——有了LIMIT 1和索引，每次执行只需要扫描几行就能返回结果。外层100万行，每行执行一次索引查找，总代价远远小于物化2000万行再加全表扫描。

实测结果：

优化了15倍。

四、LATERAL JOIN的适用边界

LATERAL JOIN不是万能的，用不对也可能踩坑：

适用场景：

• 子查询需要引用外层表的列（关联子查询）

• 子查询结果集小（有LIMIT、聚合后结果少）

• 外层表有索引支撑快速过滤

不适用场景：

• 子查询返回大量数据（没有LIMIT或GROUP BY压缩）

• 关联条件不是等值（如<、>）

• 子查询引用了多层嵌套的外层表

• 外层表本身很大且没有索引

五、如何判断你的派生表该不该改？

第一步：看执行计划

EXPLAIN输出中，如果Extra列出现Using temporary，说明派生表被物化了。但这不一定就是问题——如果派生表很小（几百行），物化代价可以忽略。

第二步：看临时表大小

用EXPLAIN FORMAT=JSON看materialized_from_subquery的rows估算。如果估算行数超过10万，就需要警惕。

第三步：看外层过滤条件

• 如果外层WHERE条件能利用索引、筛选后数据量很小 → LATERAL JOIN可能收益明显

• 如果外层本身就是全表扫描 → LATERAL JOIN可能反而更慢

六、总结

派生表的性能陷阱，根因在物化机制——它老老实实把子查询的结果算完、存好，外层再过来取。当子查询结果集大、外层只需要少量数据时，物化的代价就变得非常可观。

三个关键认知：

1. 派生表不是“坏”的——数据量小的时候，物化代价可以忽略，代码可读性反而更好

2. LATERAL JOIN不是“万能药”——它适用于“外层驱动、内层小结果集”的场景

3. 优化前先诊断——看执行计划、看物化行数、看临时表大小，再决定用什么方案

下次写派生表之前，先问自己三个问题：派生表的数据量有多大？外层查询最终需要多少数据？能不能用LATERAL JOIN让内层“按需执行”？

小耶在手，SQL 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~