Oracle長(zhǎng)耗時(shí)SQL優(yōu)化案例

最近在生產(chǎn)客服平臺(tái)，運(yùn)營(yíng)崗老師反饋，一個(gè)2w人的企業(yè)，在信息詳情查詢時(shí)，加載時(shí)間過(guò)長(zhǎng)，越70s左右出結(jié)果，需要后臺(tái)優(yōu)化下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，我們第一步是要將無(wú)用的查詢和碼表去掉，來(lái)簡(jiǎn)化這種較長(zhǎng)的SQL，如上面所展示，去除tms_mobile_number_screen 、tms_certi_type_tbl、tms_member_sts_tbl、tms_member_account_sate表關(guān)聯(lián)。

之后發(fā)現(xiàn)，當(dāng)去掉tms_plan_member_collect_ext這個(gè)表的關(guān)聯(lián)條件后，查詢的速度就特別快，SQL中，使用該表的關(guān)聯(lián)條件為：

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;

等價(jià)于

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

再舉個(gè)例子，這次是右外連接：

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

等價(jià)于

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

觀察了一下ext表的結(jié)構(gòu)，發(fā)現(xiàn)使用了聯(lián)合索引，聯(lián)合索引就是apply_id和person_id兩個(gè)字段，這時(shí)候第一反應(yīng)是索引是否生效，看了一下執(zhí)行計(jì)劃，確實(shí)走了索引，這里普及一下索引是否生效的例子：

索引：IDX(b,c）

select id where b = xx；

select id where c = xx；

上面的兩句sql會(huì)走b,c的聯(lián)合索引嗎？

答案是第一條會(huì)走，第二條不會(huì)。

那多個(gè)字段的聯(lián)合索引，使用部分字段會(huì)走索引嗎？例如 IDX(a,b,c) select id where b = xx and c = xx；

答案是不會(huì)，其實(shí)不管是多少個(gè)字段的聯(lián)合索引，不管查詢順序，不管查詢用到了幾個(gè)字段，只要沒(méi)有使用聯(lián)合索引的第一個(gè)字段，則不會(huì)走聯(lián)合索引。

在數(shù)據(jù)量很大的表中，聯(lián)合索引要比單個(gè)索引要慢，因?yàn)橐葘?duì)兩次，接著看表索引，發(fā)現(xiàn)該表有一個(gè)主鍵是索引，果斷采用主鍵索引，因?yàn)槲ㄒ恍愿撸褂弥麈I索引后，速度立刻由70s下降到0.5s左右。