OB 運(yùn)維 | 1000s->10s OceanBase 標(biāo)量子查詢改寫案例
作者：胡呈清
重點(diǎn)是 10、14 兩個(gè)算子，對(duì)應(yīng)的 2 個(gè)標(biāo)量子查詢除了和外表關(guān)聯(lián)外，本身內(nèi)部還有 o、k 這 2 張表關(guān)聯(lián)，這兩張表要做多少次關(guān)聯(lián)？13萬(wàn)次！ 很明顯這里效率會(huì)很低。
1.問(wèn)題描述
- 數(shù)據(jù)庫(kù)版本：OceanBase 3.2.3.3
下面這個(gè) SQL 執(zhí)行超過(guò) 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';2.分析過(guò)程
執(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 語(yǔ)法語(yǔ)義進(jìn)行解讀。
首先，這個(gè) SQL 從語(yǔ)法上分兩部分：
- 標(biāo)量子查詢，即投影部分的子查詢。
- 外部查詢，即 FROM 子句的關(guān)聯(lián)查詢和子查詢。
因此，這個(gè) SQL 的執(zhí)行邏輯是（也就是執(zhí)行計(jì)劃里的 0 號(hào) SUBPLAN FILTER 算子）：
- 先執(zhí)行外部查詢，得到 結(jié)果集 r（執(zhí)行計(jì)劃中的 1-6 號(hào)算子）。
- 再執(zhí)行標(biāo)量子查詢，從 結(jié)果集 r 中取一行數(shù)據(jù)，帶入到標(biāo)量子查詢中執(zhí)行（執(zhí)行計(jì)劃中的 7-18 號(hào)算子）。
- 重復(fù)上一步，直到循環(huán)取完最后一行數(shù)據(jù)。
為了定位 SQL 到底慢在哪一步？讓我們繼續(xù)拆解。
- 先拆出外部查詢（即對(duì)應(yīng)的 1-6 號(hào)算子部分），單獨(dú)執(zhí)行很快得到結(jié)果 13 萬(wàn)行，也就意味著所有標(biāo)量子查詢都需要執(zhí)行 13 萬(wàn)次。
- 從執(zhí)行計(jì)劃來(lái)看，7、8、9、18 號(hào)算子對(duì)應(yīng)的 4 個(gè)標(biāo)量子查詢都可以走索引，效率較高。只保留外部查詢和這 4 個(gè)標(biāo)量子查詢，執(zhí)行耗時(shí)很短。
- 重點(diǎn)是 10、14 兩個(gè)算子，對(duì)應(yīng)的 2 個(gè)標(biāo)量子查詢除了和外表關(guān)聯(lián)外，本身內(nèi)部還有 o、k 這 2 張表關(guān)聯(lián)，這兩張表要做多少次關(guān)聯(lián)？13萬(wàn)次！ 很明顯這里效率會(huì)很低。
SQL 中 10、14 兩個(gè)算子對(duì)應(yīng)的標(biāo)量子查詢?nèi)缦?，還可以再拆解 SQL，單獨(dú)只做一次 、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 萬(wàn)行);3.結(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)字段沒(méi)有索引，優(yōu)化器也沒(méi)法像 JOIN 語(yǔ)法一樣使用 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 萬(wàn)次，這個(gè)耗時(shí)就長(zhǎng)了。所以這個(gè) SQL 只能改寫成 LEFT JOIN 來(lái)優(yōu)化，這也是標(biāo)量子查詢的標(biāo)準(zhǔn)優(yōu)化方法。
4.優(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 萬(wàn)行)
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 萬(wàn)，執(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 |
=============================================================作者：胡呈清，愛(ài)可生 DBA 團(tuán)隊(duì)成員，擅長(zhǎng)故障分析、性能優(yōu)化，個(gè)人博客：[簡(jiǎn)書 | 輕松的魚]
責(zé)任編輯：武曉燕
來(lái)源：
愛(ài)可生開(kāi)源社區(qū)
