Sqlite ORDER BY 组的计数很慢

Question

当我向查询添加 ORDER BY 语句时，它变得非常慢。

这是我没有 ORDER BY 的查询:

SELECT ClientIpAddress, Agentstring, Count(ClientIpAddress) AS Count FROM LogEntries
WHERE SiteIisId = 3 AND DateTime >= '13-09-2012 00:00:00'
GROUP BY ClientIpAddress, Agentstring
LIMIT 5

ET:1ms

现在使用 ORDER BY:

SELECT ClientIpAddress, Agentstring, Count(ClientIpAddress) AS Count FROM LogEntries
WHERE SiteIisId = 3 AND DateTime >= '13-09-2012 00:00:00'
GROUP BY ClientIpAddress, Agentstring
ORDER BY Count DESC
LIMIT 5

ET:294 毫秒

我查询的表包含 1.380.855 行。

这是我正在使用的索引:

CREATE INDEX "LogEntries_MostActiveClients" ON "LogEntries" ("ClientIpAddress" ASC, "Agentstring" ASC, "SiteIisId" ASC, "DateTime" DESC)

使用 EXPLAIN QUERY PLAN Sqlite 告诉我它正在使用我的索引扫描表并且正在使用 TEMB B-TREE为我的 Order By。

我怎样才能克服这个问题？显然我不能索引 Count ， 那么该怎么办？

太感谢了!

Answer 1

当您单步执行结果集时，SQLite 会尝试动态计算尽可能多的值。

因此，在您的第一个查询中，SQLite 永远不需要对表中的所有地址/代理值进行分组；一旦它读取了前五个的记录ClientIpAddress/Agentstring通过一些索引组合，它可以停止。

在您的第二个查询中，这是不可能的:必须完全计算所有地址/代理组，然后才能对它们进行排序并选择前五个。

临时结果中待排序的记录已经在缓存中，并且比原表中的数据要小，所以我猜大部分时间不是花在排序上，而是分组上。

如果排序是问题所在，并且您估计了五个最大计数的大小，您可以尝试添加 HAVING "Count" >= some_limit子句以减少要排序的记录数。

您无法避免分组。
您所能尝试的只是通过通用优化进行小的改进，例如:

增加 SQLite 的 page cache到您的工作集的大小；和

创建 covering index以避免必须在表本身中进行查找(您已经拥有了)。

另一种方法是预先计算这个查询的值:有一个单独的表，你的 Count ，并在您添加日志条目时更新它。这将使这些更新变慢，并且您必须确定用于时间戳的粒度。