8.9.3 优化器提示

控制优化程序策略的一种方法是设置optimizer_switch系统变量(请参见第 8.9.2 节“可切换的优化”)。对该变量的更改会影响所有后续查询的执行；为了使一个查询与另一个查询有不同的影响，必须在每个查询之前更改optimizer_switch。

控制优化器的另一种方法是使用优化器提示，该提示可以在各个语句中指定。由于优化器提示是基于每个语句应用的，因此它们比optimizer_switch可以更好地控制语句执行计划。例如，您可以在语句中为一个 table 启用优化，而对另一 table 禁用优化。语句中的提示优先于optimizer_switch标志。

Examples:

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

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

• 优化程序提示概述

• 优化程序提示语法

• table 级优化器提示

• 索引级优化器提示

• 子查询优化器提示

• 语句执行时间优化器提示

• 用于命名查询块的优化器提示

优化程序提示概述

优化器提示适用于不同的作用域级别：

• 全局：提示会影响整个语句

• 查询块：提示会影响语句中的特定查询块

• table 级别：提示会影响查询块中的特定 table

• 索引级：提示会影响 table 中的特定索引

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

table8.2 提供了优化程序提示

Hint Name	Description	Applicable Scopes
BKA, NO_BKA	影响批量密钥访问联接处理	查询块，table
BNL, NO_BNL	影响块嵌套循环连接处理	查询块，table
MAX_EXECUTION_TIME	限制语句执行时间	Global
MRR, NO_MRR	影响多范围读取优化	Table, index
NO_ICP	影响索引条件下推式优化	Table, index
NO_RANGE_OPTIMIZATION	影响范围优化	Table, index
QB_NAME	为查询块分配名称	Query block
SEMIJOIN, NO_SEMIJOIN	影响半联接策略	Query block
SUBQUERY	影响实现，IN至EXISTS子查询策略	Query block

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

优化程序提示语法

MySQL 支持第 9.6 节“Comments 语法”中所述的 SQL 语句中的 Comments。必须在/*+ ... */条 Comments 中指定优化程序提示。也就是说，优化程序提示使用/* ... */ C 样式 Comments 语法的变体，在/*Comments 打开序列之后带有+字符。例子：

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

+字符后允许使用空格。

解析器在SELECT，UPDATE，INSERT，REPLACE和DELETE语句的初始关键字之后识别优化器提示 Comments。在以下情况下允许提示：

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

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来查看优化器提示如何影响执行计划。在EXPLAIN之后立即使用SHOW WARNINGS来查看如何使用提示。以下SHOW WARNINGS显示的扩展EXPLAIN输出指示使用了哪些提示。不显示忽略的提示。

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

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

但这是无效的：

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

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

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

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

查询块名称是标识符，并遵循有关有效名称以及如何对其进行引用的常规规则(请参见第 9.2 节“架构对象名称”)。

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

table 级优化器提示

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

table 级提示的语法：

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：为指定的 table 启用或禁用 BKA。

  • BNL，NO_BNL：为指定的 table 启用或禁用 BNL。

• • tbl_name *：语句中使用的 table 的名称。提示适用于它命名的所有 table。如果提示未命名任何 table，则它将应用于出现该查询的查询块的所有 table。

如果 table 具有别名，则提示必须引用别名，而不是 table 名称。

提示中的 table 名不能用架构名称限定。

• • query_block_name *：提示适用于的查询块。如果该提示不包含前导@query_block_name，则该提示将应用于出现该提示的查询块。对于tbl_name@query_block_name语法，该提示适用于命名查询块中的命名 table。要将名称分配给查询块，请参阅用于命名查询块的优化器提示。

Examples:

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;

table 级提示适用于从先前的 table 而非发送方 table 接收记录的 table。考虑以下语句：

SELECT /*+ BNL(t2) */ FROM t1, t2;

如果优化器选择先处理t1，则它会先缓冲t1的行，然后再开始从t2进行读取，从而对t2应用“块嵌套循环”联接。如果优化程序选择先处理t2，则该提示无效，因为t2是发送方 table。

索引级优化程序提示

索引级别的提示会影响优化器针对特定 table 或索引使用的索引处理策略。这些提示类型会影响索引条件下推(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 *：允许以下提示名称：

• MRR，NO_MRR：为指定的 table 或索引启用或禁用 MRR。 MRR 提示仅适用于InnoDB和MyISAMtable。

  • NO_ICP：为指定的 table 或索引禁用 ICP。默认情况下，ICP 是一种候选优化策略，因此没有启用它的提示。

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

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

• • tbl_name *：提示适用的 table。
• • index_name *：命名 table 中索引的名称。提示适用于它命名的所有索引。如果提示未命名索引，则它将应用于 table 中的所有索引。

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

• • query_block_name *：提示适用于的查询块。如果该提示不包含前导@query_block_name，则该提示将应用于出现该提示的查询块。对于tbl_name@query_block_name语法，该提示适用于命名查询块中的命名 table。要将名称分配给查询块，请参阅用于命名查询块的优化器提示。

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

子查询优化程序提示

子查询提示会影响是否使用半联接转换以及允许使用的半联接策略，以及在不使用半联接时，是否使用子查询实现或IN到EXISTS转换。有关这些优化的更多信息，请参见第 8.2.2 节“优化子查询，派生 table 和视图引用”。

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

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来避免。

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

影响是否使用子查询实现或从IN到EXISTS转换的提示的语法：

SUBQUERY([@query_block_name] strategy)

提示名称始终为SUBQUERY。

对于SUBQUERY个提示，允许以下* strategy *值：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);

对于半联接和SUBQUERY提示，前导@query_block_name指定提示所应用的查询块。如果该提示不包含前导@query_block_name，则该提示将应用于出现该提示的查询块。要将名称分配给查询块，请参见用于命名查询块的优化器提示。

如果提示 Comments 包含多个子查询提示，则使用第一个。如果还存在其他该类型的提示，则会产生警告。其他类型的以下提示将被忽略。

语句执行时间优化器提示

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提示适用于以下情况：

• 对于具有多个SELECT关键字的语句(例如，并集或具有子查询的语句)，MAX_EXECUTION_TIME适用于整个语句，并且必须出现在第一个SELECT之后。

• 它适用于只读的SELECT语句。非只读的语句是那些调用存储函数修改数据的副作用的语句。

• 它不适用于存储程序中的SELECT语句，将被忽略。

命名查询块的优化器提示

table 级，索引级和子查询优化器提示允许将特定查询块命名为其参数语法的一部分。要创建这些名称，请使用QB_NAME提示，该提示将名称分配给出现该名称的查询块：

QB_NAME(name)

QB_NAME提示可用于以明确的方式明确显示哪些查询阻止了其他提示。它们还允许在单个提示 Comments 中指定所有非查询块名称提示，以便于理解复杂的语句。考虑以下语句：

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)适用于查询块qb1中的 tablet1。

• BKA(@qb2)适用于查询块qb2。

• NO_MRR(@ qb3 t1 idx1，id2)适用于查询块qb3中 tablet1中的索引idx1和idx2。

查询块名称是标识符，并遵循有关有效名称以及如何对其进行引用的常规规则(请参见第 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") */ ...) ...