物化视图

视图(view) 是已命名的查询，它提供了另一种在数据库表中显示数据的方式。视图是基于一个或多个表来定义的，这些表被称为基表。当你创建一个视图时，基本上是创建一个查询并为其分配一个名称，因此视图对于包装一个常用的复杂查询很有用。

请注意，普通视图不会存储任何数据，物化视图除外。在 PostgreSQL / Redshift 中可以创建一个特殊视图，称为物化视图，物理存储数据并定期从基表中刷新数据。

物化视图在很多情况下都有很多优点，例如更快地访问远程服务器的数据，数据缓存等等。主要用于需要快速访问数据且要频繁访问数据的场景，如数据仓库和商务智能应用中

本节我们将基于IMDB的复杂查询，为它创建物化视图，并介绍如何对物化视图进行刷新

创建物化视图

在前面我们执行过下面的join查询：

select
       title.title,
       title.language,
       title_basics.primarytitle,
       title_basics.startyear,
       title_basics.genres
from imdb.title
  join imdb.title_basics on title.titleid = title_basics.tconst
where title.language = 'en';

假设现在每天要多次执行这个查询(可能用于报表展示)，我们想把它做成物化视图，这样每次直接从视图里拿数据，会大大加快速度，并减少Redshift的工作负载。

执行create materialized view 命令，为该查询创建物化视图：

set search_path = imdb,public;  # 这样下面语句创建的物化视图会在imdb schema下面

create materialized view title_infos as
(select
       title.title,
       title.language,
       title_basics.primarytitle,
       title_basics.startyear,
       title_basics.genres
from imdb.title
  join imdb.title_basics on title.titleid = title_basics.tconst
where title.language = 'en');

创建物化视图完成后，可以从里面查询数据：

select * from title_infos order by startyear;

image-20221104170538112

刷新物化视图

物化视图是基于基表创建的，如果基表里的数据发生变动(增删改)，而物化视图没有被更新的话，就会查到旧的数据。物化视图的刷新有三种方式：

  1. 手动执行refresh命令刷新

  2. 自动刷新。在创建表的时候设置物化视图自动更新，这样每当基表的数据发生变化，Redshift在后台会自动更新对应的物化视图。在自动更新时，为了不影响集群的性能，Redshift会考虑很多因素，比如当前系统负载、进行刷新时需要占用的负载、物化视图多久被查一次…。Redshift会优先考虑查询语句而放弃刷新视图，所以物化视图的更新可能会有延迟，如果此时急着查，可以执行手动refresh命令。

  3. 做自动定时刷新，使用Redshift Schedular API定时更新，参考 https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-schedule-query.html

接下来我们将对上面的视图做手动刷新和自动刷新

对视图查询时，前两条数据是1874年：

image-20221104170538112

在基表里，把它更新成1875年：

image-20221104170706212

此时再查询物化视图，发现数据没有被更新：

image-20221104170737622

执行物化视图手动刷新：

refresh materialized view title_infos;

image-20221104170900013

刷新完成后，再次查询，此时查到的是最新数据：

image-20221104170935050

当然我们也可以使用自动刷新，这样每当基表发生变化时，物化视图都会自动更新：

create materialized view title_infos AUTO REFRESH YES
as (select
       title.title,
       title.language,
       title_basics.primarytitle,
       title_basics.startyear,
       title_basics.genres
from imdb.title
  join imdb.title_basics on title.titleid = title_basics.tconst
where title.language = 'en');

如何确认物化视图和基表数据一致

执行select * from stv_mv_info，里面会有物化视图的基本信息，比如是否开启auto_refresh， 数据是否和基表一致等：

image-20221106205048713