产品动态
产品公告
通过 HINT 优化 SQL 执行
什么是 Hint
优化器一般会为用户 SQL 选择最优的执行计划，但是在某些场景下，例如统计信息估计误差、代价模型拟合偏差等，优化器生成的执行计划可能就不是最优的。这个时候用户可以通过 Hint 机制指导优化器生成更优的执行计划。
Hints 不区分大小写，通过 /*+ ... */ 注释的形式跟在 SELECT 关键字后面，多个 Hint 通过空格或逗号间隔开。以下是一个 Hint 使用实例。
SELECT /*+ [hint_text] [hin_text]... */ * FROM ....
Hint 生效范围
Hint 以 Query Block 为单位生效，在 DML 语句中，每一个 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 所在的查询块的名字为 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 方式分发数据。 |
BROADCASR_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 fileter 的开启与关闭。 |
只读分析引擎 Hint 语法详解
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，结果如下图所示。
注意：
此 Hint 只在生成分布式计划时生效，在单机计划时无效。
只读分析引擎优化器会选择 Hash Join 的 Build 端作为 Broadcast 的广播表，若需要进行调整，可结合 HASH_JOIN_BUILD 一起进行调整。
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，最终执行计划如下图所示。
注意：
存在多个 LEADING Hint 时会导致 Hint 失效。
优化器无法按照 LEADING 进行表连接时 Hint 会失效。
SET_VAR(NAME="VALUE")
使用说明
SET_VAR(XXXX="YY")，用于在 SQL 执行期间临时修改系统变量，在 SQL 执行完毕后，指定系统变量会自动恢复为原始值，使用方法如下所示。
参考示例
SELECT /*+ SET_VAR(max_threads=64) */ * FROM t1
此条 SQL 通过 SET_VAR Hint 临时指定了在 SQL 执行期间的最大线程数为 64。
注意：
NO_PX_JOIN_FILTER_ID(ID)/PX_JOIN_FILTER_ID(ID)
使用说明
no_px_join_filter_id(ID)/px_join_filter_id(ID)，用于控制优化器关闭或开启 RuntimeFilter。
参考示例
常见 Hint 问题
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 正确地放在指定关键字的后面，它将无法生效。例如：
SELECT * /*+ SET_VAR(max_threads = 64)) */ FROM t;SHOW WARNINGS;
Warning 信息如下。
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Warning | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use [parser:8066]Optimizer hint can only be followed by certain keywords like SELECT etc. |+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)
在如上的示例中，您需要将 Hint 直接放在 SELECT 关键字之后。
文档反馈