📑 本页目录(点开跳转)
06b · 列表接口:分页、过滤,和会被翻到第一万页的那个坑
⏱ 68 分钟 | ⭐ OFFSET 分页在 AI 应用里几乎必然出重复和漏项 —— 因为你翻着历史,新消息还在往里写
🎯 一句话
任何 AI 产品的第二个接口都是「列出我的历史对话 / 文档 / 任务」,而写它的第一反应 LIMIT ... OFFSET ... 有两个必死的坑:深翻页越翻越慢、翻页途中一旦有插入就重复和漏项。
使用匹配索引与稳定唯一排序键的 keyset,可减少深翻页扫描和位置移动问题;通常不能直接跳到任意页,排序值更新及一致快照另需处理。
上一章(06)建好了
messages表、seq列、(conversation_id, seq)索引,实测「点开会话」从 3.802 秒降到 0.0019 秒。那是查一条会话的全部消息。这一章是这张表怎么往外一页一页地翻。
🧩 一、OFFSET 分页的两个必然死法
翻页最直觉的写法,几乎每份教程的第一版都长这样:
SELECT id, content FROM messages ORDER BY id DESC LIMIT 20 OFFSET ?;
-- 第 1 页 OFFSET 0,第 2 页 OFFSET 20,第 N 页 OFFSET (N-1)*20
它当场能跑,小数据看不出任何问题。但两件事会打脸。
死法一:深翻页越翻越慢,因为数据库要从头数过前 N 条
OFFSET 100000 不是「跳到第 10 万条」——数据库没有这个能力,它只能从头把前 10 万条都取出来、一条条数着扔掉,再返回接下来的 20 条。翻得越深,扔掉的越多。
实测(SQLite 内存库塞 40 万行消息,同一条查询重复 50 次取平均;你的绝对数值会不同,但增长形状是结构性的):
| 翻到第几页 | OFFSET |
执行计划 | OFFSET 每次 | keyset 每次 | 快多少 |
|---|---|---|---|---|---|
| 第 1 页 | 0 | SCAN |
0.009 ms | 0.009 ms | 1× |
| 第 501 页 | 10000 | SCAN |
0.094 ms | 0.008 ms | ~11× |
| 第 5001 页 | 100000 | SCAN |
0.785 ms | 0.008 ms | ~92× |
| 第 10000 页 | 199980 | SCAN |
1.56 ms | 0.008 ms | ~187× |
| 第 20000 页 | 399980 | SCAN |
3.82 ms | 0.009 ms | ~400× |
⭐ 看增长趋势,也看执行计划:大 OFFSET 仍要跳过前面的匹配行;keyset 若能使用匹配的索引,通常能从边界定位。是否使用索引还取决于过滤、排序、统计信息和查询规划,不能把本例的 SCAN / SEARCH 当作所有数据库的固定计划。参见 PostgreSQL LIMIT/OFFSET 文档。
死法二 ⭐:翻页途中有插入,就重复和漏项 —— 这才是 AI 应用的真坑
慢还能忍。真正要命的是:OFFSET 算的是「位置」,而位置会被插入和删除挪动。 AI 聊天恰恰是「你往回翻历史,新消息还在源源不断写进来」——这个场景下坑几乎必然触发。用纯 Python + SQLite 演示(可直接跑):
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE messages (conversation_id TEXT, seq INTEGER, content TEXT, "
"PRIMARY KEY (conversation_id, seq))")
db.executemany("INSERT INTO messages VALUES ('c1', ?, ?)", [(i, f"m{i}") for i in range(1, 13)])
def off(page): # 页大小 5,最新的排在前
return [r[0] for r in db.execute(
"SELECT seq FROM messages WHERE conversation_id='c1' "
"ORDER BY seq DESC LIMIT 5 OFFSET ?", (page * 5,))]
p1 = off(0); print("第1页:", p1) # [12, 11, 10, 9, 8]
db.executemany("INSERT INTO messages VALUES ('c1', ?, ?)", # ⭐ 用户读第1页时,来了两条新消息
[(s, f"m{s}") for s in (13, 14)])
p2 = off(1); print("第2页:", p2) # [9, 8, 7, 6, 5]
print("⚠️ 重复:", sorted(set(p1) & set(p2), reverse=True)) # [9, 8] —— 用户看到两条一模一样的
实测输出:第 1 页 [12,11,10,9,8],插入两条新消息后第 2 页 [9,8,7,6,5]——⚠️ 9 和 8 在两页里各出现一次。原因很简单:新插的 13、14 把每一条都往后挤了两个位置,OFFSET 5 原本指向 7,现在指向了 9。
反过来,如果翻页途中删了记录,OFFSET 会往前缩,中间那几条会被整段跳过,读者永远看不到(同一段代码里再删两条,第 3 页实测漏掉了 3、4)。
⭐ 一句话记住:
OFFSET假设「列表在你翻页期间是冻结的」。AI 应用里它一秒都不冻结——所以这个假设从一开始就不成立。
🧩 二、游标(keyset)分页:什么条件下能稳定续页
换个思路:不记「我翻到第几位置」,记「我读到的最后一条是哪一条」,下一页从它之后接着取。这个「最后一条的标识」就是游标(cursor)。
这里的 cursor 是跨请求携带的分页边界令牌;数据库结果游标则关联查询结果和当前读取位置,驱动的 cursor 对象也不能一律等同服务端游标。SQL 游标接口中,OPEN 准备结果遍历、FETCH 取行、CLOSE 关闭并释放相关资源,具体生命周期依数据库、事务和接口约定。以 MySQL 8.4 存储程序游标为例,它只读、不可滚动、asensitive(结果可能物化,也可能不物化);这些限制不能泛化到全部数据库游标。
-- 第一页:不带游标
SELECT id, content FROM messages WHERE conversation_id=? ORDER BY seq DESC LIMIT 20;
-- 下一页:游标 = 上一页最后一条的 seq,比它小的接着取
SELECT id, content FROM messages WHERE conversation_id=? AND seq < ? ORDER BY seq DESC LIMIT 20;
⭐ 它怎样避开位置移动:本例的 seq 在会话内唯一且不变;条件 seq < 8 从上次边界继续,较大的新值不会把旧行的位置推偏。已删除的行无法再返回,新插入到边界之前的行也不会被本次向后遍历读到,所以这不是“覆盖所有时刻全部记录”的保证。
⭐ 游标还有一个 OFFSET 给不了的好处:那条游标记录被删了也不影响。因为查的是 seq < 8,不是「id=8 那条之后」——8 这条在不在都无所谓,比它小的照样查得出来(实测把游标行 seq=8 删掉,下一页仍正确返回 [7,6,5,4,3])。
⚠️ 排序列有重复值时,游标必须是「复合」的
唯一还不够,排序值也要稳定。复合 (updated_at, id) 可以打破并列,但 updated_at 在两次请求之间变化时,记录仍可能跨过游标而漏读或重复。普通会话列表要说明这种实时变化;要求完整一致的导出,应固定数据库快照或物化待遍历结果。只签名或编码游标并不能建立快照。
上面用 seq 当游标很干净,因为它在一个会话里唯一。但会话列表按 updated_at 排序,updated_at 会大量撞车(批量导入、脚本回填时一秒能写几百条)。这时候单列游标会漏掉同一秒里游标之后的那些行:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conversations (id INTEGER PRIMARY KEY, updated_at INTEGER)")
db.executemany("INSERT INTO conversations VALUES (?,?)", # ⭐ 前 4 条故意撞在同一秒 100
[(1,100),(2,100),(3,100),(4,100),(5,99),(6,98),(7,97)])
first = db.execute("SELECT id, updated_at FROM conversations "
"ORDER BY updated_at DESC, id DESC LIMIT 2").fetchall()
print("第1页:", first) # [(4,100),(3,100)]
ts, i = first[-1][1], first[-1][0] # 游标停在 (updated_at=100, id=3)
bad = db.execute("SELECT id FROM conversations WHERE updated_at < ? " # ⚠️ 只用 updated_at
"ORDER BY updated_at DESC, id DESC LIMIT 2", (ts,)).fetchall()
ok = db.execute("SELECT id FROM conversations WHERE (updated_at, id) < (?, ?) " # ⭐ 复合游标
"ORDER BY updated_at DESC, id DESC LIMIT 2", (ts, i)).fetchall()
print("单列游标 → 第2页:", [r[0] for r in bad]) # [5, 6] ⚠️ id=2,1 被整段跳过!
print("复合游标 → 第2页:", [r[0] for r in ok]) # [2, 1] ✅ 一条不漏
实测:updated_at < 100 把还没读的 id=2、id=1(它们的 updated_at 也是 100)整段跳过,直接蹦到 99 那条去了。⭐ 正确写法是复合游标 (updated_at, id) < (?, ?)——行值比较,和 ORDER BY updated_at DESC, id DESC 一一对应。
⭐ 判据:游标里的列,必须和
ORDER BY的列一字不差地对齐,而且末尾要有一个唯一列(通常是主键)兜底。 排序几个列,游标就带几个列。少一个,撞值那一刻就漏数据。
⚠️ 这套查询要快,得靠索引。 复合游标 (updated_at, id) < ... 没有索引时会 SCAN 并临时排序;建了 (updated_at DESC, id DESC) 后才会走覆盖索引。
索引怎么建、为什么要带上 ORDER BY 的列,是上一章第三节的事;这里直接使用这个结论。
keyset 的代价:不能跳页
游标只知道「上一页的末尾」,所以它只能一页一页往下翻,给不了「直接跳到第 500 页」,也给不了「共 1234 页」这种页码条。⭐ 但这对 AI 产品几乎不是损失:历史对话、消息流、任务列表全是「无限下滑」交互,没人会去点第 500 页。 真需要跳页的(后台管理表格、报表)另说,那种场景数据量通常也没大到 OFFSET 会疼。
把深分页、读写分离、分库分表分开看:单库深分页先复用前面的匹配索引与稳定复合游标;读写分离只改变请求路由,不减少一条查询自身的扫描量,权威读与副本新鲜度仍按上一章的读写路由约定处理。分片后,先按已知分片键和路由元数据缩小目标;跨片仍须按同一全局稳定、唯一的顺序合并,续页的全局边界、所需跨片一致性/快照及拓扑变化后的处理也要约定,不能直接在各片套相同的OFFSET。
只看固定两片、无并发变更的算术反例:按全局唯一数字键升序,A=[1,4,7]、B=[2,3,8],全局OFFSET 2 LIMIT 2应得[3,4];各片先做同样截取,只剩A=[7]、B=[8],再合并取两条得到[7,8],已经漏过正确页。这只是列表计算,不是MySQL集群测试。
跨片聚合/连接也不能由一条单库keyset自动解决;评估时记录目标片扇出、各片扫描/返回量、网络传输与合并代价,以及一致性目标。具体路由与合并能力依中间件;MySQL 8.4的LIMIT/排序说明与Vitess的路由说明、24.0 VSchema例仅作各自机制参考,不能当成所有分片产品的通用实现。
🧩 三、游标要不要编码,翻页途中那条记录被删了怎么办
游标要发给前端、下次再带回来。两种做法:
| 做法 | 好在哪 | 坏在哪 |
|---|---|---|
直接暴露 ?after_id=8231 |
简单、能读、好调试 | 把内部字段(自增 id、时间戳)漏给了前端;换了排序字段,URL 契约就得改 |
⭐ 编码成不透明串 ?cursor=eyJ1Ijou... |
前端只当它是「黑盒令牌」,服务端想换排序键、加字段都不动契约 | 多一层编解码;调试时得解一下才看得懂 |
客户端只需要保存并回传令牌。下面演示把边界值编码成 Base64 字符串,再检查类型和范围。这个往返示例没有签名。需要验证完整性时,可以用服务端保存的随机令牌,或校验自包含令牌的签名。签名只能帮助验证内容的完整性,读取权限仍须按当前请求检查。可读编码和签名都不能为字段提供保密性。
import base64
import json
def encode(updated_at, last_id):
raw = json.dumps({"u": updated_at, "i": last_id}, separators=(",", ":")).encode()
return base64.urlsafe_b64encode(raw).rstrip(b"=").decode()
def decode(cursor):
if not isinstance(cursor, str) or not 1 <= len(cursor) <= 256:
raise ValueError("游标格式错误")
pad = "=" * (-len(cursor) % 4)
try:
payload = json.loads(base64.b64decode(cursor + pad, altchars=b"-_", validate=True))
except ValueError:
raise ValueError("游标格式错误") from None
if not isinstance(payload, dict) or payload.keys() != {"u", "i"}:
raise ValueError("游标字段错误")
timestamp, last_id = payload["u"], payload["i"]
max_integer = 2**63 - 1 # 演示表使用有符号 64 位整数
if (type(timestamp) is not int or type(last_id) is not int
or not 0 <= timestamp <= max_integer or not 1 <= last_id <= max_integer):
raise ValueError("游标字段错误")
return timestamp, last_id
cursor = encode(1754870400, 8231)
assert decode(cursor) == (1754870400, 8231)
print(decode(cursor))
输出 (1754870400, 8231)。服务端要限制令牌大小,校验字段名、类型、范围,以及筛选、排序和用户范围是否仍匹配。令牌中不放敏感信息。第五节会用服务端保存的令牌走完两次请求。
⚠️ 翻页途中那条游标记录被删了怎么办——第二节已经答过:不影响。游标存的是「值」(updated_at=100, id=3),查的是「比这个值小的」,id=3 那条在不在都无所谓。这正是 keyset 比「查 id 之后的行」这种朴素写法更稳的地方。
🔍 四、过滤和排序:可排序字段必须白名单
列表接口一定会长出 ?sort=updated_at&order=desc&status=failed 这类参数。排序字段最危险:它常被直接拼进 ORDER BY,而那里不只允许列名,也允许子查询或 CASE,于是成了 SQL 注入面。
⚠️ 参数化占位符 ? 救不了这里:它只能替换值,不能替换列名或关键字,在SQLite中 ORDER BY ? 可以是合法的绑定值表达式,但不会把参数字符串解析为列标识符。排序字段只能走白名单映射。
服务端先把外部排序选项映射到固定的 SQL 片段。允许的列名和方向由代码定义,未知选项在查库前就返回客户端错误。下面的 Python 3 片段用正常输入与两个未知选项验证这道检查。它只生成排序子句。
SORTABLE = {"updated": "updated_at", "title": "title"}
DIRECTIONS = {"desc": "DESC", "asc": "ASC"}
def order_clause(sort, order):
if sort not in SORTABLE or order not in DIRECTIONS:
raise ValueError("不支持的排序选项")
column = SORTABLE[sort]
direction = DIRECTIONS[order]
return f"ORDER BY {column} {direction}, id {direction}"
assert order_clause("updated", "desc") == "ORDER BY updated_at DESC, id DESC"
for sort, order in [("unknown", "desc"), ("updated", "up")]:
try:
order_clause(sort, order)
except ValueError as error:
print(error)
else:
raise AssertionError("应在查库前拒绝未知选项")
最小SQLite对照:给出三行(id,score)=(1,30)、(2,10)、(3,20),绑定字符串score执行
ORDER BY ?并不按score列排序;显式ORDER BY score才得到id序列2、3、1。前者是各行同一常量表达式,行间顺序没有由它规定,不能把某次返回顺序当通用保证。这个本地对照只验证SQLite语义;现有白名单仍负责选列和方向,普通过滤值仍用占位符。依据 SQLite参数表达式文档。⭐ 判据:凡是要拼进 SQL 结构位置(列名、方向、表名)的外部输入,一律走白名单映射,不认识就拒绝。 值可以参数化;列名/方向等结构不能借绑定值来选择——在SQLite中
ORDER BY ?可以是合法的绑定值表达式,但不会把参数字符串解析为列标识符,因此仍须白名单映射,不能让外部输入直接拼接SQL。
⚠️ 过滤条件(WHERE status=?、WHERE model=?)要能命中索引,否则一个 LIKE '%词%' 就把整张大表扫一遍(下一节有实测)。给哪些列建索引是上一章的事;这一节只提醒:别让过滤参数落到没索引的列上,尤其别开放前缀模糊匹配。
🧯 五、总数要不要给:COUNT(*) 在大表上不是免费的
前端常想要「共 1234 条」好画页码。但精确总数在大表上可能比取一页数据贵几个数量级,而且多数场景根本用不着。实测(40 万行):
| 操作 | 执行计划 | 耗时 |
|---|---|---|
取一页(keyset LIMIT 20) |
SEARCH |
0.009 ms |
COUNT(*) 全表(能走覆盖索引) |
SCAN ... USING COVERING INDEX |
0.031 ms |
⚠️ COUNT(*) WHERE content LIKE '%..%'(命不中索引) |
SCAN messages |
30.7 ms |
⭐ 关键不是「COUNT 一定慢」,是「带上命不中索引的过滤条件才慢」:一旦过滤走不了索引,COUNT 就得把全表每一行都取出来数——上面那条比取一页慢了三千多倍,而且它随数据量线性变慢,今天不疼不代表明年不疼。
多数「无限下滑」界面根本不需要精确总数,它只需要回答一个是非题:「还有没有下一页?」 这个答案不用 COUNT——⭐ 多取一条就行:请求 20 条时查 LIMIT 21,取回 21 条说明「还有」,把第 21 条丢掉、拿它生成下一页游标;取回 ≤20 条说明「到底了」。实测这招 0.008 ms,和取一页一样快。
💡 要展示总数怎么办:要么接受它是近似值(Postgres 可以读
pg_class.reltuples估算,🗓️ 未实跑,需要真实 Postgres),要么维护一个计数器表(写入时 ±1)。⚠️ 别在每次翻页都跑一遍精确COUNT——那是最贵、最没必要的一种。
沿两次 GET 翻完会话列表
小林打开自己的「进行中」会话,界面每次只显示两条。服务端已从会话验证出当前用户是 u1。假设时间单位为秒,测试数据中这位用户的进行中会话按时间和编号降序排列为 (100,4)、(100,3)、(100,2)、(99,1)。同一用户已归档的 5 和另一用户的 6 都排在更晚的第 101 秒,仍须先被过滤。
第一次请求用 GET /conversations?status=open&limit=2。响应是 200 OK,JSON 正文为 {"items":[{"id":4,"updated_at":100},{"id":3,"updated_at":100}],"next_cursor":"<实际令牌>"}。客户端把 next_cursor 的实际值原样带进第二次请求的 cursor 参数,继续使用同一个 status。多读出的第 3 条只用来判断还有下一页。如果用它做边界,下一页会跳过 id=2。
两次请求之间,新会话 (101,7) 来了,边界行 id=3 被删除。第二次发 GET /conversations?status=open&limit=2&cursor=<实际令牌>,仍查 (updated_at, id) < (100,3)。先比时间,同一秒再比编号,于是取到 id=2、id=1。响应为 200 OK,JSON 正文为 {"items":[{"id":2,"updated_at":100},{"id":1,"updated_at":99}],"next_cursor":null}。更晚的 7 留给刷新后第一页。修改排序字段仍可能让记录跨过边界,本接口逐次读取当前数据。
下面把这份接口合同做成可以直接运行的 Python 3 + SQLite 练习。函数返回 HTTP 状态码和 JSON 对应的数据结构,整个过程只用内存数据库,错误响应里的 trace_id 使用固定演示值。固定排序对应索引中的 updated_at DESC, id DESC。令牌是随机的,服务端字典只保存它对应的范围与边界。真实服务须在每次请求重新核对身份和访问范围,持久化或统一管理令牌及有效期。
import secrets
import sqlite3
from contextlib import closing
def bad_request(code, message):
return 400, {"error": {"code": code, "message": message, "trace_id": "demo-06b"}}
def list_conversations(db, cursors, user_id, status, limit, cursor=None):
# user_id 由调用者从已验证会话取得,本例用固定测试身份。
if status not in {"open", "archived"}:
return bad_request("invalid_status", "不支持的状态筛选")
if type(limit) is not int or not 1 <= limit <= 20:
return bad_request("invalid_limit", "limit 必须是 1 到 20 的整数")
scope = (user_id, status)
sql = "SELECT id, updated_at FROM conversations WHERE user_id = ? AND status = ?"
values = list(scope)
if cursor is not None:
saved = None
if isinstance(cursor, str) and 1 <= len(cursor) <= 128:
saved = cursors.get(cursor)
if saved is None:
return bad_request("cursor_expired", "请从第一页重新加载")
saved_scope, boundary = saved
if saved_scope != scope:
return bad_request("cursor_scope_mismatch", "查询范围已变化,请从第一页加载")
sql += " AND (updated_at, id) < (?, ?)"
values.extend(boundary)
sql += " ORDER BY updated_at DESC, id DESC LIMIT ?"
rows = db.execute(sql, (*values, limit + 1)).fetchall()
items = rows[:limit]
next_cursor = None
if len(rows) > limit:
next_cursor = secrets.token_urlsafe(16)
last = items[-1]
cursors[next_cursor] = (scope, (last["updated_at"], last["id"]))
return 200, {"items": [dict(row) for row in items], "next_cursor": next_cursor}
with closing(sqlite3.connect(":memory:")) as db:
db.row_factory = sqlite3.Row
db.executescript("""
CREATE TABLE conversations (
id INTEGER PRIMARY KEY,
user_id TEXT NOT NULL,
status TEXT NOT NULL,
updated_at INTEGER NOT NULL
);
CREATE INDEX conversations_page
ON conversations(user_id, status, updated_at DESC, id DESC);
INSERT INTO conversations VALUES
(1, 'u1', 'open', 99), (2, 'u1', 'open', 100),
(3, 'u1', 'open', 100), (4, 'u1', 'open', 100),
(5, 'u1', 'archived', 101), (6, 'u2', 'open', 101);
""")
cursors = {}
http, first = list_conversations(db, cursors, "u1", "open", 2)
first_ids = [row["id"] for row in first["items"]]
token = first["next_cursor"]
assert http == 200 and first_ids == [4, 3] and token
assert cursors[token] == (("u1", "open"), (100, 3))
print(http, first_ids, token is not None)
with db:
db.execute("INSERT INTO conversations VALUES (7, 'u1', 'open', 101)")
db.execute("DELETE FROM conversations WHERE id = 3")
http, second = list_conversations(db, cursors, "u1", "open", 2, token)
second_ids = [row["id"] for row in second["items"]]
assert http == 200 and second_ids == [2, 1] and second["next_cursor"] is None
print(http, second_ids, second["next_cursor"] is not None)
def expect_error(result, expected):
http, body = result
assert http == 400 and body["error"]["code"] == expected
print(http, body["error"]["code"])
expect_error(list_conversations(db, cursors, "u1", "archived", 2, token),
"cursor_scope_mismatch")
del cursors[token] # 模拟服务端令牌到期
expect_error(list_conversations(db, cursors, "u1", "open", 2, token),
"cursor_expired")
expect_error(list_conversations(db, cursors, "u1", "open", 0),
"invalid_limit")
前两行输出分别是 200 [4, 3] True 与 200 [2, 1] False,最后一列表示有没有 next_cursor。接着依次得到 400 cursor_scope_mismatch、400 cursor_expired 和 400 invalid_limit。换状态或令牌失效时,前端清掉旧列表,从第一页重载。页大小无效时,先修正 limit 再发送。错误码与请求编号的用法见 03b · 错误响应。两列比较的求值顺序见 SQLite 行值比较文档。
先预测 · 如果误把第 3 条候选当成游标,会少哪条?
会少 id=2。候选第 3 条是 (100,2),下一次查询只取严格小于该值的记录,2 自己也被排除。游标应指向已经返回的 (100,3)。
📋 六、顺带:批量接口的部分失败怎么报
列表选中多项后「批量删除 / 批量归档」是列表接口的孪生兄弟。它有个独有的坑:十条里第三条失败了,你返 200 还是 500? 都不对——200 骗人(有失败),500 也骗人(大部分成了,而且客户端不知道该不该整批重试)。⭐ 正确做法是逐项报:
def delete_one(doc_id):
if doc_id == 3: raise PermissionError("不是你的文档")
if doc_id == 99: raise KeyError("不存在")
def batch_delete(ids):
results = []
for i in ids:
try: delete_one(i); results.append({"id": i, "status": "ok"})
except PermissionError: results.append({"id": i, "status": "error", "code": "forbidden"})
except KeyError: results.append({"id": i, "status": "error", "code": "not_found"})
ok = sum(1 for r in results if r["status"] == "ok")
http = 200 if ok == len(results) else (207 if ok else 422) # ⭐ 部分成功用 207
return http, {"succeeded": ok, "failed": len(results) - ok, "results": results}
print(batch_delete([1, 3, 99, 2])) # HTTP 207,results 里每项各带自己的 status/code
实测:[1,3,99,2] 返回 HTTP 207,body 逐项给出 ok / forbidden / not_found,再汇总 succeeded:2, failed:2。每项的 code 仍用03 章的「机器码 + 人话」形状。
⚠️ 除非业务真要求原子性,别让整批共享一个事务而全成或全败;多数批量删除更需要「删掉能删的,并告诉我哪些没删成」。
🔄 换个栈怎么对应
| 概念 | Python/FastAPI(本板块主栈) | Node(Express/Hono) | Go |
|---|---|---|---|
| 游标分页 | 手写 WHERE (col,id) < (?,?) |
手写 / Prisma cursor + skip:1 |
手写 / sqlc 查询 |
| 游标编码 | base64.urlsafe_b64encode(json…) |
Buffer.from(JSON…).toString('base64url') |
base64.RawURLEncoding |
| 排序白名单 | dict 映射 + get |
Record<string,string> 查表 |
map[string]string 查表 |
| 部分失败 | 手拼 207 + results[] |
同 | 同 |
| GraphQL 里的对应物 | Relay Connections | 同 | 同 |
⭐ 这张表要看出的是:游标分页没有一个框架级的「分页组件」帮你,三个栈都是手写同一套 WHERE (排序列, 唯一列) < (游标值)。⭐ 它是一个 SQL/HTTP 层的模式,不是某个库的功能——所以「换栈要重学的只有 base64 和 map 的语法」。(🗓️ Prisma 的 cursor 参数底层也是 keyset,但它默认仍需要你排一个唯一字段,道理一样。)
🔗 这一章连到哪里
| 去哪 | 为什么 |
|---|---|
| 06-关系数据库.html | 本章沿用它的索引思路。会话内消息可用 (conversation_id, seq)。本章的会话列表还按状态筛选,用 (user_id, status, updated_at DESC, id DESC) 对齐过滤和排序。实际是否命中索引要看执行计划。 |
| 03-后端骨架.html | 批量接口里每一项的 code / message,用的就是它那套统一错误形状;本章只是把它从「一个错」扩成「一批错」 |
| 08-认证会话与多租户.html | GET /conversations 每页都要从已验证身份重新取得允许访问的范围,再据此过滤。服务端还要核对令牌与这次范围是否匹配。第 08 章区分身份认证与租户、对象授权。 |
| 12-限流配额与成本护栏.html | ⚠️ 列表接口也要限流——有人写脚本一秒翻一百页,OFFSET 版会把库拖垮。那一章的令牌桶同样盖在这个路由上 |
| 00-怎么用这份教程.html | 本章属于「带字母后缀的章」:补的是这个零件本来的规矩(分页、过滤),不是「被 AI 那四件事咬到的那一面」。想看这套分层的全貌回这里 |
✅ 检查点
OFFSET 100000为什么慢?数据库在这一步实际做了什么?- 「翻页途中插入导致重复」——实测第 1 页
[12,11,10,9,8],插入两条新消息后第 2 页是什么?重复了哪两条?为什么? - 游标(keyset)分页在什么条件下能稳定续页?它比
OFFSET换来的代价是什么? - 排序列是
updated_at、而它大量撞车时,为什么单列游标会漏数据?正确的游标该怎么写? - Base64 编码或签名能保护游标字段的保密性吗?服务端还要核对哪些条件?
- 为什么把列名字符串绑定给
ORDER BY ?不能选择排序列?这与SQLite语法错误有何区别?那靠什么防注入? - 「共 1234 条」这种精确总数,什么时候会很贵?只想知道「还有没有下一页」要多取几条?用哪条记录生成令牌?
- 批量删除十条,第三条没权限,该返什么 HTTP 状态码?body 里放什么?
👀 答案
OFFSET N不能「跳」到第 N 条,数据库只能从头把前 N 条都取出来、一条条数着丢弃,再返回接下来的。翻得越深丢弃得越多,所以随页深线性变慢。实测第 2 万页(OFFSET 399980)比第 1 页慢约 400 倍(3.82 ms vs 0.009 ms),执行计划是SCAN。- 第 2 页是
[9,8,7,6,5],重复了9和8。因为新插的两条把每一条都往后挤了两个位置,OFFSET 5原本指向7,插入后指向了9——OFFSET算的是位置,位置被插入挪动了。 - 条件
seq < 8按稳定唯一的值继续。新插入较大的值不会把续页边界挪动,边界行删了也能继续查。删掉的旧行不再返回,新插入的较小值可能进入后续页。通常要按页续取,完整一致的导出另需快照。 updated_at撞车时,updated_at < 100会把「同样是 100、但还没读」的那些行整段跳过。正确写法是复合游标(updated_at, id) < (?, ?)——行值比较,和ORDER BY updated_at DESC, id DESC一一对应,末尾用唯一列(主键)兜底。- 不能。编码后仍可读,签名用于检查完整性。校验格式、范围、有效期及查询作用域,每次按当前身份重新授权。
- 因为
?绑定的是值表达式,不是列名/关键字替换,在SQLite中ORDER BY ?可以是合法的绑定值表达式,但不会把参数字符串解析为列标识符。这不能实现外部指定列排序,因此使用白名单:外部排序名映射到写死的真实列名,方向也从固定选项映射。未知选项在查库前就拒绝。 - 当过滤条件命不中索引时——实测
COUNT(*) WHERE content LIKE '%..%'要全表SCAN,30.7 ms,比取一页(0.009 ms)慢三千多倍,还随数据量线性变慢。只想知道「还有没有下一页」就多取一条:查LIMIT 21,回来 21 条就是「还有」,只返回前 20 条,用第 20 条生成令牌。查到不超过 20 条时返回的令牌为null。 - 返 207(部分成功;全成 200,全败 4xx 如 422)。body 里放逐项结果
results:[{id, status, code}]加一个succeeded/failed汇总,每项的code用 03 章那套机器码。别让整批共享一个「全成全败」的事务,除非业务真要求原子性。
本节回顾
⚡ 走神救援
历史对话、文档和任务列表若直接写
LIMIT 20 OFFSET ?,会有两个坑:深翻页时数据库仍要从头丢掉前 N 条,页数越深越慢;翻页途中插入或删除数据时,位置会移动,导致重复或漏项。聊天历史还在持续写入,因此改用游标(keyset)分页:记住最后一条值,下一页查
WHERE seq < 上次游标 ORDER BY seq DESC LIMIT 20。它按值比较而非位置偏移,游标记录被删也不影响;代价是不能任意跳页,但无限下滑的历史列表通常不需要这个能力。排序列会撞值时,游标必须复合:
(updated_at, id) < (?, ?),并与ORDER BY完全对齐、以唯一列兜底,否则会漏行。Base64 编码后的游标字段仍然可读。验证令牌范围与有效期,每次按当前身份核对访问权限。排序字段必须走白名单映射;ORDER BY的列名不能用?参数化,直接拼接就是注入面。精确
COUNT(*)带上不命中索引的过滤会全表扫描。无限下滑只需知道「还有没有下一页」,多取一条(LIMIT 21),用最后返回给客户端的那条记录生成令牌。批量接口若部分失败,返回 207 并在results[]逐项带状态,别把整批误报成全成或全败。
下一节 👉 07-向量检索落地.html