BodySense 数据库第 9 课：ORM Query Shape——N+1、Preload、Batch 与 Pagination

从 GORM 关联读取和 API projection 出发学习 N+1、eager/lazy/batch loading、offset/keyset pagination，并把 HTTP response shape 与 SQL query shape 对齐。

#course #growing #backend #database #postgresql #bodysense

[!info] related notes

课程 MOC：BodySense 数据库工程深化课程 MOC

前置：第 8 课

知识：N+1 Query Problem、ORM 的 Eager Loading 与 Lazy Loading

BodySense 数据库第 9 课：ORM Query Shape——N+1、Preload、Batch 与 Pagination

[!abstract] 本课目标性能问题常常不是“单条 SQL 慢”，而是一次 HTTP 请求悄悄发了几十/几百条 SQL。

从零建立概念：HTTP Response Shape 会决定 Database Query Shape

假设 API 要返回：

{
  "conversations": [
    { "id": "c1", "messages": [...] },
    { "id": "c2", "messages": [...] }
  ]
}

前端看到的是一个嵌套 JSON。

数据库却是：

conversations table
messages table

ORM 的任务之一，就是把关系数据组织回对象结构。

真正的工程问题是：

为了构造这个对象图，究竟发了几条 SQL？每条 SQL 返回多少行？

Lazy Loading：什么时候访问关联，什么时候才查询

某些 ORM 支持类似：

conversation.messages
→ 第一次访问时才发 SQL

优点：

不用就不查

风险：

loop over 100 conversations
→ each access messages
→ 100 child queries

N+1 很容易悄悄出现。

GORM 的关联加载模型和 Hibernate 之类并不完全相同，所以不要机械套某个 ORM 的“lazy”术语；核心仍是追真实 SQL。

N+1 从具体时间成本理解

假设：

1 query for 100 conversations
+
100 queries for messages

每条 child SQL 只 2ms：

看起来都很快

但至少有：

101 round trips
101 pool acquisitions / usages
101 parse/plan/execute cycles

高并发下：

100 HTTP requests × 101 SQL
= 10,100 DB queries

所以 N+1 的危害是查询数量放大。

Eager Loading / Preload：先批量把关联拿回来

GORM 常见：

Preload("Messages")

可能形成：

SELECT ... FROM conversations ...;

SELECT ... FROM messages
WHERE conversation_id IN (...);

也就是：

1 + 1

而不是：

1 + N

这叫 Batch Fetch 的一种形式。

JOIN Eager Load：为什么“一条 SQL”不总是最好

另一种：

conversations
JOIN messages

确实可能只发 1 条 SQL。

但 1:N 时会产生：

conversation c1 repeated for message m1
conversation c1 repeated for message m2
conversation c1 repeated for message m3

如果：

100 parents
× 100 children each

会有 10,000 joined rows。

所以评价 query shape 不能只看：

SQL count

还要看：

rows returned
duplicate bytes
mapping memory
sort
network payload

Cartesian Explosion：多个 1:N 同时 JOIN 的危险

如果一个 parent 同时 JOIN：

10 messages
×
20 events

可能生成：

200 joined combinations

即使真实 child 总数只有 30。

这就是为什么多个关联有时分批加载反而更合理。

Batch Query 的通用模式

Step 1
SELECT parents

Step 2
collect parent IDs

Step 3
SELECT children
WHERE parent_id IN (...)

Step 4
group in application memory

GraphQL DataLoader 本质也常是在做类似“把很多单点 lookup 合并为批量查询”。

Pagination 为什么必须先定义稳定排序

分页不是：

LIMIT 20

就结束。

如果没有稳定 ORDER BY，页面之间没有可靠语义。

例如：

ORDER BY created_at DESC

如果很多行 created_at 相同：

page boundary ambiguous

更稳定：

ORDER BY created_at DESC, id DESC

形成 total order。

OFFSET Pagination 的工作模型

LIMIT 20 OFFSET 100000

数据库通常不能瞬移到“第 100001 个业务结果”。

它仍需：

produce/order enough prior rows
→ skip 100000
→ return next 20

越往后，成本可能越高。

并发插入/删除还会造成：

duplicate across pages
missing rows between pages

Keyset / Cursor Pagination

使用上一页最后一个排序 key：

WHERE (created_at, id) < ($last_time, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20

Index (created_at DESC, id DESC) 可以从 cursor 位置继续 range scan。

优势：

deep page cost 更稳定
并发变化下边界更自然

代价：

不能直接跳到任意 page number
cursor 与排序 contract 强绑定

Query Budget：ORM 性能需要可观察

对 endpoint 可以设：

queries/request <= N
DB total time
slowest query
rows scanned
rows returned
pool wait
response bytes

这样 N+1 不需要等用户投诉才发现。

1. N+1

SELECT N parents
→ for each parent: SELECT children
→ 1 + N queries

单条 child query 都很快，整体仍因 round trip、pool contention 和重复 planner/execution 成本变慢。

2. Eager / Preload / Join / Batch

不同手段：

Preload → 常是额外批量查询
JOIN    → 一条 SQL 组合结果，但可能重复 parent columns
Batch   → collect IDs → WHERE id IN (...)
Lazy    → 用到才查，最容易无意 N+1

最佳方式由 response shape、关系基数和数据量决定。

3. Pagination

Offset：

ORDER BY created_at DESC
LIMIT 20 OFFSET 100000

大 offset 可能需要扫描/跳过大量记录。

Keyset/Cursor：

WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20

更适合稳定滚动的大列表，但 cursor 设计必须与排序唯一性一致。

4. Query Count 也是指标

一个 endpoint 的 performance budget 可以包含：

DB queries/request
rows scanned/returned
SQL latency
pool wait
payload bytes

Prediction

[!question]

N+1 的每条 SQL 都只要 1ms，为什么仍可能严重？

JOIN 是否永远比 Preload 两条 SQL 更好？

列表排序只按 created_at，keyset cursor 为什么可能不稳定？

Offset pagination 的主要成本在哪里？

GraphQL N+1 与普通 REST ORM N+1 本质是否相同？

[!success]- Answer / 解析 1. 100 个 child 就是额外约 100 次 round trip/连接占用，还会放大并发下的 pool 压力。

2. 不永远。 1:N 大关系 JOIN 会重复 parent 数据并产生巨大结果集；两次批量查询可能更高效、更易映射。

3. 多行 created_at 相同会导致边界歧义；通常需要 (created_at, id) 等稳定 total order。

4. 数据库仍需定位并跳过前面大量结果，offset 越大成本通常越高。

5. 本质相同。 都是上层逐实体/逐字段访问把一个关系读取放大成 1+N 次数据库 I/O。

Lab

人为实现一个 1+N BodySense 查询，记录 query count 和总延迟；再分别用 Preload/IN batch/JOIN 消除，比较 SQL 数量、返回行数和内存。

L4 Gate

看到 API response shape 时，能预先估计它需要多少查询、可能返回多少关系行，并用 trace/log 证明。