SQLite键集分页 从复合游标到变化数据的读取边界
从一个具体请求或状态变化出发,追踪数据流与方法成立的条件。
章节速览6 节 · 展开选择
联合索引解释怎样沿有序键定位读取段,SQLite事务说明一个读事务看到的快照。把列表分成多页时,这两条线需要连接起来:下一页是“跳过目前排在前面的若干行”,还是“继续读取上次末项之后的键”?
本篇限定SQLite整数键、同一用户筛选、非空时间戳与唯一id的降序列表。SQL是可复现教学查询,数字均人工构造;不提供网页接口,也不宣称实测生产吞吐。
先给排序补上唯一的最后一项
设记录有id、user_id、created_at,其中id唯一,created_at为整数且NOT NULL。用户7的五条记录按(created_at DESC,id DESC)排列为(30,5)、(30,4)、(20,3)、(20,2)、(10,1),每页两条。
只按created_at排序,时间相同的记录没有由查询指定唯一顺序;分页位置就可能不稳定。SQLite对ORDER BY并列项的边界见SELECT官方文档。本文加id作为确定平局的最后排序键,而不是假定插入顺序会自动成为排序条件。
1 | CREATE TABLE items ( |
本篇只讨论整数时间,避免把日期字符串格式与排序规则混进同一个例子。若实际系统使用文本、可空字段、不同collation或混合排序方向,游标比较必须与ORDER BY逐项一致,不能直接照抄本式。
OFFSET保存的是数量,游标保存的是边界
第一页按用户筛选后取前两条,末项为(30,4)。对于降序列表,下一页要严格小于这个复合键,不能使用大于,也不能只比较时间30。
1 | SELECT id, created_at FROM items |
1 | SELECT id, created_at FROM items |
第二个查询依次绑定用户7、游标时间30、游标id4和页大小2。SQLite行值文档提供多列比较与滚动窗口模式;本篇使用降序和唯一id,逐项反转了下一页方向。
等价于:t<c_t,或t=c_t且id<c_id
两个游标字段都来自最后一条实际返回记录。整数非空,id唯一;本篇不把NULL或不同方向字段纳入这个简式。
| 页 | 返回的id | 最后一项游标 |
|---|---|---|
| 第一页 | 5,4 | (30,4) |
| 第二页 | 3,2 | (20,2) |
| 第三页 | 1 | (10,1) |
若改成created_at<30,会跳过尚未读到的同时间戳记录;若使用≤复合键,会把上一页末项再次返回。在这个五行例子中,第一种错误恰好没漏下一条30,因为该时间只有两行;增加第三条同时间记录即可暴露它,不能只依赖一个幸运样例。
flowchart TD A[固定筛选与完整排序] --> B[读取一页] B --> C[保存最后一项完整键] C --> D[严格比较边界之后的键] D --> B
插入与删除使偏移量漂移
第一页读完id5、4后,另一个操作插入更靠前的(40,6)。现在列表为6、5、4、3、2、1。若下一次仍用OFFSET 2,就从id4开始,重复一条;复合游标仍取小于(30,4)的记录,得到3、2。
反过来,若第一页后删除id5,现在列表为4、3、2、1,OFFSET 2从id2开始,遗漏尚未读过的id3;游标仍得到3、2。两段是互斥的教学历史,不在同一数据库状态上连续拼接。
自检1:保存第一页的行数2,为何不等于保存了读取边界?
别的操作会改变当前排在前面的记录数量。完整末项键描述排序位置,固定偏移只描述本次查询要跳过多少条。
游标稳定不等于固定快照
若每次查询都在新的读事务中执行,它们可以看到不同快照。后来插入(25,7),位于游标(30,4)之后,下一页会包含它;后来插入(40,6),则在已越过的前部,不会被这次向后遍历读到。键集分页定义的是移动边界,不自动定义“首次打开列表时已有的全部记录”。
排序键更新也可能跨越边界:已经读过的id5若从时间30改成15,可能在后续页再次出现;尚未读过的id3若从20改成40,可能移到边界前而不再出现。唯一id只能消除平局,不能使可变时间戳变成不可变。
若任务是导出固定集合,可在适当读事务中保持同一快照,或另行固定对象清单;WAL模式下长读事务也有运维代价,不应无限延长。只保存一个“起始最大id”也不普遍冻结结果:它可能限制新增id,却不能自动冻结旧行的删除、字段更新和筛选变化。
flowchart TD
A[需要连续浏览还是固定导出] --> B{是否要求同一时点集合}
B -->|连续浏览| C[明确可见新增与键更新语义]
B -->|固定集合| D[另行设计快照或对象清单]
C --> E[游标只表达排序边界]
D --> E
索引、反向与接口边界
复合索引把用户等值筛选和排序键放在同一路径上,有利于从游标附近读取。OFFSET通常还需越过被丢弃的前缀,SQLite文档对此有明确解释;但不能据此给所有查询承诺固定耗时。投影列、过滤条件、覆盖索引、统计信息与执行计划仍会影响实际工作量,本文不做毫秒排名。
要回到上一页,可取大于当前页首键的记录,按升序获取最近的若干条,再在显示前反转。不能只把比较符改成大于,却继续降序取LIMIT,那会优先取到列表最前部,而非紧邻当前页的前一页。
接口中的游标还应绑定用户、筛选、排序版本与字段类型;编码不是授权。客户端传入的游标不能改变服务端权限范围,SQL参数继续绑定,页大小应限定正上限。游标失效、字段语义改变和列表为空时要有明确处理;键集分页通常不能仅凭页码直接跳到任意第几页。
自检2:用了唯一id和游标,就能保证整个遍历不重不漏吗?
只在固定结果集合与固定排序键等条件下才能作这种保证。跨请求的新快照、删除与排序键更新仍会改变遍历集合,需要另外定义一致性目标。
参考资料
- SQLite Row Values:字典序比较、NULL边界与滚动窗口。
- SQLite SELECT:ORDER BY与LIMIT语义。数据与变化历史由本文自行构造;代码片段需要在给定表结构和参数契约下执行。