当前位置: 代码网 > it编程>数据库>MsSqlserver > PostgreSQL实体化视图(Materialized View)的方法详解

PostgreSQL实体化视图(Materialized View)的方法详解

2026年11月17日 • MsSqlserver •我要评论
一、什么是实体化视图(materialized view)普通视图(view)本质上只是一条存储起来的 select 查询语句。每次访问视图,都会实时执行这条 sql,从基表中计算结果,它不存数据,只

一、什么是实体化视图(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,两种方案:

  1. 先 drop,再重新 create(简单,适合低访问场景)
  2. 使用 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、不允许装扩展的环境。

五、常见踩坑清单

  1. concurrently 必须要有唯一索引,忘记建就直接报错
  2. 大物化视图普通刷新的时候,业务查询直接卡住,超时、报错
  3. 基表ddl变更(加字段、删表),物化视图不会自动感知,刷新时直接报错
  4. 物化视图不支持触发器、约束(除了唯一索引),不能当成业务主表来写
  5. 刷新会消耗大量cpu、io,业务高峰期不要执行刷新,尽量放到凌晨低峰
  6. with no data 创建的mv,直接select会报错:materialized view "xxx" has not been populated,需要先refresh
  7. 物化视图会占用磁盘空间,相当于一份冗余数据,要做好容量规划

六、适用场景 & 不适合场景

✅ 适合使用实体化视图:

  • 运营后台、bi报表、大屏统计,大量多表join、group by、聚合计算
  • 底层原始表数据量大,每次实时聚合很慢,查询频次很高
  • 数据不需要毫秒级实时,分钟/小时级延迟完全可以接受
  • 数据仓库、数仓分层中间层,dwd/dws层汇总表

❌ 不适合:

  • 需要实时和基表保持一致的数据(实时统计请用实时流、触发器、pg_stat_statements、普通视图)
  • 频繁增删改、要求低延迟的业务主数据
  • 结果集很小,实时查询本身就很快,没必要额外存储+维护刷新任务

七、进阶小技巧

  1. 分区物化视图:pg支持物化视图挂载分区表逻辑,适合按时间分区的统计报表
  2. 刷新的时候可以结合 pg_stat_activity 观察刷新会话,长耗时刷新可以监控
  3. 可以给物化视图设置 fillfactor,适合频繁并发刷新的场景,减少页分 裂
  4. pg15 新增了 refresh materialized view ... skip concurrently 等语法,增强控制

八、总结

postgresql 的实体化视图是一个非常好用的预计算快照方案,核心就是「用磁盘空间 + 定时刷新的延迟,换取查询速度」。
它和普通视图不是替代关系,是互补:

  • 简单查询、需要实时一致性 → 普通视图
  • 重聚合、高频查询、允许一定数据延迟 → 实体化视图

在做bi、数据看板、后台统计的时候,合理使用物化视图,可以大幅降低底层主库的计算压力,也是pg做轻量数仓非常经典的手段。

以上就是postgresql实体化视图(materialized view)的方法详解的详细内容,更多关于postgresql实体化视图的资料请关注代码网其它相关文章!

赞 (0)

相关文章:

版权声明:本文内容由互联网用户贡献,该文观点仅代表作者本人。本站仅提供信息存储服务,不拥有所有权,不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 2386932994@qq.com 举报,一经查实将立刻删除。

发表评论

验证码:
Copyright © 2017-2026  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com