SQL優(yōu)化:標(biāo)量子查詢改寫案例1

問題描述

數(shù)據(jù)庫版本：OceanBase3.2.3.3
下面這個(gè)SQL執(zhí)行超過1000秒，本文用這個(gè)例子談?wù)剺?biāo)量子查詢慢的原因和優(yōu)化方法。

select
  rq.processinstid processinstid,
  rq.question_id questionId,
  rq.question_no questionNo,
  to_char(rq.rev_start_date, 'yyyy-MM-dd') revStartDate,
  (
    select
      e.name
    from
      e
    where
      e.category_code = 'REV_SOURCE'
      and e.code = rq.rev_source
  ) revSource,
  (
    select
      e.name
    from
      e
    where
      e.category_code = 'QUESTION_TYPE'
      and e.code = rq.question_type
  ) questionType,
  rq.question_summary questionSummary,
  rq.question_desc questionDesc,
  to_char(rq.question_discover_date, 'yyyy-MM-dd') questionDiscoverDate,
  rq.aud_project_type audProjectType,
  (
    select
      d.dept_name
    from
      d
    where
      d.dept_id = rq.check_dept
  ) checkDept,
  (
    select
      to_char(wm_concat(distinct(k.org_name)))
    from
      o,
      k
    where
      o.question_id = rq.question_id
      and o.ASC_ORG = k.org_id
      and o.REFORM_TYPE = '0'
  ) ascOrg,
  (
    select
      to_char(wm_concat(distinct(k.dept_name)))
    from
      o,
      fnd_dept_t k
    where
      o.question_id = rq.question_id
      and o.MAIN_REV_DEPT = k.dept_id
      and o.REFORM_TYPE = '0'
  ) mainRevDept,
  (
    select
      e.name
    from
      e
    where
      e.category_code = 'REV_FINISH_STATE'
      and e.code = rq.rev_finish_state
  ) revFinishState,
  to_char(rq.compliance_date, 'yyyy-MM-dd') complianceDATE
from
  rq
  left join REM_QUESTION_PLAN_T t on rq.question_id = t.question_id
  left join fnd_org_t org on t.ASC_ORG = org.org_id
where
  1 = 1
  and rq.asc_org is null
  and (
    t.asc_org in (
      select
        f.org_id
      from
        f
      where
        f.org_type = 'G'
    )
    or rq.created_by_org in (
      select
        f.org_id
      from
        f
      where
        f.org_type = 'G'
    )
  )
  and rq.company_type = 'G';

分析過程

執(zhí)行計(jì)劃如下：

===========================================================
|ID|OPERATOR          |NAME           |EST. ROWS|COST     |
-----------------------------------------------------------
|0 |SUBPLAN FILTER    |               |6283     |788388847|
|1 | SUBPLAN FILTER   |               |6283     |1325483  |
|2 |  HASH OUTER JOIN |               |8377     |210530   |
|3 |   TABLE SCAN     |RQ             |7966     |77932    |
|4 |   TABLE SCAN     |T              |152919   |59150    |
|5 |  TABLE SCAN      |F              |440      |2763     |
|6 |  TABLE SCAN      |F              |440      |2763     |
|7 | TABLE SCAN       |E(SYS_C0011218)|1        |92       |
|8 | TABLE SCAN       |E(SYS_C0011218)|1        |92       |
|9 | TABLE GET        |D              |1        |46       |
|10| SCALAR GROUP BY  |               |1        |62483    |
|11|  NESTED-LOOP JOIN|               |1        |62483    |
|12|   TABLE SCAN     |O              |1        |62468    |
|13|   TABLE GET      |K              |1        |28       |
|14| SCALAR GROUP BY  |               |1        |62483    |
|15|  NESTED-LOOP JOIN|               |1        |62483    |
|16|   TABLE SCAN     |O              |1        |62468    |
|17|   TABLE GET      |K              |1        |27       |
|18| TABLE SCAN       |E(SYS_C0011218)|1        |92       |
===========================================================

總成本很高，但是每個(gè)子算子的成本都不高，結(jié)合SQL語法語義進(jìn)行解讀：

  • 從語法上看，這個(gè)SQL分兩部分：
    1. 標(biāo)量子查詢，即投影部分的子查詢
    2. 外部查詢，即 from 子句的關(guān)聯(lián)查詢和子查詢
  • 因此，這個(gè)SQL的執(zhí)行邏輯是（也就是執(zhí)行計(jì)劃里的 0號 SUBPLAN FILTER 算子）：
    1. 先執(zhí)行外部查詢，得到結(jié)果集r（執(zhí)行計(jì)劃中的1-6號算子）
    2. 從結(jié)果集r中取一行數(shù)據(jù)，帶入到標(biāo)量子查詢中執(zhí)行（執(zhí)行計(jì)劃中的7-18號算子）
    3. 重復(fù)上一步，直到循環(huán)取完最后一行數(shù)據(jù)

繼續(xù)拆解SQL來驗(yàn)證到底慢在哪一步：

  • 先拆出外部查詢（即對應(yīng)的1-6號算子部分），單獨(dú)執(zhí)行很快得到結(jié)果13萬行，也就意味著所有標(biāo)量子查詢都需要執(zhí)行 13萬次
  • 從執(zhí)行計(jì)劃來看 7、8、9、18號算子對應(yīng)的4個(gè)標(biāo)量子查詢都可以走索引，效率較高。只保留外部查詢和這4個(gè)標(biāo)量子查詢，執(zhí)行耗時(shí)很短
  • 重點(diǎn)是 10、14 兩個(gè)算子，對應(yīng)的2個(gè)標(biāo)量子查詢除了和外表關(guān)聯(lián)外，本身內(nèi)部還有O、K 這2張表關(guān)聯(lián)，這兩張表要做多少次關(guān)聯(lián)？13萬次！很明顯這里效率會(huì)很低

10、14 兩個(gè)算子對應(yīng)的標(biāo)量子查詢?nèi)缦拢€可以再拆解SQL，單獨(dú)只做一次 o、k 表的關(guān)聯(lián)查詢（如下標(biāo)黃部分）要200毫秒：

select
xxx,
(
    select
      to_char(wm_concat(distinct(k.org_name)))
    from
      REM_QUESTION_PLAN_T o,
      fnd_org_t k
    where
      o.question_id = rq.question_id
      and o.ASC_ORG = k.org_id
      and o.REFORM_TYPE = '0'
  ) ascOrg,
  (
    select
      to_char(wm_concat(distinct(k.dept_name)))
    from
      REM_QUESTION_PLAN_T o,
      fnd_dept_t k
    where
      o.question_id = rq.question_id
      and o.MAIN_REV_DEPT = k.dept_id
      and o.REFORM_TYPE = '0'
  ) mainRevDept,
  xxx
  from t(外部查詢，結(jié)果有13萬行);

結(jié)論

標(biāo)量子查詢的執(zhí)行計(jì)劃只能是循環(huán)嵌套連接，也就是 SUBPLAN FILTER 算子（等同于 nested-loop join 執(zhí)行邏輯），它的執(zhí)行效率取決于兩個(gè)因素：

  • 外部查詢的結(jié)果集大小
  • 子查詢的效率

因此只有當(dāng)外部查詢結(jié)果集不大，并且子查詢的關(guān)聯(lián)字段有高效索引時(shí)，執(zhí)行效率才高。如果關(guān)聯(lián)字段沒有索引，優(yōu)化器也沒法像 join 語法一樣使用 hash join 算子，執(zhí)行效率很差。
在上面這個(gè)慢SQL中，有兩個(gè)標(biāo)量子查詢不只和外表關(guān)聯(lián)，它內(nèi)部還有關(guān)聯(lián)查詢，所以即使關(guān)聯(lián)字段有索引，子查詢單次執(zhí)行的效率也受限，再加上要執(zhí)行 13萬次，這個(gè)耗時(shí)就長了。所以這個(gè)SQL只能改寫成 left join 來優(yōu)化，這也是標(biāo)量子查詢的標(biāo)準(zhǔn)優(yōu)化方法。

優(yōu)化方案

這個(gè)SQL的標(biāo)量子查詢中有聚合函數(shù)，應(yīng)該先 group by 聚合后再和外表關(guān)聯(lián)，SQL（局部）改寫如下：

with t1 as (
  select
    o.question_id,
    to_char(wm_concat(distinct(k.org_name))) as org_name
  from
    REM_QUESTION_PLAN_T o,
    fnd_org_t k
  where
    o.ASC_ORG = k.org_id
    and o.REFORM_TYPE = '0'
  group by
    o.question_id
),
t2 as (
  select
    o.question_id,
    to_char(wm_concat(distinct(k.dept_name))) as dept_name
  from
    REM_QUESTION_PLAN_T o,
    fnd_dept_t k
  where
    o.MAIN_REV_DEPT = k.dept_id
    and o.REFORM_TYPE = '0'
  group by
    o.question_id
)
select
xxx,
t1.org_name as ascOrg,
t2.dept_name as mainRevDept,
xxx
  from t(外部查詢，結(jié)果有13萬行)
  left join t1 on t.question_id=t1.question_id
  left join t2 on t.question_id=t2.question_id;

改寫后的執(zhí)行計(jì)劃如下（變成了使用 HASH OUTER JOIN 算法），可以看到成本7.88 億降到了 365 萬，執(zhí)行耗時(shí)降到10秒：

=============================================================
|ID|OPERATOR              |NAME           |EST. ROWS|COST   |
-------------------------------------------------------------
|0 |SUBPLAN FILTER        |               |6318     |3653489|
|1 | MERGE GROUP BY       |               |6318     |1636701|
|2 |  SORT                |               |6318     |1632074|
|3 |   SUBPLAN FILTER     |               |6318     |1613799|
|4 |    HASH OUTER JOIN   |               |8424     |492531 |
|5 |     HASH OUTER JOIN  |               |8377     |331672 |
|6 |      MERGE OUTER JOIN|               |7966     |198317 |
|7 |       TABLE SCAN     |RQ             |7966     |77932  |
|8 |       SUBPLAN SCAN   |T2             |2351     |119098 |
|9 |        MERGE GROUP BY|               |2351     |119062 |
|10|         SORT         |               |2352     |118658 |
|11|          HASH JOIN   |               |2352     |113818 |
|12|           TABLE SCAN |K              |22268    |8614   |
|13|           TABLE SCAN |O              |76460    |60075  |
|14|      TABLE SCAN      |T              |152919   |59150  |
|15|     SUBPLAN SCAN     |T1             |76415    |118014 |
|16|      HASH JOIN       |               |76415    |116865 |
|17|       TABLE SCAN     |K              |7033     |2721   |
|18|       TABLE SCAN     |O              |76460    |60075  |
|19|    TABLE SCAN        |F              |440      |2763   |
|20|    TABLE SCAN        |F              |440      |2763   |
|21| TABLE SCAN           |E(SYS_C0011218)|1        |92     |
|22| TABLE SCAN           |E(SYS_C0011218)|1        |92     |
|23| TABLE GET            |D              |1        |46     |
|24| TABLE SCAN           |E(SYS_C0011218)|1        |92     |
=============================================================
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容