SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);

优化器提示在不同的范围级别上应用：

以下是可用的优化器提示、它们影响的优化策略以及应用范围或范围的总结。后续会有更多详细信息。

禁用优化器提示会阻止优化器使用它。启用优化器提示意味着优化器可以自由使用该策略，如果它适用于语句执行，不是说优化器一定会使用它。

MySQL 支持 SQL 语句中的注释，正如 第 11.7 节“注释” 所描述的。优化器提示必须在 `/*+ ... */` 注释中指定。这就是说，优化器提示使用一种 `/* ... */` C 风格注释语法的变体，其中包含一个 `+` 字符，紧随着 `/*` 注释开头序列。示例：

/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */

空格允许在 `+` 字符之后。

解析器识别优化器提示注释，在 `SELECT`, `UPDATE`, `INSERT`, `REPLACE` 和 `DELETE` 语句的开头。提示允许在这些上下文中：

一个提示注释可以包含多个提示，但一个查询块不能包含多个提示注释。这是有效的：

SELECT /*+ BNL(t1) BKA(t2) */ ...

但这是不正确的：

SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...

当一个提示注释中包含多个提示时，可能存在重复和冲突的问题。以下是一些一般性指导原则。对于特定类型的提示，还可能有额外的规则，如提示描述中所指出的。

查询块名称是标识符，它们遵循通常的规则来确定有效的名称以及如何引用它们（见 第 11.2 节“模式对象名称”）。

提示名称、查询块名称和策略名称是不区分大小写的。对表和索引名称的引用遵循通常的标识符大小写敏感性规则（见 第 11.2.3 节“标识符大小写敏感性”）。

Join-Order 提示影响优化器如何连接表的顺序。

JOIN_固定顺序提示语法：

hint_name([@query_block_name])

其他连接顺序提示语法：

hint_name([@query_block_name] tbl_name [, tbl_name] ...)
hint_name(tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)

语法中涉及到的术语：

示例：

SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
    JOIN_ORDER(t4@subq1, t3)
    JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
           WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
             AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);

提示控制合并到外部查询块的半连接表的行为。如果子查询subq1和subq2被转换为半连接，表t4@subq1和t5@subq2被合并到外部查询块。在这种情况下，外部查询块中指定的提示将控制t4@subq1、t5@subq2表的行为。

优化器根据以下原则解析连接顺序提示：

表级别的提示影响：

这些提示类型适用于特定的表或查询块中的所有表。

表级别提示的语法：

hint_name([@query_block_name] [tbl_name [, tbl_name] ...])
hint_name([tbl_name@query_block_name [, tbl_name@query_block_name] ...])

语法中使用了以下术语：

示例：

SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;

表级提示适用于接收来自先前表的记录的表，而不是发送表。请考虑以下语句：

SELECT /*+ BNL(t2) */ FROM t1, t2;

如果优化器选择先处理 t1，则它会通过在开始从 t2 读取之前缓冲来自 t1 的行，将块嵌套循环连接应用于 t2。如果优化器选择先处理 t2，则提示无效，因为 t2 是发送表。

对于 MERGE 和 NO_MERGE 提示，以下优先规则适用：

索引级别的提示影响了优化器在特定的表或索引上使用哪些索引处理策略。这些提示类型会影响索引条件推送（ICP）、多范围读取（MRR）、索引合并以及范围优化（参见第10.2.1节，“优化SELECT语句”）。

索引级别提示的语法：

hint_name([@query_block_name] tbl_name [index_name [, index_name] ...])
hint_name(tbl_name@query_block_name [index_name [, index_name] ...])

语法中使用了这些术语：

示例：

SELECT /*+ INDEX_MERGE(t1 f3, PRIMARY) */ f2 FROM t1
  WHERE f1 = 'o' AND f2 = f3 AND f3 <= 4;
SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
INSERT INTO t3(f1, f2, f3)
  (SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
   WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1
   AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
SELECT /*+ SKIP_SCAN(t1 PRIMARY) */ f1, f2
  FROM t1 WHERE f2 > 40;

以下示例使用索引合并提示，但其他索引级别的提示遵循相同的原则，关于优化器提示在 optimizer_switch 系统变量或索引提示之前的忽略和优化器提示的先后顺序。

假设表 t1 有列 a, b, c, 和 d；并且存在名为 i_a, i_b, 和 i_c 的索引，它们分别在列 a, b, 和 c 上存在：

SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1
  WHERE a = 1 AND b = 2 AND c = 3 AND d = 4;

索引合并用于 (i_a, i_b, i_c) 的情况。

SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1
  WHERE b = 1 AND c = 2 AND d = 3;

索引合并用于 (i_b, i_c) 的情况。

/*+ INDEX_MERGE(t1 i_a, i_b) NO_INDEX_MERGE(t1 i_b) */

NO_INDEX_MERGE 被忽略，因为有一个先前的提示应用于相同的表。

/*+ NO_INDEX_MERGE(t1 i_a, i_b) INDEX_MERGE(t1 i_b) */

INDEX_MERGE 被忽略，因为有一个先前的提示应用于相同的表。

对于 INDEX_MERGE 和 NO_INDEX_MERGE 优化器提示，这些先后顺序的规则适用：

子查询提示影响是否使用半连接变换以及哪些半连接策略被允许，以及当不使用半连接时，是否使用子查询物化或IN-to-EXISTS变换。有关这些优化的更多信息，请参阅第10.2.2节，“优化子查询、派生表、视图引用和通用表达式”。

Syntax of hints that affect semijoin strategies:

hint_name([@query_block_name] [strategy [, strategy] ...])

这些术语的语法指明了以下内容:

如果一个子查询嵌套在另一个子查询中，并且两个都被合并为外部查询的半连接，任何对最内层查询的半连接策略的指定都会被忽略。SEMIJOIN和NO_SEMIJOIN提示仍然可以用于启用或禁用对这样的嵌套子查询的半连接转换。

如果禁用了DUPSWEEDOUT，有时优化器可能会生成一个远非最优的查询计划。这是由于在贪婪搜索期间进行的启发式剪枝，可以通过设置optimizer_prune_level=0来避免。

示例:

SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

语法，用于影响是否使用子查询材料化或IN-to-EXISTS转换的提示:

SUBQUERY([@query_block_name] strategy)

提示名称始终是SUBQUERY.

对于SUBQUERY提示，这些strategy值是允许的: INTOEXISTS, MATERIALIZATION.

示例:

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);

对于半连接和SUBQUERY提示，一个带有@query_block_name的前缀指定了提示适用的查询块。如果提示中没有包含@query_block_name，则提示应用于它出现的查询块。为查询块分配名称，请参阅Optimizer Hints for Naming Query Blocks.

如果提示注释中包含多个子查询提示，第一个将被使用。其他跟随的相同类型的提示会产生警告。跟随的不同类型的提示将被忽略。

优化器提示的语法，用于影响半连接策略:

MAX_EXECUTION_TIME(N)

Example with a timeout of 1 second (1000 milliseconds):

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

The MAX_EXECUTION_TIME(N) hint sets a statement execution timeout of N milliseconds. If this option is absent or N is 0, the statement timeout established by the max_execution_time system variable applies.

以下是MAX_EXECUTION_TIME提示的适用情况：

以下是SET_VAR提示的用法：

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;

语法如下：

SET_VAR(var_name = value)

var_name是指一个具有会话值的系统变量（尽管不是所有这样的变量都可以命名，因为后面将解释）。value是要分配给该变量的值；这个值必须是一个标量。

使用SET_VAR可以临时更改一个变量的值，正如以下这些语句所示：

mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+
mysql> SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               0 |
+-----------------+
mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+

使用SET_VAR不需要保存和恢复变量值。这使得你可以用一个单独的语句替换多个语句。考虑以下序列：

SET @saved_val = @@SESSION.var_name;
SET @@SESSION.var_name = value;
SELECT ...
SET @@SESSION.var_name = @saved_val;

这个序列可以被下面的单个语句替换：

SELECT /*+ SET_VAR(var_name = value) ...

独立的SET语句允许任何这些命名会话变量的语法：

SET SESSION var_name = value;
SET @@SESSION.var_name = value;
SET @@.var_name = value;

由于SET_VAR提示仅适用于会话变量，会话范围是隐式的，并且SESSION、@@SESSION.和@@都不是需要的也不被允许。包括显式的会话指示符语法将导致SET_VAR提示被忽略并发出警告。

不是所有的会话变量都可以用于SET_VAR。每个系统变量的描述指示哪些变量是可命名的；见第7.1.8节，“服务器系统变量”。你还可以在运行时检查一个系统变量是否可用，尝试使用它与SET_VAR。如果变量不可用，将发出警告：

mysql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 4537
Message: Variable 'collation_server' cannot be set using SET_VAR hint.

多个提示可以给出以设置多个变量：

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off')
           SET_VAR(max_heap_table_size = 1G) */ 1;

如果在同一语句中出现多个相同名称的提示，第一个将被应用，其他将被忽略并发出警告：

SELECT /*+ SET_VAR(max_heap_table_size = 1G)
           SET_VAR(max_heap_table_size = 3G) */ 1;

在这种情况下，第二个提示会被忽略，并且会发出一个冲突的警告。

如果没有指定的系统变量或变量值不正确，将会忽略SET_VAR提示并发出警告：

SELECT /*+ SET_VAR(max_size = 1G) */ 1;
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;

对于第一个语句，没有max_ size变量。对于第二个语句，mrr_cost_based只能取值为on或off，因此尝试将其设置为yes是不正确的。在每种情况下，提示都被忽略并发出警告。

使用SET_VAR提示只能在语句级别进行。如果在子查询中使用，提示将被忽略并发出警告。

复制忽略SET_ VAR在复制语句中的提示，以避免潜在的安全问题。

资源组优化器提示RESOURCE_ GROUP用于资源组管理（见第7.1.16节，“资源组”）。此提示将执行语句的线程临时分配给命名的资源组（仅限于语句执行期间）。它需要RESOURCE_ GROUP_ ADMIN或RESOURCE_ GROUP_ USER权限。

示例：

SELECT /*+ RESOURCE_GROUP(USR_default) */ name FROM people ORDER BY name;
INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);

资源组优化器提示的语法：RESOURCE_ GROUP提示：

RESOURCE_GROUP(group_name)

group_name表示要为执行语句期间的线程分配到的资源组。如果该组不存在，将发出警告并忽略提示。

资源组优化器提示必须出现在初始语句关键字（SELECT、INSERT、REPLACE、UPDATE或DELETE）之后。

资源组优化器提示的替代方案是RESOURCE_ GROUP，它可以使用SET RESOURCE GROUP语句来非临时地将线程分配给资源组。见第15.7.2.4节，“SET RESOURCE GROUP 语句”。

表级、索引级和子查询优化器提示允许将特定的查询块作为其参数语法的一部分进行命名。为了创建这些名称，使用QB_ NAME提示，它将给出该提示所在的查询块的名称：

QB_NAME(name)

QB_ NAME提示可以用来以明确的方式在复杂语句中指定哪些查询块其他提示适用于。它们还允许所有非查询块名称提示在单个提示注释中进行指定，以便更容易理解复杂的语句。考虑以下语句：

SELECT ...
  FROM (SELECT ...
  FROM (SELECT ... FROM ...)) ...

QB_ NAME提示将名称分配给查询块中的表：

SELECT /*+ QB_NAME(qb1) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

然后其他提示可以使用这些名称来引用适当的查询块：

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

结果如下：

查询块名称是标识符，遵循通常的规则来确定哪些名称有效以及如何引用它们（见第11.2节，“模式对象名称”）。例如，查询块名称中包含空格的名称必须被引号括起来，这可以使用反引号来完成：

SELECT /*+ BKA(@`my hint name`) */ ...
  FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...

如果启用了ANSI_ QUOTESSQL模式，那么在双引号中引用查询块名称也是可能的：

SELECT /*+ BKA(@"my hint name") */ ...
  FROM (SELECT /*+ QB_NAME("my hint name") */ ...) ...