一、什么是 Hint
Hints 不区分大小写，通过 /*+ … */ 注释的形式跟在 SELECT 关键字后面，多个 Hint 通过空格或逗号间隔开。以下是一个 Hint 使用示例。
SELECT /*+ [hint_text] [hin_text]… */ * FROM ….
二、Hint 生效范围
Hint 以 Query Block 为单位生效，在查询语句中，每一个查询 Block 都会有一个 QB_NAME (Query Block Name)，只读分析引擎会按照 @sel_1、@sel_2 的方式，从左到右依次为每个查询 Block 生成 QB_NAME。以下面的 SQL 为例。
SELECT * FROM (SELECT * FROM t) t1, (SELECT * FROM t) t2;
这条 SQL 中包含了三个 Query Block，最外面一层 SELECT 所在的查询块的名字为 sel_1，两个 SELECT 子查询的名字依次为 sel_2 和 sel_3，编号依次递增。Hint 中使用 QB_NAME 即可控制 Hint 的作用范围以及 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 级别设置系统参数。 |
| NO_PX_JOIN_FILTER_ID/PX_JOIN_FILTER_ID | NO_PX_JOIN_FILTER_ID(rf_id1,rf_id2…)/PX_JOIN_FILTER_ID(rf_id1,rf_id2…) | 控制 runtime filter 的开启与关闭。 |
四、SHUFFLE_JOIN(t1_name, t2_name …)
使用说明
SHUFFLE_JOIN(t1_name, t2_name …)，用于控制只读分析引擎优化器在进行 JOIN 操作的时候采用 Shuffle Join 算法，将左右两个表数据进行打散重新分布，再进行 JOIN 操作返回结果。
参考示例
EXPLAIN SELECT /*+ SHUFFLE_JOIN(t1) */ * FROM t1, t2 WHERE t1.id = t2.id;
此条 SQL 中 t1 与 t2 表进行 JOIN 操作，通过 SHUFFLE_JOIN Hint 指定了 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 表与 t3 表用括号括起来，并指定好每个表的 QB_NAME，即可指定 t1 与 t3 表 JOIN 的中间结果与 t2 表 JOIN 时采用 Shuffle Join。
注意：此 Hint 只在生成分布式计划时生效，在单机计划时无效。
五、BROADCAST_JOIN(t1_name [, tl_name …])
使用说明
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 Hint 指定了 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 的中间结果与 t2 表 JOIN 时采用 Broadcast Join，结果如下所示。
六、HASH_JOIN_BUILD(t1_name,t2_name…)
使用说明
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;
如上所示，通过将 t1 表与 t2 表用括号括起来，并指定好每个表的 QB_NAME 即可。
七、HASH_JOIN_PROBE(t1_name,t2_name…)
使用说明
HASH_JOIN_PROBE(t1_name,t2_name …)，用于控制优化器对指定表使用 HASH JOIN 算法，同时将指定表作为 HASH JOIN 算法的 Probe 端，即用指定表作为 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;
如上所示，通过将 t1 表与 t2 表用括号括起来，并指定好每个表的 QB_NAME 即可，最终计划如下所示。
八、LEADING(t1_name,t2_name …)
使用说明
Leading(t1_name,t2_name…)，用于控制优化器在 Join Reorder 阶段生成的 Join Order，优化器会按照 Leading Hint 中出现的顺序来确定 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 Hint 显示指定了 t1 表首先与 t3 表进行 JOIN 操作，接着与 t2 表进行 JOIN，最终与 t4 表进行 JOIN，最终计划如下所示。
上述方法指定 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 Hint 首先通过将 (t1,t3) 控制优化器先进行 t1 表和 t3 表的 JOIN，之后将通过 (t2,t4) 控制优化器进行 t2 表与 t4 表的 JOIN，最后通过 ((t1,t3),(t2,t4)) 控制优化器将上述两个 JOIN 操作的结果进行 JOIN，最终执行计划如下所示。
九、SET_VAR(NAME="VALUE")
使用说明
SET_VAR(XXXX="YY")，用于在 SQL 执行期间临时修改系统变量，在 SQL 执行完毕后，指定系统变量会自动恢复为原始值，使用方法如下所示。
参考示例
SELECT /*+ SET_VAR(max_threads=64) */ * FROM t1
此条 SQL 通过 SET_VAR Hint 临时指定了在 SQL 执行期间的最大线程数为 64。
注意：并不是所有参数都支持 SET_VAR Hint，使用前请确认目标参数可以支持 Hint 修改。支持通过 Hint 修改的 参数请参考 系统变量。
十、NO_PX_JOIN_FILTER_ID(ID)/PX_JOIN_FILTER_ID(ID)
使用说明
no_px_join_filter_id(ID)/px_join_filter_id(ID)，用于控制优化器关闭或开启 RuntimeFilter。
参考示例
具体使用方式参考 Runtime Filter 使用手册。
十一、MYSQL 客户端消除 Hint 导致不生效
MySQL 命令行客户端在 5.7 版本之前默认清除了 Optimizer Hints。如果需要在这些早期版本的客户端中使用 Hint 语法，需要在启动客户端时加上 –comments 选项，例如：mysql -h 127.0.0.1 -P 4000 -u root -c。
十二、跨库查询不指定库名导致 Hint 不生效
对于查询中有需要跨库访问的表，需要显示的在 Hint 中指定数据库名称，否则可能会出现 Hint 不生效的问题。例如对于下面这条 SQL：
SELECT /*+ SHUFFLE_JOIN(t1) */ * FROM test1.t1, test2.t2 WHERE t1.id = t2.id;
由于当前 t1表在当前 database 中，从而导致 Hint 失效。Warning 信息如下所示。
mysql> 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 不生效
对于有多个 QB_NAME 的查询，如果 Hint 没有写在目标表的 Query Block 中，则需要显示的在 Hint 中指定 QB_NAME，若没有指定，则可能发生 Query 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 中的 t1表没有显示指定 QB_NAME，导致 Hint 没有生效，Warning 信息如下。
mysql> 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)
此时可通过关键字查询 SQL 计划，确认每个表所在 Query Block 的 QB_NAME。
十四、Hint 位置不正确导致不生效
如果没有按照 Optimizer Hints 语法将 Hint 正确地放在指定关键字的后面，它将无法生效。例如：
MySQL> 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"