postgres中的视图和物化视图

最新推荐文章于 2024-11-15 00:51:30 发布

转载最新推荐文章于 2024-11-15 00:51:30 发布 · 580 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/336525

文章标签：

#数据库

本文对比了PostgreSQL中的视图与物化视图的性能差异，并详细介绍了物化视图的数据填充及更新机制。通过查询计划的对比显示，物化视图能提供更快的查询速度，适用于数据实时性要求不高的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

视图和物化视图区别

postgres中的视图和mysql中的视图是一样的，在查询的时候进行扫描子表的操作，而物化视图则是实实在在地将数据存成一张表。说说版本，物化视图是在9.3 之后才有的逻辑。

比较下视图和物化视图的性能

创建两个表

CREATE TABLE teacher (
    id int NOT NULL,
    sname varchar(100)
);

CREATE TABLE student (
    sid int NOT NULL,
    teacher_id int NOT NULL DEFAULT 0,
    tname varchar(100)
);

创建一个视图

CREATE OR REPLACE VIEW student_view AS
SELECT  *
   FROM student
   LEFT JOIN teacher 
   ON student.teacher_id = teacher.id;

创建一个物化视图

CREATE MATERIALIZED VIEW student_view_m AS
SELECT  *
   FROM student
   LEFT JOIN teacher 
   ON student.teacher_id = teacher.id;

进行查询explain:

master=> explain select * from student_view;
                               QUERY PLAN
------------------------------------------------------------------------
 Hash Right Join  (cost=16.98..48.34 rows=496 width=448)
   Hash Cond: (teacher.id = student.teacher_id)
   ->  Seq Scan on teacher  (cost=0.00..13.20 rows=320 width=222)
   ->  Hash  (cost=13.10..13.10 rows=310 width=226)
         ->  Seq Scan on student  (cost=0.00..13.10 rows=310 width=226)
(5 rows)

master=> explain select * from student_view_m;
                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan on student_view_m  (cost=0.00..11.70 rows=170 width=448)
(1 row)

可以看出，student_view去每个表中进行查询，而student_view_m 直接去视图表查询，而物化视图的查询效率确确实实高于视图不少。

物化视图的数据填充

物化视图既然是一个实实在在存在的表，它就需要有数据填充过程，数据填充的命令是REFRESH MATERIALIZED VIEW

master=> \h REFRESH
Command:     REFRESH MATERIALIZED VIEW
Description: replace the contents of a materialized view
Syntax:
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
    [ WITH [ NO ] DATA ]

这里有个注意的，如果你的psql是9.3的，那么你查看帮助文档就只会看到：

master=> \h REFRESH
Command:     REFRESH MATERIALIZED VIEW
Description: replace the contents of a materialized view
Syntax:
REFRESH MATERIALIZED VIEW name
    [ WITH [ NO ] DATA ]

这里就引入说postgres的更新数据库有两种方式，一种是全量更新，一种是增量更新，增量更新是在REFRESH的时候增加一个CONCURRENTLY参数。而增量更新是9.4才加入的操作。

那么哪种更新快呢？答案是全量更新，增量更新做的操作是将当前视图表中的数据和query中的数据做一个join操作，然后才将差量做填充。

但是全量更新会阻塞select操作，就是说，你全量更新的过程中，所有对视图的select操作都会被阻塞，而增量更新却不会这样。