SQL报表的SUM统计是业务分析中非常常见的需求，当数据量增长到一定规模后，直接对原始明细表执行SUM聚合查询，往往会因为全表扫描、大量数据计算导致查询耗时过长，影响用户体验。预聚合表优化是解决这类问题的有效方案之一，通过提前对数据进行分层汇总存储，大幅减少查询时的计算量。
SUM统计慢的核心原因
直接对明细表做SUM统计慢，主要有以下几个原因：
- 数据量过大，全表扫描的IO成本高，即使有索引也很难覆盖SUM聚合的需求
- 聚合计算需要遍历所有符合条件的行，逐行累加，计算耗时随数据量线性增长
- 多个维度组合的SUM查询无法复用计算结果，每次查询都要重新计算
- 高峰期大量报表查询并发执行，会进一步加剧数据库的资源压力
预聚合表的设计思路
预聚合表的核心逻辑是“空间换时间”，提前按照常用的查询维度对数据进行汇总，存储聚合后的结果，查询时直接读取预计算的结果即可，不需要再扫描原始明细表。
维度选择
首先要梳理报表常用的查询维度，比如常见的维度有时间维度（年、月、日）、业务维度（渠道、品类、地区）等，预聚合表需要覆盖这些常用维度的组合，避免查询时还需要二次聚合。
粒度设计
预聚合表的粒度需要根据查询需求设计，通常可以设计多层粒度：
- 明细粒度：和原始表一致，一般不作为预聚合表的粒度
- 日粒度：按照天+业务维度汇总，满足按天查询的报表需求
- 月粒度：按照月+业务维度汇总，满足按月查询的报表需求
- 总粒度：全量汇总，满足只看总计的查询需求
预聚合表的实现步骤
1. 创建预聚合表
以电商订单的销售额SUM统计为例，假设常用查询维度是统计日期、商品品类，我们可以创建日粒度的预聚合表：
-- 创建日粒度预聚合表
CREATE TABLE order_sales_daily_agg (
stat_date DATE NOT NULL COMMENT '统计日期',
category_id INT NOT NULL COMMENT '商品品类ID',
category_name VARCHAR(50) NOT NULL COMMENT '商品品类名称',
total_sales DECIMAL(18,2) NOT NULL COMMENT '当日该品类销售额汇总',
order_count INT NOT NULL COMMENT '当日该品类订单数',
PRIMARY KEY (stat_date, category_id)
) COMMENT '订单销售额日预聚合表';
2. 定时更新预聚合表数据
预聚合表的数据需要通过定时任务更新，通常可以在每天凌晨执行，计算前一天的聚合数据并插入或更新到预聚合表中：
-- 插入或更新昨日预聚合数据
INSERT INTO order_sales_daily_agg (stat_date, category_id, category_name, total_sales, order_count)
SELECT
DATE(create_time) AS stat_date,
category_id,
MAX(category_name) AS category_name,
SUM(sale_amount) AS total_sales,
COUNT(order_id) AS order_count
FROM order_detail
WHERE DATE(create_time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY DATE(create_time), category_id
ON DUPLICATE KEY UPDATE
total_sales = VALUES(total_sales),
order_count = VALUES(order_count),
category_name = VALUES(category_name);
3. 查询时直接使用预聚合表
优化后的报表查询直接读取预聚合表，不需要再扫描原始订单明细表：
-- 查询2024年5月各品类的销售额汇总
SELECT
category_id,
category_name,
SUM(total_sales) AS total_sales,
SUM(order_count) AS total_order_count
FROM order_sales_daily_agg
WHERE stat_date BETWEEN '2024-05-01' AND '2024-05-31'
GROUP BY category_id, category_name;
预聚合表的优缺点
| 优点 | 缺点 |
|---|---|
| 查询性能提升明显，SUM统计耗时从秒级降到毫秒级 | 需要额外的存储空间存放预聚合数据 |
| 减少原始表的查询压力，避免大查询影响线上业务 | 预聚合数据存在延迟，不适合实时性要求极高的场景 |
| 支持多维度的快速聚合，不需要查询时再做复杂计算 | 新增查询维度时需要新增预聚合表或调整现有表结构 |
适用场景与注意事项
预聚合表优化适合以下场景：
- 报表查询的维度相对固定，不会频繁新增新的查询维度
- 数据实时性要求不高，允许分钟级或天级的数据延迟
- SUM统计的查询频率高，且数据量较大，直接查询性能无法满足需求
注意事项：
- 预聚合表的更新任务要做好监控，避免更新失败导致数据不一致
- 如果原始数据有更新或删除操作，需要同步更新预聚合表的数据，保证数据一致性
- 可以根据查询频率调整预聚合表的粒度，高频查询的维度优先做预聚合
总结
对于SQL报表中SUM统计慢的问题，预聚合表是一种成熟且有效的优化方案，通过提前计算汇总结果，能够大幅降低查询时的计算成本，提升报表响应速度。实际落地时需要根据业务的查询维度、实时性要求合理设计预聚合表的粒度和更新策略，平衡存储成本和查询性能，达到最优的优化效果。