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

Hint Name	Description	Applicable Scopes
BKA,?NO_BKA	Affects Batched Key Access join processing	Query block, table
BNL,?NO_BNL	Affects Block Nested-Loop join processing	Query block, table
MAX_EXECUTION_TIME	Limits statement execution time	Global
MRR,?NO_MRR	Affects Multi-Range Read optimization	Table, index
NO_ICP	Affects Index Condition Pushdown optimization	Table, index
NO_RANGE_OPTIMIZATION	Affects range optimization	Table, index
QB_NAME	Assigns name to query block	Query block
SEMIJOIN,?NO_SEMIJOIN	semijoin strategies	Query block
SUBQUERY	Affects materialization,?-to-?subquery stratgies	Query block

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 withincomments. That is, optimizer hints use a variant ofC-style comment syntax, with acharacter following thecomment opening sequence. Examples:

MySQL支持SQL语句中的注释，如9.6节“注释”所述。优化器提示必须在 c风格的注释语法，在这样的提示，MySQL使用第一个提示并发出关于重复提示的警告。

Conflicting hints: For a hint such as?, MySQL uses the first hint and issues a warning about the second conflicting hint.

冲突提示:对于像这样的提示，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:?

语法指的是这些术语:

: 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。

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

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

: The query block to which the hint applies. If the hint includes no leading?, the hint applies to the query block in which it occurs. For?@?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 processfirst, it applies a Block Nested-Loop join toby buffering the rows frombefore starting to read from?. If the optimizer instead chooses to processfirst, the hint has no effect becauseis 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:?

语法指的是这些术语:

: These hint names are permitted:

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

• MRR,?NO_MRR: Enable or disable MRR for the specified table or indexes. MRR hints apply only toandtables.

• 启用或禁用指定表或索引的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.禁用指定表或索引的索引范围访问。此提示还禁用表或索引的索引合并和松散索引扫描。默认情况下，范围访问是一种候选优化策略，因此没有启用它的提示。

: The table to which the hint applies.

tbl_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?. To see the index names for a table, use?SHOW INDEX.

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

: The query block to which the hint applies. If the hint includes no leading?, the hint applies to the query block in which it occurs. For?@?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?-to-?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:

语法指的是这些术语:

: These hint names are permitted:

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

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

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

: A semijoin strategy to be enabled or disabled. These strategy names are permitted:?,?,?,?.

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,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,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提示仍可用于为此类嵌套子查询启用或禁用半连接转换。

Ifis 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?-to-?transformations:?

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

The hint name is always?SUBQUERY.?

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

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

For semijoin and?SUBQUERY?hints, a leading??specifies the query block to which the hint applies. If the hint includes no leading?, 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??(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??milliseconds. If this option is absent or??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 multiplekeywords, 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 tablein query block?.

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

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

• 适用于查询块qb2。

• NO_MRR(@qb3 t1 idx1, id2)?applies to indexesandin tablein query block?.

• 适用于查询块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模式，也可以用双引号引用查询块名称:

?

?

?