deep nested query too slow

## Enhancement
DDL: [https://github.com/pingcap/tidb/files/8499759/mysql_bk.txt](https://github.com/pingcap/tidb/files/8499759/mysql_bk.txt)

this SQL is too slow compared with MySQL 5.7/8.0 (TiDB(with TiKV) 15s、TiDB(stand-alone) 3s 、 MySQL 1s) :
mysql -h "127.0.0.1" -u root -P 4000 -D testdb

mysql> select
ref_0.c_lmpznc as c5
from
t_zb_m5 as ref_0
where (ref_0.c_mu4_e in (
select distinct
ref_2.pkey > ref_2.c_c23g6c as c0
from
(t_wzgyvd as ref_2
cross join t_wzgyvd as ref_3
)
where EXISTS (
select
ref_4.c__gkztd as c0,
ref_2.pkey as c1
from
t_wzgyvd as ref_4
where EXISTS (
select
ref_5.c_pqvmnd as c10
from
t_wzgyvd as ref_5
where (ref_5.c_hysvi < (
select
ref_3.c_oswlic as c0
from
t_wzgyvd as ref_6
where ref_4.c_dm4wqb in (
select
'o5sq1c' as c0
from
t_zb_m5 as ref_7
where ref_7.wkey = ref_7.pkey
)
) or ref_2.c_c23g6c < ref_4.wkey)))
))
;

refer issue: [https://github.com/pingcap/tidb/issues/34043](https://github.com/pingcap/tidb/issues/34043)

It seems there are too many apply operators in the plan.

id task estRows operator info actRows execution info memory disk
HashJoin_36 root 10000 inner join, equal:[eq(testdb.t_zb_m5.c_mu4_e, Column#67)] 0 time:16.1s, loops:1, build_hash_table:{total:16.1s, fetch:16.1s, build:6.71µs}, probe:{concurrency:5, total:1m20.5s, max:16.1s, probe:12µs, fetch:1m20.5s} 380 Bytes 0 Bytes
├─TableReader_39 root 10000 data:TableFullScan_38 18 time:737.9µs, loops:2, cop_task: {num: 1, max: 867.6µs, proc_keys: 18, rpc_num: 1, rpc_time: 803.2µs, copr_cache_hit_ratio: 0.00} 501 Bytes N/A
│ └─TableFullScan_38 cop[tikv] 10000 table:ref_0, keep order:false, stats:pseudo 18 tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 18, total_process_keys_size: 1381, total_keys: 19, rocksdb: {delete_skipped_count: 0, key_skipped_count: 18, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─Projection_40 root 8000 cast(Column#56, double BINARY)->Column#67 1 time:16.1s, loops:2, Concurrency:5 19.1 KB N/A
└─HashAgg_41 root 8000 group by:Column#75, funcs:firstrow(Column#74)->Column#56 1 time:16.1s, loops:2, partial_worker:{wall_time:16.09884264s, concurrency:5, task_num:1, tot_wait:1m20.493732195s, tot_exec:30.521µs, tot_time:1m20.493793727s, max:16.098771051s, p95:16.098771051s}, final_worker:{wall_time:16.098891319s, concurrency:5, task_num:1, tot_wait:1m20.493815727s, tot_exec:23.956µs, tot_time:1m20.493897792s, max:16.09881177s, p95:16.09881177s} 17.4 KB N/A
└─Projection_69 root 100000000 gt(testdb.t_wzgyvd.pkey, testdb.t_wzgyvd.c_c23g6c)->Column#74, gt(testdb.t_wzgyvd.pkey, testdb.t_wzgyvd.c_c23g6c)->Column#75 162 time:16.1s, loops:2, Concurrency:5 16.2 KB N/A
└─Apply_43 root 100000000 CARTESIAN semi join 162 time:16.1s, loops:2, Concurrency:OFF, cache:ON, cacheHitRatio:14.403% 11.4 KB N/A
├─HashJoin_44 root 100000000 CARTESIAN inner join 729 time:1.05ms, loops:3, build_hash_table:{total:530.5µs, fetch:513.8µs, build:16.8µs}, probe:{concurrency:5, total:3ms, max:923µs, probe:376.9µs, fetch:2.63ms} 16.2 KB 0 Bytes
│ ├─TableReader_47 root 10000 data:TableFullScan_46 27 time:444.2µs, loops:2, cop_task: {num: 1, max: 894.3µs, proc_keys: 27, rpc_num: 1, rpc_time: 832.3µs, copr_cache_hit_ratio: 0.00} 639 Bytes N/A
│ │ └─TableFullScan_46 cop[tikv] 10000 table:ref_2, keep order:false, stats:pseudo 27 tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 27, total_process_keys_size: 2264, total_keys: 28, rocksdb: {delete_skipped_count: 0, key_skipped_count: 27, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
│ └─TableReader_49 root 10000 data:TableFullScan_48 27 time:422.7µs, loops:2, cop_task: {num: 1, max: 801.7µs, proc_keys: 27, rpc_num: 1, rpc_time: 739.2µs, copr_cache_hit_ratio: 0.00} 570 Bytes N/A
│ └─TableFullScan_48 cop[tikv] 10000 table:ref_3, keep order:false, stats:pseudo 27 tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 27, total_process_keys_size: 2264, total_keys: 28, rocksdb: {delete_skipped_count: 0, key_skipped_count: 27, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─Apply_51 root 10000 CARTESIAN semi join, other cond:or(lt(testdb.t_wzgyvd.c_hysvi, Column#54), lt(testdb.t_wzgyvd.c_c23g6c, testdb.t_wzgyvd.wkey)) 1430 time:16.1s, loops:754, Concurrency:OFF 261 Bytes N/A
├─TableReader_53 root 10000 data:TableFullScan_52 16848 time:319.8ms, loops:1378, cop_task: {num: 624, max: 1.36ms, min: 236.7µs, avg: 428.8µs, p95: 617.6µs, max_proc_keys: 27, p95_proc_keys: 27, tot_proc: 44ms, tot_wait: 16ms, rpc_num: 624, rpc_time: 249.7ms, copr_cache_hit_ratio: 0.00} 799 Bytes N/A
│ └─TableFullScan_52 cop[tikv] 10000 table:ref_4, keep order:false, stats:pseudo 16848 tikv_task:{proc max:4ms, min:0s, p80:0s, p95:0s, iters:624, tasks:624}, scan_detail: {total_process_keys: 16848, total_process_keys_size: 1412736, total_keys: 17472, rocksdb: {delete_skipped_count: 0, key_skipped_count: 16848, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─HashJoin_54 root 10000 CARTESIAN left outer join 454896 time:12.8s, loops:33696, build_hash_table:{total:9.74s, fetch:9.65s, build:96.5ms}, probe:{concurrency:5, total:51.4s, max:22.2ms, probe:437.1ms, fetch:51s} 16.2 KB 0 Bytes
├─TableReader_57 root 10000 data:TableFullScan_56 454896 time:7.39s, loops:33696, cop_task: {num: 16848, max: 22ms, min: 223.2µs, avg: 492.3µs, p95: 663.9µs, max_proc_keys: 27, p95_proc_keys: 27, tot_proc: 1.05s, tot_wait: 727ms, rpc_num: 16848, rpc_time: 7.78s, copr_cache_hit_ratio: 0.00} 559 Bytes N/A
│ └─TableFullScan_56 cop[tikv] 10000 table:ref_5, keep order:false, stats:pseudo 454896 tikv_task:{proc max:4ms, min:0s, p80:0s, p95:0s, iters:16848, tasks:16848}, scan_detail: {total_process_keys: 454896, total_process_keys_size: 38143872, total_keys: 471744, rocksdb: {delete_skipped_count: 0, key_skipped_count: 454896, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─MaxOneRow_58 root 1 16848 time:8.7s, loops:33696 N/A N/A
└─Projection_59 root 2 testdb.t_wzgyvd.c_oswlic 0 time:8.58s, loops:16848, Concurrency:OFF 0 Bytes N/A
└─HashJoin_60 root 2 CARTESIAN semi join 0 time:8.5s, loops:16848, build_hash_table:{total:6.17s, fetch:6.17s, build:0s} 0 Bytes 0 Bytes
├─IndexReader_64 root 2.50 index:IndexFullScan_63 454896 time:4.85s, loops:16848, cop_task: {num: 16848, max: 21.8ms, min: 200.3µs, avg: 481.1µs, p95: 648.3µs, max_proc_keys: 27, p95_proc_keys: 27, tot_proc: 1.05s, tot_wait: 640ms, rpc_num: 16848, rpc_time: 7.59s, copr_cache_hit_ratio: 0.00} 416 Bytes N/A
│ └─IndexFullScan_63 cop[tikv] 2.50 table:ref_6, index:pkey_2(pkey), keep order:false, stats:pseudo 454896 tikv_task:{proc max:4ms, min:0s, p80:0s, p95:0s, iters:16848, tasks:16848}, scan_detail: {total_process_keys: 454896, total_process_keys_size: 20015424, total_keys: 471744, rocksdb: {delete_skipped_count: 0, key_skipped_count: 454896, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─Projection_65 root 8000 1->Column#72 0 time:5.58s, loops:16848, Concurrency:5 12.7 KB N/A
└─TableReader_68 root 8000 data:Selection_67 0 time:4.79s, loops:16848, cop_task: {num: 16848, max: 16.4ms, min: 204.8µs, avg: 458.1µs, p95: 616.5µs, max_proc_keys: 18, p95_proc_keys: 18, tot_proc: 947ms, tot_wait: 667ms, rpc_num: 16848, rpc_time: 7.25s, copr_cache_hit_ratio: 0.00} 192 Bytes N/A
└─Selection_67 cop[tikv] 8000 eq(testdb.t_wzgyvd.c_dm4wqb, "o5sq1c"), eq(testdb.t_zb_m5.wkey, testdb.t_zb_m5.pkey) 0 tikv_task:{proc max:4ms, min:0s, p80:0s, p95:0s, iters:16848, tasks:16848}, scan_detail: {total_process_keys: 303264, total_process_keys_size: 23267088, total_keys: 320112, rocksdb: {delete_skipped_count: 0, key_skipped_count: 303264, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─TableFullScan_66 cop[tikv] 10000 table:ref_7, keep order:false, stats:pseudo 303264 tikv_task:{proc max:4ms, min:0s, p80:0s, p95:0s, iters:16848, tasks:16848} N/A N/A