tidb多表关联where后增加条件导致性能急剧下降

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

【TiDB 版本】
4.0.0
【问题描述】

未增加where筛选条件 2-3s

id	estRows	actRows	task	access object	execution info	operator info	memory	disk
Projection_55	50.00	50	root		time:863.567087ms, loops:2, Concurrency:OFF	h3_ware.t_ware_group_base_info.ware_code, h3_ware.t_ware_group_base_info.ware_name, h3_ware.t_ware_platform_base_info.ware_nickname, h3_ware.t_ware_platform_base_info.ware_spec, h3_ware.t_ware_platform_base_info.generic_name, h3_basic_setting.t_basic_setting_production_cp.production_cp_name, h3_basic_setting.t_basic_setting_measurement_unit_record.measurement_unit_name, h3_ware.t_ware_group_base_info.production_company_used_name, h3_ware.t_ware_platform_base_info.file_no, h3_price.t_price_group.price_group_code, h3_price.t_price_group.price_group_name, Column#151, Column#152, Column#153	17.421875 KB	N/A
└─Limit_58	50.00	50	root		time:863.544748ms, loops:2	offset:0, count:50	N/A	N/A
  └─HashJoin_59	50.00	50	root		time:863.543771ms, loops:1, Concurrency:5, probe collision:0, build:11.48µs	left outer join, equal:[eq(Column#150, h3_price.t_price_group.company_id) eq(Column#300, Column#301)]	9.81640625 KB	0 Bytes
    ├─Projection_288(Build)	0.08	19	root		time:4.993087ms, loops:2, Concurrency:OFF	h3_price.t_price_group.company_id, h3_price.t_price_group.price_group_code, h3_price.t_price_group.price_group_name, cast(h3_price.t_price_group.price_group_code, double BINARY)->Column#301	1.9453125 KB	N/A
    │ └─IndexLookUp_294	0.08	19	root		time:4.970603ms, loops:2, rpc num: 1, rpc time:2.086463ms, proc keys:19		10.2421875 KB	N/A
    │   ├─IndexRangeScan_292(Build)	0.08	19	cop[tikv]	table:t10, index:uk_group_price_code(group_id, price_group_code)	time:0s, loops:1	range:[100120512,100120512], keep order:false, stats:pseudo	N/A	N/A
    │   └─TableRowIDScan_293(Probe)	0.08	19	cop[tikv]	table:t10	time:0s, loops:1	keep order:false, stats:pseudo	N/A	N/A
    └─Projection_61(Probe)	50.00	50	root		time:862.833581ms, loops:2, Concurrency:OFF	Column#150, Column#151, Column#152, Column#153, h3_ware.t_ware_group_base_info.ware_code, h3_ware.t_ware_group_base_info.production_company_used_name, h3_ware.t_ware_group_base_info.ware_name, h3_ware.t_ware_platform_base_info.generic_name, h3_ware.t_ware_platform_base_info.ware_nickname, h3_ware.t_ware_platform_base_info.ware_spec, h3_ware.t_ware_platform_base_info.file_no, h3_basic_setting.t_basic_setting_measurement_unit_record.measurement_unit_name, h3_basic_setting.t_basic_setting_production_cp.production_cp_name, cast(Column#147, double BINARY)->Column#300	17.375 KB	N/A
      └─Limit_64	50.00	50	root		time:862.809572ms, loops:2	offset:0, count:50	N/A	N/A
        └─HashJoin_66	50.00	50	root		time:862.808188ms, loops:1, Concurrency:5, probe collision:0, build:44.238µs	left outer join, equal:[eq(h3_ware.t_ware_platform_base_info.dosage_form_code, h3_basic_setting.t_basic_setting_dosage_form_record.dosage_form_code)]	167.51171875 KB	0 Bytes
          ├─Limit_69(Build)	50.00	50	root		time:862.091298ms, loops:2	offset:0, count:50	N/A	N/A
          │ └─IndexMergeJoin_76	50.00	50	root		time:862.08841ms, loops:1, Concurrency:4	left outer join, inner:TableReader_74, outer key:h3_ware.t_ware_platform_base_info.production_cp_id, inner key:h3_basic_setting.t_basic_setting_production_cp.production_cp_id	15.666015625 KB	N/A
          │   ├─Limit_83(Build)	50.00	50	root		time:710.346456ms, loops:3	offset:0, count:50	N/A	N/A
          │   │ └─HashJoin_94	50.00	50	root		time:710.342082ms, loops:1, Concurrency:5, probe collision:0, build:12.638µs	left outer join, equal:[eq(h3_ware.t_ware_platform_base_info.measurement_unit_id, h3_basic_setting.t_basic_setting_measurement_unit_record.measurement_unit_id)]	159.51953125 KB	0 Bytes
          │   │   ├─Limit_97(Build)	50.00	50	root		time:661.331389ms, loops:2	offset:0, count:50	N/A	N/A
          │   │   │ └─IndexMergeJoin_104	50.00	50	root		time:661.328267ms, loops:1, Concurrency:4	left outer join, inner:TableReader_102, outer key:h3_ware.t_ware_group_base_info.ware_platform_id, inner key:h3_ware.t_ware_platform_base_info.ware_platform_id	90.142578125 KB	N/A
          │   │   │   ├─Limit_111(Build)	50.00	50	root		time:559.298778ms, loops:3	offset:0, count:50	N/A	N/A
          │   │   │   │ └─IndexMergeJoin_120	50.00	50	root		time:559.296556ms, loops:1, Concurrency:4	left outer join, inner:TableReader_118, outer key:Column#148, inner key:h3_ware.t_ware_group_base_info.ware_inside_code	4.3173828125 KB	N/A
          │   │   │   │   ├─Limit_127(Build)	50.00	50	root		time:494.102327ms, loops:3	offset:0, count:50	N/A	N/A
          │   │   │   │   │ └─Union_128	50.00	50	root		time:494.094612ms, loops:1		N/A	N/A
          │   │   │   │   │   ├─Projection_129	50.00	0	root		time:865.604964ms, loops:1, Concurrency:OFF	h3_price.t_price_group_ware_detail.price_group_id, h3_price.t_price_group_ware_detail.ware_inside_code, h3_price.t_price_group_ware_detail.company_id, h3_price.t_price_group_ware_detail.sale_price, h3_price.t_price_group_ware_detail.member_price, 零售低于进价->Column#153	2.5341796875 KB	N/A
          │   │   │   │   │   │ └─Limit_132	50.00	0	root		time:865.59905ms, loops:1	offset:0, count:50	N/A	N/A
          │   │   │   │   │   │   └─IndexHashJoin_155	50.00	0	root		time:865.598324ms, loops:1, Concurrency:4	inner join, inner:IndexLookUp_147, outer key:h3_ware.t_ware_company_base_info.company_id, inner key:h3_price.t_price_group_ware_detail.company_id, other cond:eq(h3_price.t_price_group_ware_detail.ware_inside_code, h3_ware.t_ware_company_base_info.ware_inside_code), lt(h3_price.t_price_group_ware_detail.sale_price, h3_ware.t_ware_company_base_info.recent_purchase_price)	20.14983081817627 MB	N/A
          │   │   │   │   │   │     ├─IndexLookUp_177(Build)	8.56	5254	root		time:29.436832ms, loops:11, rpc num: 1, rpc time:8.760155ms, proc keys:5254		261.859375 KB	N/A
          │   │   │   │   │   │     │ ├─IndexRangeScan_175(Build)	8.56	5254	cop[tikv]	table:t44, index:uk_g_ware(group_id, company_id, ware_inside_code)	time:6ms, loops:10	range:[100120512,100120512], keep order:false	N/A	N/A
          │   │   │   │   │   │     │ └─TableRowIDScan_176(Probe)	8.56	5254	cop[tikv]	table:t44	proc max:2ms, min:0s, p80:2ms, p95:2ms, iters:34, tasks:7	keep order:false	N/A	N/A
          │   │   │   │   │   │     └─IndexLookUp_147(Probe)	5.84	365116	root		time:2.555721723s, loops:364, rpc num: 4, rpc max:348.078222ms, min:89.147139ms, avg:177.237371ms, p80:348.078222ms, p95:348.078222ms, proc keys max:91279, p95:91279		4.647117614746094 MB	N/A
          │   │   │   │   │   │       ├─IndexRangeScan_145(Build)	5.84	365116	cop[tikv]	table:t1, index:uk_ware_is_delete(group_id, company_id, price_group_id, ware_inside_code, is_delete)	proc max:157ms, min:60ms, p80:157ms, p95:157ms, iters:376, tasks:4	range: decided by [eq(h3_price.t_price_group_ware_detail.company_id, h3_ware.t_ware_company_base_info.company_id) eq(h3_price.t_price_group_ware_detail.group_id, 100120512)], keep order:false	N/A	N/A
          │   │   │   │   │   │       └─TableRowIDScan_146(Probe)	5.84	365116	cop[tikv]	table:t1	proc max:58ms, min:2ms, p80:24ms, p95:38ms, iters:516, tasks:32	keep order:false	N/A	N/A
          │   │   │   │   │   ├─Projection_190	23.09	50	root		time:494.04929ms, loops:2, Concurrency:OFF	h3_price.t_price_group_ware_detail.price_group_id, h3_price.t_price_group_ware_detail.ware_inside_code, h3_price.t_price_group_ware_detail.company_id, h3_price.t_price_group_ware_detail.sale_price, h3_price.t_price_group_ware_detail.member_price, 零售价为0->Column#153	3.041015625 KB	N/A
          │   │   │   │   │   │ └─Limit_192	23.09	50	root		time:494.030848ms, loops:2	offset:0, count:50	N/A	N/A
          │   │   │   │   │   │   └─IndexLookUp_202	23.09	50	root		time:494.029669ms, loops:1, rpc num: 1, rpc time:308.145979ms, proc keys:91281		2.31341552734375 MB	N/A
          │   │   │   │   │   │     ├─IndexRangeScan_198(Build)	109.07	91281	cop[tikv]	table:t1, index:uk_ware_is_delete(group_id, company_id, price_group_id, ware_inside_code, is_delete)	time:228ms, loops:94	range:[100120512,100120512], keep order:false	N/A	N/A
          │   │   │   │   │   │     └─Limit_201(Probe)	23.09	358	cop[tikv]		proc max:5ms, min:0s, p80:3ms, p95:5ms, iters:72, tasks:13	offset:0, count:50	N/A	N/A
          │   │   │   │   │   │       └─Selection_200	23.09	1747	cop[tikv]		proc max:5ms, min:0s, p80:3ms, p95:5ms, iters:72, tasks:13	eq(h3_price.t_price_group_ware_detail.sale_price, 0)	N/A	N/A
          │   │   │   │   │   │         └─TableRowIDScan_199	109.07	21248	cop[tikv]	table:t1	proc max:5ms, min:0s, p80:3ms, p95:5ms, iters:72, tasks:13	keep order:false	N/A	N/A
          │   │   │   │   │   └─Projection_210	50.00	0	root		time:865.549611ms, loops:1, Concurrency:OFF	h3_price.t_price_group_ware_detail.price_group_id, h3_price.t_price_group_ware_detail.ware_inside_code, h3_price.t_price_group_ware_detail.company_id, h3_price.t_price_group_ware_detail.sale_price, h3_price.t_price_group_ware_detail.member_price, 零售高于进价100倍->Column#153	2.5341796875 KB	N/A
          │   │   │   │   │     └─Limit_213	50.00	0	root		time:865.545441ms, loops:1	offset:0, count:50	N/A	N/A
          │   │   │   │   │       └─IndexHashJoin_236	50.00	0	root		time:865.544922ms, loops:1, Concurrency:4	inner join, inner:IndexLookUp_228, outer key:h3_ware.t_ware_company_base_info.company_id, inner key:h3_price.t_price_group_ware_detail.company_id, other cond:eq(h3_price.t_price_group_ware_detail.ware_inside_code, h3_ware.t_ware_company_base_info.ware_inside_code), gt(h3_price.t_price_group_ware_detail.sale_price, mul(100, h3_ware.t_ware_company_base_info.recent_purchase_price))	25.059406280517578 MB	N/A
          │   │   │   │   │         ├─IndexLookUp_258(Build)	8.56	5254	root		time:40.084277ms, loops:11, rpc num: 1, rpc time:8.225283ms, proc keys:5254		204.921875 KB	N/A
          │   │   │   │   │         │ ├─IndexRangeScan_256(Build)	8.56	5254	cop[tikv]	table:t44, index:uk_g_ware(group_id, company_id, ware_inside_code)	time:6ms, loops:10	range:[100120512,100120512], keep order:false	N/A	N/A
          │   │   │   │   │         │ └─TableRowIDScan_257(Probe)	8.56	5254	cop[tikv]	table:t44	proc max:2ms, min:0s, p80:2ms, p95:2ms, iters:34, tasks:7	keep order:false	N/A	N/A
          │   │   │   │   │         └─IndexLookUp_228(Probe)	5.84	456395	root		time:2.707185339s, loops:455, rpc num: 5, rpc max:241.029726ms, min:69.311165ms, avg:157.214042ms, p80:241.029726ms, p95:241.029726ms, proc keys max:91279, p95:91279		5.351097106933594 MB	N/A
          │   │   │   │   │           ├─IndexRangeScan_226(Build)	5.84	456395	cop[tikv]	table:t1, index:uk_ware_is_delete(group_id, company_id, price_group_id, ware_inside_code, is_delete)	proc max:70ms, min:58ms, p80:70ms, p95:70ms, iters:470, tasks:5	range: decided by [eq(h3_price.t_price_group_ware_detail.company_id, h3_ware.t_ware_company_base_info.company_id) eq(h3_price.t_price_group_ware_detail.group_id, 100120512)], keep order:false	N/A	N/A
          │   │   │   │   │           └─TableRowIDScan_227(Probe)	5.84	456395	cop[tikv]	table:t1	proc max:72ms, min:1ms, p80:27ms, p95:57ms, iters:645, tasks:40	keep order:false	N/A	N/A
          │   │   │   │   └─TableReader_118(Probe)	0.55	50	root		time:64.685008ms, loops:2, rpc num: 1, rpc time:60.780111ms, proc keys:50	data:Selection_117	N/A	N/A
          │   │   │   │     └─Selection_117	0.55	50	cop[tikv]		time:1ms, loops:2	eq(100120512, h3_ware.t_ware_group_base_info.group_id)	N/A	N/A
          │   │   │   │       └─TableRangeScan_116	1.00	50	cop[tikv]	table:t3	time:1ms, loops:2	range: decided by [Column#148], keep order:true	N/A	N/A
          │   │   │   └─TableReader_102(Probe)	1.00	50	root		time:93.890829ms, loops:2, rpc num: 1, rpc time:92.566654ms, proc keys:50	data:TableRangeScan_101	N/A	N/A
          │   │   │     └─TableRangeScan_101	1.00	50	cop[tikv]	table:t4	time:0s, loops:2	range: decided by [h3_ware.t_ware_group_base_info.ware_platform_id], keep order:true	N/A	N/A
          │   │   └─TableReader_283(Probe)	66.00	66	root		time:4.934105ms, loops:3, rpc num: 1, rpc time:5.293239ms, proc keys:66	data:TableFullScan_282	1.4384765625 KB	N/A
          │   │     └─TableFullScan_282	66.00	66	cop[tikv]	table:t5	time:0s, loops:2	keep order:false, stats:pseudo	N/A	N/A
          │   └─TableReader_74(Probe)	1.00	1	root		time:50.085971ms, loops:2, rpc num: 1, rpc time:20.344474ms, proc keys:1	data:TableRangeScan_73	N/A	N/A
          │     └─TableRangeScan_73	1.00	1	cop[tikv]	table:t6	time:1ms, loops:1	range: decided by [h3_ware.t_ware_platform_base_info.production_cp_id], keep order:true	N/A	N/A
          └─TableReader_287(Probe)	130.00	130	root		time:1.362711ms, loops:4, rpc num: 1, rpc time:1.608636ms, proc keys:130	data:TableRangeScan_286	1.654296875 KB	N/A
            └─TableRangeScan_286	130.00	130	cop[tikv]	table:t9	time:0s, loops:3	range:[0,+inf], keep order:false, stats:pseudo	N/A	N/A

修改SQL内容:

增加where筛选条件 100s+

id	estRows	actRows	task	access object	execution info	operator info	memory	disk
Projection_37	4.53	0	root		time:3m31.132105995s, loops:1, Concurrency:OFF	h3_ware.t_ware_group_base_info.ware_code, h3_ware.t_ware_group_base_info.ware_name, h3_ware.t_ware_platform_base_info.ware_nickname, h3_ware.t_ware_platform_base_info.ware_spec, h3_ware.t_ware_platform_base_info.generic_name, h3_basic_setting.t_basic_setting_production_cp.production_cp_name, h3_basic_setting.t_basic_setting_measurement_unit_record.measurement_unit_name, h3_ware.t_ware_group_base_info.production_company_used_name, h3_ware.t_ware_platform_base_info.file_no, h3_price.t_price_group.price_group_code, h3_price.t_price_group.price_group_name, Column#151, Column#152, Column#153	9.392578125 KB	N/A
└─Limit_40	4.53	0	root		time:3m31.132104645s, loops:1	offset:0, count:50	N/A	N/A
  └─HashJoin_41	4.53	0	root		time:3m31.132103116s, loops:1, Concurrency:5, probe collision:0, build:38.809µs	inner join, equal:[eq(Column#150, h3_price.t_price_group.company_id) eq(Column#300, Column#301)]	9.81640625 KB	0 Bytes
    ├─Projection_220(Build)	0.08	19	root		time:2.95557ms, loops:2, Concurrency:OFF	h3_price.t_price_group.company_id, h3_price.t_price_group.price_group_code, h3_price.t_price_group.price_group_name, cast(h3_price.t_price_group.price_group_code, double BINARY)->Column#301	1.9453125 KB	N/A
    │ └─IndexLookUp_226	0.08	19	root		time:2.939544ms, loops:2, rpc num: 1, rpc time:1.508283ms, proc keys:19		10.2421875 KB	N/A
    │   ├─IndexRangeScan_224(Build)	0.08	19	cop[tikv]	table:t10, index:uk_group_price_code(group_id, price_group_code)	time:0s, loops:1	range:[100120512,100120512], keep order:false, stats:pseudo	N/A	N/A
    │   └─TableRowIDScan_225(Probe)	0.08	19	cop[tikv]	table:t10	time:1ms, loops:1	keep order:false, stats:pseudo	N/A	N/A
    └─Projection_43(Probe)	163.59	90869	root		time:3m31.131563224s, loops:90, Concurrency:OFF	Column#150, Column#151, Column#152, Column#153, h3_ware.t_ware_group_base_info.ware_code, h3_ware.t_ware_group_base_info.production_company_used_name, h3_ware.t_ware_group_base_info.ware_name, h3_ware.t_ware_platform_base_info.generic_name, h3_ware.t_ware_platform_base_info.ware_nickname, h3_ware.t_ware_platform_base_info.ware_spec, h3_ware.t_ware_platform_base_info.file_no, h3_basic_setting.t_basic_setting_measurement_unit_record.measurement_unit_name, h3_basic_setting.t_basic_setting_production_cp.production_cp_name, cast(Column#147, double BINARY)->Column#300	1.8929481506347656 MB	N/A
      └─HashJoin_44	163.59	90869	root		time:3m31.129698931s, loops:90, Concurrency:5, probe collision:0, build:68.797µs	left outer join, equal:[eq(h3_ware.t_ware_platform_base_info.dosage_form_code, h3_basic_setting.t_basic_setting_dosage_form_record.dosage_form_code)]	12.23046875 KB	0 Bytes
        ├─TableReader_219(Build)	130.00	130	root		time:1.53221ms, loops:2, rpc num: 1, rpc time:1.720659ms, proc keys:130	data:TableRangeScan_218	1.654296875 KB	N/A
        │ └─TableRangeScan_218	130.00	130	cop[tikv]	table:t9	time:0s, loops:3	range:[0,+inf], keep order:false, stats:pseudo	N/A	N/A
        └─IndexMergeJoin_52(Probe)	130.87	90869	root		time:3m30.967419441s, loops:90, Concurrency:4	left outer join, inner:TableReader_50, outer key:h3_ware.t_ware_platform_base_info.production_cp_id, inner key:h3_basic_setting.t_basic_setting_production_cp.production_cp_id	22.681004524230957 MB	N/A
          ├─HashJoin_66(Build)	130.87	90869	root		time:3m30.408962061s, loops:92, Concurrency:5, probe collision:0, build:30.3µs	left outer join, equal:[eq(h3_ware.t_ware_platform_base_info.measurement_unit_id, h3_basic_setting.t_basic_setting_measurement_unit_record.measurement_unit_id)]	20.46484375 KB	0 Bytes
          │ ├─TableReader_215(Build)	66.00	66	root		time:1.798741ms, loops:2, rpc num: 1, rpc time:2.981771ms, proc keys:66	data:TableFullScan_214	1.4404296875 KB	N/A
          │ │ └─TableFullScan_214	66.00	66	cop[tikv]	table:t5	time:1ms, loops:2	keep order:false, stats:pseudo	N/A	N/A
          │ └─IndexMergeJoin_75(Probe)	104.69	90869	root		time:3m30.991370586s, loops:92, Concurrency:4	left outer join, inner:TableReader_73, outer key:h3_ware.t_ware_group_base_info.ware_platform_id, inner key:h3_ware.t_ware_platform_base_info.ware_platform_id	14.334736824035645 MB	N/A
          │   ├─IndexMergeJoin_88(Build)	104.69	90869	root		time:3m29.044791542s, loops:93, Concurrency:4	inner join, inner:TableReader_86, outer key:Column#148, inner key:h3_ware.t_ware_group_base_info.ware_inside_code	5.931244850158691 MB	N/A
          │   │ ├─Union_93(Build)	104.69	90869	root		time:3m30.899251488s, loops:97		N/A	N/A
          │   │ │ ├─Projection_94	47.34	570	root		time:3m18.697157743s, loops:5, Concurrency:OFF	h3_price.t_price_group_ware_detail.price_group_id, h3_price.t_price_group_ware_detail.ware_inside_code, h3_price.t_price_group_ware_detail.company_id, h3_price.t_price_group_ware_detail.sale_price, h3_price.t_price_group_ware_detail.member_price, 零售低于进价->Column#153	15.859375 KB	N/A
          │   │ │ │ └─IndexHashJoin_119	47.34	570	root		time:3m18.697070788s, loops:5, Concurrency:4	inner join, inner:IndexLookUp_110, outer key:h3_ware.t_ware_company_base_info.company_id, inner key:h3_price.t_price_group_ware_detail.company_id, other cond:eq(h3_price.t_price_group_ware_detail.ware_inside_code, h3_ware.t_ware_company_base_info.ware_inside_code), lt(h3_price.t_price_group_ware_detail.sale_price, h3_ware.t_ware_company_base_info.recent_purchase_price)	35.01213550567627 MB	N/A
          │   │ │ │   ├─IndexLookUp_143(Build)	18.71	5254	root		time:28.043952ms, loops:11, rpc num: 1, rpc time:8.359045ms, proc keys:5254		261.859375 KB	N/A
          │   │ │ │   │ ├─IndexRangeScan_141(Build)	18.71	5254	cop[tikv]	table:t44, index:uk_g_ware(group_id, company_id, ware_inside_code)	time:6ms, loops:10	range:[100120512,100120512], keep order:false	N/A	N/A
          │   │ │ │   │ └─TableRowIDScan_142(Probe)	18.71	5254	cop[tikv]	table:t44	proc max:3ms, min:0s, p80:1ms, p95:3ms, iters:34, tasks:7	keep order:false	N/A	N/A
          │   │ │ │   └─IndexLookUp_110(Probe)	2.53	638955	root		time:3.614408284s, loops:637, rpc num: 7, rpc max:269.307684ms, min:102.702257ms, avg:166.38891ms, p80:177.070706ms, p95:269.307684ms, proc keys max:91281, p95:91281		4.201995849609375 MB	N/A
          │   │ │ │     ├─Selection_109(Build)	2.53	638955	cop[tikv]		proc max:139ms, min:66ms, p80:101ms, p95:139ms, iters:658, tasks:7	not(isnull(h3_price.t_price_group_ware_detail.company_id)), not(isnull(h3_price.t_price_group_ware_detail.ware_inside_code))	N/A	N/A
          │   │ │ │     │ └─IndexRangeScan_107	2.53	638955	cop[tikv]	table:t1, index:uk_ware_is_delete(group_id, company_id, price_group_id, ware_inside_code, is_delete)	proc max:132ms, min:55ms, p80:92ms, p95:132ms, iters:658, tasks:7	range: decided by [eq(h3_price.t_price_group_ware_detail.company_id, h3_ware.t_ware_company_base_info.company_id) eq(h3_price.t_price_group_ware_detail.group_id, 100120512)], keep order:false	N/A	N/A
          │   │ │ │     └─TableRowIDScan_108(Probe)	2.53	638955	cop[tikv]	table:t1	proc max:141ms, min:1ms, p80:29ms, p95:53ms, iters:903, tasks:56	keep order:false	N/A	N/A
          │   │ │ ├─Projection_144	10.01	18551	root		time:523.437172ms, loops:20, Concurrency:OFF	h3_price.t_price_group_ware_detail.price_group_id, h3_price.t_price_group_ware_detail.ware_inside_code, h3_price.t_price_group_ware_detail.company_id, h3_price.t_price_group_ware_detail.sale_price, h3_price.t_price_group_ware_detail.member_price, 零售价为0->Column#153	56.90625 KB	N/A
          │   │ │ │ └─IndexLookUp_152	10.01	18551	root		time:522.609ms, loops:20, rpc num: 1, rpc time:98.549966ms, proc keys:91281		2.760894775390625 MB	N/A
          │   │ │ │   ├─Selection_150(Build)	47.26	91281	cop[tikv]		time:83ms, loops:94	not(isnull(h3_price.t_price_group_ware_detail.ware_inside_code))	N/A	N/A
          │   │ │ │   │ └─IndexRangeScan_148	47.26	91281	cop[tikv]	table:t1, index:uk_ware_is_delete(group_id, company_id, price_group_id, ware_inside_code, is_delete)	time:82ms, loops:94	range:[100120512 -inf,100120512 +inf], keep order:false	N/A	N/A
          │   │ │ │   └─Selection_151(Probe)	10.01	18551	cop[tikv]		proc max:22ms, min:1ms, p80:22ms, p95:22ms, iters:129, tasks:8	eq(h3_price.t_price_group_ware_detail.sale_price, 0)	N/A	N/A
          │   │ │ │     └─TableRowIDScan_149	47.26	91281	cop[tikv]	table:t1	proc max:21ms, min:1ms, p80:20ms, p95:21ms, iters:129, tasks:8	keep order:false	N/A	N/A
          │   │ │ └─Projection_153	47.34	71748	root		time:3m30.969597735s, loops:73, Concurrency:OFF	h3_price.t_price_group_ware_detail.price_group_id, h3_price.t_price_group_ware_detail.ware_inside_code, h3_price.t_price_group_ware_detail.company_id, h3_price.t_price_group_ware_detail.sale_price, h3_price.t_price_group_ware_detail.member_price, 零售高于进价100倍->Column#153	47.421875 KB	N/A
          │   │ │   └─IndexHashJoin_178	47.34	71748	root		time:3m30.894567389s, loops:73, Concurrency:4	inner join, inner:IndexLookUp_169, outer key:h3_ware.t_ware_company_base_info.company_id, inner key:h3_price.t_price_group_ware_detail.company_id, other cond:eq(h3_price.t_price_group_ware_detail.ware_inside_code, h3_ware.t_ware_company_base_info.ware_inside_code), gt(h3_price.t_price_group_ware_detail.sale_price, mul(100, h3_ware.t_ware_company_base_info.recent_purchase_price))	35.01213550567627 MB	N/A
          │   │ │     ├─IndexLookUp_202(Build)	18.71	5254	root		time:28.041958ms, loops:11, rpc num: 1, rpc time:7.195365ms, proc keys:5254		261.859375 KB	N/A
          │   │ │     │ ├─IndexRangeScan_200(Build)	18.71	5254	cop[tikv]	table:t44, index:uk_g_ware(group_id, company_id, ware_inside_code)	time:4ms, loops:10	range:[100120512,100120512], keep order:false	N/A	N/A
          │   │ │     │ └─TableRowIDScan_201(Probe)	18.71	5254	cop[tikv]	table:t44	proc max:2ms, min:0s, p80:2ms, p95:2ms, iters:34, tasks:7	keep order:false	N/A	N/A
          │   │ │     └─IndexLookUp_169(Probe)	2.53	638955	root		time:3.403755407s, loops:637, rpc num: 7, rpc max:311.790294ms, min:88.248497ms, avg:138.238129ms, p80:143.667929ms, p95:311.790294ms, proc keys max:91281, p95:91281		4.251502990722656 MB	N/A
          │   │ │       ├─Selection_168(Build)	2.53	638955	cop[tikv]		proc max:159ms, min:64ms, p80:77ms, p95:159ms, iters:658, tasks:7	not(isnull(h3_price.t_price_group_ware_detail.company_id)), not(isnull(h3_price.t_price_group_ware_detail.ware_inside_code))	N/A	N/A
          │   │ │       │ └─IndexRangeScan_166	2.53	638955	cop[tikv]	table:t1, index:uk_ware_is_delete(group_id, company_id, price_group_id, ware_inside_code, is_delete)	proc max:148ms, min:55ms, p80:67ms, p95:148ms, iters:658, tasks:7	range: decided by [eq(h3_price.t_price_group_ware_detail.company_id, h3_ware.t_ware_company_base_info.company_id) eq(h3_price.t_price_group_ware_detail.group_id, 100120512)], keep order:false	N/A	N/A
          │   │ │       └─TableRowIDScan_167(Probe)	2.53	638955	cop[tikv]	table:t1	proc max:50ms, min:2ms, p80:21ms, p95:32ms, iters:903, tasks:56	keep order:false	N/A	N/A
          │   │ └─TableReader_86(Probe)	0.55	27023	root		time:2.756761189s, loops:42, rpc num: 12, rpc max:583.415415ms, min:17.795844ms, avg:220.836697ms, p80:379.460707ms, p95:583.415415ms, proc keys max:4580, p95:4580	data:Selection_85	N/A	N/A
          │   │   └─Selection_85	0.55	27023	cop[tikv]		proc max:17ms, min:2ms, p80:12ms, p95:17ms, iters:82, tasks:12	eq(h3_ware.t_ware_group_base_info.group_id, 100120512)	N/A	N/A
          │   │     └─TableRangeScan_84	1.00	27023	cop[tikv]	table:t3	proc max:16ms, min:2ms, p80:12ms, p95:16ms, iters:82, tasks:12	range: decided by [Column#148], keep order:true	N/A	N/A
          │   └─TableReader_73(Probe)	1.00	19682	root		time:7.723275515s, loops:132, rpc num: 64, rpc max:411.815332ms, min:1.54839ms, avg:99.2719ms, p80:159.456547ms, p95:378.272536ms, proc keys max:5085, p95:568	data:TableRangeScan_72	N/A	N/A
          │     └─TableRangeScan_72	1.00	19682	cop[tikv]	table:t4	proc max:11ms, min:0s, p80:1ms, p95:2ms, iters:223, tasks:64	range: decided by [h3_ware.t_ware_group_base_info.ware_platform_id], keep order:true	N/A	N/A
          └─TableReader_50(Probe)	1.00	155	root		time:6.685667615s, loops:128, rpc num: 64, rpc max:201.625947ms, min:697.843µs, avg:74.467047ms, p80:119.023478ms, p95:160.467722ms, proc keys max:28, p95:11	data:TableRangeScan_49	N/A	N/A
            └─TableRangeScan_49	1.00	155	cop[tikv]	table:t6	proc max:1ms, min:0s, p80:0s, p95:1ms, iters:64, tasks:64	range: decided by [h3_ware.t_ware_platform_base_info.production_cp_id], keep order:true	N/A	N/A
1 个赞

加上where条件之后,left join 变成 inner join了

1 个赞

IndexMergeJoin 合并Join的执行时间相差很大

建议你把where 条件在加内部,不是聚合以后在加条件筛选,这样可以减少聚合和合并的结果集的大小

1 个赞

:joy:我知道呀,你回复错人了

1 个赞

:slight_smile: 我以为是同一个小伙伴…

1 个赞

可以尝试2楼的谓词推入的方法。

1 个赞
      │   │   └─Selection_85  0.55  27023 cop[tikv]   proc max:17ms, min:2ms, p80:12ms, p95:17ms, iters:82, tasks:12  eq(h3_ware.t_ware_group_base_info.group_id, 100120512)  N/A N/A 

这里最下面的 where 条件 没有走索引。麻烦看一下索引的情况。

1 个赞

如果表的新增和修改比较频繁的话,没有及时的更新收集器,也会影响到索引或者其他的优化规则

可以先尝试更新下几个关联表收集,然后在试试

1 个赞

优化了这个关联没走索引的问题

1 个赞

这个是自动加上去做租户隔离的,加到内部子查询里SQL解析复杂程度太高

1 个赞

加上索引应该可以解决。

1 个赞