OB 运维 | 1000s->10s OceanBase 标量子查询改写案例
1.问题描述
数据库版本：OceanBase 3.2.3.3下面这个 SQL 执行超过 1000 秒……
本文用这个例子，谈谈标量子查询慢的原因和优化方法。
复制
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;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.
2.分析过程
执行计划如下：
复制
===========================================================
|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 |
===========================================================1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.
每个子算子的成本都不高，但总成本很高！
下面结合 SQL 语法语义进行解读。
首先，这个 SQL 从语法上分两部分：
标量子查询，即投影部分的子查询。外部查询，即 FROM 子句的关联查询和子查询。因此，这个 SQL 的执行逻辑是（也就是执行计划里的 0 号 SUBPLAN FILTER 算子）：
先执行外部查询，得到 结果集 r（执行计划中的 1-6 号算子）。再执行标量子查询，从 结果集 r 中取一行数据，带入到标量子查询中执行（执行计划中的 7-18 号算子）。重复上一步，直到循环取完最后一行数据。为了定位 SQL 到底慢在哪一步？让我们继续拆解。
先拆出外部查询（即对应的 1-6 号算子部分），单独执行很快得到结果 13 万行，也就意味着所有标量子查询都需要执行 13 万次。从执行计划来看，7、8、9、18 号算子对应的 4 个标量子查询都可以走索引，效率较高。只保留外部查询和这 4 个标量子查询，执行耗时很短。重点是 10、14 两个算子，对应的 2 个标量子查询除了和外表关联外，本身内部还有 o、k 这 2 张表关联，这两张表要做多少次关联？13万次！ 很明显这里效率会很低。SQL 中 10、14 两个算子对应的标量子查询如下，还可以再拆解 SQL，单独只做一次 、k 表的关联查询（如下标黄部分）要 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(外部查询，结果有 13 万行);1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.
3.结论
标量子查询的执行计划只能是循环嵌套连接，也就是 SUBPLAN FILTER 算子（等同于 NESTED-LOOP JOIN 执行逻辑），它的执行效率取决于两个因素：
外部查询的结果集大小子查询的效率因此只有当外部查询结果集不大，并且子查询的关联字段有高效索引时，执行效率才高。如果关联字段没有索引，优化器也没法像 JOIN 语法一样使用 HASH JOIN 算子，执行效率很差。
在上面这个慢 SQL 中，有两个标量子查询不只和外表关联，它内部还有关联查询，所以即使关联字段有索引，子查询单次执行的效率也受限，再加上要执行 13 万次，这个耗时就长了。所以这个 SQL 只能改写成 LEFT JOIN 来优化，这也是标量子查询的标准优化方法。
4.优化方案
这个 SQL 的标量子查询中有聚合函数，应该先 GROUP BY 聚合后再和外表关联，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(外部查询，结果有 13 万行)
left join t1 on t.question_id=t1.question_id
left join t2 on t.question_id=t2.question_id;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.
改写后的执行计划如下（变成了使用 HASH OUTER JOIN 算法），可以看到。
成本 7.88 亿降到了 365 万，执行耗时降到 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 |
=============================================================1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.
作者：胡呈清，爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：[简书 | 轻松的鱼]
阅读剩余
THE END
