【 使用环境 】生产环境
【 OB or 其他组件 】
【 使用版本 】5.7.25-OceanBase-v4.2.1.3
【问题描述】OB建立了一个字段名last_update_time 类型是 datetime on update current_timestamp。索引 idx_crm_member_points_1 类型Normal 字段last_update_time
【复现路径】使用 where 字段 between STR_TO_DATE(‘20250310 00:00:00’, ‘%Y%m%d %H:%i:%s’)
and STR_TO_DATE(‘20250310 23:59:59’, ‘%Y%m%d %H:%i:%s’)
和 where 字段 >=‘2025-03-10 00:00:00’
and 字段<=‘2025-03-10 23:59:59’
看执行计划都没有走索引
where 字段 >=‘20250310 00:00:00’
and 字段<=‘20250310 23:59:59’
看执行计划走索引了，但是没有数据。因为不符合时间格式
应该怎么样才能不用强制指定索引名称，时间能走索引。
备注：有些表是可以自动走索引的，但是部分表不能。
下面提供的是不能走的。
1、建表
CREATE TABLE crm (
id bigint(20) NOT NULL AUTO_INCREMENT ,
create_by varchar(30) DEFAULT NULL ,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
update_by varchar(30) DEFAULT NULL ,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
last_update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
m bigint(20) NOT NULL DEFAULT ‘0’,
s varchar(15) NOT NULL DEFAULT ‘’,
t varchar(10) NOT NULL ,
st varchar(15) DEFAULT NULL ,
c varchar(30) NOT NULL ,
sta varchar(10) NOT NULL ,
be datetime DEFAULT NULL,
en datetime DEFAULT NULL ,
PRIMARY KEY (id),
KEY idx_crm_1 (last_update_time) BLOCK_SIZE 16384 LOCAL,
KEY idx_crm_2 (s) BLOCK_SIZE 16384 LOCAL,
KEY idx_crm_3 (create_by) BLOCK_SIZE 16384 LOCAL,
KEY idx_crm_4 (m) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 1901843488069148675 AUTO_INCREMENT_MODE = ‘ORDER’ 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
2、
explain extended
select *
from crm
where last_update_time between STR_TO_DATE(‘2025-03-17 00:00:00’, ‘%Y-%m-%d %H:%i:%s’)
and STR_TO_DATE(‘2025-03-18 23:59:59’, ‘%Y-%m-%d %H:%i:%s’)
执行计划
==========================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
|0 |TABLE FULL SCAN|crm|6205069 |8691094 |
==========================================================
Outputs & filters:
0 - output([crm.id(0x7f3e69e245a0)], [crm.create_by(0x7f3e69e24880)], [crm.create_time(0x7f3e69e24b60)], [crm.update_by(0x7f3e69e24e40)],
[crm.update_time(0x7f3e69e25120)], [crm.last_update_time(0x7f3e69e23910)], [crm.m(0x7f3e69e25400)], [crm.s(0x7f3e69e256e0)],
[crm.t(0x7f3e69e259c0)], [crm.st(0x7f3e69e25ca0)], [crm.c(0x7f3e69e25f80)], [crm.sta(0x7f3e69e26260)],
[crm.be(0x7f3e69e26540)], [crm.en(0x7f3e69e26820)]), filter([crm.last_update_time(0x7f3e69e23910) >=
STR_TO_DATE('2025-03-17 00:00:00', '%Y-%m-%d %H:%i:%s')(0x7f3e69e20440)(0x7f3e69e222c0)], [crm.last_update_time(0x7f3e69e23910) <= STR_TO_DATE('2025-03-18
23:59:59', '%Y-%m-%d %H:%i:%s')(0x7f3e69e211c0)(0x7f3e69e229c0)]), rowset=256
access([crm.id(0x7f3e69e245a0)], [crm.last_update_time(0x7f3e69e23910)], [crm.create_by(0x7f3e69e24880)], [crm.create_time(0x7f3e69e24b60)],
[crm.update_by(0x7f3e69e24e40)], [crm.update_time(0x7f3e69e25120)], [crm.m(0x7f3e69e25400)], [crm.s(0x7f3e69e256e0)],
[crm.t(0x7f3e69e259c0)], [crm.st(0x7f3e69e25ca0)], [crm.c(0x7f3e69e25f80)], [crm.sta(0x7f3e69e26260)],
[crm.be(0x7f3e69e26540)], [crm.en(0x7f3e69e26820)]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false],
range_key([crm.id(0x7f3e69e245a0)]), range(MIN ; MAX)always true
Used Hint:
/*+
*/
Qb name trace:
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:
/*+
BEGIN_OUTLINE_DATA
OPTIMIZER_FEATURES_ENABLE('4.2.1.0')
END_OUTLINE_DATA
*/
Optimization Info:
crm:
table_rows:225268914
physical_range_rows:224847532
logical_range_rows:215560740
index_back_rows:0
output_rows:0
table_dop:1
dop_method:Table DOP
avaiable_index_name:[idx_crm_1, idx_crm_2, idx_crm_3, idx_crm_4, crm]
pruned_index_name:[idx_crm_2, idx_crm_3, idx_crm_4]
stats version:1741021861651651
dynamic sampling level:0
Plan Type:
LOCAL
Note:
Degree of Parallelisim is 1 because of table property
