如何优化SQLite ORDER BY rowid？

Question

我想查询我的sqlite数据库中所有大于20的“级别”值，将结果限制为100，并按rowid进行排序。

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

该语句大约需要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查询？

这是使用的索引：

CREATE INDEX level_idx ON table (level)

Answer 1

有两种可能的方法来执行此查询：

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

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

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

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

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

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