1. 云图网首页
  2. 技术专区
  3. 数据库

Waiting for PostgreSQL9.3：增加物化视图 (MATERIALIZED VIEW)

• 数据库

今天在访问 depesz 博客时，发现了一个令人兴奋的消息，在未来的 PostgreSQL 9.3 版本中将带来物化视图，很多 PostgreSQL 爱好者对这个功能期待已久，闲话少说，先体验一下。

物化视图创建语法

1
2
3
4
5
6
7
8
9
 
Command:   CREATE MATERIALIZED VIEW
Description: define a new materialized view
Syntax:
CREATE [ UNLOGGED ] MATERIALIZED VIEW table_name
  [ (column_name [, ...] ) ]
  [ WITH ( storage_parameter [= value] [, ... ] ) ]
  [ TABLESPACE tablespace_name ]
  AS query
  [ WITH [ NO ] DATA ]

创建物化视图

1.1 创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
francs=# select version();
                        version                         
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.3devel on i686-pc-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 32-bit
(1 row)
  
francs=> create table test_1 (id int4,name character varying(32));
CREATE TABLE
francs=> insert into test_1 select generate_series(1,10),generate_series(1,10)||'a';
INSERT 0 10
  
francs=> select * from test_1;
id | name
----+------
 1 | 1a
 2 | 2a
 3 | 3a
 4 | 4a
 5 | 5a
 6 | 6a
 7 | 7a
 8 | 8a
 9 | 9a
10 | 10a
(10 rows)

1,2 创建普通视图和物化视图

1
2
3
4
5
 
francs=> create view v_test_1 as select * from test_1 where pg_sleep(5) is null;
CREATE VIEW
  
francs=> create materialized view mv_test_1 as select * from test_1 where id > 5;
SELECT 5

1.3 查看PLAN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
francs=> explain analyze select * From v_test_1;
                      QUERY PLAN                      
----------------------------------------------------------------------------------------------------
Seq Scan on test_1 (cost=0.00..18.75 rows=233 width=86) (actual time=0.032..0.041 rows=5 loops=1)
 Filter: (id > 5)
 Rows Removed by Filter: 5
Total runtime: 0.153 ms
(4 rows)
  
francs=> explain analyze select * from mv_test_1;
                       QUERY PLAN                       
-------------------------------------------------------------------------------------------------------
Seq Scan on mv_test_1 (cost=0.00..17.00 rows=700 width=86) (actual time=0.222..0.231 rows=5 loops=1)
Total runtime: 0.296 ms
(2 rows)

备注：上面看出访问普通视图时，依然是访问基表; 而访问物化视图时，不需要访问基表，而是直接从物化视图读数据。

1.4 查看大小

1
2
3
4
5
6
7
8
9
10
11
12
13
 
francs=> /dm+
             List of relations
Schema |  Name  |    Type    | Owner | Size | Description
--------+-----------+-------------------+--------+-------+-------------
francs | mv_test_1 | materialized view | francs | 16 kB |
(1 row)
  
francs=> /dv+
          List of relations
Schema |  Name  | Type | Owner | Size  | Description
--------+----------+------+--------+---------+-------------
francs | v_test_1 | view | francs | 0 bytes |
(1 row)

备注: /dm 元子命令显示物化视图大小，mv_test_1 大小为 16 k，这也说明了物化视图直接存储数据。

刷新物化视图

2.1 物化视图刷新语法

1
2
3
4
5
6
 
francs=> /h refresh materialized view
Command:   REFRESH MATERIALIZED VIEW
Description: replace the contents of a materialized view
Syntax:
REFRESH MATERIALIZED VIEW name
  [ WITH [ NO ] DATA ]

2.2 刷新前

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
francs=> select * from mv_test_1;
id | name
----+------
 6 | 6a
 7 | 7a
 8 | 8a
 9 | 9a
10 | 10a
(5 rows)
  
francs=> delete from test_1 where id=10;
DELETE 1
  
francs=> select * from mv_test_1;
id | name
----+------
 6 | 6a
 7 | 7a
 8 | 8a
 9 | 9a
10 | 10a
(5 rows)

2.3 刷新物化视图

1
2
 
francs=> refresh materialized view mv_test_1 ;
REFRESH MATERIALIZED VIEW

2.4 再次查看物化视图

1
2
3
4
5
6
7
8
 
francs=> select * from mv_test_1;
id | name
----+------
 6 | 6a
 7 | 7a
 8 | 8a
 9 | 9a
(4 rows)

2.5 “WITH NO DATE” 刷新模式

1
2
3
4
5
6
7
8
9
10
11
12
13
 
francs=> refresh materialized view mv_test_1 with no data;
REFRESH MATERIALIZED VIEW
  
francs=> select * from mv_test_1;
ERROR: materialized view "mv_test_1" has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
  
francs=> /dm+ mv_test_1
              List of relations
Schema |  Name  |    Type    | Owner | Size  | Description
--------+-----------+-------------------+--------+---------+-------------
francs | mv_test_1 | materialized view | francs | 0 bytes |
(1 row)

备注：刷新物化视图时有两种模式，”WITH DATA” 模式表示重新加载数据到物化视图，命令执行后物化视图可以访问；而”WITH NO DATE” 模式表示不刷新物化视图，命令执行之后物化视图不可访问。

总结

  1. 以上只是 PostgreSQL 9.3 deve 版目前提供的功能，可能之后会有较大改变。
  2. 目前物化视图功能并不完善，例如自动刷新、增量刷新、跨库刷新等功能暂不支持，期待后续完善。

参考

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/237943.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论