梧桐数据库物化视图的创建及使用介绍

一、前言

  提升查询性能一直是数据库的核心设计理念，物化视图就是其中一个项重要工具，目前梧桐数据库支持创建物化视图，本文简单介绍了梧桐数据库的物化视图创建与使用，供大家参考。

二、物化视图介绍

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

在梧桐数据库中创建物化视图的基本语法如下

create materialized view 视图名称 as select 列名称 from 表名称 where 条件;

三、物化视图使用及优缺点分析

下面通过实际创建一个物化视图，通过执行时长对比总结其优缺点：

普通查询语句执行：

select to_char(create_time,'yyyymm') opne_month,count(cust_id) as total_cnt

from public.dw_***_***_dm

group by to_char(create_time,'yyyymm');

• 执行查询语句时长：440571ms

创建物化视图

create materialized view public.dw_cust_open_total_view as
select to_char(create_time,'yyyymm') opne_month
,count(cust_id) as total_cnt
from public.dw_***_***_dm
group by to_char(create_time,'yyyymm');

• 执行时长：320988ms

查询物化视图：

select * from public.dw_cust_open_total_view ;

• 执行时长：11ms

更新视图数据：

refresh materialized view public.dw_cust_open_total_view ;

• 执行时长：56898ms

通过以上实际操作总结物化视图的优缺点：

优点：预先计算并存储查询结果，减少查询时的计算开销，加快查询速度；

缺点：默认手动刷新视图数据，需要存储空间；

四、物化视图刷新策略

除手动刷新视图还可以通过触发器刷新，具体操作如下：

  创建视图刷新函数，通过调度工具触发视图刷新；

创建视图刷新函数：

CREATE OR REPLACE function public.refresh_materialized_view_func() 
RETURNS record AS

$BODY$

DECLARE

BEGIN

    REFRESH MATERIALIZED VIEW CONCURRENTLY  public.dw_cust_open_total_view;

    RETURN NULL;

END;
$BODY$
 LANGUAGE plpgsql;

五、修改物化视图

物化视图也可类似数据表修改视图名和字段名：

修改视图名

alter materialized view 原视图名 rename  to 新视图名;

修改字段名

alter materialized view 视图名 rename 原字段名 to 新字段名;

• 修改视图名称：

alter materialized view public.dw_cust_open_total_view rename to dw_cust_open_total_view_1;

• 修改字段名称

alter materialized view public.dw_cust_open_total_view_1 rename total_cnt to total_cnt_1;

• 删除物化视图

drop materialized view 物化视图名称;

总结

  物化视图通过预先计算并存储复杂查询的结果，可以显著提高数据检索的速度，以上简单介绍了梧桐数据库物化视图创建、查询、修改、删除、数据更新的相关语法，与SQL脚本查询对比的优缺点，在SQL查询优化、模型调优提速等场景中可以参考使用。