sql语句条件查询 效率低

Bug 反馈
我有一个表如下数据又72w多条,在使用查询语句时如果where条件字段没有索引将会大大降低查询效率。(200ms-800ms) 详细信息如下(已经脱敏处理)

建表语句

DROP TABLE IF EXISTS `t_collect_record_1659571200`;
CREATE TABLE `t_collect_record_1659571200` (
  `id` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  `uid` varchar(64) NOT NULL ,
  `src` int(8) NOT NULL ,
  `collect_time` bigint(20) NOT NULL ,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `uid_ctime_id` (`uid`,`collect_time`,`id`) COMMENT 'uid_ctime_id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=780001 */;

查询语句

SELECT
  `id`,
  `src`,
  `collect_time`
FROM
  `t_collect_record_1659571200`
WHERE
  `uid` = "11100000018"
  AND `collect_time` <= 1660800000
  AND `collect_time` >= 1659579000
  AND IF (0=10,TRUE,id > 10)
  AND IF (0=1,TRUE,src = 1) # 这里条件成立则慢查询
LIMIT
  30;

执行过程对比 (修改了时间以规避缓存)

为保证格式已经上传到github
https://github.com/AtlanCI/tidb-bug/blob/main/task

【 TiDB 版本】
v5.4.2
【 Bug 的影响】
查询耗时加大
【可能的问题复现步骤】

  1. 执行建表语句
    2.写入70w左右数据
  2. 执行查询语句即可
    【看到的非预期行为】
    1.当第二个IF条件成立查询的耗时为800ms 否则只有200ms
    【期望看到的行为】
  3. 查询耗时200ms左右
    【相关组件及具体版本】

【其他背景信息或者截图】

需要环境可以联系QQ: 2029229189

条件为 IF (0=1,TRUE,src = 1) 时增加了src=1这个条件,IndexRangeScan不能过滤出所有符合条件的记录,必须要回表过滤src=1的记录,因此 导致不能将limit 下推到tikv。而IF (1=1,TRUE,src = 1) 这种条件时返回为true ,索引中包含了其他条件列,直接将Limit下推到index range返回的数据少,所以会很快。可仅针对这个SQL可以建立一个包含src字段的索引(uid,collect_time,id, src)

此时将limit下推是肯定能包含到要查询的信息,为什么不会下推呢?

将limit下推到所有kv(返回的数据肯定大于要查询的limit数量),所有kv的数据最后在有TiDB server判断返回那些。这样不表查全表高效?

1、理论上优化方式应该像下面这样(5.2.3版本),应为没有排序,在回表后对回表结果进行limit即可 。你的是更新的5.4.2版本,不知道是不是有啥其他环境影响,可以在github上提个Issue

2、你的环境中耗时主要是消耗在indexrangescan上,即便是limit下推到Probe端 也不会有大的性能提升。如果性能影响比较敏感,建议先试试复合索引增加src列

  1. 实际使用中我会使用ORDER BY这里为了简化就先去除了。
  2. 我limit30条 按理说在扫索引时应该只扫30条就返回了,这里为什么会扫描全部的条目?

主要疑问就是为什么会在扫索引的时候,扫了全表的条目而非limit 设定的数量

1、 order by 的不能下推 看下 https://github.com/pingcap/tidb/issues/21250
2、因为你加了src=1条件,仅扫描30条索引数据不对啊 ,需要用索引获取的rowid回表后,得到src=1的记录才行

这里的流程是这样吗? 扫完全部索引数据回到TiDB server 然后在下发消息到Tikv 过滤src 。 Tikv扫索引回传TiDB在下发到Tikv判断src?

这里按理说不是应该直接都在Tikv中直接做掉?

index scan就是扫索引获取rowid返回tidb server 然后再回tikv 根据rowid 扫描记录,然后通过selection过滤 索引中不包含的条件 就是src=1, 之后再把结果返回tidb,理论上最后符合条件的数据应该可以使用Limit,就像我贴的5.2.3那个截图一样。

这里有个疑问啊 就是Tikv返回扫索引得到的rowID 这里是一次性返回70w条还是 基于某种分页? 如果是分页的话 我这里TiDB在接收到分页返回的rowID 后直接下推过滤。 按理来说我这一次分页 应该就已经拿到了复合条件的数据。 不用关注后面的rowID了吧

按照类似分页的某种批次返回,因为limit没能下推,所以所有的rowid都要回表

每太能理解啊 不是得到一页rowID就下推Tikv查? 得到所有rowID在分页下推TiKV过滤src? 第一种的话 我查一次基本都得到了所有数据。就完事了

  1. 这里为什么这样设计? 直接一页一页查不是有很明显优势?

这应该和 key构成、coprocessor task构建有关系,需要获得所有rowid后,然后根据region key 范围构建cop task下发到tikv

在这个链接中查到了关于这个场景的方法。 直达链接https://github.com/pingcap/tidb/issues/30578 通过 tidb_enable_paging 选项可以使这里逻辑变成Tikv返回一批rowID数据后直接去回表查。不会现在TiDB server汇总 。 这样达成低延迟200ms返回数据和没有src条件时基本一致。

变量连接:https://docs.pingcap.com/zh/tidb/v5.4/system-variables#:~:text=tidb_enable_paging%20从%20v5.4.0%20版本开始引入

:call_me_hand: 启用后的执行计划贴下看看

这里我们想了一下。 可能是是这样的。 如果不在TiDB server层汇总所有rowID,而是采用返回一批rowID直接回表查询,如果这个符合条件的数据在最后一批rowID返回,那么TiDB server会下发很多无效查询,多次的回表查询,这里会网络和磁盘I/O都会爆炸。 当TiDB 拿到所有的rowID他就可以优化这里的查询次数。 像这里就是返回了70W的rowID最终回表只有200行。 这样也确实合理。