SQL优化

为提高效率,提问时请尽量提供详细背景信息,问题描述清晰可优先响应。以下信息点请尽量提供:

  • 系统版本 & kernel 版本
    centos7
  • TiDB 版本
    3.0.4
  • 磁盘型号
  • 集群节点分布
    2个tidb节点,3个pd节点,3个tikv节点
  • 数据量 & region 数量 & 副本数
  • 问题描述(我做了什么)
    目前我这个集群上就跑这一个sql,没有其他业务,表结构以及sql如图,帮忙看看可以在优化嘛
    SELECT id,vin,sn,file_key,convert_file_key,md5,secret_key,account_id,convert_status,fail_reason,collect_time,convert_time,callback_time,receive_time FROM veh_can_message_file ORDER BY id DESC LIMIT 5000000, 1 ;
  • 关键词

slow log 以及 EXPLAIN ANALYZE 辛苦贴一下

EXPLAIN ANALYZE 结果

慢查询

Time: 2019-10-25T14:54:54.344123886+08:00

Txn_start_ts: 412086825306030081

User: root@172.21.214.58

Conn_ID: 8461

Query_time: 8.632494915

Parse_time: 0.000180318

Compile_time: 0.00053771

Process_time: 21.131 Wait_time: 3.162 Request_count: 14 Total_keys: 5082999 Process_keys: 5082985

DB: ssp_vds_ptest

Is_internal: false

Digest: 3d94fb8cd643d0370f977762aa96193d44cfaae2963ffbd50dd6ce8cfd0ca603

Stats: veh_can_message_file:412067073502740481

Num_cop_tasks: 14

Cop_proc_avg: 1.509357142 Cop_proc_p90: 1.806 Cop_proc_max: 1.874 Cop_proc_addr: 172.21.214.54:20160

Cop_wait_avg: 0.225857142 Cop_wait_p90: 0.268 Cop_wait_max: 0.268 Cop_wait_addr: 172.21.214.55:20160

Mem_max: 954195318

Prepared: false

Has_more_results: false

Succ: true

SELECT id,vin,sn,file_key,convert_file_key,md5,secret_key,account_id,convert_status,fail_reason,collect_time,convert_time,callback_time,receive_time FROM veh_can_message_file ORDER BY id DESC LIMIT 5000000, 1;

看起来 等待时间比较久,可以按照以下方式分析下:

先按照 SQL tunning 逻辑查一下分析这个问题,有具体的情况可以看看哈。 SQL Tunging 方向

  1. 检查 SQL 涉及的表的执行计划是否是最新,注意 https://pingcap.com/docs-cn/v3.0/reference/performance/statistics/
  2. 确认 SQL Plan 是否符合预期,走上预期索引,有没有 cost 特别高的执行计划 https://pingcap.com/docs-cn/v3.0/reference/performance/understanding-the-query-execution-plan/
  3. 检查 TiKV 存储的 region size 的平均值是否在 96MB 左右 https://pingcap.com/docs-cn/v3.0/reference/key-monitoring-metrics/tikv-dashboard/
  4. 检查 TiDB 集群中 CPU 资源瓶颈和 scan key 的数据量,确认当前瓶颈的点,适当调整 coprocessor 和 conccurency 情况 https://pingcap.com/docs-cn/v3.0/reference/performance/tune-tikv/