具有数百万条记录的 SQLite 中的高效分页 [英] Efficient paging in SQLite with millions of records

问题描述

我需要在列表视图中显示 SQLite 结果.当然，我需要对结果进行分页.

I need to show the SQLite results in a list view. Of course, I need to page the results.

第一个选项是使用 LIMIT 子句.例如:

The first option is to use the LIMIT clause. For example:

SELECT * FROM Table LIMIT 100, 5000

返回5001到5100条记录，问题是SQLite内部读取"了前5000条记录，效率不高.

It returns records 5001 to 5100. The problem is that internally SQLite "reads" the first 5000 records and it is not too efficient.

当有很多记录时，最好的分页方法是什么?

What is the best approach for paging when there are a lot of records?

推荐答案

请注意，您必须始终使用 ORDER BY 子句；否则，顺序是任意的.

Please note that you always have to use an ORDER BY clause; otherwise, the order is arbitrary.

要进行有效的分页，请保存已排序字段的第一个/最后一个显示值，并在显示下一页时在它们之后继续:

To do efficient paging, save the first/last displayed values of the ordered field(s), and continue just after them when displaying the next page:

SELECT *
FROM MyTable
WHERE SomeColumn > LastValue
ORDER BY SomeColumn
LIMIT 100;

(在 在 SQLite wiki 上有更详细的解释.)

(This is explained with more detail on the SQLite wiki.)

当您有多个排序列(以及 SQLite 3.15 或更高版本)时，您可以使用 row价值比较:

When you have multiple sort columns (and SQLite 3.15 or later), you can use a row value comparison for this:

SELECT *
FROM MyTable
WHERE (SomeColumn, OtherColumn) > (LastSome, LastOther)
ORDER BY SomeColumn, OtherColumn
LIMIT 100;

这篇关于具有数百万条记录的 SQLite 中的高效分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！