🏠 总目录📚 本教程 06b · 列表接口与分页 ← →

AI全栈 / 06b

列表翻页时,为什么会重复、漏项或越来越慢?

偏移、游标和排序约定要一起看;跨分片与批量部分失败有各自边界。

能按访问方式选择分页,并说明游标失效时怎样响应。

再看游标依赖的排序

📑 本页目录(点开跳转)

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 秒,仍须先被过滤。

① 确定范围用服务端已核验的用户与请求的状态筛出四条。
② 多取一条按时间与编号都降序取 2 + 1 条,得到 4、3、2。
③ 返回两条并记边界返回 4、3,用最后交给用户的 3 记住 (100,3)。

第一次请求用 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 那四件事咬到的那一面」。想看这套分层的全貌回这里

✅ 检查点

  1. OFFSET 100000 为什么慢?数据库在这一步实际做了什么?
  2. 「翻页途中插入导致重复」——实测第 1 页 [12,11,10,9,8],插入两条新消息后第 2 页是什么?重复了哪两条?为什么?
  3. 游标(keyset)分页在什么条件下能稳定续页?它比 OFFSET 换来的代价是什么?
  4. 排序列是 updated_at、而它大量撞车时,为什么单列游标会漏数据?正确的游标该怎么写?
  5. Base64 编码或签名能保护游标字段的保密性吗?服务端还要核对哪些条件?
  6. 为什么把列名字符串绑定给 ORDER BY ? 不能选择排序列?这与SQLite语法错误有何区别?那靠什么防注入?
  7. 「共 1234 条」这种精确总数,什么时候会很贵?只想知道「还有没有下一页」要多取几条?用哪条记录生成令牌?
  8. 批量删除十条,第三条没权限,该返什么 HTTP 状态码?body 里放什么?
👀 答案
  1. OFFSET N 不能「跳」到第 N 条,数据库只能从头把前 N 条都取出来、一条条数着丢弃,再返回接下来的。翻得越深丢弃得越多,所以随页深线性变慢。实测第 2 万页(OFFSET 399980)比第 1 页慢约 400 倍(3.82 ms vs 0.009 ms),执行计划是 SCAN。
  2. 第 2 页是 [9,8,7,6,5],重复了 9 和 8。因为新插的两条把每一条都往后挤了两个位置,OFFSET 5 原本指向 7,插入后指向了 9——OFFSET 算的是位置,位置被插入挪动了。
  3. 条件 seq < 8 按稳定唯一的值继续。新插入较大的值不会把续页边界挪动,边界行删了也能继续查。删掉的旧行不再返回,新插入的较小值可能进入后续页。通常要按页续取,完整一致的导出另需快照。
  4. updated_at 撞车时,updated_at < 100 会把「同样是 100、但还没读」的那些行整段跳过。正确写法是复合游标 (updated_at, id) < (?, ?)——行值比较,和 ORDER BY updated_at DESC, id DESC 一一对应,末尾用唯一列(主键)兜底。
  5. 不能。编码后仍可读,签名用于检查完整性。校验格式、范围、有效期及查询作用域,每次按当前身份重新授权。
  6. 因为 ? 绑定的是值表达式,不是列名/关键字替换,在SQLite中 ORDER BY ? 可以是合法的绑定值表达式,但不会把参数字符串解析为列标识符。这不能实现外部指定列排序,因此使用白名单:外部排序名映射到写死的真实列名,方向也从固定选项映射。未知选项在查库前就拒绝。
  7. 当过滤条件命不中索引时——实测 COUNT(*) WHERE content LIKE '%..%' 要全表 SCAN,30.7 ms,比取一页(0.009 ms)慢三千多倍,还随数据量线性变慢。只想知道「还有没有下一页」就多取一条:查 LIMIT 21,回来 21 条就是「还有」,只返回前 20 条,用第 20 条生成令牌。查到不超过 20 条时返回的令牌为 null。
  8. 返 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

打卡记录保存在你的浏览器里,首页能看到总进度