报表聚合治理实战：从实时明细扫描到汇总表预计算

dbaai

报表聚合治理实战：从实时明细扫描到汇总表预计算

一、具体的问题

接手过一个订单分析系统，运营报表每次打开都要 6~8 秒，高峰期直接超时。最典型的一条 SQL：

2. SELECT DATE(pay_time)          AS stat_day,
3.        province, category,
4.        COUNT(*)                AS order_cnt,
5.        SUM(pay_amount)         AS pay_sum
6. FROM order_detail
7. WHERE pay_time >= '2026-01-01'
8. GROUP BY DATE(pay_time), province, category;

复制代码

order_detail 是一张 2.1 亿行的明细表。每打开一次报表，就把几个月几十 GB 的明细全量扫一遍、做一次完整 GROUP BY。报表并发一上来，业务库 CPU 顶到 78%，连 OLTP 写入都跟着抖。

第一反应往往是加索引。给 pay_time、province、category 建组合索引，收益很有限——这条 SQL 本来就要扫全部数据，索引只是让扫描有序一点，扫的行数一点没少。这类问题的正解不是把扫描变快，而是让报表根本不碰明细。

二、核心原理

先说透一件事：聚合查询的成本由"扫描行数"决定，不由"输出行数"决定。上面这条 SQL 输出只有几千行，却要扫上亿行。索引能让"扫"变得有序，不能让"少"。治理方向只有一个：把"每次查询都从头算"改成"提前算好，查询只读结果"——这就是汇总表预聚合。

但要保证三件事不塌：

• 增量可算。汇总表不能每天全量重算，必须按水位线增量推进，否则明细增速迟早超过重算速度。
  
• 口径唯一。分组键、指标定义必须有唯一出处，且每日对账兜底。否则业务改一处明细逻辑，报表数字静默漂移，没人发现。
  
• 原子可见。整段重建汇总数据时不能让查询读到空窗，用影子表 + RENAME 原子切换。
  

还有一个容易被推翻的点：COUNT(DISTINCT)、GROUP_CONCAT 这类指标在预聚合层不可再聚合——日去重用户数不能直接相加得到月去重用户数（用户跨天重复）。要么保留明细级二次聚合能力，要么明确接受近似算法（HLL）。很多汇总表上线后被推翻，根子都在这里。

三、实例参考（动手步骤）

步骤 0：确认现状基线

用 digest 汇总表找"扫描多、输出少"的聚合 SQL：

2. SELECT DIGEST_TEXT,
3.        COUNT_STAR,
4.        SUM_TIMER_WAIT/1e12 AS total_sec,
5.        SUM_ROWS_EXAMINED/COUNT_STAR AS avg_rows
6. FROM performance_schema.events_statements_summary_by_digest
7. WHERE DIGEST_TEXT LIKE 'SELECT%GROUP BY%'
8. ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

复制代码

avg_rows 到千万级、输出只有几千行的，就是预聚合的第一批目标。

步骤 1：设计汇总表

粒度取业务最细常用口径（这里按日 + 省 + 类目）：

2. CREATE TABLE report_order_day (
3.   stat_day   DATE NOT NULL,
4.   province   VARCHAR(32) NOT NULL,
5.   category   VARCHAR(32) NOT NULL,
6.   order_cnt  BIGINT NOT NULL DEFAULT 0,
7.   pay_sum    DECIMAL(18,2) NOT NULL DEFAULT 0,
8.   updated_at DATETIME NOT NULL,
9.   PRIMARY KEY (stat_day, province, category)
10. ) COMMENT '订单日报汇总：主键即分组键，upsert 幂等';

复制代码

主键就是分组键，重复刷新天然幂等。指标定义先和业务方书面确认（order_cnt 算不算退款单），这一步省掉的后患最多。

步骤 2：增量刷新（水位线 + 重叠窗口）

2. INSERT INTO report_order_day
3.   (stat_day, province, category, order_cnt, pay_sum, updated_at)
4. SELECT DATE(pay_time), province, category,
5.        COUNT(*), SUM(pay_amount), NOW()
6. FROM order_detail
7. WHERE pay_time > (
8.   SELECT IFNULL(MAX(watermark), '1970-01-01')
9.   FROM etl_watermark WHERE task = 'report_order_day')
10. GROUP BY DATE(pay_time), province, category
11. ON DUPLICATE KEY UPDATE
12.   order_cnt  = VALUES(order_cnt),
13.   pay_sum    = VALUES(pay_sum),
14.   updated_at = NOW();

复制代码

两个关键点：水位线持久化在 etl_watermark 表里；每次刷新把起点回看 1 小时重叠窗口（覆盖迟到支付），靠 upsert 幂等吸收重复计算。推进水位后按日抽样对账。

步骤 3：全量重建走影子表原子切换

口径调整或补数需要重建。直接 DELETE + INSERT 会让报表读到几十分钟空窗：

2. CREATE TABLE report_order_day_new LIKE report_order_day;
3. -- 灌入全量重算结果 ...
4. RENAME TABLE report_order_day     TO report_order_day_old,
5.              report_order_day_new TO report_order_day;
6. DROP TABLE report_order_day_old;

复制代码

RENAME 是原子的，查询无感知。

步骤 4：查询改写与灰度

报表 SQL 改查 report_order_day，日期聚合在几万行内完成。建议应用层留双数据源开关，新旧口径并行对账 3 天、差异为 0 再切流量，出问题一条配置回退。

步骤 5：PostgreSQL 对照：物化视图

PG 场景不必手建汇总表：

2. CREATE MATERIALIZED VIEW mv_order_day AS
3. SELECT pay_time::date AS stat_day, province, category,
4.        COUNT(*) AS order_cnt, SUM(pay_amount) AS pay_sum
5. FROM order_detail GROUP BY 1, 2, 3;
7. CREATE UNIQUE INDEX ON mv_order_day (stat_day, province, category);
8. REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_day;

复制代码

CONCURRENTLY 不阻塞读，但要求唯一索引，且刷新仍是全量重算——大表要控制刷新频率，或叠加水位表做增量。

步骤 6：对账兜底

每日凌晨抽最近 7 天明细 vs 汇总：

2. SELECT a.stat_day, a.order_cnt AS agg, b.c AS detail
3. FROM report_order_day a
4. JOIN (SELECT DATE(pay_time) d, COUNT(*) c
5.       FROM order_detail
6.       WHERE pay_time >= CURDATE() - INTERVAL 7 DAY
7.       GROUP BY 1) b ON a.stat_day = b.d
8. WHERE a.order_cnt <> b.c;

复制代码

差异行数 > 0 即告警。这一步是汇总表长期可信的保险丝。

四、实操检查清单

• digest 表确认慢 SQL 的 avg_rows 与输出行数比值，先证明确实是"扫描多、输出少"型，再动手
  
• 汇总表主键 = 业务最细常用分组键；指标口径（退款单算不算）书面确认留档
  
• COUNT(DISTINCT) 类指标已明确处理方式（近似或保留明细可查），字段命名区分 user_cnt / user_uv
  
• 增量刷新带重叠回看窗口（≥ 最大迟到时长），水位线持久化且任务失败不推进
  
• 全量重建走影子表 + RENAME 原子切换，禁用 DELETE + INSERT
  
• 上线前新旧口径并行对账 ≥ 3 天，差异为 0 再切流量
  
• 每日自动对账任务落位，差异告警发给能改明细逻辑的人
  
• 报表账号只读汇总表，与明细表权限分离
  

几个容易踩的坑

• 口径静默漂移：明细侧加了"退款单作废"逻辑，汇总侧没人同步，报表数字从那天起就不对——对账任务必须存在，告警要打到改明细的团队。
  
• 去重指标被当普通和数相加：日去重相加成"用户人次"却当"用户数"上报。命名上就分开，并在表注释写明哪些列不可相加。
  
• 迟到数据漏算：水位线按 pay_time 直线推进不留重叠窗口，迟到的离线支付永久丢失。重叠 + upsert 的代价可忽略，别省。
  
• 汇总表被反向当明细用：有人提"按订单号查"，一旦往汇总表塞明细字段，它就退化成第二张明细表。粒度边界写进表注释与评审流程。
  

治理前后对比

指标	治理前	治理后
报表查询 P95	6.8s	130ms
单次扫描行数	约 4200 万行	约 3.6 万行
业务库 CPU 峰值	78%	31%
报表并发承载	约 20	200+
口径对账	无，差异未知	每日自动，差异 0
重建可见性	DELETE+INSERT 空窗约 40min	RENAME 原子切换