09-Materialized view

Materialized view

基础使用

物化视图是包括一个查询结果的数据库对象，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。

物化视图和视图类似，反映的是某个查询的结果，但是和视图仅保存SQL定义不同，物化视图本身会存储数据，因此是物化了的视图。

可以认为就是一张新的表.但是物化视图不允许手动修改，比如(insert,update)，只能使用专门的同步操作。

相比普通的视图。不需要每次执行复杂的SQL，每次查询的都是真实的物理存储的本地的表。物化视图会脱离原来的表结构，持久化到本地。

缺点就是同步问题，更新的频率不容易确定。不够频繁，数据存在延迟问题；过于频繁，压力太大。

创建物化视图语法(与普通视图创建一致):

Command:     CREATE MATERIALIZED VIEW
Description: define a new materialized view
Syntax:
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

我们使用一个student表。

select * from student;

 student_id | name 
------------+------
          2 | bbb
          3 | ccc
(2 rows)

创建物化视图

create materialized view mv_student as (select student_id from student);
SELECT 2

我们可以查看物化视图的信息：

postgres=# \dm
             List of materialized views
 Schema |    Name    |       Type        |  Owner   
--------+------------+-------------------+----------
 public | mv_student | materialized view | postgres
(1 row)

postgres=# \dmSx+
List of materialized views
-[ RECORD 1 ]-+------------------
Schema        | public
Name          | mv_student
Type          | materialized view
Owner         | postgres
Persistence   | permanent
Access method | heap
Size          | 16 kB
Description   | 

我们可以对这个物化视图进行查询：

postgres=# select * from mv_student;
 student_id 
------------
          2
          3
(2 rows)

全量更新&增量更新

特性	全量更新	增量更新
数据量	处理全部数据	只处理变化的数据
资源消耗	高（CPU、内存、I/O）	低
执行时间	长（与数据量成正比）	短（与变化量成正比）
实现复杂度	简单	复杂
数据一致性	强一致（快照）	最终一致
适用场景	小数据量、每日报表	大数据量、实时更新

全量更新

postgres=# \help refresh
Command:     REFRESH MATERIALIZED VIEW
Description: replace the contents of a materialized view
Syntax:
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

postgres=# select * from mv_student;
 student_id 
------------
          2
          3
(2 rows)

postgres=# insert into student values(5);
INSERT 0 1
postgres=# insert into student values(6);
INSERT 0 1
postgres=# select * from student;
 student_id | name 
------------+------
          2 | bbb
          3 | ccc
          5 | 
          6 | 
(4 rows)

postgres=# select * from mv_student;
 student_id 
------------
          2
          3
(2 rows)

postgres=# refresh materialized view mv_student;
REFRESH MATERIALIZED VIEW
postgres=# select * from mv_student;
 student_id 
------------
          2
          3
          5
          6
(4 rows)

增量更新

语法

refresh materialized view concurrently mv_student;

因为物化视图没有唯一索引，所以直接执行会报错，因为它不知道按照哪个字段作为参考增量更新。

所以我们需要手动给物化视图增加索引

create unique index unique_index_mv_student on mv_student(student_id);

我们修改原视图，之后执行增量更新，发现信息同步了。

postgres=# insert into student values(9999);
INSERT 0 1
postgres=# insert into student values(999967867);
INSERT 0 1
postgres=# refresh materialized view concurrently  mv_student;
REFRESH MATERIALIZED VIEW
postgres=# select * from mv_student;
 student_id 
------------
          2
          3
          5
          6
       9999
  999967867
(6 rows)

现在思考一个问题，我们给得索引是student_id，也就是参考这个值进行增量更新，那我update原表的某个数据，再执行增量更新，对应的物化视图上的这个数据会不会变呢？

update student set name = 'hhh' where id = 5;
-- 原本的表太简单没有添加其他的字段，这里补充name作为演示

实际上，我们再次查询物化视图，这个数据是会改变的！

可能会疑惑，不是根据student_id作为参考进行同步的吗，为什么这个数据也会变呢？

实际上每条数据都有一个版本号，比如原来的数据版本号是101，可能修改之后成了102.同步的时候检测到不同，就进行了更新。