sql语句条件查询 效率低

🌞 建议反馈 产品缺陷
1

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左右
    【相关组件及具体版本】

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

image
image1436×708 36.2 KB
需要环境可以联系QQ: 2029229189

2

条件为 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）

3

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

4

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

5

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

image
image1070×153 6.33 KB

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

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

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

8

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

9

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

10

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

11

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

12

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

13

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

14

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

15

image
image975×281 29.1 KB

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

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

18

在这个链接中查到了关于这个场景的方法。 直达链接Using paging to improve performance for indexlookup · Issue #30578 · pingcap/tidb · GitHub 通过 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版本开始引入

19

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

20

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