PostgreSQL物化视图详解

开源软件联盟PostgreSQL分会 2019-09-29

10130

作者：姜瑞海

姜瑞海

中国PG分会认证专家

PostgreSQL资深内核研发工程师

1 什么是视图

2 什么是物化视图

3 物化视图多表Join举例

4 物化视图的其他特点

5 物化视图和外部表（fdw）配合使用

6 总结

什么是视图

视图(View)：一个名字（视图的名字），对应着一个SQL查询语句。做查询时，把视图的名字当作表名使用，PostgreSQL会使用对应的SQL查询语句替换视图名称，然后做查询，返回最终的结果。

举例：给定一个表employee_info，定义一个视图employee_contact如下：

create view employee_contact
as select employee_id, 
   first_name ||' '|| last_name as name, 
   mobile_num, 
   email 
from employee_info;

该视图的作用是：把表employee_info(公司员工信息表)的字段做过滤，只取其中的5个字段，拼成4个字段，形成一个公司内部通信录。

其中，表employee_info的定义如下：

create table employee_info 
(
    employee_id  integer not null,
    first_name  varchar(100),
    last_name   varchar(100),
    email       varchar(50),
    mobile_num  varchar(20),
    hire_date   timestamp without time zone not null,
    salary      int,
    bank_number varchar(20),
    department_id  smallint not null,
    supervisor_id  integer,
    password    varchar(50)
);

应用程序做查询时候，只需要查询employee_contact即可：

select * from employee_contact;

对于应用程序来说，只知道employee_contact，而不必知道employee_info，甚至不必知道employee_contact是表还是视图。

下面是另外一个例子，定义了另外一个视图sales_dept_contact，表示销售部门的通讯录。

create view sales_dept_contact
as select employee_id, 
          first_name ||' '|| last_name as name, 
          mobile_num, 
          email 
from employee_info
where department_id=6;

什么是物化视图

物化视图(Materialized View)和视图(View)类似，也是一个视图名字对应一个SQL查询查询语句。不同之处在于：物化视图定义时使用了额外的关键字materialized，它把结果集保存在起来，查询的时候直接读取保存的结果集，而不必扫描原始表。物化视图的结果集（即物化视图自己的数据）的存储和扫描方式和普通表的方式相同。

下面例子中，定义的2个物化视图：

mat_employee_contact和mat_sales_dept_contact。

create materialized view mat_employee_contact
as select employee_id, 
   first_name ||' '|| last_name as name, 
   mobile_num, 
   email 
from employee_info;

create materialized view mat_sales_dept_contact
as select employee_id, 
          first_name ||' '|| last_name as name, 
          mobile_num, 
          email 
from employee_info
where department_id=6;

对这2个视图做查询时，扫描的是物化视图自己的数据，而不是原表数据。

select * from mat_employee_contact;
select * from mat_sales_dept_contact;

物化视图多表Join举例

视图/物化视图都可以对多个表做Join，只要是一个正常的SQL查询即可。

下面例子中使用了2个表: account和history_order。其中account表示购物网站的用户，history_order表示历史订单。

create table account (
    id int,
    first_name varchar(100),
    last_name varchar(100),
    address varchar(100),
    mobile_phone varchar(50),
    email varchar(100),
    city_code int
);


create table history_order (
    order_id int,
    account_id int,
    amount numeric(13,2),
    status smallint
);

定义一个普通视图view_7，用于扫描城市代码7的所有历史订单+账号信息。

create view view_7 as
select history_order.order_id, 
account.first_name ||' ' || last_name as name,
history_order.order_id, history_order.amount 
from account left join history_order on
account.id=history_order.account_id 
where account.city_code=7;

定义一个物化视图mat_view_7，用于扫描城市代码7的所有历史订单+账号信息。

create materialized view mat_view_7 as
select history_order.order_id, 
account.first_name ||' ' || last_name as name, 
history_order.order_id, history_order.amount 
from account left join history_order on
account.id=history_order.account_id 
where account.city_code=7;

加载数据：1千个账号，1百万条历史订单。

insert into account


select generate_series(1,1000)            ,
       'first-' || MD5(random()::text)     ,
       'last-' || MD5(random()::text)     , 
       'address-' || MD5(random()::text)  ,


        '('
        || (random() * 9)::INT
        || (random() * 9)::INT 
        || (random() * 9)::INT
        || ') ' 
        || (random() * 9)::INT
        || (random() * 9)::INT
        || (random() * 9)::INT
        || '-'
        || (random() * 9)::INT
        || (random() * 9)::INT
        || (random() * 9)::INT
        || (random() * 9)::INT   ,
       'person' || generate_series(1,10) || '@example.com' ,
        (random() * 1000)::INT;


insert into history_order
select  generate_series(1,1000000)  , 
        floor(random() * 100)       ,
        (random() * 10000)          ,
        (random() *10)::INT         ;

比较查询速度：普通视图耗时201.071 ms，物化视图耗时11.014 ms。

由于物化视图事先把结果集保存起来，所以查询的时候不必扫描2个物理表并做join，所以返回速度更快。

postgres=# select sum(amount) from view_7;
     sum     
-------------
 49982781.69
(1 row)


Time: 201.071 ms


postgres=# select sum(amount) from mat_view_7;
     sum     
-------------
 49982781.69
(1 row)


Time: 11.014 ms

物化视图的其他特点

1. 物化视图的数据更新

如果原始表数据有更改，必须使用命令下面命令对物化视图的数据做全更新（非增量更新）：refresh materialized view mat_view_7;

PostgreSQL目前不支持物化视图的自动/增量更新；但是社区里有开发者在尝试实现。

2. 物化视图支持索引

create index mat_index mat_view_7 ( order_id );

3. 物化视图不支持数据插入

下面的insert是不能用的。

insert into mat_view_7 values (...);

物化视图和外部表配合使用

假设PostgreSQL中定义了一个外表(Foreign Table)：fdw_employee_info，该表可能存在于外部的一个数据库，也可能是一个LDAP服务器，或者是CSV，XML。扫描该表时，可能会很慢。

SELECT employee_id, 
   first_name ||' '|| last_name as name, 
   mobile_num, 
   email
from fdw_employee_info;

如果针对这个外表定义一个物化视图，就可以把对外表的查询结果保存起来，后续的扫描就不必扫描并加载外部数据了。

create materialized view local_employee_contact
as select employee_id, 
   first_name ||' '|| last_name as name, 
   mobile_num, 
   email 
from fdw_employee_info;


select * from local_employee_contact;

总结

使用PostgreSQL时，如果一个视图的查询耗时很大，可以考虑使用物化视图。因为物化视图把结果集以表的方式记录下来，下次查询时候，直接使用顺序扫描返回结果。

如果原始表的数据更新频繁，而物化视图刷新很慢，则不太适合使用物化视图。

最终是否要使用物化视图，以及如何使用物化视图，要结合具体情况决定。

欢迎投

中国开源软件推进联盟PostgreSQL分会，欢迎大家积极投稿，向PGer分享自己的实践经验、心得体会，共建PG中国生态。

投稿邮箱：

press@postgresqlchina.com

postgresql

最后修改时间：2019-11-06 10:50:28

文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL物化视图详解

评论