SQL Explain 优化实践 - OceanBase 数据库 V4.2.2

某客户数据库系统发生性能故障，发生大量交易拥堵的现象，直接原因是部分 OceanBase 数据库服务器节点的 CPU 使用率达到 100%，导致交易无法正常办理。通过查询 GV$OB_SQL_AUDIT 视图抓取信息进行分析，定位到一条问题 SQL 的平均耗时为 2s，并且该 SQL 总的 CPU 使用量超过该服务器节点 CPU 资源的 70%，再结合 Explain 信息以及相关表和视图的 DDL 定义确定影响性能的原因，经过一系列的 DDL 优化后，使得该 SQL 的 CPU 耗时降至 4 ms，彻底解决了性能瓶颈问题。

排查思路

本案例的排查和优化思路如下：

通过查询 GV$OB_SQL_AUDIT 视图定位 TOP SQL。

执行如下 SQL 语句查询 GV$OB_SQL_AUDIT 视图定位问题 SQL。本案例主要查询问题发生 30 分钟的时间段内指定的 OBserver 节点上（svr_ip）的 SQL (GROUP BY sql_id) 相比于问题时间段内总 CPU 的使用率（SUM(execute_time)/(30*60*1000*1000）AS cpu_cnt）等信息。

SELECT sql_id,
       COUNT(*)               AS executions,
       SUM(execute_time)      AS tot_cpu_time,
       AVG(execute_time)      AS avg_cpu_time,
       SUM(execute_time)/(30*60*1000*1000)     AS cpu_cnt,
       query_sql
    FROM oceanbase.GV$OB_SQL_AUDIT
    WHERE tenant_id= 'mysql001'
        AND svr_ip='xxx.xxx.xxx.xxx'
        AND request_time BETWEEN (TIME_TO_USEC(NOW())-30*60*1000*1000) AND TIME_TO_USEC(NOW())
        AND is_executor_rpc =0 
        GROUP BY sql_id
        HAVING COUNT(*)>1
        ORDER BY cpu_cnt DESC LIMIT 10;

查询得到 SELECT ... FROM V_TT01 WHERE (COL001 IN (...)) 类别 SQL 的执行频率和 CPU 使用率较高，大概为 70%（80 c/108 c），而且集中于同一台 OBServer 节点上。

+----------------------------------+------------+--------------+--------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+
|       sql_id                     | executions | tot_cpu_time | avg_cpu_time | cpu_cnt | query_sql      |
+----------------------------------+------------+--------------+--------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2705182A6EAB699CEC8E59DA80710B64 |   23,425 |   33,818,182,526  |      1,443,679 |      56 | SELECT ... FROM V_TT01 WHERE (COL001 IN (20017476)) |
| 32AB97A0126F566064F84DDDF4936F82 |    7,112 |     7,926.295,919 |      1,114,496 |      13 | SELECT ... FROM V_TT01 WHERE (COL001 IN (20035891,20035892)) |
| A5F514E873BE9D1F9A339D0DA7481D69 |    4,046 |     6,053,872,739 |      1,496,261 |      10 | SELECT ... FROM V_TT01 WHERE (COL001 IN (20017892,20007654,20025634)) |
| 31FD78420DB07C11C8E3154F1658D237 |      186 |       646,926,207 |      3,478,098 |       1 | SELECT ... FROM V_TT01 WHERE (COL001 IN (20027596,20035891))  |
| C48AEE941D985D8DEB66892228D5E845 |       28 |       569,004,505 |     20,321,589 |       1 | SELECT ... |
| 101B7B79DFA9AE801BEE4F1A234AD294 |      155 |       440,190,675 |      2,839,940 |       1 | SELECT ... |
| 1D0BA376E273B9D622641124D8C59264 |      423 |       429,140,148 |      1,014,516 |       1 | SELECT ... |
| 64CF75576816DB5614F3D5B1F35B1472 |      113 |       326,338,159 |      2,887,948 |       1 | SELECT ... |
| 23D1C653347BA469396896AD9B20DCA1 |      381 |       309.367.170 |        811,987 |       1 | SELECT ... |
| FA4F493FA5CE2DCC64F51CF3754F96C6 |        7 |       289,191,515 |     41,313,074 |       0 | SELECT ... |
+------------+----------------------------+--------------+------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set

查询问题 SQL 的 Explain 信息，分析影响性能的关键点。

通过执行 EXPLAIN 命令得到该问题 SQL 的 Explain 信息如下：

=================================================================================
|ID|OPERATOR                             |NAME                 |EST. ROWS|COST  |
---------------------------------------------------------------------------------
|0 |EXCHANGE IN REMOTE                   |                     |177      |432961|
|1 | EXCHANGE OUT REMOTE                 |                     |177      |397632|
|2 |  SUBPLAN SCAN                       |V_TT01               |177      |397632|
|3 |   UNION ALL                         |                     |177      |397629|
|4 |    NESTED-LOOP JOIN CARTESIAN       |                     |0        |151990|
|5 |     TABLE SCAN                      |TBL1(IDX_TBL1_COL001)|1        |92    |   
|6 |     MATERIAL                        |                     |0        |151899|
|7 |      NESTED-LOOP ANTI JOIN CARTESIAN|                     |0        |151899|
|8 |       TABLE SCAN                    |TT01(IDX_TT01_COL001)|1        |92    |
|9 |       MATERIAL                      |                     |177      |151781|
|10|        SUBPLAN SCAN                 |VIEW1                |177      |151780|
|11|         TABLE SCAN                  |TBL3(IDX_TBL3_COL170)|177      |151777|
|12|    NESTED-LOOP JOIN CARTESIAN       |                     |177      |236327|
|13|     TABLE GET                       |TBL2                 |1        |46    |
|14|     TABLE SCAN                      |TBL3(IDX_TBL3_COL170)|177      |235510|
=================================================================================

Outputs & filters: 
-------------------------------------

  5 - output(......), filter(nil), 
      access(......), partitions(p0), 
      is_index_back=true, 
      range_key([TBL1.COL001(0x7f510ed97860)], [TBL1.COL002(0x7f48eec9baa0)]), range(20017476,MIN ; 20017476,MAX), 
      range_cond([TBL1.COL001(0x7f510ed97860) = 20017476(0x7f510edbd750)])

  8 - output(......), filter(nil), 
      access(......), partitions(p0), 
      is_index_back=true, 
      range_key([TT01.COL001(0x7f510ed97570)], [TT01.COL003(0x7f510ed97b50)]), range(20017476,MIN ; 20017476,MAX), 
      range_cond([TT01.COL001(0x7f510ed97570) = 20017476(0x7f510edbcc50)])

 11 - output(......), filter([20017476 = cast(cast(TBL3.COL170(0x7f3f5b51c090), VARCHAR2(20 BYTE))(0x7f3f5b51d6b0), NUMBER(-1, -85))(0x7f3f5b51ce80)(0x7f3f5b51c380)]), 
      access(......), partitions(p0), 
      is_index_back=false, filter_before_indexback[false],   
      range_key([TBL3.COL170(0x7f3f5b51c090)], [TBL3.COL002(0x7f3f5b528e80)]), range(MIN,MIN ; MAX,MAX)always true  

 13 - output(......), filter(nil), 
      access(......), partitions(p0), 
      is_index_back=false, 
      range_key([TBL2.COL004(0x7f0cb0ff9f20)]), range[20017476 ; 20017476], 
      range_cond([TBL2.COL004(0x7f0cb0ff9f20) = 20017476(0x7f0cb1001170)])

 14 - output(......), filter([20017476 = cast(cast(TBL3.COL170(0x7f0cb0ffa210), VARCHAR2(20 BYTE))(0x7f0cb1002fa0), NUMBER(-1, -85))(0x7f0cb1002770)(0x7f0cb1001c70)]), 
      access(......), partitions(p0), 
      is_index_back=true, filter_before_indexback[true],
      range_key([TBL3.COL170(0x7f0cb0ffa210)], [TBL3.COL002(0x7f3f5b582c10)]), range(MIN,MIN ; MAX,MAX)always true

根据上述 EXPALIN 信息，了解得到该条 SQL 的执行总代价估算为 432961，代价主要体现在 3 号算子 UNION ALL 部分（代价估算为 397629），UNION ALL 的两个分支都包含 NESTED-LOOP JOIN，代价估算分别为 151990 和 236327。UNION ALL 部分第一个分支（从 4 号到 11 号算子）的主要代价体现在 11 号算子上面，即通过索引 TBL3(IDX_TBL3_COL170) 进行 TABLE SCAN；第二个分支（从 12 号到 14 号算子）的主要代价体现在 14 号算子上面，也是通过索引 TBL3(IDX_TBL3_COL170) 进行 TABLE SCAN。由此可见 11 号算子和 14 号算子代价偏高可能都是由于 TBL3 的索引导致的，初步定位出该案例中性能问题的瓶颈点。

接下来再分析 11 号算子和 14 号算子的 Outputs & filters 信息，其中 range(MIN,MIN ; MAX,MAX)always true 表明该 SQL 执行过程中没有做索引匹配，而 filter([20017476 = cast(cast(TBL3.COL170(0x7f0cb0ffa210), VARCHAR2(20 BYTE))(0x7f0cb1002fa0), NUMBER(-1, -85))(0x7f0cb1002770)(0x7f0cb1001c70)]) 表明在访问时发生了数据类型的转换，由此得知可能是关联字段的数据类型不统一而进行了数据类型的转换，导致了 TBL3.COL170 上的索引不能做匹配。基于此结论再结合 DDL 定义做进一步的分析。

 11 - output(......), filter([20017476 = cast(cast(TBL3.COL170(0x7f3f5b51c090), VARCHAR2(20 BYTE))(0x7f3f5b51d6b0), NUMBER(-1, -85))(0x7f3f5b51ce80)(0x7f3f5b51c380)]), 
      access(......), partitions(p0), 
      is_index_back=false, filter_before_indexback[false],   
      range_key([TBL3.COL170(0x7f3f5b51c090)], [TBL3.COL002(0x7f3f5b528e80)]), range(MIN,MIN ; MAX,MAX)always true 

 14 - output(......), filter([20017476 = cast(cast(TBL3.COL170(0x7f0cb0ffa210), VARCHAR2(20 BYTE))(0x7f0cb1002fa0), NUMBER(-1, -85))(0x7f0cb1002770)(0x7f0cb1001c70)]), 
      access(......), partitions(p0), 
      is_index_back=true, filter_before_indexback[true],
      range_key([TBL3.COL170(0x7f0cb0ffa210)], [TBL3.COL002(0x7f3f5b582c10)]), range(MIN,MIN ; MAX,MAX)always true

分析相关表和视图的 DDL 定义，确定出现性能问题的原因。

首先，从视图 V_TT01 的定义中可知, UNION ALL 的第一个分支中 tbl3 存在于 NOT EXISTS 子查询中，结合 EXPLAIN 信息，可以得出相关子查询被改写为 NESTED-LOOP ANTI JOIN。此外，视图 V_TT01 发生了 TO_NUMBER 转换，其中的 col001 和 col004 会通过 col170 传递给 TBL3 的索引 (IDX_TBL3_COL170)。

接下来分析表 TBL3 的定义，可知索引 TBL3(IDX_TBL3_COL170) 定义于 COL170 字段，数据类型为 VARCHAR2(20)，而该字段就是视图 V_TT01 与其他表进行关联的字段。从视图 V_TT01 的定义可知，UNION ALL 第一个分支中，11 号算子对应的关联为 "tbl3"."col170" = "tt01"."col001" = 20017476, 第二个分支中 14 号算子对应的关联为 "tbl3"."col170" = "tbl2"."col004" = 20017476，由于关联字段数据类型不一致，所以发生了 TO_NUMBER 转换，即将 COL170 字段的 VARCHAR2 类型被强制转换为了 NUMBER。这与 11 号算子和 14 号算子的 Outputs & filters 中的 filter 信息相符合。

综合 DDL 定义和 EXPALIN 信息可知，该类别 SQL 执行过程中，视图 V_TT01 发生的 TO_NUMBER 强制转换不合理导致索引性能下降，造成 11 号和 14 号算子的代价估算较大，应该去掉 TO_NUMBER 转换。

/* 问题 SQL 的定义 */
SELECT ......
    FROM   v_tt01
    WHERE  COL001 IN (20017476)；

/* 视图 V_TT01 的定义 */
CREATE VIEW "V_TT01_OLD"
AS
  (SELECT  
          To_number("LOCAL_CXZX"."tt01"."col001") AS "COL001",   
          ... ...
   FROM   "LOCAL_CXZX"."tt01",
          "LOCAL_CXZX"."tbl1"
   WHERE  ( "LOCAL_CXZX"."tt01"."col001" = "LOCAL_CXZX"."tbl1"."col001" )
          AND NOT EXISTS((SELECT 1
                          FROM   "LOCAL_CXZX"."tbl3"
                          WHERE  ( "LOCAL_CXZX"."tt01"."col001" =
                                   "LOCAL_CXZX"."tbl3"."col170"
                                 ))))

  UNION ALL

  (SELECT  
          To_number("LOCAL_CXZX"."tbl2"."col004") AS "COL001",   
          ... ... 
   FROM   "LOCAL_CXZX"."tbl2",
          "LOCAL_CXZX"."tbl3" "TBL3"
   WHERE  ( "LOCAL_CXZX"."tbl2"."col004" = "tbl3"."col170" ))

   ; 


/* 表 TBL1 的定义*/
CREATE TABLE "LOCAL_BDZGBGYY"."TBL1" (
  "COL001" NUMBER(16)  NOT NULL ,    // 5: To_number("tt01"."col001") = "tbl1"."col001" = 20017476,  To_number(NUMBER(16)) = NUMBER(20)
  ... ...
  PRIMARY KEY ("COL002")
) ;

CREATE INDEX "LOCAL_BDZGBGYY"."IDX_TBL1_COL001" on "LOCAL_BDZGBGYY"."TBL1" (
 "COL001"
) GLOBAL ;  


/* 表 TT01 的定义 */
CREATE TABLE "EINP_BASICINFO"."TT01" (
... ...
  "COL001" NUMBER(20)  NOT NULL,     // 8: To_number("tt01"."col001") = "tbl1"."col001" = 20017476,  To_number(NUMBER(16)) = NUMBER(20)  
... ...
  PRIMARY KEY ("COL003")  
) ;

CREATE INDEX "EINP_BASICINFO"."IDX_TT01_COL001" on "EINP_BASICINFO"."TT01" (
 "COL001"
) GLOBAL ;  


/* 表 TBL3 的定义*/
CREATE TABLE "LOCAL_BDZGBGYY"."TBL3" (
  "COL170" VARCHAR2(20) NOT NULL,      // 11: "tbl3"."col170" = "tt01"."col001" = 20017476, VARCHAR2(20) 修改为 NUMBER (20)  
  ... ...                              // 14: "zb10"."col170" = "tbl2"."col004" = 20017476, VARCHAR2(20) 修改为 NUMBER (20)
  PRIMARY KEY ("COL002")
) ;

CREATE INDEX "LOCAL_BDZGBGYY"."IDX_TBL3_COL170" on "LOCAL_BDZGBGYY"."TBL3" (
 "COL170"
) GLOBAL ;

/* 表 TBL2 的定义 */
CREATE TABLE "SICP4_BASICINFOCENTER_EINP"."TBL2" (
  "COL004" NUMBER(20)  NOT NULL,      //  13: COL004 = 20017476
  ... ....
  PRIMARY KEY ("COL004")
) ;

优化思路

本案例中针对表和视图的 DDL 优化方案如下：

去除视图中 TO_NUMBER 类型转换，优化器选择了更优的 JOIN 顺序，SQL RT 从 2s 降为 150 ms（视图的入参为 NUMBER 数据类型，视图中的 COL001 在原表中也是 NUMBER 类型，所以视图中的 TO_NUMBER 强制转换是多余的）。
将表与视图中的关联字段的数据类型定义统一为 NUMBER(20)，规避类型转换后，使得 TBL3(IDX_TBL3_COL170) 可以做索引匹配，SQL RT 进一步下降为 20ms（因为 TBL3.COL170 是 VARCHAR 数据类型，视图上的查询条件 COL001 IN ( value,value,...) 传递到 TBL3.COL170 时发生了 VARCHAR 到 NUMBER 的类型转换，导致 TBL3.COL170 上的索引不能做匹配）。
对视图中的 4 张关联表进行分区和表组改造。该视图是业务的入口查询视图，此前使用该视图的 SQL 导致了少数 OBServer 节点的计算资源用满的问题，为了避免该热点视图可能再次成为瓶颈，通过分区改造把视图的查询压力分散到各个 OBServer 节点上，即统一对视图中的 4 张关联表按照关联键进行 Hash 分区，并加入表组，避免跨节点的关联查询。改造后，SQL RT进一步下降到 4ms。

优化后 SQL 的 Explain 信息如下，PX PARTITION ITERATOR 表明只在分区内执行 UNION ALL，此时 TBL3 上索引的代价非常低，性能得到了很大的提升。

====================================================================
|ID|OPERATOR                |NAME                   |EST. ROWS|COST|
--------------------------------------------------------------------
|0 |PX COORDINATOR          |                       |3        |477 |
|1 | EXCHANGE OUT DISTR     |:EX10000               |3        |405 |
|2 |  SUBPLAN SCAN          |V_TT01                 |3        |405 |
|3 |   PX PARTITION ITERATOR|                       |3        |405 |
|4 |    UNION ALL           |                       |3        |405 |
|5 |     NESTED-LOOP JOIN   |                       |2        |273 |
|6 |      MERGE ANTI JOIN   |                       |2        |183 |
|7 |       TABLE SCAN       |TT01(INDX_TT01_COL001) |2        |137 |
|8 |       SUBPLAN SCAN     |VIEW1                  |1        |46  |
|9 |        TABLE SCAN      |TBL3(INDEX_TBL3_COL170)|1        |46  |
|10|      TABLE SCAN        |TBL1(INDEX_TBL1_COL001)|1        |44  |
|11|     NESTED-LOOP JOIN   |                       |1        |132 |
|12|      TABLE SCAN        |TBL3(INDEX_TBL3_COL170)|1        |92  |
|13|      TABLE GET         |TBL2                   |1        |40  |
====================================================================