当前位置: 萬仟网 > IT编程>数据库>其他数据库 > oracle性能优化(项目中的一个sql优化的简单记录)

oracle性能优化(项目中的一个sql优化的简单记录)

2019年12月01日 15:14  | 萬仟网IT编程  | 我要评论

在项目中,写的sql主要以查询为主,但是数据量一大,就会突出sql性能优化的重要性。其实在数据量2000w以内,可以考虑索引,但超过2000w了,就要考虑分库分表这些了。本文主要记录在实际项目中,一个需要查询很慢的sql的优化过程,如果有更好的方案,请在下面留言交流。

很多文章都有关于sql优化的方法,这里就不一一陈述了。如果有需要可以查看博客:

 

select t.yhbh,
       (select name from dim_region where code = substr(t.gddwbm, 0, 4)) gddwmc,
       (select name from dim_region where code = t.gddwbm) fjmc,
       t.dfny,
       t.yhmc,
       t.yddz,
       (select name from dim_electricity_type where code = t.yhlbdm) ydlbmc
  from (select distinct t.yhbh,
                        decode(t.gddwbm,
                               null,
                               '0000',
                               decode(t.gddwbm, '09', '0000', t.gddwbm)) as gddwbm,
                        t.bbny as dfny,
                        t.yhlbdm as yhlbdm,
                        t.yhmc,
                        t2.yddz
          from v_temp_table_jhcbhstj_history t, tmp_kh_ydkh t2
         where t.yhbh = t2.yhbh(+)
           and not exists (select 1
                  from djhjsl_lsb_fz_history b
                 where b.bbny = t.bbny
                   and b.yhbh = t.yhbh
                   and b.gddwbm = t.gddwbm
                   and b.yhlbdm = t.yhlbdm
                   and b.zdcbzhs <> '0')
) t 
where substr(t.gddwbm, 0, 4) = '0946' 
  and t.dfny = '201911'

这个是我的sql脚本。其实这个脚本一点都不复杂。其中v_temp_table_jhcbhstj_historydjhjsl_lsb_fz_history每个月增加330万,目前有1960多万, tmp_kh_ydkh表有330多万。dim_region dim_electricity_type 是两个数据字典项表。

在没有索引的情况下,这个脚本执行需要30s,看到执行过程,现在都是全表扫描的。接下来开始优化。

1.修改脚本的查询,将外层的查询条件放到里面,减少数据量。

select t.yhbh,
       (select name from dim_region where code = substr(t.gddwbm, 0, 4)) gddwmc,
       (select name from dim_region where code = t.gddwbm) fjmc,
       t.dfny,
       t.yhmc,
       t.yddz,
       (select name from dim_electricity_type where code = t.yhlbdm) ydlbmc
  from (select distinct t.yhbh,
                        decode(t.gddwbm,
                               null,
                               '0000',
                               decode(t.gddwbm, '09', '0000', t.gddwbm)) as gddwbm,
                        t.bbny as dfny,
                        t.yhlbdm as yhlbdm,
                        t.yhmc,
                        t2.yddz
          from v_temp_table_jhcbhstj_history t, tmp_kh_ydkh t2
         where t.yhbh = t2.yhbh(+)
           and not exists (select 1
                  from djhjsl_lsb_fz_history b
                 where b.bbny = t.bbny
                   and b.yhbh = t.yhbh
                   and b.gddwbm = t.gddwbm
                   and b.yhlbdm = t.yhlbdm
                   and b.zdcbzhs <> '0')
            and substr(t.gddwbm, 0, 4) = '0946' 
            and t.bbny = '201911'
) t 

2.对三个表都建上索引

v_temp_table_jhcbhstj_history根据dfnysubstr(t.gddwbm, 0, 4)建上联合索引。

create index idx_tmp_jhcbhstj_history_union on v_temp_table_jhcbhstj_history(bbny,substr(gddwbm, 0, 4));

tmp_kh_ydkh表,使用了关联,所以需要对yhbh建个索引

create index idx_yhbh_kh on tmp_kh_ydkh (yhbh);

对于djhjsl_lsb_fz_history表,在not exists里面,会全表扫描这个表,现在对他建立联合索引试试。

create index idx_djhjsl_fz_history_union on v_temp_table_jhcbhstj_history(bbny,yhbh,gddwbm,yhlbdm);

查看oracle的执行计划,建立联合索引,并没有让这个表走索引,还是在全表扫描的,但是查询已经提升到9s了。

接下来对分别对这四个字段建立索引:

create index idx_djhjsl_fz_history_bbny on djhjsl_lsb_fz_history (bbny);
create index idx_djhjsl_fz_history_yhbh on djhjsl_lsb_fz_history (yhbh);
create index idx_djhjsl_fz_history_gddwbm on djhjsl_lsb_fz_history (gddwbm);
create index idx_djhjsl_fz_history_yhlbdm on djhjsl_lsb_fz_history (yhlbdm);

 从执行计划来看,oracle只走了idx_djhjsl_fz_history_bbny这个索引,现在最快已经到1.95s了。

虽然现在已经满足了查询3s内的要求,但是考虑到以后,每个月的数据增长,数据量有5000万,一亿这样的大数据量的时候还是会很慢。

其实我在正式环境测试的时候,not exists 里面的这个表,建立单个索引是没有用的,建立联合索引才会使这个表走索引,可能是因为电脑的cpu不同等因素影响的。

 

上面的优化方法当然不能满足项目的需求,接下来结合业务进行优化。作为一个监控系统,数据是t+1的,不需要追求实时性,这些数据,都是使用etl抽取工具每天定时抽取的。而且每个月300万数据,用户只关注的只有几千条。所以结合业务,我们在使用etl抽取完数据后,将用户关注的数据插入到另一张表中,这样,每个月只有几千条数据,这样的话,一年也才几万条数据,对oracle来说决定是零压力的。

 

如果大家还有其他的方式优化,请在下方留言交流。

 

您可能感兴趣的文章:

如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复

相关文章:

◎已有 0 人评论

Copyright © 2019  萬仟网 保留所有权利. 粤ICP备17035492号-1
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com