慢日志:
# 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