顾氏网站建设有限公司怎么样,太原网页设计师招聘信息,湖州市住房和城乡建设局网站,做网站怎么和广告公司合作前言#xff1a;
我们一般认为数字的世界是一个虚拟的世界#xff0c;OK#xff0c;但我们其实有些需求是和现实世界一模一样的#xff0c;比如#xff0c;数据库尤其是关系型数据库#xff0c;希望在使用的数据库能够更快#xff08;查询速度#xff09;#xff0c;…前言
我们一般认为数字的世界是一个虚拟的世界OK但我们其实有些需求是和现实世界一模一样的比如数据库尤其是关系型数据库希望在使用的数据库能够更快查询速度更高性能上限更高更强并发性能写能力这些属性正如奥林匹克的体育精神更快更高更强但是很可惜愿望就是愿望了要想实现这样的愿望需要更多的技术想法。
那么毫无疑问的物化视图是一个专门针对查询性能的关系型数据库内的一个对象。
等等视图物化视图
是的就是物化视图我只能告诉大家一个残酷的事实结论先给了不服气的接着看物化视图虽然可以对数据库的查询性能起到一个比较大的提升但物化视图有很大的局限性也可以简单的理解为物化视图是一个双刃剑用好了可以使得数据库系统查询速度飞快用不好可能会造成系统崩溃这样的惨烈后果。
下面我将就物化视图的优缺点适用场景如何使用物化视图做一个简单的介绍。
一
物化视图是什么
###注基表也就是基础表查询结果不是凭空出现的自然是从一个或者N个表内查询得出的结果一个或者N个表也就省略称之为基表了
物化视图是介于表和视图之间的一个关系型数据库的对象可以将它想象成一个查询基表产生的结果集但这个结果集可以很复杂可以是多表联查的结果集可以是一个简单的单表查询结果集。
和普通视图相比更为关键的是物化视图是有对应的存放于$PGDATA目录下的物理文件也就是它不是一个虚幻的虚拟的东西了而是一个真真切切存在的可操作的对象了这也是为什么叫物化视图的原因了。
那么很多同学就会有疑问了普通视图也是一个基于基表的查询而产生的结果集为毛要用什么物化视图有毛病吗
OK普通视图是无法添加索引的而我们都知道索引是可以加快查询速度的也就是基于合适的索引我们是可以加快查询效率的因此一个设计良好的物化视图的查询速度会远远超过一个设计良好的普通视图
和表相比物化视图仅仅是一个查询结果集那自然是没有insertupdate这些功能了也就是说物化视图不可改变其内的数据。
当然在navicat里这个物化视图叫实体化视图目前只需要明白一点叫什么都无所吊谓的 二
物化视图的优缺点和适用场景
OK现在来总结一下物化视图的优缺点
优点 提高性能通过预先计算并存储结果可以避免在每次查询时都需要执行复杂的联接操作或聚合运算从而大大提高查询速度。减少磁盘空间由于物化视图只存储部分数据而不必存储所有基础表的所有数据因此可以减少磁盘空间的需求。支持快速数据刷新大多数数据库系统都支持物化视图的快速刷新可以在较短的时间内更新物化视图中的数据以便及时反映底层数据的变化。物化视图可以添加索引而索引可以有效的提升查询效率
缺点
需要额外的存储空间物化视图需要占用额外的磁盘空间来存储其结果集。更新延迟由于物化视图通常需要定期刷新因此在底层数据发生变化后可能需要一段时间才能反映到物化视图中。因此如果是经常更新的基表而对物化视图的准确性有较高的要求的情况下更新需求会是一个不得不考虑的问题so建议基表更新太多的基表不建议使用物化视图维护复杂性由于物化视图需要定期刷新而且在某些情况下还需要执行复杂的计算因此需要更多的硬件资源提供支持。不适合高并发环境在高并发环境下如果多个用户同时访问物化视图可能会出现锁竞争问题影响性能。主要原因是在刷新或者说同步物化视图这样的操作的时候会有锁的问题。
由于这些优缺点因此可以得出物化视图并不是万能的很有可能是一个双刃剑物化视图需要在一个适用的环境下才可以使用。 OK物化视图的适用场景一般为
基表更新不是非常频繁这个可以有一些时间上的量化例如更新条目在分钟级附近。相对物化视图来说并发程度不是太高多用户同时查询不会影响到物化视图的更新正常完成。数据库的硬件条件比较高可以承担物化视图的频繁更新主要是CPU和内存能够满足物化视图的刷新任务对外提供数据例如A向B提供数据A这边按规做好物化视图并确定合适的刷新物化视图规则就可以以合规数据形式提供给B了。某些复杂查询应用的频率比较高。
三
物化视图的创建
CREATE MATERIALIZED view 物化视图名称 as 查询语句 with DATA
说明with后接data或者no datano data表示不填充此物化视图仅仅生成数据结构默认是with data
下面就以pgbench的一个表pgbench_accounts为例来说明物化视图的创建和管理
创建物化视图
CREATE MATERIALIZED view vvv as SELECT * FROM pgbench_accounts;
查看物化视图 OK此时如果基表pgbench_accounts 改变了的话物化视图vvv并不会跟随改变因为规定必须是刷新同步pgbench_accounts这个表
修改基本的aid等于48的 abalance值为123456789修改后查询确认是修改了
UPDATE pgbench_accounts set abalance123456789 WHERE aid48
SELECT * from pgbench_accounts where aid48 此时查询物化视图vvv可以看到aid 48 并没有改变 手动刷新该物化视图
refresh MATERIALIZED VIEW vvv with data;还一种刷新是不影响现有物化视图使用的也就是不加锁的并行刷新如果该物化视图比较大的时候
REFRESH MATERIALIZED VIEW CONCURRENTLY vvv 但此时这样刷新会报错
REFRESH MATERIALIZED VIEW CONCURRENTLY vvvERROR: cannot refresh materialized view public.vvv concurrently
HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.
此时需要给物化视图添加一个唯一索引在本例中就给aid添加吧注意也是并行添加索引 CONCURRENTLY
CREATE UNIQUE INDEX CONCURRENTLY vvvv ON vvv(aid) 再次查询可以看到物化视图与基表同步了 四
物化视图的自动刷新
物化视图的自动更新需要安装一些特殊的插件例如Apache iceberg冰山或者是自己手动创建触发器函数触发器这样的形式本例中是触发器函数触发器
触发器-函数的创建触发器 触发后要执行的函数这里自然是刷新物化视图啦
CREATE OR REPLACE FUNCTION update_my_view()
RETURNS TRIGGER AS $$
DECLAREBEGIN-- Update the materialized view here.REFRESH MATERIALIZED VIEW CONCURRENTLY vvv;RETURN NULL;END;
$$ LANGUAGE plpgsql;
触发器的创建此触发器是基于基表的哦
CREATE TRIGGER update_my_view_trigger
AFTER INSERT OR UPDATE OR DELETE ON pgbench_accounts
FOR EACH STATEMENT
EXECUTE PROCEDURE update_my_view();
OK现在可以验证了首先基表更新aid 48 更新为888888删除aid 47
UPDATE pgbench_accounts set abalance888888 WHERE aid48
DELETE from pgbench_accounts where aid47
这个时候查询物化视图可以看到我们并没有执行刷新命令就可以看到物化视图的改变了
pgbench# SELECT * from vvv where aid47;aid | bid | abalance | filler
----------------------------
(0 rows)pgbench# SELECT * from vvv where aid48;aid | bid | abalance | filler
----------------------------------------------------------------------------------------------------------48 | 1 | 888888 |
(1 row)OK自动刷新物化视图是成功的
五
物化视图的修改
更改物化视图基本和更改表一样的语法例如更改物化视图的名称这里需要注意如果有触发器那么触发器函数也应该同时更改否则触发器会报错的哦
ALTER MATERIALIZED VIEW IF EXISTS vvvRENAME TO vvvvvv
UPDATE pgbench_accounts set abalance8888882 WHERE aid48ERROR: relation vvv does not exist
CONTEXT: SQL statement REFRESH MATERIALIZED VIEW CONCURRENTLY vvv
PL/pgSQL function update_my_view() line 5 at SQL statement其它的修改就不一一举例了
ALTER MATERIALIZED VIEW [ IF EXISTS ] nameaction [, ... ]
ALTER MATERIALIZED VIEW nameDEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] nameRENAME [ COLUMN ] column_name TO new_column_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] nameSET SCHEMA new_schema
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name[ OWNED BY role_name [, ... ] ]SET TABLESPACE new_tablespace [ NOWAIT ]
