优化器提示适用于不同的范围级别：

下表总结了可用的优化器提示、它们影响的优化器策略以及它们应用的一个或多个范围。稍后给出更多细节。

禁用优化会阻止优化器使用它。启用优化意味着优化器可以自由使用该策略（如果它适用于语句执行），而不是优化器必须使用它。

MySQL 支持 SQL 语句中的注释，如 第 9.6 节，“注释”中所述。必须在/*+ ... */注释中指定优化器提示。也就是说，优化器提示使用/* ... */ 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) */ ...

当提示注释包含多个提示时，存在重复和冲突的可能性。以下一般准则适用。对于特定的提示类型，附加规则可能适用，如提示描述中所示。

查询块名称是标识符，并遵循关于什么名称有效以及如何引用它们的通常规则（请参阅 第 9.2 节，“模式对象名称”）。

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

表级提示会影响块嵌套循环 (BNL) 和批处理密钥访问 (BKA) 连接处理算法的使用（请参阅 第 8.2.1.11 节，“块嵌套循环和批处理密钥访问连接”）。这些提示类型适用于特定表，或查询块中的所有表。

表级提示的语法：

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 /*+ BNL(t2) */ FROM t1, t2;

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

索引级提示会影响优化器对特定表或索引使用的索引处理策略。这些提示类型会影响索引条件下推 (ICP)、多范围读取 (MRR) 和范围优化的使用（请参阅 第 8.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] ...])

语法指的是这些术语：

Examples:

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

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 8.2.2, “Optimizing Subqueries, Derived Tables, and View References”.

Syntax of hints that affect semijoin strategies:

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

The syntax refers to these terms:

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.

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

Syntax of hints that affect whether to use subquery materialization or IN-to-EXISTS transformations:

SUBQUERY([@query_block_name] strategy)

The hint name is always SUBQUERY.

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

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.

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)

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.

The MAX_EXECUTION_TIME hint is applicable as follows:

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 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 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:

查询块名称是标识符，并遵循关于什么名称有效以及如何引用它们的通常规则（请参阅 第 9.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") */ ...) ...