20亿数据表基于主键asc查询耗时特别高

TiDBer_yyy · 2022 年6 月 20 日 06:36

TiDB版本：5.0.4
问题描述：
20亿数据表基于主键asc查询耗时特别高；降序查询速度非常快。

问题相似于：order by asc 和desc 响应时差不一 - #5，来自 Hacker_FrIg00OR

执行计划：

问题1:
tidb执行基于主键升序排序耗时过高，升序查询的原理是什么？

问题2: 20亿条数据的表，执行 analyze table t1; 整个过程大概持续多久。

问题3: 类似 select name from t1 order by id asc limit 100 的sql有什么逻辑优化的建议咩？

xfworld · 2022 年6 月 20 日 07:47

tidb 不支持倒排，基本上是将数据聚合到 tidb 的内存中，重新排序获取的
这个要看表的健康度，健康度本身就还不错，用不了一会
如果有倒排的要求，建议尽量缩小数据规模后，在执行倒排…

songxuecheng · 2022 年6 月 20 日 07:50

select * from information_schema.statistics COLLATION ：列在索引中的排序方式。取值可以是 A （升序）、 D （降序）或 NULL （未排序）。

jiyf · 2022 年6 月 20 日 08:33

从表结构以及 sql 来看，当前的执行计划没有问题，至于为什么慢，可以按照下面排查验证：

推断对表执行了 delete from tb where I_ID < k 类似的清理数据的操作，而这些删除还没有被真正 gc 掉
select I_ID from tb order by I_ID asc limit 100; 查询出来最小的 I_ID 例如为 1000(当前 sql 执行应该较慢)；
执行 select name from tb where I_ID >= 1000 order by id asc limit 100
如果 3 很快，那就验证了 1，同时这也是优化方法
也可以查看执行计划中 processed keys 以及 Total Keys，两者是否差别很大。

TiDBer_yyy · 2022 年6 月 20 日 08:52

get!
健康度 99

TiDBer_yyy · 2022 年6 月 20 日 08:57

多谢指点：
步骤3，执行比较慢，时间同步骤2相近。

jiyf · 2022 年6 月 20 日 09:01

如果改为执行 select name from tb where I_ID >= 1000 order by id asc limit 1 会慢吗？

完整的 analyze 执行计划看看 processed keys 以及 Total Keys？

这里说删除不一定是 delete from tb where I_ID < k 这种，删除“旧数据”，较大概率删除 I_ID 较小的数据记录都可能造成这种情况。

TiDBer_yyy · 2022 年6 月 20 日 09:02

asc 在TiDB是倒排么，好像mysql 中称正向排序。是tidb不支持升序排序么？

TiDBer_yyy · 2022 年6 月 20 日 09:05

仍然比较慢
dashboard上的执行计划：（今天的，sql每次要执行20min左右，只能提供相对新鲜的analyze）
PS： i_id为主键。

SELECT CH_ID FROM tb1 ORDER BY I_ID ASC LIMIT 100;




	id                       	task     	estRows	operator info                                     	actRows	execution info                                                                                                                                                                                                                                                                                         	memory 	disk
	Projection_7             	root     	100    	db1.tb1.ch_trace_id                	100    	time:21m44.1s, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                	6.62 KB	N/A
	└─Limit_12               	root     	100    	offset:0, count:100                               	100    	time:21m44.1s, loops:2                                                                                                                                                                                                                                                                                 	N/A    	N/A
	  └─TableReader_25       	root     	100    	data:Limit_24                                     	100    	time:21m44.1s, loops:1, cop_task: {num: 14518, max: 459.6ms, min: 285.2µs, avg: 88.5ms, p95: 183.5ms, max_proc_keys: 100, p95_proc_keys: 0, tot_proc: 21m10s, tot_wait: 5.29s, rpc_num: 14518, rpc_time: 21m24s, copr_cache_hit_ratio: 0.12}, backoff{regionMiss: 15.3s}                              	5.19 KB	N/A
	    └─Limit_24           	cop[tikv]	100    	offset:0, count:100                               	100    	tikv_task:{proc max:459ms, min:4ms, p80:126ms, p95:188ms, iters:14520, tasks:14518}, scan_detail: {total_process_keys: 100, total_keys: 4875306766, rocksdb: {delete_skipped_count: 3947646, key_skipped_count: 4883560764, block: {cache_hit_count: 124342, read_count: 3390687, read_byte: 16.9 GB}}}	N/A    	N/A
	      └─TableRangeScan_23	cop[tikv]	100    	table:tb1, range:[0,+inf], keep order:true	100    	tikv_task:{proc max:459ms, min:4ms, p80:126ms, p95:188ms, iters:14520, tasks:14518}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}}                                     	N/A    	N/A

jiyf · 2022 年6 月 20 日 09:12

total_process_keys: 100, total_keys: 4875306766

这里两个值差别很大，要么就是 gc 问题，要么就是版本还多。

你说执行 select name from tb where I_ID >= 1000 order by id asc limit 1 比较慢耗时是多少？
这里 limit 是 1， 1000 代表上一个查出来的 I_ID.

TiDBer_yyy · 2022 年6 月 20 日 09:28

目前已经执行 400s+

tidb 集群开启了 ti-cdc，但是这个表没有使用cdc同步。
gc-ttl = 86400 （1天）

gc相关变量：tidb_gc_life_time=8h; tidb_gc_run_interval=10m0s
查看MVCC版本：发现 write-cf中版本信息过旧：

 write cf value: r_type: Del start_ts: 429506730458873876 commit_ts: 429506730458873881

TiDBer_yyy · 2022 年7 月 6 日 12:07

问题相似：order by asc 和desc 响应时差不一
集群版本：5.0.4
PS：表统计信息每天收集
gc-life-time =8h

@小王同学

ASC 慢 sql：


# Time: 2022-07-06T09:23:30.70220307+08:00
# Txn_start_ts: 434390759282835474
# Conn_ID: 23472111
# Query_time: 1369.013604921
# Parse_time: 0.000064954
# Compile_time: 0.00041523
# Rewrite_time: 0.000100666
# Optimize_time: 0.000189457
# Wait_TS: 0.000027597
# Cop_time: 1368.741306837 Process_time: 1335.475 Wait_time: 5.12 Backoff_time: 15.19 Request_count: 13762 Process_keys: 100 Total_keys: 5186334980 Rocksdb_delete_skipped_count: 3847527 Rocksdb_key_skipped_count: 5193472877 Rocksdb_block_cache_hit_count: 128933 Rocksdb_block_read_count: 3433538 Rocksdb_block_read_byte: 15552386931
# Is_internal: false
# Digest: 00dce6df80ad19fbbc855e012f29395af002befab0ad17055ddbcd2212dbdd32
# Stats: t1:434382294313336944
# Num_cop_tasks: 13762
# Cop_proc_avg: 0.097040764 Cop_proc_p90: 0.158 Cop_proc_max: 0.458 Cop_proc_addr: ip1:20160
# Cop_wait_avg: 0.000372038 Cop_wait_p90: 0.001 Cop_wait_max: 0.011 Cop_wait_addr: ip2:20160
# Cop_backoff_regionMiss_total_times: 7595 Cop_backoff_regionMiss_total_time: 15.19 Cop_backoff_regionMiss_max_time: 0.002 Cop_backoff_regionMiss_max_addr: 172.16.25.8:20160 Cop_backoff_regionMiss_avg_time: 0.002 Cop_backoff_regionMiss_p90_time: 0.002
# Mem_max: 11874
# Prepared: false
# Plan_from_cache: false
# Plan_from_binding: false
# Has_more_results: false
# KV_total: 1348.78450469
# PD_total: 2.6595965010000002
# Backoff_total: 15.194
# Write_sql_response_total: 0.000011118
# Succ: true
# Plan_digest: d615ddde2dbc83573fa182d7fbe85507a864959c9472780b366cb033d06141b8
SELECT xxxxx_ID FROM t1 ORDER BY I_ID ASC LIMIT 100;

DESC 慢SQL

# Time: 2022-07-06T20:05:24.196223074+08:00
# Txn_start_ts: 434401213205446729
# Conn_ID: 24416521
# Query_time: 3.969983584
# Parse_time: 0.000049059
# Compile_time: 0.000247488
# Rewrite_time: 0.00004905
# Optimize_time: 0.00012623
# Wait_TS: 0.000012632
# Cop_time: 0.001093352 Request_count: 2 Process_keys: 100 Total_keys: 108 Rocksdb_delete_skipped_count: 20 Rocksdb_key_skipped_count: 147 Rocksdb_block_cache_hit_count: 27 Rocksdb_block_read_count: 4 Rocksdb_block_read_byte: 69750
# Is_internal: false
# Digest: 1b6ec0b90d04550a4a5fa1952e8a57c6460ad7a19aa51fc5718d1b161496fc5c
# Stats: t1:434398306067808282
# Num_cop_tasks: 2
# Cop_proc_avg: 0 Cop_proc_p90: 0 Cop_proc_max: 0 Cop_proc_addr: ip1:20160
# Cop_wait_avg: 0 Cop_wait_p90: 0 Cop_wait_max: 0 Cop_wait_addr: ip2:20160
# Mem_max: 11688
# Prepared: false
# Plan_from_cache: false
# Plan_from_binding: false
# Has_more_results: false
# KV_total: 0.002458376
# PD_total: 3.717066355
# Backoff_total: 0
# Write_sql_response_total: 0.000010702
# Succ: true
SELECT xxx_ID FROM t1 ORDER BY I_ID desc LIMIT 100;
``

xuexiaogang · 2022 年7 月 7 日 01:06

问题1 看执行计划应该是全表了
问题2不好说时间
问题3 select name from t1 where id<100

TiDBer_yyy · 2022 年7 月 7 日 01:21

感谢回复，还有个问题，主键asc排序，耗时巨高；相反desc排序时间快很多。

xuexiaogang · 2022 年7 月 7 日 01:25

我看有人回复说是不支持，我自己没试过。但是一般我做的时候都是where user + 时间，从业务场景来说都是看最新的那么就用到索引了，看到最新的。
但是如果说你一定要看最早的，那么是不是可以 select from t where id或者其他条件（一定范围，这个范围不要大，比如1000），然后你再排序，我觉得升降都不会慢的。

TiDBer_yyy · 2022 年7 月 7 日 03:39

理解为子查询？或者业务实现point-get or batch-point-get 逻辑。

xuexiaogang · 2022 年7 月 7 日 03:42

我的意思是范围小的排序随便怎么排都不是问题，不管是不是索引排序。
至于业务，基本上业务都是要看最新的。你说你会关心3年前的业务数据吗？还是看最近一周的？

TiDBer_yyy · 2022 年7 月 7 日 06:35

嗯嗯，业务场景目前需要看一些随机的数据。

TiDBer_yyy · 2022 年7 月 13 日 02:15

老师您好：

针对于 select create_at from t1 order by pk asc limit N 排序需求（看执行计划是全表扫描），一般有什么建议呢？
select create_at from t1 order by pk desc limit N 排序执行计划不会用到全表扫描，为什么呢？

PS
没有where条件。。。

cs58_dba · 2022 年7 月 15 日 02:39

一般建议带个日期，先筛选出最新的数据，再去排序

20亿数据表 基于主键asc查询耗时特别高

20亿数据表基于主键asc查询耗时特别高