try to optimize index join query by reduce cop task count

[crazycs520]

Enhancement

Related issue: #54160 #54840

1. Minimal reproduce step

drop table if exists t0,t1;
create table t1 (id int key auto_increment, b int, index idx1 (b));
insert into t1 () values (), (), (), (), (), (), (), ();
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1 limit 34464;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1;
insert into t1 (b) select b from t1 limit 200000;
update t1 set b = rand() * 100000;
create table t0 (id int key, b int, index idx1 (b));
insert into t0 select * from t1 order by id limit 100000;
update t0 set b = rand() * 100000;
split table t1 index idx1 between (0) and (100000) regions 10;
analyze table t0,t1;
set @@tidb_store_batch_size=0;
explain analyze select /*+ INL_JOIN(t0,t1) */ t0.* from t0 join t1 where t0.id<=50000 and t0.b=t1.b;

> explain analyze select /*+ INL_JOIN(t0,t1) */ t0.* from t0 join t1 where t0.id<=50000 and t0.b=t1.b;
+-------------------------+-----------+---------+-----------+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
| id                      | estRows   | actRows | task      | access object           | execution info                                                                                                                                                                                                                                                                                                     | operator info                                                                                                   | memory   | disk |
+-------------------------+-----------+---------+-----------+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
| IndexJoin_12            | 498516.86 | 501090  | root      |                         | time:163.3ms, loops:491, RU:624.032959, inner:{total:445.2ms, concurrency:5, task:10, construct:26ms, fetch:384.3ms, build:34.9ms}, probe:28.2ms                                                                                                                                                                   | inner join, inner:IndexReader_11, outer key:test.t0.b, inner key:test.t1.b, equal cond:eq(test.t0.b, test.t1.b) | 10.8 MB  | N/A  |
| ├─TableReader_33(Build) | 49740.02  | 50000   | root      |                         | time:29.1ms, loops:54, cop_task: {num: 9, max: 5.27ms, min: 146.5µs, avg: 2.16ms, p95: 5.27ms, tot_proc: 11ms, copr_cache_hit_ratio: 0.00, build_task_duration: 4.21µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:9, total_time:19.3ms}}                                                                 | data:Selection_32                                                                                               | 546.5 KB | N/A  |
| │ └─Selection_32        | 49740.02  | 50000   | cop[tikv] |                         | tikv_task:{proc max:4.68ms, min:135.5µs, avg: 1.57ms, p80:4.54ms, p95:4.68ms, iters:0, tasks:9}, time_detail: {total_process_time: 11ms}                                                                                                                                                                           | not(isnull(test.t0.b))                                                                                          | N/A      | N/A  |
| │   └─TableRangeScan_31 | 49740.02  | 50000   | cop[tikv] | table:t0                | tikv_task:{proc max:4.68ms, min:135.5µs, avg: 1.57ms, p80:4.54ms, p95:4.68ms, iters:0, tasks:9}                                                                                                                                                                                                                    | range:[-inf,50000], keep order:false                                                                            | N/A      | N/A  |
| └─IndexReader_11(Probe) | 498516.86 | 469930  | root      |                         | time:346.7ms, loops:474, cop_task: {num: 425, max: 14.8ms, min: 23.2µs, avg: 2.95ms, p95: 6.86ms, tot_proc: 1.02s, copr_cache_hit_ratio: 0.00, build_task_duration: 2.47ms, max_distsql_concurrency: 11}, rpc_info:{Cop:{num_rpc:426, total_time:1.25s}, rpc_errors:{epoch_not_match:1}}, backoff{regionMiss: 4ms} | index:Selection_10                                                                                              | 2.55 KB  | N/A  |
|   └─Selection_10        | 498516.86 | 469930  | cop[tikv] |                         | tikv_task:{proc max:14.8ms, min:16µs, avg: 2.86ms, p80:4.92ms, p95:6.67ms, iters:0, tasks:425}, time_detail: {total_process_time: 1.02s}                                                                                                                                                                           | not(isnull(test.t1.b))                                                                                          | N/A      | N/A  |
|     └─IndexRangeScan_9  | 498516.86 | 469930  | cop[tikv] | table:t1, index:idx1(b) | tikv_task:{proc max:14.8ms, min:16µs, avg: 2.86ms, p80:4.92ms, p95:6.67ms, iters:0, tasks:425}                                                                                                                                                                                                                     | range: decided by [eq(test.t1.b, test.t0.b)], keep order:false                                                  | N/A      | N/A  |
+-------------------------+-----------+---------+-----------+-------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+

Pay attention to the execution information of IndexReader_11(Probe) executor, it send too many cop task, since the total index region of table t1's index idx1 is 10.

└─IndexReader_11(Probe) ... time:346.7ms, loops:474, cop_task: {num: 425,

And I try to increase tidb_max_chunk_size, but this has no affected, this is unexpected, it should work and reduce the number of cop task.

> set @@tidb_max_chunk_size=25000;
> explain analyze select /*+ INL_JOIN(t0,t1) */ t0.* from t0 join t1 where t0.id<=50000 and t0.b=t1.b;
+-------------------------+-----------+---------+-----------+-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
| id                      | estRows   | actRows | task      | access object           | execution info                                                                                                                                                                                                                                          | operator info                                                                                                   | memory   | disk |
+-------------------------+-----------+---------+-----------+-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
| IndexJoin_12            | 498516.86 | 501090  | root      |                         | time:141.7ms, loops:22, RU:534.556616, inner:{total:355.9ms, concurrency:5, task:10, construct:32.2ms, fetch:287.5ms, build:36.1ms}, probe:26ms                                                                                                         | inner join, inner:IndexReader_11, outer key:test.t0.b, inner key:test.t1.b, equal cond:eq(test.t0.b, test.t1.b) | 10.5 MB  | N/A  |
| ├─TableReader_33(Build) | 49740.02  | 50000   | root      |                         | time:19ms, loops:12, cop_task: {num: 9, max: 6.24ms, min: 305.7µs, avg: 2.18ms, p95: 6.24ms, tot_proc: 17ms, copr_cache_hit_ratio: 0.00, build_task_duration: 25.8µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:9, total_time:19.5ms}}        | data:Selection_32                                                                                               | 546.5 KB | N/A  |
| │ └─Selection_32        | 49740.02  | 50000   | cop[tikv] |                         | tikv_task:{proc max:6.19ms, min:249µs, avg: 2.13ms, p80:5.17ms, p95:6.19ms, iters:0, tasks:9}, time_detail: {total_process_time: 17ms}                                                                                                                  | not(isnull(test.t0.b))                                                                                          | N/A      | N/A  |
| │   └─TableRangeScan_31 | 49740.02  | 50000   | cop[tikv] | table:t0                | tikv_task:{proc max:6.19ms, min:249µs, avg: 2.13ms, p80:5.17ms, p95:6.19ms, iters:0, tasks:9}                                                                                                                                                           | range:[-inf,50000], keep order:false                                                                            | N/A      | N/A  |
| └─IndexReader_11(Probe) | 498516.86 | 469930  | root      |                         | time:245.7ms, loops:36, cop_task: {num: 425, max: 9.58ms, min: 37µs, avg: 2.21ms, p95: 5.43ms, tot_proc: 748ms, copr_cache_hit_ratio: 0.00, build_task_duration: 2.09ms, max_distsql_concurrency: 11}, rpc_info:{Cop:{num_rpc:425, total_time:934.6ms}} | index:Selection_10                                                                                              | 1.66 KB  | N/A  |
|   └─Selection_10        | 498516.86 | 469930  | cop[tikv] |                         | tikv_task:{proc max:9.56ms, min:27.4µs, avg: 2.19ms, p80:4.05ms, p95:5.4ms, iters:0, tasks:425}, time_detail: {total_process_time: 748ms}                                                                                                               | not(isnull(test.t1.b))                                                                                          | N/A      | N/A  |
|     └─IndexRangeScan_9  | 498516.86 | 469930  | cop[tikv] | table:t1, index:idx1(b) | tikv_task:{proc max:9.56ms, min:27.4µs, avg: 2.19ms, p80:4.05ms, p95:5.4ms, iters:0, tasks:425}                                                                                                                                                         | range: decided by [eq(test.t1.b, test.t0.b)], keep order:false                                                  | N/A      | N/A  |
+-------------------------+-----------+---------+-----------+-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+