TiDB 7.5 LTS 版本新增的 Hint
作者：TiDB 社区干货传送门
- 2024-03-15 北京
本文字数：30797 字
阅读完需：约 101 分钟
作者： longzhuquan 原文来源：https://tidb.net/blog/86b3c703
背景
TiDB 7.5 LTS 版本已经发布三个月，许多客户已经开始广泛使用。在本文中，我们将深入探讨 TiDB v7.5 版本相较于 TiDB v6.5 版本新增的 Hint，了解这些新 Hint 带来的优势和改进。
TiDB v7.5 比 TiDB v6.5 新增的 Hint
SET_VAR(VAR_NAME=VAR_VALUE)
允许在语句执行期间以 Hint 形式临时修改会话级系统变量的值。当语句执行完成后，系统变量将在当前会话中自动恢复为原始值。这个功能类似于 MySQL 的 Variable-Setting Hint 和 Oracle 的 OPT_PARAM Hint。在我看来，这个 Hint 非常实用，因为它使得在 SQL 级别设置会话变量成为可能，从而影响 SQL 执行过程。
以下是几种常见的应用场景：
场景 1：单独为抽数 SQL 限制扫描并发度，降低对线上业务影响。
mysql> explain analyze select /*+ set_var(tidb_distsql_scan_concurrency=10)*/ * from t1;+-----------------------+------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+-----------------------+------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+------+| TableReader_5 | 2040000.00 | 4080000 | root | | time:1.51s, loops:3997, RU:6278.961683, cop_task: {num: 145, max: 94.8ms, min: 1.11ms, avg: 29.9ms, p95: 65.8ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 3.89s, tot_wait: 28.9ms, rpc_num: 149, rpc_time: 4.33s, copr_cache_hit_ratio: 0.00, build_task_duration: 34.8µs, max_distsql_concurrency: 8}, ResolveLock:{num_rpc:4, total_time:2.97s} | data:TableFullScan_4 | 8.91 MB | N/A || └─TableFullScan_4 | 2040000.00 | 4080000 | cop[tikv] | table:t1 | tikv_task:{proc max:87ms, min:0s, avg: 25ms, p80:44ms, p95:57ms, iters:4559, tasks:145}, scan_detail: {total_process_keys: 2230723, total_process_keys_size: 321896340, total_keys: 2230868, get_snapshot_time: 6.13ms, rocksdb: {delete_skipped_count: 1418239, key_skipped_count: 5498239, block: {cache_hit_count: 18259, read_count: 19934, read_byte: 319.5 MB, read_time: 363.2ms}}} | keep order:false | N/A | N/A |+-----------------------+------------+---------+-----------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+------+2 rows in set (1.51 sec)
mysql> explain analyze select /*+ set_var(tidb_distsql_scan_concurrency=1)*/ * from t1;+-----------------------+------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+-----------------------+------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+------+| TableReader_5 | 2040000.00 | 4080000 | root | | time:3.26s, loops:3992, RU:5026.977726, cop_task: {num: 145, max: 147.2ms, min: 211.2µs, avg: 22.3ms, p95: 67.7ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 3.07s, tot_wait: 8.46ms, rpc_num: 145, rpc_time: 3.23s, copr_cache_hit_ratio: 0.21, build_task_duration: 288.1µs, max_distsql_concurrency: 1} | data:TableFullScan_4 | 5.94 MB | N/A || └─TableFullScan_4 | 2040000.00 | 4080000 | cop[tikv] | table:t1 | tikv_task:{proc max:147ms, min:0s, avg: 23.8ms, p80:33ms, p95:64ms, iters:4559, tasks:145}, scan_detail: {total_process_keys: 3268416, total_process_keys_size: 257884560, total_keys: 3268531, get_snapshot_time: 4.84ms, rocksdb: {delete_skipped_count: 8177731, key_skipped_count: 11446147, block: {cache_hit_count: 83706, read_count: 10554, read_byte: 115.6 MB, read_time: 137.5ms}}} | keep order:false | N/A | N/A |+-----------------------+------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+------+2 rows in set (3.26 sec)
复制代码
场景 2：让分析场景单独 SQL 走 Tiflash MPP 执行计划。
通过配置 TiDB 的配置文件参数 isolation-read.engines，可以使所有 SQL 默认使用 TiKV 存储引擎。同时，可以使用单独的 Hint 来指定分析场景 SQL 使用 TiFlash 存储引擎。
mysql> show variables like '%tidb_isolation_read_engines%';+-----------------------------+-----------+| Variable_name | Value |+-----------------------------+-----------+| tidb_isolation_read_engines | tikv,tidb |+-----------------------------+-----------+1 row in set (0.00 sec)
mysql> explain analyze select t1.*,t2.* from t1,t2 where t1.id=t2.id;+--------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+----------+---------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+--------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+----------+---------+| Projection_9 | 40854.91 | 120000 | root | | time:639.8ms, loops:120, RU:5781.500730, Concurrency:5 | test.t1.id, test.t1.col1, test.t1.col2, test.t1.col3, test.t2.id, test.t2.col1, test.t2.col2, test.t2.col3 | 1.61 MB | N/A || └─HashJoin_11 | 40854.91 | 120000 | root | | time:639.5ms, loops:120, build_hash_table:{total:8.23ms, fetch:7.02ms, build:1.21ms}, probe:{concurrency:5, total:3.2s, max:639.7ms, probe:498.9ms, fetch:2.7s} | inner join, equal:[eq(test.t2.id, test.t1.id)] | 1.41 MB | 0 Bytes || ├─TableReader_14(Build) | 10000.00 | 10000 | root | | time:7.58ms, loops:12, cop_task: {num: 6, max: 1.86ms, min: 782.8µs, avg: 1.32ms, p95: 1.86ms, max_proc_keys: 3248, p95_proc_keys: 3248, tot_proc: 6.18ms, tot_wait: 255µs, rpc_num: 6, rpc_time: 7.9ms, copr_cache_hit_ratio: 0.00, build_task_duration: 1.92µs, max_distsql_concurrency: 1} | data:Selection_13 | 381.9 KB | N/A || │ └─Selection_13 | 10000.00 | 10000 | cop[tikv] | | tikv_task:{proc max:1ms, min:0s, avg: 500µs, p80:1ms, p95:1ms, iters:33, tasks:6}, scan_detail: {total_process_keys: 10000, total_process_keys_size: 769873, total_keys: 10006, get_snapshot_time: 120.2µs, rocksdb: {key_skipped_count: 10000, block: {cache_hit_count: 12, read_count: 27, read_byte: 221.3 KB, read_time: 647.9µs}}} | not(isnull(test.t2.id)) | N/A | N/A || │ └─TableFullScan_12 | 10000.00 | 10000 | cop[tikv] | table:t2 | tikv_task:{proc max:1ms, min:0s, avg: 500µs, p80:1ms, p95:1ms, iters:33, tasks:6} | keep order:false | N/A | N/A || └─TableReader_17(Probe) | 4080000.00 | 4080000 | root | | time:504.2ms, loops:3994, cop_task: {num: 145, max: 58.8ms, min: 472µs, avg: 21.6ms, p95: 50.6ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 2.35s, tot_wait: 38.1ms, rpc_num: 145, rpc_time: 3.14s, copr_cache_hit_ratio: 0.00, build_task_duration: 5.13µs, max_distsql_concurrency: 8} | data:Selection_16 | 11.9 MB | N/A || └─Selection_16 | 4080000.00 | 4080000 | cop[tikv] | | tikv_task:{proc max:48ms, min:0s, avg: 16.5ms, p80:29ms, p95:40ms, iters:4559, tasks:145}, scan_detail: {total_process_keys: 4080000, total_process_keys_size: 321896340, total_keys: 4080145, get_snapshot_time: 7.34ms, rocksdb: {key_skipped_count: 4080000, block: {cache_hit_count: 7018, read_count: 2267, read_byte: 18.9 MB, read_time: 22.8ms}}} | not(isnull(test.t1.id)) | N/A | N/A || └─TableFullScan_15 | 4080000.00 | 4080000 | cop[tikv] | table:t1 | tikv_task:{proc max:47ms, min:0s, avg: 16ms, p80:28ms, p95:40ms, iters:4559, tasks:145} | keep order:false | N/A | N/A |+--------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+----------+---------+8 rows in set (0.64 sec)
mysql> explain analyze select /*+ set_var(tidb_isolation_read_engines='tiflash,tidb'),set_var(tidb_enforce_mpp=1)*/ t1.*,t2.* from t1,t2 where t1.id=t2.id;+----------------------------------------+------------+---------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+---------+------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+----------------------------------------+------------+---------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+---------+------+| TableReader_36 | 40854.91 | 120000 | root | | time:246.2ms, loops:121, RU:0.000000, cop_task: {num: 7, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_35 | 3.55 MB | N/A || └─ExchangeSender_35 | 40854.91 | 120000 | mpp[tiflash] | | tiflash_task:{time:146.4ms, loops:66, threads:8} | ExchangeType: PassThrough | N/A | N/A || └─Projection_34 | 40854.91 | 120000 | mpp[tiflash] | | tiflash_task:{time:146.3ms, loops:66, threads:8} | test.t1.id, test.t1.col1, test.t1.col2, test.t1.col3, test.t2.id, test.t2.col1, test.t2.col2, test.t2.col3 | N/A | N/A || └─HashJoin_31 | 40854.91 | 120000 | mpp[tiflash] | | tiflash_task:{time:146.3ms, loops:66, threads:8} | inner join, equal:[eq(test.t2.id, test.t1.id)] | N/A | N/A || ├─ExchangeReceiver_18(Build) | 10000.00 | 10000 | mpp[tiflash] | | tiflash_task:{time:24.6ms, loops:1, threads:8} | | N/A | N/A || │ └─ExchangeSender_17 | 10000.00 | 10000 | mpp[tiflash] | | tiflash_task:{time:22.7ms, loops:1, threads:8} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A || │ └─Selection_16 | 10000.00 | 10000 | mpp[tiflash] | | tiflash_task:{time:22.5ms, loops:1, threads:8} | not(isnull(test.t2.id)) | N/A | N/A || │ └─TableFullScan_15 | 10000.00 | 10000 | mpp[tiflash] | table:t2 | tiflash_task:{time:22.5ms, loops:1, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:6, total_skipped_packs:0, total_scanned_rows:30000, total_skipped_rows:0, total_rs_index_check_time: 0ms, total_read_time: 0ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0, total_learner_read_time: 3ms} | pushed down filter:empty, keep order:false | N/A | N/A || └─Selection_20(Probe) | 4080000.00 | 4080000 | mpp[tiflash] | | tiflash_task:{time:124.9ms, loops:66, threads:8} | not(isnull(test.t1.id)) | N/A | N/A || └─TableFullScan_19 | 4080000.00 | 4080000 | mpp[tiflash] | table:t1 | tiflash_task:{time:120.5ms, loops:66, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:501, total_skipped_packs:0, total_scanned_rows:4080000, total_skipped_rows:0, total_rs_index_check_time: 4ms, total_read_time: 591ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 8, total_remote_region_num: 0, total_learner_read_time: 2ms} | pushed down filter:empty, keep order:false | N/A | N/A |+----------------------------------------+------------+---------+--------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+---------+------+10 rows in set (0.34 sec)
复制代码
场景 3：在某些新版本优化器新特性关闭情况下，为某条 SQL 单独开启优化器新特性。
mysql> show variables like '%tidb_enable_null_aware_anti_join%';+----------------------------------+-------+| Variable_name | Value |+----------------------------------+-------+| tidb_enable_null_aware_anti_join | OFF |+----------------------------------+-------+1 row in set (0.01 sec)
mysql> explain analyze select count(1) from t1 where t1.col1 not in (select t2.col1 from t2 where t2.id<100);+--------------------------------+------------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+----------+---------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+--------------------------------+------------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+----------+---------+| HashAgg_10 | 1.00 | 1 | root | | time:12s, loops:2, RU:3968.325071, partial_worker:{wall_time:12.023946333s, concurrency:5, task_num:3986, tot_wait:1m0.003337946s, tot_exec:115.771736ms, tot_time:1m0.119603709s, max:12.023935167s, p95:12.023935167s}, final_worker:{wall_time:12.023954292s, concurrency:5, task_num:5, tot_wait:1m0.11968429s, tot_exec:13.79µs, tot_time:1m0.119700334s, max:12.023942667s, p95:12.023942667s} | funcs:count(1)->Column#11 | 165.3 KB | N/A || └─HashJoin_12 | 3264000.00 | 4079888 | root | | time:12s, loops:3987, build_hash_table:{total:466.2µs, fetch:433.6µs, build:32.6µs}, probe:{concurrency:5, total:1m0.1s, max:12s, probe:1m0.1s, fetch:35.3ms} | CARTESIAN anti semi join, other cond:eq(test.t1.col1, test.t2.col1) | 27.7 KB | 0 Bytes || ├─TableReader_21(Build) | 99.00 | 99 | root | | time:408.8µs, loops:2, cop_task: {num: 1, max: 391µs, proc_keys: 0, tot_proc: 375ns, tot_wait: 124.1µs, rpc_num: 1, rpc_time: 383.5µs, copr_cache_hit_ratio: 1.00, build_task_duration: 1µs, max_distsql_concurrency: 1} | data:Selection_20 | 2.83 KB | N/A || │ └─Selection_20 | 99.00 | 99 | cop[tikv] | | tikv_task:{time:5ms, loops:14}, scan_detail: {get_snapshot_time: 98.8µs, rocksdb: {block: {}}} | lt(test.t2.id, 100) | N/A | N/A || │ └─TableFullScan_19 | 10000.00 | 10000 | cop[tikv] | table:t2 | tikv_task:{time:5ms, loops:14} | keep order:false | N/A | N/A || └─IndexReader_16(Probe) | 4080000.00 | 4080000 | root | | time:12.3ms, loops:3991, cop_task: {num: 114, max: 33.6ms, min: 372.5µs, avg: 12ms, p95: 28.2ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1.25s, tot_wait: 14.3ms, rpc_num: 114, rpc_time: 1.37s, copr_cache_hit_ratio: 0.32, build_task_duration: 5.79µs, max_distsql_concurrency: 4} | index:IndexFullScan_15 | 4.31 MB | N/A || └─IndexFullScan_15 | 4080000.00 | 4080000 | cop[tikv] | table:t1, index:t1_col1(col1) | tikv_task:{proc max:30ms, min:0s, avg: 14.1ms, p80:20ms, p95:25ms, iters:4439, tasks:114}, scan_detail: {total_process_keys: 3098144, total_process_keys_size: 229262656, total_keys: 3098221, get_snapshot_time: 5.29ms, rocksdb: {key_skipped_count: 3098144, block: {cache_hit_count: 6438}}} | keep order:false | N/A | N/A |+--------------------------------+------------+---------+-----------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------+----------+---------+7 rows in set (12.02 sec)
mysql> explain analyze select /*+ set_var(tidb_enable_null_aware_anti_join=1)*/ count(1) from t1 where t1.col1 not in (select t2.col1 from t2 where t2.id<100);+--------------------------------+------------+---------+-----------+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+----------+---------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+--------------------------------+------------+---------+-----------+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+----------+---------+| HashAgg_10 | 1.00 | 1 | root | | time:367.3ms, loops:2, RU:4147.807116, partial_worker:{wall_time:367.305209ms, concurrency:5, task_num:3986, tot_wait:1.581050842s, tot_exec:253.523651ms, tot_time:1.836087875s, max:367.250125ms, p95:367.250125ms}, final_worker:{wall_time:367.320375ms, concurrency:5, task_num:5, tot_wait:1.83629271s, tot_exec:23.959µs, tot_time:1.836319124s, max:367.270333ms, p95:367.270333ms} | funcs:count(1)->Column#11 | 165.3 KB | N/A || └─HashJoin_12 | 3264000.00 | 4079888 | root | | time:363.7ms, loops:3987, build_hash_table:{total:529.3µs, fetch:499.4µs, build:30µs}, probe:{concurrency:5, total:1.84s, max:367.1ms, probe:1.02s, fetch:819ms} | Null-aware anti semi join, equal:[eq(test.t1.col1, test.t2.col1)] | 27.7 KB | 0 Bytes || ├─TableReader_21(Build) | 99.00 | 99 | root | | time:474.9µs, loops:2, cop_task: {num: 1, max: 519.1µs, proc_keys: 0, tot_proc: 458ns, tot_wait: 109.8µs, rpc_num: 1, rpc_time: 512.8µs, copr_cache_hit_ratio: 1.00, build_task_duration: 1.04µs, max_distsql_concurrency: 1} | data:Selection_20 | 2.83 KB | N/A || │ └─Selection_20 | 99.00 | 99 | cop[tikv] | | tikv_task:{time:5ms, loops:14}, scan_detail: {get_snapshot_time: 87.3µs, rocksdb: {block: {}}} | lt(test.t2.id, 100) | N/A | N/A || │ └─TableFullScan_19 | 10000.00 | 10000 | cop[tikv] | table:t2 | tikv_task:{time:5ms, loops:14} | keep order:false | N/A | N/A || └─IndexReader_16(Probe) | 4080000.00 | 4080000 | root | | time:161.5ms, loops:3991, cop_task: {num: 114, max: 24.7ms, min: 210.8µs, avg: 10.3ms, p95: 20.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1.04s, tot_wait: 15.4ms, rpc_num: 114, rpc_time: 1.18s, copr_cache_hit_ratio: 0.25, build_task_duration: 5.29µs, max_distsql_concurrency: 4} | index:IndexFullScan_15 | 3.75 MB | N/A || └─IndexFullScan_15 | 4080000.00 | 4080000 | cop[tikv] | table:t1, index:t1_col1(col1) | tikv_task:{proc max:30ms, min:0s, avg: 11.6ms, p80:16ms, p95:21ms, iters:4439, tasks:114}, scan_detail: {total_process_keys: 3319040, total_process_keys_size: 245608960, total_keys: 3319126, get_snapshot_time: 5.84ms, rocksdb: {key_skipped_count: 3319040, block: {cache_hit_count: 6914}}} | keep order:false | N/A | N/A |+--------------------------------+------------+---------+-----------+-------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------+----------+---------+7 rows in set (0.36 sec)
复制代码
SHUFFLE_JOIN(t1_name , tl_name …)
提示优化器对指定表使用 Shuffle Join 算法，该 Hint 只在 MPP 模式下生效。默认情况下，优化器在选择 Shuffle Join 算法或 Broadcast Join 算法受tidb_broadcast_join_threshold_count、tidb_broadcast_join_threshold_size 和 tidb_prefer_broadcast_join_by_exchange_data_size 这三个系统变量影响。当大表与大表关联时，更适合采用 Shuffle Join 算法。
mysql> set session tidb_enforce_mpp=1;Query OK, 0 rows affected (0.00 sec)
mysql> explain analyze select /*+ SHUFFLE_JOIN(t1,t2)*/ t1.id,t1.col1 from t1,t2 where t1.id=t2.id;+----------------------------------------+------------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+----------------------------------------+------------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+| TableReader_28 | 40854.90 | 120000 | root | | time:179.1ms, loops:121, RU:0.000000, cop_task: {num: 7, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_27 | 762.8 KB | N/A || └─ExchangeSender_27 | 40854.90 | 120000 | mpp[tiflash] | | tiflash_task:{time:139ms, loops:66, threads:8} | ExchangeType: PassThrough | N/A | N/A || └─Projection_26 | 40854.90 | 120000 | mpp[tiflash] | | tiflash_task:{time:139ms, loops:66, threads:8} | test.t1.id, test.t1.col1 | N/A | N/A || └─HashJoin_23 | 40854.90 | 120000 | mpp[tiflash] | | tiflash_task:{time:138ms, loops:66, threads:8} | inner join, equal:[eq(test.t2.id, test.t1.id)], stream_count: 8 | N/A | N/A || ├─ExchangeReceiver_16(Build) | 10000.00 | 10000 | mpp[tiflash] | | tiflash_task:{time:42.7ms, loops:8, threads:8} | stream_count: 8 | N/A | N/A || │ └─ExchangeSender_15 | 10000.00 | 10000 | mpp[tiflash] | | tiflash_task:{time:35.3ms, loops:1, threads:8} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.t2.id, collate: binary], stream_count: 8 | N/A | N/A || │ └─Selection_14 | 10000.00 | 10000 | mpp[tiflash] | | tiflash_task:{time:33.4ms, loops:1, threads:8} | not(isnull(test.t2.id)) | N/A | N/A || │ └─TableFullScan_13 | 10000.00 | 10000 | mpp[tiflash] | table:t2 | tiflash_task:{time:33.4ms, loops:1, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:2, total_skipped_packs:0, total_scanned_rows:10000, total_skipped_rows:0, total_rs_index_check_time: 0ms, total_read_time: 0ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0, total_learner_read_time: 6ms} | pushed down filter:empty, keep order:false | N/A | N/A || └─ExchangeReceiver_20(Probe) | 4080000.00 | 4080000 | mpp[tiflash] | | tiflash_task:{time:135.7ms, loops:66, threads:8} | | N/A | N/A || └─ExchangeSender_19 | 4080000.00 | 4080000 | mpp[tiflash] | | tiflash_task:{time:135ms, loops:66, threads:8} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.t1.id, collate: binary] | N/A | N/A || └─Selection_18 | 4080000.00 | 4080000 | mpp[tiflash] | | tiflash_task:{time:73.9ms, loops:66, threads:8} | not(isnull(test.t1.id)) | N/A | N/A || └─TableFullScan_17 | 4080000.00 | 4080000 | mpp[tiflash] | table:t1 | tiflash_task:{time:71ms, loops:66, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:501, total_skipped_packs:0, total_scanned_rows:4080000, total_skipped_rows:0, total_rs_index_check_time: 2ms, total_read_time: 378ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 8, total_remote_region_num: 0, total_learner_read_time: 6ms} | pushed down filter:empty, keep order:false | N/A | N/A |+----------------------------------------+------------+---------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+----------+------+12 rows in set (0.22 sec)
复制代码
BROADCAST_JOIN(t1_name , tl_name …)
提示优化器对指定表使用 Broadcast Join 算法，该 Hint 只在 MPP 模式下生效。当小表与大表进行关联时，更适合采用 Broadcast Join 算法。
mysql> explain analyze select /*+ BROADCAST_JOIN(t1)*/ t1.id,t1.col1 from t1,t2 where t1.id=t2.id;+----------------------------------------+------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+----------+------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+----------------------------------------+------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+----------+------+| TableReader_26 | 40854.90 | 120000 | root | | time:66.2ms, loops:121, RU:0.000000, cop_task: {num: 7, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_25 | 762.8 KB | N/A || └─ExchangeSender_25 | 40854.90 | 120000 | mpp[tiflash] | | tiflash_task:{time:39.5ms, loops:66, threads:8} | ExchangeType: PassThrough | N/A | N/A || └─Projection_24 | 40854.90 | 120000 | mpp[tiflash] | | tiflash_task:{time:39.5ms, loops:66, threads:8} | test.t1.id, test.t1.col1 | N/A | N/A || └─HashJoin_21 | 40854.90 | 120000 | mpp[tiflash] | | tiflash_task:{time:39.4ms, loops:66, threads:8} | inner join, equal:[eq(test.t2.id, test.t1.id)] | N/A | N/A || ├─ExchangeReceiver_16(Build) | 10000.00 | 10000 | mpp[tiflash] | | tiflash_task:{time:6.45ms, loops:1, threads:8} | | N/A | N/A || │ └─ExchangeSender_15 | 10000.00 | 10000 | mpp[tiflash] | | tiflash_task:{time:4.59ms, loops:1, threads:8} | ExchangeType: Broadcast, Compression: FAST | N/A | N/A || │ └─Selection_14 | 10000.00 | 10000 | mpp[tiflash] | | tiflash_task:{time:4.56ms, loops:1, threads:8} | not(isnull(test.t2.id)) | N/A | N/A || │ └─TableFullScan_13 | 10000.00 | 10000 | mpp[tiflash] | table:t2 | tiflash_task:{time:4.55ms, loops:1, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:2, total_skipped_packs:0, total_scanned_rows:10000, total_skipped_rows:0, total_rs_index_check_time: 0ms, total_read_time: 0ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 1, total_remote_region_num: 0, total_learner_read_time: 0ms} | pushed down filter:empty, keep order:false | N/A | N/A || └─Selection_18(Probe) | 4080000.00 | 4080000 | mpp[tiflash] | | tiflash_task:{time:31.5ms, loops:66, threads:8} | not(isnull(test.t1.id)) | N/A | N/A || └─TableFullScan_17 | 4080000.00 | 4080000 | mpp[tiflash] | table:t1 | tiflash_task:{time:30.9ms, loops:66, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:501, total_skipped_packs:0, total_scanned_rows:4080000, total_skipped_rows:0, total_rs_index_check_time: 0ms, total_read_time: 146ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 8, total_remote_region_num: 0, total_learner_read_time: 0ms} | pushed down filter:empty, keep order:false | N/A | N/A |+----------------------------------------+------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+----------+------+10 rows in set (0.07 sec)
复制代码
MPP_1PHASE_AGG()
提示优化器对指定查询块中所有聚合函数使用一阶段聚合算法，该 Hint 只在 MPP 模式下生效。一阶段聚合算法适用于数据重复性不高的聚合场景。
```sqlmysql> explain analyze select /+ MPP_1PHASE_AGG()/ t1.id,max(t1.col1) from t1 where t1.id<1000000 group by t1.id;+--------------------------------------+------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+---------+------+| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |+--------------------------------------+------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+---------+------+| TableReader_36 | 998656.24 | 999999 | root | | time:190.5ms, loops:1362, RU:0.000000, cop_task: {num: 689, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | MppVersion: 2, data:ExchangeSender_35 | 37.4 KB | N/A || └─ExchangeSender_35 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:187.7ms, loops:2048, threads:8} | ExchangeType: PassThrough | N/A | N/A || └─Projection_6 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:172.8ms, loops:2048, threads:8} | test.t1.id, Column#6 | N/A | N/A || └─Projection_34 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:172.6ms, loops:2048, threads:8} | Column#6, test.t1.id | N/A | N/A || └─HashAgg_32 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:172.2ms, loops:2048, threads:8} | group by:test.t1.id, funcs:max(test.t1.col1)->Column#6, funcs:firstrow(test.t1.id)->test.t1.id, stream_count: 8 | N/A | N/A || └─ExchangeReceiver_28 | 4080000.00 | 4079996 | mpp[tiflash] | | tiflash_task:{time:63.9ms, loops:375, threads:8} | stream_count: 8 | N/A | N/A || └─ExchangeSender_27 | 4080000.00 | 4079996 | mpp[tiflash] | | tiflash_task:{time:121.8ms, loops:66, threads:8} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.t1.id, collate: binary], stream_count: 8 | N/A | N/A || └─Selection_26 | 4080000.00 | 4079996 | mpp[tiflash] | | tiflash_task:{time:89.6ms, loops:66, threads:8} | lt(test.t1.id, 1000000) | N/A | N/A || └─TableFullScan_25 | 4080000.00 | 4080000 | mpp[tiflash] | table:t1 | tiflash_task:{time:88.1ms, loops:66, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:501, total_skipped_packs:0, total_scanned_rows:4080000, total_skipped_rows:0, total_rs_index_check_time: 0ms, total_read_time: 196ms, total_disagg_read_cache_hit_size: 0, total_disagg_read_cache_miss_size: 0}, total_create_snapshot_time: 0ms, total_local_region_num: 8, total_remote_region_num: 0, total_learner_read_time: 0ms} | pushed down filter:empty, keep order:false
划线
评论
复制
发布于: 刚刚阅读数: 2
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/b1ea968a0ca111c8f9ec994cd】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论