tidb 执行计划不对

[lhlzjx]

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 小时日志）

---

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

[Kongdom]

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

[lhlzjx]

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

[Kongdom]

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

读性能慢-总纲

背景 随着 TiDB 的深入使用，在日常运维当中经常会遇到诸如这样的问题： 为什么我的 TiDB 集群 QPS Duration 升高了？ 为什么我的 TiDB 集群，之前很快的语句今天变得执行非常慢了？ 为什么我的 TiDB 集群同时出现了大量的 慢 SQL？ 诸如此类各种与读请求慢相关的问题。 那么引发这些现象背后的原因是什么？我们要怎么通过 TIDB 的各种监控项与日志排查定位问…