我简单测试了下，这两种方案都是可行的，你可以做下相关尝试，下面是我用tpch1的例子。
表结构：
mysql> show create table customer \G
*************************** 1. row ***************************
Table: customer
Create Table: 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 nation \G
*************************** 1. row ***************************
Table: nation
Create Table: CREATE TABLE `nation` (
`N_NATIONKEY` bigint(20) NOT NULL,
`N_NAME` char(25) NOT NULL,
`N_REGIONKEY` bigint(20) NOT NULL,
`N_COMMENT` varchar(152) DEFAULT NULL,
PRIMARY KEY (`N_NATIONKEY`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
mysql> show create table orders \G
*************************** 1. row ***************************
Table: orders
Create Table: 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 `idx1` (`O_CUSTKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)
通过改写SQL（将子查询前移到left join前面）或者高版本打开tidb_enable_outer_join_reorder都能走到理想执行计划，性能提升较大。
执行情况如下：
set tidb_enable_outer_join_reorder=OFF;
mysql> explain analyze select a.* ,b.* from customer a left join nation b on a.C_NATIONKEY=b.N_NATIONKEY where a.C_CUSTKEY in (select O_CUSTKEY from orders where O_ORDERKEY=1 limit 1);
+-------------------------------+-----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+---------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-------------------------------+-----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+---------+---------+
| HashJoin_19 | 1.00 | 1 | root | | time:100.1ms, loops:2, RU:176.777568, build_hash_table:{total:917.8µs, fetch:905.9µs, build:12µs}, probe:{concurrency:5, total:499.1ms, max:99.9ms, probe:8.13ms, fetch:490.9ms} | inner join, equal:[eq(tpch1.orders.o_custkey, tpch1.customer.c_custkey)] | 9.46 KB | 0 Bytes |
| ├─HashAgg_20(Build) | 1.00 | 1 | root | | time:872.2µs, loops:2 | group by:tpch1.orders.o_custkey, funcs:firstrow(tpch1.orders.o_custkey)->tpch1.orders.o_custkey | 8.99 KB | 0 Bytes |
| │ └─Limit_21 | 1.00 | 1 | root | | time:857.1µs, loops:2 | offset:0, count:1 | N/A | N/A |
| │ └─Point_Get_23 | 1.00 | 1 | root | table:orders | time:852.1µs, loops:1, Get:{num_rpc:1, total_time:809.5µs}, total_process_time: 80.3µs, total_wait_time: 163.1µs, tikv_wall_time: 334.1µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 134, total_keys: 1, get_snapshot_time: 13.7µs, rocksdb: {block: {cache_hit_count: 2}}} | handle:1 | N/A | N/A |
| └─HashJoin_33(Probe) | 150000.00 | 150000 | root | | time:99.8ms, loops:149, build_hash_table:{total:901.6µs, fetch:882.1µs, build:19.6µs}, probe:{concurrency:5, total:496.7ms, max:99.7ms, probe:92.5ms, fetch:404.2ms} | left outer join, equal:[eq(tpch1.customer.c_nationkey, tpch1.nation.n_nationkey)] | 50.2 KB | 0 Bytes |
| ├─TableReader_38(Build) | 25.00 | 25 | root | | time:827.7µs, loops:2, cop_task: {num: 1, max: 927.1µs, proc_keys: 25, tot_proc: 124.6µs, tot_wait: 114.6µs, rpc_num: 1, rpc_time: 901.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 9.15µs, max_distsql_concurrency: 1} | data:TableFullScan_37 | 3.05 KB | N/A |
| │ └─TableFullScan_37 | 25.00 | 25 | cop[tikv] | table:b | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 25, total_process_keys_size: 3109, total_keys: 26, get_snapshot_time: 31.8µs, rocksdb: {key_skipped_count: 25, block: {cache_hit_count: 1}}} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_36(Probe) | 150000.00 | 150000 | root | | time:83ms, loops:149, cop_task: {num: 11, max: 64.4ms, min: 547.4µs, avg: 7.02ms, p95: 64.4ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 44.2ms, tot_wait: 1.25ms, rpc_num: 11, rpc_time: 77ms, copr_cache_hit_ratio: 0.91, build_task_duration: 12.5µs, max_distsql_concurrency: 1} | data:TableFullScan_35 | 18.9 MB | N/A |
| └─TableFullScan_35 | 150000.00 | 150000 | cop[tikv] | table:a | tikv_task:{proc max:31ms, min:2ms, avg: 12ms, p80:25ms, p95:31ms, iters:190, tasks:11}, scan_detail: {total_process_keys: 50144, total_process_keys_size: 10206946, total_keys: 50145, get_snapshot_time: 263.3µs, rocksdb: {key_skipped_count: 50144, block: {cache_hit_count: 335}}} | keep order:false | N/A | N/A |
+-------------------------------+-----------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------+---------+---------+
9 rows in set (0.10 sec)
mysql> explain analyze select a.* ,b.* from (select * from customer a where a.C_CUSTKEY in (select O_CUSTKEY from orders where O_ORDERKEY=1 limit 1)) a left join nation b on a.C_NATIONKEY=b.N_NATIONKEY ;
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| IndexJoin_22 | 1.25 | 1 | root | | time:3.09ms, loops:2, RU:1.510949, inner:{total:792µs, concurrency:5, task:1, construct:4.06µs, fetch:778.7µs, build:3.34µs}, probe:7.12µs | left outer join, inner:TableReader_19, outer key:tpch1.customer.c_nationkey, inner key:tpch1.nation.n_nationkey, equal cond:eq(tpch1.customer.c_nationkey, tpch1.nation.n_nationkey) | 54.9 KB | N/A |
| ├─IndexHashJoin_36(Build) | 1.00 | 1 | root | | time:2.01ms, loops:3, inner:{total:909.3µs, concurrency:5, task:1, construct:4.79µs, fetch:891.2µs, build:5.53µs, join:7.2µs} | inner join, inner:TableReader_31, outer key:tpch1.orders.o_custkey, inner key:tpch1.customer.c_custkey, equal cond:eq(tpch1.orders.o_custkey, tpch1.customer.c_custkey) | 147.5 KB | N/A |
| │ ├─HashAgg_41(Build) | 1.00 | 1 | root | | time:994.4µs, loops:3 | group by:tpch1.orders.o_custkey, funcs:firstrow(tpch1.orders.o_custkey)->tpch1.orders.o_custkey | 8.97 KB | 0 Bytes |
| │ │ └─Limit_42 | 1.00 | 1 | root | | time:973.8µs, loops:2 | offset:0, count:1 | N/A | N/A |
| │ │ └─Point_Get_44 | 1.00 | 1 | root | table:orders | time:967.7µs, loops:1, Get:{num_rpc:1, total_time:891.1µs}, total_process_time: 107.1µs, total_wait_time: 194.5µs, tikv_wall_time: 355.7µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 134, total_keys: 1, get_snapshot_time: 23.8µs, rocksdb: {block: {cache_hit_count: 2}}} | handle:1 | N/A | N/A |
| │ └─TableReader_31(Probe) | 1.00 | 1 | root | | time:753.7µs, loops:2, cop_task: {num: 1, max: 628.8µs, proc_keys: 1, tot_proc: 71.8µs, tot_wait: 102µs, rpc_num: 1, rpc_time: 607.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 31.1µs, max_distsql_concurrency: 1, max_extra_concurrency: 1} | data:TableRangeScan_30 | N/A | N/A |
| │ └─TableRangeScan_30 | 1.00 | 1 | cop[tikv] | table:a | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 242, total_keys: 1, get_snapshot_time: 19.8µs, rocksdb: {block: {cache_hit_count: 1}}} | range: decided by [tpch1.orders.o_custkey], keep order:false | N/A | N/A |
| └─TableReader_19(Probe) | 1.00 | 1 | root | | time:692.6µs, loops:2, cop_task: {num: 1, max: 589.3µs, proc_keys: 1, tot_proc: 57µs, tot_wait: 88.8µs, rpc_num: 1, rpc_time: 571.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 22.6µs, max_distsql_concurrency: 1, max_extra_concurrency: 1} | data:TableRangeScan_18 | N/A | N/A |
| └─TableRangeScan_18 | 1.00 | 1 | cop[tikv] | table:b | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 104, total_keys: 1, get_snapshot_time: 14.4µs, rocksdb: {block: {cache_hit_count: 1}}} | range: decided by [tpch1.customer.c_nationkey], keep order:false, stats:pseudo | N/A | N/A |
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
9 rows in set (0.00 sec)
set tidb_enable_outer_join_reorder=ON;
mysql> explain analyze select a.* ,b.* from customer a left join nation b on a.C_NATIONKEY=b.N_NATIONKEY where a.C_CUSTKEY in (select O_CUSTKEY from orders where O_ORDERKEY=1 limit 1);
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| IndexJoin_23 | 1.25 | 1 | root | | time:3.01ms, loops:2, RU:1.514662, inner:{total:803.9µs, concurrency:5, task:1, construct:6.21µs, fetch:783.3µs, build:4.67µs}, probe:5.8µs | left outer join, inner:TableReader_20, outer key:tpch1.customer.c_nationkey, inner key:tpch1.nation.n_nationkey, equal cond:eq(tpch1.customer.c_nationkey, tpch1.nation.n_nationkey) | 54.9 KB | N/A |
| ├─IndexHashJoin_36(Build) | 1.00 | 1 | root | | time:1.99ms, loops:3, inner:{total:965µs, concurrency:5, task:1, construct:4.7µs, fetch:950.2µs, build:5.78µs, join:6.77µs} | inner join, inner:TableReader_31, outer key:tpch1.orders.o_custkey, inner key:tpch1.customer.c_custkey, equal cond:eq(tpch1.orders.o_custkey, tpch1.customer.c_custkey) | 162.5 KB | N/A |
| │ ├─HashAgg_41(Build) | 1.00 | 1 | root | | time:895µs, loops:3 | group by:tpch1.orders.o_custkey, funcs:firstrow(tpch1.orders.o_custkey)->tpch1.orders.o_custkey | 8.97 KB | 0 Bytes |
| │ │ └─Limit_42 | 1.00 | 1 | root | | time:876.2µs, loops:2 | offset:0, count:1 | N/A | N/A |
| │ │ └─Point_Get_44 | 1.00 | 1 | root | table:orders | time:869.1µs, loops:1, Get:{num_rpc:1, total_time:790.5µs}, total_process_time: 104.2µs, total_wait_time: 113.8µs, tikv_wall_time: 277.6µs, scan_detail: {total_process_keys: 1, total_process_keys_size: 134, total_keys: 1, get_snapshot_time: 22.5µs, rocksdb: {block: {cache_hit_count: 2}}} | handle:1 | N/A | N/A |
| │ └─TableReader_31(Probe) | 1.00 | 1 | root | | time:824.2µs, loops:2, cop_task: {num: 1, max: 681.7µs, proc_keys: 1, tot_proc: 76.6µs, tot_wait: 95µs, rpc_num: 1, rpc_time: 661.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 30.6µs, max_distsql_concurrency: 1, max_extra_concurrency: 1} | data:TableRangeScan_30 | N/A | N/A |
| │ └─TableRangeScan_30 | 1.00 | 1 | cop[tikv] | table:a | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 242, total_keys: 1, get_snapshot_time: 18.9µs, rocksdb: {block: {cache_hit_count: 1}}} | range: decided by [tpch1.orders.o_custkey], keep order:false | N/A | N/A |
| └─TableReader_20(Probe) | 1.00 | 1 | root | | time:666.1µs, loops:2, cop_task: {num: 1, max: 555µs, proc_keys: 1, tot_proc: 66.2µs, tot_wait: 89.6µs, rpc_num: 1, rpc_time: 536µs, copr_cache_hit_ratio: 0.00, build_task_duration: 26.2µs, max_distsql_concurrency: 1, max_extra_concurrency: 1} | data:TableRangeScan_19 | N/A | N/A |
| └─TableRangeScan_19 | 1.00 | 1 | cop[tikv] | table:b | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 104, total_keys: 1, get_snapshot_time: 17.8µs, rocksdb: {block: {cache_hit_count: 1}}} | range: decided by [tpch1.customer.c_nationkey], keep order:false, stats:pseudo | N/A | N/A |
+---------------------------------+---------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
9 rows in set (0.01 sec)
