暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 案例分享：oceanbase关于子查询中or慢sql优化

案例分享：oceanbase关于子查询中or慢sql优化

IT那活儿 2022-11-05
451

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

事件背景

业务找过来反馈语句前台超时，页面报错，让分析下sql怎么优化，我后台查了下8.9s左右执行完成，a表1300W，b表900W，看到两个大表先想到了hash，检查了下语句测试了下是无法走到hash连接的。
二

事件分析

语句如下：
SELECT
1
FROM
CUSTXXXX CUST
WHERE
CUST.CUSTTYPE = 'PeeeeCUSTXXXX'
AND CUST.CERTID = '9999999999999'
AND CUST.CERTTYPE = 'BusSSSSSSSSSS'
AND CUST.STATUS = 'stcCCCCC' AND EXISTS (
SELECT
1
FROM
SUBSSSSSSS SUBS
WHERE
(
CUST.CUSTID = SUBS.CUSTID OR CUST.CUSTID = SUBS.USERID
) AND SUBS.STATUS IN ('US10', 'US30') AND SUBS.ACTIVE = 1 AND ROWNUM = 1
) AND ROWNUM = 1 AND CUST.CUSTNAME <> '日出东方（北京）科技股份有限公司' ;
explain看下执行计划如下：
| =======================================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
---------------------------------------------------------------------------------------
|0 |SUBPLAN SCAN |VIEW5 |1 |4546731|
|1 | LIMIT |                                  |1 |4546731|
|2 | HASH UNION DISTINCT |                                  |1 |4546731|
|3 | LIMIT |                                  |1 |2273366|
|4 | NESTED-LOOP SEMI JOIN |                                  |1 |2273366|
|5 | PX COORDINATOR |                                  |1 |2273366|
|6 | EXCHANGE OUT DISTR |:EX10000                          |1 |2273366|
|7 | PX PARTITION ITERATOR |                                  |1 |2273366|
|8 | TABLE SCAN |CUST(IDX_CUSTXXXX_CERTID) |1 |2273366|
|9 | PX COORDINATOR |                                  |1 |365    |
|10| EXCHANGE OUT DISTR |:EX20000                          |1 |365    |
|11| SUBPLAN SCAN |VIEW4 |1 |365    |
|12| PX PARTITION ITERATOR|                                  |1 |365    |
|13| TABLE SCAN |SUBS(IDX_SUBSSSSSSS_CUSTID_ACTIVE)|1 |365    |
|14| LIMIT |                                  |1 |2273366|
|15| NESTED-LOOP SEMI JOIN |                                  |1 |2273366|
|16| PX COORDINATOR |                                  |1 |2273366|
|17| EXCHANGE OUT DISTR |:EX30000                          |1 |2273366|
|18| PX PARTITION ITERATOR |                                  |1 |2273366|
|19| TABLE SCAN |CUST(IDX_CUSTXXXX_CERTID) |1 |2273366|
|20| PX COORDINATOR |                                  |1 |639    |
|21| EXCHANGE OUT DISTR |:EX40000                          |1 |639    |
|22| SUBPLAN SCAN |VIEW4 |1 |639    |
|23| PX PARTITION ITERATOR|                                  |1 |639    |
|24| TABLE SCAN |SUBS(IDX_SUBSSSSSSS_USERID) |1 |639    |
=======================================================================================

Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil),
access(nil)
1 - output([UNION([1])], [UNION([2])], [UNION([3])], [UNION([4])], [UNION([5])], [UNION([6])], [UNION([7])]), filter(nil), limit(?), offset(nil)
2 - output([UNION([1])], [UNION([2])], [UNION([3])], [UNION([4])], [UNION([5])], [UNION([6])], [UNION([7])]), filter(nil)
3 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil), limit(?), offset(nil)
4 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil),
conds(nil), nl_params_([CUST.CUSTID])
5 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil)
6 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil), dop=1
7 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil)
8 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter([CUST.CERTTYPE = ?], [CUST.STATUS = ?], [CUST.CUSTTYPE = ?], [CUST.CUSTNAME != ?]),
access([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), partitions(p[0-17])
9 - output([1]), filter(nil)
10 - output([1]), filter(nil), dop=1
11 - output([1]), filter(nil),
access([VIEW4.SUBS.CUSTID])
12 - output([SUBS.CUSTID]), filter(nil)
13 - output([SUBS.CUSTID]), filter([SUBS.STATUS IN (?, ?)]),
access([SUBS.CUSTID], [SUBS.STATUS]), partitions(p[0-17])
14 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil), limit(?), offset(nil)
15 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil),
conds(nil), nl_params_([CUST.CUSTID])
16 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil)
17 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil), dop=1
18 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil)
19 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter([CUST.CERTTYPE = ?], [CUST.STATUS = ?], [CUST.CUSTTYPE = ?], [CUST.CUSTNAME != ?]),
access([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), partitions(p[0-17])
20 - output([1]), filter(nil)
21 - output([1]), filter(nil), dop=1
22 - output([1]), filter(nil),
access([VIEW4.SUBS.USERID])
23 - output([SUBS.USERID]), filter(nil)
24 - output([SUBS.USERID]), filter([SUBS.STATUS IN (?, ?)], [SUBS.ACTIVE = 1]),
access([SUBS.USERID], [SUBS.STATUS], [SUBS.ACTIVE]), partitions(p[0-17])
第一眼看到这个计划，虽然知道cost不准，但是明显也能知道在扫描cust表时消耗不少，扫描垮了全部的17个分区，还有回表，我先尝试创建了个global复合索引：
虽然看起来cost下来了，但是实际效率并没有提升，那么代表分析方向错了。
我把语句拆分了下，把两个or保留了一个：
SELECT
1
FROM
CUSTXXXX CUST
WHERE
CUST.CUSTTYPE = 'PeeeeCUSTXXXX'
AND CUST.CERTID = '9999999999999'
AND CUST.CERTTYPE = 'BusSSSSSSSSSS'
AND CUST.STATUS = 'stcCCCCC' AND EXISTS (
SELECT
1
FROM
SUBSSSSSSS SUBS
WHERE
(
CUST.CUSTID = SUBS.CUSTID #######这个位置####
) AND SUBS.STATUS IN ('US10', 'US30') AND SUBS.ACTIVE = 1 AND ROWNUM = 1
) AND ROWNUM = 1 AND CUST.CUSTNAME <> '日出东方（北京）科技股份有限公司' ;
单独执行是在4s符合一开始整体sql8s左右的一个分支消耗。
可以看下我以前处理的一个union all的案例有讲到NO_REWRITE这个Hint，优化器禁用查询块的查询重写：
SELECT /*+no_rewrite*/
1
FROM
CUSTXXXX CUST
WHERE
CUST.CUSTTYPE = 'PeeeeCUSTXXXX'
AND CUST.CERTID = '9999999999999'
AND CUST.CERTTYPE = 'BusSSSSSSSSSS'
AND CUST.STATUS = 'stcCCCCC' AND EXISTS (
SELECT
1
FROM
SUBSSSSSSS SUBS
WHERE
(
CUST.CUSTID = SUBS.CUSTID #######这个位置####
) AND SUBS.STATUS IN ('US10', 'US30') AND SUBS.ACTIVE = 1 AND ROWNUM = 1
) AND ROWNUM = 1 AND CUST.CUSTNAME <> '日出东方（北京）科技股份有限公司' ;
加上这个hint之后由4s降到了0.46s，效果很明显。
以为到这里这个优化就告一段落了，把拆出去的or条件加回来，发现执行不出来了，看下执行计划：
| ==============================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------------------------------
|0 |SUBPLAN SCAN |VIEW2 |1 |2273367 |
|1 | LIMIT |                         |1 |2273367 |
|2 | SUBPLAN FILTER |                         |1 |2273367 |
|3 | PX COORDINATOR |                         |1 |2273366 |
|4 | EXCHANGE OUT DISTR |:EX10000                 |1 |2273366 |
|5 | PX PARTITION ITERATOR |                         |1 |2273366 |
|6 | TABLE SCAN |CUST(IDX_CUSTXXXX_CERTID)|1 |2273366 |
|7 | LIMIT |                         |1 |12211906|
|8 | PX COORDINATOR |                         |1 |12211906|
|9 | EXCHANGE OUT DISTR |:EX20000                 |1 |12211906|
|10| LIMIT |                         |1 |12211906|
|11| PX PARTITION ITERATOR|                         |1 |12211906|
|12| TABLE SCAN |SUBS |1 |12211906|
==============================================================================

Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil),
access(nil)
1 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil), limit(?), offset(nil)
2 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter([(T_OP_EXISTS, subquery(1))]),
exec_params_([CUST.CUSTID]), onetime_exprs_(nil), init_plan_idxs_(nil)
3 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil)
4 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil), dop=1
5 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil)
6 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter([CUST.CERTTYPE = ?], [CUST.STATUS = ?], [CUST.CUSTTYPE = ?], [CUST.CUSTNAME != ?]),
access([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), partitions(p[0-17])
7 - output([1]), filter(nil), limit(?), offset(nil)
8 - output([1]), filter(nil)
9 - output([1]), filter(nil), dop=1
10 - output([1]), filter(nil), limit(?), offset(nil)
11 - output([1]), filter(nil)
12 - output([1]), filter([? = SUBS.CUSTID OR ? = SUBS.USERID], [SUBS.STATUS IN (?, ?)], [SUBS.ACTIVE = 1]),
access([SUBS.CUSTID], [SUBS.USERID], [SUBS.STATUS], [SUBS.ACTIVE]), partitions(p[0-17]),
limit(?), offset(nil)
他把or聚合到一个谓词里，走了sub表的全表扫。
这时候我想到了一个hint，USE_CONCAT，Hint 指示优化器使用UNION ALL运算符将查询WHERE子句中的组合OR条件转换为复合查询。如果没有设置USE_CONCATHint，则仅当使用串联查询的成本低于不使用的成本时，才会发生此转换。
最终语句如下：
SELECT /*+no_rewrite*/
1
FROM
CUSTXXXX CUST
WHERE
CUST.CUSTTYPE = 'PeeeeCUSTXXXX'
AND CUST.CERTID = '9999999999999'
AND CUST.CERTTYPE = 'BusSSSSSSSSSS'
AND CUST.STATUS = 'stcCCCCC' AND EXISTS (
SELECT   /*+use_concat*/
1
FROM
SUBSSSSSSS SUBS
WHERE
(
CUST.CUSTID = SUBS.CUSTID OR CUST.CUSTID = SUBS.USERID
) AND SUBS.STATUS IN ('US10', 'US30') AND SUBS.ACTIVE = 1 AND ROWNUM = 1
) AND ROWNUM = 1 AND CUST.CUSTNAME <> '日出东方（北京）科技股份有限公司' ;
explain看下执行计划：
| ========================================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
----------------------------------------------------------------------------------------
|0 |SUBPLAN SCAN |VIEW2 |1 |2273366|
|1 | LIMIT |                                  |1 |2273366|
|2 | SUBPLAN FILTER |                                  |1 |2273366|
|3 | PX COORDINATOR |                                  |1 |2273366|
|4 | EXCHANGE OUT DISTR |:EX10000                          |1 |2273366|
|5 | PX PARTITION ITERATOR |                                  |1 |2273366|
|6 | TABLE SCAN |CUST(IDX_CUSTXXXX_CERTID) |1 |2273366|
|7 | LIMIT |                                  |1 |1324   |
|8 | UNION ALL |                                  |2 |1324   |
|9 | LIMIT |                                  |1 |366    |
|10| PX COORDINATOR |                                  |1 |366    |
|11| EXCHANGE OUT DISTR |:EX20000                          |1 |365    |
|12| LIMIT |                                  |1 |365    |
|13| PX PARTITION ITERATOR|                                  |1 |365    |
|14| TABLE SCAN |SUBS(IDX_SUBSSSSSSS_CUSTID_ACTIVE)|1 |365    |
|15| LIMIT |                                  |1 |959    |
|16| PX COORDINATOR |                                  |1 |959    |
|17| EXCHANGE OUT DISTR |:EX30000                          |1 |959    |
|18| LIMIT |                                  |1 |959    |
|19| PX PARTITION ITERATOR|                                  |1 |959    |
|20| TABLE SCAN |SUBS(IDX_SUBSSSSSSS_USERID) |1 |959    |
========================================================================================

Outputs & filters:
-------------------------------------
0 - output([1]), filter(nil),
access(nil)
1 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil), limit(?), offset(nil)
2 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter([(T_OP_EXISTS, subquery(1))]),
exec_params_([CUST.CUSTID]), onetime_exprs_(nil), init_plan_idxs_(nil)
3 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil)
4 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil), dop=1
5 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter(nil)
6 - output([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), filter([CUST.CERTTYPE = ?], [CUST.STATUS = ?], [CUST.CUSTTYPE = ?], [CUST.CUSTNAME != ?]),
access([CUST.REGION], [CUST.CUSTTYPE], [CUST.CERTID], [CUST.CERTTYPE], [CUST.STATUS], [CUST.CUSTID], [CUST.CUSTNAME]), partitions(p[0-17])
7 - output([UNION([1])]), filter(nil), limit(?), offset(nil)
8 - output([UNION([1])]), filter(nil)
9 - output([1]), filter(nil), limit(?), offset(nil)
10 - output([1]), filter(nil)
11 - output([1]), filter(nil), dop=1
12 - output([1]), filter(nil), limit(?), offset(nil)
13 - output([1]), filter(nil)
14 - output([1]), filter([SUBS.STATUS IN (?, ?)]),
access([SUBS.STATUS]), partitions(p[0-17]),
limit(?), offset(nil)
15 - output([1]), filter(nil), limit(?), offset(nil)
16 - output([1]), filter(nil)
17 - output([1]), filter(nil), dop=1
18 - output([1]), filter(nil), limit(?), offset(nil)
19 - output([1]), filter(nil)
20 - output([1]), filter([SUBS.STATUS IN (?, ?)], [SUBS.ACTIVE = 1], [lnnvl(cast(? = SUBS.CUSTID, TINYINT(-1, 0)))]),
access([SUBS.CUSTID], [SUBS.STATUS], [SUBS.ACTIVE]), partitions(p[0-17]),
limit(?), offset(nil)
看起来没有问题了，实际执行一下，0.89s符合预期，该优化告一段落。
结 论：
优化之美，sql之美就在于解决过程中的探索分析的过程，以及最后问题解决时候的满足。
比较开心的是oceanbase的oracle模式比较好的引用或者实现了oracle的很多hint，在分析起来对于传统的oracle的dba来说还是比较友好的，期待ob的生态圈越来越好，越来越多人能探索参与进来。
行之所向，莫问远方。



本文作者：张瑞远(上海新炬王翦团队）

本文来源：“IT那活儿”公众号

sqlsql优化oceanbasesubs
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论