delete 删除优化

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:4.0.9

  • 【问题描述】:

    id task estRows operator info actRows execution info memory disk
    Delete_5 root 0 N/A 0 time:27.295369115s, loops:1, lock_keys: {time:23.383124ms, region:1, keys:2, lock_rpc:23.327396ms, rpc_count:1} 1.443359375 KB N/A
    └─SelectLock_7 root 0.04061395321763991 for update 2 time:27.295308172s, loops:2 N/A N/A
    └─IndexLookUp_14 root 0.04061395321763991 2 time:27.271903788s, loops:2, index_task:90.545254ms, table_task:{num:10, concurrency:4, time:1m7.262855057s} 1.97650146484375 MB N/A
    ├─IndexScan_11 cop[tikv] 214603.92573224317 table:tags, index:index_tag(tag), range:[“Sunny”,“Sunny”], keep order:false 121548 time:12.267865516s, loops:10, cop_task: {num: 1, max:88.944643ms, proc_keys: 131067, rpc_num: 1, rpc_time: 88.922812ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:80ms, loops:123} N/A N/A
    └─Selection_13 cop[tikv] 0.04061395321763991 eq(test.tags.file_key, “8d03a6096498e378e438aff975f1a99e3feb4349d4451577ad1963f92c3203db”) 2 time:1m6.855473624s, loops:11, cop_task: {num: 12, max: 14.994114452s, min: 53.72071ms, avg: 6.039115905s, p95: 14.994114452s, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 2.371s, tot_wait: 1.015s, rpc_num: 12, rpc_time: 1m12.469060728s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:485ms, min:28ms, p80:448ms, p95:485ms, iters:176, tasks:12} N/A N/A
    └─TableScan_12 cop[tikv] 214603.92573224317 table:tags, keep order:false 121548 time:0ns, loops:0, tikv_task:{proc max:484ms, min:28ms, p80:446ms, p95:484ms, iters:176, tasks:12} N/A N/A


执行时间过长

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出打印结果,请务必全选并复制粘贴上传。

建议将 tag 与 file_key 建立联合索引
参数 sunny 命中行数为 214603 ,然后需要回表扫描 file_key 命中行数 估算少于1 行