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

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

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

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 之前缓冲来自 t1 的行，对 t2 应用块嵌套循环联接。如果优化器改为选择先处理 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") */ ...) ...