postgresql|数据库|提升查询性能的物化视图解析

前言：

我们一般认为数字的世界是一个虚拟的世界，OK，但我们其实有些需求是和现实世界一模一样的，比如，数据库尤其是关系型数据库，希望在使用的数据库能够更快（查询速度），更高（性能上限更高），更强（并发性能，写能力这些属性），正如奥林匹克的体育精神：更快，更高，更强，但是很可惜，愿望就是愿望了，要想实现这样的愿望需要更多的技术，想法。

那么，毫无疑问的，物化视图是一个专门针对查询性能的关系型数据库内的一个对象。

等等，视图？物化视图？

是的，就是物化视图，我只能告诉大家一个残酷的事实（结论先给了，不服气的接着看），物化视图虽然可以对数据库的查询性能起到一个比较大的提升，但物化视图有很大的局限性，也可以简单的理解为物化视图是一个双刃剑，用好了可以使得数据库系统查询速度飞快，用不好可能会造成系统崩溃这样的惨烈后果。

下面，我将就物化视图的优缺点，适用场景，如何使用物化视图做一个简单的介绍。

一，

物化视图是什么？

###注：基表，也就是基础表，查询结果不是凭空出现的，自然是从一个或者N个表内查询得出的结果，一个或者N个表也就省略称之为基表了

物化视图是介于表和视图之间的一个关系型数据库的对象，可以将它想象成一个查询基表产生的结果集，但这个结果集可以很复杂，可以是多表联查的结果集，可以是一个简单的单表查询结果集。

和普通视图相比，更为关键的是，物化视图是有对应的存放于$PGDATA目录下的物理文件，也就是它不是一个虚幻的虚拟的东西了，而是一个真真切切存在的可操作的对象了，这也是为什么叫物化视图的原因了。

那么，很多同学就会有疑问了：普通视图也是一个基于基表的查询而产生的结果集，为毛要用什么物化视图？有毛病吗？

OK，普通视图是无法添加索引的，而我们都知道索引是可以加快查询速度的，也就是基于合适的索引，我们是可以加快查询效率的，因此，一个设计良好的物化视图的查询速度会远远超过一个设计良好的普通视图

和表相比，物化视图仅仅是一个查询结果集，那自然是没有insert，update这些功能了，也就是说，物化视图不可改变其内的数据。

当然，在navicat里，这个物化视图叫实体化视图，目前只需要明白一点，叫什么都无所吊谓的

二，

物化视图的优缺点和适用场景

OK，现在来总结一下物化视图的优缺点

优点：

提高性能：通过预先计算并存储结果，可以避免在每次查询时都需要执行复杂的联接操作或聚合运算，从而大大提高查询速度。
减少磁盘空间：由于物化视图只存储部分数据，而不必存储所有基础表的所有数据，因此可以减少磁盘空间的需求。
支持快速数据刷新：大多数数据库系统都支持物化视图的快速刷新，可以在较短的时间内更新物化视图中的数据，以便及时反映底层数据的变化。
物化视图可以添加索引，而索引可以有效的提升查询效率

缺点：

需要额外的存储空间：物化视图需要占用额外的磁盘空间来存储其结果集。
更新延迟：由于物化视图通常需要定期刷新，因此在底层数据发生变化后，可能需要一段时间才能反映到物化视图中。因此，如果是经常更新的基表，而对物化视图的准确性有较高的要求的情况下，更新需求会是一个不得不考虑的问题，so，建议基表更新太多的基表不建议使用物化视图
维护复杂性：由于物化视图需要定期刷新，而且在某些情况下还需要执行复杂的计算，因此需要更多的硬件资源提供支持。
不适合高并发环境：在高并发环境下，如果多个用户同时访问物化视图，可能会出现锁竞争问题，影响性能。主要原因是在刷新或者说同步物化视图这样的操作的时候，会有锁的问题。

由于这些优缺点，因此，可以得出物化视图并不是万能的，很有可能是一个双刃剑，物化视图需要在一个适用的环境下才可以使用。
OK，物化视图的适用场景一般为：

基表更新不是非常频繁，这个可以有一些时间上的量化，例如，更新条目在分钟级附近。
相对物化视图来说，并发程度不是太高，多用户同时查询不会影响到物化视图的更新正常完成。
数据库的硬件条件比较高，可以承担物化视图的频繁更新，主要是CPU和内存能够满足物化视图的刷新任务
对外提供数据，例如A向B提供数据，A这边按规做好物化视图，并确定合适的刷新物化视图规则，就可以以合规数据形式提供给B了。
某些复杂查询，应用的频率比较高。

三，

物化视图的创建

CREATE MATERIALIZED view 物化视图名称 as 查询语句 with DATA

说明：with后接data或者no data，no data表示不填充此物化视图，仅仅生成数据结构，默认是with data

下面就以pgbench的一个表pgbench_accounts为例来说明物化视图的创建和管理

创建物化视图

CREATE MATERIALIZED view vvv as SELECT * FROM pgbench_accounts;

查看物化视图：

postgresql|数据库|提升查询性能的物化视图解析-LMLPHP

OK，此时如果基表pgbench_accounts 改变了的话，物化视图vvv并不会跟随改变，因为规定必须是刷新（同步）pgbench_accounts这个表

修改基本的aid等于48的 abalance值为123456789，修改后查询确认是修改了

UPDATE pgbench_accounts set abalance='123456789' WHERE aid='48' 
SELECT * from pgbench_accounts where aid='48'

postgresql|数据库|提升查询性能的物化视图解析-LMLPHP

此时查询物化视图vvv，可以看到aid 48 并没有改变：

postgresql|数据库|提升查询性能的物化视图解析-LMLPHP

手动刷新该物化视图：

refresh MATERIALIZED VIEW  vvv with data;

还一种刷新是不影响现有物化视图使用的，也就是不加锁的并行刷新，如果该物化视图比较大的时候：

REFRESH MATERIALIZED VIEW CONCURRENTLY vvv

但此时这样刷新会报错：

REFRESH MATERIALIZED VIEW CONCURRENTLY vvv
> ERROR:  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)

再次查询可以看到物化视图与基表同步了：

postgresql|数据库|提升查询性能的物化视图解析-LMLPHP

四，

物化视图的自动刷新

物化视图的自动更新需要安装一些特殊的插件例如Apache iceberg（冰山）或者是自己手动创建触发器函数+触发器这样的形式，本例中是触发器函数+触发器

触发器-函数的创建（触发器触发后要执行的函数，这里自然是刷新物化视图啦）：

CREATE OR REPLACE FUNCTION update_my_view()
RETURNS TRIGGER AS $$
DECLARE
    BEGIN
        -- 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 abalance='888888' WHERE aid='48' 
DELETE from pgbench_accounts where aid='47'

这个时候查询物化视图，可以看到我们并没有执行刷新命令就可以看到物化视图的改变了：

pgbench=# SELECT * from vvv where aid='47';
 aid | bid | abalance | filler 
-----+-----+----------+--------
(0 rows)

pgbench=# SELECT * from vvv where aid='48';
 aid | bid | abalance |                                        filler                                        
-----+-----+----------+--------------------------------------------------------------------------------------
  48 |   1 |   888888 |                                                                                     
(1 row)

OK，自动刷新物化视图是成功的

五，

物化视图的修改

更改物化视图基本和更改表一样的语法，例如，更改物化视图的名称，这里需要注意，如果有触发器，那么，触发器函数也应该同时更改，否则触发器会报错的哦：

ALTER MATERIALIZED VIEW IF EXISTS  vvv
     RENAME TO vvvvvv

UPDATE pgbench_accounts set abalance='8888882' WHERE aid='48'
> ERROR:  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 ] name
     action [, ... ]
ALTER MATERIALIZED VIEW name
     DEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
     RENAME [ COLUMN ] column_name TO new_column_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
     SET SCHEMA new_schema
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name
     [ OWNED BY role_name [, ... ] ]
     SET TABLESPACE new_tablespace [ NOWAIT ]

晚风_END