mysql> explain analyze select /*+ INL_JOIN(customer,orders) */ count(*) from customer where exists (select orders.O_CUSTKEY from orders where customer.C_CUSTKEY=orders.O_CUSTKEY);
+-------------------------------+-------------+----------+-----------+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+---------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------+-------------+----------+-----------+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+---------+---------+
| StreamAgg_11 | 1.00 | 1 | root | | time:11.3s, loops:2 | funcs:count(1)->Column#18 | 8 Bytes | N/A |
| └─MergeJoin_33 | 2400000.00 | 1999956 | root | | time:11.3s, loops:1955 | semi join, left key:tpch.customer.c_custkey, right key:tpch.orders.o_custkey | 38.5 KB | 0 Bytes |
| ├─IndexReader_26(Build) | 29955968.00 | 29955968 | root | | time:10.5s, loops:30000, cop_task: {num: 784, max: 102.8ms, min: 693.1µs, avg: 26.1ms, p95: 53.6ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 13.8s, tot_wait: 438ms, rpc_num: 784, rpc_time: 20.5s, copr_cache: disabled, distsql_concurrency: 15} | index:IndexFullScan_25 | 5.16 MB | N/A |
| │ └─IndexFullScan_25 | 29955968.00 | 29955968 | cop[tikv] | table:orders, index:index_o_custkey(O_CUSTKEY) | tikv_task:{proc max:87ms, min:0s, avg: 17.5ms, p80:27ms, p95:39ms, iters:32383, tasks:784}, scan_detail: {total_process_keys: 29955968, total_process_keys_size: 1377974528, total_keys: 29956752, get_snapshot_time: 183.8ms, rocksdb: {key_skipped_count: 29955968, block: {cache_hit_count: 21394}}} | keep order:true | N/A | N/A |
| └─TableReader_24(Probe) | 3000000.00 | 3000000 | root | | time:6.92ms, loops:2938, cop_task: {num: 116, max: 84.8ms, min: 696.6µs, avg: 27.6ms, p95: 73.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1.99s, tot_wait: 158ms, rpc_num: 116, rpc_time: 3.2s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_23 | 13.4 MB | N/A |
| └─TableFullScan_23 | 3000000.00 | 3000000 | cop[tikv] | table:customer | tikv_task:{proc max:65ms, min:0s, avg: 17.2ms, p80:37ms, p95:52ms, iters:3387, tasks:116}, scan_detail: {total_process_keys: 3000000, total_process_keys_size: 81000000, total_keys: 3000116, get_snapshot_time: 24.3ms, rocksdb: {key_skipped_count: 3000000, block: {cache_hit_count: 10238}}} | keep order:true | N/A | N/A |
+-------------------------------+-------------+----------+-----------+------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+---------+---------+
6 rows in set, 1 warning (11.34 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | There are no matching table names for (customer, orders) in optimizer hint /*+ INL_JOIN(customer, orders) */ or /*+ TIDB_INLJ(customer, orders) */. Maybe you can use the table alias name |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
发现用index join 也是不行，按理来说用index join 也是OK的