如何在已有索引约束下优化Bug大表的COUNT查询？

哇，碰到这种不能新增索引但查询超时的情况确实让人头疼，不过咱们可以充分利用现有资源和一些技巧来缓解这个问题，以下是几个针对你场景的可行方案：

1. 尝试利用现有复合索引的关联字段（如果业务逻辑允许） #

你现有的索引是 index_bugs_on_category_and_token_and_reported_at，包含 category、token、reported_at 三个字段。如果你的业务场景中，reported_at 不会早于 created_at（比如Bug创建后立即上报），那可以尝试用 reported_at 替代 created_at 作为过滤条件：

SELECT COUNT(*) FROM bugs WHERE reported_at >= '2019-03-01';

这个查询可以直接利用现有索引的全索引扫描（因为索引包含 reported_at），相比全表扫描，索引的数据量更小，扫描速度会快很多。注意一定要先验证业务逻辑上 reported_at 和 created_at 的关系，确保统计结果的准确性。

2. 按现有索引的前缀字段分批统计后累加 #

如果 reported_at 和 created_at 无法替代，那可以利用现有索引的前缀列（category）来拆分查询：

• 第一步，先获取所有不重复的 category 值：

  SELECT DISTINCT category FROM bugs;
  

• 第二步，针对每个 category 单独统计符合条件的Bug数量，最后手动累加结果：

  SELECT COUNT(*) FROM bugs WHERE category = 'xxx' AND created_at >= '2019-03-01';
  

因为 category 是现有索引的第一列，数据库可以快速定位到该 category 下的所有行，再在这个小范围内过滤 created_at，比全表扫描的效率高很多。如果你的 category 数量不多，这个方法的效果会很明显。

3. 使用近似计数（适合允许非精确结果的场景） #

如果你的业务场景不需要绝对精确的数量，可以利用数据库的统计信息来快速获取近似值：

• 比如在PostgreSQL中，可以查询系统表获取近似行数：

  SELECT reltuples::bigint AS approximate_count 
  FROM pg_class 
  WHERE relname = 'bugs';
  

  然后结合 created_at 的分布比例（比如从统计信息中获取2019-03-01之后的数据占比）来估算总数。
• MySQL中可以使用 EXPLAIN 来获取近似行数：

  EXPLAIN SELECT COUNT(*) FROM bugs WHERE created_at >= '2019-03-01';
  

  查看结果中的 rows 字段，就是数据库估算的匹配行数。
  这个方法的优势是速度极快，但结果是近似值，适合做快速报表、监控等场景。

4. 考虑创建物化视图（如果权限允许） #

如果你的数据库支持物化视图，并且业务可以接受一定的数据延迟，那可以创建一个按 created_at 预聚合的物化视图：

-- 以PostgreSQL为例
CREATE MATERIALIZED VIEW bugs_count_by_created_date AS
SELECT DATE(created_at) AS created_date, COUNT(*) AS bug_count
FROM bugs
GROUP BY DATE(created_at);

然后定期刷新这个物化视图（比如每晚），查询时直接从物化视图中累加2019-03-01之后的数量：

SELECT SUM(bug_count) FROM bugs_count_by_created_date WHERE created_date >= '2019-03-01';

这个方法可以将统计查询的时间降到最低，但需要确认你有创建物化视图的权限，并且能接受数据不是实时最新的。

内容的提问来源于stack exchange，提问作者Marko Sami