tidb 执行计划不对

SELECT T_Account.Id AS AccountId
, (
SELECT CASE
WHEN COUNT(1) > 0 THEN 1
ELSE 0
END
FROM T_Account A
INNER JOIN T_AccountSupervision ASN ON A.AccountSupervisionId = ASN.Id
INNER JOIN T_AccountBatchInfo AB ON ASN.AccountBatchInfoId = AB.Id
WHERE A.IsValid = 1
AND A.DebtorId = T_Debtor.Id
AND A.Id != T_Account.Id
AND AB.IsFindDebt = 1
) AS IsBigNoFind
FROM T_Account
JOIN T_AccountBasicWorkState ON T_AccountBasicWorkState.Id = T_Account.AccountBasicWorkStateId
JOIN T_Debtor ON T_Debtor.Id = T_Account.DebtorId
WHERE T_Account.IsValid = 1
AND T_AccountBasicWorkState.IsWorking = 1
AND T_AccountBasicWorkState.UserId = 25448
AND T_AccountBasicWorkState.CloseTime > '2021-07-22 00:00:00 ’

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:

【概述】 场景 + 问题概述

【备份和数据迁移策略逻辑】
使用
【背景】 做过哪些操作

【现象】 业务和数据库现象

【问题】 当前遇到的问题
在mysql中执行只需要0.3秒,而在tidb中需要26秒,tidb已经收集统计信息

【业务影响】

【TiDB 版本】
v5.0.3
【附件】

  • 相关日志、配置文件、Grafana 监控(https://metricstool.pingcap.com/)
  • TiUP Cluster Display 信息
  • TiUP CLuster Edit config 信息
  • TiDB-Overview 监控
  • 对应模块的 Grafana 监控(如有 BR、TiDB-binlog、TiCDC 等)
  • 对应模块日志(包含问题前后 1 小时日志)

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

麻烦上传一下explain analyze的执行计划结果:handshake:

Projection_34 2.70 3503 root time:26.5s, loops:5, Concurrency:OFF oscarsystem.service.db2.t_account.id, case(gt(Column#300, 0), 1, 0)->Column#301 49.5 KB N/A
└─Apply_36 2.70 3503 root time:26.5s, loops:5, , cache:OFF CARTESIAN left outer join 372 Bytes N/A
├─IndexJoin_44(Build) 2.70 3503 root time:35.6ms, loops:6, inner:{total:37.1ms, concurrency:5, task:6, construct:2.47ms, fetch:33.6ms, build:1.02ms}, probe:1.54ms inner join, inner:TableReader_41, outer key:oscarsystem.service.db2.t_account.debtorid, inner key:oscarsystem.service.db2.t_debtor.id, equal cond:eq(oscarsystem.service.db2.t_account.debtorid, oscarsystem.service.db2.t_debtor.id) 235.7 KB N/A
│ ├─IndexJoin_100(Build) 2.70 3503 root time:70.1ms, loops:9, inner:{total:138.8ms, concurrency:5, task:6, construct:2.23ms, fetch:135.5ms, build:1.09ms}, probe:1.17ms inner join, inner:IndexLookUp_99, outer key:oscarsystem.service.db2.t_accountbasicworkstate.id, inner key:oscarsystem.service.db2.t_account.accountbasicworkstateid, equal cond:eq(oscarsystem.service.db2.t_accountbasicworkstate.id, oscarsystem.service.db2.t_account.accountbasicworkstateid) 449.1 KB N/A
│ │ ├─IndexReader_150(Build) 2.66 3503 root time:403.8µs, loops:10, cop_task: {num: 1, max: 347.6µs, proc_keys: 0, rpc_num: 1, rpc_time: 323.1µs, copr_cache_hit_ratio: 1.00} index:IndexRangeScan_149 110.0 KB N/A
│ │ │ └─IndexRangeScan_149 2.66 3503 cop[tikv] table:T_AccountBasicWorkState, index:rds_idx_1(IsWorking, UserId, CloseTime) tikv_task:{time:9ms, loops:8}, 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}}} range:(1 25448 2021-07-22 00:00:00,1 25448 +inf], keep order:false N/A N/A
│ │ └─IndexLookUp_99(Probe) 1.02 3503 root time:133.1ms, loops:13, index_task: {total_time: 67.4ms, fetch_handle: 67.3ms, build: 4.87µs, wait: 41.1µs}, table_task: {total_time: 401.3ms, num: 7, concurrency: 30} 13 KB N/A
│ │ ├─IndexRangeScan_96(Build) 1.03 3503 cop[tikv] table:T_Account, index:index_001(AccountBasicWorkStateId) time:67.1ms, loops:18, cop_task: {num: 10, max: 41.3ms, min: 560.3µs, avg: 7.15ms, p95: 41.3ms, max_proc_keys: 1487, p95_proc_keys: 1487, tot_proc: 64ms, rpc_num: 10, rpc_time: 71.3ms, copr_cache_hit_ratio: 0.30}, tikv_task:{proc max:40ms, min:1ms, p80:15ms, p95:40ms, iters:35, tasks:10}, scan_detail: {total_process_keys: 2270, total_keys: 4540, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2270, block: {cache_hit_count: 26856, read_count: 0, read_byte: 0 Bytes}}} range: decided by [eq(oscarsystem.service.db2.t_account.accountbasicworkstateid, oscarsystem.service.db2.t_accountbasicworkstate.id)], keep order:false N/A N/A
│ │ └─Selection_98(Probe) 1.02 3503 cop[tikv] time:57.7ms, loops:14, cop_task: {num: 12, max: 20.9ms, min: 447.8µs, avg: 5.33ms, p95: 20.9ms, max_proc_keys: 1024, p95_proc_keys: 1024, tot_proc: 53ms, rpc_num: 12, rpc_time: 63.7ms, copr_cache_hit_ratio: 0.17}, tikv_task:{proc max:19ms, min:0s, p80:10ms, p95:19ms, iters:40, tasks:12}, scan_detail: {total_process_keys: 2784, total_keys: 5568, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2784, block: {cache_hit_count: 37491, read_count: 0, read_byte: 0 Bytes}}} eq(oscarsystem.service.db2.t_account.isvalid, 1) N/A N/A
│ │ └─TableRowIDScan_97 1.03 3503 cop[tikv] table:T_Account tikv_task:{proc max:19ms, min:0s, p80:10ms, p95:19ms, iters:40, tasks:12}, 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}}} keep order:false N/A N/A
│ └─TableReader_41(Probe) 1.00 3430 root time:15.9ms, loops:13, cop_task: {num: 166, max: 3.42ms, min: 354.3µs, avg: 1.27ms, p95: 2.15ms, max_proc_keys: 147, p95_proc_keys: 69, tot_proc: 73ms, tot_wait: 31ms, rpc_num: 166, rpc_time: 204.2ms, copr_cache_hit_ratio: 0.01} data:TableRangeScan_40 N/A N/A
│ └─TableRangeScan_40 1.00 3430 cop[tikv] table:T_Debtor tikv_task:{proc max:11ms, min:0s, p80:1ms, p95:2ms, iters:204, tasks:166}, scan_detail: {total_process_keys: 3308, total_keys: 3319, rocksdb: {delete_skipped_count: 0, key_skipped_count: 22, block: {cache_hit_count: 29718, read_count: 0, read_byte: 0 Bytes}}} range: decided by [oscarsystem.service.db2.t_account.debtorid], keep order:false N/A N/A
└─StreamAgg_185(Probe) 1.00 3503 root time:26s, loops:7006 funcs:count(1)->Column#300 8 Bytes N/A
└─IndexJoin_329 1.49 9112 root time:26s, loops:6268, inner:{total:6.25s, concurrency:5, task:3098, construct:49.8ms, fetch:6.19s, build:7.89ms}, probe:10.3ms inner join, inner:TableReader_325, outer key:oscarsystem.service.db2.t_accountsupervision.accountbatchinfoid, inner key:oscarsystem.service.db2.t_accountbatchinfo.id, equal cond:eq(oscarsystem.service.db2.t_accountsupervision.accountbatchinfoid, oscarsystem.service.db2.t_accountbatchinfo.id) 24.9 KB N/A
├─IndexJoin_242(Build) 1.49 15260 root time:19.6s, loops:9699, inner:{total:6.25s, concurrency:5, task:3098, construct:51.9ms, fetch:6.19s, build:10.6ms}, probe:11.5ms inner join, inner:TableReader_239, outer key:oscarsystem.service.db2.t_account.accountsupervisionid, inner key:oscarsystem.service.db2.t_accountsupervision.id, equal cond:eq(oscarsystem.service.db2.t_account.accountsupervisionid, oscarsystem.service.db2.t_accountsupervision.id) 49.6 KB N/A
│ ├─IndexLookUp_287(Build) 1.49 15260 root time:13.3s, loops:9699, index_task: {total_time: 1.48s, fetch_handle: 1.47s, build: 2.17ms, wait: 16.6ms}, table_task: {total_time: 15.3s, num: 3098, concurrency: 5} 1.98 KB N/A
│ │ ├─Selection_285(Build) 1.51 15432 cop[tikv] time:1h35m12s, loops:33975597, cop_task: {num: 3503, max: 8.11ms, min: 259.8µs, avg: 432.7µs, p95: 496.9µs, max_proc_keys: 61, p95_proc_keys: 13, tot_proc: 267ms, tot_wait: 159ms, rpc_num: 3503, rpc_time: 1.46s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:35ms, min:0s, p80:0s, p95:1ms, iters:3506, tasks:3503}, scan_detail: {total_process_keys: 18920, total_keys: 22420, rocksdb: {delete_skipped_count: 0, key_skipped_count: 18920, block: {cache_hit_count: 36796, read_count: 0, read_byte: 0 Bytes}}} ne(oscarsystem.service.db2.t_account.id, oscarsystem.service.db2.t_account.id) N/A N/A
│ │ │ └─IndexRangeScan_283 1.89 18935 cop[tikv] table:A, index:IX_DebtorId(DebtorId) tikv_task:{proc max:35ms, min:0s, p80:0s, p95:1ms, iters:3506, tasks:3503}, 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}}} range: decided by [eq(oscarsystem.service.db2.t_account.debtorid, oscarsystem.service.db2.t_debtor.id)], keep order:false N/A N/A
│ │ └─Selection_286(Probe) 1.49 15260 cop[tikv] time:1.86s, loops:6196, cop_task: {num: 14019, max: 26.6ms, min: 297.8µs, avg: 459.4µs, p95: 549.6µs, max_proc_keys: 42, p95_proc_keys: 2, tot_proc: 1.15s, tot_wait: 578ms, rpc_num: 14019, rpc_time: 6.23s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:1ms, min:0s, p80:0s, p95:1ms, iters:14020, tasks:14019}, scan_detail: {total_process_keys: 15432, total_keys: 30866, rocksdb: {delete_skipped_count: 0, key_skipped_count: 15438, block: {cache_hit_count: 250603, read_count: 0, read_byte: 0 Bytes}}} eq(oscarsystem.service.db2.t_account.isvalid, 1) N/A N/A
│ │ └─TableRowIDScan_284 1.51 15432 cop[tikv] table:A tikv_task:{proc max:1ms, min:0s, p80:0s, p95:1ms, iters:14020, tasks:14019}, 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}}} keep order:false N/A N/A
│ └─TableReader_239(Probe) 1.00 15260 root time:1.58s, loops:6196, cop_task: {num: 13731, max: 14.7ms, min: 265.7µs, avg: 401.2µs, p95: 480.9µs, max_proc_keys: 42, p95_proc_keys: 2, tot_proc: 821ms, tot_wait: 563ms, rpc_num: 13731, rpc_time: 5.31s, copr_cache_hit_ratio: 0.00} data:TableRangeScan_238 N/A N/A
│ └─TableRangeScan_238 1.00 15260 cop[tikv] table:ASN tikv_task:{proc max:12ms, min:0s, p80:0s, p95:1ms, iters:13732, tasks:13731}, scan_detail: {total_process_keys: 15259, total_keys: 30518, rocksdb: {delete_skipped_count: 0, key_skipped_count: 15259, block: {cache_hit_count: 167890, read_count: 0, read_byte: 0 Bytes}}} range: decided by [oscarsystem.service.db2.t_account.accountsupervisionid], keep order:false N/A N/A
└─TableReader_325(Probe) 0.43 9112 root time:1.6s, loops:5863, cop_task: {num: 13275, max: 14.4ms, min: 249.2µs, avg: 407.2µs, p95: 470.9µs, max_proc_keys: 42, p95_proc_keys: 2, tot_proc: 768ms, tot_wait: 534ms, rpc_num: 13275, rpc_time: 5.22s, copr_cache_hit_ratio: 0.00} data:Selection_324 N/A N/A
└─Selection_324 0.43 9112 cop[tikv] tikv_task:{proc max:12ms, min:0s, p80:0s, p95:1ms, iters:13276, tasks:13275}, scan_detail: {total_process_keys: 15256, total_keys: 15256, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 157768, read_count: 0, read_byte: 0 Bytes}}} eq(oscarsystem.service.db2.t_accountbatchinfo.isfinddebt, 1) N/A N/A
└─TableRangeScan_323 1.00 15260 cop[tikv] table:AB tikv_task:{proc max:12ms, min:0s, p80:0s, p95:0s, iters:13276, tasks:13275}, 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}}} range: decided by [oscarsystem.service.db2.t_accountsupervision.accountbatchinfoid], keep order:false N/A N/A

count()可以考虑使用TiFlash列存组件
读性能慢可以参考下面的Trouble Shooting指南分析一下~

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。