【 TiDB 使用环境】
生产测试环境,目前的部署架构:
服务器1:tidb、pd、tiflash
服务器2:tikv、tiflash
服务器3:tikv
服务器4:tikv
服务器都是实体物理机,1、2、3服务器都是40核心,512G内存;服务器4 40核心,内存128G;
【问题】 当前遇到的问题
目前有个业务问题,单表数据量目前达到了2.7亿+数据量,使用count(1)查询需要12s左右;
【业务影响】
查询效率慢
【TiDB 版本】
V4.0.12
【应用软件及版本】
【附件】 相关日志及配置信息
附慢日志结果:
# Time: 2021-10-25T15:45:01.107853347+08:00
# Txn_start_ts: 428644208276144134
# User@Host: xxx[xxx] @ 172.50.60.100 [172.50.60.100]
# Conn_ID: 13
# Query_time: 11.83318921
# Parse_time: 0.000061941
# Compile_time: 0.000679687
# Rewrite_time: 0.000166583
# Optimize_time: 0.000399941
# Wait_TS: 0.000014668
# Cop_time: 11.825721565 Process_time: 171.375 Wait_time: 0.143 Request_count: 287 Total_keys: 275725504 Process_keys: 275725159
# DB: xxx
# Index_names: [d_record:record_idx_platno]
# Is_internal: false
# Digest: 403e9a01163c65e76236e6a097374d57ad9f896e75dda24b377103ba348034e5
# Stats: d_record:428644002187182108
# Num_cop_tasks: 287
# Cop_proc_avg: 0.597125435 Cop_proc_p90: 0.833 Cop_proc_max: 1.177 Cop_proc_addr: 172.50.60.5:20160
# Cop_wait_avg: 0.000498257 Cop_wait_p90: 0.001 Cop_wait_max: 0.001 Cop_wait_addr: 172.50.60.3:20160
# Mem_max: 2480
# Prepared: false
# Plan_from_cache: false
# Plan_from_binding: false
# Has_more_results: false
# KV_total: 171.68577111
# PD_total: 0.000013648
# Backoff_total: 0
# Write_sql_response_total: 0.000002247
# Succ: true
# Plan: tidb_decode_plan('uAWIMAk1XzQ4CTAJMQlmdW5jczpjb3VudChDb2x1bW4jNDYpLT4NDAAyASSYdGltZToxMS44cywgbG9vcHM6MgkyLjQyIEtCCU4vQQoxCTMyXzQ5BVBQaW5kZXg6U3RyZWFtQWdnXzgJMjg3TkIA4CwgY29wX3Rhc2s6IHtudW06IDI4NywgbWF4OiAxLjE4cywgbWluOiAyMjBtcywgYXZnOiA1OTguMgEOJHA5NTogODkzLjUBDlhtYXhfcHJvY19rZXlzOiAxNDc1MjgwLAEmNhgAJDEzNjI0LCB0b3QFGCA6IDJtNTEuNHMJEwx3YWl0AT4AMwFUDHJwY18ZlgEOJQYJMgQ3cwW+bHJfY2FjaGU6IGRpc2FibGVkfQkzNDYgQnl0ZXMlGRwyCTVfOAkxXyFFLmkBADE5YQQ0Ni0hBGt2KQ0AewHKACAhBz0GCDIxOQGVGHA4MDo3NDcFCxQ5NTo4OTIBC1xpdGVyczoyNjk0MTUsIHRhc2tzOjI4N30BgwEEHAozCTQ2XzQwBYkoMjc1Njg1MzY2CXQBszg6ZF9ldGNfcmVjb3JkLCApvhkSLF9pZHhfcGxhdG5vKAEHWGVfbm8pLCBrZWVwIG9yZGVyOmZhbHNlAVYcNzI1MTU5CXSqyQAANgG+rskA')
# Plan_digest: 0516abf305183e69afb56af241f3a9af20260ea1650856f5b360a27c6920244e
SELECT count(1) FROM `d_record`;