【 使用环境 】 测试环境
【 OB or 其他组件 】ob
【 使用版本 】3.1
【问题描述】
oceanbase 1-1-1 租户 资源为 12c 40g 表大小600w查询count 需要2.6s，然后mysql 单机同样的表需要1.1s
ob 查询如下
root@xxx 15:30:57>select count() from cn_xxxx_occcm;
±---------+
| count() |
±---------+
| 6369107 |
±---------+
1 row in set (2.70 sec)
ob 环境表结构如下
CREATE TABLE cn_xxxxxx_xxxx· ( idchar(32) NOT NULL, tenant_idchar(32) DEFAULT NULL, personnel_idchar(32) DEFAULT NULL, room_idchar(32) NOT NULL, community_idchar(32) DEFAULT NULL, pathvarchar(255) DEFAULT NULL, check_in_datedatetime DEFAULT NULL, check_out_datedatetime DEFAULT NULL, personnel_typetinyint(4) DEFAULT NULL, creation_timetimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, roomOrdertinyint(4) DEFAULT '0' COMMENT '', permanent_typetinyint(1) DEFAULT '0' COMMENT ', stage_numtinyint(1) NOT NULL COMMENT '', build_numint(8) NOT NULL COMMENT '', unit_numint(8) NOT NULL COMMENT '', room_numint(8) NOT NULL COMMENT '', dad_relation_idvarchar(64) DEFAULT NULL COMMENT ', ban_accesstinyint(4) DEFAULT NULL COMMENT '', ban_id char(32) DEFAULT NULL COMMENT ', PRIMARY KEY (id), UNIQUE KEY personnel_room_key (personnel_id, room_id) BLOCK_SIZE 16384 LOCAL, KEY idx_room_id (room_id) BLOCK_SIZE 16384 LOCAL, KEY idx_community_id (community_id) BLOCK_SIZE 16384 LOCAL, KEY idx1 (community_id, personnel_id`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0；
ob 环境执行计划
root@neptune 15:51:47>explain select count(*) from cn_xxxx_xxx;
| Query Plan
|ID|OPERATOR |NAME |EST. ROWS|COST |
|0 |SCALAR GROUP BY| |1 |2706685|
|1 | TABLE SCAN |cn_xxxl_xxxx(idx_room_id)|6369107 |2463601|
Outputs & filters:
0 - output([T_FUN_COUNT()]), filter(nil),
group(nil), agg_func([T_FUN_COUNT()])
1 - output([1]), filter(nil),
access([cn_personnel_room.room_id]), partitions(p0)
|
MySQL单机查询结果如下
root@xxxxx 15:31:32>select count() from cn_xxxx_xxxx;
±---------+
| count() |
±---------+
| 6378138 |
±---------+
1 row in set (1.22 sec)
mysql 执行计划如下
root@xxxx 15:50:36>explain select count(*) from cn_xxx_xxxx;
±—±------------±------------------±-----------±------±--------------±------------±--------±-----±--------±---------±------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------------------±-----------±------±--------------±------------±--------±-----±--------±---------±------------+
| 1 | SIMPLE | cn_xxxx_xxxx | NULL | index | NULL | idx_room_id | 96 | NULL | 7231696 | 100.00 | Using index |
±—±------------±------------------±-----------±------±--------------±------------±--------±-----±--------±---------±------------+
1 row in set, 1 warning (0.00 sec)
mysql 表结构如下
CREATE TABLE cn_xxxxx_xxxx (
id char(32) NOT NULL,
tenant_id char(32) DEFAULT NULL,
personnel_id char(32) DEFAULT NULL,
room_id char(32) NOT NULL,
community_id char(32) DEFAULT NULL,
path varchar(255) DEFAULT NULL,
check_in_date datetime DEFAULT NULL,
check_out_date datetime DEFAULT NULL,
personnel_type tinyint(4) DEFAULT NULL,
creation_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
roomOrder tinyint(4) DEFAULT ‘0’ COMMENT ',
permanent_type tinyint(1) DEFAULT ‘0’ COMMENT ‘’,
stage_num tinyint(1) NOT NULL COMMENT ‘’,
build_num int(8) NOT NULL COMMENT ‘’,
unit_num int(8) NOT NULL COMMENT ‘’,
room_num int(8) NOT NULL COMMENT ‘’,
dad_relation_id varchar(64) DEFAULT NULL COMMENT ',
ban_access tinyint(4) DEFAULT NULL COMMENT ‘’,
ban_id char(32) DEFAULT NULL COMMENT ‘’,
PRIMARY KEY (id) USING BTREE,
UNIQUE KEY personnel_room_key (personnel_id,room_id),
KEY idx_room_id (room_id),
KEY idx_community_id (community_id),
KEY idx1 (community_id,personnel_id),
KEY idxid (id)
)
【复现路径】问题出现前后相关操作
【问题现象及影响】
问题是 ob跑不过 mysql是否正常现象？就算ob里加一个id的索引然count走id所以也是2.6s多！
【附件】