【 TiDB 使用环境】
生产：4.0.15 11 x 16core
测试：5.2.1 kv配置：3 x 4core
【概述】 场景 + 问题概述
explain 结果对比如下：
测试的explain结果：
Projection_8 1.00 1000 root time:2m14.9s, loops:2, Concurrency:OFF
└─TopN_11 1.00 1000 root time:2m14.9s, loops:2
└─HashAgg_37 1.00 2148 root time:2m14.9s, loops:6, partial_worker:{wall_time:2m14.89815157s, concurrency:5, task_num:349, tot_wait:11m14.099532635s, tot_exec:387.55835ms, tot_time:11m14.488336345s, max:2m14.898129291s, p95:2m14.898129291s}, final_worker:{wall_time:2m14.904073041s, concurrency:5, task_num:25, tot_wait:11m14.491842471s, tot_exec:18.321344ms, tot_time:11m14.510174812s, max:2m14.904056573s, p95:2m14.904056573s}
└─IndexLookUp_38 1.00 356541 root time:2m14.9s, loops:350, index_task: {total_time: 2m14s, fetch_handle: 4.41s, build: 403.6µs, wait: 2m9.6s}, table_task: {total_time: 2m14.9s, num: 228, concurrency: 1}
├─Selection_31(Build) 43.09 489103 cop[tikv] time:4.38s, loops:507, cop_task: {num: 42, max: 542.6ms, min: 15.9ms, avg: 104.6ms, p95: 233.4ms, max_proc_keys: 60771, p95_proc_keys: 27894, tot_proc: 3.89s, tot_wait: 264ms, rpc_num: 45, rpc_time: 4.39s, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 2ms}, tikv_task:{proc max:537ms, min:7ms, p80:129ms, p95:226ms, iters:670, tasks:42}, scan_detail: {total_process_keys: 490941, total_keys: 493644, rocksdb: {delete_skipped_count: 0, key_skipped_count: 490941, block: {cache_hit_count: 18731, read_count: 4158, read_byte: 157.2 MB}}}
│ └─IndexRangeScan_29 43086.11 490941 cop[tikv] table:dish_analysis_result, index:dim_date_brand(dim_pshop_id, date, brand_id) tikv_task:{proc max:535ms, min:7ms, p80:129ms, p95:225ms, iters:670, tasks:42}
└─HashAgg_17(Probe) 1.00 356541 cop[tikv] time:2m14.2s, loops:694, cop_task: {num: 7993, max: 268.5ms, min: 5.83ms, avg: 16.6ms, p95: 35.4ms, max_proc_keys: 389, p95_proc_keys: 126, tot_proc: 32.9s, tot_wait: 7.92s, rpc_num: 7997, rpc_time: 2m12.9s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:191ms, min:0s, p80:5ms, p95:13ms, iters:7993, tasks:7993}, scan_detail: {total_process_keys: 489103, total_keys: 499620, rocksdb: {delete_skipped_count: 0, key_skipped_count: 21418, block: {cache_hit_count: 3904981, read_count: 51626, read_byte: 570.6 MB}}}
└─Selection_32 8.73 456222 cop[tikv] tikv_task:{proc max:190ms, min:0s, p80:4ms, p95:12ms, iters:7993, tasks:7993}
└─TableRowIDScan_30 43.09 489103 cop[tikv] table:dish_analysis_result tikv_task:{proc max:190ms, min:0s, p80:4ms, p95:12ms, iters:7993, tasks:7993}
生产的explain结果:
Projection_8 102.22 1000 root time:1.13s, loops:2, Concurrency:OFF
└─TopN_11 102.22 1000 root time:1.13s, loops:2
└─HashAgg_32 102.22 2138 root time:1.13s, loops:5, PartialConcurrency:2, FinalConcurrency:2
└─IndexLookUp_33 102.22 41236 root time:1.12s, loops:42, index_task: {total_time: 968.9ms, fetch_handle: 89.5ms, build: 21.6µs, wait: 879.3ms}, table_task: {total_time: 4.32s, num: 19, concurrency: 4}
├─Selection_30(Build) 39674.92 306035 cop[tikv] time:62.1ms, loops:309, cop_task: {num: 16, max: 129.8ms, min: 4.77ms, avg: 53.1ms, p95: 129.8ms, max_proc_keys: 60873, p95_proc_keys: 60873, tot_proc: 790ms, tot_wait: 27ms, rpc_num: 16, rpc_time: 849.3ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:125ms, min:3ms, p80:79ms, p95:125ms, iters:368, tasks:16}, scan_detail: {total_process_keys: 306035, total_keys: 311039}
│ └─IndexRangeScan_28 39674924.00 306035 cop[tikv] table:dish_analysis_result, index:dish_data_brand(sh_dish_id, date, brand_id) tikv_task:{proc max:123ms, min:3ms, p80:79ms, p95:123ms, iters:368, tasks:16}
└─HashAgg_16(Probe) 102.22 41236 cop[tikv] time:3.9s, loops:70, cop_task: {num: 1045, max: 209.3ms, min: 3.27ms, avg: 45.5ms, p95: 121.5ms, max_proc_keys: 1014, p95_proc_keys: 723, tot_proc: 16.1s, tot_wait: 15.2s, rpc_num: 1045, rpc_time: 47.5s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:94ms, min:0s, p80:23ms, p95:38ms, iters:1045, tasks:1045}, scan_detail: {total_process_keys: 306035, total_keys: 315783}
└─Selection_31 39674.92 277318 cop[tikv] tikv_task:{proc max:93ms, min:0s, p80:22ms, p95:37ms, iters:1045, tasks:1045}
└─TableRowIDScan_29 39674.92 306035 cop[tikv] table:dish_analysis_result tikv_task:{proc max:93ms, min:0s, p80:22ms, p95:37ms, iters:1045, tasks:1045}
问题：
同张表，使用一样的索引，生产索引扫描行数38万， 测试索引扫描49万。
所有算子执行时间，生产2秒 测试需要149秒。 生产父算子调用子算子695次， 测试父算子调用子算子69次
请问这差异是什么影响的？ 感谢
