【 使用环境 】测试环境
【 OB or 其他组件 】
【 使用版本 】4.3.1
【问题描述】
1、我们尝试将MySQL业务测试环境切换为Oceanbase，在进行手动统计分析的情况下，4.3 & 4.2版本都出现如下问题：
SQL：
MySQL执行计划正常走索引。
但是OB执行计划：
最后，OB没跑出结果，而MySQL是在几百毫秒级别。
为啥这种情况OB无法使用索引呢？
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：
【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！
您提到的问题是特定 SQL 在执行时未使用预期的索引，从而导致性能异常。根据您的描述，这可能是由于 SQL 优化器选择了次优的执行计划，或者是由于 Plan Cache 中的执行计划不再适应当前的数据分布情况所引起的。
为了进一步诊断这个问题，我们建议您使用 obdiag 工具来收集相关的诊断信息。
日志采集场景: SQL性能问题
日志采集命令: obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”
请注意替换上述命令中的 db_connect 参数为您实际的数据库连接信息，并确保 trace_id 是您想要诊断的 SQL 的跟踪 ID。如果您不确定如何获取 trace_id，可以通过开启 SQL 语句的追踪功能来获得。
请您执行上述命令并将生成的日志压缩包发送给我们，以便我们能够更准确地定位问题所在。
为了更好地帮助您解决问题，请您提供以下信息：
通过这些问题的答案，我们将能够更全面地了解您的问题背景，并为您提供更加具体的优化建议。
具体信息可参考以下文档:
附上敏捷诊断工具 obdiag 使用帮助链接
这个问题好像是用了“regexp”，也不完全确定，
原SQL不复杂，无非就是关联。
select
if(CHAR_LENGTH(mx.name_) - CHAR_LENGTH(replace(mx.name_, ‘’, ‘’)) >= 2 ,
SUBSTRING_INDEX(SUBSTRING_INDEX(mx.name_, '’, 2), ‘’, -1),
mx.name_) ssflJc
from
ftsp_fp_jx jx
left join ftsp_fp_jx_mx mx on
mx.fp_jx_id_ = jx.id_
left join ftsp_zt_kjkm_440000 mk on
mk.zt_ztxx_id_ = jx.zt_ztxx_id_
and mk.km_nbbm_ = ‘50020’
left join ftsp_zt_fylx fy on
mx.zt_fylx_id_ = fy.id_
left join ftsp_zt_kjkm_440000 km on
fy.zt_kjkm_id_ = km.id_
and km.km_dm_ like concat(mk.km_dm_, ‘%’)
left join ftsp_zt_chxx ch on
ch.id_ = mx.zt_chxx_id_
left join ftsp_zt_cplx cp on
cp.id_ = ch.zt_cplx_id_
where
jx.zt_ztxx_id_ =‘0604F70FDCEC4D00BB373C7C3807D2F1’
and mx.name_ regexp '\[\S]+?\.’
and (jx.sm_status_ not in (1, 2, 3, 4, 6, 8, 10, 11)
or jx.fp_status_ not in (4, 6, 8))
and jx.fp_date_ < DATE_FORMAT(concat(‘202409’, ‘01’), ‘%Y-%m-%d’)
and (km.id_ is not null
or cp.id_ is not null)
group by
replace(mx.name_,
REGEXP_REPLACE(mx.name_, ‘\[\S]+?\’, ‘’),
‘’)
order by
jx.fp_date_ desc
limit 5;
感谢您的补充信息。根据您的描述，问题可能与正则表达式的使用有关。在 OceanBase 中，正则表达式相关的函数包括 REGEXP, RLIKE, REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE 等。这些函数在某些情况下可能会影响 SQL 的执行效率，特别是当涉及到复杂的正则表达式模式匹配时。
根据检索到的文档片段，我们可以看到：
regexp_time_limit 用于正则表达式匹配操作的时间限制，默认单位为毫秒。从 V4.1.0 版本开始引入。 [1]
regexp_stack_limit 用于正则表达式匹配操作的内部堆栈的最大可用内存，默认单位为字节。从 V4.1.0 版本开始引入。 [7]
这两个参数可能会影响正则表达式匹配的性能。如果您的 SQL 中使用了复杂的正则表达式，可能会导致性能下降。
此外，根据您的描述，MySQL 执行计划正常走索引，而 OceanBase 的执行计划没有使用索引，这可能是由于正则表达式的影响。在某些情况下，正则表达式可能导致优化器选择不同的执行计划。
建议您检查 SQL 中是否使用了正则表达式，并尝试以下方法来优化性能：
regexp_time_limit 和 regexp_stack_limit 参数：根据实际情况调整这两个参数的值，以优化正则表达式的性能。希望这些建议能帮助您解决问题。如果仍有疑问，请提供更多详细信息，以便我们进一步分析。
详情请参考:
(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)
原SQL：
select
if(CHAR_LENGTH(mx.name_) - CHAR_LENGTH(replace(mx.name_, '*', '')) >= 2 ,
SUBSTRING_INDEX(SUBSTRING_INDEX(mx.name_, '*', 2), '*', -1),
mx.name_) ssflJc
from
ftsp_fp_jx jx
left join ftsp_fp_jx_mx mx on
mx.fp_jx_id_ = jx.id_
left join ftsp_zt_kjkm_440000 mk on
mk.zt_ztxx_id_ = jx.zt_ztxx_id_
and mk.km_nbbm_ = '50020'
left join ftsp_zt_fylx fy on
mx.zt_fylx_id_ = fy.id_
left join ftsp_zt_kjkm_440000 km on
fy.zt_kjkm_id_ = km.id_
and km.km_dm_ like concat(mk.km_dm_, '%')
left join ftsp_zt_chxx ch on
ch.id_ = mx.zt_chxx_id_
left join ftsp_zt_cplx cp on
cp.id_ = ch.zt_cplx_id_
where
jx.zt_ztxx_id_ ='0604F70FDCEC4D00BB373C7C3807D2F1'
and mx.name_ regexp '\\*[\\S]+?\\*.*'
and (jx.sm_status_ not in (1, 2, 3, 4, 6, 8, 10, 11)
or jx.fp_status_ not in (4, 6, 8))
and jx.fp_date_ < DATE_FORMAT(concat('202409', '01'), '%Y-%m-%d')
and (km.id_ is not null
or cp.id_ is not null)
group by
replace(mx.name_,
REGEXP_REPLACE(mx.name_, '\\*[\\S]+?\\*', ''),
'')
order by
jx.fp_date_ desc
limit 5;
执行计划：
=========================================================================================================
|ID|OPERATOR |NAME |EST.ROWS |EST.TIME(us)|
---------------------------------------------------------------------------------------------------------
|0 |TOP-N SORT | |5 |268358106 |
|1 |└─HASH GROUP BY | |4260 |268358010 |
|2 | └─HASH RIGHT OUTER JOIN | |91967252 |260658471 |
|3 | ├─TABLE FULL SCAN |km |24712821 |372348 |
|4 | └─HASH RIGHT OUTER JOIN | |91967252 |221494207 |
|5 | ├─TABLE FULL SCAN |cp |14951444 |174630 |
|6 | └─HASH RIGHT OUTER JOIN | |91967252 |185614343 |
|7 | ├─TABLE FULL SCAN |ch |68342886 |1415543 |
|8 | └─HASH RIGHT OUTER JOIN | |91967252 |121031896 |
|9 | ├─TABLE FULL SCAN |fy |51861168 |1074168 |
|10| └─HASH JOIN | |91967252 |68436451 |
|11| ├─NESTED-LOOP OUTER JOIN CARTESIAN | |2011 |9418 |
|12| │ ├─TABLE RANGE SCAN |jx(uk_ftsp_fp_jx_3) |2011 |9362 |
|13| │ └─MATERIAL | |1 |9 |
|14| │ └─TABLE RANGE SCAN |mk(idx_ftsp_zt_kjkm_440000_2)|1 |9 |
|15| └─TABLE FULL SCAN |mx |185082296|14223401 |
=========================================================================================================
Outputs & filters:
-------------------------------------
0 - output([CASE WHEN CHAR_LENGTH(mx.name_) - CHAR_LENGTH(replace(mx.name_, '*', '')) >= 2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(mx.name_, '*', 2), '*',
-1) ELSE mx.name_ END]), filter(nil), rowset=8
sort_keys([jx.fp_date_, DESC]), topn(5)
1 - output([jx.fp_date_], [mx.name_]), filter(nil), rowset=8
group([replace(mx.name_, cast(REGEXP_REPLACE(cast(mx.name_, VARCHAR(1048576)), cast('\\*[\\S]+?\\*', VARCHAR(1048576)), cast('', VARCHAR(1048576))),
VARCHAR(1048576)), '')]), agg_func(nil)
2 - output([jx.fp_date_], [mx.name_]), filter([km.id_ IS NOT NULL OR cp.id_ IS NOT NULL]), rowset=8
equal_conds([fy.zt_kjkm_id_ = km.id_]), other_conds([(T_OP_LIKE, km.km_dm_, concat(mk.km_dm_, '%'), '\\')])
3 - output([km.id_], [km.km_dm_]), filter(nil), rowset=8
access([km.id_], [km.km_dm_]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([km.id_]), range(MIN ; MAX)always true
4 - output([jx.fp_date_], [mx.name_], [cp.id_], [fy.zt_kjkm_id_], [mk.km_dm_]), filter(nil), rowset=8
equal_conds([cp.id_ = ch.zt_cplx_id_]), other_conds(nil)
5 - output([cp.id_]), filter(nil), rowset=8
access([cp.id_]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([cp.id_]), range(MIN ; MAX)always true
6 - output([jx.fp_date_], [mx.name_], [ch.zt_cplx_id_], [fy.zt_kjkm_id_], [mk.km_dm_]), filter(nil), rowset=8
equal_conds([ch.id_ = mx.zt_chxx_id_]), other_conds(nil)
7 - output([ch.id_], [ch.zt_cplx_id_]), filter(nil), rowset=8
access([ch.id_], [ch.zt_cplx_id_]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([ch.id_]), range(MIN ; MAX)always true
8 - output([jx.fp_date_], [mx.name_], [fy.zt_kjkm_id_], [mk.km_dm_], [mx.zt_chxx_id_]), filter(nil), rowset=8
equal_conds([mx.zt_fylx_id_ = fy.id_]), other_conds(nil)
9 - output([fy.id_], [fy.zt_kjkm_id_]), filter(nil), rowset=8
access([fy.id_], [fy.zt_kjkm_id_]), partitions(p0)
is_index_back=false, is_global_index=false,
range_key([fy.id_]), range(MIN ; MAX)always true
10 - output([jx.fp_date_], [mx.name_], [mk.km_dm_], [mx.zt_fylx_id_], [mx.zt_chxx_id_]), filter(nil), rowset=8
equal_conds([mx.fp_jx_id_ = jx.id_]), other_conds(nil)
11 - output([jx.fp_date_], [jx.id_], [mk.km_dm_]), filter(nil), rowset=8
conds(nil), nl_params_(nil), use_batch=false
12 - output([jx.id_], [jx.fp_date_]), filter([jx.fp_date_ < INTERNAL_FUNCTION(DATE_FORMAT(concat('202409', '01'), '%Y-%m-%d'), 113, 17)], [(T_OP_NOT_IN,
jx.sm_status_, (1, 2, 3, 4, 6, 8, 10, 11)) OR (T_OP_NOT_IN, jx.fp_status_, (4, 6, 8))]), rowset=8
access([jx.id_], [jx.sm_status_], [jx.fp_status_], [jx.fp_date_]), partitions(p0)
is_index_back=true, is_global_index=false, filter_before_indexback[false,false],
range_key([jx.zt_ztxx_id_], [jx.fp_dm_], [jx.fp_hm_], [jx.shadow_pk_0]), range(0604F70FDCEC4D00BB373C7C3807D2F1,MIN,MIN,MIN ; 0604F70FDCEC4D00BB373C7C3807D2F1,
MAX,MAX,MAX),
range_cond([jx.zt_ztxx_id_ = '0604F70FDCEC4D00BB373C7C3807D2F1'])
13 - output([mk.km_dm_]), filter(nil), rowset=8
14 - output([mk.km_dm_]), filter(nil), rowset=8
access([mk.id_], [mk.km_dm_]), partitions(p0)
is_index_back=true, is_global_index=false,
range_key([mk.zt_ztxx_id_], [mk.km_nbbm_], [mk.id_]), range(0604F70FDCEC4D00BB373C7C3807D2F1,50020,MIN ; 0604F70FDCEC4D00BB373C7C3807D2F1,50020,MAX),
range_cond([cast(mk.km_nbbm_, DECIMAL(11, 0)) = cast('50020', DECIMAL(1, -1))], [mk.zt_ztxx_id_ = '0604F70FDCEC4D00BB373C7C3807D2F1'])
15 - output([mx.fp_jx_id_], [mx.zt_fylx_id_], [mx.zt_chxx_id_], [mx.name_]), filter([(T_OP_REGEXP, cast(mx.name_, VARCHAR(1048576)), cast('\\*[\\S]+?\\*.*',
VARCHAR(1048576)))]), rowset=8
access([mx.fp_jx_id_], [mx.zt_fylx_id_], [mx.zt_chxx_id_], [mx.name_]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([mx.id_]), range(MIN ; MAX)always true
参考obdiag部署一个obdiag采集收集下该sql
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001429131
SQL性能问题, 此处env中的trace_id对应gv$ob_sql_audit的trace_id
obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
我这边已经处理，发现是 and mx.name_ regexp ‘\[\S]+?\.*’ 这个条件引起的。
将这个条件剔除了执行计划就正常了。