手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

PostgreSQL 9.3物化视图使用

[日期:2013-09-13] 来源:oschina.net  作者:Kenyon [字体: ]

物化视图在Oracle里面是很早就内置的一个功能,而PostgreSQL也很早就将功能代码做出来,方式是类似create table as....,只是一直没有内置,9.3版本终于将此作为一个内置的功能点来使用,下面分享下最新版本的物化视图使用。

目前postgres9.3在官网上有4个安装包,分别是9.3.0(stable version)、9.3.0 beta1、9.3.0betal2和9.3.0rc版本(release candidate version),所以我们下载稳定的9.3.0 stable版本。

下载地址: http://www.postgresql.org/ftp/source/v9.3.0/
安装略。

一、语法

CREATE MATERIALIZED VIEW table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

二、说明
storage_parameter是存储参数,诸如填充因子(fillfactor)等,tablespace可以指定表空间,比较关键的是后面的as query with [no] data,后面示例描述

三、示例

1.创建基础表

[postgres@primary ~]$ psql
psql (9.3.0)
Type "help" for help.

postgres=# create table test_kenyon(id int,vname text);                                                 
CREATE TABLE
postgres=# insert into test_kenyon select generate_series(1,20),'kenyon good boy'||generate_series(1,20);
INSERT 0 20
postgres=# select * from test_kenyon ;
 id |       vname       
----+-------------------
  1 | kenyon good boy1
  2 | kenyon good boy2
  3 | kenyon good boy3
  4 | kenyon good boy4
  5 | kenyon good boy5
  6 | kenyon good boy6
  7 | kenyon good boy7
  8 | kenyon good boy8
  9 | kenyon good boy9
 10 | kenyon good boy10
 11 | kenyon good boy11
 12 | kenyon good boy12
 13 | kenyon good boy13
 14 | kenyon good boy14
 15 | kenyon good boy15
 16 | kenyon good boy16
 17 | kenyon good boy17
 18 | kenyon good boy18
 19 | kenyon good boy19
 20 | kenyon good boy20
(20 rows)

2.创建物化视图

postgres=# create materialized view mv_test_kenyon  as select * from test_kenyon where id > 10;
SELECT 10
postgres=# select * from mv_test_kenyon;
 id |       vname       
----+-------------------
 11 | kenyon good boy11
 12 | kenyon good boy12
 13 | kenyon good boy13
 14 | kenyon good boy14
 15 | kenyon good boy15
 16 | kenyon good boy16
 17 | kenyon good boy17
 18 | kenyon good boy18
 19 | kenyon good boy19
 20 | kenyon good boy20
(10 rows)

postgres=# \d+
                              List of relations
 Schema |      Name      |       Type        |  Owner   | Size  | Description 
--------+----------------+-------------------+----------+-------+-------------
 public | mv_test_kenyon | materialized view | postgres | 16 kB | 
 public | test_kenyon    | table             | postgres | 16 kB | 
(2 rows)

postgres=# \d mv_test_kenyon
Materialized view "public.mv_test_kenyon"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 vname  | text    |
--size有大小(默认空表是8kb,而这里是16kb)说明存储了数据,有相应的物理文件,并且有类似表的结构

3.物化视图更新

postgres=# insert into test_kenyon values(21,'bad boy');
INSERT 0 1
postgres=# insert into test_kenyon values(22,'bad boy2');
INSERT 0 1
postgres=# select * from test_kenyon where id>20;
 id |  vname   
----+----------
 21 | bad boy
 22 | bad boy2
(2 rows)

postgres=# select * from mv_test_kenyon where id>20;
 id | vname 
----+-------
(0 rows)
--物化视图的数据没有刷新过来

--刷新物化视图数据
postgres=# refresh materialized view mv_test_kenyon;
REFRESH MATERIALIZED VIEW
postgres=# select * from mv_test_kenyon where id>20;
 id |  vname   
----+----------
 21 | bad boy
 22 | bad boy2
(2 rows)

--使用with no data刷新
postgres=# insert into test_kenyon values(32,'bad boy3'); 
INSERT 0 1
postgres=# select * from mv_test_kenyon where id>20;     
 id |  vname   
----+----------
 21 | bad boy
 22 | bad boy2
(2 rows)

postgres=# refresh materialized view mv_test_kenyon with no data;
REFRESH MATERIALIZED VIEW
postgres=# \d+
                                 List of relations
 Schema |      Name      |       Type        |  Owner   |    Size    | Description 
--------+----------------+-------------------+----------+------------+-------------
 public | mv_test_kenyon | materialized view | postgres | 8192 bytes | 
 public | test_kenyon    | table             | postgres | 16 kB      | 
(2 rows)

postgres=# select * from mv_test_kenyon;
ERROR:  materialized view "mv_test_kenyon" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.

使用了with no data刷新后会导致物化视图里面的数据清除干净,并使物化视图不可用,如果需要继续使用,需要使用REFRESH MATERIALIZED VIEW view_name来恢复。

4.删除物化视图

postgres=# drop materialized view mv_test_kenyon ;
DROP MATERIALIZED VIEW
postgres=# 
--如果有其他约束在物化视图上,需要加cascade来级联删除

四、应用场景和优劣势
可以将复杂的SQL写成视图来调用,并可增大数据的安全性
另外物化视图与普通视图比因为直接扫描数据,通常扫描的数据更少,在有索引的支持下,效率更高,网络消耗也更少,特别是跨DB,跨服务器的查询
与普通视图相比的劣势是数据需要不定时地刷新才能获取到最实时的数据。

、总结
1.物化视图当前是全量刷新,暂不支持增量刷新
2.刷新参数with data是全量更新物化视图内容,且是默认参数;with no data会清除物化视图内容,释放物化视图所占的空间,并使物化视图不可用

六、参考:
http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html http://wiki.postgresql.org/wiki/Materialized_Views

相关阅读

PostgreSQL删除表中重复数据行 http://www.linuxidc.com/Linux/2013-07/87780.htm

PostgreSQL数据库连接池PgBouncer的搭建 http://www.linuxidc.com/Linux/2013-06/85928.htm

Windows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htm

PostgreSQL备份心得笔记 http://www.linuxidc.com/Linux/2013-04/82812.htm

PostgreSQL 的详细介绍请点这里
PostgreSQL 的下载地址请点这里

linux
相关资讯       物化视图  PostgreSQL 9.3  PostgreSQL视图 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款