0


【postgresql初级使用】可以存储数据的视图-物化视图,加速大数据下的查询分析

物化视图

专栏内容

  • postgresql使用入门基础
  • 手写数据库toadb
  • 并发编程

个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.

文章目录

概述


本节主要分享物化视图(materialize view)的基础知识,分为原理机制,创建,数据刷新,以及删除等小节。

在原理机制一节,会与普通view对比不同点,它们执行机制的差异;之后的各小节结合案例分享语法与使用场景。

原理机制


先来看看普通的view ,它只是记录一条view定义时的查询语句,从view查询的SQL,会被重写,用view定义的语句进行替换,然后执行查询,实际是从数据表中查询。

执行流程 如下:

select * from vw_employee;

->

查询优化器

->

重写为 select * from (select * from employee) as vw_employee;

->

执行器

->

返回结果

每次从view 查询时,都会执行一次view定义的查询语句,view 本身不会存储数据。

下面来看看materialize view, 它在定义时会将数据保存一份,它有自己存储数据,但是它不会自动与主表进行数据同步,也就是主表的数据变化了,物化视图中的备份数据不会变化,需要手动进行同步。

执行流程如下:

select * from mvw_emplyee;

->

查询优化器

->

执行器 

->

返回结果

如果对某相view访问很频繁时,结果集只生成一次,后面都是直接查结果集就可以,会大大节省时间,尤其对于大数据分析,每次结果集的生成都需要数秒,甚至数分钟,每次都进行结果生成,那是不可想象的一件事。

因为物化视图会保存源表的数据,所以它不能被修改,避免数据的分叉,这不像前一节介绍的updatable view。

下面我们来介绍一下物化视图的使用。

materialize view 创建


语法说明

物化视图的创建语法如下:

CREATE MATERIALIZED VIEW view_name
AS
query_sql
WITH[No]DATA;

物化视图与普通视图的创建语法类似,区别如下:

  • 这里使用关键字materialized来指示视图的类型为物化视图;
  • 定义的最后使用with datawith no data来指定创建后的物化视图,是否需要同步数据; 如果不指定,默认为前者,在创建完后,会同步主表的数据; 如果选择with no data,则创建完后没有数据,并且不能查询,必须先同步数据;

使用案例

下面我们分享一个案例,它是一个大数据分析平台,生产库每天会产生上千万条的订单数据,而分析库每天晚上定时生成分析报表,来指导第二天的库存备货以及调货运输。

初始化数据

  • 创建产品与订单表。
-- 创建产品表  CREATETABLE products (  
    product_id INTPRIMARYKEY,  
    product_name VARCHAR(255)NOTNULL,  
    price DECIMAL(10,2)NOTNULL,  
    category VARCHAR(255));-- 创建订单表  CREATETABLE orders (  
    order_id INTPRIMARYKEY,  
    product_id INT,  
    quantity INTNOTNULL,  
    region VARCHAR(255)NOTNULL,  
    order_date DATENOTNULL,FOREIGNKEY(product_id)REFERENCES Products(product_id));
  • 这里使用一个生成随机字符串的存储过程,来辅助我们生成模拟数据,它的定义如下:
CREATEORREPLACEFUNCTION generate_random_string(length INTEGER)RETURNSVARCHARLANGUAGE plpgsql
AS $$
DECLARE  
    result VARCHAR(255) :='';  
    chars TEXT[] := ARRAY['a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'];BEGINFOR i IN1..length LOOP  
        result := result || chars[1+(random()*(array_length(chars,1)-2))::int];ENDLOOP;RETURN result;END $$;
  • 生成10万条商品的数据,同时生成100万条的订单数据。

生成商品数据;

INSERTINTO products (product_id,product_name,price,category )SELECT
    id, generate_random_string(10),-- 假设商品名字长度为 10(random()*1000)::numeric(10,2),-- 随机价格在 0 到 1000 之间'Category'||(random()*10)::int-- 假设有 10 个不同的商品类别FROM generate_series(1,100000)as id;

生成订单数据,其中商品的ID在10万以内随机生成。

INSERTINTO orders (order_id,product_id,quantity,region,order_date)SELECT
    id,1+random()*99999,(random()*10000)::int,'region'||(random()*22)::int,current_date- floor((random()*360))::int rand_date
FROM generate_series(1,1000000)as id;

物化视图使用

报表中的一个指标项查询SQL如下:

-- 查询每个区域的销量SELECT region,SUM(quantity)AS total_sales
    FROM orders
    GROUPBY region;

我们开启psql中的时间统计,看看执行一次统计花费的时间。

postgres=# \timing on
Timing is on.
postgres=# SELECT region, SUM(quantity) AS total_sales
postgres-#     FROM orders
postgres-#     GROUP BY region;
  region  | total_sales
----------+-------------
 region0  |114114062
 region1  |226243565
 region10 |227494628
 region11 |227777263
 region12 |228053045
 region13 |228127572
 region14 |227847192
 region15 |226668865
 region16 |227167622
 region17 |226283956
 region18 |227486573
 region19 |226798204
 region2  |227236551
 region20 |229110138
 region21 |228462753
 region22 |112870077
 region3  |225945462
 region4  |230059348
 region5  |226837565
 region6  |228821897
 region7  |226570562
 region8  |227755978
 region9  |225983661(23 rows)

Time: 50.188 ms

看到结果生成了23条记录,花费了50ms时间。

之前我们使用的是普通视图或者是CTE语句,报表的生成需要花费整整一个晚上,因为每次执行都需要重新进行结果的生成。

后来我们将这个指标项创建为物化视图的形式,只需要同步数据的时间就可以了,不仅节省了时间,而且不再长时间占用计算资源。

下面分享给大家这个方法,先来创建一个物化视图:

postgres=# create materialized view mvw_regiion_sales AS SELECT region, SUM(quantity) AS total_sales
postgres-#     FROM orders
postgres-#     GROUP BY region;
SELECT 23
Time: 52.976 ms

创建物化视图时,没有指定

with no data

时,默认会同步数据到物化视图中,可以看到花费了52ms,与上面查询的时间相当。

此时我们查看各区域的销量时,就可以从物化视图中来查看。

postgres=# select * from mvw_regiion_sales ;
  region  | total_sales
----------+-------------
 region0  |114114062
 region1  |226243565
 region10 |227494628
 region11 |227777263
 region12 |228053045
 region13 |228127572
 region14 |227847192
 region15 |226668865
 region16 |227167622
 region17 |226283956
 region18 |227486573
 region19 |226798204
 region2  |227236551
 region20 |229110138
 region21 |228462753
 region22 |112870077
 region3  |225945462
 region4  |230059348
 region5  |226837565
 region6  |228821897
 region7  |226570562
 region8  |227755978
 region9  |225983661(23 rows)

Time: 0.260 ms

多次查看各区域的销量,花费时间都不到1ms,是原来的五十分之一,大大节省了时间和资源。

materialize view 刷新数据


当白天运营时,商品表和订单表都可能发生变化,而区域销量的物化视图中的数据如何更新呢?

在订单表中新增一条订单记录,新增区域

region100

,这是一个新区域产生了订单。

postgres=# insert into orders values(1000001, 1000,10000,'region100','2023-12-03');
INSERT 01
Time: 8.132 ms

查看物化视图,还是前一天的值,它不会自动更新数据。

postgres=# select * from mvw_regiion_sales where region='region100';
 region | total_sales
--------+-------------
(0 rows)

Time: 0.259 ms

对物化视图中的数据进行刷新,就会同步源表中的数据。

postgres=# refresh materialized view mvw_regiion_sales ;
REFRESH MATERIALIZED VIEW
Time: 54.042 ms

可以看到执行时间大概为54ms。

postgres=# select * from mvw_regiion_sales where region='region100';
  region   | total_sales
-----------+-------------
 region100 |10000(1row)Time: 0.265 ms

再次查询时,已经有了新数据。

在执行

refresh materialize view

命令时,会从源表中加载数据到物化视图中,此时会对源表进行加锁,使得源表不能进行操作。

如果要并发执行物化视图的刷新,可以增加关键字

concurrently

,执行命令

refresh materialize view concurrently

。当前并发执行的前提是,当前物化视图必须有唯一性索引列。

materialize view 删除


删除物化视图的SQL语法,类似与普通view。

drop materialize view view_name;

如果有关联的视图,可以增加关键字

cascade

,进行级联删除。

总结


在大数据分析场景下,物化视图是一个非常高效的记录中间报表结果的方法,避免多次重复执行。

结尾


非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!

作者邮箱:study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出,互相学习。

注:未经同意,不得转载!


本文转载自: https://blog.csdn.net/senllang/article/details/138508718
版权归原作者 韩楚风 所有, 如有侵权,请联系我们删除。

“【postgresql初级使用】可以存储数据的视图-物化视图,加速大数据下的查询分析”的评论:

还没有评论