tidb sql hint 老是报错

🪐 TiDB 技术问题
1

请教一下各位老师，不让下列语句使用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)
2

https://docs.pingcap.com/zh/tidb/stable/optimizer-hints#semi_join_rewrite

是不是先使用这个hint会好一些。
感觉选择merge join是exists被重写的结果。

3

exists并不是join操作，应该不可以用NO_MERGE_JOIN这个hint，可以用join改写试一下。

4

我就是想测试下，在semi join的情况下，语句的执行效果，但是加了sql hint 也没用

5

image
image1760×695 71.2 KB

wo我执行计划里面都出现了merge join

6 
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)

image
image1405×1101 24.3 KB

发现用index join 也是不行，按理来说用index join 也是OK的

7

没有这个hint，执行计划中应该也是MergeJoin。

8

你这个orders表上O_CUSTKEY上有索引，而customer的C_CUSTKEY上没索引，应该只能走orders表上索引之后作为驱动表连接customer表的MergeJoin semi连接，走不了其他的，你换换条件试试

9

C_CUSTKEY 是customer的主键索引