Sqlite 查询优化(使用限制和偏移) [英] Sqlite Query Optimization (using Limit and Offset)

问题描述

以下是我用于从具有数百万条记录的数据库中获取固定数量记录的查询:-

Following is the query that I use for getting a fixed number of records from a database with millions of records:-

select * from myTable LIMIT 100 OFFSET 0

我观察到的是，如果偏移量非常高，比如 90000，那么执行查询需要更多时间.以下是具有不同偏移量的 2 个查询之间的时间差:

What I observed is, if the offset is very high like say 90000, then it takes more time for the query to execute. Following is the time difference between 2 queries with different offsets:

select * from myTable LIMIT 100 OFFSET 0       //Execution Time is less than 1sec
select * from myTable LIMIT 100 OFFSET 95000   //Execution Time is almost 15secs

谁能建议我如何优化此查询?我的意思是，对于我希望从任何 OFFSET 检索的任意数量的记录，查询执行时间应该是相同且快速的.

Can anyone suggest me how to optimize this query? I mean, the Query Execution Time should be same and fast for any number of records I wish to retrieve from any OFFSET.

新增:-实际情况是我有一个超过 100 万条记录的数据库.但由于它是嵌入式设备，我无法执行select * from myTable"，然后从查询中获取所有记录.我的设备崩溃了.相反，我所做的是按照上面提到的查询逐批获取记录(批量大小 = 100 或 1000 条记录).但正如我所提到的，随着偏移量的增加，它变得很慢.所以，我的最终目标是我想从数据库中读取所有记录.但由于我无法在一次执行中获取所有记录，因此我需要其他一些有效的方法来实现这一点.

Newly Added:- The actual scenario is that I have got a database having > than 1 million records. But since it's an embedded device, I just can't do "select * from myTable" and then fetch all the records from the query. My device crashes. Instead what I do is I keep fetching records batch by batch (batch size = 100 or 1000 records) as per the query mentioned above. But as i mentioned, it becomes slow as the offset increases. So, my ultimate aim is that I want to read all the records from the database. But since I can't fetch all the records in a single execution, I need some other efficient way to achieve this.

推荐答案

正如 JvdBerg 所说，在 LIMIT/OFFSET 中不使用索引.简单地添加ORDER BY indexed_field"也无济于事.

As JvdBerg said, indexes are not used in LIMIT/OFFSET. Simply adding 'ORDER BY indexed_field' will not help too.

为了加快分页速度，您应该避免使用 LIMIT/OFFSET 并使用 WHERE 子句.例如，如果您的主键字段名为 'id' 并且没有间隙，那么您上面的代码可以像这样重写:

To speed up pagination you should avoid LIMIT/OFFSET and use WHERE clause instead. For example, if your primary key field is named 'id' and has no gaps, than your code above can be rewritten like this:

SELECT * FROM myTable WHERE id>=0     AND id<100     //very fast!
SELECT * FROM myTable WHERE id>=95000 AND id<95100   //as fast as previous line!

这篇关于Sqlite 查询优化(使用限制和偏移)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！