大 inlist 转 Values Table 优化的几个已知问题和修复版本说明

大 inlist 指的是 SQL 语句含有谓词 in，且 in 谓词条件中包含了大量的参数，如几万甚至几十万以上，如下所示。

select sum(t1.c1) from t1 where c1 in (1, 2, 3, ..., 799999, 800000);

外部生产环境的大 inlist 场景下会出现执行 SQL 语句时在硬解析阶段耗时特别长、甚至打爆 CPU 或者内存的情况。因此，在 OceanBase 数据库 V4.2.2 版本中，针对这种大 inlist 做了改写优化以缓解硬解析阶段时间长和资源损耗高的问题。

关于大 inlist 优化的详细内容以及优化效果可以参考：关于大 inlist 优化的说明和验证。

本文主要介绍关于大 inlist 优化的几个已知问题和对应修复版本，供外部交付和客户项目测试中参考使用。

详细说明

1. 大 inlist SQL 语句执行时自动转 Values Table 的解析优化只在 OceanBase 数据库 V4.2.2 及更高的 OBServer 内核版本上实现了，最新的 OceanBase 数据库 V4.2.1 BP 版本上也未包含该优化。

2. 大 inlist SQL 语句第一次硬解析慢只针对于 inlist 是常量列表的情况，如果 inlist 是个子查询，第一次硬解析也不会慢的。

   obclient [SYS]> alter system flush plan cache global;
   Query OK, 0 rows affected (0.010 sec)
   
   obclient [SYS]> select count(*) from test_table where x in (select level from dual connect by level <= 500001);
   +----------+
   | COUNT(*) |
   +----------+
   |    10000 |
   +----------+
   1 row in set (2.768 sec)
   
   obclient [SYS]> select count(*) from test_table where x in (select level from dual connect by level <= 500001);
   +----------+
   | COUNT(*) |
   +----------+
   |    10000 |
   +----------+
   1 row in set (2.722 sec)
   
   obclient [SYS]> select count(*) from test_table where x in (select level from dual connect by level <= 500001);
   +----------+
   | COUNT(*) |
   +----------+
   |    10000 |
   +----------+
   1 row in set (2.766 sec)
   

3. 如果 SQL 语句执行时走了普通文本协议，大 inlist SQL 语句只是在第一次硬解析时慢，一旦硬解析过了，执行计划存在于计划缓存 plan cache 中，后面重复执行相同 SQL 走软解析就不会再慢了。

4. 如果 SQL 语句执行时走了 PS 二进制协议（比如使用了 Java 代码中的 PreparedStatement 或 ODBC 代码走了 PS 协议），在 Prepare 阶段主要做的工作是语法解析（Parser）和语义分析（Resolver），不会生成执行计划，执行计划会在第一次 Execute 的时候生成。在 Prepare 阶段和 Execute 阶段都需要做大 inlist 转 Values Table 的改写，如果某个阶段没有做改写，inlist 个数非常多的情况下该阶段就会较慢（如果走了 PS 二合一协议，则只有 Execute 阶段会慢）。

5. 内部测试大 inlist 优化功能时发现：如果大 inlist SQL 语句走了 remote 执行计划速度会非常慢（[OB 4.3.2 BP1] inlist optimization doesn't work when the sql statement is executed on remote node(plan_type=remote)），该问题最终定位是 inlist 优化在最初禁用 PS 模式改写的时候，范围禁多了导致 remote 改写也不生效了。该问题的修复版本为：V4.2.5 Hotfix2、V4.3.3 BP1 Hotfix4。

   备注：在该问题修复之前，PS模式下全部禁掉了大inlist改写的优化功能。

6. 上面第 5 点中提到的 BUG 修复除了解决掉了大 inlist SQL 语句走 remote 执行计划时速度慢的问题，也覆盖了走 PS 协议且不包含 "?" 的场景，即不包含 "?" 的 PS 协议可以走到大 inlist 的改写优化。但如果走了 PS 协议且 PreparedStatement 中包含了 "?"，还是会遇到 Prepare 阶段速度较慢的问（[OB 4.3.3 BP1 hotfix2] large inlist select query cost too much time in the prepare stage when using PS binary protocol）。

   测试数据准备如下：

   [root@ob62 ~]# obclient -h127.1 -usys@perforacle -P2881 -pxxx -A -c
   Welcome to the OceanBase.  Commands end with ; or \g.
   Your OceanBase connection id is 3221521592
   Server version: OceanBase 4.3.3.1 (r101020012024111309-bbe812c2baaf7f18c4b74511b091569f8ab457ef) (Built Nov 13 2024 10:18:56)
   
   Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   obclient [SYS]> select @@version_comment from dual;
   +---------------------------------------------------------------------------------------------------------------+
   | @@VERSION_COMMENT                                                                                             |
   +---------------------------------------------------------------------------------------------------------------+
   | OceanBase 4.3.3.1 (r101020012024111309-bbe812c2baaf7f18c4b74511b091569f8ab457ef) (Built Nov 13 2024 10:18:56) |
   +---------------------------------------------------------------------------------------------------------------+
   1 row in set (0.002 sec)
   
   obclient [SYS]> create table test_table (x int, y int, z int, primary key (x));
   Query OK, 0 rows affected (0.239 sec)
   
   obclient [SYS]> create index test_table_idx1 on test_table (y);
   Query OK, 0 rows affected (0.466 sec)
   
   obclient [SYS]> insert into test_table select level,level,level from dual connect by level <= 500001;
   Query OK, 500001 rows affected (6.460 sec)
   Records: 500001  Duplicates: 0  Warnings: 0
   
   obclient [SYS]> commit;
   Query OK, 0 rows affected (0.009 sec)
   
   obclient [SYS]> select count(*) from test_table;
   +----------+
   | COUNT(*) |
   +----------+
   |   500001 |
   +----------+
   1 row in set (0.326 sec)
   

   Java 测试代码如下：

   [root@ob62 ~]# cat TestInlist.java
   import java.sql.*;
   import java.text.SimpleDateFormat;
   
   public class TestInlist {
   public static void select() throws SQLException{
    String ip = "127.0.0.1";
    String port = "2881";
    String database = "SYS";
    String url = String.format("jdbc:oceanbase://%s:%s/%s?useSSL=false&useUnicode=true&characterEncoding=utf-8&useLocalSessionState=true&useServerPrepStmts=true", ip, port, database);
    String username = "sys@perforacle";
    String password = "xxx";
    Connection conn = null;
    try {
      Class.forName("com.alipay.oceanbase.jdbc.Driver");
      conn = DriverManager.getConnection(url, username, password);
   
      StringBuilder sb = new StringBuilder();
      for (int i = 1; i <= 500000; i++) {
        if (i > 1) {
          sb.append(",");
        }
        sb.append(i);
      }
      String inlist_str = sb.toString();
   
      SimpleDateFormat formatter= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
      Date date = new Date(System.currentTimeMillis());
      System.out.println("before prepare: " + formatter.format(date));
   
      PreparedStatement ps = conn.prepareStatement("select max(x) as n from test_table where y>= ? and x in (" + inlist_str + ")");
      ps.setInt(1, 0);
   
      date = new Date(System.currentTimeMillis());
      System.out.println("after prepare: " + formatter.format(date));
   
      ResultSet rs = ps.executeQuery();
      while (rs.next()){
        System.out.println(String.format("max is : %s", rs.getInt(1)));
      }
   
      date = new Date(System.currentTimeMillis());
      System.out.println("after executeQuery: " + formatter.format(date));
   
      rs.close();
      ps.close();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (null != conn) {
        conn.close();
      }
    }
   }
   
   public static void main(String[] args) {
    try {
      select();
    } catch (SQLException e) {
      e.printStackTrace();
    }
   }
   }
   

   执行结果如下：

   [root@ob62 ~]# date ; time java -cp ./oceanbase-client-2.4.13.jar:./ TestInlist
   Sun Nov 24 19:25:50 CST 2024
   before prepare: 2024-11-24 19:25:50
   after prepare: 2024-11-24 19:27:31   <== 这边 Prepare 阶段耗时长达 1m 41s
   max is : 500000
   after executeQuery: 2024-11-24 19:27:33
   
   real    1m43.211s
   user    0m1.091s
   sys     0m0.170s
   

   该问题的修复版本为：V4.2.5 BP1、V4.3.3 BP1 Hotfix4、V4.3.5。

影响租户

影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。

适用版本

OceanBase 数据库 V4.2.2 及之后版本。