Oracle长耗时SQL优化案例

最近在生产客服平台，运营岗老师反馈，一个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左右。