PostgreSQL视图和物化视图

卑微的程序猿成长史-MC

已于 2023-12-07 16:40:01 修改

阅读量6.5k

点赞数 5

文章标签： postgresql 数据库

于 2022-07-04 14:11:03 首次发布

本文链接：https://blog.csdn.net/weixin_45706122/article/details/125596690

版权

由于每天工作忙碌，好久没更新了，今天更新一下，讲一讲视图和物化视图的概念以及区别吧。

1. 视图

1.1 PostgreSQL视图概述

PostgreSQL中的视图和MySQL中的视图是一样的，在查询的时候进行扫描基础表的操作。
视图表示基础表对应的虚拟表，一个视图对应着一个SQL查询语句。
做查询时，把视图的名字当作表名使用，PostgreSQL内部会使用对应的SQL查询语句替换视图名称做查询。由此可见，视图的数据并未持久化（所谓持久化就是将数据实实在在的存储起来）在某一张表中。
创建视图语句：

CREATE OR REPLACE VIEW testview as selectstatement;

pgAdmin4视图截图，被框选住的就是我手动创建的视图
在这里插入图片描述

1.2 标题 PostgreSQL视图在系统表里面的注册信息

Postgres在创建视图时会将对应的字段注册到pg_attribute,表类型注册到pg_class, 对应的约束也会注册到pg_constraint等表中；

注意： Postgres在注册字段时是不会有“tableoid” 、“cmax”、“xmax”、“cmin”、“xmin”、“ctid”系统字段，如下截图在pgAdmin4客户端执行语句的查询结果。
在这里插入图片描述
以上查询结果证实视图只是对应查询语句对应的一个虚拟表，查询该视图结果实际上就是去对应的查询语句表里面去筛选。

1.3 PostgreSQL所支持功能

支持规则和触发器；
不支持索引；
可通过创建触发器或rule实现视图更新；
Rule创建链接：https://blog.csdn.net/pg_hgdb/article/details/78668476
如下图所示：

1.4常用视图系统表介绍

pg_indexes：pg索引表；
pg_matviews：物化视图表，里面包含所有物化视图表；
pg_rules：规则表；
pg_sequences：自增序列ID表； pg_tables：筛选基础表和分区表
pg_user：筛选用户视图表；
pg_views：筛选对饮数据库中所有视图表；

2. 物化视图

2.1 物化视图概述

物化视图是PostgreSQL9.3版本才支持的一个功能，物化视图的使用规则和视图是一样的，和视图不同的是，物化视图将结果集持久化在表中；
一个物化视图对应一个SQL语句，查询时去对应的结果集表查询（跟视图查询方式有些区别）；
创建物化视图语句：

CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

2.2 物化视图系统表信息注册

物化视图在PostgreSQL系统目录的信息和基础表信息完全一样。对于解析器来说，物化视图类似基础表和视图一样的关系型表。直接从物化视图里面查询数据时也和基础表和视图一样的形式返回。
PG创建物化视图会注册 “tableoid” 、“cmax”、“xmax”、“cmin”、“xmin”、“ctid”系统字段，如下图所示；
在这里插入图片描述
由此可见，物化视图是将数据真正的存储起来；

2.3 物化视图所支持功能

物化视图无法对表进行插入更新删除操作;
物化视图有系统表字段信息；
支持创建空间索引和属性字段索引，也可以在物化视图上创建索引（GIST、BRIN、SP-GiST、以及字段索引等）；
物化视图更新1）：全量刷新，PostgreSQL9.3版本只有该刷新规则;

REFRESH MATERIALIZED VIEW mymatview

物化视图更新2）：增量刷新吗，PostgreSQL9.4版本增加的一个刷新规则;

 REFRESH  MATERIALIZED VIEW CONCURRENTLY [ MATERIALIZED  view table name]

2.4 增量更新与全量更新

全量更新： 直接去基础表里面查询数据，刷新过程中会对该物化视图的所有的select操作阻塞，但刷新效率快。
增量刷新： 刷新要具备有唯一字段索引，将基础表查询出来的数据和现在物化视图表进行对比，填充差量数据，但刷新过程中不会对该物化视图的select操作进行阻塞，但刷新比全量更新慢。

2.5 物化视图其他用途

物化视图的另外一种用法就是允许通过外部数据包裹器快速的访问来自远程系统的数据。下面是一个使用 file_fdw 的简单示例，其中包含时间，但由于这是在本地系统上使用缓存，因此与访问远程系统相比，性能差异通常会比此处显示的要大。但是外部数据表file_fdw是不支持索引的，这个优势也不太适用于其他类型的外部表数据访问。所以可以基于外部数据表建立物化视图创建索引提高查询能力。
在这里插入图片描述