预聚合视图更快是因为将高频查询所需的SUM、COUNT等聚合计算提前固化为物化结果,查询时仅扫描万级聚合数据而非百亿行明细,响应从分钟级降至亚秒级;需严格对齐业务粒度、避免冗余字段、按时间分区,并通过物化视图+定时刷新(PostgreSQL)或汇总表+增量更新(MySQL)实现。

为什么预聚合视图比直接查原始表快得多
因为大屏查询通常只关心「按天/按地区/按品类的汇总指标」,比如 SUM(sales_amount)、COUNT(DISTINCT user_id),而原始表是百亿行明细(如每笔订单一行)。每次刷屏都全表扫描+实时聚合,IO 和 CPU 压力巨大。预聚合视图把计算提前固化到物化结果里,查询时只需扫几万行聚合后数据,响应从分钟级降到亚秒级。
- 聚合粒度必须对齐业务查询模式:大屏若只展示「地市+日粒度销售额」,视图就只建
GROUP BY city, stat_date,别加冗余维度(如order_id) - 避免在视图中用
SELECT *:字段越多,存储膨胀越严重,后续增删字段也容易引发下游报表错列 - 时间分区是刚需:按
stat_date分区后,即使查最近7天,数据库也能自动裁剪掉99%的分区文件
PostgreSQL 中如何创建可刷新的预聚合视图
PostgreSQL 本身不支持物化视图自动刷新(14+ 才有 REFRESH MATERIALIZED VIEW CONCURRENTLY),但可用 CREATE MATERIALIZED VIEW + 定时任务组合落地:
CREATE MATERIALIZED VIEW mv_daily_sales AS SELECT city, stat_date, SUM(sales_amount) AS total_sales, COUNT(DISTINCT user_id) AS uv FROM fact_order WHERE stat_date >= CURRENT_DATE - INTERVAL '30 days' GROUP BY city, stat_date;
- 刷新不能锁表:用
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales(注意:要求主键或唯一索引支持并发刷新) - 每次刷新前先检查依赖表是否被 DDL 修改:否则
REFRESH会失败并报错relation "fact_order" does not exist - 不要设太短的刷新间隔(如每分钟):频繁刷新会导致 WAL 写放大,影响线上写入性能
MySQL 用户该用什么替代方案
MySQL 没有原生物化视图,得靠「汇总表 + 应用层调度」模拟:
创建结构一致的汇总表:
CREATE TABLE t_daily_sales (city VARCHAR(32), stat_date DATE, total_sales DECIMAL(18,2), uv INT, PRIMARY KEY(city, stat_date))用
INSERT ... SELECT ... ON DUPLICATE KEY UPDATE增量更新,避免全量重算关键陷阱:别用
REPLACE INTO,它会先删后插,触发 DELETE 触发器且无法回滚部分失败如果原始表有
ON UPDATE CURRENT_TIMESTAMP字段,汇总表必须显式忽略,否则时间戳会被意外覆盖查询时强制走汇总表:在应用 SQL 中明确写
FROM t_daily_sales,而不是依赖视图名——MySQL 视图只是语法糖,不改变执行计划
哪些聚合函数会破坏预聚合效果
不是所有聚合都能安全下推到预聚合层。SUM、COUNT、AVG(需同时存 SUM 和 COUNT)可以;但以下函数会导致二次计算,失去加速意义:
-
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount):中位数无法拆分合并,必须原始明细 -
STRING_AGG(user_name, ','):去重和排序逻辑在聚合后不可逆 -
MAX(last_login_time):看似简单,但如果原始表存在多条同一天记录,MAX结果可能被错误截断(比如只聚合了当天部分批次)
真正能稳压百亿量级的预聚合,核心不是“堆函数”,而是“砍维度、控时效、定分区”。漏掉任意一环,大屏一卡,你就要半夜爬起来看慢查日志。

















