一、什么是物化视图
物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对普通视图的查询,数据库都实际上转换为视图SQL语句的查询,这样对整体查询性能的提高,并没有实质上的好处。
二、与普通视图对比
类型\对比项 |
物理结构 |
时效性 |
有无索引 |
视图 |
虚拟表 |
实时 |
无索引 |
物化视图 |
物理表 |
非实时 |
可以创建索引 |
三、物化视图的作用
用于预先计算,并保存表连接或表聚集等耗时较多操作的结果。以便于在执行查询时,避免在基表上进行这些耗时的操作(基表上进行耗时操作是不是会影响其他用户使用这个基表的效率,毕竟基表查询肯定很频繁?或者单纯从Oracle性能角度出发,希望利用物化视图减少基表非常耗时的操作,从而提升数据库性能?),从而快速得到结果。
四、物化视图SQL脚本
4.1 Oracle
4.1.1 创建
-- 创建视图 create materialized view [view_name] refresh [fast|complete|force] [ on [commit|demand] | start with (start_time) next (next_time) ] as {创建物化视图时用的查询语句} -- 创建索引 create unique index index_name on view_name (列名1,列名2...);
4.1.2 修改
-- 修改视图 alter materialized view [view_name] refresh [fast|complete|force] [ on [commit|demand] | start with (start_time) next (next_time) ] -- 修改索引 alter materialized view mv_name add constraint
4.1.3 删除
drop materialized view [if exists] view_name;
4.1.4 查询
select * from mv_name;
4.2 PostgreSQL
4.2.1 创建
-- 创建视图 CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] [ USING method ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] AS query [ WITH [ NO ] DATA ] -- 创建索引 create unique index index_name on view_name (列名1,列名2...);
4.2.2 修改
-- 修改视图 ALTER MATERIALIZED VIEW [ IF EXISTS ] name action [, ... ] ALTER MATERIALIZED VIEW name [ NO ] DEPENDS ON EXTENSION extension_name ALTER MATERIALIZED VIEW [ IF EXISTS ] name RENAME [ COLUMN ] column_name TO new_column_name ALTER MATERIALIZED VIEW [ IF EXISTS ] name RENAME TO new_name ALTER MATERIALIZED VIEW [ IF EXISTS ] name SET SCHEMA new_schema ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] SET TABLESPACE new_tablespace [ NOWAIT ] where action is one of: ALTER [ COLUMN ] column_name SET STATISTICS integer ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] ) ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } ALTER [ COLUMN ] column_name SET COMPRESSION compression_method CLUSTER ON index_name SET WITHOUT CLUSTER SET ACCESS METHOD new_access_method SET TABLESPACE new_tablespace SET ( storage_parameter [= value] [, ... ] ) RESET ( storage_parameter [, ... ] ) OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }--
4.2.3 删除
DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ];
4.2.4 查询
select * from mv_name;
五、物化视图刷新
5.1 Oracle
语法:DBMS_SNAPSHOT.REFRESH( 'v_materialized_foo_tbl','f');
(默认刷新方式是force on demand)
5.1.1 刷新模式
- fast:一种增量刷新,在上次刷新的基础上,对此次在基表做的DML修改进行刷新。只刷新上次刷新以后做的修改;
- complete:完全刷新。是对整个物化视图进行的刷新;
- force:Oracle在刷新时会判断是否可以采用fast刷新,如果不可以会采用complete刷新(肯定会刷新。若上次刷新之后的操作丢失,无法做fast刷新,于是做全表刷新)
- never:不会使用刷新机制刷新
5.1.2 刷新时机
- on demand:在用户需要刷新的时候刷新,这里就要求用户自己动手去刷新数据了(也可以使用job定时刷新);
- on commit:当主表中有数据提交的时候,立即刷新MV中的数据;
- start … next …:从指定的时间开始,每隔一段时间(由next指定)就刷新一次;
5.2 PostgreSQL
语法:REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ]
(默认刷新方式是with data)
5.2.1 刷新模式
1.全量更新: 直接去基础表里面查询数据,刷新过程中会对该物化视图的所有的select操作阻塞,但刷新效率快。
2.增量更新(concurrently):刷新要具备有唯一字段索引,将基础表查询出来的数据和现在物化视图表进行对比,填充差量数据,但刷新过程中不会对该物化视图的select操作进行阻塞,但刷新比全量更新慢。
5.2.2 刷新设置
- with data:创建物化视图后,视图可查询数据;
- with no data:创建物化视图后,如果无数据则视图不可查询;
六、注意事项
- 不要处理运算结果,因为可能有其它功能需要做聚合运算;
- 需要保存原SQL,如果数据库格式化SQL,会导致很难读懂和修改;
[参考文献]: