国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

案例分享：oceanbase關于子查詢中or慢sql優化

IT那活兒 / 3132人閱讀
案例分享：oceanbase關于子查詢中or慢sql優化

點擊上方“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那活兒”公眾號

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129196.html

相關文章

  • 厲害了，螞蟻金服！創造了中國自己的數據庫OceanBase（下）

    摘要：技術成就劃時代的分布式數據庫通過核心業務的不斷上線，螞蟻金服幫助渡過了自研基礎軟件產品最艱難的應用關。年天貓雙十一，支付寶創造了萬筆每秒支付峰值的業界新紀錄，這對于數據庫來說，意味著每秒需要同時運行萬條。 技術成就：劃時代的分布式數據庫 通過核心業務的不斷上線，螞蟻金服幫助OceanBase渡過了自研基礎軟件產品最艱難的應用關。OceanBase不只是被研發出來的，更是被用出來的，是在...

    shiina 評論0 收藏0

發表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<