SQL 慢查询:process keys 远大于 索引范围内的key

慢日志:

# Time: 2021-12-15T19:55:59.334044941+08:00 
# Txn_start_ts: 429803270043860996 
# User@Host: security_danmu[security_danmu] @ 10.144.55.112 [10.144.55.112] 
# Conn_ID: 288632 
# Query_time: 0.319036059 
# Parse_time: 0.000005581 
# Compile_time: 0.000397014 
# Rewrite_time: 0.000049733 
# Cop_time: 0.354730808 Process_time: 0.396 Wait_time: 0.024 Request_count: 361 Total_keys: 591981 Process_keys: 307329 
# DB: security_danmu 
# Index_names: [danmu:ix_current_push_status_last_push_time] 
# Is_internal: false 
# Digest: 8b2ea40d31ae72f2ac1dc05f13740b5772368ba4281bd8a3bec543a554d93b4c 
# Stats: danmu:429803261196501018 
# Num_cop_tasks: 361 
# Cop_proc_avg: 0.001096952 Cop_proc_p90: 0 Cop_proc_max: 0.3 Cop_proc_addr: 10.144.224.24:20160 
# Cop_wait_avg: 6.6481e-05 Cop_wait_p90: 0 Cop_wait_max: 0.004 Cop_wait_addr: 10.144.192.29:20160 
# Mem_max: 661557 
# Prepared: true 
# Plan_from_cache: false 
# Has_more_results: false 
# KV_total: 0.519981378 
# PD_total: 0.000464811 
# Backoff_total: 0 
# Write_sql_response_total: 0.000076874 
# Succ: true 
# # Plan_digest: SELECT id, videocid, videoaid, cmtid, userid, state, dmctime, utime, last_push_time, last_push_state, current_push_status, last_hit_strategy, validate, mtime, ctime FROM danmu WHERE current_push_status = ? AND utime <= ? LIMIT 500 [arguments: (0, 1639569269)];

:这个索引范围里的key大概只有5000,上面的process高达300,000
各位大老能帮忙看看啥原因吗,多谢
查询计划:

tidb_decode_plan('kQ9oMAkzXzcJMAk1MDAJc2VjdXJpdHlfZGFubXUuCQYMaWQsIFIZABR2aWRlb2N2HwAAYWIfAAhjbXRiHAAMdXNlcmIdABBzdGF0ZVqTABRkbWN0aW1eHgAAdWocACRsYXN0X3B1c2hfkiUAboUAFGN1cnJlbg1OASkEdXNarwAFdSxoaXRfc3RyYXRlZ3laKAAQdmFsaWRm9gAAbWqzACUQEAkyMTcJASVoOjMxOC4zOT: 	id                    	task     	estRows	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	actRows	execution info                                                                                                                                                                                                                                                                                                                          	memory          	disk
* 	Projection_7          	root     	500    	security_danmu.danmu.id, security_danmu.danmu.videocid, security_danmu.danmu.videoaid, security_danmu.danmu.cmtid, security_danmu.danmu.userid, security_danmu.danmu.state, security_danmu.danmu.dmctime, security_danmu.danmu.utime, security_danmu.danmu.last_push_time, security_danmu.danmu.last_push_state, security_danmu.danmu.current_push_status, security_danmu.danmu.last_hit_strategy, security_danmu.danmu.validate, security_danmu.danmu.mtime, security_danmu.danmu.ctime	217    	time:318.399515ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                             	63.1064453125 KB	N/A
* 	└─Limit_9             	root     	500    	offset:0, count:500                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	217    	time:318.38694ms, loops:2                                                                                                                                                                                                                                                                                                               	N/A             	N/A
* 	  └─IndexLookUp_22    	root     	500    	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        	217    	time:318.385418ms, loops:2, index_task:299.741802ms, table_task:{num:6, concurrency:4, time:1.251138239s}                                                                                                                                                                                                                               	582.9453125 KB  	N/A
* 	    ├─IndexScan_15    	cop[tikv]	500    	table:danmu, index:ix_current_push_status_last_push_time(current_push_status, last_push_time), range:[0,0], keep order:false                                                                                                                                                                                                                                                                                                                                                            	17566  	time:299.748628ms, loops:6, cop_task: {num: 5, max: 299.520558ms, min: 606.297µs, avg: 62.641575ms, p95: 299.520558ms, max_proc_keys: 275851, p95_proc_keys: 275851, tot_proc: 316ms, rpc_num: 5, rpc_time: 313.159891ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:300ms, min:0s, p80:300ms, p95:300ms, iters:33, tasks:5}     	N/A             	N/A
* 	    └─Limit_21        	cop[tikv]	500    	offset:0, count:500                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	217    	time:56.798679ms, loops:11, cop_task: {num: 356, max: 17.568268ms, min: 243.998µs, avg: 587.594µs, p95: 968.035µs, max_proc_keys: 8192, p95_proc_keys: 23, tot_proc: 80ms, tot_wait: 24ms, rpc_num: 357, rpc_time: 207.472013ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:16ms, min:0s, p80:0s, p95:0s, iters:404, tasks:356}	N/A             	N/A
* 	      └─Selection_17  	cop[tikv]	500    	le(security_danmu.danmu.utime, 1639569269)                                                                                                                                                                                                                                                                                                                                                                                                                                              	217    	time:0ns, loops:0, tikv_task:{proc max:16ms, min:0s, p80:0s, p95:0s, iters:404, tasks:356}                                                                                                                                                                                                                                              	N/A             	N/A
* 	        └─TableScan_16	cop[tikv]	500    	table:danmu, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                           	17566  	time:0ns, loops:0, tikv_task:{proc max:16ms, min:0s, p80:0s, p95:0s, iters:404, tasks:356}                                                                                                                                                                                                                                              	N/A             	N/A
4 个赞

修改删除产生的Mvcc历史版本较多,GC保留多久

3 个赞

我这强迫症 看到就想格式化下:smiley:

4 个赞

看看tidb_gc_life_time设置的时间

2 个赞

但看上去total key 和 process key 相差不大,和范围内的key相差过大

1 个赞

可以贴写explain analyze执行计划

2 个赞

查询的表是否有频繁或者大量的写入、更新和删除操作

2 个赞

数据库什么版本

1 个赞

是有的

2 个赞

第一个问题是mvcc版本太多,导致耗时增加。
第二个问题是,可能sql语句还有优化空间,需要看执行计划才知道要怎么优化

2 个赞

v4.0.10

2 个赞

嗯嗯,执行计划已贴

2 个赞

嗯嗯,我疑惑的是,我每行数据最多更新三次,为什么会有那么多的版本呢

2 个赞

版本多少,参考这个bug SQL优化 扫描total_keys数的奇怪问题。

他这个案例是process key 和 total差距过大,但我这个情况是差不多的,是index range里的数量和proces key差距过大

2 个赞

索引的5000多key指的什么?表数据多少行?explain analyze看下

上面的执行计划已贴,index range下5000说的是上面走了命中索引[0, 0],在这个索引下数据行数一共不超过5000

根据执行计划,看起来统计信息不太对,建议先analyze一下

tidb是通过gc去删除数据的,你的更新,插入,删除操作,在底层其实都是在插入数据,所以版本会比较多

建议先analyze table and index,然后再贴一下explain analyze结果