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

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

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

MySQL 支持 SQL 语句中的注释，如 第 9.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) */ ...

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

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

提示名称、查询块名称和策略名称不区分大小写。对表和索引名称的引用遵循通常的标识符区分大小写规则（请参阅 第 9.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 应用块嵌套循环连接 。如果优化器选择首先处理，则提示无效，因为它是一个发送方表。 t2t1t2t2t2

对于MERGE和 NO_MERGE提示，这些优先规则适用：

索引级提示会影响优化器对特定表或索引使用的索引处理策略。这些提示类型会影响索引条件下推 (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] ...])

语法指的是这些术语：

例子：

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;

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 转换。有关这些优化的更多信息，请参阅第 8.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@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 ...

该 提示将语句执行超时设置为 毫秒。如果此选项不存在或为 0，则应用系统变量 建立的语句超时 。MAX_EXECUTION_TIME(N)NNmax_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. 单独的系统变量描述表明每个变量是否可提示；参见第 5.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 采用onor 的值off，因此尝试将其设置为 yes是不正确的。在每种情况下，都会忽略提示并发出警告。

SET_VAR仅在语句级别允许提示 。如果在子查询中使用，提示将被忽略并发出警告。

副本忽略SET_VAR 复制语句中的提示以避免潜在的安全问题。

优化器RESOURCE_GROUP提示用于资源组管理（请参阅 第 5.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临时地将线程分配给资源组的语句。请参阅 第 13.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 ...)) ...

产生的效果如下：

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