划线反馈

关于大 inlist 优化的说明和验证

更新时间：2026-05-14 09:21

适用版本：V4.2.x、V4.3.x内容类型：Troubleshoot

大 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 优化的详细内容以及优化效果的验证。

详细说明

用户使用 DML 语法时，如果 where 谓词条件中包含有表达式 in 并且表达式右支 inlist 是大量常量时，在 SQL 的硬解析阶段 CPU 和内存消耗较重。当 in 表达式右支 inlist 中常量个数超过规定的优化阈值 _inlist_rewrite_threshold 时，则对 inlist 优化改写为对 values_table 表的子查询，提升 SQL 硬解析的性能。

相关参数：

MySQL [oceanbase]> select * from __all_virtual_sys_parameter_stat where name='_inlist_rewrite_threshold'\G   /* [1, 2147483647] specifies transform how much const params in IN list to values table 指定触发 inlist 转 values table 的改写时，inlist 中常量元素个数。默认值：2147483647。TENANT 租户级别/DYNAMIC_EFFECTIVE */

输出结果如下：

*************************** 1. row ***************************
         zone: zone1
     svr_type: observer
       svr_ip: 192.168.xxx.xx
     svr_port: 2882
         name: _inlist_rewrite_threshold
    data_type: NULL
        value: 2147483647
 value_strict: NULL
         info: [1, 2147483647]specifies transform how much const params in IN list to values table
  need_reboot: NULL
      section: TENANT
visible_level: NULL
        scope: TENANT
       source: DEFAULT
   edit_level: DYNAMIC_EFFECTIVE
default_value: 2147483647
    isdefault: 1
1 row in set (0.035 sec)

使用示例：

系统租户。

MySQL [oceanbase]> alter system set _inlist_rewrite_threshold = 1000 tenant=xxx;

MySQL 模式业务租户。

MySQL [(none)]> alter system set _inlist_rewrite_threshold = 1000;

Oracle 模式业务租户。

obclient [SYS]> alter system set "_inlist_rewrite_threshold" = 1000;

Hint：

通过添加 opt_param 的 inlist_rewrite_threshold 的 Hint，SQL 的 inlist 中常量个数超过 inlist_rewrite_threshold 则触发优化。

SQL中大INLIST常量个数超过1000时，则触发优化，示例如下。

obclient> select /*+ opt_param('inlist_rewrite_threshold', 1000) */ sum(t1.c1) from t1 where c1 in (1, 2, 3, ..., 799999, 800000);

测试验证：

准备测试使用的表结构和数据。

[root@xxx ~]# obclient -h127.0.0.1 -usys@oracle -P2881 -pxxx -A -c
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221678020
Server version: OceanBase 4.2.4.0 (r100010032024072923-01587a1ca904ab78985aa244ebbac851438c61d2) (Built Jul 30 2024 07:31:22)

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.2.4.0 (r100010032024072923-01587a1ca904ab78985aa244ebbac851438c61d2) (Built Jul 30 2024 07:31:22) |
+---------------------------------------------------------------------------------------------------------------+
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)

准备大 inlist SQL 语句。

[root@xxx ~]# echo -n "select count(*) from test_table where x in (" > 1.sql
[root@xxx ~]# for i in {1..500000}; do echo -n "$i," >> 1.sql ; done
[root@xxx ~]# echo "500001);" >> 1.sql
[root@ob71 ~]# wc -l 1.sql
1 1.sql

先清空所有的 plan cache，然后使用默认的 _inlist_rewrite_threshold=2147483647执行大 inlist SQL 语句。

[root@xxx ~]# obclient -h127.0.0.1 -usys@oracle -P2881 -pxxx -A -c -e "alter system flush plan cache global"

[root@ob71 ~]# time obclient -h127.0.0.1 -usys@oracle -P2881 -pxxx -A -c -e "source 1.sql"
+----------+
| COUNT(*) |
+----------+
|   500001 |
+----------+

real    4m29.644s
user    0m0.066s
sys     0m0.012s

##-- gv$ob_sql_audit 中记录的对应的硬解析时间和执行时间如下。
MySQL [oceanbase]> select usec_to_time(request_time),is_hit_plan,get_plan_time,execute_time,elapsed_time from gv$ob_sql_audit where tenant_id=1016 and query_sql like 'select count(*) from test_table%' order by request_time\G

输出结果如下：

*************************** 12. row ***************************
usec_to_time(request_time): 2024-10-11 22:37:52.742475
          is_hit_plan: 0   <== 走了硬解析
          get_plan_time: 268742702   <== 获取执行计划的时间
          execute_time: 699576   <== 执行时间
          elapsed_time: 269443808

硬解析后再次重复执行相同的 SQL，执行速度很快，说明之前之前主要的耗时在于硬解析阶段。

[root@xxx ~]# time obclient -h127.0.0.1 -usys@oracle -P2881 -pxxx -A -c -e "source 1.sql"
+----------+
| COUNT(*) |
+----------+
|   500001 |
+----------+

real    0m1.065s
user    0m0.061s
sys     0m0.018s

[root@ob71 ~]# time obclient -h127.0.0.1 -usys@oracle -P2881 -pxxx -A -c -e "source 1.sql"
+----------+
| COUNT(*) |
+----------+
|   500001 |
+----------+

real    0m1.150s
user    0m0.058s
sys     0m0.020s

[root@ob71 ~]# time obclient -h127.0.0.1 -usys@oracle -P2881 -pxxx -A -c -e "source 1.sql"
+----------+
| COUNT(*) |
+----------+
|   500001 |
+----------+

real    0m0.997s
user    0m0.065s
sys     0m0.014s

##-- gv$ob_sql_audit 中记录的对应的获取执行计划的时间和执行时间如下。
MySQL [oceanbase]> select usec_to_time(request_time),is_hit_plan,get_plan_time,execute_time,elapsed_time from gv$ob_sql_audit where tenant_id=1016 and query_sql like 'select count(*) from test_table%' order by request_time\G

输出结果如下：

*************************** 13. row ***************************
usec_to_time(request_time): 2024-10-11 22:43:07.703836
            is_hit_plan: 1   <== 走了软解析
          get_plan_time: 332857   <== 获取执行计划的时间
           execute_time: 562680   <== 执行时间
           elapsed_time: 905535
*************************** 14. row ***************************
usec_to_time(request_time): 2024-10-11 22:43:09.956054
            is_hit_plan: 1   <== 走了软解析
          get_plan_time: 332541   <== 获取执行计划的时间
           execute_time: 625899   <== 执行时间
           elapsed_time: 961689
*************************** 15. row ***************************
usec_to_time(request_time): 2024-10-11 22:43:12.006697
            is_hit_plan: 1   <== 走了软解析
          get_plan_time: 309120   <== 获取执行计划的时间
           execute_time: 531197   <== 执行时间
           elapsed_time: 843615

将 _inlist_rewrite_threshold 调整为 1000。

obclient [SYS]> alter system set "_inlist_rewrite_threshold" = 1000;
Query OK, 0 rows affected (0.005 sec)

清空所有的 plan cache 后使用 _inlist_rewrite_threshold=1000 执行大 inlist SQL 语句。

[root@xxx ~]# obclient -h127.0.0.1 -usys@oracle -P2881 -pxxx -A -c -e "alter system flush plan cache global"

[root@xxx ~]# time obclient -h127.0.0.1 -usys@oracle -P2881 -pxxx -A -c -e "source 1.sql"
+----------+
| COUNT(*) |
+----------+
|   500001 |
+----------+

real    0m1.782s
user    0m0.068s
sys     0m0.015s

##-- gv$ob_sql_audit 中记录的对应的硬解析时间和执行时间如下。
MySQL [oceanbase]> select usec_to_time(request_time),is_hit_plan,get_plan_time,execute_time,elapsed_time from gv$ob_sql_audit where tenant_id=1016 and query_sql like 'select count(*) from test_table%' order by request_time\G

输出结果如下：

*************************** 16. row ***************************
usec_to_time(request_time): 2024-10-11 22:44:42.948010
            is_hit_plan: 0   <== 走了硬解析
          get_plan_time: 895960   <== 获取执行计划的时间
           execute_time: 793826   <== 执行时间
           elapsed_time: 1691284

说明

从上面的测试可以看出：大 inlist SQL 语句只是第一次硬解析慢，一旦硬解析过了，执行计划存在于 plan cache，后面重复执行就不会再慢了。
大 inlist SQL 语句第一次硬解析慢只针对于 inlist 是常量的情况，如果 inlist 是个子查询，第一次硬解析也不会慢的。
大 inlist 转 Values Table 后，可能会出现计划改变后导致的性能回退，例如 fast min/max 中，消序和 limit 下压不能同时进行了。
inlist 配置项 _inlist_rewrite_threshold 默认值为 int 类型的最大值 2147483647，in 个数达到这个值大 in 解析优化才生效，使用时可以将 _inlist_rewrite_threshold 调小。

影响租户

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

适用版本

OceanBase 数据库 V4.2.2 GA（oceanbase-4.2.2.0-100000082024011317）及之后版本。

OceanBase 数据库集群内简单 SQL 语句因 -6004(lock_for_read need retry) 变慢的原因和解决方法

如何配置 OBProxy 捕获慢查询

OceanBase 数据库