java培训

亿元级外企Java培训企业

  • 全国服务监督电话400-111-8989
Java培训 > java教程 > 生产环境sql语句调优实例讲解
  • 生产环境sql语句调优实例讲解

    发布:Java培训  来源:java教程  时间: 2017年01月06日

  • 生产环境中的sql语句执行时间是很关键的性能指标,如果某个sql语句执行几个小时,优化以后几分钟,几十秒的话。会有很大的成就感...

  • 生产环境sql语句调优实战篇

    生产环境中的sql语句执行时间是很关键的性能指标,如果某个sql语句执行几个小时,优化以后几分钟,几十秒的话。会有很大的成就感,同时如果某个sql语句执行10秒,能够优化到1秒,感觉提升的幅度不是很大,但是如果这条语句执行极为频繁的话,那这种调优还是更有成就感的。

    执行时间是sql调优的一把标尺,但是同时也需要考虑到系统资源的平衡。

    今天在系统中发现一条sql语句执行时间很长。平均一个查询要执行一个半小时左右,而且系统的资源消耗极大。

    需要说明的service_details 是数据量过亿的表。ch_distribute 是千万级的表,subscriber是百万级的表。

    payment是千万级的表,paychannel是百万级的表。

    查看执行计划,倒看不出有明显的异常,这也就是执行计划的一个误区了,我们不能总是参考执行计划来进行调优,很多时候发现执行计划几乎是完美的,但是执行效率却很长。

    sql语句如下所示。

    SELECT cd.target_pcn, se.agreement_no, s.subscriber_no, s.prim_resource_val

    FROM ch_distribute CD, service_details SE, subscriber S

    WHERE cd.target_pcn IN

    (SELECT

    cp.pym_channel_no

    FROM paychannel cp, payment pym

    WHERE cp.pym_channel_no IN

    (SELECT cd.target_pcn

    FROM ch_distribute cd

    WHERE (cd.agreement_no, cd.soc, cd.soc_seq_no) IN

    (SELECT sg.agreement_no, sg.soc, sg.soc_seq_no

    FROM service_details sg

    WHERE sg.soc_status = 'A'

    AND sg.agreement_no IN

    (SELECT

    sg.agreement_no

    FROM service_details sg, subscriber s

    WHERE s.subscriber_no = sg.agreement_no

    AND sg.soc = 50412

    AND sg.soc_status = 'A'

    AND sg.soc_sts_rsn_cd =

    (SELECT param_values

    FROM small_table

    WHERE param_name =

    'XXXXXXXX1'

    AND job_name = 'XXXXXX') --bottleneck

    AND s.subscriber_type IN

    (SELECT param_values

    FROM small_table

    WHERE param_name = 'XXXXXXXX2'

    AND job_name = 'XXXXXX')

    ))

    AND expiration_date IS NULL)

    AND cp.ban = pym.account_id

    AND (pym.transaction_id >

    (SELECT param_values

    FROM small_table

    WHERE param_name = 'XXXXXXXX3'

    AND job_name = 'XXXXXX') AND

    pym.transaction_id <= 255004442))

    AND se.agreement_no = cd.agreement_no

    AND s.subscriber_no = se.agreement_no

    GROUP BY cd.target_pcn,

    se.agreement_no,

    s.subscriber_no,

    s.prim_resource_val

    猛一看这个查询语句还是挺臃肿的,可以明显的看到反复引用了大表service_details,chg_distribute.

    我先把这个问题发给一个性能调优的哥们,他在不改动sql语句的前提下,加了几个Hint,执行时间就从1个半小时降低到4分钟左右,猛一看这是一个极大的提升,看似不用修改sql语句了。

    我看了下他建议的hint,从执行时间来说,是很大的提升,但是从系统的资源消耗来看,还存在一定的隐患,建议的Hint如下:

    SELECT /*+parallel(pym,4) full(pym) use_hash(pym)*/

    cp.pym_channel_no

    SELECT /*+PARALLEL(S,4) full(S) FULL(SG) PARALLEL(SG,4) USE_HASH(S,SG)*/

    sg.agreement_no

    FROM service_details sg, subscriber s

    两个Hint本身也没有什么问题,对于大表的关联用hash_join效率比nested loop要高很多。加上并行,如果查询执行不够频繁,涉及的表不多,确实是很好的选择。

    我个人的观点还是从语句本身入手,先来看看有什么可以从结构中的改进,先在头脑中有一个基本的思路,然后主要查找数据的性能瓶颈到底在哪,因为根据在备份库上的测试,这个查询返回的数据条数在几千条左右,从上亿条,上千万的数据中排查出几千条肯定是有一些关键的过滤条件。

    使用并行固然好,如果在不使用并行的条件下,高效的使用索引是更好的选择。如果实在条件所限,对个别做表全表扫描速度也是很快的。

    在分析了数据的统计信息,索引情况之后,在备份库中进行了简单的数据筛查。

    首先定位了性能瓶颈,是如下的这个查询条件。通过如下的条件能够过滤掉99%以上的数据,剩下的数据和其它大表关联,都是可以使用到索引的,速度就会快很多。

    select *from agreement_no from service_details sg

    where

    sg.soc = 50412 and sg.soc_status = 'A'

    有了这个思路,修改起来就轻松多了.

    有了主要的改进,其他的改进就可以锦上添花了。

    还有两个需要修改的部分。

    一个是简化sql语句的表关联,可以看到很多的表出现了多次,这对查询本身来说也不是必须的,个人认为这个sql语句是在开发人员边开发,变修改导致了查询语句嵌套了很多重复的关联。

    一个是关于子查询的优化。有几个子查询会关联到一个小表,对小表中的数据进行反复关联。这对子查询而言,执行频率是极高的。

    SELECT param_values

    FROM small_table

    WHERE param_name =

    'XXXXXXXX1'

    AND job_name = 'XXXXXX'

    对于这种特别的子查询,可以考虑使用with语句来替代。改进后的语句如下,这样看就清晰多了。

    with ssrc as (SELECT param_values

    FROM small_table

    WHERE param_name = 'XXXXXXXX1'

    AND job_name = 'XXXXXX') ,

    sub_type as (SELECT param_values

    FROM small_table

    WHERE param_name = 'XXXXXXXX2'

    AND job_name = 'XXXXXX'),

    hr_pay_trx as (SELECT param_values

    FROM small_table

    WHERE param_name = 'XXXXXXXX3'

    AND job_name = 'XXXXXX')

    SELECT

    cp.pym_channel_no,sg.agreement_no, s.subscriber_no, s.prim_resource_val

    FROM service_details sg, subscriber s,chg_distribute chg,paychannel cp,sub_type,ssrc

    WHERE s.subscriber_no = sg.agreement_no

    AND sg.soc = 50412

    AND sg.soc_status = 'A'

    AND sg.soc_sts_rsn_cd =

    ssrc.param_values --bottleneck

    AND s.subscriber_type =sub_type.param_values

    and sg.agreement_no=chg.agreement_no

    and sg.soc=chg.soc

    and sg.soc_seq_no=chg.soc_seq_no

    and chg.expiration_date is null

    and cp.pym_channel_no=chg.target_pcn

    and exists(

    select 1 from ar1_payment pym,hr_pay_trx

    where cp.ban = pym.account_id

    AND (pym.transaction_id > hr_pay_trx.param_values

    AND

    pym.transaction_id <= :1))

    可以看到from后面跟了好几个大表,但是性能瓶颈在service_details上所以为了保险起见,我们可以使用hint来指定表的访问顺序。先过滤到99%以上的数据,剩下的就可以自然的走索引扫描了。

    添加的Hint如下,对于表service_details,因为没法使用到索引,所以就对这一个表进行全表扫描,走个并行。

    SELECT /*+leading(ssrc,sub_type,sg,s,chg,cp) parallel(sg 4) full(sg)*/

    cp.pym_channel_no,sg.agreement_no, s.subscriber_no, s.prim_resource_val

    最后在备份库中测试,效果果然很明显,备份库中速度从优化后的4分钟降低到2分钟。

    在生产环境中执行,速度更快,稳定在40秒左右。

  • 上一篇:C++、Java、JavaScript中的正则表达式

    下一篇:2016 Java程序员的年终总结

网站导航
2001-2016 达内时代科技集团有限公司 版权所有 京ICP证8000853号-56