PostgreSQL视图和物化视图_pg 视图转物化视图

---

我们常用说的视图一般是指 view，即普通视图；而物化视图则是materialized view（materialized 使物质化，使具体化（materialize 的过去式和过去分词））。两者都是视图，但是名称不一样，说明两者还有有所差异的。下面就来简单介绍一下PostgreSQL中的视图和物化视图的差异。

1.视图

对标实体表，视图其实是个虚拟的表，实际上一个SQL拼接而成的虚拟表（假表），可能是有一个表或者多个表、视图关联而成的复杂的SQL，而这个SQL一般都是select语句（当然也可能是 update、delete 等的语句，但是可能会有诸多限制，而且用视图进行这些操作也不安全，一般也不会这么用，这些不在本文讨论范围内。）。而视图的用途常常用于展示一些用户需要的信息，隐藏一些敏感或者是不重要的数据，汇总一些关键数据展示给用户。

操作：可以像表一样查询视图的字段，也可以当做一个表进行关联查询。
查询底层实现：通过创建视图的语句进行查询，就是每次实时查询底层的表，数据都是实时的。
索引：走表的索引。

2.物化视图

基本功能和视图类型。对标实体表和普通视图，会比普通视图更像一个表。上面说到视图是一个虚拟的表，这个物化视图其实是一个物理表，它可以用自己的索引。

操作：可以像表一样查询视图的字段，也可以当做一个表进行关联查询。可以创建索引。
查询底层实现：创建后就真的被当做是表了，即结果都物化（固化）成了一个表，假如创建语句的底层表数据有变化，也不会影响到这个物化视图的数据，除非手动进行刷新。就是每次查询的都是固化的数据，不是实时的数据。
索引：走自己的索引，可以创建索引。

3.视图和物化视图的对比

4.示例：查询系统的用户表

基础查询如下，后面我们会使用这个SQL来构造视图和物化视图。
（简单介绍下pg_class ：pgsql里面的一个内置系统表，是一个统计或者说是汇总的数据表。class就是类、对象，即pgsql里面的对象都会在这里面记录，比如实体表、视图、物化视图、索引、toast表等，详细信息可以查看官方文档。）

select * from pg_class where relnamespace != '11' and relnamespace != '13887'  and relkind = 'r';

-- 创建一个表，备用
create table Sheet4(
	id int8,
	name varchar(100)
);

-- 查询该表的基础信息（元数据信息）
select * from pg_class where relnamespace != '11' and relnamespace != '13887'  and relkind = 'r' and  relname = 'Sheet4';
1
2
3
4
5
6
7
8
9
10

4.1 视图

4.1.1 创建视图

CREATE VIEW "public"."v_user_tab"
AS
select * from pg_class where relnamespace != '11' and relnamespace != '13887'  and relkind = 'r';
1
2
3

4.1.2 使用视图

-- 普通查询
select * from v_user_tab;
select * from v_user_tab where relname = 'Sheet4';
1
2
3

-- 关联查询，找命名空间是啥（public默认的）
select * from pg_namespace;
select ns.nspname,vt.* from v_user_tab vt left join  pg_namespace ns on vt.relnamespace = ns.oid;
select ns.nspname,vt.* from v_user_tab vt left join  pg_namespace ns on vt.relnamespace = ns.oid where relname = 'Sheet4';
1
2
3
4

4.1.3 查看视图的执行计划

查看索引使用情况：上面提到了，实际上走的是底层的查询，索引走的也是原来基础表上的索引。下面我们来看下是不是。（注意一下，因为pgsql规则器有自己的考量，有时候即使有索引也不一定会走，和具体的表记录和查询语句有关系。因此可能explain结果和本文可能会有点不一致。）

通过计划可以看到这个表名的简单SQL，使用了系统表 pg_class 上的索引 pg_class_relname_nsp_index 。

explain select * from v_user_tab where relname = 'Sheet4';

Index Scan using pg_class_relname_nsp_index on pg_class  (cost=0.28..8.31 rows=1 width=271)
  Index Cond: (relname = 'Sheet4'::name)
  Filter: ((relnamespace <> '11'::oid) AND (relnamespace <> '13887'::oid) AND (relkind = 'r'::"char"))
1
2
3
4
5

下面这个使用了，两个索引：pg_class.pg_class_relname_nsp_index 和pg_namespace.pg_namespace_oid_index 。

explain select ns.nspname,vt.* from v_user_tab vt left join  pg_namespace ns on vt.relnamespace = ns.oid where relname = 'Sheet4';

Merge Left Join  (cost=0.43..12.02 rows=1 width=335)
  Merge Cond: (pg_class.relnamespace = ns.oid)
  ->  Index Scan using pg_class_relname_nsp_index on pg_class  (cost=0.28..8.31 rows=1 width=271)
        Index Cond: (relname = 'Sheet4'::name)
        Filter: ((relnamespace <> '11'::oid) AND (relnamespace <> '13887'::oid) AND (relkind = 'r'::"char"))
  ->  Index Scan using pg_namespace_oid_index on pg_namespace ns  (cost=0.14..18.81 rows=244 width=68)
1
2
3
4
5
6
7
8

4.1.4 视图的时效性

创建一个表，看能不能在这个视图里面查到。

create table testv1(
	id int8,
	name varchar(100)
);
1
2
3
4

-- 有数据，说明数据是实时的。
select * from v_user_tab where relname = 'testv1';
1
2

4.1.5 尝试为视图创建索引

create index idx_v_user_tab_relname on v_user_tab using btree (relname);
-- 会报错：> 错误:  "v_user_tab" 不是一个表或物化视图
1
2

4.1.6 删除视图

drop view v_user_tab;
1

4.2 物化视图

4.2.1 创建物化视图

CREATE MATERIALIZED VIEW "public"."mv_user_tab"
AS
select * from pg_class where relnamespace != '11' and relnamespace != '13887'  and relkind = 'r';
1
2
3

4.2.2 使用物化视图

-- 普通查询
select * from mv_user_tab;
select * from mv_user_tab where relname = 'Sheet4';
1
2
3

-- 关联查询
select * from pg_namespace;
select ns.nspname,vt.* from mv_user_tab vt left join  pg_namespace ns on vt.relnamespace = ns.oid;
select ns.nspname,vt.* from mv_user_tab vt left join  pg_namespace ns on vt.relnamespace = ns.oid where relname = 'Sheet4';
1
2
3
4

基础使用，到目前为止好像都没有差异。

4.2.3 查看物化视图的执行计划

explain select * from mv_user_tab where relname = 'Sheet4';

Seq Scan on mv_user_tab  (cost=0.00..55.53 rows=1 width=260)
  Filter: (relname = 'Sheet4'::name)	
1
2
3
4

没有走索引，根据上面的查询我们可以知道其实 pg_class.relname 是有索引的，但是这里没有用到。
其实不是这样的，这里和视图就有点不一样，当它创建后，我们应该把物化视图当做一个独立的个体（独立的表），它有索引，它只走自己的索引（等下我们尝试为这个字段创建索引）。

explain select ns.nspname,vt.* from mv_user_tab vt left join  pg_namespace ns on vt.relnamespace = ns.oid where relname = 'Sheet4';

Merge Right Join  (cost=55.76..55.86 rows=1 width=324)
  Merge Cond: (ns.oid = vt.relnamespace)
  ->  Index Scan using pg_namespace_oid_index on pg_namespace ns  (cost=0.14..18.81 rows=244 width=68)
  ->  Sort  (cost=55.54..55.54 rows=1 width=260)
        Sort Key: vt.relnamespace
        ->  Seq Scan on mv_user_tab vt  (cost=0.00..55.53 rows=1 width=260)
              Filter: (relname = 'Sheet4'::name)
1
2
3
4
5
6
7
8
9

这个也没有走索引，使用的是全表扫描。

4.2.4 视图的时效性

创建一个表，看能不能在这个物化视图里面查到。

create table testv2(
	id int8,
	name varchar(100)
);
1
2
3
4

-- 没有数据，说明数据不是实时的。
select * from mv_user_tab where relname = 'testv2';
1
2

我们刷新一下物化视图

refresh materialized view  mv_user_tab;
1

-- 刷新后，我们就可以查到刚才那个表了
select * from mv_user_tab where relname = 'testv2';
1
2

4.2.5 尝试为物化视图创建索引

create index idx_mv_user_tab_relname on mv_user_tab using btree (relname);
1

这里我们再执行这个语句，发现是可以走索引的。

explain select * from mv_user_tab where relname = 'Sheet4';

Index Scan using idx_mv_user_tab_relname on mv_user_tab  (cost=0.28..8.29 rows=1 width=260)
  Index Cond: (relname = 'Sheet4'::name)

--删除索引
DROP index idx_mv_user_tab_relname;
1
2
3
4
5
6
7

4.2.6 删除物化视图

drop materialized view mv_user_tab;
1

注意删除物化视图会同步删除对应的索引（和删除表会删除表对应的索引是一样的道理），但是刷新物化视图不会。

4.3.物化视图延伸

4.3.1并发刷新物化视图

refresh (全量刷新)默认会为视图加上排它锁，会阻塞查询。

生产上，我们为了安全，我们一般采用“并发的方式”刷新物化视图。并发刷新的意思就是我刷新的时候，你还可以查询对应的数据。（注意：如果逻辑表复制、数据比较多，并发刷新可能需要时间比较长，而且只能同时刷新一个物化视图，但是整理来说不影响。）

-- 比如这样
refresh materialized view concurrently mv_user_tab;
> 错误:  不能同时刷新物化视图 "public.mv_user_tab"
> HINT:  在物化视图的一个或多个列上创建不带WHERE子句的唯一索引.
1
2
3
4

（因为还没研究过对应文档）个人理解，并发刷新就是根据唯一索引判断是新增还是更新数据到物化视图里面，因此需要先有唯一索引。

-- 创建唯一索引
create unique index idx_mv_user_tab_oid on mv_user_tab using btree (oid);
-- 再次尝试并发刷新
refresh materialized view concurrently mv_user_tab;
1
2
3
4

4.3.2尝试修改物化视图的数据

尝试删除数据

select * from mv_user_tab where relname = 'testv2';

DELETE from mv_user_tab where relname = 'testv2';

> 错误:  不能改变物化视图 "mv_user_tab"

1
2
3
4
5
6

尝试更新物化视图

update  mv_user_tab set relname = 'testv21' where relname = 'testv2';

> 错误:  不能改变物化视图 "mv_user_tab"
1
2
3

经过尝试：发现除了刷新操作外，物化视图的数据是不能进行修改和删除操作的。