sqlite 即使在创建索引后,具有多个值的WHERE子句也太慢

lf5gs5x2  于 2022-11-15  发布在  SQLite
关注(0)|答案(1)|浏览(165)

我的表checked_result包含列idxABC,包含200.000多条记录。
我为idx和每一列以及这些列的组合创建了索引,总共16个索引:IDX,A,B,C,AB,BC,AC,ABC,(IDX,A),(IDX,B),(IDX,C),(IDX,A,B),(IDX,A,C),(IDX,B,C),(IDX,A,B,C)。
应根据查询筛选的方式使用所有索引。当我对单列使用查询时:

SELECT * FROM checked_result WHERE (A in ('123')) AND idx >= 0 ORDER BY B DESC LIMIT 10
SELECT * FROM checked_result WHERE (A in ('123')) AND idx >= 0 ORDER BY B DESC, C ASC LIMIT 10

它很快,但当我过滤一列中的多个值时:

SELECT * FROM checked_result WHERE (A in ('123','456')) AND idx >= 0 ORDER BY B DESC
SELECT * FROM checked_result WHERE (A in ('123','456')) AND idx >= 0 ORDER BY B DESC, C ASC LIMIT 10

它真的很慢。为什么即使在索引之后也会发生这种情况?我怎么才能让它更快呢?

EXPLAIN QUERY PLAN SELECT * FROM checked_result WHERE (A in ('123')) AND idx >= 0
EXPLAIN QUERY PLAN SELECT * FROM checked_result WHERE (A in ('123','456')) AND idx >= 0

每个选项都显示以下结果:

SEARCH TABLE checked_result USING INDEX idx_checked_result_A_B (A=?)
SCAN TABLE checked_result USING INDEX idx_checked_result_A_B_C
mm5n2pyu

mm5n2pyu1#

您应该只构建查询真正需要的索引。由于可能有许多索引可用,因此运行Analyze让SQLite选择正确的索引非常重要。
正如评论中指出的,您的许多索引都是多余的。如果您有索引ABC,则不需要索引A和索引AB。此外,如果WHERE子句中存在不平等,则索引中列的顺序非常重要。在您的查询中,可以使用索引(A,IDX),这将是有用的,但不能使用您的索引(IDX,A)。因为您没有(A,IDX)索引,所以唯一可以用来减少要读取的记录数量的索引是索引(A)。(编辑:您的查询规划者实际上选择了索引AB而不是A,因为ORDER BY B,但它没有太大变化)。
然后使用索引ABC以您选择的顺序返回记录。如果只有ABC索引,可能会更好,因为SQLite将对WHERE和ORDER BY子句使用相同的索引。
即使使用索引,查询的速度也取决于所选索引的选择性程度。如果您的记录有数千个不同的A值,那么索引将是非常有选择性的,并且只会读取表的一小部分。但是,如果您只有12个不同的值,那么查询将不得不读取表的很大一部分,并且您在IN子句中添加的每个值都会使情况变得更糟。
有关SQLite查询优化器的更多信息,请单击此处:https://www.sqlite.org/optoverview.html

相关问题