sql查询不命中索引

OceanBase
,
#1

【 使用环境 】生产环境
【 OB or 其他组件 】ob4.2.1
【 使用版本 】
【问题描述】
SELECT COUNT(1)
from bz_objstatus BO
inner join family_info bf on bf.id = BO.familyId and bf.familystate = 1
inner join family_member br on br.familyId = bf.id and br.relationShip = ‘0’ and br.personnelStatus = 1
where BO.status = 1
and BO.policy_id = 200 ;

执行计划.txt (8.1 KB)
上述sql中BO.status = 1和BO.policy_id = 200一起使用就很慢，单独都很快。
查看执行计划，发现bz_objstatus怎么创建索引都不命中，创建了policy_id、status、policy_id_status联合索引都试过了

#3

可以用hint指定
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000644124

#4

用了，直接使用的index(BO policy_id)，更慢了

#5

建表语句可以提供下吗

#6

CREATE TABLE bz_objstatus (
ss_id bigint NOT NULL AUTO_INCREMENT,
bTime varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘时间’,
familyId bigint DEFAULT NULL COMMENT ‘家庭表主键id’,
status varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘1 正常 2 异常’,
updatetime datetime DEFAULT NULL,
policy_id bigint DEFAULT NULL,
PRIMARY KEY (ss_id) USING BTREE,
KEY bz_objstatus_policy_id_IDX (policy_id) USING BTREE,
KEY bz_objstatus_statusp_IDX (status,policy_id) USING BTREE
)

#7

  • 你发的执行计划里有远程执行计划，建议先将情形弄简单点。将三个表的 primary_zone 都设置同一个 zone。然后再发一次执行计划。

  • 发一下数据特征。

select count(*), count(distinct policy_id) from bz_objstatus;
select status, count(*) from bz_objstatus group by status;

index 调整为 (policy_id, status)

再发一次执行计划。

#8

primary_zone都设置同一个 zone，是指优先级配置呈阶梯吗？

#9

inner join的相关表的建表语句可以发一下吗？

#10 
alter table t1 primary_zone='zone1';
alter table t2 primary_zone='zone1';
alter table t3 primary_zone='zone1';
#11

好的，我们有ocp，修改租户的可以吗？
还是像你写的修改表？

#12

CREATE TABLE family_info (
id bigint NOT NULL AUTO_INCREMENT,
familyState int DEFAULT NULL COMMENT '1 正常 ',
oneLevel varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
protectionCount int DEFAULT ‘0’ COMMENT ‘人数’,
protectionMoney double DEFAULT NULL COMMENT ‘金额’,
apply_type int DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
KEY family_info_familyNo_IDX (familyNo) USING BTREE,
)

CREATE TABLE family_member (
id bigint NOT NULL AUTO_INCREMENT,
familyId bigint DEFAULT NULL COMMENT ‘主键id’,
personnelStatus varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘人员状态’,
relationShip varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘家庭关系’,
remark varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT ‘remark’,
PRIMARY KEY (id) USING BTREE,
KEY family_member_familyId_IDX (familyId) USING BTREE,
)

#13

也可以。改后 等1分钟。

#14

select count(*), count(distinct policy_id) from bz_objstatus;
count( * ) count(distinct policy_id)
2190697 53

select status, count( ) from bz_objstatus group by status;
status count()
1 1382855
0 807842

调整primary_zone的优先级后，sql查询快了，变成8s了。

index 调整为 (policy_id, status) ,下面是执行计划
执行计划.txt (6.0 KB)

#15

貌似隐式转换了，搞成 status = ‘1’ 试试

#16

在primary_zone的优先级配置的情况下，8s变成3s了。
primary_zone同一等级的情况下，手动指定索引+status = ‘1’，3s
不手动指定索引，几百秒没查出来

#17

/+INDEX(BO policy_id,status)/
这个写法不对， 改成 /*+ index(BO 索引名) */

不过 policy_Id 的 NDV 不多，走索引也不一定好。实际对比看看。

#18

image
image1138×524 53.3 KB

写法没问题，不知道为什么不显示星号，索引名和列名是一样的。
/* +index(BO policy_id,status) */

#19

老师们，有什么不改变primary_zone，不手动指定索引的方式吗？

#20

OB INDEX HINT 没有你说的这种用法 。你用标准的 试试 /*+ index(BO 索引名) */ 。

前面建议先将表的 PRIMARY_ZONE 统一到一个地方，是为了将问题复杂度降低，以便能专门分析不走索引的原因。

建议用 INDEX HINT 是为了对比用索引和不用索引的执行计划差异。 走索引不一定就对。这个需要分析。

#21

/* +index(BO policy_id,status) */ 我已经试过，现在用怎么做？看执行计划？