PostgreSQL的物化视图

概述9.4英文文档：http://www.postgresql.org/docs/9.4/interactive/sql-creatematerializedview.html 9.3中文文档：http://www.postgres.cn/docs/9.3/sql-creatematerializedview.html 概述     物化视图是PG9.3新增的，物化视图既能记住查询SQL，也能填充数据

9.4英文文档：http://www.postgresql.org/docs/9.4/interactive/sql-creatematerializedview.html

9.3中文文档：http://www.postgres.cn/docs/9.3/sql-creatematerializedview.html

概述

物化视图是PG9.3新增的，物化视图既能记住查询sql，也能填充数据。后期，还能在需要的时候通过refresh materialized vIEw来实现数据刷新。(注意：目前还不支持在原表数据提交后自动刷新的功能)

refresh materialized vIEw的 concurrently语法是9.4新增的，可以在刷新视图时不锁住对该物化视图的查询工作，但在多行受影响时刷新速度会下降；该参数的原理和优缺点与索引的concurrently类似，以时间来换取查询锁，刷新的速度会变慢。

一、创建物化视图：@H_404_33@
语法：@H_404_33@

CREATEMATERIAliZEDVIEWtable_name[(column_name[,...])][WITH(storage_parameter[=value][,...])][tableSPACEtablespace_name]ASquery[WITH[NO]DATA]

是一个Postgresql扩展。

CREATE MATERIAliZED VIEW用来定义一个查询的物化视图。在命令发出时，查询会被执行并且默认用来填充该物化视图(使用WITH NO DATA则不会填充)。之后可以通过REFRESH MATERIAliZED VIEW来刷新。

物化视图和表有很多相同的属性，但是物化视图不支持临时物化视图和自动生更OID(既不支持WITH oID语法)。

CREATE MATERIAliZED VIEW: 既保存数据,又保存sql；

CREATE table AS:只保存数据，不保存sql；

CREATE VIEW：不保存数据，只保存sql;

参数：@H_404_33@

table_name：是要创建的物化视图的名称(可以有模式修饰)。

column_name：物化视图的列名。如果不提供column_name，将从查询结果中去获取。

storage_parameter：该自居为物化视图指定可选的存储参数。请参照Storage Parameters。所有CREATE table支持的参数CREATE MATERIAliZED VIEW也都支持，除了OID。请参照CREATE TABLE。

tablespace_name:指定本物化视图会被创建到哪个表空间。如果不指定，则会使用default_tablespace。

query:是一个SELECT,TABLE，或者VALUES查询.该查询将在一个受限制的安全 *** 作中执行。对自身创建临时表的函数的调用会失败。

VALUES(1，'lily');等价于select1,'lily';values(1,'lily'),(2,'lucy');等价于select1AScolumn1,'lily'AScolumn2unionselect2,'lucy';tablename；等价于SELECT*FROMname

WITH [NO] DATA: 声明物化视图是否在创建时填充数据。如果不填充的话，该物化视图将会被标记为不可扫描的，直到首次REFRESH MATERIAliZED VIEW执行后才能被查询。

例子：@H_404_33@

table=#createtablelyy(IDintprimarykey,namevarchar);CREATEtabletable=#insertintolyyselectgenerate_serIEs(1,10),'name';INSERT010table=#select*fromlyy;ID|name----+------1|name2|name.....(10rows）--创建物化视图withdata或者缺省时，物化视图会被填充，处于可扫描状态table=#creatematerializedvIEwlyy_mv(ID,name)asselectID,namefromlyy;SELECT10table=#select*fromlyy_mv;ID|name----+------1|name2|name.....(10rows）

二、刷新物化视图@H_404_33@ 语法：@H_404_33@

REFRESHMATERIAliZEDVIEW[CONCURRENTLY]name[WITH[NO]DATA]

REFRESH MATERIAliZED VIEW 会完全替代物化视图的原有内容，原有内容会被舍弃。如果声明了WITH DATA(或者是缺省)，会执行后端查询来提供新的数据，物化视图的将保留在可扫描状态。如果声明了WITH NO DATA，物化视图将保留在不可扫描的状态。

CONCURRENTLY和WITH NO DATA不能同时使用，否则会报错。

参数：@H_404_33@

name：要刷新的物化视图的名字(可以有模式修饰)。@H_404_33@

CONCURRENTLY：刷新物化视图而不锁定物化视图上的查询。不指定concurrently选项时，一次影响很多行的刷新，将会使用更少的资源并且完成地更迅速，但是会锁定其他试图从该物化视图读数据的链接。该选项在少量行受影响时，可能速度会更快。

该选项仅在这种情况下才能使用：在物化视图上有至少一个UNIQUE索引，Unique索引仅使用列名并涵盖所有行。也就是说，必须首先创建至少一个，在物化视图的一个或多个字段上，没有where子句的，UNIQUE索引，才能使用concurrently选项；否则会报错。

在物化视图未被填充时，不能使用该选项；否则会报错。

即使一次运行一个带有该选项的REFRESH，也可能会与任何一个物化视图形成竞态。

例子：@H_404_33@

--原表数据增加后，不刷新物化视图，物化视图的数据就不会变table=#insertintolyyvalues(11,'lyy');INSERT01table=#select*fromlyy_mv;ID|name----+------1|name2|name......(10rows)--使用withdata或者缺省刷新物化视图后，新数据会填充，原数据被舍弃table=#refreshmaterializedvIEwlyy_mvwithdata;REFRESHMATERIAliZEDVIEWtable=#select*fromlyy_mv;ID|name----+------1|name2|name......11|lyy（11rows）--使用withnodata刷新物化视图，原数据被舍弃，未填充新数据，将处于不可扫描状态。table=#refreshmaterializedvIEwlyy_mvwithnodata;REFRESHMATERIAliZEDVIEWtable=#select*fromlyy_mv;ERROR:materializedvIEw"lyy_mv"hasnotbeenpopulateddetail:UsetheREFRESHMATERIAliZEDVIEWcommand.--重现刷新物化视图后，原数据恢复，物化视图又可以进行扫描table=#refreshmaterializedvIEwlyy_mv;REFRESHMATERIAliZEDVIEWtable=#select*fromlyy_mv;ID|name----+------1|name2|name......11|lyy（11rows）--必须先创建至少一个unique索引(无where子句，至少涉及一个字段)，才能使用concurrentlytable=#refreshmaterializedvIEwconcurrentlylyy_mvwithdata;ERROR:cannotrefreshmaterializedvIEw"public.lyy_mv"concurrentlydetail:CreateauniqueindexwithNowHEREclauSEOnoneormorecolumnsofthematerializedvIEw.table=#createuniqueindexlyy_mv_uindexonlyy_mv(ID);CREATEINDEXtable=#refreshmaterializedvIEwconcurrentlylyy_mvwithdata;REFRESHMATERIAliZEDVIEW--concurrently与withnodata不能同时使用.table=#refreshmaterializedvIEwconcurrentlylyy_mvwithnodata;ERROR:CONCURRENTLYanDWITHNODATAoptionscannotbeusedtogether--物化视图未被填充时，不能使用concurrently.table=#refreshmaterializedvIEwlyy_mvwithnodata;REFRESHMATERIAliZEDVIEWtable=#refreshmaterializedvIEwconcurrentlylyy_mvwithnodata;ERROR:CONCURRENTLYcannotbeuseDWhenthematerializedvIEwisnotpopulated

多事务中，物化视图的普通更新会阻塞对它的查询，currently更新不会阻塞查询。

物化视图的普通更新：

--事务1table=#begin;BEGINtable=#selectpg_backend_pID();pg_backend_pID----------------3644table=#refreshmaterializedvIEwlyy_mvwithdata;REFRESHMATERIAliZEDVIEW--事务2table=#begin;BEGINtable=#selectpg_backend_pID();pg_backend_pID----------------1316table=#selectpID,mode,relation,grantedfrompg_lockswhererelation='lyy_mv'::regclass;pID|mode|relation|granted------+---------------------+----------+---------3644|AccessShareLock|74440|t3644|ShareLock|74440|t3644|ExclusiveLock|74440|t3644|AccessExclusiveLock|74440|t(4rows)--事务3table=#begin;BEGINtable=#selectpg_backend_pID();pg_backend_pID----------------2772table=#select*fromlyy_mv;--查询处于等待状态--事务2table=#selectpID,grantedfrompg_lockswhererelation='lyy_mv'::regclass;pID|mode|relation|granted------+---------------------+----------+---------2772|AccessShareLock|74440|f3644|AccessShareLock|74440|t3644|ShareLock|74440|t3644|ExclusiveLock|74440|t3644|AccessExclusiveLock|74440|t(5rows）

物化视图的concurrently更新：

--事务1table=#begin;BEGINtable=#selectpg_backend_pID();pg_backend_pID----------------3644table=#refreshmaterializedvIEwconcurrentlylyy_mvwithdata;REFRESHMATERIAliZEDVIEW--事务2table=#begin;BEGINtable=#selectpID,grantedfrompg_lockswhererelation='lyy_mv'::regclass;pID|mode|relation|granted------+------------------+----------+---------3644|AccessShareLock|74440|t3644|RowExclusiveLock|74440|t3644|ExclusiveLock|74440|t(3rows)--事务3table=#begin;BEGINtable=#selectpg_backend_pID();pg_backend_pID----------------2772table=#select*fromlyy_mv;ID|name----+------1|name2|name......11|lyy(11rows)--查询可以执行，未被阻塞--事务2table=#selectpID,grantedfrompg_lockswhererelation='lyy_mv'::regclass;pID|mode|relation|granted------+------------------+----------+---------2772|AccessShareLock|74440|t3644|AccessShareLock|74440|t3644|RowExclusiveLock|74440|t3644|ExclusiveLock|74440|t(4rows)

物化视图的普通更新要比concurrently更新的效率高很多。

table=#createtableyy(IDintprimarykey,namevarchar);CREATEtabletable=#creatematerializedvIEwyy_mv(ID,namefronodata;SELECT0table=#insertintoyy(ID,name)selectgenerate_serIEs(1,100000),'nameINSERT0100000table=#selectcount(*)fromyy;count--------100000table=#\timingTimeison.--普通刷新table=#refreshmaterializedvIEwyy_mvwithdata;REFRESHMATERIAliZEDVIEWTime:54.606mstable=#selectcount(*)fromyy_mv;count--------100000--concurrently刷新,明显慢了很多.（此处不考虑数据量很小的情况）--首先得为物化视图创建无where子句的unique索引。table=#createuniqueindexyy_mv_uindexonyy_mv(ID);CREATEINDEXtable=#refreshmaterializedvIEwconcurrentlyyy_mvwithdata;REFRESHMATERIAliZEDVIEWTIME：226.042mstable=#selectcount(*)fromyy;count--------100000

三、移除物化视图 语法：@H_404_33@

DROPMATERIAliZEDVIEW[IFEXISTS]name[,...][CASCADE|RESTRICT]

DROP MATERIAliZED VIEW 删除已存在的物化视图。要执行该命令必须是该物化视图的所有者。

参数：@H_404_33@

IF EXISTS ： 如果物化视图不存在不会抛出错误，仅发送一个提示信息。

name: 要移除的物化视图的名称（可以有模式修饰）。

CASCADE：自动删除依赖该物化视图的对象(比如其他的物化视图或者普通视图)。

RESTRICT : 如果有任何对象依赖于该物化视图，则拒绝删除该物化视图。（这是缺省的）。

参考资料：

http://francs3.blog.163.com/blog/static/405767272014421104127225/

http://my.oschina.net/Kenyon/blog/407093

总结

以上是内存溢出为你收集整理的PostgreSQL的物化视图全部内容，希望文章能够帮你解决PostgreSQL的物化视图所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。