400万条answers表数据下COUNT(CASE)查询优化咨询

针对你这个处理400万条记录的慢查询，我整理了几个立竿见影的优化思路，咱们一步步来：

1. 优先创建复合覆盖索引 #

这是最能快速提升性能的核心方案。原查询的瓶颈大概率是缺少合适的索引，导致数据库不得不扫描大量符合时间范围的行甚至全表。

你需要创建一个包含过滤条件和统计字段的复合索引：

CREATE INDEX idx_answers_createdat_value ON answers(created_at, value);

这个索引的作用是：

• 利用created_at快速定位到时间范围内的记录，直接避免全表扫描
• 索引中已经包含了value字段，数据库不需要再回表查询原数据（这就是覆盖索引的特性），直接从索引里就能完成所有计数统计。

2. 简化聚合函数写法（可选，更直观高效） #

原查询里的COUNT(CASE WHEN VALUE = n THEN 1 END)可以替换成SUM(CASE WHEN VALUE = n THEN 1 ELSE 0 END)，两者功能完全一致，但SUM的写法更直观地表达了“统计符合条件行数”的意图。在部分数据库引擎中，这种写法的执行计划也会更高效。

修改后的查询示例：

SELECT 
  SUM(CASE WHEN VALUE = 1 THEN 1 ELSE 0 END) AS score_1,
  SUM(CASE WHEN VALUE = 2 THEN 1 ELSE 0 END) AS score_2,
  SUM(CASE WHEN VALUE = 3 THEN 1 ELSE 0 END) AS score_3,
  SUM(CASE WHEN VALUE = 4 THEN 1 ELSE 0 END) AS score_4,
  SUM(CASE WHEN VALUE = 5 THEN 1 ELSE 0 END) AS score_5,
  SUM(CASE WHEN VALUE = 6 THEN 1 ELSE 0 END) AS score_6,
  SUM(CASE WHEN VALUE = 7 THEN 1 ELSE 0 END) AS score_7,
  SUM(CASE WHEN VALUE = 8 THEN 1 ELSE 0 END) AS score_8,
  SUM(CASE WHEN VALUE = 9 THEN 1 ELSE 0 END) AS score_9,
  SUM(CASE WHEN VALUE = 10 THEN 1 ELSE 0 END) AS score_10
FROM `answers` 
WHERE `created_at` BETWEEN '2017-01-01 00:00:00' AND '2019-11-30 23:59:59';

3. 先分组统计再转置（适合value枚举值固定的场景） #

如果你的value字段只有1-10这几个固定值，可以先按value分组统计总数，再转置成列。这种方式的优势是分组阶段的数据量极小（最多10行），后续转置几乎没有性能开销：

SELECT
  SUM(CASE WHEN value = 1 THEN cnt ELSE 0 END) AS score_1,
  SUM(CASE WHEN value = 2 THEN cnt ELSE 0 END) AS score_2,
  SUM(CASE WHEN value = 3 THEN cnt ELSE 0 END) AS score_3,
  SUM(CASE WHEN value = 4 THEN cnt ELSE 0 END) AS score_4,
  SUM(CASE WHEN value = 5 THEN cnt ELSE 0 END) AS score_5,
  SUM(CASE WHEN value = 6 THEN cnt ELSE 0 END) AS score_6,
  SUM(CASE WHEN value = 7 THEN cnt ELSE 0 END) AS score_7,
  SUM(CASE WHEN value = 8 THEN cnt ELSE 0 END) AS score_8,
  SUM(CASE WHEN value = 9 THEN cnt ELSE 0 END) AS score_9,
  SUM(CASE WHEN value = 10 THEN cnt ELSE 0 END) AS score_10
FROM (
  SELECT value, COUNT(*) AS cnt
  FROM `answers`
  WHERE `created_at` BETWEEN '2017-01-01 00:00:00' AND '2019-11-30 23:59:59'
  GROUP BY value
) AS grouped;

配合前面创建的复合索引，这个子查询的分组统计会非常快——因为索引已经按created_at和value排序好了，数据库可以直接快速聚合。

4. 长期优化：表分区（针对超大规模数据） #

如果你的answers表数据持续增长，未来会超过千万级，可以考虑按created_at字段做分区（比如按年、季度分区）。这样查询指定时间范围时，数据库只会扫描对应的分区，而不是整个表，性能提升会非常明显。具体的分区方式需要根据你使用的数据库（MySQL/PostgreSQL等）来调整。

内容的提问来源于stack exchange，提问作者Hovo Asatryan