SQLite:使用 COUNT 和 GROUP BY 加速 SQL 语句

Question

我正在处理一个带有“状态”列的表，该列通常只包含 2 或 3 个不同的值。有时，当这个表有几百万行时，下面的 SQL 语句会变慢(我假设已完成全表扫描):

SELECT state, count(*) FROM mytable GROUP BY state

我希望得到这样的东西:

disabled |  500000
enabled  | 2000000

(基本上我想知道有多少项目被“启用”和有多少项目被“禁用”——实际上在我的实际应用程序中这是一个数字而不是文本)

我想为我的状态列添加索引是毫无用处的，因为在那里只能找到很少的不同值。我还有哪些其他选择？

还有一个“时间戳”列(带有索引)。理想情况下，如果我添加:

WHERE timestamp BETWEEN x AND y

现在我正在使用 SQLite3 数据库，但看起来其他数据库引擎并没有太大不同，因此其他数据库引擎的解决方案也可能很有趣。

谢谢!

Answer 1

我会在时间戳、状态(按此顺序)上放置一个覆盖索引。理由是:

• 时间戳上的条件会比状态更具选择性

• 如果状态仍在索引中(即覆盖索引)，引擎只需对索引本身生成范围扫描(无需支付随机 I/O 来访问索引的主要数据表)。

注意:如果时间戳范围太宽，尽管有索引也会变慢。因为随机 I/O 比顺序 I/O 更昂贵，所以索引范围扫描会变得比表扫描更昂贵。根据经验，如果需要扫描表的 10% 以上，引擎应该考虑保持表扫描而忽略索引。不过，我注意到 sqlite 足够智能以支持这种优化。