相信大伙肯定使用过视图，那物化视图又是什么呢？
实际上物化视图(materialized View)跟视图(View)类似，也是一个视图名字对应一个 SQL 查询语句。
不同的是，物化视图定义的时候需要使用关键字 materialized，它会把结果集保存起来，查询的时候直接读取保存的结果集，而不需要扫描原始表。
如果说视图是虚拟表，那么物化视图可以说是实体表。
接下来我们来做几个实验看看他们实际的差别是怎么样的。
tpch=# create view test1 as SELECT * FROM orders c where o_orderdate = '1995-05-02';
CREATE VIEW
Time: 3.463 ms
tpch=# create materialized view test2 as SELECT * FROM orders c where o_orderdate = '1995-05-02';
SELECT 3667
Time: 504.359 ms
tpch=# explain (costs off,analyze) select count(1) from test1;
QUERY PLAN
------------------------------------------------------------------------------------------------
Finalize Aggregate (actual time=500.128..501.252 rows=1 loops=1)
-> Gather (actual time=499.998..501.246 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (actual time=492.542..492.543 rows=1 loops=3)
-> Parallel Seq Scan on orders c (actual time=0.343..492.035 rows=1222 loops=3)
Filter: (o_orderdate = '1995-05-02'::date)
Rows Removed by Filter: 2998777
Planning Time: 0.113 ms
Execution Time: 501.343 ms
(10 rows)
Time: 502.113 ms
tpch=# explain (costs off,analyze) select count(1) from test2;
QUERY PLAN
----------------------------------------------------------------------
Aggregate (actual time=0.623..0.625 rows=1 loops=1)
-> Seq Scan on test2 (actual time=0.014..0.364 rows=3667 loops=1)
Planning Time: 0.113 ms
Execution Time: 0.651 ms
(4 rows)
Time: 1.496 ms
tpch=# SELECT pg_relation_filepath('test1');
pg_relation_filepath
----------------------
(1 row)
Time: 0.660 ms
tpch=# SELECT pg_relation_filepath('test2');
pg_relation_filepath
----------------------
base/16918/41807
(1 row)
Time: 0.290 ms通过上面的实验我们得出两个结论：
物化视图是真实存在的，而视图只是一个定义。
创建物化视图的时间比较比视图长，但是执行查询比视图短，因为物化视图需要真实执行然后保存结果集。而且可以看到物化视图是没有Filter，因为它已经存储了这个查询条件的结果。
tpch=# delete from orders where o_orderdate = '1995-05-02' and o_orderkey ='6755';
DELETE 1
Time: 1262.232 ms (00:01.262)
tpch=# select count(1) from test1;
count
-------
3666
(1 row)
Time: 485.321 ms
tpch=# select count(1) from test2;
count
-------
3667
(1 row)
Time: 0.753 ms可以看到我们删除了原表一条数据， 但物化视图却没有更新到。
因为PostgreSQL目前不支持物化视图的自动/增量更新。 如果原始表数据有更改，必须使用命令下面命令对物化视图的数据做全更新（非增量更新）：refresh materialized view xxxxx;
物化视图支持创建索引，但是无法执行 DML 操作，如下图实验所示：
tpch=# create index o_id on test1(o_orderkey);
ERROR: cannot create index on relation "test1"
DETAIL: This operation is not supported for views.
Time: 0.443 ms
tpch=# create index o_id on test2(o_orderkey);
CREATE INDEX
Time: 6.607 ms
tpch=# INSERT INTO test1
tpch-# (o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment)
tpch-# VALUES(6755, 662980, 'O', 28349.44, '1995-05-02', '2-HIGH ', 'Clerk#000002720', 0, 'ully above the asymptotes. even accounts haggle fluf');
INSERT 0 1
tpch=# INSERT INTO test2
(o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment)
VALUES(6755, 662980, 'O', 28349.44, '1995-05-02', '2-HIGH ', 'Clerk#000002720', 0, 'ully above the asymptotes. even accounts haggle fluf');
ERROR: cannot change materialized view "test2"
tpch=# delete from test2 where o_orderkey=6755;
ERROR: cannot change materialized view "test2"
tpch=# delete from test1 where o_orderkey=6755;
DELETE 1
tpch=# update test2 set o_orderkey='123' where o_orderkey='6759';
ERROR: cannot change materialized view "test2"
Time: 1.081 ms
tpch=# update test1 set o_orderkey='123' where o_orderkey='6759';
UPDATE 0
Time: 1292.286 ms (00:01.292)总结
使用PostgreSQL时，如果一个视图的查询耗时很大，可以考虑使用物化视图。因为物化视图把结果集以表的方式记录下来，下次查询时候，直接使用顺序扫描返回结果。
如果原始表的数据更新频繁，而物化视图刷新很慢，则不太适合使用物化视图。
最终是否要使用物化视图，以及如何使用物化视图，要结合具体情况决定。
评论