在 SQL 查询中，包含多个 OR 条件的场景非常常见，尤其在需要匹配多个离散值时（如模糊搜索、多分类筛选）。OceanBase 当前存在对包含大 OR SQL 硬解析慢的问题，目前可以将大 OR SQL 改写成大 IN 的形式进行优化。 本文通过实际案例 SQL 对比验证大 OR 硬解析耗时高，以及如果将改写成 SQL。
详细说明
原始 SQL 中包含多个条件，并用 OR 连接，注意到执行 SQL 的硬解析耗时 8878us。
obclient [new]> select execute_time,get_plan_time, query_sql from oceanbase.gv$ob_sql_audit where trace_id='YA6240BEE9193-00063603FD7C99D6-0-0';
+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| execute_time | get_plan_time | query_sql |
+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 210 | 8878 | select *
from t1
where (
c1 = 1
and c2 = 2
)
or (
c1 = 4
and c2 = 5
)
or (
c1 = 7
and c2 = 8
)
or (
c1 = 10
and c2 = 11
)
or (
c1 = 11
and c2 = 21
)
or (
c1 = 41
and c2 = 51
)
or (
c1 = 7
and c2 = 81
)
or (
c1 = 101
and c2 = 111
)
or (
c1 = 42
and c2 = 52
)
or (
c1 = 72
and c2 = 82
)
or (
c1 = 102
and c2 = 112
)
or (
c1 = 112
and c2 = 212
)
or (
c1 = 412
and c2 = 512
)
or (
c1 = 72
and c2 = 812
)
or (
c1 = 1012
and c2 = 1112
)
and c3 = 7 |
+--------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.206 sec)
SQL 形如 (c1=xx and c2=yy) or (c1=ww and c2=vv) or (c1=aa and c2=bb) ...，对于这样的 SQL，可以改写成 (c1,c2) in ((xx,yy),(ww,vv),(aa,bb)) ... 改写成 IN 之后，会大大降低 SQL 硬解析的时间， 此外将 OR 转成 IN 之后，还会生成诸如 IN 转 VALUES 的大 IN SQL优化，提高执行效率。
对于上面的 SQL 可以进行如下的转换，转换后硬解析时间降低 2609us。
obclient [new]> select execute_time,get_plan_time, query_sql from oceanbase.gv$ob_sql_audit where trace_id='YA6240BEE9193-00063603FD7C99D9-0-0';
+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| execute_time | get_plan_time | query_sql |
+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 285 | 2609 | select * from t1 where (c1,c2) in((1,2),(4,5),(7,8),(10,11),(11,21),(41,51),(71,81),(101,111),(42,52),(72,82),(102,112),(112,212),(412,52),(712,812),(1012,1112)) and c3=7 |
+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.203 sec)
适用版本
OceanBase 数据库 V4.x 版本。
