在 SQLite 中分页浏览结果的最佳方式

Question

假设我在SQLite中有以下查询：

SELECT * FROM mytable ORDER BY product ASC

我希望最终用户能够以可滚动的方式查看数据(例如，像Excel中当前可查看的数据一样(。如果用户向下滚动 100 个结果，而不是执行以下操作：

SELECT * FROM mytable ORDER BY product ASC LIMIT 50 OFFSET 100

我想确保返回给他们的结果在 25 毫秒左右，而不是让他们等待每次超出可视范围时执行查询所需的时间。

最好的方法是什么？在伪代码中，我的想法如下：

-- 1st time query is run
SELECT * FROM mytable ORDER BY product ASC LIMIT 50 -- in main thread
INSERT INTO queryset_cache -- in background thread
SELECT rowid, * FROM mytable ORDER BY product ASC 
-- 2nd time query is run, offset 84,500 (user scrolls down to that position
SELECT * FROM queryset_cache ORDER BY rowid LIMIT 50 OFFSET 84500

这是完成我想要做的事情的最佳方式吗？或者有没有一种标准化的方法可以在SQLite中做类似的事情？如果是这样，最好的方法是什么？

Answer 1

使用 OFFSET 通常不是一个好主意，因为数据库在返回结果之前必须跳过这么多行。

一个好的解决方案是为product列和SELECT ... FROM ... WHERE product BETWEEN _start_ AND _end_编制索引

并不断更改开始和_end，以便数据库返回所需的页面。

如果这很困难，比如说，您有很多行具有相同的产品，您可以定义一个组合键(product, xxx)，以更好地选择行并使用它来选择页面。

如果所有其他方法都失败了，并且您决定创建一个用于分页的新表，请使用该键id列和页面添加自动增量。