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 节，“Schema 对象名称”）。

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

连接顺序提示影响表的连接顺序。

语法 JOIN_FIXED_ORDER 的提示：

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;

以下示例使用Index Merge提示，但其他索引级提示遵循相同的原则，忽略提示和优化器提示在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;

在这种情况下，Index Merge用于(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;

在这种情况下，Index Merge用于(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 节，“优化子查询、派生表、视图引用和公共表表达式”。

影响半连接策略的提示语法：

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 提示，leading @query_block_name 指定了应用于哪个查询块的提示。如果提示不包含 leading @query_block_name，则应用于当前查询块中。要为查询块命名，请参阅 优化器提示命名查询块。

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

MAX_EXECUTION_TIME 提示仅适用于 SELECT 语句。它将语句执行时间限制为 N 毫秒（超时值），然后服务器终止语句：

MAX_EXECUTION_TIME(N)

示例，超时为 1 秒（1000 毫秒）：

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

MAX_EXECUTION_TIME(N) 提示设置语句执行超时为 N 毫秒。如果该选项不存在或 N 为 0，则应用系统变量 max_execution_time 设置的超时。

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 提示语法：

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.

SET_VAR 语法允许设置单个变量，但可以提供多个提示以设置多个变量：

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 表示要将线程分配到的资源组。如果组不存在，将发出警告并忽略提示。

RESOURCE_GROUP 提示必须出现在初始语句关键字 (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_QUOTES SQL 模式，也可以使用双引号来引用查询块名称：

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