服务器之家

服务器之家 > 正文

Oracle数据库中基本的查询优化与子查询优化讲解

时间:2019-12-25 14:17     来源/作者:littlechang

1. 查询条件合理排序

Oracle采用自下而上的顺序解析WHERE字据,从优化性能角度考虑,建议将那些可以过滤掉大量记录行的条件写在WHERE子句的末尾,而将表

之间的连接条件置于其他WHERE子句之前,即对易排查的条件先做判断处理,这样在过滤掉尽可能多的记录后再进行等值连接,可以提高检索效率。

例如:

?
1
2
3
4
5
SELECT empno, ename, job, sal, dept.deptno, dname
 
FROM emp, dept
 
WHERE emp.deptno = dept.deptno AND emp.deptno = 20;

要比下述语句的查询效率高一些:

?
1
2
3
4
5
SELETE empno, ename, job, sal, dept.deptno, dname
 
FROM emp, dept
 
WHERE emp.deptno = 20 AND emp.deptno = dept.deptno;

2.连接中使用表别名

在进行连接查询时,建议在SQL语句中使用表的别名, 并把别名前缀与每个字段上。这样可以减少解析的时间,并可避免因字段名存在歧义(使用多个

表中出现的同名字段)而导致的语法错误。例如下述语句:

?
1
2
3
4
5
SELECT e.empno, e.ename, e.job, e.sal, e.deptno, d.name
 
FROM emp e, dept d
 
WHERE e.deptno = d.deptno AND e.deptno = 20;

3.用EXISTS替换DISTINCT

在进行一对多关系的表间连接查询时,如果要剔除结果中的重复行,可以考虑使用EXISTS(结合子查询)替换DISTINCT。

例如:

?
1
SELECT deptno, dname FROM dept d WHERE EXIST (SELECT 'y' FROM emp e WHERE e.deptno = d.deptno);

4.用WHERE替换HAVING

由于SELECT语句的执行顺序为:先WHERE子句, 在GROUP BY 子句, 然后SELECT查询, 再后HAVINT子句,最后是ORDER BY子句,因此在进行分组

查询时,如果过滤条件不涉及分组计算,则应该使用WHERE语句替换HAVING指定的过滤条件

例如:

?
1
2
3
4
5
SELECT deptno, avg(sal) FROM emp
 
WHERE deptno IN (10, 20)
 
GROUP BY deptno;

效率会高于下面

?
1
2
3
4
5
SELECT deptno, avg(sal) FROM emp
 
WHERE deptno IN (10, 20)
 
GROUP BY deptno;

当然, 如果分组查询的过滤条件设计分组计算,就只能在HAVING子句中指定了。

5.子查询优化

1) 在数据量大时,子查询的性能会受到比较大的影响;

2) 在子查询中使用了函数to_char(add_months(to_date(tb.rptdate,'yyyymm'),12),'yyyymm'),对大数据量查询时,执行函数也会对系统产生比较大的影响。

下面来看一个例子:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
createor replace procedure Rpt_Dyhgl_year(
 rptyear      in varchar2, --年yyyy
 --rpttype      in varchar2,  --报表类型,1年报,2季报
 ref_cursor      out sys_refcursor --返回游标
)
As
 -- 变量声明
 。。。。。
begin
  delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = rptyear and RPTTYPE = '1';
  commit;
 --查询原始数据
 v_sql := 'select rptdate, jcdlb, jcdlbName, sum(nvl(runtime,0)) sumruntime,sum(nvl(buhegeTime,0)) buhegeTime, count(jcdcount) jcdcount,
    sum(nvl(tongbiRuntime,0)) tongbiRuntime, sum(nvl(tongbiBhg,0))tongbiBhg from (
  select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1 jcdcount,
    (select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime,
    (select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg,
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb,
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName
    from ywtj_rpt_dyhgl_month r where rptdate = :rptYear)
  group by rptdate, jcdlb,  jcdlbName 
  order by rptdate ,jcdlb ';
  -- 插入新数据
  v_sqlRptInsert := 'Insert IntoYWTJ_RPT_DYHGL_YEAR (RPTYEAR,RPTDATE,RPTTYPE) values(:RPTYEAR,:RPTDATE,:RPTTYPE)';
  v_sqlRptUpdate := '';
  -- 计算1--12月的当月数据
  for rptmonth in 1..12 loop
   execute immediate v_sqlRptInsertusing rptyear, rptmonth, '1';
   commit;
   open v_cur for v_sql using rptyear ||LPAD(rptmonth, 2, '0');
   loop
     fetch v_cur intov_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg;
     exit when v_cur%notfound;
     --计算合格率
     。。。。。。
     -- 更新当月数据
     。。。。。。
    end loop;
    close v_cur;
  end loop;
  -- 更新累计数据
  。。。。。。
  commit;
  -- 计算综合合格率
  open ref_cursor for select * fromYWTJ_RPT_DYHGL_YEAR r where r.rptyear = rptyear and r.rpttype = 1 order byr.rptdate;
end Rpt_Dyhgl_year;

功能运行正常,查询时间大约30-40秒。

然后,把报表部署到运行环境中后,验证功能时,发现2、3分钟报表没有出来!多次尝试,后来终于出来结果了:940多秒,15分钟多!这样用户肯定是不能接受的!使用pl/sql dev的性能分析对Rpt_Dyhgl_year的执行过程进行分析,发现几乎所有的时间都化在了

?
1
fetch v_cur into v_rptDate,v_jcdlb,v_jcdName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount,v_tongbiRuntime,v_tongbiBhg;

(期间把过程转成程序执行,性能分析时发现,主要时间都是由dr.Read()占用,和对过程的分析一致。)

那问题应该是在fetch对应的查询上,把

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
v_sql :='select rptdate, jcdlb, jcdlbName,sum(nvl(runtime,0)) sumruntime, sum(nvl(buhegeTime,0)) buhegeTime,count(jcdcount) jcdcount,
 
    sum(nvl(tongbiRuntime,0)) tongbiRuntime, sum(nvl(tongbiBhg,0))tongbiBhg from (
 
  select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1 jcdcount,
 
    (select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime,
 
    (select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg,
 
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb,
 
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName
 
    from ywtj_rpt_dyhgl_month r where rptdate = :rptYear)
 
  group by rptdate, jcdlb,  jcdlbName 
 
  order by rptdate ,jcdlb ';      --①

单独拿出来执行,大约5.7秒多。
再把其中的子查询:

?
1
2
3
4
5
6
7
8
9
10
11
selectrptdate, runtime, r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1jcdcount,
 
    (select runtime fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiRuntime,
 
    (select chaoshangxian + chaoxiaxian fromywtj_rpt_dyhgl_month tb whereto_char(add_months(to_date(tb.rptdate,''yyyymm''),12),''yyyymm'') = r.rptdateand tb.objid = r.objid) tongbiBhg,
 
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb,
 
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName
 
    from ywtj_rpt_dyhgl_month r where rptdate =:rptYear;  --②

单独拿出来执行,速度很快,但返回的数据量很大。如果再对其循环分析,估计性能也好不到哪去,先行放弃这种尝试。

把查询②中的tongbiRuntime和tongbiBhg两个字段子查询去掉,执行速度更快,同样再把①中的tongbiRuntime和tongbiBhg两个字段子查询去掉,执行速度提高很多,时间约0.157秒。

这样速度就提高了很多,但少了去年同比的数据,那就分成两个查询!按这个思路下来,最后得到的过程如下(只保留主要部分):

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
createor replace procedure Rpt_Dyhgl_year(
--电压合格率-年
 v_rptyear      in varchar2, --年yyyy
 --rpttype      in varchar2,  --报表类型,1年报,2季报
 ref_cursor      out sys_refcursor --返回游标
)
as
 -- 变量声明
 .......
begin
  delete from YWTJ_RPT_DYHGL_YEAR where RPTYEAR = v_rptyear and RPTTYPE = '1';
  commit;
 --查询原始数据
 v_sql := 'select rptdate, jcdlb, jcdlbName, sum(nvl(runtime,0)) sumruntime,sum(nvl(buhegeTime,0)) buhegeTime, count(jcdcount) jcdcount
    from ( select rptdate, runtime,r.chaoshangxian+r.chaoxiaxian buhegeTime, r.objid, 1 jcdcount,
    (select jcdlb from jczl_dwjg d whered.objid = r.objid ) jcdlb,
    (select jcdlbname from dic_jcdlb wherejcdlbid = (select jcdlb from jczl_dwjg where objid = r.objid)) jcdlbName
    from ywtj_rpt_dyhgl_month r where rptdate = :rptYear)
  group by rptdate, jcdlb,  jcdlbName
  order by rptdate ,jcdlb ';
  -- 插入新数据
  v_sqlRptInsert := 'Insert IntoYWTJ_RPT_DYHGL_YEAR (RPTYEAR,RPTDATE,RPTTYPE) values(:RPTYEAR,:RPTDATE,:RPTTYPE)';
  v_sqlRptUpdate := '';
 
  -- 计算1--12月的当月数据
  for rptmonth in 1..12 loop
   -- 打开游标开始计算合格率
   open v_cur for v_sql using v_rptyear||LPAD(rptmonth, 2, '0');
   loop
     fetch v_cur intov_rptDate,v_jcdlb,v_jcdLbName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount;
     exit when v_cur%notfound;
     --计算合格率
     if v_sumRuntime <> 0 then
      v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime *100;
     end if;
     -- 更新当月数据
     ........
    end loop;
    close v_cur;
   -- 打开游标开始计算同比合格率
   open v_cur for v_sql using (v_rptyear-1)|| LPAD(rptmonth, 2, '0');
   loop
     fetch v_cur into v_rptDate,v_jcdlb,v_jcdLbName,v_sumRuntime,v_sumbuhegeTime,v_jcdCount;
     exit when v_cur%notfound;
     --计算合格率
     if v_sumRuntime <> 0 then
      v_hgl := (v_sumRuntime-v_sumbuhegeTime)/v_sumRuntime*100;
     end if;
     -- 更新同比数据
     .........
    end loop;
    close v_cur;
  end loop;
 
  -- 更新累计数据
  ......... 
  -- 计算综合合格率
  ........
 
  open ref_cursor for select * fromYWTJ_RPT_DYHGL_YEAR r where r.rptyear = v_rptyear and r.rpttype = 1 order byr.rptdate;
end Rpt_Dyhgl_year;

经验证,执行速度大约在一秒多,也就是说性能从原来的900多秒提高到1秒,提高差不多三个数量级。

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
配置IIS网站web服务器的安全策略配置解决方案
配置IIS网站web服务器的安全策略配置解决方案 2019-05-23
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
返回顶部