背景
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 模式下生效。一阶段聚合算法适用于数据重复性不高的聚合场景。
mysql> 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 | N/A | N/A |
+--------------------------------------+------------+---------+--------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+---------+------+
9 rows in set (0.21 sec)
MPP_2PHASE_AGG()
提示优化器对指定查询块中所有聚合函数使用二阶段聚合算法，该 Hint 只在 MPP 模式下生效。二阶段聚合算法适用于数据重复性较高的聚合场景，它能有效减少数据交换量，提升执行效率。
mysql> explain analyze select /*+ MPP_2PHASE_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_40 | 998656.24 | 999999 | root | | time:352.7ms, loops:1361, 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_39 | 37.4 KB | N/A |
| └─ExchangeSender_39 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:348.5ms, loops:2048, threads:8} | ExchangeType: PassThrough | N/A | N/A |
| └─Projection_6 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:332.4ms, loops:2048, threads:8} | test.t1.id, Column#6 | N/A | N/A |
| └─Projection_35 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:332.3ms, loops:2048, threads:8} | Column#6, test.t1.id | N/A | N/A |
| └─HashAgg_36 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:332ms, loops:2048, threads:8} | group by:test.t1.id, funcs:max(Column#11)->Column#6, funcs:firstrow(test.t1.id)->test.t1.id, stream_count: 8 | N/A | N/A |
| └─ExchangeReceiver_38 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:304.2ms, loops:96, threads:8} | stream_count: 8 | N/A | N/A |
| └─ExchangeSender_37 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:300.3ms, loops:256, threads:8} | ExchangeType: HashPartition, Compression: FAST, Hash Cols: [name: test.t1.id, collate: binary], stream_count: 8 | N/A | N/A |
| └─HashAgg_34 | 998656.24 | 999999 | mpp[tiflash] | | tiflash_task:{time:294.7ms, loops:256, threads:8} | group by:test.t1.id, funcs:max(test.t1.col1)->Column#11 | N/A | N/A |
| └─Selection_26 | 4080000.00 | 4079996 | mpp[tiflash] | | tiflash_task:{time:13.8ms, 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:10.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: 342ms, 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.38 sec)
RESOURCE_GROUP(resource_group_name)
RESOURCE_GROUP(resource_group_name) 用于使用资源管控 (Resource Control) 实现资源隔离。此 Hint 将临时使用指定的资源组执行当前的语句。如果指定的资源组不存在，则该 Hint 将被忽略。作为 DBA，在实践中可以结合绑定执行计划功能，迅速在后台限制某大型 SQL 的执行资源消耗，从而减少对其他 SQL 的影响。
mysql> CREATE RESOURCE GROUP IF NOT EXISTS rg1 RU_PER_SEC = 100;
Query OK, 0 rows affected (0.08 sec)
mysql> explain analyze select /*+ RESOURCE_GROUP(rg1)*/ t1.id,t1.col1 from t1,t2 where t1.id=t2.id and t2.col1='5fd7933817';
+------------------------------------+------------+---------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+------------+---------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-----------+---------+
| HashJoin_27 | 4.09 | 0 | root | | time:1.05ms, loops:1, RU:9.356034, build_hash_table:{total:914.3µs, fetch:914.3µs, build:0s} | inner join, equal:[eq(test.t2.id, test.t1.id)] | 0 Bytes | 0 Bytes |
| ├─IndexLookUp_37(Build) | 1.00 | 0 | root | | time:902.8µs, loops:1 | | 245 Bytes | N/A |
| │ ├─IndexRangeScan_34(Build) | 1.00 | 0 | cop[tikv] | table:t2, index:t2_col1(col1) | time:857.9µs, loops:1, cop_task: {num: 1, max: 816µs, proc_keys: 0, tot_proc: 124µs, tot_wait: 164.6µs, rpc_num: 1, rpc_time: 808.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 8.33µs, max_distsql_concurrency: 1}, tikv_task:{time:0s, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 123.5µs, rocksdb: {block: {cache_hit_count: 2}}} | range:["5fd7933817","5fd7933817"], keep order:false | N/A | N/A |
| │ └─Selection_36(Probe) | 1.00 | 0 | cop[tikv] | | | not(isnull(test.t2.id)) | N/A | N/A |
| │ └─TableRowIDScan_35 | 1.00 | 0 | cop[tikv] | table:t2 | | keep order:false | N/A | N/A |
| └─TableReader_30(Probe) | 4080000.00 | 1024 | root | | time:959.3µs, loops:1, cop_task: {num: 5, max: 934.4µs, min: 834.3µs, avg: 906.5µs, p95: 934.4µs, max_proc_keys: 224, p95_proc_keys: 224, tot_proc: 1.13ms, tot_wait: 981.2µs, rpc_num: 5, rpc_time: 4.38ms, copr_cache_hit_ratio: 0.00, build_task_duration: 12µs, max_distsql_concurrency: 8} | data:Selection_29 | 36.2 KB | N/A |
| └─Selection_29 | 4080000.00 | 1120 | cop[tikv] | | tikv_task:{proc max:1ms, min:0s, avg: 400µs, p80:1ms, p95:1ms, iters:15, tasks:5}, scan_detail: {total_process_keys: 1120, total_process_keys_size: 88480, total_keys: 1125, get_snapshot_time: 765.7µs, rocksdb: {key_skipped_count: 1120, block: {cache_hit_count: 10}}} | not(isnull(test.t1.id)) | N/A | N/A |
| └─TableFullScan_28 | 4080000.00 | 1120 | cop[tikv] | table:t1 | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:15, tasks:5} | keep order:false | N/A | N/A |
+------------------------------------+------------+---------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------+-----------+---------+
8 rows in set (0.00 sec)
总结
TiDB v7.5 版本相较于 TiDB v6.5 版本新增了 6 个 Hint。其中，有 4 个 Hint 与 Tiflash 存储引擎的 MPP 模式密切相关，显著增强了 MPP 模式下执行计划的可控性。我认为 SET_VAR Hint 是 7.5 版本中最强大的 Hint，它允许在 SQL 级别临时修改会话变量，提供了极大的灵活性，适用于多种实际场景。RESOURCE_GROUP Hint 则与资源管控相关，进一步增强了资源管控的能力。