postgresql9.5 物化视图测试

概述copyright http://cupegraf.com/                          视图是指数据库只存储定义该视图的查询语句（内容是查询时产生），而物化视图是一个其查询语句查询后的内容并存储的视图(内容是创建物化视图刷新视图时产生，数据可修改。独立)。因为物化视图是视图的一个物化表结构，但是里面的数据是创建时刷新查询到额数据，当原数据更新修改时如果物化视图的表没有更新会

copyrighthttp://cupegraf.com/

视图是指数据库只存储定义该视图的查询语句（内容是查询时产生），而物化视图是一个其查询语句查询后的内容并存储的视图(内容是创建物化视图刷新视图时产生，数据可修改。独立)。因为物化视图是视图的一个物化表结构，但是里面的数据是创建时刷新查询到额数据，当原数据更新修改时如果物化视图的表没有更新会造成数据的不一致。从9.4开始增加增量刷新，9.5的版本中支持物化视图也支持索引，修改表空间，指定用户访问权限

postgresql9.5物化视图测试:

search package: sudo apt-cache search dtrace-*

dtrac && readline install :sudo apt-get installsystemtap-sdt-dev libssl-devlibpam-dev libxml2-dev libxslt-devlibtcl8.4 libperl-dev python-dev

./configure --prefix=/home/pg5/pgsql9.5-devel --with-port=5433 --with-perl --without-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-blocksize=32 --enable-dtrace --enable-deBUG

make

sudo make install

cd postgresql源码目录/contrib

make

sudo make install(后面要用到fDW)

.bashrc的环境变量要配置:

exportPGHOME=/home/pg5/pgsql9.5-develexportPATH=$PATH:$PGHOME/binexportPGDATA=/home/pg5/dataexportPGUSER=pg5exportPGPORT=5433

测试数据：

createdatabaseeachmaENCoding=UTF8;createtabletbl(IDintprimarykey,infotext,crt_timetimestamp);insertintotblselectgenerate_serIEs(1,100000),md5(random()::text),clock_timestamp();creatematerializedvIEwtbl_vIEwasselect*fromtblwhereID<1000withnodata;creatematerializedvIEwtbl_vIEw1asselect*fromtblwithnodata;createuniqueindexIDx_tbl_vIEw_IDontbl_vIEw(ID);createuniqueindexIDx_tbl_vIEw1_IDontbl_vIEw1(ID);refreshmaterializedvIEwtbl_vIEw;refreshmaterializedvIEwtbl_vIEw1;\timing#打开事务执行时间

增量刷新;refresh materialized vIEw concurrently tbl_vIEw1;

非增量刷新：refresh materialized vIEw tbl_vIEw1;

pg中查看物化视图表：

select * from pg_matvIEws;

eachma=#select*frompg_matvIEws;-[RECORD1]+-------------------------schemaname|publicmatvIEwname|tbl_vIEwmatvIEwowner|pg5tablespace|hasindexes|tispopulated|tdeFinition|SELECTtbl.ID,+|tbl.info,+|tbl.crt_time+|FROMtbl+|WHERE(tbl.ID<1000);-[RECORD2]+-------------------------schemaname|publicmatvIEwname|tbl_vIEw1matvIEwowner|pg5tablespace|hasindexes|tispopulated|tdeFinition|SELECTtbl.ID,+|tbl.crt_time+|FROMtbl;Time:0.758ms

增量刷新不会锁表，阻断其他查询。但是视图的非增量刷新会锁表。两者利弊不一，前者不锁表，但是执行需要的时间比较长，因为是Join查询需要一条条的数据进行对比，以时间来换取查询锁。以至于不会影响到物化视图的查询工作。而后者的执行等待时间比较短.但其他的查询需要等待刷新之后才能完成：

增量刷新：

eachma=#begin;BEGINTime:0.117mseachma=#refreshmaterializedvIEwconcurrentlytbl_vIEw1;REFRESHMATERIAliZEDVIEWTime:2085.527mseachma=#commit;COMMITTime:2.718mseachma=#end

#非增量刷新eachma=#begin;BEGINTime:0.104mseachma=#refreshmaterializedvIEwtbl_vIEw1;REFRESHMATERIAliZEDVIEWTime:209.312mseachma=#commit;COMMITTime:9.777mseachma=#end;WARNING:thereisnotransactioninprogressCOMMITTime:0.318ms

pg是支持外部表物化视图。例如oracle里面有一个表的数据是我们想需要的，但是一般情况下是需要我们通过odbc或者是dump出来。但是因为pg支持外部表（FDW,dblink）。可以通过创建一个我们需要oracle中数据，那么可以先创建一个外部表，然后给这个外部表创建物化视图，这样也减少的数据的拷贝，oracle有数据更新时也可以去更新视图。但要注意物化视图的表增量的刷新要与远程表规则一致（索引）

下面是做一个外部表(以postgresql9.4的库为外部库)的物化视图测试:

eachma=# CREATE EXTENSION postgres_fDW;

安装成功：

eachma=#\dflistoffunctionsSchema|name|Resultdatatype|Argumentdatatypes|Type--------+------------------------+------------------+---------------------+--------public|postgres_fDW_handler|fDW_handler||normalpublic|postgres_fDW_valIDator|voID|text[],oID|normal

createserverpg4VERSION'9.4'foreigndatawrapperpostgres_fDWOPTIONS(host'211.69.228.50',dbname'tech',port'5432');#创建servercreateuserMAPPingforpg5serverpg4options(user'eachma',password'612345');#创建映射用户CREATEFOREIGNtableorders(order_numintegernotnull,order_datedatenotnull,cust_IDcharacter(10)notnull)serverpg4OPTIONS(schema_name'public',table_name'orders');#外部表目前不支持主健约束eachma=#select*frompg_foreign_table;#外部表-[RECORD1]-------------------------------------ftrelID|16579ftserver|16577ftoptions|{schema_name=public,table_name=orders}

eachma=#select*fromorders;order_num|order_date|cust_ID-----------+------------+------------20005|2012-05-01|100000000120006|2012-01-12|100000000320007|2012-01-30|100000000420008|2012-02-03|100000000520009|2012-02-08|1000000001(5rows)eachma=#\dtlistofrelationsSchema|name|Type|Owner--------+------+-------+-------public|tbl|table|pg5(1row)

创建一个外部表物化视图：

eachma=#creatematerializedvIEworder_vIEwasselect*fromorderswithnodata;SELECT0eachma=#select*fromorder_vIEw;#没有刷新ERROR:materializedvIEw"order_vIEw"hasnotbeenpopulatedHINT:UsetheREFRESHMATERIAliZEDVIEWcommand.eachma=#refreshmaterializedvIEworder_vIEw;REFRESHMATERIAliZEDVIEWeachma=#select*fromorder_vIEw;order_num|order_date|cust_ID-----------+------------+------------20005|2012-05-01|100000000120006|2012-01-12|100000000320007|2012-01-30|100000000420008|2012-02-03|100000000520009|2012-02-08|1000000001(5rows)eachma=#select*frompg_matvIEws;-[RECORD1]+--------------------------schemaname|publicmatvIEwname|order_vIEwmatvIEwowner|pg5tablespace|hasindexes|fispopulated|tdeFinition|SELECTorders.order_num,+|orders.order_date,+|orders.cust_ID+|FROMorders;

参考：http://www.postgresql.org/docs/9.5/static/postgres-fdw.html

postgresql资料:https://github.com/ty4z2008/Qix/blob/master/pg.md

总结

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

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