为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【TiDB 版本】4.0.11
【问题描述】执行计划按预期运行一段时间后发生改变，效率变差，查看执行计划时有warning。具体如下：
1、sql语句如下：
EXPLAIN ANALYZE SELECT
ds.areaName str1,
yf.dept_name str2,
a.dept_name str3,
ifnull( t3.user_code, "未接通" ) str4,
ifnull( t3.user_name, "预测式外呼" ) str5,
sum( nocount ) vn6,
sum( callcount ) vn7,
sum( callSuccessCount ) vn8,
IFNULL( concat( round( sum( callSuccessCount ) / sum( callcount ) * 100, 2 ), '%' ), '0.00%' ) str9,
sum( IFNULL( a.callTimes, 0 ) ) vn10,
sum( ifnull( agreeCount, 0 ) ) vn11,
IFNULL(
concat( round( sum( ifnull( agreeCount, 0 ) ) / sum( callSuccessCount ) * 100, 2 ), '%' ),
'0.00%'
) str12,
sum( ifnull( t.orderCount, 0 ) ) vn13,
sum( ifnull( b.quality_count, 0 ) ) vn14,
sum( ifnull( CASE WHEN b.quality_result = 'approve' THEN b.quality_count ELSE 0 END, 0 ) ) vn15,
date_format(
date_sub(
from_unixtime(
IFNULL( sum( t5.free_times ), 0 ) + IFNULL( sum( t5.talking_times ), 0 ) + IFNULL( sum( t5.busy_times ), 0 )
),
INTERVAL 8 HOUR
),
'%H:%i:%s'
) str16,
date_format( date_sub( from_unixtime( IFNULL( sum( t5.free_times ), 0 ) ), INTERVAL 8 HOUR ), '%H:%i:%s' ) str17,
date_format( date_sub( from_unixtime( IFNULL( sum( t5.talking_times ), 0 ) ), INTERVAL 8 HOUR ), '%H:%i:%s' ) str18,
date_format( date_sub( from_unixtime( IFNULL( sum( t5.busy_times ), 0 ) ), INTERVAL 8 HOUR ), '%H:%i:%s' ) str19,
IFNULL( sum( t5.free_times ), 0 ) + IFNULL( sum( t5.talking_times ), 0 ) + IFNULL( sum( t5.busy_times ), 0 ) vn16,
IFNULL( sum( t5.free_times ), 0 ) vn17,
IFNULL( sum( t5.talking_times ), 0 ) vn18,
IFNULL( sum( t5.busy_times ), 0 ) vn19
FROM
tbl_call_record_sta a
LEFT JOIN t_touch_address ds ON a.city = ds.areaId
AND ds.pAreaId = '71'
LEFT JOIN tbl_app_dept yf ON a.parentId = yf.dept_id
LEFT JOIN (
SELECT
b1.usercode,
b1.task_id,
b1.quality_result,
b1.dept_id,
sum( b1.quality_count ) quality_count
FROM
tbl_call_quality_sta b1
WHERE
b1.quality_time = 1
AND b1.create_time >= str_to_date( '2021-03-30', '%Y-%m-%d' )
AND b1.create_time <= str_to_date( '2021-03-30', '%Y-%m-%d' )
GROUP BY
b1.usercode,
b1.task_id,
b1.quality_result,
b1.dept_id
) b ON a.usercode = b.usercode
AND a.task_id = b.task_id
AND a.dept_id = b.dept_id
LEFT JOIN (
SELECT
t1.task_id,
t1.user_id,
count( 1 ) orderCount
FROM
tbl_order_info t1
WHERE
t1.order_status = '2'
GROUP BY
t1.task_id,
t1.user_id
) t ON t.user_id = a.usercode
AND t.task_id = a.task_id
LEFT JOIN tbl_app_user t3 ON a.usercode = t3.user_id
LEFT JOIN (
SELECT
aa.city,
aa.task_id,
aa.task_name,
bb.leve1_project_type,
bb.leve2_project_type
FROM
tbl_task_info aa,
tbl_project_info bb
WHERE
aa.project_id = bb.project_id
) t4 ON t4.task_id = a.task_id
LEFT JOIN (
SELECT
tt.user_id,
sum( tt.free_times ) free_times,
sum( tt.talking_times ) talking_times,
sum( tt.busy_times ) busy_times
FROM
tbl_agent_log_sta tt
WHERE
1 = 1
AND tt.start_time >= '2021-03-30'
AND tt.start_time <= '2021-03-30'
GROUP BY
tt.user_id
) t5 ON t5.user_id = a.usercode
WHERE
1 = 1
AND a.city = '0717'
AND a.create_time >= '2021-03-30'
AND a.create_time <= '2021-03-30'
AND a.dept_Id = '116a311bc656421081f8910997a096a3'
AND a.city = '0717'
GROUP BY
ds.areaName,
yf.dept_name,
a.dept_name,
ifnull( t3.user_code, "未接通" ),
ifnull( t3.user_name, "预测式外呼" )
LIMIT 0,
10;
SHOW WARNINGS;
2、正常的执行计划如下：
|id|estRows|actRows|task|access object|execution info|operator info|memory|disk|
|---|---|---|---|---|---|---|---|---|
|Projection_38|10.00|10|root||time:135.4ms, loops:4, Concurrency:OFF|hollycas.t_touch_address.areaname, hollycas.tbl_app_dept.dept_name, hollycas.tbl_call_record_sta.dept_name, ifnull(hollycas.tbl_app_user.user_code, 未接通)->Column#199, ifnull(hollycas.tbl_app_user.user_name, 预测式外呼)->Column#200, Column#188, Column#189, Column#190, ifnull(concat(cast(round(mul(div(Column#190, Column#189), 100), 2), var_string(67)), %), 0.00%)->Column#201, Column#191, Column#192, ifnull(concat(cast(round(mul(div(Column#192, Column#190), 100), 2), var_string(67)), %), 0.00%)->Column#202, Column#193, Column#194, Column#195, date_format(date_sub(from_unixtime(plus(plus(ifnull(Column#196, 0), ifnull(Column#197, 0)), ifnull(Column#198, 0))), 8, HOUR), %H:%i:%s)->Column#203, date_format(date_sub(from_unixtime(ifnull(Column#196, 0)), 8, HOUR), %H:%i:%s)->Column#204, date_format(date_sub(from_unixtime(ifnull(Column#197, 0)), 8, HOUR), %H:%i:%s)->Column#205, date_format(date_sub(from_unixtime(ifnull(Column#198, 0)), 8, HOUR), %H:%i:%s)->Column#206, plus(plus(ifnull(Column#196, 0), ifnull(Column#197, 0)), ifnull(Column#198, 0))->Column#207, ifnull(Column#196, 0)->Column#208, ifnull(Column#197, 0)->Column#209, ifnull(Column#198, 0)->Column#210|17.8 KB|N/A|
|└─Limit_41|10.00|10|root||time:135.2ms, loops:4|offset:0, count:10|N/A|N/A|
| └─HashAgg_42|10.00|13|root||time:135.2ms, loops:3, PartialConcurrency:4, FinalConcurrency:4|group by:Column#296, Column#297, Column#298, Column#299, Column#300, funcs:sum(Column#280)->Column#188, funcs:sum(Column#281)->Column#189, funcs:sum(Column#282)->Column#190, funcs:sum(Column#283)->Column#191, funcs:sum(Column#284)->Column#192, funcs:sum(Column#285)->Column#193, funcs:sum(Column#286)->Column#194, funcs:sum(Column#287)->Column#195, funcs:sum(Column#288)->Column#196, funcs:sum(Column#289)->Column#197, funcs:sum(Column#290)->Column#198, funcs:firstrow(Column#291)->hollycas.tbl_call_record_sta.dept_name, funcs:firstrow(Column#292)->hollycas.t_touch_address.areaname, funcs:firstrow(Column#293)->hollycas.tbl_app_dept.dept_name, funcs:firstrow(Column#294)->hollycas.tbl_app_user.user_code, funcs:firstrow(Column#295)->hollycas.tbl_app_user.user_name|162.5 KB|N/A|
| └─Projection_244|23.82|19|root||time:135ms, loops:2, Concurrency:OFF|cast(hollycas.tbl_call_record_sta.nocount, decimal(42,0) BINARY)->Column#280, cast(hollycas.tbl_call_record_sta.callcount, decimal(42,0) BINARY)->Column#281, hollycas.tbl_call_record_sta.callsuccesscount, cast(ifnull(hollycas.tbl_call_record_sta.calltimes, 0), decimal(42,0) BINARY)->Column#283, ifnull(hollycas.tbl_call_record_sta.agreecount, 0)->Column#284, cast(ifnull(Column#84, 0), decimal(42,0) BINARY)->Column#285, ifnull(Column#63, 0)->Column#286, ifnull(case(eq(hollycas.tbl_call_quality_sta.quality_result, approve), Column#63, 0), 0)->Column#287, Column#185, Column#186, Column#187, hollycas.tbl_call_record_sta.dept_name, hollycas.t_touch_address.areaname, hollycas.tbl_app_dept.dept_name, hollycas.tbl_app_user.user_code, hollycas.tbl_app_user.user_name, hollycas.t_touch_address.areaname, hollycas.tbl_app_dept.dept_name, hollycas.tbl_call_record_sta.dept_name, ifnull(hollycas.tbl_app_user.user_code, 未接通)->Column#299, ifnull(hollycas.tbl_app_user.user_name, 预测式外呼)->Column#300|13.2 KB|N/A|
| └─HashJoin_44|23.82|19|root||time:134.9ms, loops:2, build_hash_table:{total:7.2ms, fetch:6.98ms, build:215.3µs}, probe:{concurrency:5, total:674.2ms, max:134.9ms, probe:48.2µs, fetch:674.2ms}|left outer join, equal:[eq(hollycas.tbl_call_record_sta.usercode, hollycas.tbl_agent_log_sta.user_id)]|136.2 KB|0 Bytes|
| ├─HashAgg_220(Build)|17.57|740|root||time:6.94ms, loops:5, PartialConcurrency:4, FinalConcurrency:4|group by:hollycas.tbl_agent_log_sta.user_id, funcs:sum(Column#259)->Column#185, funcs:sum(Column#260)->Column#186, funcs:sum(Column#261)->Column#187, funcs:firstrow(hollycas.tbl_agent_log_sta.user_id)->hollycas.tbl_agent_log_sta.user_id|176.4 KB|N/A|
| │ └─IndexLookUp_221|17.57|740|root||time:5.58ms, loops:2, index_task: {total_time: 2.19ms, fetch_handle: 2.18ms, build: 571ns, wait: 9.62µs}, table_task: {total_time: 12.1ms, num: 1, concurrency: 4}||135.5 KB|N/A|
| │ ├─IndexRangeScan_218(Build)|883.93|740|cop[tikv]|table:tt, index:idx_agent_log1(start_time)|time:2.18ms, loops:3, cop_task: {num: 1, max: 2.15ms, proc_keys: 740, tot_proc: 2ms, rpc_num: 2, rpc_time: 2.01ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:2ms, loops:5}|range:[2021-03-30,2021-03-30], keep order:false|N/A|N/A|
| │ └─HashAgg_205(Probe)|17.57|740|cop[tikv]||time:3ms, loops:2, cop_task: {num: 1, max: 2.92ms, proc_keys: 740, tot_proc: 2ms, rpc_num: 1, rpc_time: 2.91ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:1ms, loops:1}|group by:hollycas.tbl_agent_log_sta.user_id, funcs:sum(hollycas.tbl_agent_log_sta.free_times)->Column#259, funcs:sum(hollycas.tbl_agent_log_sta.talking_times)->Column#260, funcs:sum(hollycas.tbl_agent_log_sta.busy_times)->Column#261|N/A|N/A|
| │ └─TableRowIDScan_219|883.93|740|cop[tikv]|table:tt|tikv_task:{time:1ms, loops:1}|keep order:false|N/A|N/A|
| └─HashJoin_56(Probe)|23.82|19|root||time:134.8ms, loops:2, build_hash_table:{total:126.2ms, fetch:126.1ms, build:9.89µs}, probe:{concurrency:5, total:673.4ms, max:134.8ms, probe:8ms, fetch:665.4ms}|left outer join, equal:[eq(hollycas.tbl_call_record_sta.task_id, hollycas.tbl_task_info.task_id)]|251.6 KB|0 Bytes|
| ├─IndexJoin_61(Build)|23.45|19|root||time:126ms, loops:2, inner:{total:1.91ms, concurrency:4, task:1, construct:37.6µs, fetch:1.86ms, build:11.4µs}, probe:24.9µs|left outer join, inner:IndexLookUp_60, outer key:hollycas.tbl_call_record_sta.usercode, inner key:hollycas.tbl_app_user.user_id, equal cond:eq(hollycas.tbl_call_record_sta.usercode, hollycas.tbl_app_user.user_id)|48.7 KB|N/A|
| │ ├─HashJoin_68(Build)|23.42|19|root||time:125ms, loops:3, build_hash_table:{total:2.79ms, fetch:2.78ms, build:12.5µs}, probe:{concurrency:5, total:623.9ms, max:124.9ms, probe:1.27ms, fetch:622.6ms}|left outer join, equal:[eq(hollycas.tbl_call_record_sta.usercode, hollycas.tbl_order_info.user_id) eq(hollycas.tbl_call_record_sta.task_id, hollycas.tbl_order_info.task_id)]|10.2 KB|0 Bytes|
| │ │ ├─HashJoin_70(Build)|23.42|19|root||time:2.63ms, loops:2, build_hash_table:{total:832.6µs, fetch:832.6µs, build:0s}, probe:{concurrency:5, total:12.5ms, max:2.52ms, probe:27.5µs, fetch:12.5ms}|left outer join, equal:[eq(hollycas.tbl_call_record_sta.usercode, hollycas.tbl_call_quality_sta.usercode) eq(hollycas.tbl_call_record_sta.task_id, hollycas.tbl_call_quality_sta.task_id) eq(hollycas.tbl_call_record_sta.dept_id, hollycas.tbl_call_quality_sta.dept_id)]|0 Bytes|0 Bytes|
| │ │ │ ├─HashAgg_112(Build)|1.00|0|root||time:782µs, loops:1, PartialConcurrency:4, FinalConcurrency:4|group by:Column#276, Column#277, Column#278, Column#279, funcs:sum(Column#271)->Column#63, funcs:firstrow(Column#272)->hollycas.tbl_call_quality_sta.task_id, funcs:firstrow(Column#273)->hollycas.tbl_call_quality_sta.dept_id, funcs:firstrow(Column#274)->hollycas.tbl_call_quality_sta.usercode, funcs:firstrow(Column#275)->hollycas.tbl_call_quality_sta.quality_result|42.2 KB|N/A|
| │ │ │ │ └─Projection_243|0.00|0|root||time:731.9µs, loops:1, Concurrency:OFF|cast(hollycas.tbl_call_quality_sta.quality_count, decimal(31,0) BINARY)->Column#271, hollycas.tbl_call_quality_sta.task_id, hollycas.tbl_call_quality_sta.dept_id, hollycas.tbl_call_quality_sta.usercode, hollycas.tbl_call_quality_sta.quality_result, hollycas.tbl_call_quality_sta.usercode, hollycas.tbl_call_quality_sta.task_id, hollycas.tbl_call_quality_sta.quality_result, hollycas.tbl_call_quality_sta.dept_id|3.03 KB|N/A|
| │ │ │ │ └─IndexLookUp_131|0.00|0|root||time:724.3µs, loops:1, table_task: {total_time: 2.6ms, num: 0, concurrency: 4}||208 Bytes|N/A|
| │ │ │ │ ├─IndexRangeScan_128(Build)|0.00|0|cop[tikv]|table:b1, partition:p202103, index:idx_quality1(create_time)|time:597.8µs, loops:1, cop_task: {num: 1, max: 640.8µs, proc_keys: 0, rpc_num: 1, rpc_time: 634.2µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|range:[2021-03-30,2021-03-30], keep order:false|N/A|N/A|
| │ │ │ │ └─Selection_130(Probe)|0.00|0|cop[tikv]|||eq(hollycas.tbl_call_quality_sta.quality_time, 1)|N/A|N/A|
| │ │ │ │ └─TableRowIDScan_129|0.00|0|cop[tikv]|table:b1, partition:p202103||keep order:false|N/A|N/A|
| │ │ │ └─IndexJoin_76(Probe)|23.42|19|root||time:2.49ms, loops:2, inner:{total:941.8µs, concurrency:4, task:1, construct:32.3µs, fetch:905.7µs, build:2.66µs}, probe:22.2µs|left outer join, inner:IndexLookUp_75, outer key:hollycas.tbl_call_record_sta.parentid, inner key:hollycas.tbl_app_dept.dept_id, equal cond:eq(hollycas.tbl_call_record_sta.parentid, hollycas.tbl_app_dept.dept_id)|34.8 KB|N/A|
| │ │ │ ├─HashJoin_88(Build)|23.42|19|root||time:1.92ms, loops:3, build_hash_table:{total:1.43ms, fetch:1.42ms, build:12.4µs}, probe:{concurrency:5, total:9.16ms, max:1.86ms, probe:34.4µs, fetch:9.13ms}|left outer join, equal:[eq(hollycas.tbl_call_record_sta.city, hollycas.t_touch_address.areaid)]|36.7 KB|0 Bytes|
| │ │ │ │ ├─IndexLookUp_104(Build)|12.00|14|root||time:1.32ms, loops:2, index_task: {total_time: 351.7µs, fetch_handle: 347.1µs, build: 638ns, wait: 4µs}, table_task: {total_time: 2.45ms, num: 1, concurrency: 4}||9.67 KB|N/A|
| │ │ │ │ │ ├─IndexRangeScan_102(Build)|12.00|14|cop[tikv]|table:ds, index:inx-touch-address-pareaid(pAreaId)|time:343µs, loops:3, cop_task: {num: 1, max: 347.1µs, proc_keys: 14, rpc_num: 1, rpc_time: 339.2µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|range:["71","71"], keep order:false|N/A|N/A|
| │ │ │ │ │ └─TableRowIDScan_103(Probe)|12.00|14|cop[tikv]|table:ds|time:764.8µs, loops:2, cop_task: {num: 1, max: 721.3µs, proc_keys: 14, rpc_num: 1, rpc_time: 714.1µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|keep order:false|N/A|N/A|
| │ │ │ │ └─IndexLookUp_98(Probe)|23.42|19|root||time:1.81ms, loops:2, index_task: {total_time: 805.4µs, fetch_handle: 800.2µs, build: 1.24µs, wait: 3.98µs}, table_task: {total_time: 4.17ms, num: 1, concurrency: 4}||9.57 KB|N/A|
| │ │ │ │ ├─IndexRangeScan_96(Build)|23.42|19|cop[tikv]|table:a, partition:p202103, index:idx_record_sta_dct(dept_id, city, create_time)|time:799.6µs, loops:3, cop_task: {num: 1, max: 747.9µs, proc_keys: 19, rpc_num: 1, rpc_time: 735.5µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|range:["116a311bc656421081f8910997a096a3" "0717" 2021-03-30,"116a311bc656421081f8910997a096a3" "0717" 2021-03-30], keep order:false|N/A|N/A|
| │ │ │ │ └─TableRowIDScan_97(Probe)|23.42|19|cop[tikv]|table:a, partition:p202103|time:821.8µs, loops:2, cop_task: {num: 1, max: 769.6µs, proc_keys: 19, rpc_num: 1, rpc_time: 762.9µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|keep order:false|N/A|N/A|
| │ │ │ └─IndexLookUp_75(Probe)|1.00|1|root||time:866.7µs, loops:2, index_task: {total_time: 407.2µs, fetch_handle: 402.8µs, build: 650ns, wait: 3.73µs}, table_task: {total_time: 2.12ms, num: 1, concurrency: 4}||8.98 KB|N/A|
| │ │ │ ├─IndexRangeScan_73(Build)|1.00|1|cop[tikv]|table:yf, index:PRIMARY(dept_id)|time:401.1µs, loops:3, cop_task: {num: 1, max: 368.7µs, proc_keys: 1, rpc_num: 1, rpc_time: 362µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|range: decided by [eq(hollycas.tbl_app_dept.dept_id, hollycas.tbl_call_record_sta.parentid)], keep order:false|N/A|N/A|
| │ │ │ └─TableRowIDScan_74(Probe)|1.00|1|cop[tikv]|table:yf|time:353.4µs, loops:2, cop_task: {num: 1, max: 318.5µs, proc_keys: 1, rpc_num: 1, rpc_time: 311.9µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|keep order:false|N/A|N/A|
| │ │ └─HashAgg_144(Probe)|676.60|4206|root||time:124.6ms, loops:8, PartialConcurrency:4, FinalConcurrency:4|group by:hollycas.tbl_order_info.task_id, hollycas.tbl_order_info.user_id, funcs:count(Column#243)->Column#84, funcs:firstrow(hollycas.tbl_order_info.task_id)->hollycas.tbl_order_info.task_id, funcs:firstrow(hollycas.tbl_order_info.user_id)->hollycas.tbl_order_info.user_id|302.1 KB|N/A|
| │ │ └─TableReader_145|676.60|4333|root||time:122.3ms, loops:9, cop_task: {num: 5, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}|data:HashAgg_136|81.5 KB|N/A|
| │ │ └─HashAgg_136|676.60|4333|cop[tiflash]||tikv_task:{proc max:115.1ms, min:0s, p80:115.1ms, p95:115.1ms, iters:512, tasks:5}|group by:hollycas.tbl_order_info.task_id, hollycas.tbl_order_info.user_id, funcs:count(1)->Column#243|N/A|N/A|
| │ │ └─Selection_143|57698.19|62876|cop[tiflash]||tikv_task:{proc max:109.1ms, min:0s, p80:109.1ms, p95:109.1ms, iters:38, tasks:5}|eq(hollycas.tbl_order_info.order_status, "2")|N/A|N/A|
| │ │ └─TableFullScan_142|3261929.00|3263161|cop[tiflash]|table:t1|tikv_task:{proc max:104.1ms, min:0s, p80:104.1ms, p95:104.1ms, iters:58, tasks:5}|keep order:false|N/A|N/A|
| │ └─IndexLookUp_60(Probe)|1.00|18|root||time:1.79ms, loops:2, index_task: {total_time: 734.3µs, fetch_handle: 730.1µs, build: 607ns, wait: 3.61µs}, table_task: {total_time: 4.03ms, num: 1, concurrency: 4}||10.0 KB|N/A|
| │ ├─IndexRangeScan_58(Build)|1.00|18|cop[tikv]|table:t3, index:PRIMARY(user_id)|time:728.6µs, loops:3, cop_task: {num: 1, max: 691.2µs, proc_keys: 18, rpc_num: 1, rpc_time: 683.1µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|range: decided by [eq(hollycas.tbl_app_user.user_id, hollycas.tbl_call_record_sta.usercode)], keep order:false|N/A|N/A|
| │ └─TableRowIDScan_59(Probe)|1.00|18|cop[tikv]|table:t3|time:932.9µs, loops:2, cop_task: {num: 1, max: 885.3µs, proc_keys: 18, rpc_num: 1, rpc_time: 878.2µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|keep order:false|N/A|N/A|
| └─HashJoin_176(Probe)|44112.61|43599|root||time:52.9ms, loops:44, build_hash_table:{total:10.9ms, fetch:8.56ms, build:2.32ms}, probe:{concurrency:5, total:671.5ms, max:134.6ms, probe:453.1ms, fetch:218.4ms}|inner join, equal:[eq(hollycas.tbl_project_info.project_id, hollycas.tbl_task_info.project_id)]|221.3 KB|0 Bytes|
| ├─TableReader_192(Build)|9478.00|9478|root||time:8.52ms, loops:11, cop_task: {num: 1, max: 9.45ms, proc_keys: 0, rpc_num: 1, rpc_time: 9.44ms, copr_cache_hit_ratio: 0.00}|data:TableFullScan_191|250.3 KB|N/A|
| │ └─TableFullScan_191|9478.00|9478|cop[tiflash]|table:bb|tikv_task:{time:6.8ms, loops:1}|keep order:false|N/A|N/A|
| └─TableReader_200(Probe)|44112.61|43599|root||time:43.7ms, loops:44, cop_task: {num: 1, max: 44.5ms, proc_keys: 0, rpc_num: 1, rpc_time: 44.4ms, copr_cache_hit_ratio: 0.00}|data:Selection_199|2.69 MB|N/A|
| └─Selection_199|44112.61|43599|cop[tiflash]||tikv_task:{time:29.2ms, loops:1}|not(isnull(hollycas.tbl_task_info.project_id))|N/A|N/A|
| └─TableFullScan_198|46224.00|61966|cop[tiflash]|table:aa|tikv_task:{time:26.2ms, loops:1}|keep order:false|N/A|N/A|
无warning。
3、运行一段实际后异常的执行计划如下：
|id|estRows|actRows|task|access object|execution info|operator info|memory|disk|
|---|---|---|---|---|---|---|---|---|
|Projection_37|10.00|10|root||time:288.2ms, loops:4, Concurrency:OFF|hollycas.t_touch_address.areaname, hollycas.tbl_app_dept.dept_name, hollycas.tbl_call_record_sta.dept_name, ifnull(hollycas.tbl_app_user.user_code, 未接通)->Column#199, ifnull(hollycas.tbl_app_user.user_name, 预测式外呼)->Column#200, Column#188, Column#189, Column#190, ifnull(concat(cast(round(mul(div(Column#190, Column#189), 100), 2), var_string(67)), %), 0.00%)->Column#201, Column#191, Column#192, ifnull(concat(cast(round(mul(div(Column#192, Column#190), 100), 2), var_string(67)), %), 0.00%)->Column#202, Column#193, Column#194, Column#195, date_format(date_sub(from_unixtime(plus(plus(ifnull(Column#196, 0), ifnull(Column#197, 0)), ifnull(Column#198, 0))), 8, HOUR), %H:%i:%s)->Column#203, date_format(date_sub(from_unixtime(ifnull(Column#196, 0)), 8, HOUR), %H:%i:%s)->Column#204, date_format(date_sub(from_unixtime(ifnull(Column#197, 0)), 8, HOUR), %H:%i:%s)->Column#205, date_format(date_sub(from_unixtime(ifnull(Column#198, 0)), 8, HOUR), %H:%i:%s)->Column#206, plus(plus(ifnull(Column#196, 0), ifnull(Column#197, 0)), ifnull(Column#198, 0))->Column#207, ifnull(Column#196, 0)->Column#208, ifnull(Column#197, 0)->Column#209, ifnull(Column#198, 0)->Column#210|17.8 KB|N/A|
|└─Limit_40|10.00|10|root||time:288ms, loops:4|offset:0, count:10|N/A|N/A|
| └─HashAgg_41|10.00|13|root||time:288ms, loops:3, PartialConcurrency:4, FinalConcurrency:4|group by:Column#276, Column#277, Column#278, Column#279, Column#280, funcs:sum(Column#260)->Column#188, funcs:sum(Column#261)->Column#189, funcs:sum(Column#262)->Column#190, funcs:sum(Column#263)->Column#191, funcs:sum(Column#264)->Column#192, funcs:sum(Column#265)->Column#193, funcs:sum(Column#266)->Column#194, funcs:sum(Column#267)->Column#195, funcs:sum(Column#268)->Column#196, funcs:sum(Column#269)->Column#197, funcs:sum(Column#270)->Column#198, funcs:firstrow(Column#271)->hollycas.tbl_call_record_sta.dept_name, funcs:firstrow(Column#272)->hollycas.t_touch_address.areaname, funcs:firstrow(Column#273)->hollycas.tbl_app_dept.dept_name, funcs:firstrow(Column#274)->hollycas.tbl_app_user.user_code, funcs:firstrow(Column#275)->hollycas.tbl_app_user.user_name|162.5 KB|N/A|
| └─Projection_144|23.75|19|root||time:287.9ms, loops:2, Concurrency:OFF|cast(hollycas.tbl_call_record_sta.nocount, decimal(42,0) BINARY)->Column#260, cast(hollycas.tbl_call_record_sta.callcount, decimal(42,0) BINARY)->Column#261, hollycas.tbl_call_record_sta.callsuccesscount, cast(ifnull(hollycas.tbl_call_record_sta.calltimes, 0), decimal(42,0) BINARY)->Column#263, ifnull(hollycas.tbl_call_record_sta.agreecount, 0)->Column#264, cast(ifnull(Column#84, 0), decimal(42,0) BINARY)->Column#265, ifnull(Column#63, 0)->Column#266, ifnull(case(eq(hollycas.tbl_call_quality_sta.quality_result, approve), Column#63, 0), 0)->Column#267, Column#185, Column#186, Column#187, hollycas.tbl_call_record_sta.dept_name, hollycas.t_touch_address.areaname, hollycas.tbl_app_dept.dept_name, hollycas.tbl_app_user.user_code, hollycas.tbl_app_user.user_name, hollycas.t_touch_address.areaname, hollycas.tbl_app_dept.dept_name, hollycas.tbl_call_record_sta.dept_name, ifnull(hollycas.tbl_app_user.user_code, 未接通)->Column#279, ifnull(hollycas.tbl_app_user.user_name, 预测式外呼)->Column#280|13.2 KB|N/A|
| └─HashJoin_43|23.75|19|root||time:287.8ms, loops:2, build_hash_table:{total:5.61ms, fetch:5.41ms, build:200.1µs}, probe:{concurrency:5, total:1.44s, max:287.7ms, probe:46.6µs, fetch:1.44s}|left outer join, equal:[eq(hollycas.tbl_call_record_sta.usercode, hollycas.tbl_agent_log_sta.user_id)]|118.6 KB|0 Bytes|
| ├─HashAgg_138(Build)|17.56|642|root||time:5.37ms, loops:5, PartialConcurrency:4, FinalConcurrency:4|group by:hollycas.tbl_agent_log_sta.user_id, funcs:sum(Column#246)->Column#185, funcs:sum(Column#247)->Column#186, funcs:sum(Column#248)->Column#187, funcs:firstrow(hollycas.tbl_agent_log_sta.user_id)->hollycas.tbl_agent_log_sta.user_id|136.3 KB|N/A|
| │ └─IndexLookUp_139|17.56|642|root||time:4.22ms, loops:2, index_task: {total_time: 982.3µs, fetch_handle: 980.2µs, build: 853ns, wait: 1.32µs}, table_task: {total_time: 6.75ms, num: 1, concurrency: 4}||115.4 KB|N/A|
| │ ├─IndexRangeScan_136(Build)|1104.64|642|cop[tikv]|table:tt, index:idx_agent_log1(start_time)|time:968.4µs, loops:3, cop_task: {num: 1, max: 940.5µs, proc_keys: 642, tot_proc: 1ms, rpc_num: 1, rpc_time: 935.4µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:1ms, loops:5}|range:[2021-03-30,2021-03-30], keep order:false|N/A|N/A|
| │ └─HashAgg_133(Probe)|17.56|642|cop[tikv]||time:2.41ms, loops:2, cop_task: {num: 1, max: 2.35ms, proc_keys: 642, tot_proc: 2ms, rpc_num: 1, rpc_time: 2.35ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:2ms, loops:1}|group by:hollycas.tbl_agent_log_sta.user_id, funcs:sum(hollycas.tbl_agent_log_sta.free_times)->Column#246, funcs:sum(hollycas.tbl_agent_log_sta.talking_times)->Column#247, funcs:sum(hollycas.tbl_agent_log_sta.busy_times)->Column#248|N/A|N/A|
| │ └─TableRowIDScan_137|1104.64|642|cop[tikv]|table:tt|tikv_task:{time:1ms, loops:1}|keep order:false|N/A|N/A|
| └─HashJoin_47(Probe)|23.75|19|root||time:287.7ms, loops:2, build_hash_table:{total:279.3ms, fetch:279.3ms, build:12.8µs}, probe:{concurrency:5, total:1.44s, max:287.6ms, probe:7.27ms, fetch:1.43s}|left outer join, equal:[eq(hollycas.tbl_call_record_sta.task_id, hollycas.tbl_task_info.task_id)]|251.6 KB|0 Bytes|
| ├─IndexJoin_52(Build)|23.38|19|root||time:279.1ms, loops:2, inner:{total:3.49ms, concurrency:4, task:1, construct:55.6µs, fetch:3.42ms, build:11.2µs}, probe:25.5µs|left outer join, inner:IndexLookUp_51, outer key:hollycas.tbl_call_record_sta.usercode, inner key:hollycas.tbl_app_user.user_id, equal cond:eq(hollycas.tbl_call_record_sta.usercode, hollycas.tbl_app_user.user_id)|48.7 KB|N/A|
| │ ├─HashJoin_57(Build)|23.34|19|root||time:277ms, loops:3, build_hash_table:{total:3.58ms, fetch:3.57ms, build:12.5µs}, probe:{concurrency:5, total:1.38s, max:276.9ms, probe:1.66ms, fetch:1.38s}|left outer join, equal:[eq(hollycas.tbl_call_record_sta.usercode, hollycas.tbl_order_info.user_id) eq(hollycas.tbl_call_record_sta.task_id, hollycas.tbl_order_info.task_id)]|10.2 KB|0 Bytes|
| │ │ ├─HashJoin_60(Build)|23.34|19|root||time:3.43ms, loops:2, build_hash_table:{total:923.1µs, fetch:923.1µs, build:0s}, probe:{concurrency:5, total:16.4ms, max:3.32ms, probe:34.2µs, fetch:16.4ms}|left outer join, equal:[eq(hollycas.tbl_call_record_sta.usercode, hollycas.tbl_call_quality_sta.usercode) eq(hollycas.tbl_call_record_sta.task_id, hollycas.tbl_call_quality_sta.task_id) eq(hollycas.tbl_call_record_sta.dept_id, hollycas.tbl_call_quality_sta.dept_id)]|0 Bytes|0 Bytes|
| │ │ │ ├─HashAgg_88(Build)|1.00|0|root||time:859.6µs, loops:1, PartialConcurrency:4, FinalConcurrency:4|group by:Column#256, Column#257, Column#258, Column#259, funcs:sum(Column#251)->Column#63, funcs:firstrow(Column#252)->hollycas.tbl_call_quality_sta.task_id, funcs:firstrow(Column#253)->hollycas.tbl_call_quality_sta.dept_id, funcs:firstrow(Column#254)->hollycas.tbl_call_quality_sta.usercode, funcs:firstrow(Column#255)->hollycas.tbl_call_quality_sta.quality_result|42.2 KB|N/A|
| │ │ │ │ └─Projection_143|0.00|0|root||time:811.2µs, loops:1, Concurrency:OFF|cast(hollycas.tbl_call_quality_sta.quality_count, decimal(31,0) BINARY)->Column#251, hollycas.tbl_call_quality_sta.task_id, hollycas.tbl_call_quality_sta.dept_id, hollycas.tbl_call_quality_sta.usercode, hollycas.tbl_call_quality_sta.quality_result, hollycas.tbl_call_quality_sta.usercode, hollycas.tbl_call_quality_sta.task_id, hollycas.tbl_call_quality_sta.quality_result, hollycas.tbl_call_quality_sta.dept_id|3.03 KB|N/A|
| │ │ │ │ └─IndexLookUp_107|0.00|0|root||time:803.7µs, loops:1, table_task: {total_time: 1.78ms, num: 0, concurrency: 4}||208 Bytes|N/A|
| │ │ │ │ ├─IndexRangeScan_104(Build)|0.00|0|cop[tikv]|table:b1, partition:p202103, index:idx_quality1(create_time)|time:415.6µs, loops:1, cop_task: {num: 1, max: 390.8µs, proc_keys: 0, rpc_num: 1, rpc_time: 385.1µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|range:[2021-03-30,2021-03-30], keep order:false|N/A|N/A|
| │ │ │ │ └─Selection_106(Probe)|0.00|0|cop[tikv]|||eq(hollycas.tbl_call_quality_sta.quality_time, 1)|N/A|N/A|
| │ │ │ │ └─TableRowIDScan_105|0.00|0|cop[tikv]|table:b1, partition:p202103||keep order:false|N/A|N/A|
| │ │ │ └─IndexJoin_66(Probe)|23.34|19|root||time:3.27ms, loops:2, inner:{total:1.49ms, concurrency:4, task:1, construct:31.7µs, fetch:1.45ms, build:2.63µs}, probe:27.1µs|left outer join, inner:IndexLookUp_65, outer key:hollycas.tbl_call_record_sta.parentid, inner key:hollycas.tbl_app_dept.dept_id, equal cond:eq(hollycas.tbl_call_record_sta.parentid, hollycas.tbl_app_dept.dept_id)|203.9 KB|N/A|
| │ │ │ ├─IndexJoin_73(Build)|23.34|19|root||time:4.64ms, loops:3, inner:{total:1.58ms, concurrency:4, task:1, construct:29µs, fetch:1.55ms, build:3.85µs}, probe:20.6µs|left outer join, inner:IndexLookUp_72, outer key:hollycas.tbl_call_record_sta.city, inner key:hollycas.t_touch_address.areaid, equal cond:eq(hollycas.tbl_call_record_sta.city, hollycas.t_touch_address.areaid)|224.0 KB|N/A|
| │ │ │ │ ├─IndexLookUp_84(Build)|23.34|19|root||time:2.97ms, loops:3, index_task: {total_time: 688.9µs, fetch_handle: 686.8µs, build: 593ns, wait: 1.48µs}, table_task: {total_time: 4.45ms, num: 1, concurrency: 4}||9.57 KB|N/A|
| │ │ │ │ │ ├─IndexRangeScan_82(Build)|23.34|19|cop[tikv]|table:a, partition:p202103, index:idx_record_sta_dct(dept_id, city, create_time)|time:686.9µs, loops:3, cop_task: {num: 1, max: 657.2µs, proc_keys: 19, rpc_num: 1, rpc_time: 652.2µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|range:["116a311bc656421081f8910997a096a3" "0717" 2021-03-30,"116a311bc656421081f8910997a096a3" "0717" 2021-03-30], keep order:false|N/A|N/A|
| │ │ │ │ │ └─TableRowIDScan_83(Probe)|23.34|19|cop[tikv]|table:a, partition:p202103|time:1.28ms, loops:2, cop_task: {num: 1, max: 1.2ms, proc_keys: 19, rpc_num: 1, rpc_time: 1.2ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|keep order:false|N/A|N/A|
| │ │ │ │ └─IndexLookUp_72(Probe)|1.00|1|root||time:1.51ms, loops:2, index_task: {total_time: 590.2µs, fetch_handle: 588.5µs, build: 537ns, wait: 1.16µs}, table_task: {total_time: 2.85ms, num: 1, concurrency: 4}||9.47 KB|N/A|
| │ │ │ │ ├─IndexRangeScan_70(Build)|1.00|1|cop[tikv]|table:ds, index:PRIMARY(areaId, pAreaId, provinceCode)|time:588.3µs, loops:3, cop_task: {num: 1, max: 572.5µs, proc_keys: 1, rpc_num: 1, rpc_time: 566.8µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|range: decided by [eq(hollycas.t_touch_address.areaid, hollycas.tbl_call_record_sta.city) eq(hollycas.t_touch_address.pareaid, 71)], keep order:false|N/A|N/A|
| │ │ │ │ └─TableRowIDScan_71(Probe)|1.00|1|cop[tikv]|table:ds|time:361.2µs, loops:2, cop_task: {num: 1, max: 319.5µs, proc_keys: 1, rpc_num: 1, rpc_time: 313.7µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|keep order:false|N/A|N/A|
| │ │ │ └─IndexLookUp_65(Probe)|1.00|1|root||time:1.41ms, loops:2, index_task: {total_time: 383.9µs, fetch_handle: 377.6µs, build: 567ns, wait: 5.71µs}, table_task: {total_time: 2.21ms, num: 1, concurrency: 4}||8.98 KB|N/A|
| │ │ │ ├─IndexRangeScan_63(Build)|1.00|1|cop[tikv]|table:yf, index:PRIMARY(dept_id)|time:376.5µs, loops:3, cop_task: {num: 1, max: 350.3µs, proc_keys: 1, rpc_num: 1, rpc_time: 342.8µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:0s, loops:1}|range: decided by [eq(hollycas.tbl_app_dept.dept_id, hollycas.tbl_call_record_sta.parentid)], keep order:false|N/A|N/A|
| │ │ │ └─TableRowIDScan_64(Probe)|1.00|1|cop[tikv]|table:yf|time:343.4µs, loops:2, cop_task: {num: 1, max: 302.6µs, proc_keys: 1, tot_proc: 1ms, rpc_num: 1, rpc_time: 298.1µs, copr_cache_hit_ratio: 0.00}, tikv_task:{time:1ms, loops:1}|keep order:false|N/A|N/A|
| │ │ └─HashAgg_118(Probe)|676.60|4206|root||time:276.5ms, loops:8, PartialConcurrency:4, FinalConcurrency:4|group by:hollycas.tbl_order_info.task_id, hollycas.tbl_order_info.user_id, funcs:count(Column#242)->Column#84, funcs:firstrow(hollycas.tbl_order_info.task_id)->hollycas.tbl_order_info.task_id, funcs:firstrow(hollycas.tbl_order_info.user_id)->hollycas.tbl_order_info.user_id|310.6 KB|N/A|
| │ │ └─TableReader_119|676.60|4388|root||time:272.8ms, loops:4, cop_task: {num: 9, max: 272.9ms, min: 174.3ms, avg: 233.5ms, p95: 272.9ms, max_proc_keys: 438458, p95_proc_keys: 438458, tot_proc: 2.1s, rpc_num: 9, rpc_time: 2.1s, copr_cache_hit_ratio: 0.00}|data:HashAgg_112|308.7 KB|N/A|
| │ │ └─HashAgg_112|676.60|4388|cop[tikv]||tikv_task:{proc max:273ms, min:174ms, p80:267ms, p95:273ms, iters:3190, tasks:9}|group by:hollycas.tbl_order_info.task_id, hollycas.tbl_order_info.user_id, funcs:count(1)->Column#242|N/A|N/A|
| │ │ └─Selection_117|57698.19|62876|cop[tikv]||tikv_task:{proc max:267ms, min:166ms, p80:247ms, p95:267ms, iters:3190, tasks:9}|eq(hollycas.tbl_order_info.order_status, "2")|N/A|N/A|
| │ │ └─TableFullScan_116|3261929.00|3261785|cop[tikv]|table:t1|tikv_task:{proc max:246ms, min:152ms, p80:225ms, p95:246ms, iters:3190, tasks:9}|keep order:false|N/A|N/A|
| │ └─IndexLookUp_51(Probe)|1.00|18|root||time:3.35ms, loops:2, index_task: {total_time: 1.36ms, fetch_handle: 1.35ms, build: 611ns, wait: 1.3µs}, table_task: {total_time: 7.02ms, num: 1, concurrency: 4}||10.0 KB|N/A|
| │ ├─IndexRangeScan_49(Build)|1.00|18|cop[tikv]|table:t3, index:PRIMARY(user_id)|time:1.35ms, loops:3, cop_task: {num: 1, max: 1.32ms, proc_keys: 18, tot_proc: 1ms, rpc_num: 1, rpc_time: 1.32ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:1ms, loops:1}|range: decided by [eq(hollycas.tbl_app_user.user_id, hollycas.tbl_call_record_sta.usercode)], keep order:false|N/A|N/A|
| │ └─TableRowIDScan_50(Probe)|1.00|18|cop[tikv]|table:t3|time:1.13ms, loops:2, cop_task: {num: 1, max: 1.07ms, proc_keys: 18, tot_proc: 1ms, rpc_num: 1, rpc_time: 1.07ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:1ms, loops:1}|keep order:false|N/A|N/A|
| └─HashJoin_125(Probe)|44110.70|43596|root||time:61.9ms, loops:45, build_hash_table:{total:10.7ms, fetch:8.3ms, build:2.41ms}, probe:{concurrency:5, total:1.43s, max:287.4ms, probe:1.17s, fetch:266.2ms}|inner join, equal:[eq(hollycas.tbl_task_info.project_id, hollycas.tbl_project_info.project_id)]|222.3 KB|0 Bytes|
| ├─TableReader_131(Build)|9477.00|9477|root||time:8.25ms, loops:11, cop_task: {num: 1, max: 9.04ms, proc_keys: 9477, tot_proc: 8ms, rpc_num: 1, rpc_time: 9.04ms, copr_cache_hit_ratio: 0.00}|data:TableFullScan_130|250.4 KB|N/A|
| │ └─TableFullScan_130|9477.00|9477|cop[tikv]|table:bb|tikv_task:{time:5ms, loops:14}|keep order:false|N/A|N/A|
| └─TableReader_129(Probe)|44110.70|43596|root||time:53.3ms, loops:46, cop_task: {num: 1, max: 53.3ms, proc_keys: 46220, tot_proc: 46ms, rpc_num: 1, rpc_time: 53.3ms, copr_cache_hit_ratio: 0.00}|data:Selection_128|2.69 MB|N/A|
| └─Selection_128|44110.70|43596|cop[tikv]||tikv_task:{time:42ms, loops:50}|not(isnull(hollycas.tbl_task_info.project_id))|N/A|N/A|
| └─TableFullScan_127|46222.00|46220|cop[tikv]|table:aa|tikv_task:{time:41ms, loops:50}|keep order:false|N/A|N/A|
有warning，warning如下：
|Level|Code|Message|
|---|---|---|
|Warning|1815|No available path for table hollycas.tbl_order_info with the store type tiflash of the hint /*+ read_from_storage */, please check the status of the table replica and variable value of tidb_isolation_read_engines(map[0:{} 1:{} 2:{}])|
|Warning|1815|No available path for table hollycas.tbl_task_info with the store type tiflash of the hint /*+ read_from_storage */, please check the status of the table replica and variable value of tidb_isolation_read_engines(map[0:{} 1:{} 2:{}])|
|Warning|1815|No available path for table hollycas.tbl_project_info with the store type tiflash of the hint /*+ read_from_storage */, please check the status of the table replica and variable value of tidb_isolation_read_engines(map[0:{} 1:{} 2:{}])|
|Warning|1815|There are no matching table names for (b, t, t5) in optimizer hint /*+ HASH_JOIN(b, t, t4, t5) */ or /*+ TIDB_HJ(b, t, t4, t5) */. Maybe you can use the table alias name|
若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。
