explain analyze select /*+ LEADING(region,nation,customer,orders,lineitem,supplier) */
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = n_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= date '1994-01-01'
and o_orderdate < date '1994-01-01' + interval '1' year
group by
n_name
order by
revenue desc;
+------------------------------------------------+-------------+----------+-----------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------------+-------------+----------+-----------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+
| Sort_16 | 20.00 | 5 | root | | time:4m5.8s, loops:2 | Column#48:desc | 2.69 KB | 0 Bytes |
| └─Projection_18 | 20.00 | 5 | root | | time:4m5.8s, loops:2, Concurrency:OFF | tpch.nation.n_name, Column#48 | 2.65 KB | N/A |
| └─HashAgg_19 | 20.00 | 5 | root | | time:4m5.8s, loops:2 | group by:Column#51, funcs:sum(Column#49)->Column#48, funcs:firstrow(Column#50)->tpch.nation.n_name | 79.4 KB | 0 Bytes |
| └─Projection_126 | 4545.43 | 145977 | root | | time:4m5.8s, loops:146, Concurrency:5 | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name | 888.9 KB | N/A |
| └─HashJoin_31 | 4545.43 | 145977 | root | | time:4m5.8s, loops:146, build_hash_table:{total:2.27ms, fetch:2.27ms, build:3.16µs}, probe:{concurrency:5, total:20m29.1s, max:4m5.8s, probe:344.3ms, fetch:20m28.8s} | inner join, equal:[eq(tpch.nation.n_regionkey, tpch.region.r_regionkey)] | 25.7 KB | 0 Bytes |
| ├─TableReader_124(Build) | 0.01 | 1 | root | | time:2.25ms, loops:2, cop_task: {num: 1, max: 2.58ms, proc_keys: 5, rpc_num: 1, rpc_time: 2.57ms, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_123 | 259 Bytes | N/A |
| │ └─Selection_123 | 0.01 | 1 | cop[tikv] | | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 559, total_keys: 6, get_snapshot_time: 8.9µs, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 6}}} | eq(tpch.region.r_name, "ASIA") | N/A | N/A |
| │ └─TableFullScan_122 | 5.00 | 5 | cop[tikv] | table:region | tikv_task:{time:0s, loops:1} | keep order:false, stats:pseudo | N/A | N/A |
| └─HashJoin_41(Probe) | 18181718.62 | 726970 | root | | time:4m5.8s, loops:714, build_hash_table:{total:1.57ms, fetch:1.56ms, build:9.74µs}, probe:{concurrency:5, total:20m29.1s, max:4m5.8s, probe:3.02s, fetch:20m26.1s} | inner join, equal:[eq(tpch.customer.c_nationkey, tpch.nation.n_nationkey) eq(tpch.supplier.s_nationkey, tpch.nation.n_nationkey)] | 33.9 KB | 0 Bytes |
| ├─TableReader_121(Build) | 25.00 | 25 | root | | time:1.55ms, loops:2, cop_task: {num: 1, max: 2.06ms, proc_keys: 25, rpc_num: 1, rpc_time: 1.95ms, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_120 | 1020 Bytes | N/A |
| │ └─TableFullScan_120 | 25.00 | 25 | cop[tikv] | table:nation | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 11µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 6}}} | keep order:false, stats:pseudo | N/A | N/A |
| └─HashJoin_52(Probe) | 18181718.62 | 18173018 | root | | time:4m5.8s, loops:17749, build_hash_table:{total:313.1ms, fetch:272.3ms, build:40.9ms}, probe:{concurrency:5, total:20m29.1s, max:4m5.8s, probe:42s, fetch:19m47.1s} | inner join, equal:[eq(tpch.lineitem.l_suppkey, tpch.supplier.s_suppkey)] | 15.1 MB | 0 Bytes |
| ├─TableReader_119(Build) | 200000.00 | 200000 | root | | time:272.3ms, loops:197, cop_task: {num: 12, max: 73.5ms, min: 1.43ms, avg: 25.7ms, p95: 73.5ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 223ms, tot_wait: 3ms, rpc_num: 12, rpc_time: 308.6ms, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_118 | 1.53 MB | N/A |
| │ └─TableFullScan_118 | 200000.00 | 200000 | cop[tikv] | table:supplier | tikv_task:{proc max:58ms, min:0s, avg: 19.1ms, p80:38ms, p95:58ms, iters:242, tasks:12}, scan_detail: {total_process_keys: 200000, total_process_keys_size: 36284916, total_keys: 200012, get_snapshot_time: 3.86ms, rocksdb: {key_skipped_count: 200000, block: {cache_hit_count: 668}}} | keep order:false | N/A | N/A |
| └─IndexHashJoin_61(Probe) | 17905356.64 | 18173018 | root | | time:4m5.8s, loops:17750, inner:{total:19m57.6s, concurrency:5, task:186, construct:4.93s, fetch:19m29.7s, build:602.2ms, join:23s} | inner join, inner:TableReader_56, outer key:tpch.orders.o_orderkey, inner key:tpch.lineitem.l_orderkey, equal cond:eq(tpch.orders.o_orderkey, tpch.lineitem.l_orderkey) | 61.4 MB | N/A |
| ├─HashJoin_104(Build) | 4470018.16 | 4543127 | root | | time:14.7s, loops:4441, build_hash_table:{total:5.31s, fetch:4.26s, build:1.06s}, probe:{concurrency:5, total:19m36.9s, max:3m55.4s, probe:18m36.5s, fetch:1m0.4s} | inner join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] | 235.6 MB | 0 Bytes |
| │ ├─TableReader_112(Build) | 3000000.00 | 3000000 | root | | time:4.24s, loops:2937, cop_task: {num: 116, max: 830ms, min: 10.4ms, avg: 306.4ms, p95: 711ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 22.6s, tot_wait: 9.91s, rpc_num: 116, rpc_time: 35.5s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_111 | 1.53 MB | N/A |
| │ │ └─TableFullScan_111 | 3000000.00 | 3000000 | cop[tikv] | table:customer | tikv_task:{proc max:585ms, min:2ms, avg: 195.1ms, p80:373ms, p95:475ms, iters:3387, tasks:116}, scan_detail: {total_process_keys: 3000000, total_process_keys_size: 610451426, total_keys: 3000116, get_snapshot_time: 2.01ms, rocksdb: {key_skipped_count: 3000000, block: {cache_hit_count: 682, read_count: 9969, read_byte: 222.8 MB, read_time: 12.9s}}} | keep order:false | N/A | N/A |
| │ └─TableReader_115(Probe) | 4470018.16 | 4543127 | root | | time:6.23s, loops:4423, cop_task: {num: 511, max: 2.82s, min: 5.23ms, avg: 630.4ms, p95: 1.59s, max_proc_keys: 330720, p95_proc_keys: 217056, tot_proc: 4m21.1s, tot_wait: 38.1s, rpc_num: 511, rpc_time: 5m22.1s, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_114 | 10.0 MB | N/A |
| │ └─Selection_114 | 4470018.16 | 4543127 | cop[tikv] | | tikv_task:{proc max:2.62s, min:1ms, avg: 509.7ms, p80:939ms, p95:1.39s, iters:31328, tasks:511}, scan_detail: {total_process_keys: 29955968, total_process_keys_size: 4549646548, total_keys: 29956479, get_snapshot_time: 9.22ms, rocksdb: {key_skipped_count: 29955968, block: {cache_hit_count: 2791, read_count: 75852, read_byte: 1.27 GB, read_time: 1m6.9s}}} | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01) | N/A | N/A |
| │ └─TableFullScan_113 | 29955968.00 | 29955968 | cop[tikv] | table:orders | tikv_task:{proc max:2.59s, min:1ms, avg: 506.5ms, p80:934ms, p95:1.38s, iters:31328, tasks:511} | keep order:false | N/A | N/A |
| └─TableReader_56(Probe) | 4470018.16 | 18173018 | root | | time:19m20s, loops:18599, cop_task: {num: 6823, max: 2.54s, min: 1.75ms, avg: 561.3ms, p95: 1.21s, max_proc_keys: 32095, p95_proc_keys: 9184, tot_proc: 46m17.5s, tot_wait: 14m4.4s, rpc_num: 6823, rpc_time: 1h3m49.5s, copr_cache: disabled, distsql_concurrency: 15} | data:TableRangeScan_55 | N/A | N/A |
| └─TableRangeScan_55 | 4470018.16 | 18173018 | cop[tikv] | table:lineitem | tikv_task:{proc max:2.24s, min:0s, avg: 407.2ms, p80:651ms, p95:1.01s, iters:43509, tasks:6823}, scan_detail: {total_process_keys: 18173018, total_process_keys_size: 3569988017, total_keys: 22721856, get_snapshot_time: 148.7ms, rocksdb: {key_skipped_count: 18173018, block: {cache_hit_count: 26980596, read_count: 368157, read_byte: 5.65 GB, read_time: 12m8.3s}}} | range: decided by [eq(tpch.lineitem.l_orderkey, tpch.orders.o_orderkey)], keep order:false | N/A | N/A |
+------------------------------------------------+-------------+----------+-----------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+---------+
23 rows in set (4 min 5.83 sec)
explain select /*+ LEADING(region,nation,customer,orders,lineitem,supplier) */
n_name,
sum(l_extendedprice * (1 - l_discount)) as revenue
from
customer,
orders,
lineitem,
supplier,
nation,
region
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = n_nationkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'ASIA'
and o_orderdate >= date '1994-01-01'
and o_orderdate < date '1994-01-01' + interval '1' year
group by
n_name
order by
revenue desc;
+------------------------------------------------+-------------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------------+-------------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_16 | 20.00 | root | | Column#48:desc |
| └─Projection_18 | 20.00 | root | | tpch.nation.n_name, Column#48 |
| └─HashAgg_19 | 20.00 | root | | group by:Column#51, funcs:sum(Column#49)->Column#48, funcs:firstrow(Column#50)->tpch.nation.n_name |
| └─Projection_126 | 4545.43 | root | | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name |
| └─HashJoin_31 | 4545.43 | root | | inner join, equal:[eq(tpch.nation.n_regionkey, tpch.region.r_regionkey)] |
| ├─TableReader_124(Build) | 0.01 | root | | data:Selection_123 |
| │ └─Selection_123 | 0.01 | cop[tikv] | | eq(tpch.region.r_name, "ASIA") |
| │ └─TableFullScan_122 | 5.00 | cop[tikv] | table:region | keep order:false, stats:pseudo |
| └─HashJoin_41(Probe) | 18181718.62 | root | | inner join, equal:[eq(tpch.customer.c_nationkey, tpch.nation.n_nationkey) eq(tpch.supplier.s_nationkey, tpch.nation.n_nationkey)] |
| ├─TableReader_121(Build) | 25.00 | root | | data:TableFullScan_120 |
| │ └─TableFullScan_120 | 25.00 | cop[tikv] | table:nation | keep order:false, stats:pseudo |
| └─HashJoin_52(Probe) | 18181718.62 | root | | inner join, equal:[eq(tpch.lineitem.l_suppkey, tpch.supplier.s_suppkey)] |
| ├─TableReader_119(Build) | 200000.00 | root | | data:TableFullScan_118 |
| │ └─TableFullScan_118 | 200000.00 | cop[tikv] | table:supplier | keep order:false |
| └─IndexHashJoin_61(Probe) | 17905356.64 | root | | inner join, inner:TableReader_56, outer key:tpch.orders.o_orderkey, inner key:tpch.lineitem.l_orderkey, equal cond:eq(tpch.orders.o_orderkey, tpch.lineitem.l_orderkey) |
| ├─HashJoin_104(Build) | 4470018.16 | root | | inner join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] |
| │ ├─TableReader_112(Build) | 3000000.00 | root | | data:TableFullScan_111 |
| │ │ └─TableFullScan_111 | 3000000.00 | cop[tikv] | table:customer | keep order:false |
| │ └─TableReader_115(Probe) | 4470018.16 | root | | data:Selection_114 |
| │ └─Selection_114 | 4470018.16 | cop[tikv] | | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01) |
| │ └─TableFullScan_113 | 29955968.00 | cop[tikv] | table:orders | keep order:false |
| └─TableReader_56(Probe) | 4470018.16 | root | | data:TableRangeScan_55 |
| └─TableRangeScan_55 | 4470018.16 | cop[tikv] | table:lineitem | range: decided by [eq(tpch.lineitem.l_orderkey, tpch.orders.o_orderkey)], keep order:false |
+------------------------------------------------+-------------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
23 rows in set (0.00 sec)
参考官方文档的：
LEADING hint 在以下情况下会失效：
- 指定了多个
LEADINGhint LEADINGhint 中指定的表名不存在LEADINGhint 中指定了重复的表名- 优化器无法按照
LEADINGhint 指定的顺序进行表连接 - 已经存在
straight_join()hint - 查询语句中包含 outer join 且同时指定了包含笛卡尔积的情况
hint 没生效，而且没 warning ，你进 mysql 的时候加 -c 选项了吗？
order表有where条件吧，要先走selection
可能是语法不兼容？
mysql客户端连接时使用了—comments选项吗？没有使用，默认会当作普通注释，hints不生效。