请问下各位老师，sql hint 能不能指定index join 或者hash join的连接顺序,这些表均来自tpch
例如我想指定a是驱动表，b是被驱动表，但是执行计划里面看起来b还是驱动表
mysql> explain analyze select /*+ INL_JOIN(a,b) */ a.O_ORDERKEY, a.O_CUSTKEY,b.C_NAME from orders a join customer b on a.O_CUSTKEY = b.C_CUSTKEY;
±----------------------------±------------±---------±----------±------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±----------------------------±------------±---------±----------±------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| IndexJoin_19 | 29955968.00 | 29955968 | root | | time:57.4s, loops:29255, inner:{total:4m38.7s, concurrency:5, task:126, construct:1.66s, fetch:4m33.3s, build:3.77s}, probe:4.9s | inner join, inner:IndexReader_18, outer key:tpch.customer.c_custkey, inner key:tpch.orders.o_custkey, equal cond:eq(tpch.customer.c_custkey, tpch.orders.o_custkey) | 56.5 MB | N/A |
| ├─TableReader_31(Build) | 3000000.00 | 3000000 | root | | time:85.1ms, loops:2941, cop_task: {num: 116, max: 554.8ms, min: 2.49ms, avg: 87.6ms, p95: 250.6ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 4.37s, tot_wait: 214ms, rpc_num: 116, rpc_time: 10.2s, copr_cache_hit_ratio: 0.15, distsql_concurrency: 15} | data:TableFullScan_30 | 13.0 MB | N/A |
| │ └─TableFullScan_30 | 3000000.00 | 3000000 | cop[tikv] | table:b | tikv_task:{proc max:342ms, min:0s, avg: 36.3ms, p80:53ms, p95:150ms, iters:3387, tasks:116}, scan_detail: {total_process_keys: 2876640, total_process_keys_size: 585339241, total_keys: 2876739, get_snapshot_time: 45.2ms, rocksdb: {key_skipped_count: 2876640, block: {cache_hit_count: 10142}}} | keep order:false | N/A | N/A |
| └─IndexReader_18(Probe) | 29955968.00 | 29955968 | root | | time:4m29.6s, loops:29678, cop_task: {num: 2633, max: 957.2ms, min: 1.4ms, avg: 131.5ms, p95: 468.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 3m14.7s, tot_wait: 26.4s, rpc_num: 2633, rpc_time: 5m46.1s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | index:IndexRangeScan_17 | 17.7 KB | N/A |
| └─IndexRangeScan_17 | 29955968.00 | 29955968 | cop[tikv] | table:a, index:index_o_custkey(O_CUSTKEY) | tikv_task:{proc max:831ms, min:0s, avg: 73.3ms, p80:117ms, p95:377ms, iters:39544, tasks:2633}, scan_detail: {total_process_keys: 29955968, total_process_keys_size: 1377974528, total_keys: 32958332, get_snapshot_time: 370.6ms, rocksdb: {key_skipped_count: 29955968, block: {cache_hit_count: 18033644}}} | range: decided by [eq(tpch.orders.o_custkey, tpch.customer.c_custkey)], keep order:false | N/A | N/A |
±----------------------------±------------±---------±----------±------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
5 rows in set (57.38 sec)
表结构如下
mysql> show create table customer;
±---------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±---------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer | CREATE TABLE customer (
C_CUSTKEY bigint(20) NOT NULL,
C_NAME varchar(25) NOT NULL,
C_ADDRESS varchar(40) NOT NULL,
C_NATIONKEY bigint(20) NOT NULL,
C_PHONE char(15) NOT NULL,
C_ACCTBAL decimal(15,2) NOT NULL,
C_MKTSEGMENT char(10) NOT NULL,
C_COMMENT varchar(117) NOT NULL,
PRIMARY KEY (C_CUSTKEY) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
±---------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table orders;
±-------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±-------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE orders (
O_ORDERKEY bigint(20) NOT NULL,
O_CUSTKEY bigint(20) NOT NULL,
O_ORDERSTATUS char(1) NOT NULL,
O_TOTALPRICE decimal(15,2) NOT NULL,
O_ORDERDATE date NOT NULL,
O_ORDERPRIORITY char(15) NOT NULL,
O_CLERK char(15) NOT NULL,
O_SHIPPRIORITY bigint(20) NOT NULL,
O_COMMENT varchar(79) NOT NULL,
PRIMARY KEY (O_ORDERKEY) /*T![clustered_index] CLUSTERED */,
KEY index_o_custkey (O_CUSTKEY),
KEY O_TOTALPRICE (O_TOTALPRICE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
±-------±-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.hash join的时候，能否指定1个表去build 生成hash 表，例如指定a表去生成hash 表
explain analyze select /*+ HASH_JOIN(a,b) */ a.O_ORDERKEY, a.O_CUSTKEY,b.C_NAME from orders a join customer b on a.O_CUSTKEY = b.C_CUSTKEY;
