SELECT 投影列中关联子查询的 SQL 性能问题

适用版本

OceanBase 数据库所有版本。

问题现象

SQL 的 SELECT 列表中包含 IN 子查询（标量子查询），SQL 执行超时，存在性能问题。如果把 SELECT 投影中的 IN 查询去掉，SQL 仅需要 18 秒。

• 问题 SQL 如下：

  SELECT CASE
          WHEN a.cus_ac IN (SELECT d2.cus_ac FROM ods. table_d d2) THEN d1.rcv_br
          ELSE a.rcv_br
      END AS rcv_br
  FROM   ods. table_a a
      LEFT JOIN rts. table_b b
              ON Substr(20350623, 5, 2) = b.month
      LEFT JOIN ods. table_c c
              ON a.cus_ac = c. cus_ac
      LEFT JOIN ods.table_d d1
              ON a. cus_ac = d1.cus_ac
      LEFT JOIN ods.table_e e
              ON a. cus_ac = e.cus_ac
      LEFT JOIN rts.table_f f
              ON a.aco_br = f. org_code 
  

• EXPLAIN 信息如下：

  ==========================================================================================
  |ID|OPERATOR                                |NAME            |EST. ROWS    |COST         |
  ------------------------------------------------------------------------------------------
  |0 |SUBPLAN FILTER                          |                |3.176721e+12 |1.270232e+15 |
  |1 | HASH RIGHT OUTER JOIN                  |                |3.176721e+12 |705654824484 |
  |2 |  TABLE SCAN                            |F               |100000       |38681        |
  |3 |  MERGE OUTER JOIN                      |                |3241218738   |729667481    |
  |4 |   MERGE OUTER JOIN                     |                |31198358     |12100047     |
  |5 |    MERGE OUTER JOIN                    |                |1199599      |5173238      |
  |6 |     NESTED-LOOP OUTER JOIN CARTESIAN   |                |1199599      |5110492      |
  |7 |      TABLE SCAN                        |A(TABLE_A_I1)   |1199599      |4671605      |
  |8 |      MATERIAL                          |                |1            |46           |
  |9 |       TABLE SCAN                       |B               |1            |46           |
  |10|     TABLE SCAN                         |E(TABLE_E_I1)   |17           |46           |
  |11|    SORT                                |                |2653         |5880         |
  |12|     TABLE SCAN                         |D1              |2653         |1027         |
  |13|   TABLE SCAN                           |C(TABLE_C_I1)   |10599        |4100         |
  |14| TABLE SCAN                             |D2(TABLE_D_I1)  |2653         |1027         |
  ==========================================================================================
  

问题原因

EXPLAIN 信息显示该 SQL 执行中的主要性能瓶颈在：

• 算子 0: SUBPLAN FILTER。
  • 该算子需要用算子 1 的结果，3.17672le+12 条记录，逐一与 subplan（14 号算子） 进行匹配。
  • 该算子的代价估算为：1.270232e+15 - 705654824484 = 1.269526E+15。
• 算子 1: HASH RIGHT OUTER JOIN。
  • 该算子需要用算子 3 的结果，3241218738 条记录，逐一与 算子 2 的 hash 结果，100000 条记录，进行 join。
  • 该算子的代价估算为：705654824484 - 38681 - 729667481 =7.049251E+11。

最主要的性能瓶颈则是算子 0：SUBPLAN FILTER。SUBPLAN FILTER 的实现类似于 NLJ，当主查询的结果集很大时，需要对 SUBPLAN 中重复执行太多次的 TABLE SCAN（走索引 TABLE_D_I1），效率非常低。

解决方法

改写 SQL，将标量子查询改写为 LEFT JOIN，让优化器根据代价估算选择更好的 Join 方式。需要注意的是，要保证改写后字段 table_d.cus_ac 的唯一性，如果该字段不是唯一键，则需要使用 DISTINCT 来去重。

• 改写后的 SQL 如下：

  SELECT CASE
          WHEN d2.cus_ac IS NULL THEN a_f.arcvbr
          ELSE a_f.brevbr
      END AS rcv_br
  FROM   (SELECT a. cus_ac,
          a.rcv_br  AS arcvbr,
          d1. rcv_br AS brevbr
          FROM   ods. table_a a
          LEFT JOIN rts. table_b b
          ON Substr(20350623, 5, 2) = b.month
          LEFT JOIN ods. table_c c
          ON a.cus_ac = c. cus_ac
          LEFT JOIN ods.table_d d1
          ON a. cus_ac = d1.cus_ac
          LEFT JOIN ods.table_e e
          ON a. cus_ac = e.cus_ac
          LEFT JOIN rts.table_f f
          ON a.aco_br = f. org_code) a_f
      LEFT JOIN （select distinct cus_ac from ods. table_d） d2
              ON a_f.cus_ac = d2. cus_ac 
  

• 改写后的 EXPLAIN 信息未搜集。根据客户描述，改写后，优化器使用 Hash Join 把主查询与 d2 进行关联，执行效率提升万倍。