一、什么是实体化视图(materialized view)
普通视图(view)本质上只是一条存储起来的 select 查询语句。每次访问视图,都会实时执行这条 sql,从基表中计算结果,它不存数据,只是一个“查询别名”。
而实体化视图(materialized view,简称 mv,也常叫物化视图):
它会把查询的结果实实在在落地存储到磁盘上,生成一张物理表一样的数据集合。
简单一句话:
- 普通视图:查的时候实时计算,无存储;
- 物化视图:预先计算结果,持久化保存,查询直接读落地好的数据。
注意:pg 官方文档标准名称是 materialized view,国内习惯叫「物化视图」,本文标题使用实体化视图,二者是同一个东西。
二、普通视图 vs 实体化视图对比表
| 特性 | 普通 view | 实体化视图 materialized view |
|---|---|---|
| 数据存储 | 不存储,仅保存sql定义 | 磁盘持久化存储查询结果 |
| 查询性能 | 每次访问都执行底层sql,大数据、多表关联时慢 | 直接读取已经算好的数据,查询极快 |
| 数据时效性 | 实时,基表一变,视图查询结果立刻变 | 静态快照,不会自动更新,必须手动刷新 |
| 支持索引 | 不支持(本身不是表) | ✅ 支持创建索引、主键、唯一索引 |
| 支持dml | 一般不可直接update/insert(可通过规则触发器实现,不推荐) | 默认不能直接dml,只能通过刷新更新数据 |
| 适用场景 | 简单查询封装、权限隔离、简化sql | 报表、统计看板、多维聚合、大表join结果、高频只读查询 |
重点误区:物化视图不会自动同步基表数据! 这是最多人踩的坑。基表新增/修改数据,物化视图里面的数据不会自动跟着变,必须执行刷新命令。
三、基础语法实战
1. 创建实体化视图
语法:
create materialized view mv_user_order_stat
as
select
u.id as user_id,
u.username,
count(o.id) as order_cnt,
sum(o.amount) as total_amount
from t_user u
left join t_order o on u.id = o.user_id
group by u.id, u.username;
执行这条语句时,pg 会立刻执行子查询,把结果写入磁盘,生成 mv_user_order_stat 这个物化视图。
创建的时候可以加 with no data,只创建定义,不立刻填充数据:
create materialized view mv_user_order_stat
as
select
u.id as user_id,
u.username,
count(o.id) as order_cnt,
sum(o.amount) as total_amount
from t_user u
left join t_order o on u.id = o.user_id
group by u.id, u.username
with no data;
此时视图是空的,查询会报错,需要后续执行刷新填充数据。
2. 给物化视图建索引
这是物化视图的核心优势,报表场景必备:
-- 普通b树索引 create index idx_mv_user_id on mv_user_order_stat(user_id); -- 唯一索引 create unique index idx_mv_uid_unique on mv_user_order_stat(user_id);
索引建好之后,查询物化视图和普通表一样可以走索引加速。
3. 刷新(核心操作)
刷新分两种:refresh materialized view 和 refresh ... concurrently
方式1:普通刷新(锁表,简单)
refresh materialized view mv_user_order_stat;
逻辑:先清空物化视图全部数据,再重新跑查询写入新数据。
⚠️ 缺点:刷新过程中,会对物化视图加 access exclusive 锁,刷新期间无法查询这个mv,大结果集刷新时会有一段时间不可用。
方式2:并发刷新 concurrently(业务常用)
refresh materialized view concurrently mv_user_order_stat;
特点:
- 刷新期间允许正常查询物化视图,不会锁读;
- 原理:生成新版本数据,新旧版本共存,完成后原子切换;
- 硬性前提:这个物化视图必须有唯一索引(unique index),否则不能使用 concurrently;
- 开销比普通刷新更大,占用更多临时空间。
业务报表、对外提供查询的统计视图,优先用
concurrently;离线后台、非实时报表可以直接普通刷新。
4. 查询物化视图
和普通表写法完全一样:
select * from mv_user_order_stat where user_id = 1001;
5. 删除实体化视图
drop materialized view if exists mv_user_order_stat;
6. 修改物化视图定义
pg 没有直接 alter materialized view as 改查询语句的语法。
想要改底层sql,两种方案:
- 先 drop,再重新 create(简单,适合低访问场景)
- 使用
alter materialized view ... no data / data只控制是否带数据,不能改查询逻辑
如果是 pg14+ 可以使用 alter materialized view refresh 配置刷新参数,但不能修改select本体。
四、怎么定时自动刷新?
物化视图本身没有内置定时刷新能力,很多人以为它能自动同步,这是误区。
自动刷新一般两种方案:
方案a:pg_cron 扩展(最推荐,pg生态)
pg_cron 是citus开源的定时任务插件,在数据库内部调度:
-- 每小时并发刷新一次
select cron.schedule('refresh-mv-order-stat', '0 * * * *',
'refresh materialized view concurrently mv_user_order_stat;');
适合pg自建库,云数据库要看厂商是否支持pg_cron。
方案b:外部调度
linux crontab、airflow、django定时任务、java定时任务,直接连接数据库执行刷新sql。
适合云rds、不允许装扩展的环境。
五、常见踩坑清单
- concurrently 必须要有唯一索引,忘记建就直接报错
- 大物化视图普通刷新的时候,业务查询直接卡住,超时、报错
- 基表ddl变更(加字段、删表),物化视图不会自动感知,刷新时直接报错
- 物化视图不支持触发器、约束(除了唯一索引),不能当成业务主表来写
- 刷新会消耗大量cpu、io,业务高峰期不要执行刷新,尽量放到凌晨低峰
with no data创建的mv,直接select会报错:materialized view "xxx" has not been populated,需要先refresh- 物化视图会占用磁盘空间,相当于一份冗余数据,要做好容量规划
六、适用场景 & 不适合场景
✅ 适合使用实体化视图:
- 运营后台、bi报表、大屏统计,大量多表join、group by、聚合计算
- 底层原始表数据量大,每次实时聚合很慢,查询频次很高
- 数据不需要毫秒级实时,分钟/小时级延迟完全可以接受
- 数据仓库、数仓分层中间层,dwd/dws层汇总表
❌ 不适合:
- 需要实时和基表保持一致的数据(实时统计请用实时流、触发器、pg_stat_statements、普通视图)
- 频繁增删改、要求低延迟的业务主数据
- 结果集很小,实时查询本身就很快,没必要额外存储+维护刷新任务
七、进阶小技巧
- 分区物化视图:pg支持物化视图挂载分区表逻辑,适合按时间分区的统计报表
- 刷新的时候可以结合
pg_stat_activity观察刷新会话,长耗时刷新可以监控 - 可以给物化视图设置 fillfactor,适合频繁并发刷新的场景,减少页分 裂
- pg15 新增了
refresh materialized view ... skip concurrently等语法,增强控制
八、总结
postgresql 的实体化视图是一个非常好用的预计算快照方案,核心就是「用磁盘空间 + 定时刷新的延迟,换取查询速度」。
它和普通视图不是替代关系,是互补:
- 简单查询、需要实时一致性 → 普通视图
- 重聚合、高频查询、允许一定数据延迟 → 实体化视图
在做bi、数据看板、后台统计的时候,合理使用物化视图,可以大幅降低底层主库的计算压力,也是pg做轻量数仓非常经典的手段。
以上就是postgresql实体化视图(materialized view)的方法详解的详细内容,更多关于postgresql实体化视图的资料请关注代码网其它相关文章!
发表评论