一文搞懂PostgreSQL物化视图
目录
1 什么是视图
2 什么是物化视图
3 物化视图多表Join举例
4 物化视图的其他特点
5 物化视图和外部表（fdw）配合使用
6 总结
什么是视图
视图(View)：一个名字（视图的名字），对应着一个SQL查询语句。做查询时，把视图的名字当作表名使用，PostgreSQL会使用对应的SQL查询语句替换视图名称，然后做查询，返回最终的结果。
举例：给定一个表employee_info，定义一个视图employee_contact如下：
create view employee_contactas 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_contactas select employee_id, first_name ||' '|| last_name as name, mobile_num, email from employee_infowhere department_id=6;什么是物化视图
物化视图(Materialized View)和视图(View)类似，也是一个视图名字对应一个SQL查询查询语句。不同之处在于：物化视图定义时使用了额外的关键字materialized， 它把结果集保存在起来，查询的时候直接读取保存的结果集，而不必扫描原始表。物化视图的结果集（即物化视图自己的数据）的存储和扫描方式和普通表的方式相同。
下面例子中，定义的2个物化视图：
mat_employee_contact和mat_sales_dept_contact。
create materialized view mat_employee_contactas select employee_id, first_name ||' '|| last_name as name, mobile_num, email from employee_info;create materialized view mat_sales_dept_contactas select employee_id, first_name ||' '|| last_name as name, mobile_num, email from employee_infowhere 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 asselect history_order.order_id, account.first_name ||' ' || last_name as name,history_order.order_id, history_order.amount from account left join history_order onaccount.id=history_order.account_id where account.city_code=7;定义一个物化视图mat_view_7，用于扫描城市代码7的所有历史订单+账号信息。
create materialized view mat_view_7 asselect history_order.order_id, account.first_name ||' ' || last_name as name, history_order.order_id, history_order.amount from account left join history_order onaccount.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_orderselect 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, emailfrom fdw_employee_info;如果针对这个外表定义一个物化视图，就可以把对外表的查询结果保存起来，后续的扫描就不必扫描并加载外部数据了。
create materialized view local_employee_contactas select employee_id, first_name ||' '|| last_name as name, mobile_num, email from fdw_employee_info;
select * from local_employee_contact;总结
使用PostgreSQL时，如果一个视图的查询耗时很大，可以考虑使用物化视图。因为物化视图把结果集以表的方式记录下来，下次查询时候，直接使用顺序扫描返回结果。
如果原始表的数据更新频繁，而物化视图刷新很慢，则不太适合使用物化视图。
最终是否要使用物化视图，以及如何使用物化视图，要结合具体情况决定。
欢迎投