执行计划运行一段时间后未按预期运行

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

【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|

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

  1. warning 是说设置了 tidb_isolation_read_engines ,无法选择到 tiflash 吧,可以看下tidb是否配置了参数值为tidb,tikv,不会走tiflash; 另一个是没有对应的别名吧,是hint增加的不对,和这个sql应该没有关系吧。
  2. 两次的执行计划不一样,一次是hash join,一次是 index join。

  3. 可以使用 hint 绑定,或者 spm 绑定执行计划。
    https://docs.pingcap.com/zh/tidb/stable/sql-plan-management#执行计划管理-spm

tidb_isolation_read_engines的值为tikv, tiflash, tidb。我开启了 自动捕获绑定 和 自动演进绑定,还能做hint绑定或spm手工绑定执行计划吗?

  1. 已经使用 SPM 了吗? 目前是 v4.0.11 版本?
  2. 能发下 tiup cluster display 的信息吗?
  3. 请问是什么业务呢?
  4. 麻烦查看下 tidb_capture_plan_baselines 的值
  5. show %stmt_summary% 的值,多谢。

1、是的
MySQL [none]> select version();
±--------------------+
| version() |
±--------------------+
| 5.7.25-TiDB-v4.0.11 |
±--------------------+
1 row in set (0.00 sec)

2、tiup cluster display信息如下

3、是一个报表统计的查询业务

4、tidb_capture_plan_baselines的值显示如下:
MySQL [none]> show variables like ‘%tidb_capture_plan_baselines%’;
±----------------------------±------+
| Variable_name | Value |
±----------------------------±------+
| tidb_capture_plan_baselines | on |
±----------------------------±------+
1 row in set (0.18 sec)

5、MySQL [none]> show variables like ‘%stmt_summary%’;
±-----------------------------------±------+
| Variable_name | Value |
±-----------------------------------±------+
| tidb_enable_stmt_summary | 1 |
| tidb_stmt_summary_history_size | 24 |
| tidb_stmt_summary_internal_query | 0 |
| tidb_stmt_summary_max_sql_length | 4096 |
| tidb_stmt_summary_max_stmt_count | 200 |
| tidb_stmt_summary_refresh_interval | 1800 |
±-----------------------------------±------+
6 rows in set (0.00 sec)

  1. 麻烦查下 tidb_evolve_plan_baselines 参数值
  2. show BINDINGS where xx 看下这个sql是否绑定过,多谢。

1、MySQL [mysql]> show variables like ‘%tidb_evolve_plan_baselines%’;
±---------------------------±------+
| Variable_name | Value |
±---------------------------±------+
| tidb_evolve_plan_baselines | on |
±---------------------------±------+
1 row in set (0.26 sec)

2、应该是有绑定的,因为这个表没有sql指纹,这个sql也比较复杂,不太确定具体是哪个sql,类似的sql都有这个问题

请问下现在通过 SPM 可以解决你的问题吗?

不太清楚我的描述是否能让你理解:我的困惑在于,这个sql刚开始时,执行计划是正常的,也没有warning;运行一段时间后,执行计划变了(选择了更慢的执行计划),同时show warning的时候发现有一些警告信息。应该是这些警告信息导致了执行计划的改变,但对于这些警告信息,我不清楚应该如何处理和修复。

理解你的意思了,麻烦检查下上面 warning 信息中涉及到 的 hollycas.tbl_order_infohollycas.tbl_task_infohollycas.tbl_project_info 这三张表在 tiflash 中同步是否正常。

MySQL [mysql]> SELECT t.* FROM information_schema.tiflash_replica t WHERE TABLE_SCHEMA = ‘hollycas’ and table_name in (‘tbl_task_info’,‘tbl_order_info’,‘tbl_project_info’) order by table_name desc;
±-------------±-----------------±---------±--------------±----------------±----------±---------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
±-------------±-----------------±---------±--------------±----------------±----------±---------+
| hollycas | tbl_task_info | 6334 | 2 | | 1 | 1 |
| hollycas | tbl_project_info | 1826 | 2 | | 1 | 1 |
| hollycas | tbl_order_info | 3448 | 2 | | 1 | 1 |
±-------------±-----------------±---------±--------------±----------------±----------±---------+
3 rows in set (0.00 sec)

显示是正常的

对比两次不一样的执行计划,比较明显的一点就是原先正常的执行计划走到了 tiflash ,而新的有问题的执行计划没有走 tiflash ,所以还请麻烦确认下上面三张表能否通过 tiflash 正常访问到数据。

MySQL [hollycas]> set SESSION tidb_isolation_read_engines = “tidb,tiflash”;
Query OK, 0 rows affected (0.00 sec)

MySQL [hollycas]> select * from tbl_order_info limit 1;
±--------------------±----------------------------------±--------------±----------±-----------±--------±---------------------------------±-------------±---------±----------±----------±--------------------±------------±-------±-------------±--------------±------------------------------------±-----------±--------+
| order_id | task_id | serial_number | busi_code | product_id | dept_id | user_id | order_status | sys_code | resp_code | resp_desc | create_time | update_time | remark | request_time | response_time | record_id | order_type | sms_key |
±--------------------±----------------------------------±--------------±----------±-----------±--------±---------------------------------±-------------±---------±----------±----------±--------------------±------------±-------±-------------±--------------±------------------------------------±-----------±--------+
| 1308250000266735618 | 202009211049036713640142311149568 | 1304488xxx | YW06 | 64633755 | 71b3yvu | 7baa4935015c488191a74adfa563144a | 0 | CBS | NULL | NULL | 2020-09-22 11:41:20 | NULL | NULL | NULL | NULL | obs06216c48a7f143309d5e1d0d8800e7a0 | NULL | NULL |
±--------------------±----------------------------------±--------------±----------±-----------±--------±---------------------------------±-------------±---------±----------±----------±--------------------±------------±-------±-------------±--------------±------------------------------------±-----------±--------+
1 row in set (0.06 sec)

MySQL [hollycas]> select * from tbl_project_info limit 1;
±--------------------±-----------------------±-------------±---------------±-----±--------------------±--------------------±--------------------±----------±----------------±-----------------±------±-------------±-----------±----------±---------±-----±----------±---------±--------±------------±--------------------±-------------±-----------------±--------------±-----------±--------------------------------------------------------------------------------------±-----------±------------±----------±-------------------±-------------------±-----------±-------------------±------------±---------------±----------±----------+
| project_id | project_name | project_type | project_status | city | start_time | end_time | create_time | create_by | grab_time_limit | total_user_limit | price | max_call_num | allot_mode | tenant_id | is_index | icon | call_type | skill_id | calling | delete_flag | audit_time | audit_person | audit_reason | explicit_type | check_type | handle_results | order_type | parent_type | subscribe | leve1_project_type | leve2_project_type | is_encrypt | communicate_result | update_time | update_user_id | policy_id | anti_type |
±--------------------±-----------------------±-------------±---------------±-----±--------------------±--------------------±--------------------±----------±----------------±-----------------±------±-------------±-----------±----------±---------±-----±----------±---------±--------±------------±--------------------±-------------±-----------------±--------------±-----------±--------------------------------------------------------------------------------------±-----------±------------±----------±-------------------±-------------------±-----------±-------------------±------------±---------------±----------±----------+
| 2019072420716316160 | 地市营服混合vela | normal | audit | HBCU | 2019-07-24 00:00:00 | 2019-08-08 00:00:00 | 2019-07-24 20:33:17 | NULL | NULL | 0 | 1 | 1 | NULL | 0001 | NULL | NULL | NULL | NULL | 10018 | 1 | 2019-07-24 20:34:35 | 99999 | 测试测试0724 | 2 | 1 | intention#有意向,refuse#拒绝,agree#同意,noone#无人接听,noconn#无法接通 | | | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | 1 |
±--------------------±-----------------------±-------------±---------------±-----±--------------------±--------------------±--------------------±----------±----------------±-----------------±------±-------------±-----------±----------±---------±-----±----------±---------±--------±------------±--------------------±-------------±-----------------±--------------±-----------±--------------------------------------------------------------------------------------±-----------±------------±----------±-------------------±-------------------±-----------±-------------------±------------±---------------±----------±----------+
1 row in set (0.09 sec)

MySQL [hollycas]> select * from tbl_task_info limit 1;
±---------------------------------±----------------------------------------------------------------------------±----------±------------±-----±----------±--------------------±--------------------±--------------------±----------±----------------±-----------------±------±-------------±-----------±----------±---------±-----±----------±---------±--------±------------±--------------------±-------------±-------------±--------------±-----------±-----------±----------±--------------------±------------±-----------±------------±---------------±-------------±--------±-------+
| task_id | task_name | task_type | task_status | city | dept_name | start_time | end_time | create_time | create_by | grab_time_limit | total_user_limit | price | max_call_num | allot_mode | tenant_id | is_index | icon | call_type | skill_id | calling | delete_flag | audit_time | audit_person | audit_reason | explicit_type | check_type | order_type | subscribe | project_id | parent_type | is_encrypt | update_time | update_user_id | project_type | act_sub | act_sl |
±---------------------------------±----------------------------------------------------------------------------±----------±------------±-----±----------±--------------------±--------------------±--------------------±----------±----------------±-----------------±------±-------------±-----------±----------±---------±-----±----------±---------±--------±------------±--------------------±-------------±-------------±--------------±-----------±-----------±----------±--------------------±------------±-----------±------------±---------------±-------------±--------±-------+
| 000c2d293e054533a1b1861d4264cd53 | xxx_营销部_实体渠道_20190730_20190917174621 | normal | running | 0xxx | NULL | 2019-07-30 00:00:00 | 2019-10-16 00:00:00 | 2019-09-17 17:46:35 | Lxxx | | 0 | 1 | 1 | blend | 0001 | NULL | NULL | manual | | 10018 | 0 | 2019-09-17 17:48:50 | 李xx | | 2 | 1 | | 1 | 2019073009090381312 | | 0 | NULL | NULL | normal | NULL | NULL |
±---------------------------------±----------------------------------------------------------------------------±----------±------------±-----±----------±--------------------±--------------------±--------------------±----------±----------------±-----------------±------±-------------±-----------±----------±---------±-----±----------±---------±--------±------------±--------------------±-------------±-------------±--------------±-----------±-----------±----------±--------------------±------------±-----------±------------±---------------±-------------±--------±-------+
1 row in set (0.24 sec)

请问下 SQL 在执行计划发生变化期间,有对集群做过什么调整吗?

没有任何调整。我还做了一个测试:我把这个sql微调一下,执行计划会重新变为正常,隔天又出现warning了

1.上面这几张表每天的数据量变化大吗?可以看下统计信息健康度情况;
2.出现的 warning 内容和最上面的是一样的吗?另外 SQL 具体是怎么调整的。

1、变化是比较小的,tbl_task_info,tbl_order_info,tbl_project_info健康度分别是91、98、99。我把重新收集统计信息后,执行计划还是有warning;
2、是一样的,sql调整类似于这样 select a.col1 from table a where xxx;调整未select aa.col1 from table aa where xxx。

关键是,在各项配置和状态都正常的情况下,这个warning出现的真正原因是什么?

请问是否方便将上面这条 SQL 先解除下 sql binding ,然后再观察下执行是否正常,我这边看到有一个类似现象的 issue :https://github.com/pingcap/tidb/issues/20893