何时在SQL中构建‘平均值’表而非直接使用AVG(x)查询？

我先梳理你的核心场景：你有一张outcomes表存储玩家分数记录，需要反复查询特定日期范围的玩家平均分，担心原生聚合查询的行读取量，纠结是否要预计算汇总表、冗余字段等方案。以下是实践中的常规处理思路：

一、这类问题的常规处理路径 #

实践中会围绕查询频率、数据更新频率、性能需求三个核心维度选择方案，优先级通常是：

1. 先优化原生查询性能，而非直接预计算
2. 优化后仍不满足需求，再考虑预计算（物化视图/汇总表）
3. 极端场景下才考虑冗余字段

第一步：优先优化原生AVG查询 #

你的基础查询SELECT Person, AVG(Score) FROM outcomes WHERE date < ? GROUP BY Person;可以通过覆盖索引大幅降低行读取量。创建如下复合索引：

CREATE INDEX idx_outcomes_person_date_score ON outcomes (Person, Date, Score);

这个索引包含了查询所需的所有字段，数据库可以直接从索引中获取数据，无需回表读取原记录，行读取量会骤降。即使是百万级数据规模，这个查询的性能也会非常可观。

如果需要查询多时间点的滚动平均，也可以用窗口函数，但窗口函数的性能依然依赖索引：

SELECT Person, Date, 
       AVG(Score) OVER (PARTITION BY Person ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg
FROM outcomes;

二、构建averages汇总表的适用场景 #

汇总表（即你设想的averages表）适合以下场景：

• 查询频率极高：比如每秒要执行几十次这类平均查询，且时间范围重复度高
• 数据更新频率低：outcomes表数据多为批量导入，很少有更新/删除操作，维护汇总表的成本低
• 性能要求苛刻：查询响应时间必须控制在毫秒级，原生查询加索引后仍达不到要求

维护汇总表的核心是保证数据一致性，常见两种方式：

1. 触发器更新：在outcomes表新增/更新/删除数据时，通过触发器自动同步更新averages表中对应玩家的平均值
2. 定时任务更新：比如每天凌晨跑脚本重新计算所有玩家的累计平均，适合数据更新不频繁的场景

但汇总表的缺点也很明显：

• 存储成本翻倍，随着玩家和记录数增长，存储压力会持续增加
• 存在一致性风险：触发器可能因异常失败导致数据不一致，定时任务则会有数据延迟

三、何时直接用AVG(x)更合适 #

直接使用AVG(x)聚合函数是首选方案，只要满足以下任一条件：

• 查询频率不高：比如每天仅执行几次查询，即使扫描全表也不会带来性能问题
• 数据更新频繁：outcomes表经常有新增、修改、删除操作，维护汇总表的成本远高于直接查询
• 服务器资源充足：数据库有足够的CPU和内存支撑聚合查询，且索引优化后性能达标

要知道：数据库的聚合函数是经过高度优化的，几百个玩家、每人几百条记录的规模，只要有合适的索引，AVG查询的性能完全不用担心。

四、是否应该在outcomes表中添加average列 #

绝对不建议这么做，原因如下：

• 冗余严重：每条记录都存储累计平均值，会导致存储量暴增（比如一个玩家有100条记录，就要存100次重复的平均值）
• 维护成本极高：新增一条记录时，该玩家之前所有记录的average列都需要更新，会产生大量写操作，性能开销极大
• 毫无必要：累计平均值是可以通过聚合或窗口函数实时计算的冗余数据，除非极端特殊场景，否则完全不需要存储

1. 先给outcomes表创建复合索引idx_outcomes_person_date_score，测试原生AVG查询的性能，大概率能满足你的需求
2. 如果查询频率极高且性能仍不达标，再考虑创建averages汇总表，用触发器或定时任务维护
3. 禁止在outcomes表中添加average冗余列

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