[ACDU 翻译] MySQL 8.9.4 索引提示

控制优化器策略的一种方法是设置 optimizer_switch系统变量（请参阅第 8.9.2 节，“可切换优化”）。对此变量的更改会影响所有后续查询的执行；为了以不同的方式影响一个查询，有必要optimizer_switch在每个查询之前进行更改 。

控制优化器的另一种方法是使用优化器提示，可以在单个语句中指定。由于优化器提示适用于每个语句，因此它们对语句执行计划的控制比使用 optimizer_switch. 例如，您可以对语句中的一个表启用优化并禁用对不同表的优化。语句中的提示优先于 optimizer_switch标志。

例子：

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

此处描述的优化器提示与第 8.9.4 节“索引提示”中描述的索引提示不同。优化器和索引提示可以单独使用或一起使用。

• 优化器提示概述
• 优化器提示语法
• 联序优化器提示
• 表级优化器提示
• 索引级优化器提示
• 子查询优化器提示
• 语句执行时间优化器提示
• 变量设置提示语法
• 资源组提示语法
• 命名查询块的优化器提示

优化器提示概述

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

• 全局：提示影响整个语句
• 查询块：提示影响语句中的特定查询块
• 表级：提示影响查询块中的特定表
• 索引级别：提示影响表中的特定索引

下表总结了可用的优化器提示、它们影响的优化器策略以及它们适用的范围。更多细节将在后面给出。

表 8.2 可用的优化器提示

提示名称	描述	适用范围
BKA, NO_BKA	影响批处理密钥访问加入处理	查询块、表
BNL, NO_BNL	MySQL 8.0.20之前：影响Block Nested-Loop join处理；MySQL 8.0.18 及更高版本：也会影响散列连接优化；MySQL 8.0.20 及更高版本：仅影响散列连接优化	查询块、表
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN	对物化派生表使用或忽略派生条件下推优化（MySQL 8.0.22 新增）	查询块、表
GROUP_INDEX, NO_GROUP_INDEX	在GROUP BY操作中使用或忽略指定的索引或索引进行索引扫描 （MySQL 8.0.20 中添加）	指数
HASH_JOIN, NO_HASH_JOIN	影响 Hash Join 优化（仅限 MySQL 8.0.18	查询块、表
INDEX, NO_INDEX	充当JOIN_INDEX, GROUP_INDEX, and ORDER_INDEX的组合，或充当NO_JOIN_INDEX, NO_GROUP_INDEX, and 的组合 NO_ORDER_INDEX（MySQL 8.0.20 新增）	指数
INDEX_MERGE, NO_INDEX_MERGE	影响索引合并优化	表、索引
JOIN_FIXED_ORDER	将FROM子句中指定的表顺序用于连接顺序	查询块
JOIN_INDEX, NO_JOIN_INDEX	对任何访问方法使用或忽略指定的一个或多个索引（在 MySQL 8.0.20 中添加）	指数
JOIN_ORDER	使用提示中指定的表顺序进行连接顺序	查询块
JOIN_PREFIX	对连接顺序的第一个表使用提示中指定的表顺序	查询块
JOIN_SUFFIX	将提示中指定的表顺序用于连接顺序的最后一个表	查询块
MAX_EXECUTION_TIME	限制语句执行时间	全球的
MERGE, NO_MERGE	影响合并到外部查询块的派生表/视图	桌子
MRR, NO_MRR	影响多范围读取优化	表、索引
NO_ICP	影响索引条件下推优化	表、索引
NO_RANGE_OPTIMIZATION	影响范围优化	表、索引
ORDER_INDEX, NO_ORDER_INDEX	使用或忽略指定的索引或索引对行进行排序（MySQL 8.0.20 中添加）	指数
QB_NAME	为查询块分配名称	查询块
RESOURCE_GROUP	在语句执行期间设置资源组	全球的
SEMIJOIN, NO_SEMIJOIN	影响半连接策略；从 MySQL 8.0.17 开始，这也适用于反连接	查询块
SKIP_SCAN, NO_SKIP_SCAN	影响跳过扫描优化	表、索引
SET_VAR	在语句执行期间设置变量	全球的
SUBQUERY	影响物化， IN到EXISTS 子查询的策略	查询块

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

优化器提示语法

MySQL 支持 SQL 语句中的注释，如 第 9.7 节“注释”中所述。优化器提示必须在/*+ ... */注释中指定。也就是说，优化器提示使用/* ... */ C 风格注释语法的变体，+在/*注释开始序列后面有一个字符。例子：

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

+ 字符 后允许有空格。

解析器承认优化的初始关键字后暗示的意见SELECT， UPDATE， INSERT， REPLACE，和 DELETE语句。在这些上下文中允许使用提示：

• 在查询和数据更改语句的开头：

  SELECT /*+ ... */ ...
  INSERT /*+ ... */ ...
  REPLACE /*+ ... */ ...
  UPDATE /*+ ... */ ...
  DELETE /*+ ... */ ...
  复制

• 在查询块的开头：

  (SELECT /*+ ... */ ... )
  (SELECT ... ) UNION (SELECT /*+ ... */ ... )
  (SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
  UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
  INSERT ... SELECT /*+ ... */ ...
  复制

• 在以 EXPLAIN. 例如：

  EXPLAIN SELECT /*+ ... */ ...
  EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
  复制

  这意味着您可以使用它 EXPLAIN来查看优化器提示如何影响执行计划。使用 SHOW WARNINGS后立即EXPLAIN查看如何使用提示。EXPLAIN 以下SHOW WARNINGS显示的扩展输出指示使用了哪些提示。不显示被忽略的提示。

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

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

但这是无效的：

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

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

• 重复提示：对于诸如 的提示/*+ MRR(idx1) MRR(idx1) */，MySQL 使用第一个提示并发出有关重复提示的警告。
• 冲突提示：对于诸如 的提示/*+ MRR(idx1) NO_MRR(idx1) */，MySQL 使用第一个提示并发出有关第二个冲突提示的警告。

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

语法是指这些术语：

• hint_name：允许使用这些提示名称：

  • JOIN_FIXED_ORDER: 强制优化器使用它们在FROM 子句中出现的顺序连接表。这与指定SELECT STRAIGHT_JOIN.
  • JOIN_ORDER: 指示优化器使用指定的表顺序连接表。该提示适用于命名表。优化器可以将未命名的表放置在连接顺序中的任何位置，包括在指定的表之间。
  • JOIN_PREFIX: 指示优化器对连接执行计划的第一个表使用指定的表顺序连接表。该提示适用于命名表。优化器将所有其他表放在命名表之后。
  • JOIN_SUFFIX: 指示优化器对连接执行计划的最后一个表使用指定的表顺序连接表。该提示适用于命名表。优化器将所有其他表放在命名表之前。

• tbl_name: 语句中使用的表名。命名表的提示适用于它命名的所有表。该 JOIN_FIXED_ORDER提示名称没有表，并适用于所有的表 FROM在其发生的查询块的条款。

  如果表有别名，提示必须引用别名，而不是表名。

  提示中的表名不能用模式名限定。

• query_block_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表的行为。

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

• 多个提示实例

  每种类型只应用 一个JOIN_PREFIX和 JOIN_SUFFIX提示。任何相同类型的后续提示都将被忽略并显示警告。 JOIN_ORDER可以指定多次。

  例子：

  /*+ JOIN_PREFIX(t1) JOIN_PREFIX(t2) */
  复制

  第二个JOIN_PREFIX 提示被忽略并显示警告。

  /*+ JOIN_PREFIX(t1) JOIN_SUFFIX(t2) */
  复制

  这两个提示都适用。没有警告发生。

  /*+ JOIN_ORDER(t1, t2) JOIN_ORDER(t2, t3) */
  复制

  这两个提示都适用。没有警告发生。

• 相互矛盾的提示

  在某些情况下，提示可能会发生冲突，例如 when JOIN_ORDER和 JOIN_PREFIXtable 顺序无法同时应用：

  SELECT /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */ ... FROM t1, t2;
  复制

  在这种情况下，将应用第一个指定的提示，并忽略随后的冲突提示而不会发出警告。无法应用的有效提示将被默默忽略，不会发出警告。

• 忽略提示

  如果提示中指定的表具有循环依赖关系，则忽略提示。

  例子：

  /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */
  复制

  该JOIN_ORDER提示套表t2依赖 t1。该 JOIN_PREFIX提示将被忽略，因为表t1不能依赖t2。忽略的提示不会显示在扩展 EXPLAIN输出中。

• 与const 表的 交互

  MySQL 优化器const在连接顺序中将表放在最前面，表的位置 const不受提示的影响。const连接顺序提示中对表的引用将被忽略，尽管该提示仍然适用。例如，这些是等效的：

  JOIN_ORDER(t1, const_tbl, t2)JOIN_ORDER(t1, t2)
  复制

  扩展EXPLAIN输出中显示的接受提示 包括 const指定的表。

• 与连接操作类型的交互

  MySQL 支持多种类型的连接： LEFT、RIGHT、 INNER、CROSS、 STRAIGHT_JOIN。与指定的连接类型冲突的提示将被忽略，不会发出警告。

  例子：

  SELECT /*+ JOIN_PREFIX(t1, t2) */FROM t2 LEFT JOIN t1;
  复制

  这里在提示中请求的连接顺序与LEFT JOIN. 提示被忽略，没有警告。

表级优化器提示

表级提示影响：

• 使用块嵌套循环 (BNL) 和批量密钥访问 (BKA) 连接处理算法（请参阅 第 8.2.1.12 节，“块嵌套循环和批量密钥访问连接”）。
• 派生表、视图引用或公用表表达式应该合并到外部查询块中，还是使用内部临时表具体化。
• 使用派生表条件下推优化（在 MySQL 8.0.22 中添加）。请参见 第 8.2.2.5 节，“派生条件下推优化”。

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

表级提示的语法：

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

语法是指这些术语：

• hint_name：允许使用这些提示名称：

  • BKA, NO_BKA: 启用或禁用指定表的密钥访问。

  • BNL, NO_BNL: 启用或禁用指定表的块嵌套循环。在 MySQL 8.0.18 及更高版本中，这些提示还启用和禁用散列连接优化。

    笔记

    在 MySQL 8.0.20 和更高版本中删除了块嵌套循环优化，但这些提示继续支持启用和禁用哈希连接。

  • DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN: 启用或禁用对指定表使用派生表条件下推（在 MySQL 8.0.22 中添加）。有关更多信息，请参阅 第 8.2.2.5 节，“派生条件下推优化”。

  • HASH_JOIN, NO_HASH_JOIN: 启用或禁用对指定表使用散列连接（仅限 MySQL 8.0.18；在 MySQL 8.0.19 或更高版本中无效）。

  • MERGE, NO_MERGE: 为指定的表、视图引用或公用表表达式启用合并；或禁用合并并改用物化。

  笔记

  要使用块嵌套循环或批处理键访问提示为外连接的任何内表启用连接缓冲，必须为外连接的所有内表启用连接缓冲。

• tbl_name: 语句中使用的表名。该提示适用于它命名的所有表。如果提示未命名表，则它适用于出现它的查询块的所有表。

  如果表有别名，提示必须引用别名，而不是表名。

  提示中的表名不能用模式名限定。

• query_block_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提示，这些优先规则适用：

• 提示优先于任何非技术约束的优化器启发式。（如果提供提示作为建议没有效果，优化器有理由忽略它。）
• 提示优先于 系统变量的 derived_merge标志optimizer_switch。
• 对于视图引用，ALGORITHM={MERGE|TEMPTABLE}视图定义中的 子句优先于引用视图的查询中指定的提示。

索引级优化器提示

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

语法是指这些术语：

• hint_name：允许使用这些提示名称：

  • GROUP_INDEX, NO_GROUP_INDEX: 启用或禁用指定的一个或多个索引以进行索引扫描以进行GROUP BY 操作。相当于索引提示 FORCE INDEX FOR GROUP BY， IGNORE INDEX FOR GROUP BY。在 MySQL 8.0.20 及更高版本中可用。

  • INDEX, NO_INDEX: 充当JOIN_INDEX, GROUP_INDEX, and 的组合 ORDER_INDEX，强制服务器对任何和所有范围使用指定的一个或多个索引，或者作为NO_JOIN_INDEX, NO_GROUP_INDEX, and 的组合NO_ORDER_INDEX，这会导致服务器忽略任何和所有范围 的指定一个或多个索引所有范围。相当于 FORCE INDEX，IGNORE INDEX。从 MySQL 8.0.20 开始可用。

  • INDEX_MERGE, NO_INDEX_MERGE: 启用或禁用指定表或索引的索引合并访问方法。有关此访问方法的信息，请参阅 第 8.2.1.3 节，“索引合并优化”。这些提示适用于所有三种索引合并算法。

    该INDEX_MERGE提示强制优化器使用指定的索引集对指定的表使用索引合并。如果没有指定索引，优化器会考虑所有可能的索引组合并选择成本最低的组合。如果索引组合不适用于给定语句，则可以忽略提示。

    该NO_INDEX_MERGE 提示禁用涉及任何指定索引的索引合并组合。如果提示未指定索引，则表不允许进行索引合并。

  • JOIN_INDEX, NO_JOIN_INDEX: 强制 MySQL 对任何访问方法使用或忽略指定的一个或多个索引，例如 ref、range、 index_merge等。相当于FORCE INDEX FOR JOIN，IGNORE INDEX FOR JOIN。在 MySQL 8.0.20 及更高版本中可用。

  • MRR, NO_MRR: 为指定的表或索引启用或禁用 MRR。MRR 提示仅适用于InnoDB和 MyISAM表。有关此访问方法的信息，请参阅 第 8.2.1.11 节，“多范围读取优化”。

  • NO_ICP: 禁用指定表或索引的ICP。默认情况下，ICP是一个候选优化策略，所以没有提示启用它。有关此访问方法的信息，请参阅 第 8.2.1.6 节，“索引条件下推优化”。

  • NO_RANGE_OPTIMIZATION: 禁用指定表或索引的索引范围访问。此提示还会禁用表或索引的索引合并和松散索引扫描。默认情况下，范围访问是一种候选优化策略，因此没有启用它的提示。

    当范围的数量可能很高并且范围优化需要很多资源时，此提示可能很有用。

  • ORDER_INDEX, NO_ORDER_INDEX: 使 MySQL 使用或忽略指定的索引或索引对行进行排序。相当于FORCE INDEX FOR ORDER BY，IGNORE INDEX FOR ORDER BY。从 MySQL 8.0.20 开始可用。

  • SKIP_SCAN, NO_SKIP_SCAN: 启用或禁用指定表或索引的跳过扫描访问方法。有关此访问方法的信息，请参阅 跳过扫描范围访问方法。这些提示从 MySQL 8.0.13 开始可用。

    该SKIP_SCAN提示强制优化器使用指定的索引集对指定的表使用跳过扫描。如果没有指定索引，优化器会考虑所有可能的索引并选择成本最低的一个。如果索引不适用于给定语句，则可以忽略提示。

    该NO_SKIP_SCAN 提示禁用指定索引的跳过扫描。如果提示未指定索引，则表不允许跳过扫描。

• tbl_name：提示适用的表。

• index_name: 命名表中索引的名称。该提示适用于它命名的所有索引。如果提示未命名索引，则它适用于表中的所有索引。

  要引用主键，请使用 name PRIMARY。要查看表的索引名称，请使用SHOW INDEX.

• query_block_name：提示适用的查询块。如果提示不包含前导 ，则提示适用于出现它的查询块。对于 语法，提示适用于命名查询块中的命名表。要为查询块分配名称，请参阅 命名查询块的优化器提示。 @*query_block_name*``*tbl_name*@*query_block_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优化器提示，这些优先规则适用：

• 如果指定了优化器提示并且适用，则它优先于optimizer_switch系统变量的索引合并相关标志 。

  SET optimizer_switch='index_merge_intersection=off';
  SELECT /*+ INDEX_MERGE(t1 i_b, i_c) */ * FROM t1
  WHERE b = 1 AND c = 2 AND d = 3;
  复制

  提示优先于 optimizer_switch。(i_b, i_c)在这种情况下使用索引合并。

  SET optimizer_switch='index_merge_intersection=on';
  SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
  WHERE b = 1 AND c = 2 AND d = 3;
  复制

  提示只指定了一个索引，所以不适用，optimizer_switch 标志（on）适用。如果优化器评估它具有成本效益，则使用索引合并。

  SET optimizer_switch='index_merge_intersection=off';
  SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
  WHERE b = 1 AND c = 2 AND d = 3;
  复制

  提示只指定了一个索引，所以不适用，optimizer_switch 标志（off）适用。不使用索引合并。

• 索引级优化器提示 GROUP_INDEX、INDEX、 JOIN_INDEX和 ORDER_INDEX所有优先于等效FORCE INDEX提示；也就是说，它们会导致FORCE INDEX提示被忽略。同样， NO_GROUP_INDEX， NO_INDEX， NO_JOIN_INDEX，和 NO_ORDER_INDEX提示都优先于任何IGNORE INDEX 等价物，也使他们被忽略。

  索引级优化器提示 GROUP_INDEX、 NO_GROUP_INDEX、 INDEX、NO_INDEX、 JOIN_INDEX、NO_JOIN_INDEX、 ORDER_INDEX和 NO_ORDER_INDEX提示都优先于所有其他优化器提示，包括其他索引级优化器提示。任何其他优化器提示仅应用于这些允许的索引。

  在GROUP_INDEX， INDEX，JOIN_INDEX，和ORDER_INDEX提示都相当于FORCE INDEX而不是 USE INDEX。这是因为使用这些提示中的一个或多个意味着仅在无法使用指定索引之一来查找表中的行时才使用表扫描。为了使MySQL使用索引的相同指标或设定为具有给定的情况下USE INDEX，你可以使用NO_INDEX， NO_JOIN_INDEX， NO_GROUP_INDEX， NO_ORDER_INDEX，或它们的一些组合。

  要复制USE INDEX 查询中的效果SELECT a,c FROM t1 USE INDEX FOR ORDER BY (i_a) ORDER BY a，您可以使用 NO_ORDER_INDEX优化器提示覆盖表上的所有索引，除了需要的索引，如下所示：

  SELECT /*+ NO_ORDER_INDEX(t1 i_b,i_c) */ a,c
      FROM t1
      ORDER BY a;
  复制

  尝试将NO_ORDER_INDEX 表作为一个整体与 with 组合USE INDEX FOR ORDER BY不起作用，因为 NO_ORDER_BY导致USE INDEX被忽略，如下所示：

  mysql> EXPLAIN SELECT /*+ NO_ORDER_INDEX(t1) */ a,c FROM t1
      ->     USE INDEX FOR ORDER BY (i_a) ORDER BY a\G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: t1
     partitions: NULL
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 256
       filtered: 100.00
          Extra: Using filesort
  复制

• 的USE INDEX，FORCE INDEX和IGNORE INDEX索引提示具有比更高的优先级 INDEX_MERGE和 NO_INDEX_MERGE优化提示。

  /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ ... IGNORE INDEX i_a
  复制

  IGNORE INDEX优先于 INDEX_MERGE，因此索引 i_a被排除在索引合并的可能范围之外。

  /*+ NO_INDEX_MERGE(t1 i_a, i_b) */ ... FORCE INDEX i_a, i_b
  复制

  i_a, i_b 由于 不允许索引合并FORCE INDEX，但优化器被迫使用i_a 或i_bfor range或 ref访问。没有冲突；这两个提示都适用。

• 如果IGNORE INDEX提示命名多个索引，则这些索引不可用于索引合并。

• 在FORCE INDEX与USE INDEX只有指定索引可用于索引合并提示做。

  SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ a FROM t1
  FORCE INDEX (i_a, i_b) WHERE c = 'h' AND a = 2 AND b = 'b';
  复制

  索引合并交叉访问算法用于 (i_a, i_b). FORCE INDEX改为 时也是如此 USE INDEX。

子查询优化器提示

子查询提示影响是否要转换的半连接和半连接策略使用许可证，并且，在不使用的半连接，是否使用子查询物化或 IN至-EXISTS 变换。有关这些优化的更多信息，请参阅第 8.2.2 节，“优化子查询、派生表、视图引用和公用表表达式”。

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

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

语法是指这些术语：

• hint_name：允许使用这些提示名称：

  • SEMIJOIN, NO_SEMIJOIN: 启用或禁用指定的半连接策略。

• strategy：要启用或禁用的半连接策略。允许使用以下策略名称：DUPSWEEDOUT、 FIRSTMATCH、 LOOSESCAN、 MATERIALIZATION。

  对于SEMIJOIN提示，如果没有命名策略，则根据optimizer_switch系统变量启用的策略，尽可能使用半连接 。如果策略已命名但不适用于语句，DUPSWEEDOUT则使用。

  对于NO_SEMIJOIN提示，如果未命名策略，则不使用半连接。如果策略被命名为排除语句的所有适用策略， DUPSWEEDOUT则使用。

如果一个子查询嵌套在另一个子查询中，并且两者都合并到外部查询的半连接中，则最内层查询的任何半连接策略规范都将被忽略。 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-到-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*

如果提示注释包含多个子查询提示，则使用第一个。如果还有其他类似的提示，它们会产生警告。其他类型的以下提示将被默默忽略。

语句执行时间优化器提示

该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*)N**Nmax_execution_time

该MAX_EXECUTION_TIME提示适用如下：

• 对于带有多个SELECT 关键字的语句，例如联合或带有子查询的语句， MAX_EXECUTION_TIME 适用于整个语句并且必须出现在第一个SELECT.
• 它适用于只读 SELECT语句。非只读语句是那些调用存储函数的语句，该函数修改数据作为副作用。
• 它不适用于SELECT 存储程序中的语句并被忽略。

变量设置提示语法

所述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 = 'utf8') */ 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 ...)) ...
复制

产生的效果如下：

• MRR(@qb1 t1)适用t1于查询块中的 表qb1。
• BKA(@qb2)适用于查询块qb2。
• NO_MRR(@qb3 t1 idx1, id2)适用 于查询块中的索引idx1 和idx2表t1中qb3。

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