Oracle长耗时SQL优化案例

2023-05-17
关注

最近在生产客服平台,运营岗老师反馈,一个2w人的企业,在信息详情查询时,加载时间过长,越70s左右出结果,需要后台优化下SQL。

首先展示一下原SQL

select DISTINCT pma.member_apply_id,
                tm.mobile_num,
                tpp.product,
                tpp.plan_id,
                tpp.plan_name,
                pma.org_name,
                vtor.role_id,
                tp.person_id,
                tp.real_name,
                tp.certi_code,
                tp.birthday,
                pma.start_work_date,
                pma.rank,
                pma.retire_age,
                pma.month_base,
                ext.self_define_11,
                ext.now_month_salary,
                pma.employee_date,
                pma.eff_date,
                pma.employee_no,
                pma.member_sts_id,
                pma.account_state_id,
                tp.certi_type,
                tp.gender,
                decode(tp.gender, 'M', '男' , 'F', '女') sexName,
                mt.member_sts_name memberStateName,
                amt.account_sate_desc accountStateName,
                ct.certi_type_name certiTypeName,
                pma.email
  FROM tms_plan_member_apply pma,
       tms_person tp,
       tms_mobile_number_screen tm,
       v_tms_org_role vtor,
       tms_product_plan tpp,
       tms_certi_type_tbl ct,
       tms_member_sts_tbl mt,
       tms_member_account_sate amt,
       tms_plan_member_collect_ext ext,
       (select max(pma2.member_apply_id) member_apply_id
          FROM tms_plan_member_apply pma2, tms_busi_apply tba2
         WHERE tba2.flow_sts_id = 9999
           AND EXISTS (SELECT 1
                  FROM v_tms_org_role T
                 WHERE t.role_type = '13'
                   AND t.ORG_NAME = pma2.org_name
                 START WITH T.ROLE_ID = '46000'
                CONNECT BY PRIOR T.ORG_ID = T.PARENT_ORG)
           AND tba2.apply_id = pma2.busi_apply_id
           AND tba2.plan_id = '39076'
         GROUP BY pma2.person_id) t1
 WHERE ((tm.screen_id =
       (select max(mm.screen_id)
            FROM tms_mobile_number_screen mm
           WHERE mm.person_id = tm.person_Id)) OR tm.screen_id is null)
   AND ext.apply_id(+) = pma.busi_apply_id
   AND ext.person_id(+) = pma.person_id
   AND ct.certi_type_id(+) = tp.certi_type
   AND pma.person_id = tp.person_id
   AND tm.person_id(+) = pma.person_id
   AND mt.member_sts_id(+) = pma.member_sts_id
   AND amt.account_sate_id(+) = pma.account_state_id
   AND vtor.org_name = pma.org_name
   AND vtor.ROLE_TYPE = 13
   AND t1.member_apply_id = pma.member_apply_id
   AND tpp.plan_id = '39076'
 ORDER BY tp.real_name

遇到这种SQL,我们第一步是要将无用的查询和码表去掉,来简化这种较长的SQL,如上面所展示,去除tms_mobile_number_screen 、tms_certi_type_tbl、tms_member_sts_tbl、tms_member_account_sate表关联。

之后发现,当去掉tms_plan_member_collect_ext这个表的关联条件后,查询的速度就特别快,SQL中,使用该表的关联条件为:

ext.apply_id(+) = pma.busi_apply_id

AND ext.person_id(+) = pma.person_id

oracle中的(+)是一种特殊的用法,(+)表示外连接,并且总是放在非主表的一方。例如左外连接:

select A.a,B.a from A LEFT JOIN B ON A.b=B.b;

等价于

select A.a,B.a from A,B where A.b = B.b (+);

再举个例子,这次是右外连接:

select A.a,B.a from A RIGHT JOIN B ON A.b=B.b;

等价于

select A.a,B.a from A,B where A.b (+) = B.b;

观察了一下ext表的结构,发现使用了联合索引,联合索引就是apply_id和person_id两个字段,这时候第一反应是索引是否生效,看了一下执行计划,确实走了索引,这里普及一下索引是否生效的例子:

索引:IDX(b,c)

select id where b = xx;

select id where c = xx;

上面的两句sql会走b,c的联合索引吗?

答案是第一条会走,第二条不会。

那多个字段的联合索引,使用部分字段会走索引吗?例如 IDX(a,b,c) select id where b = xx and c = xx;

答案是不会,其实不管是多少个字段的联合索引,不管查询顺序,不管查询用到了几个字段,只要没有使用联合索引的第一个字段,则不会走联合索引。

在数据量很大的表中,联合索引要比单个索引要慢,因为要比对两次,接着看表索引,发现该表有一个主键是索引,果断采用主键索引,因为唯一性高,使用主键索引后,速度立刻由70s下降到0.5s左右。

  • sql优化
  • select
  • 索引
  • oracle
  • oracle执行计划
  • pma
  • ext
  • tms
您觉得本篇内容如何
评分

评论

您需要登录才可以回复|注册

提交评论

慧生活

这家伙很懒,什么描述也没留下

关注

点击进入下一篇

一篇带你ES入门 原创 精选

提取码
复制提取码
点击跳转至百度网盘