SQL優(yōu)化:join估行不準(zhǔn)選錯執(zhí)行計(jì)劃

問題描述

數(shù)據(jù)庫版本：OceanBase3.2.3.3
一個 join 查詢，關(guān)聯(lián)字段包含組合主鍵中的第1、2、4個字段，走 nested-loop join 時，被驅(qū)動表只能匹配主鍵的前兩個字段，成本cost1較低，但實(shí)際效率不高；并且驅(qū)動表的扇出n（也就是輸出行數(shù)）估行比實(shí)際小很多。在計(jì)算總成本時，join 總成本~=（驅(qū)動表成本 + n*cost1）。
在本案例中驅(qū)動表成本是固定的，執(zhí)行計(jì)劃中 n 的估算值只有5000，但實(shí)際值有60萬，cost1=154。計(jì)算成本時，n*cost1 比實(shí)際小很多，優(yōu)化器最終選擇了 nested-loop join，如果被驅(qū)動表可以匹配主鍵的全部字段，效率是很高的，但這里由于只能匹配前兩個字段，效率較差，導(dǎo)致整個查詢耗時非常長。

分析過程

1. 分析執(zhí)行計(jì)劃

問題SQL如下，執(zhí)行耗時 500秒+：

select
 count(*) from 
 (
  SELECT
    JGBM AS QYDJID,
    SEGMENT3 AS FNUMBER,
    PERIOD_NAME AS SSQJ,
    ...
  FROM
    (
      SELECT
        ...
      FROM
        DC_ACCOUNTBALANCE_TEMP A,
        DEF_ACCOUNTCONFIG B,
        DC_ACCOUNT C,
        NVAT_ACCANDTAXIDMAPFORP07 D,
        BI_CHOICEOFUNIT E
      WHERE
        A.SEGMENT1 = D.ZTJGBM
        AND D.SBDWID = E.SBDWID
        AND B.JGBM = E.DEPTCODE
        AND B.YXQSNY <= (
          substr(A.PERIOD_NAME, 4, 6) || substr(A.PERIOD_NAME, 1, 2)
        )
        AND (
          substr(A.PERIOD_NAME, 4, 6) || substr(A.PERIOD_NAME, 1, 2)
        ) <= B.YXJZNY
        AND C.QYDJID = B.SYZT
        AND C.FNUMBER = A.SEGMENT3
        AND C.ACCOUNTYEAR = substr(A.PERIOD_NAME, 4, 6)
        AND a.period_name = '10-2023'
    ) SUB
  GROUP BY
    JGBM,
    SEGMENT3,
    PERIOD_NAME
) X 
left join DC_ACCOUNTBALANCE A 
ON (
  A.SSQJ = X.SSQJ
  AND A.QYDJID = X.QYDJID
  AND A.FNUMBER = X.FNUMBER
);

執(zhí)行計(jì)劃如下(多余信息已刪除)，結(jié)合SQL內(nèi)容進(jìn)行解讀：

  • X 表是 A、B、C、D、E 5張表關(guān)聯(lián)的結(jié)果，然后與 A 表進(jìn)行關(guān)聯(lián)查詢。從執(zhí)行計(jì)劃看，主要成本在X 表，因此先執(zhí)行 X 部分確認(rèn)是否慢在這部分，執(zhí)行耗時只要5秒，結(jié)果有61萬行，但執(zhí)行計(jì)劃中估行只有 5123
  • X 部分很快，慢在 A 部分，因?yàn)槭?NESTED-LOOP JOIN，A 作為被驅(qū)動表會循環(huán)查詢 61萬次（batch_join=false），每次查詢走主鍵，執(zhí)行計(jì)劃13號算子中 range_key([A.SSQJ(0x7eb5a42ec400)], [A.QYDJID(0x7eb5a42ed840)], [A.DATAUSE(0x7ec8f84434e0)], [A.FNUMBER(0x7eb5a42eec80)]), range(MIN ; MAX) 部分信息說明索引里有 4個字段，但是range_cond([A.SSQJ(0x7eb5a42ec400) = ?(0x7ec8f8451e20)], [A.QYDJID(0x7eb5a42ed840) = ?(0x7ec8f8452950)])這部分表示只能用到索引的前兩個字段，這會是慢的原因嗎？有個信息可以提供佐證：A:table_rows:32310843, physical_range_rows:391, logical_range_rows:391 優(yōu)化器估算A表每次查詢需要掃描 391 行，這個效率確實(shí)是不高的
  • 在估算 nested-loop join 的總成本時，計(jì)算邏輯是驅(qū)動表的成本+驅(qū)動表的扇出*被驅(qū)動表查詢一次的成本，這個SQL中驅(qū)動表的扇出（5123）比實(shí)際值（61萬）小很多，估算出的總成本比實(shí)際小很多
=================================================================================
|ID|OPERATOR               |NAME                              |EST. ROWS|COST   |
---------------------------------------------------------------------------------
|0 |SCALAR GROUP BY        |                                  |1        |3947739|
|1 | NESTED-LOOP OUTER JOIN|                                  |5123     |3947543|
|2 |  SUBPLAN SCAN         |X                                 |5123     |3154937|
|3 |   HASH GROUP BY       |                                  |5123     |3154861|
|4 |    HASH JOIN          |                                  |5123     |3149203|
|5 |     TABLE SCAN        |C                                 |81314    |31453  |
|6 |     HASH JOIN         |                                  |63573    |2940900|
|7 |      HASH JOIN        |                                  |1898     |35447  |
|8 |       TABLE SCAN      |D(IDX_ACCANDTAXIDMAPFORP07_CMB1)  |2011     |778    |
|9 |       HASH JOIN       |                                  |1736     |32462  |
|10|        TABLE SCAN     |E(IDX_BI_CHOICEOFUNIT_CMB1)       |1704     |660    |
|11|        TABLE SCAN     |B                                 |29154    |11277  |
|12|      TABLE SCAN       |A(IDX_DC_ACCOUNTBALANCE_TEMP_TEST)|639387   |2468263|
|13|  TABLE SCAN           |A                                 |1        |154    |
=================================================================================

Outputs & filters: 
-------------------------------------
 ...
  13 - output([remove_const(1)(0x7ec8f846ba40)]), filter([A.FNUMBER(0x7eb5a42eec80) = ?(0x7ec8f8453480)]), 
      access([A.FNUMBER(0x7eb5a42eec80)]), partitions(p0), 
      is_index_back=false, filter_before_indexback[false], 
      range_key([A.SSQJ(0x7eb5a42ec400)], [A.QYDJID(0x7eb5a42ed840)], [A.DATAUSE(0x7ec8f84434e0)], [A.FNUMBER(0x7eb5a42eec80)]), range(MIN ; MAX), 
      range_cond([A.SSQJ(0x7eb5a42ec400) = ?(0x7ec8f8451e20)], [A.QYDJID(0x7eb5a42ed840) = ?(0x7ec8f8452950)])

Used Hint:
...

Optimization Info:
-------------------------------------
...
A:table_rows:32310843, physical_range_rows:391, logical_range_rows:391, index_back_rows:0, output_rows:0, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[DC_ACCOUNTBALANCE],...

2. 分析表的統(tǒng)計(jì)信息

上一步我們分析得出：X 部分查詢很快，慢在A表查詢，要查詢 61萬次。A 表查詢時使用了主鍵的前兩個字段，因此需要分析一下 A 表的統(tǒng)計(jì)信息，主鍵的4個字段的 NDV 分別是多少，結(jié)果如下：

  • SSQJ、QYDJID 兩個字段的 NDV 并不高，每組值的重復(fù)次數(shù)可以通過統(tǒng)計(jì)信息估算：32310843/(85*972)=391，這個就是執(zhí)行計(jì)劃中的 physical_range_rows:391，意思就是每次查詢大概要掃 391 行數(shù)據(jù)，這個效率如果只執(zhí)行一次是沒啥問題的，但這個SQL里需要執(zhí)行61萬次，總耗時就大了。
  • 另外SQL中關(guān)聯(lián)字段包含了主鍵的3個字段，不在條件里的第3個字段 DATAUSE 實(shí)際值都為1，從邏輯上來看，SQL中加上AND A.DATAUSE = 1條件的結(jié)果不會變，這樣的好處是 A 表查詢時可以使用主鍵的所有字段，每次只需要掃1行數(shù)據(jù)，效率會高很多。另一種更好的方式是主鍵中去掉 DATAUSE 字段，不過OB不支持修改主鍵。
--查詢
select column_name,num_distinct from all_tab_col_statistics where table_name='DC_ACCOUNTBALANCE';
--結(jié)果
column_name        num_distinct
SSQJ                85
QYDJID              972
DATAUSE             1
FNUMBER             2616

3. 改寫1：加 AND A.DATAUSE = 1 條件

加條件后，SQL耗時從 500秒降到8秒，執(zhí)行計(jì)劃如下，A表每次只要掃描1行：

=================================================================================
|ID|OPERATOR               |NAME                              |EST. ROWS|COST   |
---------------------------------------------------------------------------------
|0 |SCALAR GROUP BY        |                                  |1        |3214924|
|1 | NESTED-LOOP OUTER JOIN|                                  |5123     |3214729|
|2 |  SUBPLAN SCAN         |X                                 |5123     |3154937|
|3 |   HASH GROUP BY       |                                  |5123     |3154861|
|4 |    HASH JOIN          |                                  |5123     |3149203|
|5 |     TABLE SCAN        |C                                 |81314    |31453  |
|6 |     HASH JOIN         |                                  |63573    |2940900|
|7 |      HASH JOIN        |                                  |1898     |35447  |
|8 |       TABLE SCAN      |D(IDX_ACCANDTAXIDMAPFORP07_CMB1)  |2011     |778    |
|9 |       HASH JOIN       |                                  |1736     |32462  |
|10|        TABLE SCAN     |E(IDX_BI_CHOICEOFUNIT_CMB1)       |1704     |660    |
|11|        TABLE SCAN     |B                                 |29154    |11277  |
|12|      TABLE SCAN       |A(IDX_DC_ACCOUNTBALANCE_TEMP_TEST)|639387   |2468263|
|13|  TABLE GET            |A                                 |1        |11     |
=================================================================================
Outputs & filters: 
...
13 - output([remove_const(1)(0x7eb91646c790)]), filter(nil), 
      access([A.SSQJ(0x7eb91646b730)]), partitions(p0), 
      is_index_back=false, 
      range_key([A.SSQJ(0x7eae68cec980)], [A.QYDJID(0x7eae68ceddc0)], [A.DATAUSE(0x7eae68cf05d0)], [A.FNUMBER(0x7eae68cef200)]), range(MIN ; MAX), 
      range_cond([A.DATAUSE(0x7eae68cf05d0) = 1(0x7eae68cefeb0)], [A.SSQJ(0x7eae68cec980) = ?(0x7eb916451ce0)], [A.QYDJID(0x7eae68ceddc0) = ?(0x7eb916452810)], [A.FNUMBER(0x7eae68cef200) = ?(0x7eb916453340)])
...
Optimization Info:
-------------------------------------
A:table_rows:32310843, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback

4. 改寫2：加 hint 走 hash join

前面我們分析A表查詢只能使用主鍵索引的前2個字段，效率不高，這種情況下可以看下 hash join 的執(zhí)行效率，加 hint /*+ leading(X A) use_hash(A) */ 耗時只要40秒。執(zhí)行計(jì)劃如下，結(jié)合前面的分析進(jìn)行解讀：

  • 被驅(qū)動表 A 除了關(guān)聯(lián)條件沒有其他條件，要做全表掃描，成本很高，所以總成本也很高，并且顯然比 nested-loop join 的成本高，在沒有 hint 干預(yù)的情況下，優(yōu)化器會選 nested-loop join
=============================================================================
|ID|OPERATOR          |NAME                              |EST. ROWS|COST    |
-----------------------------------------------------------------------------
|0 |SCALAR GROUP BY   |                                  |1        |52828380|
|1 | HASH OUTER JOIN  |                                  |5123     |52828184|
|2 |  SUBPLAN SCAN    |X                                 |5123     |3154937 |
|3 |   HASH GROUP BY  |                                  |5123     |3154861 |
|4 |    HASH JOIN     |                                  |5123     |3149203 |
|5 |     TABLE SCAN   |C                                 |81314    |31453   |
|6 |     HASH JOIN    |                                  |63573    |2940900 |
|7 |      HASH JOIN   |                                  |1898     |35447   |
|8 |       TABLE SCAN |D(IDX_ACCANDTAXIDMAPFORP07_CMB1)  |2011     |778     |
|9 |       HASH JOIN  |                                  |1736     |32462   |
|10|        TABLE SCAN|E(IDX_BI_CHOICEOFUNIT_CMB1)       |1704     |660     |
|11|        TABLE SCAN|B                                 |29154    |11277   |
|12|      TABLE SCAN  |A(IDX_DC_ACCOUNTBALANCE_TEMP_TEST)|639387   |2468263 |
|13|  TABLE SCAN      |A                                 |32310843 |12497986|
=============================================================================

總結(jié)

這是一個很經(jīng)典的問題：如果 join 時關(guān)聯(lián)表太多，執(zhí)行計(jì)劃容易選錯。
原因是估算驅(qū)動表的扇出很容易產(chǎn)生誤差，尤其join的結(jié)果作為驅(qū)動表時，相當(dāng)于要估算join的結(jié)果有多少行，這個誤差會更大。而優(yōu)化器在估算 nested-loop join 算法的成本邏輯中，驅(qū)動表的扇出對計(jì)算結(jié)果影響很大，也就是說 nested-loop join 的成本估算結(jié)果很容易產(chǎn)生誤差，所以執(zhí)行計(jì)劃容易選錯。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

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

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