在160GB的数据库中选择需要很长时间

Question

我有一个160GB的SQLite数据库。表hwmp的id列和stage列上有一个索引。

CREATE INDEX idx_hwmp_id ON hwmp (id, stage);

当我进行行计数时，查询将在0.09秒内返回。

sqlite> select count (*) from hwmp where id = 2000 and stage = 4;
59397
Run Time: real 0.091 user 0.000074 sys 0.080494

然而，对于选择，所有的实时时间都是85秒。用户和系统的时间加起来只有2.5秒。为什么实际时间会这么高？

select * from hwmp where id = 2000 and stage = 4;
Run Time: real 85.420 user 0.801639 sys 1.754250

如何修复？对sqlite3数据库(300MB(的另一个查询过去在20ms内返回。今天花了652毫秒。

Run Time: real 0.652 user 0.018766 sys 0.010595

今天的Linux环境出现了问题。我把同样的SQLite下载到我的Mac上，它运行得很快。

Run Time: real 0.028 user 0.005990 sys 0.010420

它正在使用索引：

sqlite> explain query plan select * from hwmp where id = 78 and stage = 4;
QUERY PLAN
`--SEARCH hwmp USING INDEX idx_hwmp_id (id=? AND stage=?)
Run Time: real 0.005 user 0.000857 sys 0.000451

Answer 1

相关设置为pragma cache_size = 200000;200000页4096字节。设置后，第一次查询大约需要3s，第二次查询需要0.28s。

缓存设置在一段时间内提高了性能。我们正在使用一个连接了EBS SSD的AWS linux虚拟机。环境似乎也有问题。我的Mac中的查询时间比AWS linux/EBS环境快6.3倍。