优化器提示在不同的范围级别应用

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

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

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

提示名称、查询块名称和策略名称不区分大小写。对表和索引名称的引用遵循常规的标识符大小写敏感性规则（请参阅 第 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 应用块嵌套循环联接，在开始从 t2 读取之前缓冲来自 t1 的行。如果优化器改为选择先处理 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;

以下示例使用索引合并提示，但其他索引级提示遵循关于提示忽略和优化器提示相对于 optimizer_switch 系统变量或索引提示的优先级的相同原则。

假设表 t1 包含列 a、b、c 和 d；并且分别在 a、b 和 c 上存在名为 i_a、i_b 和 i_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 优化器提示，这些优先级规则适用。

子查询提示会影响是否使用半连接转换以及允许哪些半连接策略，以及在不使用半连接时，是否使用子查询物化或 IN 到 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 到 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 ...

提示 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") */ ...) ...