KEEL · 龙骨 · A CURRICULUM FOR THE AI ERA
03 · 把逻辑压进 SQL — keel 龙骨
## 现场：一万次往返
现场：一万次往返
一个「订单列表」页面，要显示每个用户的最近一单。代码是这么写的：
users = db.query("SELECT id FROM users LIMIT 100")
for u in users:
order = db.query("SELECT * FROM orders WHERE user_id = %s ORDER BY created_at DESC LIMIT 1", u.id)
一百个用户，一百零一次数据库往返。这在测试环境完全没问题——本地回环的往返成本接近零。上线后接口耗时变成 300 毫秒起，因为每次往返都有网络开销，而这一百次查询是串行的。
这就是 N+1 问题。通常的说法是「用 JOIN 或预加载解决」，但这只是解法的一半。真正的问题是：这段逻辑本来就不该在应用层。SQL 是一门能表达「每个分组取第一条」的语言，只是很多人不知道怎么写。
先猜一下：在 20 万行订单表上，下面三种写法——相关子查询、DISTINCT ON、窗口函数——哪一种的计划里会出现「被过滤掉 19 万行」？
直觉模型：让数据库决定怎么做
SQL 是声明式的：你描述要什么，优化器决定怎么拿。
应用层的循环是命令式的：你先决定「遍历每一行，再逐行查一次」，数据库只能照办。优化器没有机会告诉你「其实我可以一次扫描就给你」。
把逻辑压进 SQL 的收益不只是少几次往返，更在于把优化空间还给优化器。同样一句「取每个用户最新一单」，写成 SQL 之后，优化器可以选排序、可以选哈希、可以用并行——这些选择它做不了，如果你已经用代码把顺序钉死了。
三种写法与它们的计划
先造一张有 20 万行、2 万个不同用户的订单表：
CREATE TABLE t_ord(id bigserial PRIMARY KEY, user_id int NOT NULL,
amount numeric(12,2) NOT NULL, created_at timestamptz NOT NULL);
INSERT INTO t_ord(user_id, amount, created_at)
SELECT (g % 19999) + 1, (g % 500) + 1, now() - (g || ' minutes')::interval
FROM generate_series(1, 200000) g;
ANALYZE t_ord;
写法一：相关子查询
SELECT o.* FROM t_ord o
WHERE o.created_at = (SELECT max(x.created_at) FROM t_ord x WHERE x.user_id = o.user_id)
LIMIT 100;
Limit (actual rows=100 loops=1)
-> Seq Scan on t_ord o (actual rows=100 loops=1)
Filter: (created_at = (SubPlan 1))
SubPlan 1
-> Aggregate (actual rows=1 loops=100)
-> Seq Scan on t_ord x (actual rows=10 loops=100)
Filter: (user_id = o.user_id)
Rows Removed by Filter: 199990
这段计划里有两个数字要连起来读。SubPlan 1 后面的 loops=100 表示这个子计划被执行了 100 次——外层每扫描到一行就触发一次。每次执行都是一个 Seq Scan，而这个内层扫描 Rows Removed by Filter: 199990，也就是每次都把整张表扫了一遍。
这就是 N+1 的 SQL 版：外层 100 行，内层 100 次全表扫描，总共读了约两千万行。它在一张 20 万行的表上还能跑出结果，在 2000 万行上就是灾难。
写法二：DISTINCT ON
DISTINCT ON 是 PostgreSQL 特有的语法（不是标准 SQL），语义是「按给定的列分组，每组只留第一行」，而这个「第一行」由 ORDER BY 决定：
SELECT DISTINCT ON (user_id) * FROM t_ord ORDER BY user_id, created_at DESC LIMIT 100;
Limit (actual rows=100 loops=1)
-> Unique (actual rows=100 loops=1)
-> Gather Merge (actual rows=991 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (actual rows=496 loops=2)
Sort Key: user_id, created_at DESC
Sort Method: external merge Disk: 8224kB
-> Parallel Seq Scan on t_ord (actual rows=100000 loops=2)
一次扫描（并行成两个 worker），一次排序，然后 Unique 节点按 user_id 去重留下每组第一条。Sort Method: external merge Disk: 8224kB 说明这个排序大到内存装不下，落到磁盘了——这是这个写法的主要成本。
DISTINCT ON 有个必须记住的约束：ORDER BY 的前缀必须和 DISTINCT ON 的列一致。写成 DISTINCT ON (user_id) ... ORDER BY created_at DESC 会报错，写成别的顺序会得到静默错误的结果（不报错，但每组留的不是你以为的那条）。
写法三：窗口函数
SELECT * FROM (
SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM t_ord
) t WHERE rn = 1 LIMIT 100;
Limit (actual rows=100 loops=1)
-> Subquery Scan on t (actual rows=100 loops=1)
Filter: (t.rn = 1)
-> WindowAgg (actual rows=100 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Gather Merge (actual rows=991 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (actual rows=496 loops=2)
Sort Key: t_ord.user_id, t_ord.created_at DESC
Sort Method: external merge Disk: 7640kB
-> Parallel Seq Scan on t_ord (actual rows=100000 loops=2)
计划形状和 DISTINCT ON 几乎一样：一次并行扫描 + 一次落盘排序。多了一个 WindowAgg 节点，而 Run Condition: (row_number() OVER (?) <= 1) 是一个优化——PG 知道你要的是 rn = 1，在每组取到第一行后就不再为这一组继续计算了。
两种写法在这个场景下性能接近。选择依据不是性能，是表达能力：
- 只要「每组第一条」，
DISTINCT ON更短更直白； - 需要「每组前三条」「排名」「组内累计」这类，
DISTINCT ON表达不了，只能上窗口函数。
窗口函数能一次算完的事
DISTINCT ON 和 row_number() 都在做的事是「分组内排序后取第几行」，这是窗口函数最窄的用法。它真正的价值在于在不聚合的情况下拿到分组的聚合值。
需求：列出用户 1 和用户 2 的订单，每条订单上同时显示该用户的总金额、订单数、以及这一单占总金额的百分比。
不用窗口函数的写法是「先 GROUP BY 算出每个用户的总和，再 JOIN 回订单表」。用窗口函数就一句话：
SELECT user_id,
sum(amount) OVER (PARTITION BY user_id) AS user_total,
count(*) OVER (PARTITION BY user_id) AS user_orders,
round(100.0 * amount / sum(amount) OVER (PARTITION BY user_id), 2) AS pct_of_user
FROM t_ord WHERE user_id IN (1, 2) ORDER BY user_id, created_at DESC LIMIT 6;
user_id | user_total | user_orders | pct_of_user
---------+------------+-------------+-------------
1 | 4955.00 | 10 | 10.09
1 | 4955.00 | 10 | 10.07
1 | 4955.00 | 10 | 10.05
1 | 4955.00 | 10 | 10.03
1 | 4955.00 | 10 | 10.01
1 | 4955.00 | 10 | 9.99
每一行都带着它所属分组的汇总值，百分比是行内直接算出来的。如果要算累计（running total），把 sum(...) OVER (PARTITION BY ...) 加上 ORDER BY 就变成累积窗口：
sum(amount) OVER (PARTITION BY user_id ORDER BY created_at)
加了 ORDER BY 之后，窗口默认是从分组起点到当前行，这就是累计。这个「有无 ORDER BY 决定是分组汇总还是累积」的差别，是窗口函数最容易记混的地方。
窗口函数的一个限制要知道：窗口函数只能出现在 SELECT 列表和 ORDER BY 里，不能出现在 WHERE 里。这也解释了上面那个查询为什么必须包一层子查询——因为要对 rn 做过滤，而 rn 是窗口函数算出来的。
CTE：一个会静默改变行为的优化
CTE（WITH 子句）在 PG 12 之前是优化栅栏：写进 CTE 的查询会被独立物化，外面的条件不会下推。PG 12 起默认改为内联（除非 CTE 被引用多次、或者含 VOLATILE 函数、或者带 RETURNING）。
这个变化会让人对着同一段代码得到两种完全不同的计划：
EXPLAIN (COSTS OFF)
WITH recent AS (SELECT * FROM t_ord WHERE created_at > now() - interval '30 minutes')
SELECT count(*) FROM recent WHERE amount > 100;
Finalize Aggregate
-> Gather
Workers Planned: 1
-> Partial Aggregate
-> Parallel Seq Scan on t_ord
Filter: ((amount > '100'::numeric) AND (created_at > (now() - '00:30:00'::interval)))
外面那个 amount > 100 被推到了 CTE 内部，和 created_at 条件合并成一个 Filter。CTE 消失了，这就是内联。
强制物化：
EXPLAIN (COSTS OFF)
WITH recent AS MATERIALIZED (SELECT * FROM t_ord WHERE created_at > now() - interval '30 minutes')
SELECT count(*) FROM recent WHERE amount > 100;
Aggregate
CTE recent
-> Gather
Workers Planned: 1
-> Parallel Seq Scan on t_ord
Filter: (created_at > (now() - '00:30:00'::interval))
-> CTE Scan on recent
Filter: (amount > '100'::numeric)
这次计划里出现了 CTE Scan，说明 CTE 先被完整算出来、存起来，再被外层读一遍。过滤器分成两层，amount > 100 没被推下去。
内联通常更好（条件合并 → 单次扫描），但物化在两种情况下更划算：
- CTE 被引用多次：物化一次、用两次，比内联后执行两次便宜；
- CTE 内部的表达式代价很高，希望先算小结果集再过滤。
所以看到 CTE 相关查询变慢，第一件事是 EXPLAIN 看它有没有被内联。默认行为是内联，如果你上一份代码是在 PG 11 上写的、依赖了栅栏语义，升级到 12 之后计划会变，性能可能变好也可能变差。
批量写入：41 毫秒与 115 毫秒
写 2 万行，两种写法都放在一个事务里：
INSERT INTO t_ins_a SELECT g, 'v' || g FROM generate_series(1, 20000) g;
Time: 41.353 ms
同一个事务里逐条插入：
DO $
BEGIN
FOR i IN 1..20000 LOOP
INSERT INTO t_ins_b(id, v) VALUES (i, 'v' || i);
END LOOP;
END $;
Time: 114.677 ms
差了差不多三倍。注意这两者的差别不是事务开销——两者都在一个事务里，都没有 2 万次提交。差别在于每一条独立 INSERT 都是一次完整的语句执行：解析、规划、执行、返回。批量语句只解析规划一次。
这个比例随行数增长会变。2 万行时是三倍，到 100 万行时差距会明显拉大，因为单条 INSERT 的固定开销不随数据量摊薄。
再往上一级是 COPY。COPY table FROM STDIN 绕过了 SQL 解析层，直接走批量写入路径，是导入大批量数据最快的途径，也是官方文档推荐的初始数据装载方式。它不适合逐行做业务逻辑（触发器、约束检查都还在，但没有逐行规划），适合装载场景。
RETURNING 是另一个把逻辑留在 SQL 里的工具。它让一条 INSERT/UPDATE/DELETE 在写完之后直接返回受影响的行，省掉一次查询往返：
UPDATE t_ord SET amount = amount + 1 WHERE user_id = 1 RETURNING id, amount;
在「更新一批行并需要它们的值」的场景里，这一次往返的节省是实打实的。要注意的是 RETURNING 只能看到操作之后的状态，拿不到旧值。
写法选择表
| 需求 | 写法 | 说明 |
|---|---|---|
| 每组取第一条 | DISTINCT ON |
ORDER BY 前缀必须与分组列一致 |
| 每组取前 N 条 / 组内排名 | 窗口函数 | DISTINCT ON 表达不了 |
| 行上带分组的聚合值 | 窗口函数 | 省掉一次 GROUP BY + JOIN |
| 累积、滑动窗口 | 窗口函数 + ORDER BY |
有无 ORDER BY 决定是分组还是累积 |
| 给复杂查询分段命名 | CTE | 注意 PG12+ 默认内联，需要物化时显式写 MATERIALIZED |
| 大批量装载 | COPY |
比 INSERT 快，不适合逐行业务逻辑 |
| 写入后需要结果 | RETURNING |
省一次往返，只能看到新值 |
生产边界
本课的耗时数字来自本地 PostgreSQL 16.15、shared_buffers 128MB、work_mem 默认 4MB。所以那个 external merge Disk: 8224kB 是本机 work_mem 不足导致的落盘排序，生产上调大 work_mem 可能就变内存排序了。读形状，不要抄数字。
替换到生产环境时要改的地方：
work_mem的作用域。它是每个排序/哈希操作的上限，不是一个查询的总上限。一个查询里有三个排序节点，每个都可以用到work_mem。调大它的收益和风险都随并发数放大，要按连接数算总内存。DISTINCT ON的可移植性。它是 PG 专属语法，迁到别的数据库要改成窗口函数。COPY的权限与来源。服务端COPY ... FROM '文件'需要超级用户权限，普通应用用客户端侧的\copy（psql 元命令），两者走的是不同的路径。
上线后该盯的指标：pg_stat_statements 里 calls 高且 total_exec_time 高的查询（N+1 的典型形态是调用次数异常大）、以及计划里出现 SubPlan 且 loops 很高的查询。后者的排查方式就是本章第一个例子：看到 SubPlan 后面跟着 loops=100，就知道外层有多少行，内层就被执行了多少次。
动手
- 复现三种「取每组第一条」的写法与计划。判断标准：你能指出哪一份计划里出现了
SubPlan，以及它的loops是多少。 - 给
t_ord建一个(user_id, created_at DESC)的复合索引，重跑写法一。判断标准：SubPlan里的Seq Scan应该变成Index Scan或Index Only Scan，Rows Removed by Filter大幅下降。 - 把 03.5 的窗口函数查询改成累积：给
sum(amount) OVER (PARTITION BY user_id)加上ORDER BY created_at。判断标准：同一用户的百分比不再都接近 10%，而是逐行累加。 - 用
MATERIALIZED和不用各跑一次 03.7 的查询，对比EXPLAIN (ANALYZE)的actual time。判断标准：你能说出这份数据下哪种更快，以及这个结论在什么条件下会反过来。
自测
- 相关子查询的计划里，
SubPlan 1后面的loops=100意味着什么？它与应用层的 N+1 是同一件事吗？ DISTINCT ON (user_id)要求ORDER BY以user_id开头。如果不这样写会发生什么？为什么 PG 不直接报错？- 窗口函数的
sum(x) OVER (PARTITION BY g)和sum(x) OVER (PARTITION BY g ORDER BY t)语义差别是什么？这个差别在所有聚合函数上都成立吗？ - PG 12 之后 CTE 默认内联。什么情况下 PG 会选择物化？如果你依赖旧版的栅栏语义，升级后该怎么处理？
- 同一个事务内，2 万行
INSERT ... SELECT花 41ms，逐条INSERT花 115ms。请解释这三倍差在哪，并预测数据量增加到 100 万行时这个比例会怎么变。
↓ 下一步：04 章 · 事务边界与并发写