--- title: 针对大 in 谓词 query range 抽取的优化-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 针对大 in 谓词 query range 抽取的优化相关的常见问题和使用技巧，帮助您快速解决 针对大 in 谓词 query range 抽取的优化的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 针对大 in 谓词 query range 抽取的优化 更新时间：2023-12-25 05:31 适用版本： V3.2.x、V4.1.x、V4.2.x 内容类型：Troubleshoot 当 SQL 中存在 in 谓词，并且执行计划选择了 in 谓词相关的索引，进行了 query range 抽取，可能出现查询结果错的问题。 ## IN 优化引入的正确性问题的规避方式 遇到 in 谓词 query range 抽取相关的正确性问题时，可以尝试通过以下方法进行规避。 1. 将 in 表达式改成等价的 or 表达式，例如 `c1 in ('1','2') => (c1 = '1' or c1 = '2')`。 2. 通过 hint 禁止走包含 in 表达式所在列的索引，例如 `select /*+full(t1)*/...`。 3. 在 in 表达式后面加个 is true（适用于命中主键索引的场景），例如 `c1 in ('1','2') is true`。 4. 通过 `_enable_in_range_optimization` 的隐藏配置项来决定是否打开 in 优化，包含该配置项的版本及默认行为如下。 | 包含 IN 优化的版本 | 包含 IN 优化开关的版本 | IN 优化默认值 | | --- | --- | --- | | V4.2.0 | V4.2.0 | 打开 | | V4.1.0 | V4.1.0 BP2 | 打开 | | V3.2.4 | V3.2.4 BP4 | 关闭 | | V3.2.3 | V3.2.3 BP8 Hotfix5 | 关闭 | 其中，方法一适合 in 中参数比较少的场景；方法二可通过绑 hint/改索引做到，这种方式用得最多，但是不适合 in 表达式所在列是主键的场景；方法三可应用于任意场景，但是做法比较取巧，一般作为方法二的补充。 #### 说明 本文所有的 case 中存在 ci 时，均可认为存在(c1,c2,c3,..., ci) 的索引或主键，例如 case:(c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8)，表示含有联合主键/索引(c1,c2,c3,c4)。 ## IN 优化引入的正确性问题和解决方法 ### 场景 01：SQL 中含有 in 表达式导致正确性问题 #### 问题现象 当 SQL 中含有 `(c1 in (?,?) or c1 in (?,?)) and c2` 特证的语句，且出现的不同列都是联合主键/索引时，会出现正确性问题。 例如 (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8)，表示含有联合主键/索引 (c1,c2,c3,c4)。 示例。 1. 创建表与索引 ```shell obclient [SYS]> create table t1(c1 varchar2(50), c2 varchar2(50)); Query OK, 0 rows affected (0.029 sec) ``` ```shell obclient [SYS]> create index idx on t1(c1,c2); Query OK, 0 rows affected (0.448 sec) ``` 2. 存在正确性问题的 SQL。 ```shell obclient [SYS]> select /*+index(t1 idx)*/* from t1 where (c1 in ('AD', 'AC') or c1 in ('AD', 'ZT')) and c2 = '01'; Empty set (0.006 sec) ``` 3. 展示存在正确性问题的 SQL 的执行计划。 ```shell obclient [SYS]> explain extended_noaddr select /*+index(t1 idx)*/* from t1 where (c1 in ('AD', 'AC') or c1 in ('AD', 'ZT')) and c2 = '01'; ``` 输出结果如下: ```shell +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------- |0 |TABLE SCAN|T1(IDX)|2970 |1149| ====================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2]), filter(nil), access([T1.C1], [T1.C2]), partitions(p0), is_index_back=false, range_key([T1.C1], [T1.C2], [T1.__pk_increment]), range(AD,MIN,MIN ; AD,MAX,MAX), (AC,MIN,MIN ; AC,MAX,MAX), (ZT,MIN,MIN ; ZT,MAX,MAX), range_cond([T1.C2 = ?], [T1.C1 IN (?, ?) OR T1.C1 IN (?, ?)]) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec) ``` #### 问题的风险及影响 正确性问题。 #### 影响版本 OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 至 V3.2.4 BP3 (oceanbase-3.2.4.3-103000032023041816) 、V4.1.0 (oceanbase-4.1.0.0-100001122023040322) 版本、V4.1.0 BP1 (oceanbase-4.1.0.0-101000052023050621) 版本。 #### 解决方法 升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 版本、V3.2.4 BP4 (oceanbase-3.2.4.4-104000052023062021) 版本、V4.1.0 BP2 (oceanbase-4.1.0.1-102000042023061309) 版本。 #### 规避方案 - 改写 SQL。 ```shell select * from t1 where ((c1 = 'AD' or c1 = 'AC') or (c1 = 'AD' or c1 = 'ZT')) and c2 = '01'; ``` - 添加 FULL Hint 走主表。 ```shell select /*+full(t1)*/* from t1 where (c1 in ('AD', 'AC') or c1 in ('AD', 'ZT')) and c2 = '01'; ``` - in 表达式后加 is true。 ```shell select * from t1 where (c1 in ('AD', 'AC') is true or c1 in ('AD', 'ZT') is true) and c2 = '01'; ``` ### 场景 02：SQL 中含有 in 表达式导致正确性问题 #### 问题现象 当 SQL 中含有 `(c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8)` 特征，且满足以下三个条件时，会出现正确性问题。 1. c1,c2,c3,c4 四个索引列。 2. c1，c2 必须是 or 的普通表达式，c3 是普通或者 in 表达式，c4 必须是 in 表达式。 3. 四个列的表达式以 and 连接。 示例。 1. 创建表与索引 ```shell obclient [SYS]> create table t1(c1 int, c2 int, c3 int, c4 int, c5 int); Query OK, 0 rows affected (0.027 sec) ``` ```shell obclient [SYS]> create index idx on t1(c1,c2,c3,c4); Query OK, 0 rows affected (0.438 sec) ```shell obclient [SYS]> select /*+index (t1 idx)*/ * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8); Empty set (0.007 sec) ```shell obclient [SYS]> explain extended_noaddr select /*+index (t1 idx)*/ * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8); ``` 输出结果如下: ```shell ============================================= |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| --------------------------------------------- |0 |TABLE SCAN|t1(idx)|1 |5 | ============================================= Outputs & filters: ------------------------------------- 0 - output([t1.c1(0x7f377403db10)], [t1.c2(0x7f3774040a60)], [t1.c3(0x7f3774042710)], [t1.c4(0x7f3774044170)], [t1.c5(0x7f37740447d0)]), filter(nil), rowset=256 access([t1.__pk_increment(0x7f3774045510)], [t1.c1(0x7f377403db10)], [t1.c2(0x7f3774040a60)], [t1.c3(0x7f3774042710)], [t1.c4(0x7f3774044170)], [t1.c5(0x7f37740447d0)]), partitions(p0) is_index_back=true, is_global_index=false, range_key([t1.c1(0x7f377403db10)], [t1.c2(0x7f3774040a60)], [t1.c3(0x7f3774042710)], [t1.c4(0x7f3774044170)], [t1.__pk_increment(0x7f3774045510)]), range(1,3,5,7,MIN ; 1,3,5,7,MAX), (1,3,5,8,MIN ; 1,3,5,8,MAX), (1,4,5,7,MIN ; 1,4,5,7,MAX), (1,4,5,8,MIN ; 1,4,5,8,MAX), (2,3,5,7,MIN ; 2,3,5,7,MAX), (2,3,5,8,MIN ; 2,3,5,8,MAX), range_cond([t1.c3(0x7f3774042710) = 5(0x7f3774042060)], [t1.c1(0x7f377403db10) = 1(0x7f377403c830) OR t1.c1(0x7f377403db10) = 2(0x7f377403d3e0)(0x7f377403bc80)], [t1.c2(0x7f3774040a60) = 3(0x7f377403f780) OR t1.c2(0x7f3774040a60) = 4(0x7f3774040330)(0x7f377403ebd0)], [t1.c4(0x7f3774044170) IN (7, 8)(0x7f3774047670)(0x7f3774042c70)]) ``` ```shell select * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and (c4 = 7 or c4 = 8); ```shell select /*+full (t1)*/ * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8); ```shell select /*+full (t1)*/ * from t1 where (c1 = 1 or c1 = 2) and (c2 = 3 or c2 = 4) and c3 = 5 and c4 in (7,8) is true; ``` ### 场景 03：SQL 中含有 in 表达式导致正确性问题 #### 问题现象 当 SQL 中含有 in 表达式和普通表达式做完 or 合并后，in 中只剩一个参数，如场景 02 中案例做完 or 合并后变为 `c1 in (7) or c1 < 3 or c1 > 10` 时，会出现正确性问题。 示例。 1. 创建主键表。 ```shell obclient [SYS]> CREATE TABLE t1 ( pk int primary key); Query OK, 0 rows affected (0.021 sec) ``` 2. 插入语句并提交。 ```shell obclient [SYS]> INSERT INTO t1 VALUES (1),(2); Query OK, 2 rows affected (0.004 sec) ``` ```shell obclient [SYS]> commit; Query OK, 0 rows affected (0.001 sec) ``` 3. 关闭全局 Plan Cache。 ```shell obclient [SYS]> set @@ob_enable_plan_cache = 0; ``` 4. 存在正确性问题的 SQL。 ```shell obclient [SYS]> SELECT * FROM t1 WHERE PK IN (7,10) OR pk < 3 or pk > 10; Empty set (0.005 sec) ``` 5. 展示存在正确性问题的 SQL 的执行计划。 ```shell obclient [SYS]> explain extended_noaddr SELECT * FROM t1 WHERE PK IN (7,10) OR pk < 3 or pk > 10; ``` 输出结果如下: ```shell ================================================ |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| ------------------------------------------------ |0 |TABLE GET|t1 |4 |2 | -- 这个不是Table Get的 ================================================ Outputs & filters: ------------------------------------- 0 - output([t1.pk]), filter(nil), rowset=256 access([t1.pk]), partitions(p0) is_index_back=false, is_global_index=false, range_key([t1.pk]), range[7 ; 7], (NULL ; 3), [10 ; MAX), range_cond([(T_OP_OR, t1.pk IN (7, 10), t1.pk < 3, t1.pk > 10)]) ``` #### 影响版本 OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 至 V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 、V4.1.0 (oceanbase-4.1.0.0-100001122023040322) 版本、V4.1.0 BP1 (oceanbase-4.1.0.0-101000052023050621) 版本。 #### 解决方法 升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP9 (oceanbase-3.2.3.3-109000182023071410) 版本、V3.2.4 BP3 (oceanbase-3.2.4.3-103000032023041816) 版本、V4.1.0 BP2 (oceanbase-4.1.0.1-102000042023061309) 版本。 ```shell SELECT * FROM t1 WHERE (PK = 7 or pk = 10) OR pk < 3 or pk > 10; ```shell SELECT * FROM t1 WHERE PK IN (7,10) is true OR pk < 3 or pk > 10; ``` #### 注意 PK 是主键列，走主表的方法无效。 ### 场景 04：SQL 中含有 in 表达式导致正确性问题 #### 问题现象 当 SQL 中含有 `(c1 = x or c1 = ...) and c2 in (...) and c3 ...` 特征时，会出现正确性问题。 示例。 1. 创建表与索引。 ```shell obclient [SYS]> create table t1 (c1 int, c2 int, c3 int, c4 int); Query OK, 0 rows affected (0.31 sec) ``` ```shell obclient [SYS]> create index idx1 on t1(c1,c2,c3); Query OK, 0 rows affected (0.418 sec) ``` 2. 插入语句。 ```shell obclient [SYS]> insert into t1 values(2,4,7,2),(2,5,7,2),(2,4,7,3),(3,4,7,2),(3,5,7,2); Query OK, 2 rows affected (0.006 sec) ``` 3. 存在正确性问题的 SQL。 ```shell obclient [SYS]> select /*+index(t1 idx1)*/ * from t1 where (c1 = 2 or c1 = 3) and c2 in (4,5,6) and c3 = 7; ``` 输出结果如下: ```shell +------+------+------+------+ | C1 | C2 | C3 | C4 | +------+------+------+------+ | 2 | 4 | 7 | 2 | | 2 | 4 | 7 | 3 | | 2 | 4 | 7 | 2 | | 2 | 4 | 7 | 3 | | 2 | 5 | 7 | 2 | | 3 | 4 | 7 | 2 | | 2 | 5 | 7 | 2 | | 2 | 5 | 7 | 2 | | 3 | 4 | 7 | 2 | | 3 | 5 | 7 | 2 | | 3 | 4 | 7 | 2 | | 3 | 5 | 7 | 2 | +------+------+------+------+ 12 rows in set (0.004 sec) ``` 4. 展示存在正确性问题的 SQL 的执行计划。 ```shell obclient [SYS]> obclient [SYS]> explain extended_noaddr select /*+index(t1 idx1)*/ * from t1 where (c1 = 2 or c1 = 3) and c2 in (4,5,6) and c3 = 7; ``` 输出结果如下: ```shell +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ======================================== |ID|OPERATOR |NAME |EST. ROWS|COST | ---------------------------------------- |0 |TABLE SCAN|T1(IDX1)|15001 |59050| ======================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2], [T1.C3], [T1.C4]), filter(nil), access([T1.C1], [T1.C2], [T1.C3], [T1.C4]), partitions(p0), is_index_back=true, range_key([T1.C1], [T1.C2], [T1.C3], [T1.__pk_increment]), range(2,4,7,MIN ; 2,4,7,MAX), (2,5,7,MIN ; 2,5,7,MAX), (2,6,7,MIN ; 2,6,7,MAX), (2,4,7,MIN ; 3,4,7,MAX), (2,5,7,MIN ; 3,5,7,MAX), (2,6,7,MIN ; 3,6,7,MAX), range_cond([T1.C3 = 7], [T1.C1 = 2 OR T1.C1 = 3], [T1.C2 IN (4, 5, 6)]) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec) ``` #### 影响版本 OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521)、V3.2.3 BP7 (oceanbase-3.2.3.3-107000092023011911) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819)、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 、V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 、V4.1.0 (oceanbase-4.1.0.0-100001122023040322) 版本。 #### 解决方法 升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本、V3.2.4 BP3 (oceanbase-3.2.4.3-103000032023041816) 版本、V4.1.0 BP1 (oceanbase-4.1.0.0-101000052023050621) 版本。 ```shell select * from t1 where (c1 = 2 or c1 = 3) and (c2 = 4 or c2 = 5 or c2 = 6) and c3 = 7; ```shell select /*+full(t1)*/* from t1 where (c1 = 2 or c1 = 3) and (c2 in (4,5,6) and c3 = 7; ```shell select * from t1 where (c1 = 2 or c1 = 3) and c2 in (4,5,6) is true and c3 = 7; ``` ### 场景 05：SQL 中含有 in 表达式导致正确性问题 #### 问题版本 当 SQL 中含有以下特性时，会出现正确性问题。 1. c1 = x and c2 = y .... and ci {>,>=,<,<=} z and [c_i+1 = m and c_i+2 = n ...] ... and cj in (?,?...) and cj+1 ...。 2. c1 = x and (c2 > 0 and c2 < -1) and c3 in (??)。 #### 注意 在上述两个场景都会出现正确性问题。 示例。 1. 创建表。 ```shell obclient [SYS]> create table t1(c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, primary key(c1,c2,c3,c4,c5)); Query OK, 0 rows affected (0.39 sec) ```shell obclient [SYS]> select * from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and c3 in (3,4,5) and c4 > 0; Empty set (0.005 sec) ```shell obclient [SYS]> explain extended_noaddr select * from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and c3 in (3,4,5) and c4 > 0; ``` 输出结果如下: ```shell +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ----------------------------------- |0 |TABLE SCAN|T1 |1 |48 | =================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1], [T1.C2], [T1.C3], [T1.C4], [T1.C5], [T1.C6]), filter([T1.C3 IN (3, 4, 5)], [T1.C4 > 0]), access([T1.C1], [T1.C2], [T1.C3], [T1.C4], [T1.C5], [T1.C6]), partitions(p0), is_index_back=false, filter_before_indexback[false,false], range_key([T1.C1], [T1.C2], [T1.C3], [T1.C4], [T1.C5]), range(1,1,3,0,MAX ; 1,5,5,NULL,MIN), range_cond([T1.C1 = 1], [T1.C2 >= 1], [T1.C2 <= 5]) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.007 sec) ``` #### 影响版本 OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP7 (oceanbase-3.2.3.3-107000092023011911) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 版本、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 版本。 #### 解决方法 升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP8 (oceanbase-3.2.3.3-108000062023041511) 版本、V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 版本。 ```shell select * from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and (c3 = 3 or c3 = 4 or c3 =5) and c4 > 0; ```shell select /*+full(t1)*/* from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and c3 in (3,4,5) and c4 > 0; ```shell select * from t1 where c1 = 1 and c2 >= 1 and c2 <= 5 and c3 in (3,4,5) is true and c4 > 0; ``` ### 场景 06：SQL 中含有 in 表达式导致正确性问题 #### 问题现象 当 SQL 中含有 `(c1 IN (1,2) or (c1 IS NULL)) and c1 is null` 特征时，会出现正确性问题。 示例。 1. 创建表与索引 ```shell obclient [SYS]> create table t1(c1 int); Query OK, 0 rows affected (0.019 sec) ``` ```shell obclient [SYS]> create index t1_c1_idx on t1(c1); Query OK, 0 rows affected (0.398 sec) ```shell SELECT * FROM T1 WHERE (c1 IN (1,2) or (c1 IS NULL)) and c1 is null\G; Empty set (0.006 sec) ```shell obclient [SYS]> explain extended_noaddr SELECT * FROM T1 WHERE (c1 IN (1,2) or (c1 IS NULL)) and c1 is null\G; ``` 输出结果如下: ```shell *************************** 1. row *************************** Query Plan: ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------------- |0 |TABLE SCAN|T1(T1_C1_IDX)|1 |46 | ============================================ Outputs & filters: ------------------------------------- 0 - output([T1.C1]), filter(nil), access([T1.C1]), partitions(p0), is_index_back=false, range_key([T1.C1], [T1.__pk_increment]), range(NULL,MIN ; NULL,MAX), range_cond([T1.C1 IN (1, 2) OR (T_OP_IS, T1.C1, NULL, 0)], [(T_OP_IS, T1.C1, NULL, 0)]) ``` #### 影响版本 OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP6 Hotfix2 (oceanbase-3.2.3.3-106020042022120915) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 版本。 #### 解决方法 升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP7 (oceanbase-3.2.3.3-107000092023011911) 及之后版本、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 及之后版本。 ```shell SELECT/*+index(t1 t1_c1_idx)*/ * FROM T1 WHERE ((c1 = 1 or c1 = 2) or (c1 IS NULL)) and c1 is null; ```shell SELECT/*+full(t1)*/ * FROM T1 WHERE (c1 IN (1,2) or (c1 IS NULL)) and c1 is null; ```shell SELECT/*+index(t1 t1_c1_idx)*/ * FROM T1 WHERE (c1 IN (1,2) is true or (c1 IS NULL)) and c1 is null; ``` ### 场景 07：SQL 中含有 in 表达式导致正确性问题 当 SQL 中含有 `c1 IN (?,?,?) AND c2 IN (?,?) OR c1 IN (?,?) AND c2 > ?` 特征时，会出现正确性问题。 示例。 1. 创建表。 ```shell obclient [mysql]> CREATE TABLE table10_hash_pk_parts_4 ( col_varchar_20 varchar(20), col_timestamp timestamp NULL DEFAULT NULL, col_bigint bigint, pk int, col_decimal_20_0 decimal(20,0), col_double double); Query OK, 0 rows affected (0.032 sec) ``` 2. 插入数据。 ```shell obclient [mysql]> INSERT INTO table10_hash_pk_parts_4 VALUES ('', NULL, 0, NULL, 34, NULL) , (NULL, '2008-04-21 22:28:30.032585', NULL, -110, 0, 0) , (NULL, '2000-01-13 16:04:43.000020', NULL, 105, 9, -19699) , ('rdetnn', NULL, 2476, 1, 6, 9) , (NULL, '2007-06-21 07:04:43.008773', 24463, -127, -107, 0) , ('p', NULL, NULL, -23078, 2, 15); Query OK, 2 rows affected (0.004 sec) Records: 6 Duplicates: 0 Warnings: 0 ``` 3. 创建索引。 ```shell obclient [mysql]> create index in_expr_idx8 on table10_hash_pk_parts_4 (col_bigint,col_decimal_20_0,col_varchar_20,col_timestamp); Query OK, 0 rows affected (0.446 sec) ```shell obclient [mysql]> SELECT /*+index(table10_hash_pk_parts_4 in_expr_idx8)*/ * FROM table10_hash_pk_parts_4 WHERE col_decimal_20_0 IN (34,3) AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc; ``` 输出结果如下： ```shell +----------------+---------------+------------+------+------------------+------------+ | col_varchar_20 | col_timestamp | col_bigint | pk | col_decimal_20_0 | col_double | +----------------+---------------+------------+------+------------------+------------+ | | NULL | 0 | NULL | 34 | NULL | +----------------+---------------+------------+------+------------------+------------+ 1 row in set (0.008 sec) ```shell obclient [mysql]> explain extended_noaddr SELECT /*+index(table10_hash_pk_parts_4 in_expr_idx8)*/ * FROM table10_hash_pk_parts_4 WHERE col_decimal_20_0 IN (34,3) AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc; ``` 输出结果如下: ```shell +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST| --------------------------------------------------------------------- |0 |SORT | |1 |92 | |1 | TABLE SCAN|table10_hash_pk_parts_4(in_expr_idx8)|1 |92 | ===================================================================== Outputs & filters: ------------------------------------- 0 - output([table10_hash_pk_parts_4.col_varchar_20], [table10_hash_pk_parts_4.col_timestamp], [table10_hash_pk_parts_4.col_bigint], [table10_hash_pk_parts_4.pk], [table10_hash_pk_parts_4.col_decimal_20_0], [table10_hash_pk_parts_4.col_double]), filter(nil), sort_keys([table10_hash_pk_parts_4.pk, DESC]) 1 - output([table10_hash_pk_parts_4.col_decimal_20_0], [table10_hash_pk_parts_4.col_bigint], [table10_hash_pk_parts_4.col_varchar_20], [table10_hash_pk_parts_4.col_timestamp], [table10_hash_pk_parts_4.pk], [table10_hash_pk_parts_4.col_double]), filter([table10_hash_pk_parts_4.col_decimal_20_0 IN (?, ?) AND table10_hash_pk_parts_4.col_bigint = 0 OR (table10_hash_pk_parts_4.col_bigint, table10_hash_pk_parts_4.col_bigint) <= (4, 4)]), access([table10_hash_pk_parts_4.col_decimal_20_0], [table10_hash_pk_parts_4.col_bigint], [table10_hash_pk_parts_4.col_varchar_20], [table10_hash_pk_parts_4.col_timestamp], [table10_hash_pk_parts_4.pk], [table10_hash_pk_parts_4.col_double]), partitions(p0), is_index_back=true, filter_before_indexback[true], range_key([table10_hash_pk_parts_4.col_bigint], [table10_hash_pk_parts_4.col_decimal_20_0], [table10_hash_pk_parts_4.col_varchar_20], [table10_hash_pk_parts_4.col_timestamp], [table10_hash_pk_parts_4.__pk_increment]), range(NULL,MAX,MAX,MAX,MAX ; 4,MAX,MAX,MAX,MAX) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.012 sec) ``` #### 影响版本 OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP6 Hotfix2 (oceanbase-3.2.3.3-106020042022120915) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 版本、V4.1.0 (oceanbase-4.1.0.0-100001122023040322) 版本。 #### 解决方法 升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP7 (oceanbase-3.2.3.3-107000092023011911) 及之后版本、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 及之后版本、V4.1.0 BP1 (oceanbase-4.1.0.0-101000052023050621) 版本。 ```shell SELECT /*+index(table10_hash_pk_parts_4 in_expr_idx8)*/ * FROM table10_hash_pk_parts_4 WHERE (col_decimal_20_0 = 34 or col_decimal_20_0 = 3) AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc; ```shell SELECT /*+full(table10_hash_pk_parts_4)*/ * FROM table10_hash_pk_parts_4 WHERE col_decimal_20_0 IN (34,3) AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc; ```shell SELECT * FROM table10_hash_pk_parts_4 WHERE col_decimal_20_0 IN (34,3) is true AND col_bigint = 0 OR (col_bigint,col_bigint) <= (4,4) order by pk desc; ``` ### 场景 08：SQL 中含有 in 表达式导致稳定性问题，可能引发 500 租户 CommonArray 内存泄漏 #### 问题现象 当 SQL 中含有 `c1 in (??) and c1 in (??)` 特征时，可能引发 500 租户 CommonArray 内存泄漏，堆栈中会包含 `oceanbase::sql::ObKeyPart::merge_two_in_keys`。 示例。 1. 创建表。 ```shell obclient [SYS]> create table t1(C1 int primary key); Query OK, 0 rows affected (0.030 sec) ``` 2. 存在稳定性问题的 SQL。 ```shell obclient [SYS]> select * from t1 where c1 in (1,2,3) and c1 in (2,3,4); Empty set (0.004 sec) ``` 3. 展示存在稳定性问题的 SQL 的执行计划。 ```shell obclient [SYS]> explain extended_noaddr select * from t1 where c1 in (1,2,3) and c1 in (2,3,4); ``` 输出结果如下: ```shell +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================== |ID|OPERATOR |NAME|EST. ROWS|COST| ---------------------------------- |0 |TABLE GET|T1 |2 |92 | ================================== Outputs & filters: ------------------------------------- 0 - output([T1.C1]), filter(nil), access([T1.C1]), partitions(p0), is_index_back=false, range_key([T1.C1]), range[2 ; 2], [3 ; 3], range_cond([T1.C1 IN (1, 2, 3)], [T1.C1 IN (2, 3, 4)]) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.002 sec) ``` #### 问题的风险及影响 稳定性问题。 #### 影响版本 OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 至 V3.2.3 BP7 Hotfix2 (oceanbase-3.2.3.3-107020062023022810) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 至 V3.2.4 BP2 (oceanbase-3.2.4.2-102000042023022717) 版本。 #### 解决方法 升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP7 Hotfix3 (oceanbase-3.2.3.3-107030052023031712) 及之后版本、V3.2.4 BP3 (oceanbase-3.2.4.3-103000032023041816) 版本。 ```shell select * from t1 where (c1 = 1 or c1 = 2 or c1 = 3) and (c1 = 2 or c1 = 3 or c1 = 4); ```shell select * from t1 where c1 in (1,2,3) is true and c1 in (2,3,4) is true; ``` #### 注意 c1 是主键，Hint 不可用。 ### 场景 09：SQL 中含有 in 表达式导致稳定性问题，可能引发 sanity 版本 core #### 问题现象 当 SQL 中含有 `(c1,c2) in ((1,1),(2,2)) and c1 = 2 and c2 = 2` 特征时，可能引发 sanity 版本 core，堆栈中会包含 `ObQueryRange::and_first_in_key` 和 `ObQueryRange::ObSearchState::tailor_final_range`。 示例。 1. 创建表与索引 ```shell obclient [SYS]> create table t1(c1 int, c2 int, c3 int); Query OK, 0 rows affected (0.029 sec) ``` ```shell obclient [SYS]> create index idx on t1(c1,c2); Query OK, 0 rows affected (0.439 sec) ```shell obclient [SYS]> select * from t1 where (c1,c2) in ((1,1),(2,2)) and c1 = 2 and c2 = 2; Empty set (0.007 sec) ```shell obclient [SYS]> explain extended_noaddr select * from t1 where (c1,c2) in ((1,1),(2,2)) and c1 = 2 and c2 = 2; ``` 输出结果如下: ```shell +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ====================================== |ID|OPERATOR |NAME |EST. ROWS|COST| -------------------------------------- |0 |TABLE SCAN|T1(IDX)|10 |502 | ====================================== Outputs & filters: ------------------------------------- 0 - output([t1.c1], [t1.c2], [t1.c3]), filter([(t1.c1, t1.c2) IN ((1, 1), (2, 2))], [t1.c2 = 2]), access([t1.c1], [t1.c2], [t1.c3]), partitions(p0), is_index_back=true, filter_before_indexback[true,true], range_key([t1.c1], [t1.c2], [t1.__pk_increment]), range(2,2,MAX ; 2,2,MIN)always false, --多了个always false range_cond([t1.c1 = 2]) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.006 sec) ``` #### 影响版本 OceanBase 数据库企业版 V3.2.3 BP6 (oceanbase-3.2.3.3-106000102022111521) 版本、V3.2.4 (oceanbase-3.2.4.0-100000072022102819) 版本。 #### 解决方法 升级至问题已修复版本,目前已修复如下版本: OceanBase 数据库 V3.2.3 BP7 Hotfix3 (oceanbase-3.2.3.3-107030052023031712) 及之后版本、V3.2.4 Hotfix2 (oceanbase-3.2.4.0-100020032023032115) 版本、V3.2.4 BP1 (oceanbase-3.2.4.1-101000052023010822) 及之后版本。 ```shell select * from t1 where ((c1 = 1 and c2 = 1) or (c1 = 2 and c2 = 2)) and c1 = 2 and c2 = 2; ```shell select /*+full(t1)*/* from t1 where (c1,c2) in ((1,1),(2,2)) and c1 = 2 and c2 = 2; ```shell select * from t1 where (c1,c2) in ((1,1),(2,2)) is true and c1 = 2 and c2 = 2; ``` 上一篇 [弱读查询失败，报 cluster weak read service is disabled](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000209991) 下一篇 [in 优化 query range 抽取有问题导致查询结果不一致](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000043615) ![有帮助](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) 咨询热线