关于半连接（Semi Join）
半连接（Semi Join）是数据库中一种特殊的连接操作，它只关心一个表（通常称为外表或左表）中的记录是否在另一个表（通常称为内表或右表）中有匹配的记录，而不关心匹配记录的具体数量或内容。半连接的结果集通常只包含外表中的记录，并且这些记录在内表中至少有一个匹配项。
在SQL中，半连接可以通过多种方式实现，包括但不限于：
- 使用EXISTS子查询：最常见的实现方式之一。通过在外表的主查询中使用EXISTS关键字，并嵌套一个在内表上进行查找的子查询，可以判断外表中的记录是否在内表中有匹配项。
- 使用IN子查询：虽然IN子查询通常用于返回匹配项的具体内容，但也可以用于实现半连接的效果。需要注意的是，当内表中的匹配项很多时，IN子查询的性能可能会下降。
- 直接转换为JOIN操作：在某些数据库系统中，优化器可能会自动将半连接转换为更高效的JOIN操作。这通常取决于数据库系统的具体实现和查询的复杂性。
下面是一个典型的半连接SQL语句，
select * from t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a = t1.a);
优化器针对 Semi Join 优化的不足
优化器针对 Semi Join 语句中表之间的关联方式可能会转换为不同的 Join 方式，如 Hash Join 或 Index Join。然而，不论是哪种 Join 方式，在某些特定的场景下都存在一定的不足之处。
- 选择 Hash Join 时：Semi Join 被优化为 Hash Join 的执行方式时，只能够使用子查询构建哈希表，因此在子查询比外查询结果集大时，执行速度可能会不及预期。
- 选择 Index Join 时：Semi Join 被优化为 Index Join 的执行方式时，只能够使用外查询作为驱动表，因此在子查询比外查询结果集小时，执行速度可能会不及预期。
TiDB semi_join_rewrite() 优化的效果
针对上述默认优化器存在的不足，TiDB 提供 semi_join_rewrite() HINT。使用 SEMI_JOIN_REWRITE() 进行改写后，优化器便可以扩大选择范围，选择更好的执行方式。（目前该 Hint 只作用于 EXISTS 子查询）
- Hash Join 的优化效果
根据上述描述，执行计划默认选择走 Hash Join 的情况下，只能够使用子查询构建哈希表，因此在子查询的结果集比外查询大时，执行速度可能不及预期。我们模拟 2 张表 t1 和 t2 分别为 100 万 和 1000 万，通过执行计划可以发现，子查询中的大表 t2 被用来构建哈希表，造成语句的执行耗时约为 7 秒。
注：为了确保执行计划选择 Hash Join，添加/*+ hash_join(t1,t2@sel_2)*/ HINT。
mysql> explain analyze select /*+ hash_join(t1,t2@sel_2)*/ * from t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.a = t1.a);
+-----------------------------+-------------+----------+-----------+-----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------+-------------+----------+-----------+-----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+----------+---------+
| HashJoin_21 | 800000.00 | 1000000 | root | | time:7.13s, loops:979, RU:9602.429239, build_hash_table:{total:6.85s, fetch:172ms, build:6.68s}, probe:{concurrency:5, total:35.7s, max:7.13s, probe:1.35s, fetch and wait:34.3s} | semi join, equal:[eq(test.t1.a, test.t2.a)] | 801.3 MB | 0 Bytes |
| ├─IndexReader_29(Build) | 10000000.00 | 10000000 | root | | time:93ms, loops:9783, cop_task: {num: 328, max: 50.2ms, min: 523.3µs, avg: 23.7ms, p95: 41.4ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 7.26s, tot_wait: 55.2ms, copr_cache_hit_ratio: 0.02, build_task_duration: 46.6µs, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:328, total_time:7.75s}} | index:IndexFullScan_28 | 6.10 MB | N/A |
| │ └─IndexFullScan_28 | 10000000.00 | 10000000 | cop[tikv] | table:t2, index:idx_t2_a(a) | tikv_task:{proc max:50ms, min:0s, avg: 21.3ms, p80:40ms, p95:40ms, iters:11061, tasks:328}, scan_detail: {total_process_keys: 9987904, total_process_keys_size: 459443584, total_keys: 9988226, get_snapshot_time: 28.7ms, rocksdb: {key_skipped_count: 9987904, block: {cache_hit_count: 16496}}}, time_detail: {total_process_time: 7.26s, total_suspend_time: 20.5ms, total_wait_time: 55.2ms, total_kv_read_wall_time: 6.95s, tikv_wall_time: 7.43s} | keep order:false | N/A | N/A |
| └─TableReader_24(Probe) | 1000000.00 | 1000000 | root | | time:14.3ms, loops:979, cop_task: {num: 28, max: 1.68ms, min: 596.2µs, avg: 1.13ms, p95: 1.5ms, max_proc_keys: 992, p95_proc_keys: 480, tot_proc: 2.22ms, tot_wait: 3.62ms, copr_cache_hit_ratio: 0.89, build_task_duration: 21.4µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:28, total_time:30.2ms}} | data:Selection_23 | 1.53 MB | N/A |
| └─Selection_23 | 1000000.00 | 1000000 | cop[tikv] | | tikv_task:{proc max:50ms, min:0s, avg: 32.5ms, p80:50ms, p95:50ms, iters:1088, tasks:28}, scan_detail: {total_process_keys: 1696, total_process_keys_size: 79129, total_keys: 1699, get_snapshot_time: 1.17ms, rocksdb: {key_skipped_count: 1696, block: {cache_hit_count: 15}}}, time_detail: {total_process_time: 2.22ms, total_suspend_time: 15.4µs, total_wait_time: 3.62ms, tikv_wall_time: 11.8ms} | not(isnull(test.t1.a)) | N/A | N/A |
| └─TableFullScan_22 | 1000000.00 | 1000000 | cop[tikv] | table:t1 | tikv_task:{proc max:50ms, min:0s, avg: 31.4ms, p80:50ms, p95:50ms, iters:1088, tasks:28} | keep order:false | N/A | N/A |
+-----------------------------+-------------+----------+-----------+-----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------+----------+---------+
6 rows in set (7.13 sec)
而当我们添加了 /*+ semi_join_rewrite() */ HINT 之后，执行计划虽然还是走 Hash Join，但是被哈希的表变成了小表 t1， 同时大表 t2 也做了一个分组聚合。执行耗时也是优化到 3.6 秒，提升了 1 倍。
mysql> explain analyze select /*+ hash_join(t1,t2@sel_2)*/ * from t1 WHERE EXISTS (SELECT /*+ semi_join_rewrite() */1 FROM t2 WHERE t2.a = t1.a);
+------------------------------+-------------+----------+-----------+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+-------------+----------+-----------+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+----------+---------+
| HashJoin_28 | 1000000.00 | 1000000 | root | | time:3.6s, loops:980, RU:9856.929542, build_hash_table:{total:568.5ms, fetch:19.1ms, build:549.5ms}, probe:{concurrency:5, total:18s, max:3.6s, probe:4.96s, fetch and wait:13s} | inner join, equal:[eq(test.t1.a, test.t2.a)] | 102.3 MB | 0 Bytes |
| ├─TableReader_31(Build) | 1000000.00 | 1000000 | root | | time:13.2ms, loops:979, cop_task: {num: 28, max: 16ms, min: 570.1µs, avg: 1.55ms, p95: 2.28ms, max_proc_keys: 992, p95_proc_keys: 480, tot_proc: 2.43ms, tot_wait: 2.98ms, copr_cache_hit_ratio: 0.89, build_task_duration: 21µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:28, total_time:42.4ms}} | data:Selection_30 | 1.53 MB | N/A |
| │ └─Selection_30 | 1000000.00 | 1000000 | cop[tikv] | | tikv_task:{proc max:50ms, min:0s, avg: 32.5ms, p80:50ms, p95:50ms, iters:1088, tasks:28}, scan_detail: {total_process_keys: 1696, total_process_keys_size: 79129, total_keys: 1699, get_snapshot_time: 876.9µs, rocksdb: {key_skipped_count: 1696, block: {cache_hit_count: 15}}}, time_detail: {total_process_time: 2.43ms, total_suspend_time: 23.3µs, total_wait_time: 2.98ms, tikv_wall_time: 20.4ms} | not(isnull(test.t1.a)) | N/A | N/A |
| │ └─TableFullScan_29 | 1000000.00 | 1000000 | cop[tikv] | table:t1 | tikv_task:{proc max:50ms, min:0s, avg: 31.4ms, p80:50ms, p95:50ms, iters:1088, tasks:28} | keep order:false | N/A | N/A |
| └─StreamAgg_46(Probe) | 9965568.00 | 10000000 | root | | time:2.56s, loops:9767 | group by:test.t2.a, funcs:firstrow(test.t2.a)->test.t2.a | 8.39 KB | N/A |
| └─IndexReader_47 | 9965568.00 | 10000000 | root | | time:1.62s, loops:9774, cop_task: {num: 329, max: 61ms, min: 1.47ms, avg: 26.1ms, p95: 45.5ms, max_proc_keys: 50176, p95_proc_keys: 50176, tot_proc: 8s, tot_wait: 55.6ms, copr_cache_hit_ratio: 0.00, build_task_duration: 61.3µs, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:329, total_time:8.59s}} | index:StreamAgg_38 | 52.8 MB | N/A |
| └─StreamAgg_38 | 9965568.00 | 10000000 | cop[tikv] | | tikv_task:{proc max:50ms, min:0s, avg: 23.8ms, p80:40ms, p95:40ms, iters:9774, tasks:329}, scan_detail: {total_process_keys: 10000000, total_process_keys_size: 460000000, total_keys: 10000329, get_snapshot_time: 26.3ms, rocksdb: {key_skipped_count: 10000000, block: {cache_hit_count: 16548}}}, time_detail: {total_process_time: 8s, total_suspend_time: 24.4ms, total_wait_time: 55.6ms, total_kv_read_wall_time: 7.28s, tikv_wall_time: 8.24s} | group by:test.t2.a, | N/A | N/A |
| └─IndexFullScan_45 | 10000000.00 | 10000000 | cop[tikv] | table:t2, index:idx_t2_a(a) | tikv_task:{proc max:50ms, min:0s, avg: 22.1ms, p80:40ms, p95:40ms, iters:9774, tasks:329} | keep order:true | N/A | N/A |
+------------------------------+-------------+----------+-----------+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------+----------+---------+
8 rows in set (3.60 sec)
- Index Join 的优化效果
根据上述描述，执行计划默认选择走 Index Join 的情况下，只能够使用外查询作为驱动表，因此在子查询比外查询结果集小时，执行速度可能会不及预期。t1 和 t2 仍然是 100 万 和 1000 万，通过执行计划可以发现，1000 万的 t2 (外表) 被作为驱动表进行 Build，语句执行耗时达到 1分31秒。
注：为了确保执行计划选择 Index Join，添加/*+ no_hash_join(t2,t1@sel_2)*/ HINT。
mysql> explain analyze select /*+ no_hash_join(t2, t1@sel_2) */ * from t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t2.a = t1.a);
+------------------------------+-------------+----------+-----------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------+-------------+----------+-----------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------+---------+------+
| IndexJoin_14 | 8000000.00 | 1000000 | root | | time:1m31.2s, loops:978, RU:142580.352894, inner:{total:7m32.5s, concurrency:5, task:394, construct:11.5s, fetch:7m20.7s, build:303.2ms}, probe:1.46s | semi join, inner:IndexReader_13, outer key:test.t2.a, inner key:test.t1.a, equal cond:eq(test.t2.a, test.t1.a) | 31.2 MB | N/A |
| ├─TableReader_37(Build) | 10000000.00 | 10000000 | root | | time:50.7ms, loops:9779, cop_task: {num: 285, max: 72.9ms, min: 493.3µs, avg: 33.8ms, p95: 51.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 8.93s, tot_wait: 63.9ms, copr_cache_hit_ratio: 0.09, build_task_duration: 49.1µs, max_distsql_concurrency: 11}, rpc_info:{Cop:{num_rpc:285, total_time:9.61s}} | data:Selection_36 | 9.20 MB | N/A |
| │ └─Selection_36 | 10000000.00 | 10000000 | cop[tikv] | | tikv_task:{proc max:50ms, min:0s, avg: 30ms, p80:50ms, p95:50ms, iters:10898, tasks:285}, scan_detail: {total_process_keys: 9929120, total_process_keys_size: 466539933, total_keys: 9929380, get_snapshot_time: 39.6ms, rocksdb: {key_skipped_count: 9929120, block: {cache_hit_count: 19121}}}, time_detail: {total_process_time: 8.93s, total_suspend_time: 25.6ms, total_wait_time: 63.9ms, total_kv_read_wall_time: 8.13s, tikv_wall_time: 9.1s} | not(isnull(test.t2.a)) | N/A | N/A |
| │ └─TableFullScan_35 | 10000000.00 | 10000000 | cop[tikv] | table:t2 | tikv_task:{proc max:50ms, min:0s, avg: 28.8ms, p80:40ms, p95:50ms, iters:10898, tasks:285} | keep order:false | N/A | N/A |
| └─IndexReader_13(Probe) | 1003455.10 | 1000000 | root | | time:7m3.2s, loops:1436, cop_task: {num: 1202, max: 1.29s, min: 748.3µs, avg: 360.2ms, p95: 1.13s, max_proc_keys: 9184, p95_proc_keys: 5088, tot_proc: 6m33.2s, tot_wait: 209.3ms, copr_cache_hit_ratio: 0.03, build_task_duration: 580.2ms, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:1202, total_time:7m12.9s}} | index:Selection_12 | 6.10 KB | N/A |
| └─Selection_12 | 1003455.10 | 1000000 | cop[tikv] | | tikv_task:{proc max:0s, min:0s, avg: 339.8ms, p80:901ms, p95:1.1s, iters:3594, tasks:1202}, scan_detail: {total_process_keys: 996040, total_process_keys_size: 45817840, total_keys: 10990023, get_snapshot_time: 57.4ms, rocksdb: {key_skipped_count: 996040, block: {cache_hit_count: 39977432}}}, time_detail: {total_process_time: 6m33.2s, total_suspend_time: 15.5s, total_wait_time: 209.3ms, total_kv_read_wall_time: 6m48.2s, tikv_wall_time: 7m6.6s} | not(isnull(test.t1.a)) | N/A | N/A |
| └─IndexRangeScan_11 | 1003455.10 | 1000000 | cop[tikv] | table:t1, index:idx_t1(a) | tikv_task:{proc max:0s, min:0s, avg: 339.8ms, p80:901ms, p95:1.1s, iters:3594, tasks:1202} | range: decided by [eq(test.t1.a, test.t2.a)], keep order:false | N/A | N/A |
+------------------------------+-------------+----------+-----------+---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------+---------+------+
7 rows in set (1 min 31.21 sec)
而当我们添加了 /*+ semi_join_rewrite() */ HINT 之后，执行计划发生了显著的变化。首先，驱动表变成 100 万的小表 t1，其次表的关联方式从 IndexJoin 变成了 IndexHashJoin。添加 HINT 后的执行耗时也是有了很大的改善，直接优化到约 11 秒，提升了 9 倍。
mysql> explain analyze select /*+ no_hash_join(t2, t1@sel_2) */ * from t2 WHERE EXISTS (SELECT /*+ semi_join_rewrite() */ 1 FROM t1 WHERE t2.a = t1.a);
+----------------------------------+------------+---------+-----------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+------------+---------+-----------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
| IndexHashJoin_20 | 1003390.88 | 1000000 | root | | time:10.8s, loops:978, RU:13383.173606, inner:{total:52.9s, concurrency:5, task:42, construct:935.8ms, fetch:51s, build:246.5ms, join:884.6ms} | inner join, inner:IndexLookUp_17, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a) | 30.5 MB | N/A |
| ├─StreamAgg_72(Build) | 999936.00 | 1000000 | root | | time:98.2ms, loops:979 | group by:test.t1.a, funcs:firstrow(test.t1.a)->test.t1.a | 8.39 KB | N/A |
| │ └─IndexReader_73 | 999936.00 | 1000000 | root | | time:12.4ms, loops:978, cop_task: {num: 28, max: 3.2ms, min: 470µs, avg: 856µs, p95: 2.16ms, max_proc_keys: 1024, p95_proc_keys: 1024, tot_proc: 4.38ms, tot_wait: 3.47ms, copr_cache_hit_ratio: 0.86, build_task_duration: 22.3µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:28, total_time:23.3ms}} | index:StreamAgg_68 | 6.36 MB | N/A |
| │ └─StreamAgg_68 | 999936.00 | 1000000 | cop[tikv] | | tikv_task:{proc max:50ms, min:0s, avg: 30.7ms, p80:40ms, p95:50ms, iters:977, tasks:28}, scan_detail: {total_process_keys: 4096, total_process_keys_size: 188416, total_keys: 4100, get_snapshot_time: 1.86ms, rocksdb: {key_skipped_count: 4096, block: {cache_hit_count: 22}}}, time_detail: {total_process_time: 4.38ms, total_suspend_time: 48.7µs, total_wait_time: 3.47ms, total_kv_read_wall_time: 10ms, tikv_wall_time: 12.5ms} | group by:test.t1.a, | N/A | N/A |
| │ └─IndexFullScan_36 | 1000000.00 | 1000000 | cop[tikv] | table:t1, index:idx_t1(a) | tikv_task:{proc max:50ms, min:0s, avg: 29.3ms, p80:40ms, p95:50ms, iters:977, tasks:28} | keep order:true | N/A | N/A |
| └─IndexLookUp_17(Probe) | 1003390.88 | 1000000 | root | | time:49.5s, loops:1019, index_task: {total_time: 49.3s, fetch_handle: 49.3s, build: 619.8µs, wait: 3.04ms}, table_task: {total_time: 915ms, num: 201, concurrency: 5}, next: {wait_index: 48.6s, wait_table_lookup_build: 99.4ms, wait_table_lookup_resp: 791.6ms} | | 158.1 KB | N/A |
| ├─Selection_16(Build) | 1003390.88 | 1000000 | cop[tikv] | | time:49.2s, loops:1142, cop_task: {num: 253, max: 666.6ms, min: 1.15ms, avg: 211.1ms, p95: 501.8ms, max_proc_keys: 9184, p95_proc_keys: 9184, tot_proc: 36.1s, tot_wait: 53.1ms, copr_cache_hit_ratio: 0.00, build_task_duration: 65.1ms, max_distsql_concurrency: 3}, rpc_info:{Cop:{num_rpc:253, total_time:53.4s}}, tikv_task:{proc max:650ms, min:0s, avg: 190.8ms, p80:300ms, p95:480ms, iters:2026, tasks:253}, scan_detail: {total_process_keys: 999840, total_process_keys_size: 45992640, total_keys: 1999853, get_snapshot_time: 29.8ms, rocksdb: {key_skipped_count: 999840, block: {cache_hit_count: 4787484}}}, time_detail: {total_process_time: 36.1s, total_suspend_time: 12.3s, total_wait_time: 53.1ms, total_kv_read_wall_time: 48.2s, tikv_wall_time: 52s} | not(isnull(test.t2.a)) | N/A | N/A |
| │ └─IndexRangeScan_14 | 1003390.88 | 1000000 | cop[tikv] | table:t2, index:idx_t2_a(a) | tikv_task:{proc max:650ms, min:0s, avg: 190.7ms, p80:300ms, p95:480ms, iters:2026, tasks:253} | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false | N/A | N/A |
| └─TableRowIDScan_15(Probe) | 1003390.88 | 1000000 | cop[tikv] | table:t2 | time:775.3ms, loops:1258, cop_task: {num: 217, max: 26.9ms, min: 634.9µs, avg: 3.52ms, p95: 7.6ms, max_proc_keys: 8544, p95_proc_keys: 5024, tot_proc: 370.8ms, tot_wait: 32.1ms, copr_cache_hit_ratio: 0.33, build_task_duration: 10.7ms, max_distsql_concurrency: 2, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:217, total_time:756.1ms}}, tikv_task:{proc max:10ms, min:0s, avg: 3.92ms, p80:10ms, p95:10ms, iters:1992, tasks:217}, scan_detail: {total_process_keys: 381888, total_process_keys_size: 17893063, total_keys: 382135, get_snapshot_time: 12.6ms, rocksdb: {key_skipped_count: 381888, block: {cache_hit_count: 1681}}}, time_detail: {total_process_time: 370.8ms, total_suspend_time: 4.6ms, total_wait_time: 32.1ms, total_kv_read_wall_time: 370ms, tikv_wall_time: 475.2ms} | keep order:false | N/A | N/A |
+----------------------------------+------------+---------+-----------+-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+
9 rows in set (10.83 sec)
总结
TiDB 优化器针对 semi join 半连接默认会转换为 HashJoin 或 IndexJoin 以获得更好的性能，然而在某些特殊的场景下默认选择的 HashJoin 或 IndexJoin 可能仍然达不到最佳的性能。TiDB 提供的 semi_join_rewrite() HINT 可以可以扩大选择范围，让优化器选择更好的执行方式，从而在性能上达到几倍的性能。