如何优化SQLite ORDER BY rowid? [英] How can I optimize SQLite ORDER BY rowid?

问题描述

我想在我的sqlite数据库中查询所有大于20的级别"值，将结果限制为100，并按rowid排序.

I want to query my sqlite db for all "level" values greater than 20, limit the result to 100 and order by rowid.

按rowid排序时，查询速度要慢得多.该数据库包含约300万条记录 级别的最大值为50.将为级别创建一个索引.

When ordering by rowid, the query is much slower. The database contains ~3 million records and the maximum value of level is 50. An index is created for level.

此语句大约需要20毫秒:

SELECT * FROM log WHERE level > 20 LIMIT 100

此语句大约需要100毫秒:

SELECT * FROM log WHERE level > 20 ORDER BY rowid LIMIT 100

此语句大约需要1000毫秒(不存在级别值大于50的行):

SELECT * FROM log WHERE level > 50 ORDER BY rowid LIMIT 100

是否有一种方法可以对此进行优化，以实现更快的ORDER BY查询?

Is there a way to optimize this for faster ORDER BY query's?

这是使用的索引:

CREATE INDEX level_idx ON table (level)

推荐答案

有两种方法可以执行此查询:

There are two possible methods to execute this query:

1. 在level_idx索引中用level>20搜索第一个条目，然后扫描以下所有条目，并从表中获取每个对应的行. 由于索引条目未按rowid顺序存储，因此必须对所有结果进行排序. 然后可以返回其中的前100个.

1. Search the first entry with level>20 in the level_idx index, and then scan through all the following entries and fetch each corresponding row from the table. Because the index entries are not stored in rowid order, all the results must then be sorted. Then the first 100 of those can be returned.

忽略索引. 扫描表的所有行(已按rowid顺序存储)，并返回与level列匹配的任何行.

Ignore the index. Scan through all rows of the table (which are already stored in rowid order), and return any where the level column matches.

数据库估计第二种方法更快.

The database estimates that the second method is faster.

如果您估计第一种方法更快，即与level过滤器匹配的行太少，以至于对其余行进行获取和排序比在扫描表时忽略不匹配的行要快，那么您可以强制数据库使用带有INDEXED BY子句的索引:

If you estimate that the first method is faster, i.e., that so few rows match the level filter that fetching and sorting the remaining rows is faster than ignoring the non-matching rows while scanning through the table, then you can force the database to use the index with the INDEXED BY clause:

SELECT *
FROM log INDEXED BY level_idx
WHERE level > 20
ORDER BY rowid
LIMIT 100

但是，如果您自己的估计错误，强行编制索引可能会导致可怕的速度下降.

However, forcing an index can lead to horrible slowdowns if your own estimate is wrong.

这篇关于如何优化SQLite ORDER BY rowid?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！