什么是 Hint
优化器一般会为用户的 SQL 选择最优的执行计划。但在某些场景下（例如统计信息估计存在偏差、代价模型拟合不够精准等），优化器生成的执行计划可能并非最优。此时您可以通过 Hint 机制指导优化器生成更优的执行计划。
Hint 不区分大小写，通过
/*+ ... */ 注释形式紧跟在 SELECT 关键字后使用，多个 Hint 之间使用空格或逗号分隔。示例如下：SELECT /*+ [hint_text] [hint_text] ... */ * FROM ...;
Hint 生效范围
Hint 以 Query Block 为单位生效。在查询语句中，每一个 Query Block 都会有一个 QB_NAME（Query Block Name），列存只读分析引擎按照
@sel_1、@sel_2 的方式，从左到右依次为每个 Query Block 生成 QB_NAME。例如下面这条 SQL：SELECT * FROM (SELECT * FROM t) t1, (SELECT * FROM t) t2;
该 SQL 包含三个 Query Block，最外层
SELECT 所在的 Query Block 名为 sel_1，两个 SELECT 子查询的名字依次为 sel_2 和 sel_3，编号依次递增。Hint 中可以通过 QB_NAME 控制 Hint 的作用范围以及作用对象。如果 Hint 中没有显式指定 QB_NAME，则 Hint 的作用范围为当前 Hint 所在的 Query Block。示例如下：SELECT /*+ HASH_JOIN_PROBE(@sel_2 t1) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;SELECT /*+ HASH_JOIN_PROBE(t1@sel_2) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
以上两条 SQL 使用了两种不同方式在 Hint 中指定 QB_NAME：
第一条 SQL 在 Hint 的第一个参数位置指定 QB_NAME，并通过空格与其他参数分隔。
第二条 SQL 在参数后追加
@QB_NAME 来指定 Hint 的生效范围。支持的 Hint 概览
列存只读分析引擎目前支持的 Hint 名称、语法和语义如下表所示。
名称 | 语法 | 语义 |
SHUFFLE_JOIN | SHUFFLE_JOIN([@QB_NAME] tbl1_name, tbl2_name ...) | 指定 JOIN 操作采用 Shuffle 方式分发数据。 |
BROADCAST_JOIN | BROADCAST_JOIN([@QB_NAME] tbl1_name, tbl2_name ...) | 指定 JOIN 操作采用 Broadcast 方式分发数据。 |
HASH_JOIN_BUILD | HASH_JOIN_BUILD([@QB_NAME] tbl1_name, tbl2_name ...) | 指定 Hash Join 操作中的 Build 端表。 |
HASH_JOIN_PROBE | HASH_JOIN_PROBE([@QB_NAME] tbl1_name, tbl2_name ...) | 指定 Hash Join 操作中的 Probe 端表。 |
LEADING | LEADING([@QB_NAME] tbl1_name, tbl2_name ...) | 指定 JOIN 操作的连接顺序（Join Order）。 |
SET_VAR | SET_VAR(setting_name = value) | 在 SQL 级别临时修改系统变量。 |
PX_JOIN_FILTER_ID / NO_PX_JOIN_FILTER_ID | PX_JOIN_FILTER_ID(rf_id1, rf_id2 ...) NO_PX_JOIN_FILTER_ID(rf_id1, rf_id2 ...) | 控制 Runtime Filter 的开启与关闭。 |
Hint 语法详解
SHUFFLE_JOIN
使用说明
SHUFFLE_JOIN(t1_name, t2_name ...) 用于控制列存只读分析引擎优化器在执行 JOIN 操作时采用 Shuffle Join 算法：将左右两个表的数据按 JOIN 键打散并重新分布，再进行 JOIN 操作。参考示例
EXPLAIN SELECT /*+ SHUFFLE_JOIN(t1) */ * FROM t1, t2 WHERE t1.id = t2.id;
该 SQL 中
t1 与 t2 进行 JOIN 操作，通过 SHUFFLE_JOIN 指定 Join 数据分发方式为 Shuffle Join。最终执行计划如下图所示，Details 中第 2 行和第 5 行的 EXCHANGE TYPE 变为 HASH，代表采用了 Hash Shuffle。
除了指定单表外，还可以指定 JOIN 中间结果进行数据重分布。
EXPLAIN SELECT /*+ SHUFFLE_JOIN((t1@sel_2, t2@sel_2)) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
如上所示，将
t1 与 t2 用括号括起来，并指定每个表的 QB_NAME，即可指定 t1 与 t2 的 JOIN 中间结果与 t3 进行 JOIN 时采用 Shuffle Join。
注意：
该 Hint 只在生成分布式执行计划时生效，在单机执行计划下无效。
BROADCAST_JOIN
使用说明
BROADCAST_JOIN(t1_name, t2_name ...) 用于控制列存只读分析引擎优化器在执行 JOIN 操作时采用 Broadcast Join 算法：将指定表的数据广播到所有参与计算的节点上，再进行 JOIN 操作。参考示例
EXPLAIN SELECT /*+ BROADCAST_JOIN(t1) */ * FROM t1, t2 WHERE t1.id = t2.id;
该 SQL 中
t1 与 t2 进行 JOIN 操作，通过 BROADCAST_JOIN 指定 JOIN 方式为 Broadcast Join。最终执行计划如下图所示，Details 中第2行和第5行的 EXCHANGE TYPE 变为 BCJ，代表采用了 Broadcast。
除了指定单表外，还可以指定 JOIN 中间结果进行数据广播。
EXPLAIN SELECT /*+ BROADCAST_JOIN((t1@sel_2, t3@sel_1)) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
如上所示，将
t1 与 t3 用括号括起来，并指定每个表的 QB_NAME，即可指定 t1 与 t3 的 JOIN 中间结果在与外层 JOIN 时采用 Broadcast Join。结果如下图所示。
注意：
该 Hint 只在生成分布式执行计划时生效，在单机执行计划下无效。
列存只读分析引擎优化器默认会选择 Hash Join 的 Build 端作为 Broadcast 的广播表。如需调整广播端，请结合
HASH_JOIN_BUILD 一起使用。HASH_JOIN_BUILD
使用说明
HASH_JOIN_BUILD(t1_name, t2_name ...) 用于控制列存只读分析引擎优化器对指定表使用 Hash Join 算法，并将指定表作为 Hash Join 的 Build 端，即用指定表来构建哈希表。参考示例
EXPLAIN SELECT /*+ HASH_JOIN_BUILD(t2) */ * FROM t t1, t t2 WHERE t1.a = t2.a;
该 SQL 指定
t2 表作为 Hash Join 的 Build 端，最终执行计划如下图所示。
除了指定单表外，还可以指定 JOIN 中间结果作为 Build 端，示例如下。
EXPLAIN SELECT /*+ HASH_JOIN_BUILD((t1@sel_2, t3@sel_1)) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
如上所示，将相关表用括号括起来，并指定每个表的 QB_NAME 即可。
HASH_JOIN_PROBE
使用说明
HASH_JOIN_PROBE(t1_name, t2_name ...) 用于控制列存只读分析引擎优化器对指定表使用 Hash Join 算法，并将指定表作为 Hash Join 的 Probe 端，即用指定表来探测哈希表。参考示例
EXPLAIN SELECT /*+ HASH_JOIN_PROBE(t2) */ * FROM t t1, t t2 WHERE t1.a = t2.a;
该 SQL 指定
t2 表作为 Hash Join 的 Probe 端，最终执行计划如下图所示。
除了指定单表外，还可以指定 JOIN 中间结果作为 Probe 端，示例如下。
EXPLAIN SELECT /*+ HASH_JOIN_PROBE((t1@sel_2, t3@sel_1)) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
如上所示，将相关表用括号括起来，并指定每个表的 QB_NAME 即可。最终执行计划如下图所示。
LEADING
使用说明
LEADING(t1_name, t2_name ...) 用于控制列存只读分析引擎优化器在 Join Reorder 阶段生成的连接顺序（Join Order）。优化器会按照 LEADING 中出现的顺序确定 Join Order。参考示例
EXPLAIN SELECT /*+ LEADING(t1, t3, t2, t4) */ * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.a = t3.a AND t3.a = t4.a;
该 SQL 通过
LEADING 显式指定 t1 表首先与 t3 表进行 JOIN，接着与 t2 表进行 JOIN，最后与 t4 表进行 JOIN。最终执行计划如下图所示。
上述方式只能生成左深树（Left Deep Tree）。列存只读分析引擎还为
LEADING 提供了一种进阶语法，通过括号嵌套可以生成 Bushy Tree。示例如下：EXPLAIN SELECT /*+ LEADING((t1, t3), (t2, t4)) */ * FROM t1, t2, t3, t4 WHERE t1.a = t2.a AND t2.a = t3.a AND t3.a = t4.a;
该 SQL 中
LEADING 通过 (t1, t3) 控制优化器先进行 t1 与 t3 的 JOIN，通过 (t2, t4) 控制优化器先进行 t2 与 t4 的 JOIN，最后通过 ((t1, t3), (t2, t4)) 控制优化器将上述两个 JOIN 的结果再做 JOIN。最终执行计划如下图所示。
注意：
同一查询中存在多个
LEADING Hint 时，会导致 Hint 失效。当优化器无法按照
LEADING 指定的顺序进行表连接时，Hint 会失效。SET_VAR
使用说明
SET_VAR(setting_name = value) 用于在 SQL 执行期间临时修改系统变量。SQL 执行完毕后，被修改的系统变量会自动恢复为原始值。使用方法如下：参考示例
SELECT /*+ SET_VAR(max_threads = 64) */ * FROM t1;
该 SQL 通过
SET_VAR Hint 临时指定 SQL 执行期间的最大线程数为64。注意：
PX_JOIN_FILTER_ID 与 NO_PX_JOIN_FILTER_ID
使用说明
PX_JOIN_FILTER_ID(rf_id1, rf_id2 ...) 与 NO_PX_JOIN_FILTER_ID(rf_id1, rf_id2 ...) 用于控制优化器对指定 ID 的 Runtime Filter 的开启与关闭。参考示例
常见 Hint 问题
MySQL 客户端清除 Hint 导致不生效
MySQL 命令行客户端在5.7版本之前，默认会清除 Optimizer Hint。如需在这些早期版本的客户端中使用 Hint 语法，启动客户端时需添加
-c（即 --comments）选项。例如：mysql -h 127.0.0.1 -P 4000 -u root -c
跨库查询未指定库名导致 Hint 不生效
对于查询中跨库访问的表，需在 Hint 中显式指定数据库名，否则可能出现 Hint 不生效的问题。例如：
SELECT /*+ SHUFFLE_JOIN(t1) */ * FROM test1.t1, test2.t2 WHERE t1.id = t2.id;
由于
t1 表不在当前数据库中，且 Hint 未指定库名，导致 Hint 失效。Warning 信息如下：tdsql> SHOW WARNINGS;+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+| Warning | 1815 | There are no matching table names for (t1) in optimizer hint /*+ SHUFFLE_JOIN(t1) */ or /*+ SHUFFLE_JOIN(t1) */. Maybe you can use the table alias name |+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
QB_NAME 未指定或指定错误导致 Hint 不生效
对于包含多个 Query Block 的查询，若 Hint 未写在目标表所在的 Query Block 中，需要在 Hint 中显式指定 QB_NAME。否则可能出现 Hint 不生效的问题。例如：
SELECT /*+ HASH_JOIN_PROBE(t2) */ * FROM (SELECT t1.a, t1.b FROM t t1, t t2 WHERE t1.a = t2.a) t1, t t3 WHERE t1.b = t3.b;
由于 Hint 中的
t2 表未显式指定 QB_NAME，导致 Hint 没有生效。Warning 信息如下：tdsql> SHOW WARNINGS;+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------------------------------------------------------------------------------+| Warning | 1815 | There are no matching table names for (t2) in optimizer hint /*+ HASH_JOIN_PROBE(t2) */. Maybe you can use the table alias name |+---------+------+---------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
此时可通过
EXPLAIN 查看 SQL 的执行计划，确认每个表所在 Query Block 的 QB_NAME。
Hint 位置不正确导致不生效
Hint 必须严格按照 Optimizer Hint 语法放置在指定关键字（如
SELECT）之后，否则将无法生效。例如：tdsql> SELECT * /*+ SET_VAR(max_threads = 64) */ FROM t;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use line 1 column 42 near "/*+ SET_VAR(max_threads = 64) */ FROM t"
