SQLite带LIMIT偏移的条件查询是否全表扫描？如何优化

你当前写的分页查询不仅会触发全表扫描，整体性能甚至比单次全表扫描更差。
核心原因有两个：

• 你目前没有给任何字段建索引，SQLite处理WHERE filesize < 100的条件时，本身就只能逐行遍历整张表的所有数据做判断，没有任何捷径可走。
• LIMIT 偏移量, 返回条数的语法在大偏移量场景下天生低效：SQLite执行这类语句时，必须先扫描、加载满足WHERE条件的前「偏移量+返回条数」条记录，丢弃前偏移量条之后，才会返回你要的结果。你每次间隔200行取1条的写法，越往后查询，需要从头扫描、丢弃的无用记录就越多，到几十亿行的量级时，靠后的查询耗时会线性增长，完全不具备可用性。
  另外提一句：table是SQLite的保留关键字，实际使用时不要直接当表名，否则SQL会报语法错误。

按照优化收益从高到低排序：

• 必须给筛选条件字段建索引
  如果你需要频繁按filesize做筛选，执行CREATE INDEX idx_tab_filesize ON 你的表名(filesize);；如果频繁按crtime筛选，就给crtime建对应索引。B树索引可以让SQLite直接定位到符合条件的记录区间，不需要逐行判断过滤，扫描范围直接从全表缩小到符合条件的数据集，性能提升是几个数量级的。
• 替换偏移量分页为键集分页（Seek Pagination）
  大表全量扫描不要用LIMIT x,y的偏移量写法，改用SQLite自带的rowid做断点续扫，写法如下：

  -- 首次查询，取第一批200条符合条件的记录
  SELECT rowid, name FROM 你的表名 WHERE filesize < 100 ORDER BY rowid LIMIT 200;
  -- 后续每一页查询，传入上一批结果里最大的rowid作为断点，不需要计算偏移量
  SELECT rowid, name FROM 你的表名 WHERE filesize < 100 AND rowid > 上一批次最大rowid ORDER BY rowid LIMIT 200;
  

  这种写法配合前面建的字段索引，每次查询只会从上一次中断的位置开始扫描，刚好取够200条结果就返回，不管扫到表的哪个位置，单次查询的耗时都是稳定的，不会出现越往后越慢的问题。
• 如果业务只是做全库合规校验，不需要逐行拉取数据处理，直接用单次聚合查询即可，完全不需要分页
  比如你要统计不符合条件的记录总数，直接执行：

  SELECT COUNT(*) FROM 你的表名 WHERE filesize >= 100;
  

  单次查询的总开销远小于拆分成成千上万次分页查询的总开销，避免了多次查询带来的重复扫描、连接交互成本。

额外提示：SQLite单表存储数十亿行数据是可行的，建议提前开启WAL模式，同时可以根据数据量把默认4KB的页大小调整为8KB或16KB，进一步提升顺序扫描的性能。

内容的提问来源于stack exchange，提问作者Eric GG