我正在尝试使用该表提高查询的性能:
CREATE TABLE frag(hash primary key, type, ulen, clen, offset, refs);
SELECT MAX(offset + clen) AS 'EndPos', type AS 'Type' FROM frag GROUP BY type;
我的查询计划如下:
sqlite> EXPLAIN QUERY PLAN SELECT MAX(offset + clen) AS 'Offset', type AS 'Type' FROM frag GROUP BY type;
QUERY PLAN
|--SCAN TABLE frag
`--USE TEMP B-TREE FOR GROUP BY
创建新索引后,查询计划更改为:
CREATE INDEX max_frag ON frag(type, offset+clen DESC);
sqlite> EXPLAIN QUERY PLAN SELECT MAX(offset + clen) AS 'EndPos', type AS 'Type' FROM frag GROUP BY type;
QUERY PLAN
`--SCAN TABLE frag USING INDEX max_frag
我的问题是,使用这个新索引,我的查询速度已经从90秒降到了280秒。我的数据库有5.6 GB大小,有4550万行。为什么全表扫描比使用此索引更快?.expert
告诉我,没有新的索引可以改善这一点
2条答案
按热度按时间xj3cbfub1#
我不知道SQLite是否支持函数索引,但您可以在这里尝试的另一种方法是将总和
offset + clen
存储在表中作为某个新列,比如total
。然后,您可以将以下索引添加到表中:然后,上面的索引应该与以下查询一起使用:
hjzp0vay2#
正如您在评论中所述,索引中支持表达式。但是,“当被索引的表达式出现在查询的WHERE子句或ORDER BY子句中时,SQLite查询规划器将考虑在该表达式上使用索引”。
因为您在聚合函数中使用它,所以不会使用索引的该部分,而只使用索引的type部分。如果type是一个在整个表中只有几个不同值的字段,并且这些值分散在数据库的所有页面中,则结果是对于每个type值,SQLite必须读取几乎所有的表页面才能获得max(偏移量+clen),并且对于每个type值都必须重复这一过程。
解决方案是将生成的列添加到表中
然后在索引和查询中使用该列
结果将是只读取索引,而不访问表数据,查询计划应该如下所示:
编辑:我在测试中尝试了上面的方法,显然,SQLite不会对生成的列使用覆盖索引。
另一种解决方案是重写查询以利用这样一个事实,即您使用的是Max(),并且索引是降序的,因此任何类型的第一条记录都是具有最大值的记录: