06b · 列表接口：分页、过滤，和会被翻到第一万页的那个坑

⏱ 68 分钟 ｜ ⭐ OFFSET 分页在 AI 应用里几乎必然出重复和漏项 —— 因为你翻着历史，新消息还在往里写

🎯 一句话

任何 AI 产品的第二个接口都是「列出我的历史对话 / 文档 / 任务」，而写它的第一反应 LIMIT ... OFFSET ... 有两个必死的坑：深翻页越翻越慢、翻页途中一旦有插入就重复和漏项。 换成游标（keyset）分页，两个坑一起消失，代价只有一个：不能跳到「第 500 页」。

上一章（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 那列随页深线性增长（翻到第 2 万页比第 1 页慢约 400 倍），下面要讲的 keyset 那列几乎是平的——第 2 万页和第 1 页一样快。执行计划里 OFFSET 版永远是 SCAN messages（从头扫），keyset 版是 SEARCH messages USING INTEGER PRIMARY KEY (rowid<?)（直接定位）。

死法二 ⭐⭐：翻页途中有插入，就重复和漏项 —— 这才是 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）。

-- 第一页：不带游标
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 < 8），不是位置偏移。你已经读过的都 ≥ 上次游标，新插入的、被删除的，都不会改变「比 8 小的还有哪些」这个事实。把上一节那段演示换成游标，同样的插入和删除，重复和漏项都消失（实测：三页读完 set(重复)=[]、漏项=[]）。

⭐ 游标还有一个 OFFSET 给不了的好处：那条游标记录被删了也不影响。因为查的是 seq < 8，不是「id=8 那条之后」——8 这条在不在都无所谓，比它小的照样查得出来（实测把游标行 seq=8 删掉，下一页仍正确返回 [7,6,5,4,3]）。

⚠️ 排序列有重复值时，游标必须是「复合」的

上面用 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 + USE TEMP B-TREE FOR ORDER BY；建了 (updated_at DESC, id DESC) 索引后变成 SEARCH ... USING COVERING INDEX（实测）。索引怎么建、为什么要把 ORDER BY 的列带上，是上一章第三节的事，这里只用它的结论。

keyset 的代价：不能跳页

游标只知道「上一页的末尾」，所以它只能一页一页往下翻，给不了「直接跳到第 500 页」，也给不了「共 1234 页」这种页码条。⭐ 但这对 AI 产品几乎不是损失：历史对话、消息流、任务列表全是「无限下滑」交互，没人会去点第 500 页。 真需要跳页的（后台管理表格、报表）另说，那种场景数据量通常也没大到 OFFSET 会疼。

🧩 三、游标要不要编码，翻页途中那条记录被删了怎么办

游标要发给前端、下次再带回来。两种做法：

⭐ 推荐不透明串，但要清楚它不是加密——base64 只是「不透明」，谁都能解开看里面。所以游标里绝不能放敏感信息，解出来之后必须重新校验类型再用（别信客户端传回来的东西）：

import base64, 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()   # ⭐ urlsafe + 去掉 = 才好放进 URL
def decode(cursor):
    pad = "=" * (-len(cursor) % 4)
    d = json.loads(base64.urlsafe_b64decode(cursor + pad))
    if not isinstance(d.get("u"), int) or not isinstance(d.get("i"), int):
        raise ValueError("bad cursor")      # ⭐ 解出来先校验类型，别直接拼进 SQL
    return d["u"], d["i"]

c = encode(1754870400, 8231)
print(c, "→", decode(c))                                          # 能来回
print("⚠️ 明文可见:", base64.urlsafe_b64decode(c + "=").decode())   # {"u":1754870400,"i":8231}

实测：编码得到 eyJ1Ijox...，解回 (1754870400, 8231)；三种坏游标（乱码、缺字段、类型不对）全被 decode 挡下。这就是「不透明」的全部含义——对前端黑盒，对服务端一解就有，没有任何加密。

⚠️ 翻页途中那条游标记录被删了怎么办——第二节已经答过：不影响。游标存的是「值」（updated_at=100, id=3），查的是「比这个值小的」，id=3 那条在不在都无所谓。这正是 keyset 比「查 id 之后的行」这种朴素写法更稳的地方。

🛑 读到这里可以停 —— 前半章讲完了（约 30 分钟）。 后半章还有：过滤和排序：可排序字段必须白名单 · 总数要不要给：COUNT(*) 在大表上不是免费的 · 顺带：批量接口的部分失败怎么报 · 换个栈怎么对应 回来的时候不用重读，直接从下一节接着看就行。

🔍 四、过滤和排序：可排序字段必须白名单

列表接口一定会长出 ?sort=updated_at&order=desc&status=failed 这类参数。排序字段最危险：它常常被人直接拼进 ORDER BY，而 ORDER BY 后面能放的不只是列名——能放子查询、能放 CASE，于是它就成了一个 SQL 注入面。⚠️ 注意参数化占位符 ? 救不了你：? 只能替换「值」，不能替换「列名 / 关键字」，ORDER BY ? 是语法错误，所以这里没有「用参数化兜底」这条退路，只能靠白名单。

下面这段真的把一个密钥从数据库里偷了出来——只靠一个 sort 参数（可直接跑）：

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE conversations (id INTEGER PRIMARY KEY, title TEXT, updated_at INTEGER)")
db.executemany("INSERT INTO conversations VALUES (?,?,?)", [(1,"a",30),(2,"b",20),(3,"c",10)])
db.execute("CREATE TABLE api_keys (k TEXT)")
db.execute("INSERT INTO api_keys VALUES ('sk-live-7fd3')")   # 攻击者想拿的东西

def bad(sort):                                # ⚠️ 把 sort 直接拼进 ORDER BY
    return [r[0] for r in db.execute(f"SELECT id FROM conversations ORDER BY {sort}")]

OVERFLOW = "abs(-9223372036854775808)"        # SQLite 里这句必抛 integer overflow
def oracle(cond):                             # cond 为真→正常；为假→触发错误
    try: bad(f"CASE WHEN ({cond}) THEN id ELSE {OVERFLOW} END"); return True
    except sqlite3.OperationalError:          return False

got = ""                                       # 🧪 用「报没报错」当探针，二分问出每个字节
for pos in range(1, 13):
    lo, hi = 32, 126
    while lo < hi:
        mid = (lo + hi) // 2
        if oracle(f"(SELECT unicode(substr(k,{pos},1)) FROM api_keys) > {mid}"): lo = mid + 1
        else: hi = mid
    got += chr(lo)
print("💀 只靠 ORDER BY 拼串，把密钥问出来了:", got)     # sk-live-7fd3

实测输出 sk-live-7fd3——⚠️ 表里没有任何一句 SELECT k，攻击者却靠「这一页排出来报不报错」一个字节一个字节把密钥读了出来。修法是白名单：外部传来的排序名先映射到一个你写死的真实列名，不认识就拒绝，方向也只允许两个取值：

SORTABLE = {"updated": "updated_at", "title": "title"}   # ⭐ 外部名 → 真实列名，你写死的
def good(sort, order="desc"):
    col = SORTABLE.get(sort)
    if col is None: raise ValueError("不支持的排序字段")   # ⭐ 不认识就拒，绝不回退到「原样拼」
    direction = "DESC" if str(order).lower() == "desc" else "ASC"   # ⭐ 方向也只有两个取值
    return f"SELECT id FROM conversations ORDER BY {col} {direction}"

⭐ 判据：凡是要拼进 SQL 结构位置（列名、方向、表名）的外部输入，一律走白名单映射，不认识就拒绝。 值可以参数化，结构不能——ORDER BY ? 是语法错误，所以这里没有别的退路。

⚠️ 过滤条件（WHERE status=?、WHERE model=?）要能命中索引，否则一个 LIKE '%词%' 就把整张大表扫一遍（下一节有实测）。给哪些列建索引是上一章的事；这一节只提醒：别让过滤参数落到没索引的列上，尤其别开放前缀模糊匹配。

🧯 五、总数要不要给：COUNT(*) 在大表上不是免费的

前端常想要「共 1234 条」好画页码。但精确总数在大表上可能比取一页数据贵几个数量级，而且多数场景根本用不着。实测（40 万行）：

⭐ 关键不是「COUNT 一定慢」，是「带上命不中索引的过滤条件才慢」：一旦过滤走不了索引，COUNT 就得把全表每一行都取出来数——上面那条比取一页慢了三千多倍，而且它随数据量线性变慢，今天不疼不代表明年不疼。

多数「无限下滑」界面根本不需要精确总数，它只需要回答一个是非题：「还有没有下一页？」 这个答案不用 COUNT——⭐ 多取一条就行：请求 20 条时查 LIMIT 21，取回 21 条说明「还有」，把第 21 条丢掉、拿它生成下一页游标；取回 ≤20 条说明「到底了」。实测这招 0.008 ms，和取一页一样快。

💡 要展示总数怎么办：要么接受它是近似值（Postgres 可以读 pg_class.reltuples 估算，🗓️ 未实跑，需要真实 Postgres），要么维护一个计数器表（写入时 ±1）。⚠️ 别在每次翻页都跑一遍精确 COUNT——那是最贵、最没必要的一种。

📋 六、顺带：批量接口的部分失败怎么报

列表选中多项后「批量删除 / 批量归档」是列表接口的孪生兄弟。它有个独有的坑：十条里第三条失败了，你返 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 章那套「机器码 + 人话」的错误形状——批量接口只是把它从「一个请求一个错」扩展成「一个请求一批错」。⚠️ 别让整批共享一个事务全成全败，除非业务真要求原子性——多数「批量删除」用户是能接受「删掉能删的、告诉我哪些没删成」的。

🔄 换个栈怎么对应

⭐ 这张表要看出的是：游标分页没有一个框架级的「分页组件」帮你，三个栈都是手写同一套 WHERE (排序列, 唯一列) < (游标值)。⭐⭐ 它是一个 SQL/HTTP 层的模式，不是某个库的功能——所以「换栈要重学的只有 base64 和 map 的语法」。（🗓️ Prisma 的 cursor 参数底层也是 keyset，但它默认仍需要你排一个唯一字段，道理一样。）

🔗 这一章连到哪里

✅ 检查点

1. OFFSET 100000 为什么慢？数据库在这一步实际做了什么？
2. 「翻页途中插入导致重复」——实测第 1 页 [12,11,10,9,8]，插入两条新消息后第 2 页是什么？重复了哪两条？为什么？
3. 游标（keyset）分页凭什么天然不重不漏？它比 OFFSET 换来的代价是什么？
4. 排序列是 updated_at、而它大量撞车时，为什么单列游标会漏数据？正确的游标该怎么写？
5. 游标编码成 base64 不透明串，能不能往里放敏感信息？解码之后还要做一步什么？
6. 为什么 ORDER BY 后面不能用参数化占位符 ? 兜底？那靠什么防注入？
7. 「共 1234 条」这种精确总数，什么时候会很贵？不想要总数、只想知道「还有没有下一页」怎么做？
8. 批量删除十条，第三条没权限，该返什么 HTTP 状态码？body 里放什么？

🛑 可以停在这里

⚡ 走神救援

任何 AI 产品的第二个接口都是「列出我的历史对话/文档/任务」，写它的第一反应 LIMIT 20 OFFSET ? 有两个必死的坑。死法一：深翻页越翻越慢——OFFSET N 不能跳，数据库只能从头取出前 N 条一条条丢掉，实测 40 万行里翻到第 2 万页比第 1 页慢约 400 倍（3.82 ms vs 0.009 ms，计划永远是 SCAN）。死法二（真坑）：翻页途中有插入就重复和漏项——OFFSET 算的是「位置」，位置会被插入删除挪动；实测第 1 页 [12,11,10,9,8]，插两条新消息后第 2 页 [9,8,7,6,5]，9、8 重复；删两条则中间被整段跳过。而 AI 聊天恰恰是「你翻历史、新消息还在往里写」，这个坑几乎必然触发。 解药是游标（keyset）分页：不记「翻到第几位置」，记「读到的最后一条是哪一条」，下一页 WHERE seq < 上次游标 ORDER BY seq DESC LIMIT 20。它天然不重不漏，因为条件是值比较不是位置偏移；游标那条记录被删了也不影响（查的是「比这个值小的」）。代价只有一个：不能跳页——但历史列表都是无限下滑，不损失。⚠️ 排序列会撞值时（updated_at 一秒写几百条），游标必须复合：(updated_at, id) < (?, ?)，和 ORDER BY 一字不差对齐、末尾用唯一列兜底，否则同一秒里游标之后的行会被漏掉。游标编码成 base64 不透明串好（前端当黑盒、服务端能换排序键），但它不是加密，别放敏感信息，解码后必须重新校验类型。 过滤和排序：可排序字段必须白名单——ORDER BY 后面能放子查询和 CASE，直接拼是注入面（正文演示只靠 sort 参数就把密钥一字节一字节偷了出来），而 ? 占位符替换不了列名（ORDER BY ? 是语法错），所以没有参数化退路，只能映射到写死的真实列名、不认识就拒。总数别乱给：精确 COUNT(*) 一旦带上命不中索引的过滤就全表扫（实测 30.7 ms，比取一页慢三千倍且随数据量线性恶化）；无限下滑界面不需要总数，只需回答「还有没有下一页」——多取一条（LIMIT 21）即可，0.008 ms。批量接口的部分失败逐项报：第三条挂了返 207，body 放 results[] 每项各带 03 章那套 code，别整批全成全败。

下一节 👉 07-向量检索落地.html