sql语句条件查询效率低

Atlan · 2022 年8 月 2 日 04:14

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

github.com/AtlanCI/tidb-bug

task

main

# 第二条件成立 高耗时	
id                       	task     	estRows	operator info                                                                                                                                                actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                       	memory 	disk
	Projection_7             	root     	30     	db_location.t_collect_record_1659571200.id, db_location.t_collect_record_1659571200.src, db_location.t_	30     	time:602.2ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                               	6.33 KB	N/A
	└─Limit_12               	root     	30     	offset:0, count:30                                                                                                                                          30     	time:602.2ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                	N/A    	N/A
	  └─IndexLookUp_27       	root     	30     	                                                                                                                                                            30     	time:602.2ms, loops:1, index_task: {total_time: 604.7ms, fetch_handle: 598.6ms, build: 3.54ms, wait: 2.55ms}, table_task: {total_time: 24.5ms, num: 7, concurrency: 5}                                                                                                                                                                                                                                                                                                               	5.69 MB	N/A
	    ├─Selection_25       	cop[tikv]	30     	gt(db_location.t_collect_record_1659571200.id, 10)                                                                                                          723877 	time:597.9ms, loops:20, cop_task: {num: 1, max: 592.3ms, proc_keys: 723877, tot_proc: 568ms, rpc_num: 1, rpc_time: 592.3ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:540ms, loops:711}, scan_detail: {total_process_keys: 723877, total_process_keys_size: 59357914, total_keys: 723878, rocksdb: {delete_skipped_count: 0, key_skipped_count: 723877, block: {cache_hit_count: 708, read_count: 0, read_byte: 0 Bytes}}}                                                        	N/A    	N/A
	    │ └─IndexRangeScan_23	cop[tikv]	30     	table:t_collect_record_1659571200, index:uid_ctime_id(uid, collect_time, id), range:["11100000018" 1659579000,"11100000018" 1660900000], keep order:true    723877 	tikv_task:{time:512ms, loops:711}                                                                                                                                                                                                                                                                                                                                                                                                                                                    	N/A    	N/A
	    └─Selection_26       	cop[tikv]	30     	eq(db_location.t_collect_record_1659571200.src, 1)                                                                                                          224    	time:17.2ms, loops:10, cop_task: {num: 6, max: 3.49ms, min: 1.75ms, avg: 2.64ms, p95: 3.49ms, max_proc_keys: 81, p95_proc_keys: 81, rpc_num: 6, rpc_time: 15.7ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:0s, min:0s, p80:0s, p95:0s, iters:9, tasks:6}, scan_detail: {total_process_keys: 224, total_process_keys_size: 32480, total_keys: 225, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2, block: {cache_hit_count: 463, read_count: 0, read_byte: 0 Bytes}}}	N/A    	N/A
	      └─TableRowIDScan_24	cop[tikv]	30     	table:t_collect_record_1659571200, keep order:false

此文件已被截断。显示原始文件

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

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

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

需要环境可以联系QQ: 2029229189

h5n1 · 2022 年8 月 2 日 06:34

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

Atlan · 2022 年8 月 2 日 06:48

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

Atlan · 2022 年8 月 2 日 06:52

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

h5n1 · 2022 年8 月 2 日 07:19

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

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

Atlan · 2022 年8 月 2 日 07:32

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

Atlan · 2022 年8 月 2 日 07:33

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

h5n1 · 2022 年8 月 2 日 07:34

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

Atlan · 2022 年8 月 2 日 07:38

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

Atlan · 2022 年8 月 2 日 07:39

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

h5n1 · 2022 年8 月 2 日 07:45

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

Atlan · 2022 年8 月 2 日 08:00

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

h5n1 · 2022 年8 月 2 日 08:03

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

Atlan · 2022 年8 月 2 日 08:10

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

h5n1 · 2022 年8 月 2 日 08:17

Atlan · 2022 年8 月 2 日 08:35

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

h5n1 · 2022 年8 月 2 日 08:48

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

Atlan · 2022 年8 月 2 日 09:28

在这个链接中查到了关于这个场景的方法。直达链接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版本开始引入

h5n1 · 2022 年8 月 2 日 09:29

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

Atlan · 2022 年8 月 2 日 09:34

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

sql语句条件查询 效率低

建表语句

查询语句

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

sql语句条件查询效率低