Oceanbase优化器导致SQL执行过于缓慢

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】4.2.2
【问题描述】目前我们在测试环境使用oceanbase，但是，总是会有一两个SQL导致整个数据库很缓慢，比如这个SQL
explain
select
phone.id_ id,
phone.lxr_id_ contactId,
phone.lxr_name_ contactName,
phone.qzkh_id_ qzkhId,
phone.type_ type,
phone.number_ number,
phone.status_ status,
phone.is_default_ isDefault,
phone.is_competitor_ isCompetitor,
phone.create_user_ createUser,
phone.create_date_ createDate,
phone.update_user_ updateUser,
phone.update_date_ updateDate
from ftsp_kh_qzkh_lxr lxr
join ftsp_kh_qzkh_lxr_phone phone on phone.lxr_id_ = lxr.id_
where lxr.is_default_ = 1 and phone.is_default_ = 1
and lxr.qzkh_id_ in
(
‘h0000000000000781756701446537216’
);

这是一个普通得不能再普通的两表关联查询，但是，线上执行时间为38s。
处理方法也很简单：
直接用 hint让两个表走nested loops joins，可以保证该SQL在40毫秒内执行成功。
select /*+ use_nl(lxr phone) */
phone.id_ id,
phone.lxr_id_ contactId,
phone.lxr_name_ contactName,

执行结果如下：

image
image2188×95 73.7 KB

但是，请问有没什么方法，尽可能地不用hint去改造就能保住OB能走最高效的执行计划。
还是说，只能手动去调整SQL？

#3

看看explain的输出结果

#4

未加 use_nl，走hash join，耗时 > 30s

image
image1337×271 60.7 KB

使用use_nl，走nl join，耗时 0.047s

image
image1341×261 67.1 KB

#5

EST.TIIME(us) 感觉有点小问题~
1、nl join，
算子1：17378，算子2：124971，
hash join
算子1：17379，算子2：85146097，
算子2差异这么大，最后的结果却是 hash join的est.time更小。。。

2、从实际执行情况上，也确实是走 nl join 快 hash join很多很多。

image
image2186×182 105 KB

因为，ob的存储压缩性能很好，我们也希望能用上ob在生产环境~~~但是目前感觉，有不少SQL比 原来的MySQL慢了，感觉并不是ob的性能不行，而是，优化器在执行路径上的处理有点点小问题。 :pleading_face:

我们觉得这么简单的SQL都需要手动指定表连接的方式，实在有点不合理。

#6

是不符合预期的，老师麻烦提供下2张表结构和数据量，我找相关同学确认下。

也可以试试重新收集统计信息后再查询试试。
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000643215#7-title-手动统计信息收集的实践场景

#7

感谢感谢，其实我们也很想尝试着把Oceanbase用起来，毕竟这么优秀的存储性能，但是目前测试下来问题确实有一些些。

统计信息尝试过，但并没有效果~

相关表结构及count：
ftsp_kh_qzkh_lxr总量：101514443；

CREATE TABLE ftsp_kh_qzkh_lxr (
id_ char(32) NOT NULL COMMENT ‘无业务意义主键’,
qzkh_id_ char(32) NOT NULL COMMENT ‘’,
name_ varchar(100) NOT NULL COMMENT ‘’,
gender_ tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
wechat_id_ varchar(50) DEFAULT NULL COMMENT ‘’,
email_ varchar(50) DEFAULT NULL COMMENT ‘’,
other_contact_ varchar(100) DEFAULT NULL COMMENT ‘’,
position_ char(6) DEFAULT NULL COMMENT ‘’,
wechat_status_ char(6) DEFAULT NULL COMMENT ‘’,
is_default_ tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
create_user_ char(32) DEFAULT NULL COMMENT ‘’,
create_date_ timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT ‘’,
update_user_ char(32) DEFAULT NULL COMMENT ‘’,
update_date_ timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT ‘’,
is_fr_ tinyint(4) DEFAULT ‘0’ COMMENT ‘’,
cy_lxr_ tinyint(4) DEFAULT NULL COMMENT ‘’,
PRIMARY KEY (id_),
KEY idx_ftsp_kh_qzkh_lxr_1 (qzkh_id_),
KEY idx_ftsp_kh_qzkh_lxr_2 (position_),
KEY idx_ftsp_kh_qzkh_lxr_3 (name_(10)),
KEY idx_ftsp_kh_qzkh_lxr_4 (wechat_status_, update_user_)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMMENT = ‘’

ftsp_kh_qzkh_lxr_phone总量：215939713；

CREATE TABLE ftsp_kh_qzkh_lxr_phone (
id_ char(32) NOT NULL COMMENT ‘无业务意义主键’,
lxr_id_ char(32) NOT NULL COMMENT ‘’,
lxr_name_ varchar(100) DEFAULT NULL COMMENT ‘’,
qzkh_id_ char(32) DEFAULT NULL COMMENT ‘’,
type_ tinyint(4) NOT NULL COMMENT ‘’,
number_ varchar(600) DEFAULT NULL COMMENT ‘’,
status_ char(6) DEFAULT NULL COMMENT ‘’,
is_default_ tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘’,
is_competitor_ tinyint(4) DEFAULT ‘0’ COMMENT ‘’,
same_number_count_ int(11) DEFAULT ‘0’ COMMENT ‘’,
create_user_ char(32) DEFAULT NULL COMMENT ‘’,
create_date_ timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT ‘’,
update_user_ char(32) DEFAULT NULL COMMENT ‘’,
update_date_ timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT ‘’,
PRIMARY KEY (id_),
KEY idx_ftsp_kh_qzkh_lxr_phone_1 (lxr_id_),
KEY idx_ftsp_kh_qzkh_lxr_phone_2 (status_),
KEY idx_ftsp_kh_qzkh_lxr_phone_3 (qzkh_id_, lxr_id_),
KEY idx_ftsp_kh_qzkh_lxr_phone_4 (number_(20))
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMMENT = ‘’

个人感觉是不是由于两个表的数据量都太大，然后走了hash join~~~ :pleading_face:
而且这个SQL真的就是一个很常见的两表内连接SQL，条件过滤后小表驱动大表的场景~

#8

问题是优化器没选择正确的执行计划，内部需要先确认下。有消息会帖子中答复您，请稍等。

#9

麻烦提供2个信息：

  1. explain extended sql语句 计划提供下。
  2. trace信息
1）业务租户设置：
 SET ob_enable_show_trace='ON';
2）执行不带hint的 sql 语句后
3）获取trace_id
select last_trace_id();
4）查询SVR_IP
select * from gv$ob_audit_sql where trace_id='$trace_id'
5）去对应的SVR_IP节点过滤日志
grep "$trace_id" observer.log
#10

是不是primary_zone设置的是random啊。

#11

该问题已确认，导入数据期间进行的查询，此时表未触发合并或自动收集统计信息，导致选择的执行计划不符合实际数据量。合并后查询计划正常走merge，性能和计划符合预期。

#12

可以尝试使用ob的outline进行绑定执行计划