索引失效的问题

OceanBase 技术问题
#1

【 使用环境 】生产
【 OB or 其他组件 】
【 使用版本 】4.2.18
【问题描述】清晰明确描述问题
为何不同的查询值，会影响索引是否匹配呢？
ddl:

CREATE TABLE t_edm_xflow (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘id’,
goods_id varchar(100) DEFAULT NULL COMMENT ‘商品id’,
promotion_code varchar(100) DEFAULT NULL COMMENT ‘引流位’,
order_no varchar(100) DEFAULT NULL COMMENT ‘订单号’,
uid varchar(100) DEFAULT NULL COMMENT ‘uid’,
experiment_code varchar(100) DEFAULT NULL COMMENT ‘实验组code’,
experiment_name varchar(100) DEFAULT NULL COMMENT ‘实验组名称’,
rule_code varchar(100) DEFAULT NULL COMMENT ‘rule_code’,
action varchar(100) DEFAULT NULL COMMENT ‘事件’,
creator varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT ‘SYSTEM’ COMMENT ‘创建者’,
modifier varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT ‘SYSTEM’ COMMENT ‘修改者’,
gmt_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
gmt_modified datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘修改时间’,
is_deleted char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT ‘N’ COMMENT ‘是否删除 Y：是 N：否’,
json text DEFAULT NULL COMMENT ‘消息体’,
action_time datetime DEFAULT NULL,
env varchar(100) DEFAULT NULL COMMENT ‘环境’,
pay_way varchar(100) DEFAULT NULL COMMENT ‘支付渠道’,
business_type int(11) DEFAULT NULL COMMENT ‘订单类型’,
uw_date datetime DEFAULT NULL,
pay_chain_id bigint(20) DEFAULT NULL COMMENT ‘支付链路id’,
pay_chain varchar(32) DEFAULT NULL COMMENT ‘支付链路’,
PRIMARY KEY (id),
KEY t_edm_xflow_goods_id_uw_date_IDX (goods_id, pay_chain, uw_date)
) AUTO_INCREMENT = 5931856 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC;

情况1：
sql:explain select distinct uw_date from t_edm_xflow where goods_id=‘2019024’ and uw_date >=‘2025-04-01’ and uw_date <= ‘2025-04-30’ and pay_chain = ‘cashier_h’ and is_deleted=‘N’

image
image1427×456 44.2 KB

可见,使用了索引
情况2：
explain select distinct uw_date from t_edm_xflow where goods_id=‘2019024’ and uw_date >=‘2025-04-01’ and uw_date <= ‘2025-04-30’ and pay_chain = ‘cashier_h5’ and is_deleted=‘N’

返回：
|1 |└─TABLE FULL SCAN|t_edm_xflow|7118 |550245 |
可见，未使用索引。
情况1和情况2 唯一区别是pay_chain的值不同，请问pay_chain的值为何会影响索引的匹配呢？（表是有数据的，并且存在满足情况2pay_chain='cashier_h5’的数据，无情况1pay_chain='cashier_h’的数据）

2 个赞
#4

建议把is_deleted也加到索引里，创建覆盖索引应该就能走索引了。

2 个赞
#5

SQL 2 发现得回表7K次，不如全表扫描得了。SQL 1 发现没啥数据，走索引要比回表好。这种是大小账号场景。解决方案：
1 如果接收大账户场景走索引的性能，但不让变更DDL，那就固定索引
2 如果能变更索引，那就把 is_delete 放后面，RBO阶段会把主表裁剪掉（官网搜Skyline）
3 如果都不想干，有个最差的方案, outline 绑定一下，use_plan_cache(none)，让大账户回表，小账户走索引

4 个赞
#6

索引扫描回表也是有代价的，跟查询的条件有关系，不一定走索引就更快的，现在都是CBO的优化器，全表扫描的代价<索引扫描+回表的代价优化器就会选择全表扫描了，这个case里面还要返回表去筛选is_deleted='N’的数据

1 个赞
#7

OceanBase 的索引匹配规则（前置规则）依赖于以下几个关键因素：

  1. 查询条件是否覆盖所有索引键
    索引全匹配是指查询条件中包含了索引键上的所有列，并且这些列上存在等值条件（对应于 get 或 multi-get 操作）。如果查询条件无法完全覆盖索引键，则该索引可能不会被选中。
  2. 是否需要回表
    回表是指通过索引定位到数据后，还需要回到主表获取其他列的数据。如果查询条件能够直接从索引中获取所需数据而无需回表，那么该索引会被优先选择。
  3. 回表数量的阈值
    如果查询条件命中了唯一性索引但需要回表，优化器会进一步评估回表的数量。只有回表数量小于一定阈值时，才会选择该索引。

当前两个sql的执行时间差距大么

3 个赞
#8

非常大，情况2的时间非常快，情况1需要2s左右，我试了下如果采用覆盖索引避免回表，的确会命中索引，感谢回答！

2 个赞
#11

尽量不要回表 如果数据量大 及时命中了索引 回表的消耗比全表扫消耗的还要大 覆盖索引可以避免回表操作

1 个赞
#12

欢迎欢迎热量换迎

1 个赞