物化视图其实也是可以优化的，你了解过么？

物化视图应该是我们经常使用的一种数据库对象，另外也是一种对于复杂查询的优化手段。相比于视图，物化视图可以理解为使一个查询基表产生的结果集，是真实存在的一个对象，在磁盘上是需要占用存储磁盘空间的，当然也可以在$PGDATA目录下看到该物理文件。物化视图是静态的，必须被刷新。 既然物化视图是一个特殊的表，那么对于物化视图的优化肯定必不可少的，我相信大家第一反应肯定会想到索引；是的，合理的使用索引确实可以加速物化视图的查询效率，这可能是一件容易让我们忽略的事情。

在物化视图添加索引，对于查询性能的提升是至关重要。由于下面的几个不同的原因，通常也建议为物化视图添加索引。

• 物化视图通常适用于查询较大的数据集，另外索引也可以额外增加性能的提升。
• 即使底层表有索引，这些索引也不会在物化视图中使用。由于物化视图单独存储在磁盘上，因此它需要一个独立的索引。
• 当查询物化视图时，PG的优化器其实是把其当做普通表处理，目前优化器对物化视图时没有进行特殊处理的。对于物化视图没有特殊的查询规划器。索引可以提升普通表的查询速度，当然物化视图也可以利用来提升其查询速度。

视图和物化视图

我们先来简单的回顾一下视图和物化视图的一些特性区别。

通过上面对视图和物化视图的对比，我们也可以清晰的知道两者的区别，在这里我们需要重点看一下物化视图可以独立创建索引的，上面也提到了就是在基表上创建的索引其实对物化视图是不起作用的，因为物化视图是独立的一个数据库对象，如果我们要对物化视图进行优化，那么添加一个合理的索引是可以加快查询速度的。

创建含有索引的物化视图

我利用之前下载的航班相关的数据，对物化视图的功能进行演示。我们想要了解某个机场到达航班详细信息，在这里可以把机场业务表和航班业务表关联的做成物化视图，然后通过机场的编码来查询。

create  MATERIALIZED VIEW mv_arrival_info as 
select flight_id,flight_no,scheduled_departure,scheduled_arrival,
arrival_airport,arr.airport_name as arrival_airport_name,arr.city arrival_city
from flights f join airports arr
on f.arrival_airport = arr.airport_code;

假如我们可能需要通过机场的编码来查询数据，如果不创建索引时，先来观察一下执行计划

explain analyze select * from mv_arrival_info where arrival_airport ='RTW';
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Seq Scan on mv_arrival_info  (cost=0.00..1588.80 rows=477 width=60) (actual time=0.013..6.157 rows=484 loops=1)
   Filter: (arrival_airport = 'RTW'::bpchar)
   Rows Removed by Filter: 65180
 Planning Time: 0.061 ms
 Execution Time: 6.185 ms
(5 rows)

Time: 6.842 ms

然后我们再在物化视图的arrival_airport列上添加一个索引。

CREATE INDEX idx_mv_arrival_info_airport ON mv_arrival_info (arrival_airport);

在物化视图上创建索引和表上创建索引的语法完全相同，另外在Postgres中，支持在物化视图上添加所有主要的索引类型，包括B-tree、hast、GiST、GIN、BRIN，具体详细的就不在这里介绍。我们接着来观察一下添加索引后的执行计划

demo=# explain analyze select * from mv_arrival_info where arrival_airport ='RTW';
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on mv_arrival_info  (cost=7.99..718.17 rows=477 width=60) (actual time=0.208..0.282 rows=484 loops=1)
   Recheck Cond: (arrival_airport = 'RTW'::bpchar)
   Heap Blocks: exact=9
   ->  Bitmap Index Scan on idx_mv_arrival_info_airport  (cost=0.00..7.87 rows=477 width=0) (actual time=0.167..0.167 rows=484 loops=1)
         Index Cond: (arrival_airport = 'RTW'::bpchar)
 Planning Time: 0.276 ms
 Execution Time: 0.423 ms
(7 rows)

通过最终的执行结果我们也可以了解到，确实使用到刚才创建的索引，而且查询性能相比之前也大幅度提升了，符合我们的预期结果。至于说有没有可能用到基表的索引，我觉的你细细思考一下，相信肯定是不会用到索引的结论。

刷新物化视图

物化视图中的数据静态的，因此当我们要更新数据时，必须刷新物化视图。PG有两种方式来刷新物化视图，分别为全量刷新和并发刷新。

全量刷新

全量刷新会替换物化视图中的内容，但是之前创建索引仍然会存在，PG将在刷新物化视图后，然后对新的数据进行重新创建索引，重建索引的时长也与数据量的多少有关系。在全量刷新的过程中，由于数据库的进程需要持有ACCESS EXCLUSIVE锁，防止任何的读和写操作，直到刷新完成。这种刷新方式通常是最快速的，但是通常不适用于有实时读取的生产系统。

REFRESH MATERIALIZED VIEW mv_arrival_info;

并发刷新（唯一索引）

并发刷新可以在不锁表的情况刷新物化视图， 并允许刷新的过程中进行读写操作，由于采用增量的方法，通常比全量刷新慢一些，但是允许这个过程读取数据，这可能对于生产系统来说比较友好。

并发刷新要求在物化视图必须至少存在一个唯一索引。我们之前添加的btree索引是非唯一索引，因此在这里我们需要在flight_id列上创建一个唯一索引。

CREATE UNIQUE INDEX idx_mv_arrival_info_flight_id ON mv_arrival_info(flight_id);

在物化视图上创建唯一索引后，就可以执行并发刷新了。

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_arrival_info;

如果在物化视图只有非唯一索引，那么我们就不能使用并发刷新的功能，只能通过使用全量刷新来更新物化视图。

总结

当我们在使用索引的时候，总是需要考虑各方面的因素，在物化视图上创建也不可避免需要考虑这些因素，比如查询条件、刷新频率、字段的选择率，综合考虑来创建索引，从而最大限度地提高效率。最后我们再来总结一下：

• 即使基表中有索引，也必须在物化视图中重新创建索引。
• 索引对于物化视图性能是帮助的。
• 物化视图中的数据是静态的，需要我们定期刷新。
• 全量刷新会阻塞读写物化视图，而并发刷新不会。
• 并发刷新时，必须有一个唯一索引
• – / END / –

可以通过下面的方式联系我

• 微信公众号：@墨竹札记
• 墨天轮：@墨竹
• 微信：wshf395062788
• PGFans：@墨竹

如果这篇文章为你带来了灵感或启发，就请帮忙点赞、收藏、转发；如果文章中不严谨或者错漏之处，请及时评论指正。非常感谢！