近期项目中遇到有关报表查询sql速度过慢的问题,由于sql是多个表相关联的查询结果,并且数据量过大,所以导致查询速度过慢。为此,采用物化视图来提高查询速度。
1、物化视图概述
物化视图是包括一个查询结果的数据库对象,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。
物化视图,说白了,就是物理表,只不过这张表通过oracle的内部机制可以定期更新,将一些大的耗时的表连接用物化视图实现,会提高查询的效率。
(1) 创建方式:Build Immediate 和Build Deferred两种。Build Immediate 是在创建物化视图的时候就生成数据,而Build Deferred则在创建时不生成数据,以后根据需要再生成数据。默认为Build Immediate 。
(2)查询重写:Enable Query Rewrite和Disable Query Rewrite两种。
分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为Disable Query Rewrite。
(3)刷新:指当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种:On Demand和On Commit。
On Demand指物化视图在用户需要的时候进行刷新,可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新,也可以通过JOB定时进行刷新。On Commit指物化视图在对基表的DML操作提交的同时进行刷新。
刷新的方法有四种:Fast 、Complete 、Force和Never。
Fast 刷新采用增量刷新,只刷新自上次刷新以后进行的修改。Complete 刷新对整个物化视图进行完全的刷新。如果选择Force方式,则Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用Complete 的方式。Never指物化视图不进行任何刷新。默认值是Force On Demand。
Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。
由于本次项目需要,决定采用Complete刷新方式,所以就没有使用物化视图日志功能。
大概了解了物化视图是什么以后,我们就开始通过使用物化视图来实现提高查询速度的功能啦~
2、功能实现
(1) 创建物化视图
采用demand刷新模式,Complete刷新方法,创建视图时刷新数据,后每天凌晨一点全表刷新数据。
CREATE MATERIALIZED VIEW MV_DATA
REFRESH COMPLETE
ON DEMAND
--第一次刷新时间
START WITH SYSDATE
--每天凌晨一点刷新
NEXT TRUNC(sysdate+1)+1/24
WITH PRIMARY KEY
DISABLE QUERY REWRITE AS
<查询sql>
(2) java后台 mybatis通过视图获取报表数据
<select id="queryDataList" resultType="com.dataVo" parameterType="map">
SELECT * FROM MV_DATA mvd
WHERE 1 = 1
AND
<画面过滤条件>
</select>
(3) 获取物化视图上次刷新时间
<!-- 上次刷新时间 -->
<select id="getLastRefreshDate" resultType="java.util.Date">
SELECT
last_refresh_date
FROM
user_mviews
WHERE
mview_name = 'MV_DATA'
</select>
(4) 画面添加手动刷新物化视图的操作
由于java无法直接刷新物化视图,所以最后解决方案采用,在数据库创建存储过程,在存储过程中刷新物化视图。
包中追加存储过程方法
CREATE OR REPLACE
PACKAGE "REPORT" AS
PROCEDURE P_MV_DATA;
END;
创建存储过程
CREATE OR REPLACE
package body REPORT is
PROCEDURE P_MV_DATAIS
BEGIN
DBMS_MVIEW.REFRESH ( list => 'MV_DATA', Method => 'COMPLETE', refresh_after_errors => True
);
END;
end;
在mapper.xml中就可以调用存储过程啦~
<select id="refleshData" statementType="CALLABLE" >
{call REPORT.P_MV_DATA}
</select>
(5) 删除物化视图
DROP MATERIALIZED VIEW MV_DATA;
(6) 物化视图创建索引
create index IDX_MV_DATA_ID on MV_DATA(id);
3、有待研究~
物化视图可以进行分区