PostgreSQL物化视图的刷新机制

1、PostgreSQL物化视图的刷新机制目录l 物化视图简介l 为什么使用物化视图l 物化视图刷新方式l 锁机制l 物化视图刷新实现l 物化视图刷新示例l 重建索引简介物化视图是一个包含实属性的表，但表现上像一个视图原表数据改 变时，物化视图中的数据也要跟着改变。物化视图类型快照物化视图(snapshot materialized view)最容易实现，物化视图中的数据需要手动刷新积极物化视图(Eager materialized view)物化视图在数据库被更新时同步更新消极物化视图(Lazy materialized view)在事务(Transaction)提交时更新非常消极物化视图(Ve

2、ry Lazy materialized view)类似于快照物化视图，区别在于变化都会被增量式地记录下来( changes are recorded incrementally )并在手动刷新时被调用(applied)为什么使用在执行某些查询的时候，效率较低，而且使用传统方法（例如，索引或查询优化）无法显著提高效率 这时使用的方法是将需要查询的数据事先进行查询(pre-querying) 并储存起来，这样每次查询时就不需要都从头执行一次。类似的“缓存”机制其实就是物化视图物化视图刷新方式物化视图刷新语法：REFRESH MATERIALIZED VIEW CONCURRENTLY table

3、_name WITH NO DATA 方式一：REFRESH MATERIALIZED VIEW table_name方式二：REFRESH MATERIALIZED VIEW CONCURRENTLY table_name物化视图刷新方式物化视图刷新语法：REFRESH MATERIALIZED VIEW CONCURRENTLY table_name WITH NO DATA 能使用CONCURRENTLY选项的情况：在物化视图上必须首先创建至少一个在物化视图的一个或多个字段上，没有where子 句的UNIQUE索引，UNIQUE索引仅使用列名并涵盖所有行；否则会报错在物化视图未被填充时，

4、不能使用该选项；否则会报错物化视图刷新方式物化视图刷新语法：REFRESH MATERIALIZED VIEW CONCURRENTLY table_name WITH NO DATA 使用CONCURRENTLY选项 可以在刷新视图时不锁住对该物化视图的查询工作， 但在多行受影响时刷新速度会下降；该参数的原理和优缺点与索引的 CONCURRENTLY类似，以时间来换取查询锁，刷新的速度会变慢不指定CONCURRENTLY选项时，一次影响很多行的刷新，使用更少的资源并且 完成地更迅速，但是会锁定其他试图从该物化视图读数据的连接。该选项在少 量行受影响时，可能速度会更快PostgreSQL 锁机

5、制锁模式说明Access Share只与Access Exclusive锁模式冲突。查询命令（Select command）将会在 它查询的表上获取Access Shared锁，一般地，任何一个对表上的只读查 询操作都将获取这种类型锁。Row Share与Exclusive和Access Exclusive锁模式冲突。Select for update和Select forshare命令将获得这种类型锁，并且所有被引用但没有for update 的表上 会加上Access Shared锁。Row Exclusive与Share，Shared Row Exclusive，Exclusive，Ac

6、cess Exclusive模式冲突。 Update/Delete/Insert命令会在目标表上获得这种类型的锁，并且在其它 被引用的表上加上Access Share锁，一般地，更改表数据的命令都将在这 张表上获得Row Exclusive锁。Share Update ExclusiveShare Update Exclusive，Share，Share Row Exclusive，Exclusive，Accessexclusive模式冲突，这种模式保护一张表不被并发的模式更改和Vacuum。PostgreSQL 锁机制锁模式说明Share与Row Exclusive，Shared Updat

7、e Exclusive，Share Row Exclusive，Exclusive， Access exclusive锁模式冲突，这种模式保护一张表数据不被并发的更改。Share Row Exclusive与 Row Exclusive，Share Update Exclusive，Shared，Shared Row Exclusive， Exclusive，Access Exclusive锁模式冲突。这种模式可以保护表不受并发数据更改 的影响，并且是自排他的，因此一次只能有一个会话保存它。Exclusive与ROW Share , Row Exclusive, Share Update Ex

8、clusive, Share , Share Row Exclusive, Exclusive, Access Exclusive模式冲突，这种锁模式仅能与Access Share 模式并发， 换句话说，只有读操作可以和持有Exclusive锁的事务并行。Access Exclusive与所有模式锁冲突(Access Share，Row Share，Row Exclusive，Share Update Exclusive，Share , Share Row Exclusive，Exclusive，Access Exclusive)，这种模式 保证了当前只有一个人访问这张表；ALTER TABL

9、E，DROP TABLE，TRUNCATE， REINDEX，CLUSTER，VACUUM FULL 命令会获得这种类型锁，在Lock table 命令 中，如果没有申明其它模式，它也是默认模式。PostgreSQL 锁机制表级锁的冲突矩阵物化视图刷新实现type; concurrent; skipData;*relation;NodeTag bool bool RangeVar RefreshMatViewStmt;入口函数ExecRefreshMatView(RefreshMatViewStmt *stmt, const char*queryString,ParamwListInfo pa

10、rams, char *completionTag)typedef struct RefreshMatViewStmt实现ExecRefreshMatView函数方式二（有concurrently选项）：l 设置锁的模式：排他锁（Exclusivelock）l 正确性检查 l 唯一索引检查 l 创建临时表空间l 创建临时表l 调用refresh_by_match_merge函数完成刷新方式一（无concurrently选项）：l 设置锁的模式：访问排他锁（AccessExclusiveLock）l 正确性检查l 创建临时表l 调用refresh_by_heap_swap函数完成刷新物化视图刷新

11、实现refresh_by_heap_swap通过交换原来的表和临时表的物理文件，并重新建立索 引，完成刷新基表临时表物化视图生成替换物化视图刷新实现基表临时表物化视图差异表refresh_by_match_merge通过原来的表与临时表外部连接，根据连接结果得到两个表的 差异，并做删除操作，接着再做插入操作，来完成刷新，最后 删除临时表更新生成物化视图刷新实现idname1John2May3Lin4Li原表refresh_by_match_mergeidname5Ha2May3Lin4Lucy临时表物化视图刷新实现idname1John2May3Lin4Li原表refresh_by_match

12、_mergeidname5Ha2May3Lin4Lucy临时表idnewdata14(5,Ha)(4,Lucy)差异表CREATE TEMP TABLE diffname AS SELECT mv.ctid AS tid, newdata FROM matviename mv FULL JOIN tempname newdata ON (newdata.tid=mv.tid AND newdata = mv) WHERE newdata IS NULL OR mv IS NULL ORDER BY tid;物化视图刷新实现1John原表refresh_by_match_mergeidname5

13、Ha2May3Lin4Lucy临时表idnewdata14(5,Ha)(4,Lucy)差异表idname2May3Lin4LiDELETE FROM matviewname mv WHERE ctid = ANY (SELECT diff.tid FROM diffname diff WHERE diff.tid IS NOT NULL AND diff.newdata IS NULL);物化视图刷新实现refresh_by_match_mergeidname5Ha2May3Lin4Lucyidnewdata14(5,Ha)(4,Lucy)idname5Ha2May3Lin4Lucy旧表临时表差异表INSERT INTO matviewname SELECT (diff.newdata).* FROM diffname diff WHERE tid IS NULL;物化视图刷新实现refresh_by_match_mergeidname5Ha2May3Lin4Lucy 临时表idnewdata14(5,Ha)(4,Lucy) 差异表idname5Ha2May3Lin4Lucy旧表Drop table diffname,tempname;物化视图示例物化