KernelRanch
返回文章列表

列表翻页时为什么重复：用 SQLite 实测复合游标分页

2026-11-12 #sqlite#sql#pagination#indexes

用户读完列表第一页，另一位用户新增一条记录。继续按 OFFSET 20 取第二页，上一页最后一条可能再次出现。按倒序浏览时，把上一页最后一行的 (created_at, id) 交给下一次查询，从这个位置继续读。只传时间戳会跳过和它同一时刻写入的记录。

1. 同一个排序，同时用于索引和游标

这个例子按租户过滤，再按创建时间和唯一 ID 倒序。created_at 是非空整数，使用 Unix 时间戳。演练数据用小整数表示先后顺序。

CREATE INDEX events_feed ON events (tenant_id, created_at DESC, id DESC);

SELECT id, created_at
FROM events
WHERE tenant_id = ? AND (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT ?;

第一页省略游标条件。下一页用上一页最后一条的时间戳和 ID，按 (tenant_id, 上页时间, 上页 ID, 页大小) 绑定参数。元组比较先比时间，时间相同时再比 ID。严格小于使上一页边界行只出现一次。

2. 在两页之间插入一行

保存为 pagination_lab.py，运行 python pagination_lab.py。第一组用 6 行复现错误，第二组对 10 万行深页查询做对照。两组都在内存数据库执行。

import sqlite3
from contextlib import closing


FIRST = """SELECT id, created_at FROM events WHERE tenant_id = ?
           ORDER BY created_at DESC, id DESC LIMIT ?"""
OFFSET = FIRST + " OFFSET ?"
AFTER = """SELECT id, created_at FROM events
           WHERE tenant_id = ? AND (created_at, id) < (?, ?)
           ORDER BY created_at DESC, id DESC LIMIT ?"""
TIME_ONLY = """SELECT id, created_at FROM events
               WHERE tenant_id = ? AND created_at < ?
               ORDER BY created_at DESC, id DESC LIMIT ?"""


def ids(db, sql, params):
    return [row[0] for row in db.execute(sql, params)]


def measure(db, sql, params):
    plan = db.execute("EXPLAIN QUERY PLAN " + sql, params).fetchone()[3]
    callbacks = 0

    def progress():
        nonlocal callbacks
        callbacks += 1
        return 0

    db.set_progress_handler(progress, 1000)
    try:
        rows = db.execute(sql, params).fetchall()
    finally:
        db.set_progress_handler(None, 0)
    return rows, plan, callbacks


with closing(sqlite3.connect(":memory:")) as db:
    db.executescript("""
        CREATE TABLE events (
            id INTEGER PRIMARY KEY,
            tenant_id INTEGER NOT NULL,
            created_at INTEGER NOT NULL
        );
        CREATE INDEX events_feed ON events (tenant_id, created_at DESC, id DESC);
        INSERT INTO events VALUES (1,1,180),(2,1,190),(3,1,190),
                                  (4,1,200),(5,1,200),(6,1,200);
    """)
    page = db.execute(FIRST, (1, 2)).fetchall()
    last_id, last_ts = page[-1]
    db.execute("INSERT INTO events VALUES (7, 1, 210)")
    variants = {
        "first": [row[0] for row in page],
        "offset": ids(db, OFFSET, (1, 2, 2)),
        "time only": ids(db, TIME_ONLY, (1, last_ts, 2)),
        "cursor": ids(db, AFTER, (1, last_ts, last_id, 2)),
    }
    assert list(variants.values()) == [[6, 5], [5, 4], [3, 2], [4, 3]]
    for label, values in variants.items():
        print(f"{label}: {values}")

    db.executemany("INSERT INTO events VALUES (?, 2, ?)",
                   ((i, i // 10) for i in range(8, 100008)))
    boundary_id, boundary_ts = db.execute(OFFSET, (2, 1, 89999)).fetchone()
    offset_rows, offset_plan, offset_ticks = measure(db, OFFSET, (2, 20, 90000))
    cursor_rows, cursor_plan, cursor_ticks = measure(db, AFTER, (2, boundary_ts, boundary_id, 20))
    assert offset_rows == cursor_rows and len(cursor_rows) == 20
    print(f"deep offset: {offset_plan} | callbacks={offset_ticks}")
    print(f"deep cursor: {cursor_plan} | callbacks={cursor_ticks}")

本地实测，SQLite 3.45.1：

查询得到的 ID解释
第一页[6, 5]页尾是 (200, 5)
插入后按 OFFSET 2[5, 4]ID 7 插在最前面，ID 5 移到了第二页
只按 created_at < 200[3, 2]把同一时间的 ID 4 一起跳过了
按 (created_at, id) < (200, 5)[4, 3]同一时间内继续按 ID 读

深页对照让两种查询返回同样的 20 行。两条计划都使用覆盖索引 events_feed，但 OFFSET 的搜索条件只显示 tenant_id=?，游标查询多了 created_at<? 范围。脚本每约 1,000 条 SQLite 虚拟机指令计一次回调。本轮 OFFSET 触发 270 次，游标为 0 次。0 表示此次查询未达到回调间隔。回调次数反映本次执行的工作量，不能用来推导生产延迟。

3. API 要保留哪些信息

  • 游标来自最后一条实际返回的行。 查询 page_size + 1 条，多出的那条只用于判断是否还有下一页。如果拿它生成游标，下一页会跳过该行。
  • 绑定排序与筛选。 游标要与租户、过滤条件、排序方式和游标版本匹配。租户来自服务端鉴权。把 JSON 编码成 Base64 只能方便传输。需要防篡改时签名，或使用服务端保存的游标句柄。
  • 规定翻页期间的数据语义。 上面的查询每次读取当前数据。新行插到游标之后仍可能进入下一页，修改排序字段也可能让行跨过边界。需要可重复的全量导出时，使用数据库快照或预先固定导出数据集。创建时间和 ID 尽量保持不变。

小结

游标、ORDER BY 和索引使用同一套排序字段。用重复时间戳和翻页期间插入两种用例验证正确性，再用执行计划确认深页查询能从索引范围继续读取。

数据来源与检索时间

检索时间 2026-11-12。

来源URL对应内容
SQLite Row Valueshttps://www.sqlite.org/rowvalue.html#scrolling_window_queries元组比较和滚动窗口查询
SQLite Query Planninghttps://www.sqlite.org/queryplanner.html复合索引、覆盖索引及按索引顺序读取
SQLite EXPLAIN QUERY PLANhttps://www.sqlite.org/eqp.html索引搜索条件和计划输出的适用范围
Python 3.11 sqlite3https://docs.python.org/3.11/library/sqlite3.html#sqlite3.Connection.set_progress_handler虚拟机进度回调

核验说明

  • 在 Python 3.11.9 / SQLite 3.45.1 中验证重复、漏项和复合游标结果，并比较 10 万行数据上两种查询的计划和进度回调。
  • 实验使用内存数据库和固定数据。生产表的字段分布、索引覆盖情况和并发写入需要用实际查询再核验。
#sqlite#sql#pagination#indexes
返回文章列表