OceanBase 中一个关于 NOT IN 子查询的 SQL 优化案例

通过一个案例了解 not in 对 NULL 值敏感的处理逻辑和优化方法。

作者：胡呈清，爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：[简书 | 轻松的鱼]，欢迎讨论。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 3300 字，预计阅读需要 11 分钟。

数据库版本：OceanBase3.2.3.3

问题描述

前段时间碰到一个慢 SQL，NOT IN 子查询被优化器改写成了 NESTED-LOOP ANTI JOIN，但是被驱动表全表扫描无法使用索引，执行耗时 16 秒。SQL 如下：

SELECT AGENT_ID, MAX(REL_AGENT_ID)
            FROM T_LDIM_AGENT_UPREL
           WHERE AGENT_ID NOT IN (select AGENT_ID
                                    from T_LDIM_AGENT_UPREL
                                   where valid_flg = '1')
           group by AGENT_ID;

简略执行计划如下：

==============================================================================================
|ID|OPERATOR              |NAME                                           |EST. ROWS|COST    |
----------------------------------------------------------------------------------------------
|0 |MERGE GROUP BY        |                                               |146      |62970523|
|1 | NESTED-LOOP ANTI JOIN|                                               |149      |62970511|
|2 |  TABLE SCAN          |T_LDIM_AGENT_UPREL(I_LDIM_AGENT_UPREL_AGENT_ID)|27760    |10738   |
|3 |  MATERIAL            |                                               |13880    |11313   |
|4 |   SUBPLAN SCAN       |VIEW1                                          |13880    |11115   |
|5 |    TABLE SCAN        |T_LDIM_AGENT_UPREL                             |13880    |10906   |
==============================================================================================

问题分析

1. 分析表结构、数据量

表结构如下，关联字段 AGENT_ID 是有索引的：

CREATE TABLE "T_LDIM_AGENT_UPREL" (
  "REL_AGENT_ID" NUMBER(22) CONSTRAINT "T_LDIM_AGENT_UPREL_OBNOTNULL_1679987669730612" NOT NULL ENABLE,
  "AGENT_ID" NUMBER(22),
  "EMPLOYEE_ID" NUMBER(22),
  "EMP_PARTY_FULLNAME" VARCHAR2(60),
  "GRP_ID" NUMBER(22),
  "GRP_PARTY_FULLNAME" VARCHAR2(255),
  "CS_ID" NUMBER(22),
  "CS_ORGAN_NAME" VARCHAR2(255),
  "CRT_DTTM" DATE,
  "LASTUPT_DTTM" DATE,
  "VALID_FLG" VARCHAR2(1),
  "VALID_DTTM" DATE,
  "INVALID_DTTM" DATE,
  CONSTRAINT "PK_T_LDIM_AGENT_UPREL" PRIMARY KEY ("REL_AGENT_ID")
);
CREATE INDEX "IDX_T_LDIM_AGENT_UPREL_CT" on "T_LDIM_AGENT_UPREL" ("CRT_DTTM") GLOBAL ;
CREATE INDEX "IDX_T_LDIM_AGENT_UPREL_LT" on "T_LDIM_AGENT_UPREL" ("LASTUPT_DTTM") GLOBAL ;
CREATE INDEX "I_LDIM_AGENT_UPREL_AGENT_ID" on "T_LDIM_AGENT_UPREL" ("AGENT_ID") GLOBAL ;

数据量：T_LDIM_AGENT_UPREL 表一共 2.7 万行，子查询结果 3900 行。

2. 判断直接原因

从执行计划、表结构和数据量来看，这个 SQL 效率低有两个原因：

1. 关联字段 AGENT_ID 有索引，但对被驱动表做查询时却使用全表扫描，效率必定低。为什么不走索引？
2. 既然被驱动表不走索引，基于代价的比较，优化器为什么没有选择更高效的 HASH ANTI JOIN？

问题得一个一个看，先分析第二个问题。

3. 使用 HINT 干预 JOIN 算法

使用如下 HINT 都不生效（并且尝试了 Outline Data 中的写法）：

/*+ use_hash(A B)*/ 
/*+ USE_HASH(@"SEL$1" ("VIEW1"@"SEL$1" )) */
/*+ NO_USE_NL_AGGREGATION */

执行计划显示 Used Hint 部分都为空，说明 HINT 无法生效，原因未知：

Used Hint:
-------------------------------------
  /*+
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      NO_USE_HASH_AGGREGATION(@"SEL$1")
      LEADING(@"SEL$1" ("REPORT.A"@"SEL$1" "VIEW1"@"SEL$1" ))
      USE_NL(@"SEL$1" ("VIEW1"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("VIEW1"@"SEL$1" ) LOCAL LOCAL)
      USE_NL_MATERIALIZATION(@"SEL$1" ("VIEW1"@"SEL$1" ))
      INDEX(@"SEL$1" "REPORT.A"@"SEL$1" "I_LDIM_AGENT_UPREL_AGENT_ID")
      FULL(@"SEL$2" "REPORT.B"@"SEL$2")
      END_OUTLINE_DATA
  */

4. 对比 Oracle 执行计划

Tips：当 OB 上看到的执行计划不符合预期，但又找不到原因时，可以对比 Oracle 的执行计划。

Oracle 上执行计划如下（这里得用 set autotrace on 的方式查看真实执行计划）：

• 可以使用 HASH ANTI JOIN，并且有个重要信息 HASH JOIN RIGHT ANTI NA （EXPLAIN 是看不到 NA 的），

直接搜索就可以得到大概的解释 NA 即 Null-Aware Anti Join，这种反连接能够处理 NULL 值。啥意思？下面展开讲讲。

SQL> set autotrace on
SQL> SELECT AGENT_ID, MAX(REL_AGENT_ID)
            FROM T_LDIM_AGENT_UPREL
           WHERE AGENT_ID NOT IN (select AGENT_ID
                                    from T_LDIM_AGENT_UPREL
                                   where valid_flg = '1')
           group by AGENT_ID;  2    3    4    5    6  

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1033962367
-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                    |     9 |   171 |   276   (2)| 00:00:04 |
|   1 |  HASH GROUP BY           |                    |     9 |   171 |   276   (2)| 00:00:04 |
|*  2 |   HASH JOIN RIGHT ANTI NA|                    |  9672 |   179K|   275   (2)| 00:00:04 |
|*  3 |    TABLE ACCESS FULL     | T_LDIM_AGENT_UPREL |  3886 | 31088 |   137   (1)| 00:00:02 |
|   4 |    TABLE ACCESS FULL     | T_LDIM_AGENT_UPREL | 28098 |   301K|   137   (1)| 00:00:02 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 -