【 TiDB 使用环境】测试
【 TiDB 版本】5.7.25-TiDB-v6.5.2
在测试TPCH的Q5语句时候发现执行的特别慢，主要原因怀疑有2点：
1、join reorder 没有对等价关联条件做转换。
2、优化器并没有自动选择成本更低的执行路径。
TPCH sf=30，Q5的语句和执行计划如下：
select
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 = s_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;
--执行计划如下（explain format=verbose <sql>）：
+----------------------------------------------------+--------------+----------------+-----------+----------------+---------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | estCost | task | access object | operator info |
+----------------------------------------------------+--------------+----------------+-----------+----------------+---------------------------------------------------------------------------------------------------------------------------------+
| Sort_24 | 5.00 | 19000486279.90 | root | | Column#48:desc |
| └─Projection_26 | 5.00 | 19000485635.58 | root | | tpch.nation.n_name, Column#48 |
| └─HashAgg_27 | 5.00 | 19000485634.58 | root | | group by:Column#51, funcs:sum(Column#49)->Column#48, funcs:firstrow(Column#50)->tpch.nation.n_name |
| └─Projection_85 | 6907156.71 | 17688680515.14 | root | | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name |
| └─Projection_28 | 6907156.71 | 17618368422.68 | root | | tpch.lineitem.l_extendedprice, tpch.lineitem.l_discount, tpch.nation.n_name |
| └─HashJoin_37 | 6907156.71 | 17616300419.96 | root | | inner join, equal:[eq(tpch.orders.o_custkey, tpch.customer.c_custkey) eq(tpch.supplier.s_nationkey, tpch.customer.c_nationkey)] |
| ├─TableReader_83(Build) | 4500000.00 | 137274545.64 | root | | data:TableFullScan_82 |
| │ └─TableFullScan_82 | 4500000.00 | 1488878184.55 | cop[tikv] | table:customer | keep order:false |
| └─HashJoin_51(Probe) | 6845023.00 | 16571634388.70 | root | | inner join, equal:[eq(tpch.lineitem.l_orderkey, tpch.orders.o_orderkey)] |
| ├─TableReader_81(Build) | 6845023.00 | 1344074466.68 | root | | data:Selection_80 |
| │ └─Selection_80 | 6845023.00 | 18860015028.30 | cop[tikv] | | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01) |
| │ └─TableFullScan_79 | 45000000.00 | 14369015028.30 | cop[tikv] | table:orders | keep order:false |
| └─HashJoin_54(Probe) | 26520727.87 | 13949359180.45 | root | | inner join, equal:[eq(tpch.supplier.s_suppkey, tpch.lineitem.l_suppkey)] |
| ├─HashJoin_56(Build) | 43848.00 | 15030223.79 | root | | inner join, equal:[eq(tpch.nation.n_nationkey, tpch.supplier.s_nationkey)] |
| │ ├─HashJoin_69(Build) | 5.00 | 3005.46 | root | | inner join, equal:[eq(tpch.region.r_regionkey, tpch.nation.n_regionkey)] |
| │ │ ├─TableReader_74(Build) | 1.00 | 119.34 | root | | data:Selection_73 |
| │ │ │ └─Selection_73 | 1.00 | 1675.92 | cop[tikv] | | eq(tpch.region.r_name, "ASIA") |
| │ │ │ └─TableFullScan_72 | 5.00 | 1426.42 | cop[tikv] | table:region | keep order:false |
| │ │ └─TableReader_71(Probe) | 25.00 | 784.55 | root | | data:TableFullScan_70 |
| │ │ └─TableFullScan_70 | 25.00 | 7306.27 | cop[tikv] | table:nation | keep order:false |
| │ └─TableReader_76(Probe) | 300000.00 | 9037189.34 | root | | data:TableFullScan_75 |
| │ └─TableFullScan_75 | 300000.00 | 97541840.03 | cop[tikv] | table:supplier | keep order:false |
| └─TableReader_78(Probe) | 179998372.00 | 10336824370.54 | root | | data:TableFullScan_77 |
| └─TableFullScan_77 | 179998372.00 | 63814790758.73 | cop[tikv] | table:lineitem | keep order:false |
+----------------------------------------------------+--------------+----------------+-----------+----------------+---------------------------------------------------------------------------------------------------------------------------------+
24 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------+
| Note | 1105 | [customer] remain after pruning paths for customer given Prop{SortItems: [], TaskTp: rootTask} |
| Note | 1105 | [lineitem] remain after pruning paths for lineitem given Prop{SortItems: [], TaskTp: rootTask} |
+-------+------+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
--相关执行信息如下：
+----------------------------------------------------+--------------+-----------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------------------+--------------+-----------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+
| Sort_24 | 5.00 | 5 | root | | time:6m39.5s, loops:2 | Column#48:desc | 2.06 KB | 0 Bytes |
| └─Projection_26 | 5.00 | 5 | root | | time:6m39.5s, loops:2, Concurrency:OFF | tpch.nation.n_name, Column#48 | 2.02 KB | N/A |
| └─HashAgg_27 | 5.00 | 5 | root | | time:6m39.5s, 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_85 | 6907156.71 | 218784 | root | | time:6m39.4s, loops:217, Concurrency:5 | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name | 968.9 KB | N/A |
| └─Projection_28 | 6907156.71 | 218784 | root | | time:6m39.5s, loops:217, Concurrency:5 | tpch.lineitem.l_extendedprice, tpch.lineitem.l_discount, tpch.nation.n_name | 1.05 MB | N/A |
| └─HashJoin_37 | 6907156.71 | 218784 | root | | time:6m39.5s, loops:217, build_hash_table:{total:7.49s, fetch:957.5ms, build:6.53s}, probe:{concurrency:5, total:33m17.3s, max:6m39.5s, probe:9.86s, fetch:33m7.4s} | inner join, equal:[eq(tpch.orders.o_custkey, tpch.customer.c_custkey) eq(tpch.supplier.s_nationkey, tpch.customer.c_nationkey)] | 424.6 MB | 0 Bytes |
| ├─TableReader_83(Build) | 4500000.00 | 4500000 | root | | time:214.6ms, loops:4404, cop_task: {num: 170, max: 656.5ms, min: 1.44ms, avg: 102.1ms, p95: 310ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 10.6s, tot_wait: 2.58s, rpc_num: 170, rpc_time: 17.3s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_82 | 8.43 MB | N/A |
| │ └─TableFullScan_82 | 4500000.00 | 4500000 | cop[tikv] | table:customer | tikv_task:{proc max:552ms, min:0s, avg: 61.2ms, p80:113ms, p95:223ms, iters:5066, tasks:170}, scan_detail: {total_process_keys: 4500000, total_process_keys_size: 915730563, total_keys: 4505296, get_snapshot_time: 41.3ms, rocksdb: {delete_skipped_count: 11269, key_skipped_count: 4505126, block: {cache_hit_count: 1485, read_count: 14985, read_byte: 335.9 MB, read_time: 324.5ms}}} | keep order:false | N/A | N/A |
| └─HashJoin_51(Probe) | 6845023.00 | 5476462 | root | | time:6m39.4s, loops:5352, build_hash_table:{total:11.6s, fetch:3.71s, build:7.84s}, probe:{concurrency:5, total:33m17.2s, max:6m39.5s, probe:4m55.8s, fetch:28m21.4s} | inner join, equal:[eq(tpch.lineitem.l_orderkey, tpch.orders.o_orderkey)] | 591.6 MB | 364.5 MB |
| ├─TableReader_81(Build) | 6845023.00 | 6825548 | root | | time:3.35s, loops:6645, cop_task: {num: 770, max: 1.03s, min: 1.22ms, avg: 136.9ms, p95: 481.3ms, max_proc_keys: 312379, p95_proc_keys: 217056, tot_proc: 1m18.7s, tot_wait: 8.73s, rpc_num: 770, rpc_time: 1m45.4s, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_80 | 9.03 MB | N/A |
| │ └─Selection_80 | 6845023.00 | 6825548 | cop[tikv] | | tikv_task:{proc max:993ms, min:0s, avg: 100.3ms, p80:190ms, p95:409ms, iters:47069, tasks:770}, scan_detail: {total_process_keys: 45000000, total_process_keys_size: 6834773077, total_keys: 45000770, get_snapshot_time: 436.2ms, rocksdb: {key_skipped_count: 45000000, block: {cache_hit_count: 6752, read_count: 113731, read_byte: 2.05 GB, read_time: 1.17s}}} | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01) | N/A | N/A |
| │ └─TableFullScan_79 | 45000000.00 | 45000000 | cop[tikv] | table:orders | tikv_task:{proc max:985ms, min:0s, avg: 95.3ms, p80:178ms, p95:403ms, iters:47069, tasks:770} | keep order:false | N/A | N/A |
| └─HashJoin_54(Probe) | 26520727.87 | 36106879 | root | | time:6m29s, loops:35265, build_hash_table:{total:1.57s, fetch:1.52s, build:58.1ms}, probe:{concurrency:5, total:33m16.9s, max:6m39.4s, probe:32m7.5s, fetch:1m9.4s} | inner join, equal:[eq(tpch.supplier.s_suppkey, tpch.lineitem.l_suppkey)] | 5.31 MB | 3.11 MB |
| ├─HashJoin_56(Build) | 43848.00 | 60186 | root | | time:1.57s, loops:64, build_hash_table:{total:196.9µs, fetch:194.5µs, build:2.43µs}, probe:{concurrency:5, total:7.87s, max:1.57s, probe:325.6ms, fetch:7.54s} | inner join, equal:[eq(tpch.nation.n_nationkey, tpch.supplier.s_nationkey)] | 2.21 KB | 191 Bytes |
| │ ├─HashJoin_69(Build) | 5.00 | 5 | root | | time:173.5µs, loops:2, build_hash_table:{total:85.9µs, fetch:80.2µs, build:5.71µs}, probe:{concurrency:5, total:269.6µs, max:62.1µs, probe:20.9µs, fetch:248.6µs} | inner join, equal:[eq(tpch.region.r_regionkey, tpch.nation.n_regionkey)] | 25.7 KB | 36 Bytes |
| │ │ ├─TableReader_74(Build) | 1.00 | 1 | root | | time:55.4µs, loops:2, cop_task: {num: 1, max: 990.3µs, proc_keys: 5, rpc_num: 1, rpc_time: 956.5µs, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_73 | 267 Bytes | N/A |
| │ │ │ └─Selection_73 | 1.00 | 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: 191.4µs, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 9}}} | eq(tpch.region.r_name, "ASIA") | N/A | N/A |
| │ │ │ └─TableFullScan_72 | 5.00 | 5 | cop[tikv] | table:region | tikv_task:{time:0s, loops:1} | keep order:false | N/A | N/A |
| │ │ └─TableReader_71(Probe) | 25.00 | 25 | root | | time:42.4µs, loops:2, cop_task: {num: 1, max: 1.09ms, proc_keys: 25, rpc_num: 1, rpc_time: 1.07ms, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_70 | 1.00 KB | N/A |
| │ │ └─TableFullScan_70 | 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: 107.2µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 9}}} | keep order:false | N/A | N/A |
| │ └─TableReader_76(Probe) | 300000.00 | 300000 | root | | time:1.53s, loops:295, cop_task: {num: 14, max: 410.3ms, min: 1.67ms, avg: 112.1ms, p95: 410.3ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 956ms, tot_wait: 397ms, rpc_num: 14, rpc_time: 1.57s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_75 | 1.53 MB | N/A |
| │ └─TableFullScan_75 | 300000.00 | 300000 | cop[tikv] | table:supplier | tikv_task:{proc max:331ms, min:0s, avg: 62.4ms, p80:114ms, p95:331ms, iters:348, tasks:14}, scan_detail: {total_process_keys: 300000, total_process_keys_size: 54436630, total_keys: 300014, get_snapshot_time: 14.9ms, rocksdb: {key_skipped_count: 300000, block: {cache_hit_count: 122, read_count: 898, read_byte: 20.7 MB, read_time: 8.55ms}}} | keep order:false | N/A | N/A |
| └─TableReader_78(Probe) | 179998372.00 | 179998372 | root | | time:3.45s, loops:176140, cop_task: {num: 6613, max: 606.7ms, min: 776.4µs, avg: 70.1ms, p95: 164.8ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 5m14.1s, tot_wait: 10.8s, rpc_num: 6613, rpc_time: 7m42.8s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_77 | 73.5 MB | N/A |
| └─TableFullScan_77 | 179998372.00 | 179998372 | cop[tikv] | table:lineitem | tikv_task:{proc max:529ms, min:0s, avg: 37.9ms, p80:66ms, p95:105ms, iters:201899, tasks:6613}, scan_detail: {total_process_keys: 179998372, total_process_keys_size: 35379740257, total_keys: 180004985, get_snapshot_time: 1.67s, rocksdb: {key_skipped_count: 179998372, block: {cache_hit_count: 78559, read_count: 537544, read_byte: 8.30 GB, read_time: 8.09s}}} | keep order:false | N/A | N/A |
+----------------------------------------------------+--------------+-----------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------+-----------+-----------+
24 rows in set (6 min 39.50 sec)
该语句总的cost为：19000486279.90 ，语句执行耗时6分多钟，从上面执行信息中可以看出最耗时的部分是：HashJoin_54，且主要时间花费在hashjoin的probe端，也就是TableReader_78，对lineitem的读取耗时最高。
分析该SQL语句，经过region的r_name = 'ASIA’的过滤数据量为原来的20%，orders的o_orderdate条件过滤orders表数据量由总的45000000变为6845023，过滤后数据量为原来的15%，因此如果先走这两个过滤条件再和lineitem表走inl_join，那么效率可能会更高。根据关联条件表的选择路径为：region,nation,customer,orders,lineitem,supplier。
mysql> explain select count(*) from region where r_name = 'ASIA';
+----------------------------+---------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+---------+-----------+---------------+--------------------------------+
| StreamAgg_10 | 1.00 | root | | funcs:count(1)->Column#4 |
| └─TableReader_17 | 1.00 | root | | data:Selection_16 |
| └─Selection_16 | 1.00 | cop[tikv] | | eq(tpch.region.r_name, "ASIA") |
| └─TableFullScan_15 | 5.00 | cop[tikv] | table:region | keep order:false |
+----------------------------+---------+-----------+---------------+--------------------------------+
4 rows in set (0.01 sec)
mysql> explain select count(*) from orders where o_orderdate >= date '1994-01-01' and o_orderdate < date '1994-01-01' + interval '1' year;
+------------------------------+-------------+-----------+---------------+----------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+-------------+-----------+---------------+----------------------------------------------------------------------------------+
| StreamAgg_20 | 1.00 | root | | funcs:count(Column#12)->Column#10 |
| └─TableReader_21 | 1.00 | root | | data:StreamAgg_9 |
| └─StreamAgg_9 | 1.00 | cop[tikv] | | funcs:count(1)->Column#12 |
| └─Selection_19 | 6845023.00 | cop[tikv] | | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01) |
| └─TableFullScan_18 | 45000000.00 | cop[tikv] | table:orders | keep order:false |
+------------------------------+-------------+-----------+---------------+----------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
强制执行region,nation,customer,orders,lineitem,supplier的执行路径观察执行计划：
explain format=verbose
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 = s_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 | estCost | task | access object | operator info |
+----------------------------------------------------+--------------+----------------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_22 | 5.00 | 54405488396.22 | root | | Column#48:desc |
| └─Projection_24 | 5.00 | 54405487751.90 | root | | tpch.nation.n_name, Column#48 |
| └─HashAgg_25 | 5.00 | 54405487750.90 | root | | group by:Column#51, funcs:sum(Column#49)->Column#48, funcs:firstrow(Column#50)->tpch.nation.n_name |
| └─Projection_81 | 141292146.77 | 27571365950.04 | root | | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name |
| └─Projection_26 | 141292146.77 | 26133068412.75 | root | | tpch.lineitem.l_extendedprice, tpch.lineitem.l_discount, tpch.nation.n_name |
| └─HashJoin_35 | 141292146.77 | 26090765544.01 | root | | inner join, equal:[eq(tpch.lineitem.l_suppkey, tpch.supplier.s_suppkey) eq(tpch.customer.c_nationkey, tpch.supplier.s_nationkey) eq(tpch.nation.n_nationkey, tpch.supplier.s_nationkey)] |
| ├─TableReader_79(Build) | 300000.00 | 9037189.34 | root | | data:TableFullScan_78 |
| │ └─TableFullScan_78 | 300000.00 | 97541840.03 | cop[tikv] | table:supplier | keep order:false |
| └─IndexHashJoin_44(Probe) | 140161809.60 | 20424667418.49 | root | | inner join, inner:TableReader_39, outer key:tpch.orders.o_orderkey, inner key:tpch.lineitem.l_orderkey, equal cond:eq(tpch.orders.o_orderkey, tpch.lineitem.l_orderkey) |
| ├─HashJoin_49(Build) | 34535783.56 | 2724210810.47 | root | | inner join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] |
| │ ├─TableReader_75(Build) | 6845023.00 | 1344074466.68 | root | | data:Selection_74 |
| │ │ └─Selection_74 | 6845023.00 | 18860015028.30 | cop[tikv] | | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01) |
| │ │ └─TableFullScan_73 | 45000000.00 | 14369015028.30 | cop[tikv] | table:orders | keep order:false |
| │ └─HashJoin_52(Probe) | 22500000.00 | 182189330.59 | root | | CARTESIAN inner join |
| │ ├─HashJoin_65(Build) | 5.00 | 3005.46 | root | | inner join, equal:[eq(tpch.region.r_regionkey, tpch.nation.n_regionkey)] |
| │ │ ├─TableReader_70(Build) | 1.00 | 119.34 | root | | data:Selection_69 |
| │ │ │ └─Selection_69 | 1.00 | 1675.92 | cop[tikv] | | eq(tpch.region.r_name, "ASIA") |
| │ │ │ └─TableFullScan_68 | 5.00 | 1426.42 | cop[tikv] | table:region | keep order:false |
| │ │ └─TableReader_67(Probe) | 25.00 | 784.55 | root | | data:TableFullScan_66 |
| │ │ └─TableFullScan_66 | 25.00 | 7306.27 | cop[tikv] | table:nation | keep order:false |
| │ └─TableReader_72(Probe) | 4500000.00 | 137274545.64 | root | | data:TableFullScan_71 |
| │ └─TableFullScan_71 | 4500000.00 | 1488878184.55 | cop[tikv] | table:customer | keep order:false |
| └─TableReader_39(Probe) | 34535783.56 | 47.55 | root | | data:TableRangeScan_38 |
| └─TableRangeScan_38 | 34535783.56 | 333.12 | cop[tikv] | table:lineitem | range: decided by [eq(tpch.lineitem.l_orderkey, tpch.orders.o_orderkey)], keep order:false |
+----------------------------------------------------+--------------+----------------+-----------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------+
| Note | 1105 | [customer] remain after pruning paths for customer given Prop{SortItems: [], TaskTp: rootTask} |
+-------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
--该语句不进行执行，因为发生了笛卡尔积执行效率非常低下
经过强制指定连接顺序后，cost为：54405488396.22 大于未指定连接顺序时候的19000486279.90。通过观察上面的执行计划可以看到HashJoin_52这一步发生了笛卡尔积，原因是强制指定了region,nation,customer,orders,lineitem,supplier的连接顺序后region,nation输出的字段并没有和customer存在关联条件导致了笛卡尔积的产生，但是通过观察整体的关联条件：
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and l_suppkey = s_suppkey
and c_nationkey = s_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
可以看到customer的c_nationkey是和supplier的s_nationkey做的关联，在指定连接顺序后join reorder并没有对关联条件做等价转换即：and c_nationkey = s_nationkey 转变为：and c_nationkey = n_nationkey。
接下来我们手工对条件进行转换如下：
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
观察其执行计划：
explain format=verbose
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 | estCost | task | access object | operator info |
+------------------------------------------------------+-------------+----------------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Sort_22 | 5.00 | 11808740465.25 | root | | Column#48:desc |
| └─Projection_24 | 5.00 | 11808739820.93 | root | | tpch.nation.n_name, Column#48 |
| └─HashAgg_25 | 5.00 | 11808739819.93 | root | | group by:Column#51, funcs:sum(Column#49)->Column#48, funcs:firstrow(Column#50)->tpch.nation.n_name |
| └─Projection_81 | 28004229.08 | 6490191373.94 | root | | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name |
| └─Projection_26 | 28004229.08 | 6205119523.56 | root | | tpch.lineitem.l_extendedprice, tpch.lineitem.l_discount, tpch.nation.n_name |
| └─HashJoin_35 | 28004229.08 | 6196735057.37 | root | | inner join, equal:[eq(tpch.lineitem.l_suppkey, tpch.supplier.s_suppkey) eq(tpch.nation.n_nationkey, tpch.supplier.s_nationkey)] |
| ├─TableReader_79(Build) | 300000.00 | 9037189.34 | root | | data:TableFullScan_78 |
| │ └─TableFullScan_78 | 300000.00 | 97541840.03 | cop[tikv] | table:supplier | keep order:false |
| └─IndexHashJoin_44(Probe) | 27780195.25 | 5309127325.23 | root | | inner join, inner:TableReader_39, outer key:tpch.orders.o_orderkey, inner key:tpch.lineitem.l_orderkey, equal cond:eq(tpch.orders.o_orderkey, tpch.lineitem.l_orderkey) |
| ├─HashJoin_50(Build) | 6845023.00 | 1803070298.85 | root | | inner join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] |
| │ ├─HashJoin_52(Build) | 882112.00 | 227099580.09 | root | | inner join, equal:[eq(tpch.nation.n_nationkey, tpch.customer.c_nationkey)] |
| │ │ ├─HashJoin_65(Build) | 5.00 | 3005.46 | root | | inner join, equal:[eq(tpch.region.r_regionkey, tpch.nation.n_regionkey)] |
| │ │ │ ├─TableReader_70(Build) | 1.00 | 119.34 | root | | data:Selection_69 |
| │ │ │ │ └─Selection_69 | 1.00 | 1675.92 | cop[tikv] | | eq(tpch.region.r_name, "ASIA") |
| │ │ │ │ └─TableFullScan_68 | 5.00 | 1426.42 | cop[tikv] | table:region | keep order:false |
| │ │ │ └─TableReader_67(Probe) | 25.00 | 784.55 | root | | data:TableFullScan_66 |
| │ │ │ └─TableFullScan_66 | 25.00 | 7306.27 | cop[tikv] | table:nation | keep order:false |
| │ │ └─TableReader_72(Probe) | 4500000.00 | 137274545.64 | root | | data:TableFullScan_71 |
| │ │ └─TableFullScan_71 | 4500000.00 | 1488878184.55 | cop[tikv] | table:customer | keep order:false |
| │ └─TableReader_75(Probe) | 6845023.00 | 1344074466.68 | root | | data:Selection_74 |
| │ └─Selection_74 | 6845023.00 | 18860015028.30 | cop[tikv] | | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01) |
| │ └─TableFullScan_73 | 45000000.00 | 14369015028.30 | cop[tikv] | table:orders | keep order:false |
| └─TableReader_39(Probe) | 6845023.00 | 47.55 | root | | data:TableRangeScan_38 |
| └─TableRangeScan_38 | 6845023.00 | 333.12 | cop[tikv] | table:lineitem | range: decided by [eq(tpch.lineitem.l_orderkey, tpch.orders.o_orderkey)], keep order:false |
+------------------------------------------------------+-------------+----------------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------+
| Note | 1105 | [customer] remain after pruning paths for customer given Prop{SortItems: [], TaskTp: rootTask} |
+-------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到走了理想的执行计划，观察其执行耗时：
+------------------------------------------------------+-------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------------------+-------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Sort_22 | 5.00 | 5 | root | | time:34.7s, loops:2 | Column#48:desc | 2.69 KB | 0 Bytes |
| └─Projection_24 | 5.00 | 5 | root | | time:34.7s, loops:2, Concurrency:OFF | tpch.nation.n_name, Column#48 | 2.65 KB | N/A |
| └─HashAgg_25 | 5.00 | 5 | root | | time:34.7s, 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_81 | 28004229.08 | 218784 | root | | time:34.5s, loops:217, Concurrency:5 | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name | 968.9 KB | N/A |
| └─Projection_26 | 28004229.08 | 218784 | root | | time:34.7s, loops:217, Concurrency:5 | tpch.lineitem.l_extendedprice, tpch.lineitem.l_discount, tpch.nation.n_name | 1.05 MB | N/A |
| └─HashJoin_35 | 28004229.08 | 218784 | root | | time:34.7s, loops:217, build_hash_table:{total:701.8ms, fetch:463.7ms, build:238.1ms}, probe:{concurrency:5, total:2m53.4s, max:34.7s, probe:5.36s, fetch:2m48s} | inner join, equal:[eq(tpch.lineitem.l_suppkey, tpch.supplier.s_suppkey) eq(tpch.nation.n_nationkey, tpch.supplier.s_nationkey)] | 27.1 MB | 0 Bytes |
| ├─TableReader_79(Build) | 300000.00 | 300000 | root | | time:462.4ms, loops:295, cop_task: {num: 14, max: 119.7ms, min: 897.9µs, avg: 44.7ms, p95: 119.7ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 420ms, tot_wait: 20ms, rpc_num: 14, rpc_time: 625.8ms, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_78 | 1.53 MB | N/A |
| │ └─TableFullScan_78 | 300000.00 | 300000 | cop[tikv] | table:supplier | tikv_task:{proc max:115ms, min:0s, avg: 27.4ms, p80:65ms, p95:115ms, iters:348, tasks:14}, scan_detail: {total_process_keys: 300000, total_process_keys_size: 54436630, total_keys: 300014, get_snapshot_time: 588.7µs, rocksdb: {key_skipped_count: 300000, block: {cache_hit_count: 132, read_count: 888, read_byte: 20.4 MB, read_time: 8.52ms}}} | keep order:false | N/A | N/A |
| └─IndexHashJoin_44(Probe) | 27780195.25 | 5450573 | root | | time:34.6s, loops:5326, inner:{total:2m43.5s, concurrency:5, task:61, construct:2.62s, fetch:2m33.7s, build:575.6ms, join:7.17s} | inner join, inner:TableReader_39, outer key:tpch.orders.o_orderkey, inner key:tpch.lineitem.l_orderkey, equal cond:eq(tpch.orders.o_orderkey, tpch.lineitem.l_orderkey) | 67.7 MB | N/A |
| ├─HashJoin_50(Build) | 6845023.00 | 1362236 | root | | time:17.5s, loops:1335, build_hash_table:{total:1.41s, fetch:519.1ms, build:887.5ms}, probe:{concurrency:5, total:2m41.5s, max:32.3s, probe:1m26.5s, fetch:1m15s} | inner join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] | 77.5 MB | 0 Bytes |
| │ ├─HashJoin_52(Build) | 882112.00 | 899362 | root | | time:406.7ms, loops:883, build_hash_table:{total:30ms, fetch:30ms, build:6.66µs}, probe:{concurrency:5, total:7.02s, max:1.41s, probe:5.44s, fetch:1.58s} | inner join, equal:[eq(tpch.nation.n_nationkey, tpch.customer.c_nationkey)] | 2.21 KB | 0 Bytes |
| │ │ ├─HashJoin_65(Build) | 5.00 | 5 | root | | time:30ms, loops:2, build_hash_table:{total:29.9ms, fetch:29.9ms, build:7.93µs}, probe:{concurrency:5, total:149.5ms, max:29.9ms, probe:37.4µs, fetch:149.5ms} | inner join, equal:[eq(tpch.region.r_regionkey, tpch.nation.n_regionkey)] | 25.7 KB | 0 Bytes |
| │ │ │ ├─TableReader_70(Build) | 1.00 | 1 | root | | time:29.8ms, loops:2, cop_task: {num: 1, max: 30.8ms, proc_keys: 5, rpc_num: 1, rpc_time: 30.7ms, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_69 | 269 Bytes | N/A |
| │ │ │ │ └─Selection_69 | 1.00 | 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: 229.8µs, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 9}}} | eq(tpch.region.r_name, "ASIA") | N/A | N/A |
| │ │ │ │ └─TableFullScan_68 | 5.00 | 5 | cop[tikv] | table:region | tikv_task:{time:0s, loops:1} | keep order:false | N/A | N/A |
| │ │ │ └─TableReader_67(Probe) | 25.00 | 25 | root | | time:7.85ms, loops:2, cop_task: {num: 1, max: 8.72ms, proc_keys: 25, rpc_num: 1, rpc_time: 8.71ms, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_66 | 1.00 KB | N/A |
| │ │ │ └─TableFullScan_66 | 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: 519.1µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 9}}} | keep order:false | N/A | N/A |
| │ │ └─TableReader_72(Probe) | 4500000.00 | 4500000 | root | | time:258.4ms, loops:4405, cop_task: {num: 170, max: 177.1ms, min: 946.4µs, avg: 53.4ms, p95: 120.6ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 6.03s, tot_wait: 271ms, rpc_num: 170, rpc_time: 9.07s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_71 | 7.67 MB | N/A |
| │ │ └─TableFullScan_71 | 4500000.00 | 4500000 | cop[tikv] | table:customer | tikv_task:{proc max:168ms, min:0s, avg: 32.9ms, p80:61ms, p95:98ms, iters:5066, tasks:170}, scan_detail: {total_process_keys: 4500000, total_process_keys_size: 915730563, total_keys: 4505296, get_snapshot_time: 17.8ms, rocksdb: {delete_skipped_count: 11269, key_skipped_count: 4505126, block: {cache_hit_count: 1582, read_count: 14888, read_byte: 333.7 MB, read_time: 205.6ms}}} | keep order:false | N/A | N/A |
| │ └─TableReader_75(Probe) | 6845023.00 | 6825548 | root | | time:13.2s, loops:6645, cop_task: {num: 770, max: 2.04s, min: 4.07ms, avg: 355.7ms, p95: 924.9ms, max_proc_keys: 312379, p95_proc_keys: 217056, tot_proc: 3m29.3s, tot_wait: 42.9s, rpc_num: 770, rpc_time: 4m33.8s, copr_cache: disabled, distsql_concurrency: 15} | data:Selection_74 | 7.66 MB | N/A |
| │ └─Selection_74 | 6845023.00 | 6825548 | cop[tikv] | | tikv_task:{proc max:1.94s, min:0s, avg: 270.8ms, p80:477ms, p95:814ms, iters:47069, tasks:770}, scan_detail: {total_process_keys: 45000000, total_process_keys_size: 6834773077, total_keys: 45000770, get_snapshot_time: 2.99s, rocksdb: {key_skipped_count: 45000000, block: {cache_hit_count: 6885, read_count: 113598, read_byte: 2.05 GB, read_time: 1.57s}}} | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01) | N/A | N/A |
| │ └─TableFullScan_73 | 45000000.00 | 45000000 | cop[tikv] | table:orders | tikv_task:{proc max:1.94s, min:0s, avg: 265.1ms, p80:467ms, p95:808ms, iters:47069, tasks:770} | keep order:false | N/A | N/A |
| └─TableReader_39(Probe) | 6845023.00 | 5450573 | root | | time:2m30.6s, loops:5772, cop_task: {num: 4974, max: 1.55s, min: 8.45ms, avg: 287.2ms, p95: 608.2ms, max_proc_keys: 5088, p95_proc_keys: 3040, tot_proc: 16m42.2s, tot_wait: 4m59.2s, rpc_num: 4974, rpc_time: 23m48.6s, copr_cache: disabled, distsql_concurrency: 15} | data:TableRangeScan_38 | N/A | N/A |
| └─TableRangeScan_38 | 6845023.00 | 5450573 | cop[tikv] | table:lineitem | tikv_task:{proc max:1.47s, min:0s, avg: 200.9ms, p80:323ms, p95:527ms, iters:23217, tasks:4974}, scan_detail: {total_process_keys: 5450573, total_process_keys_size: 1071363879, total_keys: 6816602, get_snapshot_time: 21s, rocksdb: {key_skipped_count: 5450573, block: {cache_hit_count: 11799375, read_count: 511690, read_byte: 7.90 GB, read_time: 8.06s}}} | range: decided by [eq(tpch.lineitem.l_orderkey, tpch.orders.o_orderkey)], keep order:false | N/A | N/A |
+------------------------------------------------------+-------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
24 rows in set (34.68 sec)
34秒即完成了查询，且cost为：11808740465.25 小于优化器默认选择的执行成本。既然优化器不会对关联条件 c_nationkey = s_nationkey做转换，那么我们人工做转换成：c_nationkey = n_nationkey，那么在不指定表连接顺序后优化器的选择路径时怎么样的呢？
explain format=verbose
select
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 | estCost | task | access object | operator info |
+----------------------------------------------------+--------------+----------------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------+
| Sort_25 | 5.00 | 19000486279.90 | root | | Column#48:desc |
| └─Projection_27 | 5.00 | 19000485635.58 | root | | tpch.nation.n_name, Column#48 |
| └─HashAgg_28 | 5.00 | 19000485634.58 | root | | group by:Column#51, funcs:sum(Column#49)->Column#48, funcs:firstrow(Column#50)->tpch.nation.n_name |
| └─Projection_86 | 6907156.71 | 17688680515.14 | root | | mul(tpch.lineitem.l_extendedprice, minus(1, tpch.lineitem.l_discount))->Column#49, tpch.nation.n_name, tpch.nation.n_name |
| └─Projection_29 | 6907156.71 | 17618368422.68 | root | | tpch.lineitem.l_extendedprice, tpch.lineitem.l_discount, tpch.nation.n_name |
| └─HashJoin_38 | 6907156.71 | 17616300419.96 | root | | inner join, equal:[eq(tpch.orders.o_custkey, tpch.customer.c_custkey) eq(tpch.nation.n_nationkey, tpch.customer.c_nationkey)] |
| ├─TableReader_84(Build) | 4500000.00 | 137274545.64 | root | | data:TableFullScan_83 |
| │ └─TableFullScan_83 | 4500000.00 | 1488878184.55 | cop[tikv] | table:customer | keep order:false |
| └─HashJoin_52(Probe) | 6845023.00 | 16571634388.70 | root | | inner join, equal:[eq(tpch.lineitem.l_orderkey, tpch.orders.o_orderkey)] |
| ├─TableReader_82(Build) | 6845023.00 | 1344074466.68 | root | | data:Selection_81 |
| │ └─Selection_81 | 6845023.00 | 18860015028.30 | cop[tikv] | | ge(tpch.orders.o_orderdate, 1994-01-01), lt(tpch.orders.o_orderdate, 1995-01-01) |
| │ └─TableFullScan_80 | 45000000.00 | 14369015028.30 | cop[tikv] | table:orders | keep order:false |
| └─HashJoin_55(Probe) | 26520727.87 | 13949359180.45 | root | | inner join, equal:[eq(tpch.supplier.s_suppkey, tpch.lineitem.l_suppkey)] |
| ├─HashJoin_57(Build) | 43848.00 | 15030223.79 | root | | inner join, equal:[eq(tpch.nation.n_nationkey, tpch.supplier.s_nationkey)] |
| │ ├─HashJoin_70(Build) | 5.00 | 3005.46 | root | | inner join, equal:[eq(tpch.region.r_regionkey, tpch.nation.n_regionkey)] |
| │ │ ├─TableReader_75(Build) | 1.00 | 119.34 | root | | data:Selection_74 |
| │ │ │ └─Selection_74 | 1.00 | 1675.92 | cop[tikv] | | eq(tpch.region.r_name, "ASIA") |
| │ │ │ └─TableFullScan_73 | 5.00 | 1426.42 | cop[tikv] | table:region | keep order:false |
| │ │ └─TableReader_72(Probe) | 25.00 | 784.55 | root | | data:TableFullScan_71 |
| │ │ └─TableFullScan_71 | 25.00 | 7306.27 | cop[tikv] | table:nation | keep order:false |
| │ └─TableReader_77(Probe) | 300000.00 | 9037189.34 | root | | data:TableFullScan_76 |
| │ └─TableFullScan_76 | 300000.00 | 97541840.03 | cop[tikv] | table:supplier | keep order:false |
| └─TableReader_79(Probe) | 179998372.00 | 10336824370.54 | root | | data:TableFullScan_78 |
| └─TableFullScan_78 | 179998372.00 | 63814790758.73 | cop[tikv] | table:lineitem | keep order:false |
+----------------------------------------------------+--------------+----------------+-----------+----------------+-------------------------------------------------------------------------------------------------------------------------------+
24 rows in set, 2 warnings (0.01 sec)
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------+
| Note | 1105 | [customer] remain after pruning paths for customer given Prop{SortItems: [], TaskTp: rootTask} |
| Note | 1105 | [lineitem] remain after pruning paths for lineitem given Prop{SortItems: [], TaskTp: rootTask} |
+-------+------+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
可以看到即使手工做了c_nationkey关联条件的转换，但是优化器还是走了和原本Q5语句未指定hint时一样的执行计划，而且cost成本也是：19000486279.90 。该cost成本大于上面指定hint时的cost成本：11808740465.25。
因此，经过手工做关联条件的等价转换且指定表的连接顺序后，SQL执行耗时由6分39秒缩短为34秒，效率提升10多倍！。
所以有两个疑问：
1、为何优化器不对可以等价变换的关联条件做搜索路径呢？
2、为何优化器不选择成本更低的执行计划呢？