大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
你有没有写过这种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万行再加全表扫描。
实测结果：
| 写法 | 执行时间 | 临时表大小 |
|---|---|---|
| 原始派生表 | 12秒 | ~1800万行 |
| JOIN + 派生表 | 8.5秒 | ~500万行 |
| LATERAL JOIN | 0.8秒 | 无临时表 |
优化了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可能反而更慢
六、总结
派生表的性能陷阱，根因在物化机制——它老老实实把子查询的结果算完、存好，外层再过来取。当子查询结果集大、外层只需要少量数据时，物化的代价就变得非常可观。
三个关键认知：
派生表不是“坏”的——数据量小的时候，物化代价可以忽略，代码可读性反而更好
LATERAL JOIN不是“万能药”——它适用于“外层驱动、内层小结果集”的场景
优化前先诊断——看执行计划、看物化行数、看临时表大小，再决定用什么方案
下次写派生表之前，先问自己三个问题：派生表的数据量有多大？外层查询最终需要多少数据？能不能用LATERAL JOIN让内层“按需执行”？
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
