优化器提示适用于不同的作用域级别：Optimizer hints apply at different scope levels:

下表总结了可用的优化器提示、它们影响的优化器策略以及它们适用的作用域或作用域级别。更多详细信息将在后面提供。The following table summarizes the available optimizer hints, the optimizer strategies they affect, and the scope or scopes at which they apply. More details are given later.

禁用优化会阻止优化器使用它。启用优化意味着如果适用于语句执行，优化器可以自由使用该策略，但并不意味着优化器一定会使用它。Disabling an optimization prevents the optimizer from using it. Enabling an optimization means the optimizer is free to use the strategy if it applies to statement execution, not that the optimizer necessarily uses it.

MySQL 支持在 SQL 语句中使用注释，如 第 11.7 节，“注释” 所述。优化器提示必须在 /* ... */ 注释中指定。也就是说，优化器提示使用 /*+ ... */ C 风格注释语法的一种变体，并且在 /* 注释开头序列之后有一个 + 字符。示例：MySQL supports comments in SQL statements as described in Section 11.7, “Comments”. Optimizer hints must be specified within /*+ ... */ comments. That is, optimizer hints use a variant of /* ... */ C-style comment syntax, with a + character following the /* comment opening sequence. Examples:

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

在 + 字符之后允许有空白字符。Whitespace is permitted after the + character.

解析器在 SELECT、UPDATE、INSERT、REPLACE 和 DELETE 语句的初始关键字之后识别优化器提示注释。提示允许在以下上下文中使用：The parser recognizes optimizer hint comments after the initial keyword of SELECT, UPDATE, INSERT, REPLACE, and DELETE statements. Hints are permitted in these contexts:

提示注释可以包含多个提示，但查询块不能包含多个提示注释。这是有效的：A hint comment may contain multiple hints, but a query block cannot contain multiple hint comments. This is valid:

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

但这是无效的：But this is invalid:

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

当提示注释包含多个提示时，可能会出现重复和冲突的情况。适用以下一般指南。对于特定的提示类型，可能适用附加规则，如提示描述中所示。When a hint comment contains multiple hints, the possibility of duplicates and conflicts exists. The following general guidelines apply. For specific hint types, additional rules may apply, as indicated in the hint descriptions.

查询块名称是标识符，并遵循关于名称有效性和如何引用它们的常规规则（参见 第 11.2 节，“模式对象名称”）。Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (see Section 11.2, “Schema Object Names”).

提示名称、查询块名称和策略名称不区分大小写。对表和索引名称的引用遵循常规标识符的大小写敏感性规则（参见 第 11.2.3 节，“标识符大小写敏感性”）。Hint names, query block names, and strategy names are not case-sensitive. References to table and index names follow the usual identifier case-sensitivity rules (see Section 11.2.3, “Identifier Case Sensitivity”).

连接顺序提示会影响优化器连接表的顺序。Join-order hints affect the order in which the optimizer joins tables.

JOIN_FIXED_ORDER 提示的语法：Syntax of the JOIN_FIXED_ORDER hint:

hint_name([@query_block_name])

其他连接顺序提示的语法：Syntax of other join-order hints:

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

语法涉及以下术语：The syntax refers to these terms:

示例：Example:

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 表的行为。Hints control the behavior of semijoin tables that are merged to the outer query block. If subqueries subq1 and subq2 are converted to semijoins, tables t4@subq1 and t5@subq2 are merged to the outer query block. In this case, the hint specified in the outer query block controls the behavior of t4@subq1, t5@subq2 tables.

优化器根据以下原则解析连接顺序提示：The optimizer resolves join-order hints according to these principles:

表级提示影响：Table-level hints affect:

这些提示类型适用于特定表，或查询块中的所有表。These hint types apply to specific tables, or all tables in a query block.

表级提示的语法：Syntax of table-level hints:

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

语法涉及以下术语：The syntax refers to these terms:

示例：Examples:

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;

表级提示适用于从前一个表接收记录的表，而不是发送表。考虑以下语句：A table-level hint applies to tables that receive records from previous tables, not sender tables. Consider this statement:

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

如果优化器选择先处理t1，它会在开始读取t2之前，通过缓冲t1的行来对t2应用块嵌套循环连接。如果优化器改为选择先处理t2，则提示无效，因为t2是发送表。If the optimizer chooses to process t1 first, it applies a Block Nested-Loop join to t2 by buffering the rows from t1 before starting to read from t2. If the optimizer instead chooses to process t2 first, the hint has no effect because t2 is a sender table.

对于MERGE和NO_MERGE提示，适用以下优先规则：For the MERGE and NO_MERGE hints, these precedence rules apply:

索引级提示会影响优化器对特定表或索引使用的索引处理策略。这些提示类型会影响索引条件下推（ICP）、多范围读取（MRR）、索引合并以及范围优化的使用（参见 第 10.2.1 节，“优化 SELECT 语句”）。Index-level hints affect which index-processing strategies the optimizer uses for particular tables or indexes. These hint types affect use of Index Condition Pushdown (ICP), Multi-Range Read (MRR), Index Merge, and range optimizations (see Section 10.2.1, “Optimizing SELECT Statements”).

索引级提示的语法：Syntax of index-level hints:

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

语法涉及以下术语：The syntax refers to these terms:

示例：Examples:

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系统变量或索引提示的优先级方面遵循相同原则。The following examples use the Index Merge hints, but other index-level hints follow the same principles regarding hint ignoring and precedence of optimizer hints in relation to the optimizer_switch system variable or index hints.

假设表 t1 有列 a、b、c 和 d；并且在 a、b 和 c 上分别存在名为 i_a、i_b 和 i_c 的索引：Assume that table t1 has columns a, b, c, and d; and that indexes named i_a, i_b, and i_c exist on a, b, and c, respectively:

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) 使用索引合并。Index Merge is used for (i_a, i_b, i_c) in this case.

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 is used for (i_b, i_c) in this case.

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

NO_INDEX_MERGE 被忽略，因为同一表已有前置提示。NO_INDEX_MERGE is ignored because there is a preceding hint for the same table.

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

INDEX_MERGE 被忽略，因为同一张表之前已经有一个提示。INDEX_MERGE is ignored because there is a preceding hint for the same table.

对于 INDEX_MERGE 和 NO_INDEX_MERGE 优化器提示，适用以下优先规则：For the INDEX_MERGE and NO_INDEX_MERGE optimizer hints, these precedence rules apply:

子查询提示会影响是否使用半连接转换以及允许哪些半连接策略；当不使用半连接时，还会影响是否使用子查询物化或 IN-到-EXISTS 转换。有关这些优化的更多信息，请参见 第 10.2.2 节，“优化子查询、派生表、视图引用和公共表表达式”。Subquery hints affect whether to use semijoin transformations and which semijoin strategies to permit, and, when semijoins are not used, whether to use subquery materialization or IN-to-EXISTS transformations. For more information about these optimizations, see Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”.

影响半连接策略的提示语法：Syntax of hints that affect semijoin strategies:

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

语法涉及以下术语：The syntax refers to these terms:

如果一个子查询嵌套在另一个子查询中，并且两者都合并到外部查询的半连接中，则对最内层查询的半连接策略的任何指定都会被忽略。SEMIJOIN 和 NO_SEMIJOIN 提示仍然可以用于启用或禁用此类嵌套子查询的半连接转换。If one subquery is nested within another and both are merged into a semijoin of an outer query, any specification of semijoin strategies for the innermost query are ignored. SEMIJOIN and NO_SEMIJOIN hints can still be used to enable or disable semijoin transformations for such nested subqueries.

如果禁用 DUPSWEEDOUT，有时优化器可能生成远非最优的查询计划。这是由于贪婪搜索期间的启发式修剪导致的，可以通过设置 optimizer_prune_level=0 来避免。If DUPSWEEDOUT is disabled, on occasion the optimizer may generate a query plan that is far from optimal. This occurs due to heuristic pruning during greedy search, which can be avoided by setting optimizer_prune_level=0.

示例：Examples:

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到EXISTS转换的提示语法：Syntax of hints that affect whether to use subquery materialization or IN-to-EXISTS transformations:

SUBQUERY([@query_block_name] strategy)

提示名称始终是SUBQUERY。The hint name is always SUBQUERY.

对于SUBQUERY提示，允许使用这些strategy值：INTOEXISTS、MATERIALIZATION。For SUBQUERY hints, these strategy values are permitted: INTOEXISTS, MATERIALIZATION.

示例：Examples:

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，则提示适用于其所在的查询块。要为查询块分配名称，请参见 用于命名查询块的优化提示。For semijoin and SUBQUERY hints, a leading @query_block_name specifies the query block to which the hint applies. If the hint includes no leading @query_block_name, the hint applies to the query block in which it occurs. To assign a name to a query block, see Optimizer Hints for Naming Query Blocks.

如果提示注释包含多个子查询提示，则使用第一个。如果之后还有同类型的提示，它们会产生警告。其他类型的后续提示会被静默忽略。If a hint comment contains multiple subquery hints, the first is used. If there are other following hints of that type, they produce a warning. Following hints of other types are silently ignored.

MAX_EXECUTION_TIME 提示仅允许用于 SELECT 语句。它对语句允许执行的最长时间设置限制 N（以毫秒为单位的超时时间），超过该时间服务器将终止该语句：The MAX_EXECUTION_TIME hint is permitted only for SELECT statements. It places a limit N (a timeout value in milliseconds) on how long a statement is permitted to execute before the server terminates it:

MAX_EXECUTION_TIME(N)

示例，超时时间为 1 秒（1000 毫秒）：Example with a timeout of 1 second (1000 milliseconds):

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

MAX_EXECUTION_TIME(N) 提示将语句执行超时设置为 N 毫秒。如果该选项不存在或 N 为 0，则适用由 max_execution_time 系统变量设定的语句超时。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 提示适用如下：The MAX_EXECUTION_TIME hint is applicable as follows:

SET_VAR 提示临时设置系统变量的会话值（在单条语句的执行期间）。示例：The SET_VAR hint sets the session value of a system variable temporarily (for the duration of a single statement). Examples:

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 提示的语法：Syntax of the SET_VAR hint:

SET_VAR(var_name = value)

var_name 指定具有会话值的系统变量的名称（尽管并非所有此类变量都可以命名，如后文所述）。value 是要分配给变量的值；该值必须是标量。var_name names a system variable that has a session value (although not all such variables can be named, as explained later). value is the value to assign to the variable; the value must be a scalar.

SET_VAR 进行临时变量更改，如以下语句所示：SET_VAR makes a temporary variable change, as demonstrated by these statements:

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 时，无需保存和恢复变量值。这使您可以用一条语句替换多条语句。请考虑以下语句序列：With SET_VAR, there is no need to save and restore the variable value. This enables you to replace multiple statements by a single statement. Consider this sequence of statements:

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

这个序列可以被这条语句替代：The sequence can be replaced by this single statement:

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

独立的 SET 语句允许使用这些语法来命名会话变量：Standalone SET statements permit any of these syntaxes for naming session variables:

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

因为 SET_VAR 提示仅适用于会话变量，会话范围是隐含的，因此 SESSION、@@SESSION. 和 @@ 既不需要也不允许。包括显式的会话指示语法会导致 SET_VAR 提示被忽略并伴随警告。Because the SET_VAR hint applies only to session variables, session scope is implicit, and SESSION, @@SESSION., and @@ are neither needed nor permitted. Including explicit session-indicator syntax results in the SET_VAR hint being ignored with a warning.

并非所有会话变量都允许与 SET_VAR 一起使用。各个系统变量的描述会指明每个变量是否可作为提示使用；请参阅 第 7.1.8 节，“服务器系统变量”。您也可以在运行时通过尝试将其与 SET_VAR 一起使用来检查系统变量。如果变量不可作为提示使用，将会出现警告：Not all session variables are permitted for use with SET_VAR. Individual system variable descriptions indicate whether each variable is hintable; see Section 7.1.8, “Server System Variables”. You can also check a system variable at runtime by attempting to use it with SET_VAR. If the variable is not hintable, a warning occurs:

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 语法只允许设置单个变量，但可以给出多个提示以设置多个变量：SET_VAR syntax permits setting only a single variable, but multiple hints can be given to set multiple variables:

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

如果在同一语句中出现多个具有相同变量名的提示，则应用第一个提示，其他提示将被忽略并发出警告：If several hints with the same variable name appear in the same statement, the first one is applied and the others are ignored with a warning:

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

在这种情况下，第二个提示会被忽略，并发出其冲突的警告。In this case, the second hint is ignored with a warning that it is conflicting.

如果没有指定名称的系统变量或变量值不正确，SET_VAR 提示会被忽略并显示警告：A SET_VAR hint is ignored with a warning if no system variable has the specified name or the variable value is incorrect:

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 是不正确的。在每种情况下，该提示都会被忽略并显示警告。For the first statement, there is no max_size variable. For the second statement, mrr_cost_based takes values of on or off, so attempting to set it to yes is incorrect. In each case, the hint is ignored with a warning.

SET_VAR 提示仅允许在语句级别使用。如果在子查询中使用该提示，将会被忽略并发出警告。The SET_VAR hint is permitted only at the statement level. If used in a subquery, the hint is ignored with a warning.

副本在复制语句中会忽略 SET_VAR 提示，以避免潜在的安全问题。Replicas ignore SET_VAR hints in replicated statements to avoid the potential for security issues.

RESOURCE_GROUP 优化器提示用于资源组管理（参见 第 7.1.16 节，“资源组”）。此提示将执行语句的线程临时分配到指定资源组（在语句执行期间）。它需要 RESOURCE_GROUP_ADMIN 或 RESOURCE_GROUP_USER 权限。The RESOURCE_GROUP optimizer hint is used for resource group management (see Section 7.1.16, “Resource Groups”). This hint assigns the thread that executes a statement to the named resource group temporarily (for the duration of the statement). It requires the RESOURCE_GROUP_ADMIN or RESOURCE_GROUP_USER privilege.

示例：Examples:

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

RESOURCE_GROUP 提示的语法：Syntax of the RESOURCE_GROUP hint:

RESOURCE_GROUP(group_name)

group_name 指示线程在语句执行期间应分配到的资源组。如果该组不存在，将会出现警告并且提示会被忽略。group_name indicates the resource group to which the thread should be assigned for the duration of statement execution. If the group is nonexistent, a warning occurs and the hint is ignored.

RESOURCE_GROUP 提示必须出现在初始语句关键字（SELECT、INSERT、REPLACE、UPDATE 或 DELETE）之后。The RESOURCE_GROUP hint must appear after the initial statement keyword (SELECT, INSERT, REPLACE, UPDATE, or DELETE).

一个替代 RESOURCE_GROUP 的方法是 SET RESOURCE GROUP 语句，它会将线程非临时地分配到资源组。见 15.7.2.4 节，“SET RESOURCE GROUP 语句”。An alternative to RESOURCE_GROUP is the SET RESOURCE GROUP statement, which nontemporarily assigns threads to a resource group. See Section 15.7.2.4, “SET RESOURCE GROUP Statement”.

表级、索引级和子查询优化器提示允许将特定查询块命名为其参数语法的一部分。要创建这些名称，请使用 QB_NAME 提示，它会为出现的查询块分配一个名称：Table-level, index-level, and subquery optimizer hints permit specific query blocks to be named as part of their argument syntax. To create these names, use the QB_NAME hint, which assigns a name to the query block in which it occurs:

QB_NAME(name)

QB_NAME 提示可用于以清晰的方式明确其他提示适用于哪些查询块。它们还允许将所有非查询块名称提示在单个提示注释中指定，以便更容易理解复杂语句。请考虑以下语句：QB_NAME hints can be used to make explicit in a clear way which query blocks other hints apply to. They also permit all non-query block name hints to be specified within a single hint comment for easier understanding of complex statements. Consider the following statement:

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

QB_NAME 提示用于在语句中为查询块分配名称：QB_NAME hints assign names to query blocks in the statement:

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

然后其他提示可以使用这些名称来引用相应的查询块：Then other hints can use those names to refer to the appropriate query blocks:

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 ...)) ...

其产生的效果如下：The resulting effect is as follows:

查询块名称是标识符，并遵循关于有效名称以及如何引用名称的通常规则（参见 第 11.2 节，“模式对象名称”）。例如，包含空格的查询块名称必须加引号，可以使用反引号进行引用：Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (see Section 11.2, “Schema Object Names”). For example, a query block name that contains spaces must be quoted, which can be done using backticks:

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

如果启用了 ANSI_QUOTES SQL 模式，也可以使用双引号引用查询块名称：If the ANSI_QUOTES SQL mode is enabled, it is also possible to quote query block names within double quotation marks:

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