请教一下各位老师,不让下列语句使用merge join,加了sql hint 老师报错,不知道问题出在哪?麻烦请老师指教,另外是否有参数禁用merge join
mysql> explain analyze select /*+ NO_MERGE_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:547.7ms, loops:2 | funcs:count(1)->Column#18 | 8 Bytes | N/A |
| └─MergeJoin_33 | 120000.00 | 99996 | root | | time:546ms, loops:99 | semi join, left key:tpch2.customer.c_custkey, right key:tpch2.orders.o_custkey | 38.5 KB | 0 Bytes |
| ├─IndexReader_26(Build) | 1498900.00 | 1498900 | root | | time:506.1ms, loops:1507, cop_task: {num: 46, max: 51.1ms, min: 1.3ms, avg: 16.8ms, p95: 42ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 536ms, tot_wait: 10ms, rpc_num: 46, rpc_time: 772ms, copr_cache: disabled, distsql_concurrency: 15} | index:IndexFullScan_25 | 5.16 MB | N/A |
| │ └─IndexFullScan_25 | 1498900.00 | 1498900 | cop[tikv] | table:orders, index:indexO_CUSTKEY(O_CUSTKEY) | tikv_task:{proc max:39ms, min:0s, avg: 11.7ms, p80:17ms, p95:19ms, iters:1647, tasks:46}, scan_detail: {total_process_keys: 1498900, total_process_keys_size: 68949400, total_keys: 1498946, get_snapshot_time: 3.73ms, rocksdb: {key_skipped_count: 1498900, block: {cache_hit_count: 1080}}} | keep order:true | N/A | N/A |
| └─TableReader_24(Probe) | 150000.00 | 150000 | root | | time:289.7µs, loops:149, cop_task: {num: 11, max: 37.1ms, min: 1.3ms, avg: 11.5ms, p95: 37.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 69ms, tot_wait: 8ms, rpc_num: 11, rpc_time: 126.2ms, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_23 | 1.10 MB | N/A |
| └─TableFullScan_23 | 150000.00 | 150000 | cop[tikv] | table:customer | tikv_task:{proc max:29ms, min:0s, avg: 6.45ms, p80:10ms, p95:29ms, iters:190, tasks:11}, scan_detail: {total_process_keys: 150000, total_process_keys_size: 4050000, total_keys: 150011, get_snapshot_time: 1.44ms, rocksdb: {key_skipped_count: 150000, block: {cache_hit_count: 531}}} | keep order:true | N/A | N/A |
+-------------------------------+------------+---------+-----------+-----------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+---------+---------+
6 rows in set, 1 warning (0.55 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------+
| Warning | 1064 | Optimizer hint syntax error at line 1 column 41 near "NO_MERGE_JOIN(customer,orders) */" |
+---------+------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)