MySQL 5.7-8.9.3 Optimizer Hints(优化器提示)_index-level optimizer hints

One means of control over optimizer strategies is to set the optimizer_switch system variable (see Section 8.9.2, “Switchable Optimizations”). Changes to this variable affect execution of all subsequent queries; to affect one query differently from another, it is necessary to change optimizer_switch before each one.

控制优化器策略的一种方法是设置optimizer_switch系统变量(参见8.9.2节“可切换优化”)。对这个变量的更改会影响所有后续查询的执行;为了使一个查询不同于另一个查询，需要在每个查询之前更改optimizer_switch。

another way to control the optimizer is by using optimizer hints, which can be specified within individual statements. Because optimizer hints apply on a per-statement basis, they provide finer control over statement execution plans than can be achieved using optimizer_switch.

控制优化器的另一种方法是使用优化器提示，它可以在单独的语句中指定。因为优化器提示适用于每条语句，所以它们提供了比使用optimizer_switch更精细的语句执行计划控制。

For example, you can enable an optimization for one table in a statement and disable the optimization for a different table. Hints within a statement take precedence over optimizer_switch flags.

例如，您可以在一条语句中启用对一个表的优化，并禁用对另一个表的优化。语句中的提示优先于optimizer_switch标志。

Note

The mysql client by default strips comments from SQL statements sent to the server (including optimizer hints) until MySQL 5.7.7, when it was changed to pass optimizer hints to the server. To ensure that optimizer hints are not stripped if you are using an older version of the mysql client with a version of the server that understands optimizer hints, invoke mysql with the --comments option.

mysql客户端默认从发送到服务器的SQL语句中删除注释(包括优化器提示)，直到mysql 5.7.7，当它被更改为将优化器提示传递给服务器。为了确保优化器提示不会被删除，如果你使用的是旧版本的mysql客户端和理解优化器提示的服务器版本，使用——comments选项调用mysql

Optimizer hints, described here, differ from index hints, described in Section 8.9.4, “Index Hints”. Optimizer and index hints may be used separately or together.

这里描述的优化器提示不同于8.9.4节“索引提示”中描述的索引提示。优化器和索引提示可以单独使用，也可以一起使用。

• Optimizer Hint Overview

• Optimizer Hint Syntax

• Table-Level Optimizer Hints

• Index-Level Optimizer Hints

• Subquery Optimizer Hints

• Statement Execution Time Optimizer Hints

• Optimizer Hints for Naming Query Blocks

Optimizer Hint Overview

优化器提示概述

Optimizer hints apply at different scope levels:

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

• Global: The hint affects the entire statement

• 全局:提示影响整个语句

• Query block: The hint affects a particular query block within a statement

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

• Table-level: The hint affects a particular table within a query block

• 表级:提示影响查询块中的特定表

• Index-level: The hint affects a particular index within a table

• 索引级:提示影响表中的特定索引

The following table summarizes the available optimizer hints, the optimizer strategies they affect, and the scope or scopes at which they apply. More details are given later.

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

Table 8.2 Optimizer Hints Available

Disabling an optimization prevents the optimizer from using it. Enabling an optimization means the optimizer is free to use the strategy if it applies to statement execution, not that the optimizer necessarily uses it.

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

Optimizer Hint Syntax

MySQL supports comments in SQL statements as described in Section 9.6, “Comments”. Optimizer hints must be specified within /*+ ... */ comments. That is, optimizer hints use a variant of /* ... */ C-style comment syntax, with a + character following the /* comment opening sequence. Examples:

MySQL支持SQL语句中的注释，如9.6节“注释”所述。优化器提示必须在/*+…* /注释。也就是说，优化器提示使用/*…*/ c风格的注释语法，在/*注释开始序列后面有一个+字符。例子:

Whitespace is permitted after the + character.

在+字符后允许有空格。

At the beginning of query and data change statements:

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

At the beginning of query blocks: 

在查询块的开始:

 In hintable statements prefaced by EXPLAIN. For example:

在以EXPLAIN开头的暗示语句中。例如

The implication is that you can use EXPLAIN to see how optimizer hints affect execution plans. Use SHOW WARNINGS immediately after EXPLAIN to see how hints are used. The extended EXPLAIN output displayed by a following SHOW WARNINGS indicates which hints were used. Ignored hints are not displayed. 

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

A hint comment may contain multiple hints, but a query block cannot contain multiple hint comments. This is valid:

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

But this is invalid:

 When a hint comment contains multiple hints, the possibility of duplicates and conflicts exists. The following general guidelines apply. For specific hint types, additional rules may apply, as indicated in the hint descriptions.

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

• Duplicate hints: For a hint such as /*+ MRR(idx1) MRR(idx1) */, MySQL uses the first hint and issues a warning about the duplicate hint.

• 重复提示:对于像/*+ MRR(idx1) MRR(idx1) */这样的提示，MySQL使用第一个提示并发出关于重复提示的警告。

• Conflicting hints: For a hint such as /*+ MRR(idx1) NO_MRR(idx1) */, MySQL uses the first hint and issues a warning about the second conflicting hint.

• 冲突提示:对于像/*+ MRR(idx1) NO_MRR(idx1) */这样的提示，MySQL使用第一个提示并发出关于第二个冲突提示的警告。

Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (see Section 9.2, “Schema Object Names”).

查询块名称是标识符，并遵循关于哪些名称是有效的以及如何引用它们的通常规则

Hint names, query block names, and strategy names are not case-sensitive. References to table and index names follow the usual identifier case sensitivity rules (see Section 9.2.3, “Identifier Case Sensitivity”).

提示名称、查询块名称和策略名称不区分大小写。对表名和索引名的引用遵循通常的标识符区分大小写规则

Table-Level Optimizer Hints

表级优化器提示

Table-level hints affect use of the Block Nested-Loop (BNL) and Batched Key Access (BKA) join-processing algorithms (see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”). These hint types apply to specific tables, or all tables in a query block.

表级提示影响Block Nested-Loop (BNL)和batch Key Access (BKA)连接处理算法的使用。这些提示类型适用于特定的表，或查询块中的所有表。

The syntax refers to these terms: 

语法指的是这些术语:

hint_name: These hint names are permitted:

以下提示名称是允许的:

• BKA, NO_BKA: Enable or disable BKA for the specified tables.

• 启用或禁用指定表的BKA。

• BNL, NO_BNL: Enable or disable BNL for the specified tables.

• 为指定的表启用或禁用BNL。

tbl_name: The name of a table used in the statement. The hint applies to all tables that it names. If the hint names no tables, it applies to all tables of the query block in which it occurs.

tbl_name:语句中使用的表名。这个提示适用于它命名的所有表。如果提示没有指定表，它将应用于查询块中出现它的所有表。

If a table has an alias, hints must refer to the alias, not the table name.

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

Table names in hints cannot be qualified with schema names.

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

query_block_name: The query block to which the hint applies. If the hint includes no leading @query_block_name, the hint applies to the query block in which it occurs. For tbl_name@query_block_name syntax, the hint applies to the named table in the named query block. To assign a name to a query block, see Optimizer Hints for Naming Query Blocks.

query_block_name:提示所应用的查询块。如果提示不包含前导@query_block_name，则该提示应用于出现该提示的查询块。对于tbl_name@query_block_name语法，提示应用于命名查询块中的命名表。

A table-level hint applies to tables that receive records from previous tables, not sender tables. Consider this statement: 

表级提示应用于从前一个表接收记录的表，而不是发送方表。考虑一下这句话:

 If the optimizer chooses to process t1 first, it applies a Block Nested-Loop join to t2 by buffering the rows from t1 before starting to read from t2. If the optimizer instead chooses to process t2 first, the hint has no effect because t2 is a sender table.

如果优化器选择首先处理t1，它会对t2应用Block Nested-Loop连接，在开始从t2读取数据之前先缓冲t1中的行。如果优化器选择首先处理t2，则提示没有作用，因为t2是发送方表。

Index-Level Optimizer Hints

Index-level hints affect which index-processing strategies the optimizer uses for particular tables or indexes. These hint types affect use of Index Condition Pushdown (ICP), Multi-Range Read (MRR), and range optimizations (see Section 8.2.1, “Optimizing SELECT Statements”).

索引级提示会影响优化器对特定表或索引使用的索引处理策略。这些提示类型会影响索引条件下推(ICP)、多范围读取(MRR)和范围优化的使用

Syntax of index-level hints:.

索引级提示的语法:

The syntax refers to these terms: 

语法指的是这些术语:

hint_name: These hint names are permitted:

hint_name:这些提示名是允许的:

• MRR, NO_MRR: Enable or disable MRR for the specified table or indexes. MRR hints apply only to InnoDB and MyISAM tables.

• 启用或禁用指定表或索引的MRR。MRR提示仅适用于InnoDB和MyISAM表。

• NO_ICP: Disable ICP for the specified table or indexes. By default, ICP is a candidate optimization strategy, so there is no hint for enabling it.

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

• NO_RANGE_OPTIMIZATION: Disable index range access for the specified table or indexes. This hint also disables Index Merge and Loose Index Scan for the table or indexes. By default, range access is a candidate optimization strategy, so there is no hint for enabling it.禁用指定表或索引的索引范围访问。此提示还禁用表或索引的索引合并和松散索引扫描。默认情况下，范围访问是一种候选优化策略，因此没有启用它的提示。

• This hint may be useful when the number of ranges may be high and range optimization would require many resources.禁用指定表或索引的索引范围访问。此提示还禁用表或索引的索引合并和松散索引扫描。默认情况下，范围访问是一种候选优化策略，因此没有启用它的提示。

tbl_name: The table to which the hint applies.

tbl_name:提示所应用的表。

index_name: The name of an index in the named table. The hint applies to all indexes that it names. If the hint names no indexes, it applies to all indexes in the table.

index_name:指定表中索引的名称。该提示应用于它命名的所有索引。如果提示没有指定索引，它将应用于表中的所有索引。

To refer to a primary key, use the name PRIMARY. To see the index names for a table, use SHOW INDEX.

要引用主键，请使用primary名称。要查看表的索引名称，请使用SHOW index。

query_block_name: The query block to which the hint applies. If the hint includes no leading @query_block_name, the hint applies to the query block in which it occurs. For tbl_name@query_block_name syntax, the hint applies to the named table in the named query block. To assign a name to a query block, see Optimizer Hints for Naming Query Blocks.

query_block_name:提示所应用的查询块。如果提示不包含前导@query_block_name，则该提示应用于出现该提示的查询块。对于tbl_name@query_block_name语法，提示应用于命名查询块中的命名表

 

Subquery Optimizer Hints 

Subquery hints affect whether to use semijoin transformations and which semijoin strategies to permit, and, when semijoins are not used, whether to use subquery materialization or IN-to-EXISTS transformations. For more information about these optimizations, see Section 8.2.2, “Optimizing Subqueries, Derived Tables, and View References”.

子查询提示影响是否使用半连接转换以及允许使用哪些半连接策略，以及在不使用半连接时，是否使用子查询物化或IN-to-EXISTS转换

Syntax of hints that affect semijoin strategies:

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

 The syntax refers to these terms:

语法指的是这些术语:

hint_name: These hint names are permitted:

hint_name:这些提示名是允许的:

• SEMIJOIN, NO_SEMIJOIN: Enable or disable the named semijoin strategies.

• 启用或禁用指定的半连接策略。

strategy: A semijoin strategy to be enabled or disabled. These strategy names are permitted: DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION.

strategy:启用或禁用半连接策略。这些策略名称是允许的:DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION。

For SEMIJOIN hints, if no strategies are named, semijoin is used if possible based on the strategies enabled according to the optimizer_switch system variable. If strategies are named but inapplicable for the statement, DUPSWEEDOUT is used.

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

For NO_SEMIJOIN hints, if no strategies are named, semijoin is not used. If strategies are named that rule out all applicable strategies for the statement, DUPSWEEDOUT is used.

对于NO_SEMIJOIN提示，如果没有命名策略，则不使用半连接。如果指定的策略排除了该语句的所有适用策略，则使用DUPSWEEDOUT。

If one subquery is nested within another and both are merged into a semijoin of an outer query, any specification of semijoin strategies for the innermost query are ignored. SEMIJOIN and NO_SEMIJOIN hints can still be used to enable or disable semijoin transformations for such nested subqueries.

如果一个子查询嵌套在另一个子查询中，并且两个子查询合并到外部查询的半连接中，则最内层查询的任何半连接策略规范都将被忽略。SEMIJOIN和NO_SEMIJOIN提示仍可用于为此类嵌套子查询启用或禁用半连接转换。

If DUPSWEEDOUT is disabled, on occasion the optimizer may generate a query plan that is far from optimal. This occurs due to heuristic pruning during greedy search, which can be avoided by setting optimizer_prune_level=0.

如果DUPSWEEDOUT被禁用，有时优化器可能生成一个远不是最优的查询计划。这是由于在贪婪搜索期间的启发式修剪造成的，可以通过设置optimizer_prune_level=0来避免。

Syntax of hints that affect whether to use subquery materialization or IN-to-EXISTS transformations: 

影响是否使用子查询物化或IN-to-EXISTS转换的提示语法:

The hint name is always SUBQUERY. 

提示名称总是SUBQUERY。
For SUBQUERY hints, these strategy values are permitted: INTOEXISTS, MATERIALIZATION.

对于SUBQUERY提示，这些策略值是允许的:INTOEXISTS, MATERIALIZATION。

For semijoin and SUBQUERY hints, a leading @query_block_name specifies the query block to which the hint applies. If the hint includes no leading @query_block_name, the hint applies to the query block in which it occurs. To assign a name to a query block, see Optimizer Hints for Naming Query Blocks.

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

If a hint comment contains multiple subquery hints, the first is used. If there are other following hints of that type, they produce a warning. Following hints of other types are silently ignored.

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

Statement Execution Time Optimizer Hints

语句执行时间优化器提示

The MAX_EXECUTION_TIME hint is permitted only for SELECT statements. It places a limit N (a timeout value in milliseconds) on how long a statement is permitted to execute before the server terminates it:

MAX_EXECUTION_TIME提示只允许用于SELECT语句。它设置了一个限制N(一个超时值，以毫秒为单位)，表示在服务器终止语句之前，允许语句执行的时间:

 Example with a timeout of 1 second (1000 milliseconds):

：超时时间为1秒(1000毫秒)的示例:

The MAX_EXECUTION_TIME(N) hint sets a statement execution timeout of N milliseconds. If this option is absent or N is 0, the statement timeout established by the max_execution_time system variable applies.

MAX_EXECUTION_TIME(N)提示设置语句执行超时时间为N毫秒。如果没有此选项或N为0，则应用由max_execution_time系统变量建立的语句超时。

The MAX_EXECUTION_TIME hint is applicable as follows:

MAX_EXECUTION_TIME提示如下所示:

• For statements with multiple SELECT keywords, such as unions or statements with subqueries, MAX_EXECUTION_TIME applies to the entire statement and must appear after the first SELECT.

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

• It applies to read-only SELECT statements. Statements that are not read only are those that invoke a stored function that modifies data as a side effect.

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

• It does not apply to SELECT statements in stored programs and is ignored.

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

Optimizer Hints for Naming Query Blocks

命名查询块的优化器提示

Table-level, index-level, and subquery optimizer hints permit specific query blocks to be named as part of their argument syntax. To create these names, use the QB_NAME hint, which assigns a name to the query block in which it occurs:

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

 

 

 QB_NAME hints can be used to make explicit in a clear way which query blocks other hints apply to. They also permit all non-query block name hints to be specified within a single hint comment for easier understanding of complex statements. Consider the following statement:

 QB_NAME提示可用于以明确的方式显式显示其他提示所应用的查询阻塞。它们还允许在单个提示注释中指定所有非查询块名称提示，以便更容易理解复杂的语句。考虑一下下面的陈述:

QB_NAME hints assign names to query blocks in the statement: 

 QB_NAME提示在语句中为查询块分配名称:

 

Then other hints can use those names to refer to the appropriate query blocks:

然后其他提示可以使用这些名称来引用适当的查询块:

The resulting effect is as follows:

结果如下:

• MRR(@qb1 t1) applies to table t1 in query block qb1.

• 适用于查询块qb1中的表t1。

• BKA(@qb2) applies to query block qb2.

• 适用于查询块qb2。

• NO_MRR(@qb3 t1 idx1, id2) applies to indexes idx1 and idx2 in table t1 in query block qb3.

• 适用于查询块qb中表t1中的索引idx1和idx2

Query block names are identifiers and follow the usual rules about what names are valid and how to quote them (see Section 9.2, “Schema Object Names”). For example, a query block name that contains spaces must be quoted, which can be done using backticks:

查询块名称是标识符，并遵循关于名称有效以及如何引用它们的通常规则(参见9.2节，“模式对象名称”)。例如，包含空格的查询块名称必须加引号，可以使用反勾号来完成:

If the ANSI_QUOTES SQL mode is enabled, it is also possible to quote query block names within double quotation marks:

如果启用ANSI_QUOTES SQL模式，也可以用双引号引用查询块名称: