PostgreSQL的物化视图

前端之家收集整理的这篇文章主要介绍了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,也能填充数据。后期,还能在需要的时候通过refresh materialized view来实现数据刷新。(注意:目前还不支持在原表数据提交后自动刷新的功能)

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


一、创建物化视图:

语法:

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;

参数:

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执行后才能被查询

例子:

table=#createtablelyy(idintprimarykey,namevarchar);
CREATETABLE
table=#insertintolyyselectgenerate_series(1,10),'name';
INSERT010
table=#select*fromlyy;
id|name
----+------
1|name
2|name
.....
(10rows)
--创建物化视图withdata或者缺省时,物化视图会被填充,处于可扫描状态
table=#creatematerializedviewlyy_mv(id,name)asselectid,namefromlyy;
SELECT10
table=#select*fromlyy_mv;
id|name
----+------
1|name
2|name
.....
(10rows)


二、刷新物化视图

语法:

REFRESHMATERIALIZEDVIEW[CONCURRENTLY]name
[WITH[NO]DATA]

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

CONCURRENTLYWITH NO DATA不能同时使用,否则会报错

参数:

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

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

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

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

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

例子:

--原表数据增加后,不刷新物化视图,物化视图的数据就不会变
table=#insertintolyyvalues(11,'lyy');
INSERT01
table=#select*fromlyy_mv;
id|name
----+------
1|name
2|name
......
(10rows)


--使用withdata或者缺省刷新物化视图后,新数据会填充,原数据被舍弃
table=#refreshmaterializedviewlyy_mvwithdata;
REFRESHMATERIALIZEDVIEW
table=#select*fromlyy_mv;
id|name
----+------
1|name
2|name
......
11|lyy
(11rows)


--使用withnodata刷新物化视图,原数据被舍弃,未填充新数据,将处于不可扫描状态。
table=#refreshmaterializedviewlyy_mvwithnodata;
REFRESHMATERIALIZEDVIEW
table=#select*fromlyy_mv;
ERROR:materializedview"lyy_mv"hasnotbeenpopulated
detail:UsetheREFRESHMATERIALIZEDVIEWcommand.
--重现刷新物化视图后,原数据恢复,物化视图又可以进行扫描
table=#refreshmaterializedviewlyy_mv;
REFRESHMATERIALIZEDVIEW
table=#select*fromlyy_mv;
id|name
----+------
1|name
2|name
......
11|lyy
(11rows)


--必须先创建至少一个unique索引(无where子句,至少涉及一个字段),才能使用concurrently
table=#refreshmaterializedviewconcurrentlylyy_mvwithdata;
ERROR:cannotrefreshmaterializedview"public.lyy_mv"concurrently
detail:CreateauniqueindexwithnoWHEREclauSEOnoneormorecolumnsofth
ematerializedview.
table=#createuniqueindexlyy_mv_uindexonlyy_mv(id);
CREATEINDEX
table=#refreshmaterializedviewconcurrentlylyy_mvwithdata;
REFRESHMATERIALIZEDVIEW


--concurrently与withnodata不能同时使用.
table=#refreshmaterializedviewconcurrentlylyy_mvwithnodata;
ERROR:CONCURRENTLYandWITHNODATAoptionscannotbeusedtogether


--物化视图未被填充时,不能使用concurrently.
table=#refreshmaterializedviewlyy_mvwithnodata;
REFRESHMATERIALIZEDVIEW
table=#refreshmaterializedviewconcurrentlylyy_mvwithnodata;
ERROR:CONCURRENTLYcannotbeusedwhenthematerializedviewisnotpopulated


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

物化视图的普通更新:

--事务1
table=#begin;
BEGIN
table=#selectpg_backend_pid();
pg_backend_pid
----------------
3644
table=#refreshmaterializedviewlyy_mvwithdata;
REFRESHMATERIALIZEDVIEW


--事务2
table=#begin;
BEGIN
table=#selectpg_backend_pid();
pg_backend_pid
----------------
1316
table=#selectpid,mode,relation,grantedfrompg_lockswhererelation='lyy_mv'::regclass;
pid|mode|relation|granted
------+---------------------+----------+---------
3644|AccessShareLock|74440|t
3644|ShareLock|74440|t
3644|ExclusiveLock|74440|t
3644|AccessExclusiveLock|74440|t
(4rows)


--事务3
table=#begin;
BEGIN
table=#selectpg_backend_pid();
pg_backend_pid
----------------
2772
table=#select*fromlyy_mv;
--查询处于等待状态


--事务2
table=#selectpid,grantedfrompg_lockswhererelation='lyy_mv'::regclass;
pid|mode|relation|granted
------+---------------------+----------+---------
2772|AccessShareLock|74440|f
3644|AccessShareLock|74440|t
3644|ShareLock|74440|t
3644|ExclusiveLock|74440|t
3644|AccessExclusiveLock|74440|t
(5rows)

物化视图的concurrently更新:

--事务1
table=#begin;
BEGIN
table=#selectpg_backend_pid();
pg_backend_pid
----------------
3644
table=#refreshmaterializedviewconcurrentlylyy_mvwithdata;
REFRESHMATERIALIZEDVIEW


--事务2
table=#begin;
BEGIN
table=#selectpid,grantedfrompg_lockswhererelation='lyy_mv':
:regclass;
pid|mode|relation|granted
------+------------------+----------+---------
3644|AccessShareLock|74440|t
3644|RowExclusiveLock|74440|t
3644|ExclusiveLock|74440|t
(3rows)


--事务3
table=#begin;
BEGIN
table=#selectpg_backend_pid();
pg_backend_pid
----------------
2772
table=#select*fromlyy_mv;
id|name
----+------
1|name
2|name
......
11|lyy
(11rows)
--查询可以执行,未被阻塞


--事务2
table=#selectpid,grantedfrompg_lockswhererelation='lyy_mv'::regclass;
pid|mode|relation|granted
------+------------------+----------+---------
2772|AccessShareLock|74440|t
3644|AccessShareLock|74440|t
3644|RowExclusiveLock|74440|t
3644|ExclusiveLock|74440|t
(4rows)


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

table=#createtableyy(idintprimarykey,namevarchar);
CREATETABLE
table=#creatematerializedviewyy_mv(id,namefro
nodata;
SELECT0
table=#insertintoyy(id,name)selectgenerate_series(1,100000),'name
INSERT0100000
table=#selectcount(*)fromyy;
count
--------
100000
table=#\timing
Timeison.

--普通刷新
table=#refreshmaterializedviewyy_mvwithdata;
REFRESHMATERIALIZEDVIEW
Time:54.606ms
table=#selectcount(*)fromyy_mv;
count
--------
100000

--concurrently刷新,明显慢了很多.(此处不考虑数据量很小的情况)
--首先得为物化视图创建无where子句的unique索引。
table=#createuniqueindexyy_mv_uindexonyy_mv(id);
CREATEINDEX
table=#refreshmaterializedviewconcurrentlyyy_mvwithdata;
REFRESHMATERIALIZEDVIEW
TIME:226.042ms
table=#selectcount(*)fromyy;
count
--------
100000


三、移除物化视图

语法:

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

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

参数:

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

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

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

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


参考资料:

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

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

原文链接:https://www.f2er.com/postgresql/194996.html

猜你在找的Postgre SQL相关文章