写点什么

TiDB SEMI JION(半连接) 优化实践

  • 2024-10-18
    北京
  • 本文字数:19025 字

    阅读完需:约 62 分钟

作者: 数据源的 TiDB 学习之路原文来源:https://tidb.net/blog/8fe512af

关于半连接(Semi Join)

半连接(Semi Join)是数据库中一种特殊的连接操作,它只关心一个表(通常称为外表或左表)中的记录是否在另一个表(通常称为内表或右表)中有匹配的记录,而不关心匹配记录的具体数量或内容。半连接的结果集通常只包含外表中的记录,并且这些记录在内表中至少有一个匹配项。


在 SQL 中,半连接可以通过多种方式实现,包括但不限于:


  1. 使用 EXISTS 子查询:最常见的实现方式之一。通过在外表的主查询中使用 EXISTS 关键字,并嵌套一个在内表上进行查找的子查询,可以判断外表中的记录是否在内表中有匹配项。

  2. 使用 IN 子查询:虽然 IN 子查询通常用于返回匹配项的具体内容,但也可以用于实现半连接的效果。需要注意的是,当内表中的匹配项很多时,IN 子查询的性能可能会下降。

  3. 直接转换为 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 可以可以扩大选择范围,让优化器选择更好的执行方式,从而在性能上达到几倍的性能。


发布于: 刚刚阅读数: 3
用户头像

TiDB 社区官网:https://tidb.net/ 2021-12-15 加入

TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目,旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/

评论

发布
暂无评论
TiDB SEMI JION(半连接)优化实践_实践案例_TiDB 社区干货传送门_InfoQ写作社区