--- title: 关于大 inlist 优化的说明和验证-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 关于大 inlist 优化的说明和验证相关的常见问题和使用技巧，帮助您快速解决 关于大 inlist 优化的说明和验证的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # 关于大 inlist 优化的说明和验证 更新时间：2026-05-14 09:21 适用版本： V4.2.x、V4.3.x 内容类型：Troubleshoot 大 inlist 指的是 SQL 语句含有谓词 in，且 in 谓词条件中包含了大量的参数，如几万甚至几十万以上如下示例语句。 ```shell 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 硬解析的性能。 **相关参数：** ```shell 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 */ ``` 输出结果如下： ```shell *************************** 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) ``` **使用示例：** - 系统租户。 ```shell MySQL [oceanbase]> alter system set _inlist_rewrite_threshold = 1000 tenant=xxx; ``` - MySQL 模式业务租户。 ```shell MySQL [(none)]> alter system set _inlist_rewrite_threshold = 1000; ``` - Oracle 模式业务租户。 ```shell obclient [SYS]> alter system set "_inlist_rewrite_threshold" = 1000; ``` **Hint：** 通过添加 opt_param 的 `inlist_rewrite_threshold` 的 Hint，SQL 的 inlist 中常量个数超过 `inlist_rewrite_threshold` 则触发优化。 SQL中大INLIST常量个数超过1000时，则触发优化，示例如下。 ```shell obclient> select /*+ opt_param('inlist_rewrite_threshold', 1000) */ sum(t1.c1) from t1 where c1 in (1, 2, 3, ..., 799999, 800000); ``` **测试验证：** 1. 准备测试使用的表结构和数据。 ```shell [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) ``` 2. 准备大 inlist SQL 语句。 ```shell [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 ``` 3. 先清空所有的 plan cache，然后使用默认的 _inlist_rewrite_threshold=2147483647执行大 inlist SQL 语句。 ```shell [root@xxx ~]# obclient -h127.0.0.1 -usys@oracle -P2881 -pxxx -A -c -e "alter system flush plan cache global" ``` ```shell [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 ``` ```shell ##-- 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 ``` 输出结果如下： ```shell *************************** 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 ``` 4. 硬解析后再次重复执行相同的 SQL，执行速度很快，说明之前之前主要的耗时在于硬解析阶段。 ```shell [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 ``` real 0m1.150s user 0m0.058s sys 0m0.020s ``` real 0m0.997s user 0m0.065s sys 0m0.014s ``` ```shell ##-- 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 ``` 输出结果如下： ```shell *************************** 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 ``` 5. 将 _inlist_rewrite_threshold 调整为 1000。 ```shell obclient [SYS]> alter system set "_inlist_rewrite_threshold" = 1000; Query OK, 0 rows affected (0.005 sec) ``` 6. 清空所有的 plan cache 后使用 _inlist_rewrite_threshold=1000 执行大 inlist SQL 语句。 ``` real 0m1.782s user 0m0.068s sys 0m0.015s ``` ``` 输出结果如下： ```shell *************************** 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）及之后版本。 Previous [OceanBase 数据库集群内简单 SQL 语句因 -6004(lock_for_read need retry) 变慢的原因和解决方法](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002397820) Next [如何配置 OBProxy 捕获慢查询](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000001028) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线