将SQL质量审计引入软件开发可以避免不必要的SQL优化工作-阿里云开发者社区

开发者社区> eric0435> 正文

将SQL质量审计引入软件开发可以避免不必要的SQL优化工作

简介:
+关注继续查看

今天帮助兄弟部门优化五险统一征缴数据发送程序,优化其实很简单,主要是解决了原本不应该执行的全表扫描和笛卡尔积。但问题是为什么会出现全表扫描和笛卡尔积,是Oracle优化器选择错了执行计划吗,答案并不是,原因就是在设计表结构时的缺陷造成的,如果在设计表结构时能够根据业务合理设计,也就没有这次优化了。其实这个问题我在公司就提过,但不重视,现在我成了甲方,我又要当救火队员了。

下面是每个月社会保障系统向五险征缴系统发送每月所有单位各个险种的应缴数据的查询语句:
Select t.Pay_Object_Id,
       t.Pay_Object_Code,
       t.Pay_Object_Name,
       t.Insr_Detail_Code,
       t.asgn_tenet,
       t.asgn_order,
       t.use_pred_insr,
       Sum(t.Topay_Money) as topay_money,
       Sum(Pay_Money) as pay_money,
       Sum(Pred_Money) as pred_money,
       to_char(sysdate, 'yyyy-mm-dd') as pay_time,
       t.corp_type_code
  From (Select T1.Corp_Id As Pay_Object_Id,
               T1.Insr_Detail_Code,
               T1.Corp_Code As Pay_Object_Code,
               T1.Corp_Name As Pay_Object_Name,
               T1.asgn_tenet,
               T1.asgn_order,
               T1.use_pred_insr,
               Decode(Sign(T1.pay_Money),
                      -1,
                      T1.pay_Money,
                      Decode(Sign(T1.pay_Money -
                                  Decode(Sign(T1.pay_Money),
                                         -1,
                                         0,
                                         Nvl(T2.Pred_Money, 0))),
                             -1,
                             0,
                             T1.pay_Money -
                             Decode(Sign(T1.pay_Money),
                                    -1,
                                    0,
                                    Nvl(T2.Pred_Money, 0)))) As pay_Money,
               T1.toPay_Money,
               Nvl(T2.Pred_Money, 0) As Pred_Money,
               T1.corp_type_code
          from (select t11.Corp_Id,
                       t11.Corp_Code,
                       t11.Corp_Name,
                       t11.Insr_Detail_Code,
                       sum(t11.Topay_Money) as Topay_Money,
                       t11.corp_type_code,
                       sum(t11.Pay_Money) as Pay_Money,
                       t11.asgn_tenet,
                       t11.asgn_order,
                       t11.use_pred_insr
                  from (Select b.Corp_Id,
                               a.Corp_Code,
                               a.Corp_Name,
                               b.insr_detail_code,
                               a.corp_type_code,
                               Sum(b.Pay_Money - nvl(b.Payed_Money, 0)) As Topay_Money,
                               Sum(b.Pay_Money - nvl(b.Payed_Money, 0)) As Pay_Money,
                               c.asgn_tenet,
                               c.asgn_order,
                               c.use_pred_insr
                          From Bs_Corp a, Lv_Insr_Topay b, lv_scheme_detail c
                         Where a.Corp_Id = b. Corp_Id
                           and ((b.payed_flag = 0 and
                               nvl(b.busi_asg_no, 0) = 0) or
                               (b.payed_flag = 2))
                           and nvl(b.indi_pay_flag, 0) = 0
                           and c.scheme_id = 1
                           and b.insr_detail_code=c.insr_detail_code
                           and not exists
                         (select 'x'
                                  from lv_busi_bill lbb, lv_busi_record lbr
                                 where b.corp_id = lbr.pay_object_id
                                   and lbb.busi_bill_sn = lbr.busi_bill_sn
                                   and lbb.pay_object = 1
                                   and lbb.audit_flag = 0)
                           and c.insr_detail_code = b.insr_detail_code
                           and b.calc_prd < = '201508'
                           and b.insr_detail_code in
                               (select distinct insr_detail_code
                                  from lv_scheme_detail
                                 where scheme_id = 1)
                           and b.topay_type in
                               (select topay_type
                                  from lv_busi_type_topay
                                 where busi_type = 1)
                           and b.src_type = 1
                           and a.center_id = '430701'
                         Group By b.Corp_Id,
                                  b.Insr_Detail_Code,
                                  c.use_pred_insr,
                                  a.Corp_Code,
                                  a.Corp_Name,
                                  a.corp_type_code,
                                  c.asgn_tenet,
                                  c.asgn_order,
                                  c.use_pred_insr) t11
                 group by t11.Corp_Id,
                          t11.Corp_Code,
                          t11.Corp_Name,
                          t11.Insr_Detail_Code,
                          t11.corp_type_code,
                          t11.asgn_tenet,
                          t11.asgn_order,
                          t11.use_pred_insr) T1,
               (select t21.corp_id,
                       sum(t21.pred_money) as pred_money,
                       t21.Insr_Detail_Code
                  from (Select a.Corp_Id,
                               decode(c.use_pred_insr,
                                      null,
                                      b.insr_detail_code,
                                      c.use_pred_insr) as Insr_Detail_Code,
                               sum(decode(1, 0, 0, 1, b.Pred_Money)) as pred_money
                          From Bs_Corp a, Lv_Pred_Money b, lv_scheme_detail c
                         Where a.Corp_Id = b.Corp_Id
                           and c.insr_detail_code = b.insr_detail_code
                           and c.scheme_id = 1
                           and decode(c.use_pred_insr,
                                      null,
                                      c.insr_detail_code,
                                      c.use_pred_insr) = c.insr_detail_code
                         group by a.corp_id,
                                  c.use_pred_insr,
                                  b.insr_detail_code) t21
                 group by t21.corp_id, t21.Insr_Detail_Code) T2
         Where T1.Corp_Id = T2.Corp_Id(+)
           And T1.Insr_Detail_Code = T2.Insr_Detail_Code(+)) t
where not exists (select 'X'
          from lv_busi_bill a, lv_busi_record b
         where a.busi_bill_sn = b.busi_bill_sn
           and a.audit_flag = 0
           and a.pay_object = 1
           and b.PAY_OBJECT_ID = t.PAY_OBJECT_ID
           and b.INSR_DETAIL_CODE = t.insr_detail_code)
Group By t.pay_money,
          t.Pay_Object_Id,
          t.Pay_Object_Code,
          t.Pay_Object_Name,
          t.corp_type_code,
          t.insr_detail_code,
          t.asgn_tenet,
          t.asgn_order,
          t.use_pred_insr
Having Sum(t.pay_Money) = 0
order by t.Pay_Object_Name, t.asgn_order

其执行计划的统计信息如下:
3
执行时间是1481秒,这个时间是不可接受的。

其执行计划如下:
4

执行计划中对表lv_busi_record执行全表扫描,该表记录有2000w,这明显是不对,为什么不走索引了,是因为表在设计和创建时就没有创建索引,这个表的数据是不断增加的,前期数据量少,执行全表扫描对性能的影响就根本体现不出来,但随着系统的运行,数据量的增加就会越来越慢。还有就是表lv_scheme_detail和Bs_Corp之间的笛卡尔积,为什么会出现笛卡尔积了,发现两个表之间根本就没有关联条件,一开始还以为开发人员忘记书写了,但经过查询表空间发现,两个表根本就没有可以关联的字段,而最后使用了group by来进行去重。

这里我只能对表lv_busi_record根据业务规则创建索引,但没有办法解决表lv_scheme_detail和Bs_Corp之间的笛卡尔积关联的问题
如果修改表结构就涉及到修改应用程序了。在对表lv_busi_record索引后的执行情况如下。
其执行计划的统计信息如下:
2

5
执行时间缩短为接近14秒,从1481到14是百倍的提升。其实处理方法很简单,但我想说的是,这本就不应该出现的,如果我们软件开发商在设计,开发和测试阶段能认真设计,编写SQL和测试,也就是引入SQL质量审计就能避免这种问题的发生。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里巴巴复杂搜索系统的可靠性优化之路
闲鱼技术-元茂 1. 背景 搜索引擎是电商平台成交链路的核心环节,搜索引擎的高可用直接影响成交效率。闲鱼搜索引擎作为闲鱼关键系统,复杂度和系统体量都非常高,再加上闲鱼所有导购场景都依靠搜索赋能,搜索服务的稳定可靠成为了闲鱼大部分业务场景可用能力的衡量标准;如何保障搜索服务的稳定和高可用成为了极大的挑战。
13371 0
计算机软件著作权申请流程是什么?需要准备哪些材料?怎么办理?
计算机软件著作权申请流程是什么?需要准备哪些材料?怎么办理? 计算机软件著作权申请流程是什么? 软件著作权申请流程:搜索“中国版权保护中心官网”(如下图) 申请账户注册登录,进入账户中心,点击“我要登记”,然后填写申请表——提交申请文件——登记机构受理申请——审查——获得登记证书 软件著作.
942 0
【云周刊】 第210期:阿里巴巴复杂搜索系统的可靠性优化之路
本期头条 欢迎关注云周刊 阿里巴巴复杂搜索系统的可靠性优化之路 搜索引擎是电商平台成交链路的核心环节,搜索引擎的高可用直接影响成交效率。闲鱼搜索引擎作为闲鱼关键系统,复杂度和系统体量都非常高,再加上闲鱼所有导购场景都依靠搜索赋能,搜索服务的稳定可靠成为了闲鱼大部分业务场景可用能力的衡量标准;如何保障搜索服务的稳定和高可用成为了极大的挑战。
3763 0
在linux,windows上安装ruby on rails开发环境
ruby是一个非常优秀的语言,ruby的精髓rails可以让web开发的效率成倍的提高,下面就介绍一下我搭建rails环境的过程。windows下搭建ruby rails web开发环境 本篇文章主要是在阿里云的服务器上搭建rails 1.
1836 0
+关注
eric0435
系统架构师(高级工程师),ACOUG&amp;CSOUG核心成员,Oracle Young Expert 。政府信息化技术顾问。湖南省政府采购评审专家,从2007年开始从事社会保险系统开发,应用架构设计,数据库管理工作。擅长Oracle数据库故障诊断,性能调优。
105
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《Nacos架构&原理》
立即下载
《看见新力量:二》电子书
立即下载
云上自动化运维(CloudOps)白皮书
立即下载