Skip to main content
 首页 » 数据库

PostgreSQL 物化视图教程2

2022年07月19日108unruledboy

上节我们简要介绍了物化视图,本文补充说明创建和修改物化视图的一些选项。

创建物化视图

在了解物化视图之前,我们首先导入一些示例数据作为计算的基础:

CREATE TABLE t_demo (grp int, data numeric); 
 
INSERT INTO t_demo SELECT 1, random() 
		FROM generate_series(1, 5000000); 
 
INSERT INTO t_demo SELECT 2, random() 
		FROM generate_series(1, 5000000); 

插入1千万行数据,分成两个组。要创建物化视图,首先查看其语法:

postgres=# \h create materialized view 
命令:       CREATE MATERIALIZED VIEW 
描述:       建立新的物化视图 
语法: 
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] 表名 
    [ (列名称 [, ...] ) ] 
    [ USING 方法 ] 
    [ WITH ( 存储参数 [=] [, ... ] ) ] 
    [ TABLESPACE 表空间的名称 ] 
    AS 查询 
    [ WITH [ NO ] DATA ] 

我们看到创建物化视图至少需要名称、基础查询以及一些参数,请看示例:

CREATE MATERIALIZED VIEW mat_view AS 
           SELECT   grp, avg(data), count(*) 
           FROM     t_demo 
           GROUP BY 1; 

我们对比物化视图和底层物理表:

postgres=# \d+ 
                                   关联列表 
 架构模式 |   名称    |   类型   |  拥有者  | Persistence |    大小    | 描述 
----------+-----------+----------+----------+-------------+------------+------ 
 public   | mat_view  | 物化视图 | postgres | permanent   | 16 kB      | 
 public   | t_demo    | 数据表   | postgres | permanent   | 423 MB     | 

差别为423 MB 对 16 KB,你可以如查询普通表一样查询物化视图:

postgres=# select * from mat_view; 
 grp |           avg            |  count 
-----+--------------------------+--------- 
   1 | 0.5000999643123602020241 | 5000000 
   2 | 0.5000497431804337268440 | 5000000 
(2 行记录) 

我们也可以对物化视图执行查询计划,请看示例:

postgres=# analyze; 
ANALYZE 
postgres=# explain SELECT * FROM mat_view; 
                       QUERY PLAN 
--------------------------------------------------------- 
 Seq Scan on mat_view  (cost=0.00..1.02 rows=2 width=27) 
(1 行记录) 

Using 选项

PostgreSQL还提供了其他重要特征,Using主要用于指定存储格式。目前PostgreSQL支持heap,未来可能会支持其他类型存储格式,如zheap, zedstore等。Using选项用于指定对特定需求最佳存储格式。

with 选项

该选项与create table一样,用于定义存储参数,如autovacuum、fillfactor等行为,举例:

CREATE MATERIALIZED VIEW mat_view_2  
           WITH (autovacuum_enabled = false) AS 
           SELECT   grp, avg(data), count(*)  
           FROM     t_demo  
           GROUP BY 1; 

一般情况物化视图与表一样,参数可以与表保持一致。

TABLESPACE选项

对于TABLESPACE关键字也是如此。在默认设置物化视图将存储在默认表空间$PGDATA中:

postgres=# show data_directory; 
 data_directory 
---------------- 
 E:/pg-data 
(1 行记录) 

show 命令可以查看数据存储路径。

DATA / NO DATA选项

创建物化视图默认包含数据,但使用NO DATA选项会不包含数据,仅定义表。但是如果没有数据则物化视图不能被查询,需要刷新才能使用。虽然该选项很少用到,但为了完整说明这里还是进行简要说明。

修改物化视图

有时物化视图创建后需要修改,如修改字段名称可以避免重新创建,首先我们看看之前创建的物化视图:

postgres=# \d mat_view 
         物化视图 "public.mat_view" 
 栏位  |  类型   | 校对规则 | 可空的 | 预设 
-------+---------+----------+--------+------ 
 grp   | integer |          |        | 
 avg   | numeric |          |        | 
 count | bigint  |          |        | 

修改物化视图使用 ALTER MATERIALIZED VIEW命令,完整语法如下:

postgres=# \h ALTER MATERIALIZED VIEW 
命令:       ALTER MATERIALIZED VIEW 
描述:       更改物化视图的定义 
语法: 
ALTER MATERIALIZED VIEW [ IF EXISTS ] 名称 
    操作 [, ... ] 
ALTER MATERIALIZED VIEW 名称 
    [ NO ] DEPENDS ON EXTENSION extension_name(扩展名) 
ALTER MATERIALIZED VIEW [ IF EXISTS ] 名称 
    RENAME [ COLUMN ] 列名称 TO new_column_name(新列名) 
ALTER MATERIALIZED VIEW [ IF EXISTS ] 名称 
    RENAME TO 新的名称 
ALTER MATERIALIZED VIEW [ IF EXISTS ] 名称 
    SET SCHEMA 新的模式 
ALTER MATERIALIZED VIEW ALL IN TABLESPACE 名称 [ OWNED BY 角色名称 [, ... ] ] 
    SET TABLESPACE 新的表空间 [ NOWAIT ] 
 
操作可以是下列选项之一 
 
    ALTER [ COLUMN ] 列名称 SET STATISTICS 整数 
    ALTER [ COLUMN ] 列名称 SET ( 属性选项 =[, ... ] ) 
    ALTER [ COLUMN ] 列名称 RESET ( 属性选项 [, ... ] ) 
    ALTER [ COLUMN ] 列名称 SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } 
    CLUSTER ON 索引名称 
    SET WITHOUT CLUSTER 
    SET ( 存储参数 [=] [, ... ] ) 
    RESET ( 存储参数 [, ... ] ) 
    OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER } 

下面先修改列名称:

postgres=# ALTER MATERIALIZED VIEW mat_view 
postgres-#      RENAME COLUMN avg TO average; 
ALTER MATERIALIZED VIEW 
postgres=# \d mat_view 
          物化视图 "public.mat_view" 
  栏位   |  类型   | 校对规则 | 可空的 | 预设 
---------+---------+----------+--------+------ 
 grp     | integer |          |        | 
 average | numeric |          |        | 
 count   | bigint  |          |        | 

为物化视图建立索引是一个好习惯,以确保快速查询。索引规则与表相同,简单地索引记录,这些记录可以是多样化的,并且在读取物化视图时提供了更好的性能。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/120019853