mysql5.7 优化 三

8.8了解查询执行计划

8.8.1 使用 EXPLAIN 优化查询

8.8.1使用EXPLAIN优化查询

该EXPLAIN语句提供有关MySQL如何执行语句的信息：

• EXPLAIN与 SELECT, DELETE, INSERT, REPLACE,和 UPDATE语句一起使用。
• 当EXPLAIN与可解释语句一起使用时，MySQL显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理该语句，包括有关表如何连接以及以何种顺序连接的信息。有关使用 EXPLAIN获取执行计划信息的信息，请参阅第 8.8.2 节，“EXPLAIN 输出格式”。
• 当EXPLAIN与 而不是可解释的语句一起使用时，它显示在命名连接中执行的语句的执行计划。请参阅第 8.8.4 节，“获取命名连接的执行计划信息”。 FOR CONNECTION connection_id
• 对于SELECT语句， EXPLAIN生成可​​以使用显示的附加执行计划信息 SHOW WARNINGS。请参阅 第 8.8.3 节，“扩展 EXPLAIN 输出格式”。
• EXPLAIN对于检查涉及分区表的查询很有用。请参阅 第 22.3.5 节，“获取有关分区的信息”。
• 该FORMAT选项可用于选择输出格式。TRADITIONAL以表格格式显示输出。如果没有 FORMAT选项，这是默认设置。 JSONformat以JSON格式显示信息。

在 的帮助下EXPLAIN，您可以查看应该在哪里为表添加索引，以便通过使用索引查找行来更快地执行语句。您还可以使用 EXPLAIN检查优化器是否以最佳顺序连接表。要提示优化器使用与语句中表命名顺序相对应的连接顺序，请以 SELECT语句开头，SELECT STRAIGHT_JOIN而不仅仅是SELECT. （请参阅 第 13.2.9 节，“SELECT 语句”。）但是， STRAIGHT_JOIN可能会阻止使用索引，因为它禁用了半连接转换。看 第 8.2.2.1 节，“使用半连接转换优化子查询、派生表和视图引用”。

优化器跟踪有时可能会提供与EXPLAIN. 但是，优化器跟踪格式和内容可能会因版本而异。有关详细信息，请参阅 MySQL 内部：跟踪优化器。

如果您认为索引没有被使用时遇到问题，请运行ANALYZE TABLE以更新表统计信息，例如键的基数，这可能会影响优化器所做的选择。请参阅 第 13.7.2.1 节，“ANALYZE TABLE 语句”。

笔记

EXPLAIN也可用于获取有关表中列的信息。 是和 的同义词。有关更多信息，请参阅第 13.8.1 节，“DESCRIBE 语句”和 第 13.7.5.5 节，“SHOW COLUMNS 语句”。 EXPLAIN tbl_nameDESCRIBE tbl_nameSHOW COLUMNS FROM tbl_name

8.8.2 解释输出格式

8.8.2解释输出格式

该EXPLAIN语句提供有关MySQL如何执行语句的信息。 EXPLAIN与 SELECT, DELETE, INSERT, REPLACE,和 UPDATE语句一起使用。

EXPLAINSELECT为语句中使用的每个表返回一行信息 。它按照MySQL在处理语句时读取它们的顺序列出输出中的表。MySQL使用嵌套循环连接方法解析所有连接。这意味着MySQL从第一个表中读取一行，然后在第二个表、第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL会输出选定的列并通过表列表回溯，直到找到匹配行较多的表。从此表中读取下一行，并继续处理下一个表。

EXPLAIN输出包括分区信息。此外，对于SELECT 语句，EXPLAIN生成可​​以显示的扩展信息 SHOW WARNINGS（ EXPLAIN请参阅 第 8.8.3 节，“扩展 EXPLAIN 输出格式”）。

笔记

在较旧的MySQL版本中，分区和扩展信息是使用 EXPLAIN PARTITIONS和 生成的EXPLAIN EXTENDED。这些语法仍然被认为具有向后兼容性，但现在默认启用分区和扩展输出，因此PARTITIONS andEXTENDED关键字是多余的并且已弃用。使用它们会导致警告；EXPLAIN期望它们在未来的MySQL版本中 从语法中删除。

您不能在同一 语句中同时使用deprecatedPARTITIONS 和关键字。此外，这些关键字都不能与 选项一起使用。 EXTENDEDEXPLAINFORMAT

笔记

MySQL Workbench具有Visual Explain功能，可提供 EXPLAIN输出的可视化表示。请参阅 教程：使用 Explain 提高查询性能。

• 解释输出列
• 解释连接类型
• 解释额外信息
• EXPLAIN输出解释

解释输出列

本节介绍由 生成的输出列 EXPLAIN。后面的部分提供了有关 type 和 Extra 列的附加信息。

来自的每个输出行EXPLAIN 提供有关一个表的信息。每一行都包含 表8.1 “解释输出列”中总结的值，并在表后进行了更详细的描述。列名显示在表格的第一列；第二列提供了 FORMAT=JSON使用时输出中显示的等效属性名称。

表8.1 EXPLAIN输出列

笔记

JSON格式的输出 NULL中未显示的 JSON属性。EXPLAIN

• id（JSON名称 select_id：）

SELECT标识符 。这是查询中的序号 SELECT。NULL如果该行引用其他行的联合结果，则该值可以是。在这种情况下，该 table列显示一个值 ，表示该行是指具有 和值 的行的并 集。 <unionM,N>idMN

• select_type（JSON名称：无）

的类型SELECT，可以是下表中显示的任何一种。JSON格式将类型EXPLAIN公开 SELECT为a的属性 query_block，除非它是 SIMPLEor PRIMARY。JSON名称（如果适用）也显示在表中。

DEPENDENT通常表示使用相关子查询。请参阅 第 13.2.10.7 节，“相关子查询”。

DEPENDENT SUBQUERY评价不同于UNCACHEABLE SUBQUERY评价。对于DEPENDENT SUBQUERY，子查询仅针对其外部上下文中变量的每组不同值重新评估一次。对于 UNCACHEABLE SUBQUERY，为外部上下文的每一行重新评估子查询。

子查询的可缓存性不同于查询缓存中查询结果的缓存（在 第 8.10.3.1 节“查询缓存如何操作”中进行了描述）。子查询缓存发生在查询执行期间，而查询缓存仅用于在查询执行完成后存储结果。

当您指定FORMAT=JSONwith EXPLAIN时，输出没有直接等同于 select_type;的单一属性。该 query_block属性对应于给定的SELECT. 与刚刚显示的大多数子查询类型等效的属性SELECT都可用（例如 materialized_from_subqueryfor MATERIALIZED），并在适当时显示。SIMPLEor没有JSON等价物 PRIMARY。

select_type非语句 的值SELECT显示受影响表的语句类型。例如，select_typeis DELETEfor DELETE语句。

• table（JSON名称 table_name：）

输出行所引用的表的名称。这也可以是以下值之一：

• • <unionM,N>：该行是指具有 和 id值 的行的M并 集N。
  • <derivedN>：该行是指值为 的行的派生表id结果 N。例如，派生表可能来自 FROM子句中的子查询。
  • <subqueryN>：该行指的是id 值为的行的具体化子查询的结果N。请参阅 第 8.2.2.2 节，“使用物化优化子查询”。
• partitions（JSON名称 partitions：）

查询将匹配记录的分区。该值适用NULL于非分区表。请参阅 第 22.3.5 节，“获取有关分区的信息”。

• type（JSON名称 access_type：）

联接类型。有关不同类型的描述，请参阅 EXPLAIN 连接类型。

• possible_keys（JSON名称 possible_keys：）

该possible_keys列指示MySQL可以选择从中查找此表中的行的索引。请注意，此列完全独立于输出中显示的表格顺序 EXPLAIN。这意味着某些键在possible_keys实际中可能无法与生成的表顺序一起使用。

如果此列是NULL（或在JSON格式的输出中未定义），则没有相关索引。在这种情况下，您可以通过检查WHERE 子句来检查它是否引用了适合索引的某些列或列，从而提高查询的性能。如果是这样，请创建一个适当的索引并 EXPLAIN再次检查查询。请参阅 第 13.1.8 节，“ALTER TABLE 语句”。

要查看表有哪些索引，请使用. SHOW INDEX FROM tbl_name

• key（JSON名称key：）

该key列指示MySQL实际决定使用的键（索引）。如果MySQL决定使用其中一个possible_keys 索引来查找行，则该索引被列为键值。

可以key命名值中不存在的索引 possible_keys。如果没有possible_keys索引适合查找行，但查询选择的所有列都是其他索引的列，则可能会发生这种情况。也就是说，命名索引覆盖了选定的列，因此虽然它不用于确定要检索哪些行，但索引扫描比数据行扫描更有效。

对于InnoDB，即使查询还选择了主键，二级索引也可能覆盖选定的列，因为InnoDB每个二级索引都存储了主键值。如果 key是NULL，则MySQL没有找到可用于更有效地执行查询的索引。

要强制MySQL使用或忽略列中列出的索引 ，请在查询中possible_keys使用 FORCE INDEX、USE INDEX或IGNORE INDEX。请参阅第 8.9.4 节，“索引提示”。

对于MyISAM表，运行 ANALYZE TABLE有助于优化器选择更好的索引。对于 MyISAM表，myisamchk --analyze 也是如此。请参阅 第 13.7.2.1 节，“ANALYZE TABLE 语句”和 第 7.6 节，“MyISAM 表维护和崩溃恢复”。

• key_len（JSON名称 key_length：）

该key_len列指示MySQL决定使用的密钥的长度。的值 key_len使您能够确定MySQL实际使用的多部分键的多少部分。如果key列说 NULL，key_len 列也说NULL。

由于密钥存储格式的原因，列的密钥长度可能NULL 比NOT NULL列的大一。

• ref（JSON名称ref：）

该ref列显示将哪些列或常量与列中指定的索引进行比较以 key从表中选择行。

如果值为func，则使用的值是某个函数的结果。要查看哪个功能，请使用 SHOW WARNINGS以下 功能EXPLAIN查看扩展 EXPLAIN输出。该函数实际上可能是一个运算符，例如算术运算符。

• rows（JSON名称 rows：）

该rows列指示MySQL认为它必须检查以执行查询的行数。

对于InnoDB表格，这个数字是一个估计值，可能并不总是准确的。

• filtered（JSON名称 filtered：）

该filtered列指示按表条件过滤的表行的估计百分比。最大值为100，这意味着没有过滤行。从100开始减小的值表示过滤量增加。 rows显示检查的估计行数，rows× filtered显示与下表连接的行数。例如，如果 rows是1000并且 filtered是50.00 (50%)，则要与下表连接的行数是1000 × 50% = 500。

• Extra（JSON名称：无）

此列包含有关MySQL如何解析查询的附加信息。有关不同值的描述，请参阅 EXPLAIN 额外信息。

没有与该 Extra列对应的单个JSON属性；但是，此列中可能出现的值将作为JSON属性或属性文本公开message。

解释连接类型

输出列描述type了 EXPLAIN表是如何连接的。在JSON格式的输出中，这些是作为access_type属性的值找到的。以下列表描述了连接类型，按从最佳到最差的顺序排列：

• system

该表只有一行（=系统表）。const这是连接类型 的一个特例 。

• const

该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以这一行中列的值可以被优化器的其余部分视为常量。 const表非常快，因为它们只被读取一次。

constPRIMARY KEY当您将a或 UNIQUE索引的所有部分与常量值进行比较时使用。在以下查询中，tbl_name可以用作const 表：

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name

  WHERE primary_key_part1=1 AND primary_key_part2=2;

• eq_ref

对于先前表中的每个行组合，从该表中读取一行。除了 systemand const类型，这是最好的连接类型。当连接使用索引的所有部分并且索引是a PRIMARY KEY或UNIQUE NOT NULL索引时使用它。

eq_ref可用于使用 =运算符比较的索引列。比较值可以是常量或使用在此表之前读取的表中的列的表达式。在以下示例中，MySQL可以使用 eq_ref连接来处理 ref_table：

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

  AND ref_table.key_column_part2=1;

• ref

对于先前表中的每个行组合，从该表中读取具有匹配索引值的所有行。ref如果连接仅使用键的最左前缀或键不是aPRIMARY KEY或 UNIQUE索引（换句话说，如果连接不能基于键值选择单行），则使用。如果使用的键只匹配几行，这是一个很好的连接类型。

ref可用于使用 =or<=> 运算符比较的索引列。在以下示例中，MySQL可以使用 ref连接来处理 ref_table：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table

  WHERE ref_table.key_column_part1=other_table.column

  AND ref_table.key_column_part2=1;

• fulltext

连接是使用FULLTEXT 索引执行的。

• ref_or_null

这种连接类型类似于 ref，但另外MySQL会额外搜索包含NULL值的行。这种连接类型优化最常用于解析子查询。在以下示例中，MySQL可以使用 ref_or_null连接来处理ref_table：

SELECT * FROM ref_table

  WHERE key_column=expr OR key_column IS NULL;

请参阅第 8.2.1.13 节，“IS NULL 优化”。

• index_merge

此连接类型表明使用了索引合并优化。在这种情况下，key输出行中的列包含所用索引的列表，并 key_len包含所用索引的最长键部分的列表。有关更多信息，请参阅 第 8.2.1.3 节，“索引合并优化”。

• unique_subquery

此类型替换 以下形式eq_ref的某些 IN子查询：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery只是一个索引查找功能，完全替代子查询以提高效率。

• index_subquery

此连接类型类似于 unique_subquery. 它替换IN子查询，但它适用于以下形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range

仅检索给定范围内的行，使用索引选择行。输出行中的key 列指示使用了哪个索引。key_len包含使用的最长的关键部分。该ref列适用 NULL于这种类型。

range可以在使用, , , , , , , , , ,或 运算符中的任何 =一个 <>将 >键 >=列 <与 <=常量 IS NULL进行 <=>比较 BETWEEN时 LIKE使用 IN()：

SELECT * FROM tbl_name

  WHERE key_column = 10;

SELECT * FROM tbl_name

  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name

  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name

  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

• index

连接类型与index相同 ALL，只是扫描了索引树。这有两种方式：

• • 如果索引是查询的覆盖索引并且可以用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，该Extra列 显示Using index。仅索引扫描通常比仅索引扫描更快， ALL因为索引的大小通常小于表数据。
  • 使用从索引中读取以按索引顺序查找数据行来执行全表扫描。 Uses index没有出现在 Extra列中。

当查询仅使用属于单个索引的列时，MySQL可以使用此连接类型。

• ALL

对先前表中的每个行组合进行全表扫描。如果该表是第一个未标记的表 ，这通常不好，并且在所有其他情况下const通常 非常糟糕。通常，您可以 ALL通过添加索引来避免基于先前表中的常量值或列值从表中检索行。

解释额外信息

输出列Extra包含 EXPLAIN有关MySQL如何解析查询的附加信息。下面的列表解释了可以出现在此列中的值。每个项目还为JSON格式的输出指示哪个属性显示该Extra值。对于其中一些，有一个特定的属性。其他显示为message 属性的文本。

如果您想尽可能快地进行查询，请注意and的列Extra值，或者，在JSON格式的输出中，对于 和 属性等于 。 Using filesortUsing temporaryEXPLAINusing_filesortusing_temporary_tabletrue

• Child of 'table' pushed join@1（JSON：message 文本）

该表被引用为 table可以下推到NDB内核的连接中的子项。仅在启用下推连接时适用于NDB Cluster。ndb_join_pushdown有关更多信息和示例， 请参阅服务器系统变量的描述 。

• const row not found（JSON属性 const_row_not_found：）

对于诸如 的查询，表是空的。 SELECT ... FROM tbl_name

• Deleting all rows（JSON属性 message：）

对于DELETE，一些存储引擎（例如MyISAM）支持一种处理程序方法，该方法以简单快速的方式删除所有表行。Extra如果引擎使用此优化，则会显示 此值。

• Distinct（JSON属性 distinct：）

MySQL正在寻找不同的值，因此它在找到第一个匹配行后停止为当前行组合搜索更多行。

• FirstMatch(tbl_name) （JSON属性first_match：）

semijoin FirstMatch连接快捷策略用于tbl_name.

• Full scan on NULL key（JSON属性 message：）

当优化器无法使用索引查找访问方法时，子查询优化会发生这种情况作为回退策略。

• Impossible HAVING（JSON属性 message：）

该HAVING子句始终为false，不能选择任何行。

• Impossible WHERE（JSON属性 message：）

该WHERE子句始终为false，不能选择任何行。

• Impossible WHERE noticed after reading const tables（JSON属性 message：）

MySQL已读取所有 const(and system)表并注意到该WHERE子句始终为false。

• LooseScan(m..n) （JSON属性message：）

使用半连接LooseScan策略。 m并且 n是关键部件号。

• No matching min/max row（JSON属性 message：）

没有行满足查询的条件，例如 . SELECT MIN(...) FROM ... WHERE condition

• no matching row in const table（JSON属性message：）

对于带有连接的查询，有一个空表或没有满足唯一索引条件的行的表。

• No matching rows after partition pruning（JSON属性 message：）

对于DELETEor UPDATE，优化器在分区修剪后没有发现要删除或更新的内容。Impossible WHERE 它与forSELECT语句 的含义相似。

• No tables used（JSON属性 message：）

查询没有FROM子句，或有 FROM DUAL子句。

For INSERTor REPLACE语句， EXPLAIN当没有SELECT 部分时显示此值。例如，它出现forEXPLAIN INSERT INTO t VALUES(10)是因为that等价于 EXPLAIN INSERT INTO t SELECT 10 FROM DUAL。

• Not exists（JSON属性 message：）

MySQL能够对LEFT JOIN 查询进行优化，并且在找到与条件匹配的行后，不会检查该表中的前一行组合的更多行LEFT JOIN。以下是可以通过这种方式优化的查询类型的示例：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id

  WHERE t2.id IS NULL;

假设t2.id定义为 NOT NULL。在这种情况下，MySQL 使用 的值 扫描t1并查找行 。如果MySQL在 中找到匹配的行 ，它就知道 永远不可能 ，并且不会扫描具有相同值的其余行。换句话说，对于 中的每一行，MySQL只需要在 中进行一次查找，而不管在 中实际匹配了多少行。 t2t1.idt2t2.idNULLt2idt1t2t2

• Plan isn't ready yet（JSON属性：无）

EXPLAIN FOR CONNECTION当优化器尚未完成为在命名连接中执行的语句创建执行计划时， 会出现此值。如果执行计划输出包含多行，则任何或所有行都可能具有此 Extra值，具体取决于优化器在确定完整执行计划时的进度。

• Range checked for each record (index map: N)（JSON属性 message：）

MySQL没有找到可以使用的好的索引，但发现某些索引可能会在之前表中的列值已知后使用。对于前面表格中的每个行组合，MySQL检查是否可以使用rangeor index_merge访问方法来检索行。这不是很快，但比执行完全没有索引的连接要快。适用性标准如 第 8.2.1.2 节“范围优化”和 第 8.2.1.3 节“索引合并优化”中所述，但上表的所有列值都是已知的并被视为常量。

索引从1开始编号，顺序与表中所示的相同SHOW INDEX。索引映射值 N是指示哪些索引是候选索引的位掩码值。例如，值0x19（二进制11001）表示考虑索引1、4和5。

• Scanned N databases（JSON属性 message：）

这表示在处理表查询时服务器执行了多少目录扫描 INFORMATION_SCHEMA，如第 8.2.3 节“优化 INFORMATION_SCHEMA 查询”中所述。的值N可以是0、1或 all。

• Select tables optimized away（JSON属性message：）

优化器确定1)最多应该返回一行，以及2)要生成这一行，必须读取一组确定性的行。当在优化阶段可以读取要读取的行时（例如，通过读取索引行），在查询执行期间不需要读取任何表。

当查询被隐式分组（包含聚合函数但没有 GROUP BY子句）时，第一个条件得到满足。当每个使用的索引执行一次行查找时，满足第二个条件。读取的索引数决定了要读取的行数。

考虑以下隐式分组查询：

SELECT MIN(c1), MIN(c2) FROM t1;

假设MIN(c1)可以通过读取一个索引行MIN(c2) 来检索它，并且可以通过从不同的索引读取一行来检索它。也就是说，对于每一列c1和 c2，都存在一个索引，其中该列是索引的第一列。在这种情况下，通过读取两个确定性行来返回一行。

Extra如果要读取的行不确定，则不会出现 此值。考虑这个查询：

SELECT MIN(c2) FROM t1 WHERE c1 <= 10;

假设这(c1, c2)是一个覆盖索引。使用此索引，c1 <= 10必须扫描所有行以找到最小值 c2。相比之下，考虑这个查询：

SELECT MIN(c2) FROM t1 WHERE c1 = 10;

在这种情况下，第一个索引行c1 = 10包含最小值c2 。只需读取一行即可生成返回的行。

对于维护每个表的精确行数的存储引擎（例如MyISAM，但不是 InnoDB），对于缺少子句或始终为真且没有子句的查询，Extra 可能会出现此值 。（这是一个隐式分组查询的实例，其中存储引擎影响是否可以读取确定的行数。） COUNT(*)WHEREGROUP BY

• Skip_open_table, Open_frm_only, Open_full_table(JSON属性: message)

这些值表示适用于INFORMATION_SCHEMA 表查询的文件打开优化，如 第 8.2.3 节“优化 INFORMATION_SCHEMA 查询”中所述。

• • Skip_open_table:表格文件不需要打开。通过扫描数据库目录，查询中的信息已经可用。
  • Open_frm_only.frm：只需要打开 表的文件。
  • Open_full_table:未优化的信息查找。、.frm和 文件必须打开 .MYD。 .MYI
• Start temporary, End temporary(JSON属性: message)

这表明临时表用于semijoin Duplicate Weedout策略。

• unique row not found（JSON属性 message：）

对于诸如 的查询，没有行满足 索引或表的条件。 SELECT ... FROM tbl_nameUNIQUEPRIMARY KEY

• Using filesort（JSON属性 using_filesort：）

MySQL必须做一个额外的过程来找出如何按排序顺序检索行。排序是通过根据连接类型遍历所有行并存储排序键和指向与WHERE子句匹配的所有行的行的指针来完成的。然后对键进行排序，并按排序顺序检索行。请参见 第 8.2.1.14 节，“按优化排序”。

• Using index（JSON属性 using_index：）

仅使用索引树中的信息从表中检索列信息，而无需执行额外的查找来读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。

对于InnoDB具有用户定义的聚集索引的表，即使列Using index中不存在 该索引，也可以使用该索引Extra。如果 typeis index和 keyis就是这种情况PRIMARY。

• Using index condition（JSON属性 using_index_condition：）

通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。这样，除非有必要，否则索引信息用于延迟（ “下推”）读取全表行。请参阅 第 8.2.1.5 节，“索引条件下推优化”。

• Using index for group-by（JSON属性using_index_for_group_by：）

与Using index表访问方法类似，Using index for group-by 表明MySQL找到了一个索引，该索引可用于检索aGROUP BY或 DISTINCT查询的所有列，而无需对实际表进行任何额外的磁盘访问。此外，索引以最有效的方式使用，因此对于每个组，只读取几个索引条目。有关详细信息，请参阅 第 8.2.1.15 节，“GROUP BY 优化”。

• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access) (JSON属性: using_join_buffer)

来自早期连接的表被部分读入连接缓冲区，然后从缓冲区中使用它们的行来执行与当前表的连接。 (Block Nested Loop)指示使用块嵌套循环算法并(Batched Key Access)指示使用批量密钥访问算法。也就是说， EXPLAIN输出前一行的表中的键被缓冲，匹配的行从出现的行所代表的表中批量提取 Using join buffer。

在JSON格式的输出中， 的值 using_join_buffer始终是Block Nested Loop或 之一Batched Key Access。

有关这些算法的更多信息，请参阅 Block Nested-Loop Join Algorithm和 Batched Key Access Joins。

• Using MRR（JSON属性 message：）

使用多范围读取优化策略读取表。请参阅第 8.2.1.10 节，“多范围读取优化”。

• Using sort_union(...), Using union(...), Using intersect(...)(JSON属性: message)

index_merge这些指示显示如何为连接类型 合并索引扫描的特定算法 。请参阅第 8.2.1.3 节，“索引合并优化”。

• Using temporary（JSON属性 using_temporary_table：）

为了解析查询，MySQL需要创建一个临时表来保存结果。如果查询包含以不同方式列出列 的GROUP BY和 子句，通常会发生这种情况。ORDER BY

• Using where（JSON属性 attached_condition：）

子句用于限制与下一个WHERE表匹配或发送到客户端的行。Extra除非您特别打算从表中获取或检查所有行，否则如果值不是 Using where并且表连接类型是 ALLor ，则您的查询可能有问题index。

Using where在JSON格式的输出中没有直接对应项；该 attached_condition属性包含WHERE使用的任何条件。

• Using where with pushed condition（JSON属性message：）

此项仅适用于NDB 表格。这意味着NDB Cluster正在使用Condition Pushdown优化来提高非索引列和常量之间直接比较的效率。在这种情况下，条件被“下推”到集群的数据节点，并同时在所有数据节点上进行评估。这消除了通过网络发送不匹配行的需要，并且可以将此类查询速度提高5到10倍，比可以使用但不使用条件下推的情况。有关详细信息，请参阅 第 8.2.1.4 节，“发动机工况下推优化”。

• Zero limit（JSON属性 message：）

该查询有一个LIMIT 0子句，不能选择任何行。

EXPLAIN输出解释

通过获取输出rows 列中 值的乘积，您可以很好地了解连接的好坏。EXPLAIN这应该大致告诉您MySQL必须检查多少行才能执行查询。如果您使用 max_join_size系统变量限制查询，则此行积还用于确定SELECT 要执行哪些多表语句以及要中止哪些语句。请参见 第 5.1.1 节，“配置服务器”。

以下示例显示了如何根据提供的信息逐步优化多表连接 EXPLAIN。

假设您有 SELECT此处显示的语句，并且您计划使用以下命令对其进行检查 EXPLAIN：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,

               tt.ProjectReference, tt.EstimatedShipDate,

               tt.ActualShipDate, tt.ClientID,

               tt.ServiceCodes, tt.RepetitiveID,

               tt.CurrentProcess, tt.CurrentDPPerson,

               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,

               et_1.COUNTRY, do.CUSTNAME

        FROM tt, et, et AS et_1, do

        WHERE tt.SubmitTime IS NULL

          AND tt.ActualPC = et.EMPLOYID

          AND tt.AssignedPC = et_1.EMPLOYID

          AND tt.ClientID = do.CUSTNMBR;

对于此示例，请做出以下假设：

• 被比较的列已声明如下。

• 这些表具有以下索引。

• 这些tt.ActualPC值不是均匀分布的。

最初，在执行任何优化之前，该 EXPLAIN语句会生成以下信息：

table type possible_keys key  key_len ref  rows  Extra

et    ALL  PRIMARY       NULL NULL    NULL 74

do    ALL  PRIMARY       NULL NULL    NULL 2135

et_1  ALL  PRIMARY       NULL NULL    NULL 74

tt    ALL  AssignedPC,   NULL NULL    NULL 3872

           ClientID,

           ActualPC

      Range checked for each record (index map: 0x23)

因为type对于 ALL每个表，这个输出表明MySQL正在生成所有表的笛卡尔积；也就是说，行的每个组合。这需要相当长的时间，因为必须检查每个表中行数的乘积。对于手头的情况，这个产品是74 × 2135 × 74 × 3872 = 45,268,558,720行。如果桌子更大，您只能想象需要多长时间。

这里的一个问题是，如果将列声明为相同的类型和大小，MySQL可以更有效地使用列上的索引。在这种情况下，如果它们被声明为相同的大小，则认为它们是相同的 VARCHAR。 被声明为 and is ，因此存在长度不匹配。 CHARtt.ActualPCCHAR(10)et.EMPLOYIDCHAR(15)

要修复列长度之间的这种差异，请使用 ALTER TABLE将10个字符延长 ActualPC到15个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在tt.ActualPC和 et.EMPLOYID都是 VARCHAR(15)。再次执行该 EXPLAIN语句会产生以下结果：

table type   possible_keys key     key_len ref         rows    Extra

tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using

             ClientID,                                         where

             ActualPC

do    ALL    PRIMARY       NULL    NULL    NULL        2135

      Range checked for each record (index map: 0x1)

et_1  ALL    PRIMARY       NULL    NULL    NULL        74

      Range checked for each record (index map: 0x1)

et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

这并不完美，但要好得多：这些 rows值的乘积小了74倍。这个版本在几秒钟内执行。

可以进行第二次更改以消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比较的列长度不匹配：

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),

                      MODIFY ClientID   VARCHAR(15);

修改后， EXPLAIN产生如下所示的输出：

table type   possible_keys key      key_len ref           rows Extra

et    ALL    PRIMARY       NULL     NULL    NULL          74

tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using

             ClientID,                                         where

             ActualPC

et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1

do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

在这一点上，查询几乎被尽可能地优化了。剩下的问题是，默认情况下，MySQL假定tt.ActualPC 列中的值是均匀分布的，而表并非如此tt。幸运的是，告诉MySQL分析密钥分布很容易：

mysql> ANALYZE TABLE tt;

使用附加的索引信息，连接是完美的并 EXPLAIN产生以下结果：

table type   possible_keys key     key_len ref           rows Extra

tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using

             ClientID,                                        where

             ActualPC

et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1

et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1

do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

输出中的rows列 EXPLAIN是来自MySQL连接优化器的有根据的猜测。rows通过将产品与查询返回的实际行数进行比较，检查这些数字是否更接近事实 。如果数字完全不同，您可能会通过 STRAIGHT_JOIN在 SELECT语句中使用并尝试在 FROM子句中以不同的顺序列出表来获得更好的性能。（但是， STRAIGHT_JOIN可能会阻止使用索引，因为它禁用了半连接转换。请参阅 第 8.2.2.1 节，“使用半连接转换优化子查询、派生表和视图引用”.)

EXPLAIN SELECT在某些情况下，当与子查询一起使用 时，可以执行修改数据的语句；有关更多信息，请参阅第 13.2.10.8 节，“派生表”。

8.8.3 扩展 EXPLAIN 输出格式

8.8.3扩展EXPLAIN输出格式

对于SELECT语句， EXPLAIN语句会产生额外的（“扩展”）信息，这些信息不是 EXPLAIN输出的一部分，但可以通过在.SHOW WARNINGS 后面发出语句来查看EXPLAIN。输出中的 Message值SHOW WARNINGS显示优化器如何限定 SELECT语句 中的表名和列名，SELECT应用重写和优化规则后的样子，以及可能有关优化过程的其他注释。

仅针对语句生成 可显示SHOW WARNINGS以下语句 的扩展信息 。 显示其他可解释语句（、 、 和 ）的空结果。 EXPLAINSELECTSHOW WARNINGSDELETEINSERTREPLACEUPDATE

笔记

在较旧的MySQL版本中，扩展信息是使用EXPLAIN EXTENDED. 该语法仍被认为具有向后兼容性，但现在默认启用扩展输出，因此该EXTENDED关键字是多余的且已弃用。使用它会导致警告；EXPLAIN期望它在未来的MySQL版本中 从语法中删除 。

这是扩展 EXPLAIN输出的示例：

mysql> EXPLAIN

       SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: t1

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 4

          ref: NULL

         rows: 4

     filtered: 100.00

        Extra: Using index

*************************** 2. row ***************************

           id: 2

  select_type: SUBQUERY

        table: t2

         type: index

possible_keys: a

          key: a

      key_len: 5

          ref: NULL

         rows: 3

     filtered: 100.00

        Extra: Using index

2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G

*************************** 1. row ***************************

  Level: Note

   Code: 1003

Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,

         <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in

         ( <materialize> (/* select#2 */ select `test`.`t2`.`a`

         from `test`.`t2` where 1 having 1 ),

         <primary_index_lookup>(`test`.`t1`.`a` in

         <temporary table> on <auto_key>

         where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a

         IN (SELECT t2.a FROM t2)` from `test`.`t1`

1 row in set (0.00 sec)

因为显示的语句SHOW WARNINGS可能包含特殊标记以提供有关查询重写或优化器操作的信息，所以该语句不一定是有效的SQL，并且不打算执行。输出还可能包含带有 Message值的行，这些值提供有关优化器所采取的操作的附加非SQL解释性说明。

以下列表描述了可以出现在由 显示的扩展输出中的特殊标记SHOW WARNINGS：

• <auto_key>

为临时表自动生成的键。

• <cache>(expr)

表达式（例如标量子查询）执行一次，结果值保存在内存中供以后使用。对于包含多个值的结果，可能会创建一个临时表，而您可能会看到它<temporary table>。

• <exists>(query fragment)

将子查询谓词转换为 EXISTS谓词，并对子查询进行转换，以便它可以与 EXISTS谓词一起使用。

• <in_optimizer>(query fragment)

这是一个没有用户意义的内部优化器对象。

• <index_lookup>(query fragment)

使用索引查找来处理查询片段以查找符合条件的行。

• <if>(condition, expr1, expr2)

如果条件为真，则计算为 expr1，否则 为expr2。

• <is_not_null_test>(expr)

验证表达式不计算为 的测试 NULL。

• <materialize>(query fragment)

使用子查询实现。

• `materialized-subquery`.col_name

col_name对内部临时表中 列的引用具体 化以保存评估子查询的结果。

• <primary_index_lookup>(query fragment)

使用主键查找来处理查询片段以查找符合条件的行。

• <ref_null_helper>(expr)

这是一个没有用户意义的内部优化器对象。

• /* select#N */ select_stmt

SELECT与非扩展EXPLAIN输出中id值为 的行相关联N。

• outer_tables semi join (inner_tables)

半连接操作。 inner_tables显示未拉出的表。请参阅第 8.2.2.1 节，“使用半连接转换优化子查询、派生表和视图引用”。

• <temporary table>

这表示为缓存中间结果而创建的内部临时表。

当某些表属于const orsystem类型时，涉及这些表中的列的表达式由优化器提前求值，而不是显示语句的一部分。但是，使用 时FORMAT=JSON，某些 const表访问会显示为ref使用const值的访问。

8.8.4 获取命名连接的执行计划信息

8.8.4获取命名连接的执行计划信息

要获取在命名连接中执行的可解释语句的执行计划，请使用以下语句：

EXPLAIN [options] FOR CONNECTION connection_id;

EXPLAIN FOR CONNECTION返回EXPLAIN当前用于在给定连接中执行查询的信息。由于对数据（和支持统计信息）的更改，它可能会产生与 EXPLAIN在等效查询文本上运行不同的结果。这种行为差异可用于诊断更多瞬态性能问题。例如，如果您在一个会话中运行需要很长时间才能完成的语句，则EXPLAIN FOR CONNECTION在另一个会话中使用可能会产生有关延迟原因的有用信息。

connection_id是从 INFORMATION_SCHEMA PROCESSLIST表或 SHOW PROCESSLIST语句中获得的连接标识符。如果您有PROCESS权限，您可以指定任何连接的标识符。否则，您只能为自己的连接指定标识符。

如果命名连接没有执行语句，则结果为空。否则，EXPLAIN FOR CONNECTION 仅当在命名连接中执行的语句是可解释的时才适用。这包括 SELECT、 DELETE、 INSERT、 REPLACE和 UPDATE。（但是， EXPLAIN FOR CONNECTION不适用于准备好的语句，即使是那些类型的准备好的语句。）

如果命名连接正在执行一个可解释的语句，那么输出就是您 EXPLAIN在语句本身上使用所获得的结果。

如果命名连接正在执行无法解释的语句，则会发生错误。例如，您不能为当前会话命名连接标识符，因为 EXPLAIN无法解释：

mysql> SELECT CONNECTION_ID();

+-----------------+

| CONNECTION_ID() |

+-----------------+

|             373 |

+-----------------+

1 row in set (0.00 sec)

mysql> EXPLAIN FOR CONNECTION 373;

ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported

only for SELECT/UPDATE/INSERT/DELETE/REPLACE

Com_explain_otherstatus变量表示执行的 语句EXPLAIN FOR CONNECTION数。

8.8.5 估计查询性能

8.8.5估计查询性能

在大多数情况下，您可以通过计算磁盘寻道次数来估计查询性能。对于小型表，通常可以在一次磁盘查找中找到一行（因为索引可能已缓存）。对于更大的表，您可以估计，使用B-tree索引，您需要这么多次查找才能找到一行： . log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1

在MySQL中，索引块通常为1,024字节，数据指针通常为4字节。对于一个500,000行的表，键值长度为3个字节（大小为 MEDIUMINT），公式表示 log(500,000)/log(1024/3*2/(3+4)) + 1= 4seeks。

该索引需要大约500,000 * 7 * 3/2 = 5.2MB的存储空间（假设典型的索引缓冲区填充率为2/3），因此您可能在内存中有很多索引，因此只需要一两次调用读取数据以查找行。

但是，对于写入，您需要四个查找请求来查找放置新索引值的位置，通常需要两次查找来更新索引并写入行。

前面的讨论并不意味着您的应用程序性能会因log而缓慢下降 N。只要一切都被操作系统或MySQL服务器缓存，随着表变大，事情只会稍微变慢。在数据变得太大而无法缓存后，事情开始变得慢得多，直到您的应用程序仅受磁盘搜索（增加log N）的约束。为避免这种情况，请随着数据的增长增加密钥缓存大小。对于MyISAM 表，键缓存大小由 key_buffer_size系统变量控制。请参见第 5.1.1 节，“配置服务器”。

根据您的表、列、索引和WHERE子句中的条件的详细信息，MySQL优化器会考虑许多技术来有效地执行SQL查询中涉及的查找。可以在不读取所有行的情况下对一个巨大的表执行查询；可以在不比较每个行组合的情况下执行涉及多个表的连接。优化器选择执行最有效查询的一组操作称为“查询执行计划”，也称为 EXPLAIN计划。你的目标是认识到 EXPLAIN计划表明查询已优化好，并学习SQL语法和索引技术以改进计划，如果您发现一些低效的操作。

8.9控制查询优化器

8.9.1 控制查询计划评估

8.9.1控制查询计划评估

查询优化器的任务是找到执行SQL查询的最佳计划。因为“好”和“坏”之间的性能差异计划可以是数量级的（即几秒与几小时甚至几天），大多数查询优化器，包括MySQL的优化器，在所有可能的查询评估计划中执行或多或少详尽的搜索以找到最佳计划。对于连接查询，MySQL优化器调查的可能计划的数量随着查询中引用的表的数量呈指数增长。对于少量表（通常少于7到10个），这不是问题。但是，当提交较大的查询时，查询优化所花费的时间很容易成为服务器性能的主要瓶颈。

一种更灵活的查询优化方法使用户能够控制优化器在搜索最佳查询评估计划时的详尽程度。一般的想法是优化器调查的计划越少，编译查询所花费的时间就越少。另一方面，由于优化器跳过了一些计划，它可能会错过找到最佳计划。

优化器对其评估的计划数量的行为可以使用两个系统变量来控制：

• 该optimizer_prune_level 变量告诉优化器根据对每个表访问的行数的估计跳过某些计划。我们的经验表明，这种“有根据的猜测”很少会错过最佳计划，并且可能会大大减少查询编译时间。这就是为什么此选项optimizer_prune_level=1默认启用( )的原因。但是，如果您认为优化器错过了更好的查询计划，则可以关闭此选项（optimizer_prune_level=0)存在查询编译可能需要更长时间的风险。请注意，即使使用了这种启发式方法，优化器仍会探索大致呈指数级的计划数。
• 该变量告诉优化器应该评估每个不完整计划的“未来”optimizer_search_depth 多远，以评估它是否应该进一步扩展。较小的值 可能会导致查询编译时间减少几个数量级。例如，具有12、13或更多表的查询可能很容易需要数小时甚至数天的时间来编译，如果 接近查询中的表数。同时，如果编译用 optimizer_search_depthoptimizer_search_depthoptimizer_search_depth 等于3或4，优化器可以在不到一分钟的时间内编译相同的查询。如果您不确定 的合理值是多少 optimizer_search_depth，可以将此变量设置为0以告诉优化器自动确定该值。

8.9.2 可切换优化

8.9.2可切换优化

系统optimizer_switch变量可以控制优化器的行为。它的值是一组标志，每个标志都有一个值on 或off表示相应的优化器行为是启用还是禁用。此变量具有全局值和会话值，可以在运行时更改。全局默认值可以在服务器启动时设置。

要查看当前的优化器标志集，请选择变量值：

mysql> SELECT @@optimizer_switch\G

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on,

                    index_merge_sort_union=on,

                    index_merge_intersection=on,

                    engine_condition_pushdown=on,

                    index_condition_pushdown=on,

                    mrr=on,mrr_cost_based=on,

                    block_nested_loop=on,batched_key_access=off,

                    materialization=on,semijoin=on,loosescan=on,

                    firstmatch=on,duplicateweedout=on,

                    subquery_materialization_cost_based=on,

                    use_index_extensions=on,

                    condition_fanout_filter=on,derived_merge=on,

                    prefer_ordering_index=on

要更改 的值 optimizer_switch，请分配一个由一个或多个命令的逗号分隔列表组成的值：

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

每个command值应具有下表中显示的一种形式。

值中命令的顺序无关紧要，尽管default如果存在该命令，则首先执行该命令。设置opt_name标志以 default将其设置 为默认值on或off默认值。opt_name 不允许在值中多次指定任何给定值，这会导致错误。value中的任何错误都会导致分配失败并出现错误，而value optimizer_switch保持不变。

以下列表描述了允许的 opt_name标志名称，按优化策略分组：

• 批量密钥访问标志
  • batched_key_access （默认off）

控制BKA连接算法的使用。

• 要batched_key_access在设置为 时产生任何效果on， mrr标志也必须是 on。目前，MRR的成本估算过于悲观。因此，也有必要 mrr_cost_based使用 offBKA。
• 有关更多信息，请参阅 第 8.2.1.11 节，“阻止嵌套循环和批量密钥访问连接”。
• 阻止嵌套循环标志
  • block_nested_loop （默认on）

控制BNL连接算法的使用。

• 有关更多信息，请参阅 第 8.2.1.11 节，“阻止嵌套循环和批量密钥访问连接”。
• 条件过滤标志
  • condition_fanout_filter （默认on）

控制条件过滤的使用。

• 有关详细信息，请参阅 第 8.2.1.12 节，“条件过滤”。
• 派生表合并标志
  • derived_merge（默认 on）

控制将派生表和视图合并到外部查询块中。

• 该derived_merge标志控制优化器是否尝试将派生表和视图引用合并到外部查询块中，假设没有其他规则阻止合并；例如，ALGORITHM视图指令优先于 derived_merge设置。默认情况下，该标志是on启用合并。
• 有关更多信息，请参阅 第 8.2.2.4 节，“使用合并或实现优化派生表和查看引用”。
• 发动机状态下推标志
  • engine_condition_pushdown （默认on）

控制发动机状态下推。

• 有关详细信息，请参阅 第 8.2.1.4 节，“发动机工况下推优化”。
• 索引条件下推标志
  • index_condition_pushdown （默认on）

控制索引条件下推。

• 有关更多信息，请参阅 第 8.2.1.5 节，“索引条件下推优化”。
• 索引扩展标志
  • use_index_extensions （默认on）

控制索引扩展的使用。

• 有关更多信息，请参阅 第 8.3.9 节，“索引扩展的使用”。
• 索引合并标志
  • index_merge（默认 on）

控制所有索引合并优化。

• • index_merge_intersection （默认on）

控制索引合并交叉点访问优化。

• • index_merge_sort_union （默认on）

控制索引合并排序联合访问优化。

• • index_merge_union （默认on）

控制索引合并联合访问优化。

• 有关更多信息，请参阅 第 8.2.1.3 节，“索引合并优化”。
• 限制优化标志
  • prefer_ordering_index （默认on）

控制在查询具有 ORDER BYor子句GROUP BY的情况下LIMIT，优化器是否尝试使用有序索引而不是无序索引、文件排序或其他一些优化。每当优化器确定使用它可以更快地执行查询时，默认情况下都会执行此优化。

因为做出这种决定的算法不能处理所有可能的情况（部分原因是假设数据的分布总是或多或少是均匀的），所以在某些情况下，这种优化可能是不可取的。在MySQL 5.7.33之前，无法禁用此优化，但在MySQL 5.7.33及更高版本中，虽然它仍然是默认行为，但可以通过将 prefer_ordering_index 标志设置为 来禁用它off。

• 有关更多信息和示例，请参阅 第 8.2.1.17 节，“限制查询优化”。
• 多范围读取标志
  • mrr（默认 on）

控制多范围读取策略。

• • mrr_cost_based （默认on）

如果.则控制基于成本的MRR的使用 mrr=on。

• 有关详细信息，请参阅 第 8.2.1.10 节，“多范围读取优化”。
• 半连接标志
  • duplicateweedout （默认on）

控制semijoin Duplicate Weedout策略。

• • firstmatch（默认 on）

控制半联接FirstMatch策略。

• • loosescan（默认 on）

控制半连接LooseScan策略（不要与Loose Index Scan for混淆GROUP BY）。

• • semijoin（默认 on）

控制所有半连接策略。

• 、semijoin、 firstmatch和 标志启用对半连接策略的控制loosescan。 duplicateweedout该 semijoin标志控制是否使用半连接。如果设置为 on，则 firstmatch和 loosescan标志可以更好地控制允许的半连接策略。
• 如果duplicateweedout 半连接策略被禁用，除非所有其他适用的策略也被禁用，否则不会使用它。
• Ifsemijoin和 materializationare both on，半连接在适用的情况下也使用物化。这些标志是on默认的。
• 有关详细信息，请参阅第 8.2.2.1 节，“使用半连接转换优化子查询、派生表和视图引用”。
• 子查询实现标志
  • materialization （默认on）

控制物化（包括半连接物化）。

• • subquery_materialization_cost_based （默认on）

使用基于成本的实现选择。

• 该materialization标志控制是否使用子查询实现。If semijoin和 materializationare both on，半连接在适用的情况下也使用物化。这些标志是on默认的。
• 该 subquery_materialization_cost_based 标志可以控制子查询实现和子查询转换之间的 IN选择EXISTS。如果标志是（默认），优化器在子查询实现和子查询转换on之间执行基于成本的选择（ 如果可以使用任何一种方法）。如果标志是，则优化器选择子查询实现而不是 子查询转换。 INEXISTSoffINEXISTS
• 有关更多信息，请参阅 第 8.2.2 节，“优化子查询、派生表和视图引用”。

当您为 分配值时 optimizer_switch，未提及的标志将保留其当前值。这使得可以在单个语句中启用或禁用特定的优化器行为而不影响其他行为。该语句不依赖于存在哪些其他优化器标志以及它们的值是什么。假设启用了所有索引合并优化：

mysql> SELECT @@optimizer_switch\G

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on,

                    index_merge_sort_union=on,

                    index_merge_intersection=on,

                    engine_condition_pushdown=on,

                    index_condition_pushdown=on,

                    mrr=on,mrr_cost_based=on,

                    block_nested_loop=on,batched_key_access=off,

                    materialization=on,semijoin=on,loosescan=on,

                    firstmatch=on,duplicateweedout=on,

                    subquery_materialization_cost_based=on,

                    use_index_extensions=on,

                    condition_fanout_filter=on,derived_merge=on,

                    prefer_ordering_index=on

如果服务器对某些查询使用Index Merge Union或Index Merge Sort-Union访问方法，并且您想检查优化器在没有它们的情况下是否执行得更好，请像这样设置变量值：

mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';

mysql> SELECT @@optimizer_switch\G

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=off,

                    index_merge_sort_union=off,

                    index_merge_intersection=on,

                    engine_condition_pushdown=on,

                    index_condition_pushdown=on,

                    mrr=on,mrr_cost_based=on,

                    block_nested_loop=on,batched_key_access=off,

                    materialization=on,semijoin=on,loosescan=on,

                    firstmatch=on,duplicateweedout=on,

                    subquery_materialization_cost_based=on,

                    use_index_extensions=on,

                    condition_fanout_filter=on,derived_merge=on,

                    prefer_ordering_index=on

8.9.3 优化器提示

8.9.3优化器提示

控制优化器策略的一种方法是设置 optimizer_switch系统变量（参见第 8.9.2 节，“可切换优化”）。对此变量的更改会影响所有后续查询的执行；要对一个查询产生不同的影响，有必要 optimizer_switch在每个查询之前进行更改。

控制优化器的另一种方法是使用优化器提示，它可以在单个语句中指定。因为优化器提示适用于每个语句，所以它们对语句执行计划的控制比使用 optimizer_switch. 例如，您可以在语句中启用对一个表的优化，并禁用对不同表的优化。语句中的提示优先于 optimizer_switch标志。

例子：

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

笔记

默认情况下，mysql客户端会从发送到服务器的SQL语句（包括优化器提示）中删除注释，直到MySQL 5.7.7更改为将优化器提示传递给服务器。如果您将旧版本的mysql客户端与理解优化器提示的服务器版本 一起使用，为了确保优化器提示不会被剥离，请使用 该 选项 调用mysql 。--comments

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

• 优化器提示概述
• 优化器提示语法
• 表级优化器提示
• 索引级优化器提示
• 子查询优化器提示
• 语句执行时间优化器提示
• 命名查询块的优化器提示

优化器提示概述

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

• 全局：提示影响整个语句
• 查询块：提示影响语句中的特定查询块
• 表级：提示影响查询块中的特定表
• 索引级别：提示影响表中的特定索引

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

表8.2可用的优化器提示

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

优化器提示语法

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

/*+ BKA(t1) */

/*+ BNL(t1, t2) */

/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */

/*+ QB_NAME(qb2) */

+ 字符 后允许有空格。

SELECT解析器在、 UPDATE、 INSERT、 REPLACE和 DELETE语句 的初始关键字之后识别优化器提示注释。在这些情况下允许使用提示：

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

DELETE /*+ ... */ ...

• 在查询块的开头：

INSERT ... SELECT /*+ ... */ ...

• 在以.开头的可提示语句中 EXPLAIN。例如：

EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

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

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

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

但这是无效的：

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

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

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

查询块名称是标识符，并遵循关于哪些名称有效以及如何引用它们的通常规则（请参阅 第 9.2 节，“模式对象名称”）。

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

表级优化器提示

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

表级提示的语法：

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:为指定的表启用或禁用BKA。
  • BNL, NO_BNL:启用或禁用指定表的BNL。

笔记

要使用BNL或BKA提示为外连接的任何内表启用连接缓冲，必须为外连接的所有内表启用连接缓冲。

• tbl_name：语句中使用的表的名称。该提示适用于它命名的所有表。如果提示没有命名表，则它适用于出现它的查询块的所有表。

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

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

• query_block_name：提示适用的查询块。如果提示不包含前导 ，则提示适用于它出现的查询块。对于 语法，提示适用于命名查询块中的命名表。要为查询块分配名称，请参阅 命名查询块的优化器提示。 @query_block_nametbl_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 /*+ BNL(t2) */ FROM t1, t2;

如果优化器选择先处理，它会通过在开始读取之前 缓冲行来 t1 应用块嵌套循环连接 。如果优化器选择先处理，则提示无效，因为它是发送者表。 t2t1t2t2t2

索引级优化器提示

索引级提示会影响优化器对特定表或索引使用的索引处理策略。这些提示类型会影响索引条件下推(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:为指定的表或索引启用或禁用MRR。MRR提示仅适用于InnoDB和 MyISAM表。
  • NO_ICP:对指定的表或索引禁用ICP。默认情况下，ICP是一种候选优化策略，因此没有启用它的提示。
  • NO_RANGE_OPTIMIZATION：禁用指定表或索引的索引范围访问。此提示还禁用表或索引的索引合并和松散索引扫描。默认情况下，范围访问是一种候选优化策略，因此没有启用它的提示。

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

• tbl_name：提示适用的表。
• index_name：命名表中索引的名称。该提示适用于它命名的所有索引。如果提示没有指定索引，则它适用于表中的所有索引。

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

• query_block_name：提示适用的查询块。如果提示不包含前导 ，则提示适用于它出现的查询块。对于 语法，提示适用于命名查询块中的命名表。要为查询块分配名称，请参阅 命名查询块的优化器提示。 @query_block_nametbl_name@query_block_name

例子：

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 节，“优化子查询、派生表和视图引用”。

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

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。

例子：

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

该 提示将语句执行超时设置为 毫秒。如果此选项不存在或为0，则应用系统变量 建立的语句超时 。MAX_EXECUTION_TIME(N)NNmax_execution_time

MAX_EXECUTION_TIME提示适用如下 ：

• 对于具有多个SELECT 关键字的语句，例如联合或带有子查询的语句， MAX_EXECUTION_TIME 适用于整个语句，并且必须出现在第一个之后SELECT。
• 它适用于只读 SELECT语句。非只读语句是那些调用存储函数的语句，该函数将修改数据作为副作用。
• 它不适用于SELECT 存储程序中的语句并被忽略。

命名查询块的优化器提示

表级、索引级和子查询优化器提示允许将特定查询块命名为其参数语法的一部分。要创建这些名称，请使用 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 ...)) ...

产生的效果如下：

• MRR(@qb1 t1)适用t1于查询块 中的表qb1。
• BKA(@qb2)适用于查询块qb2。
• NO_MRR(@qb3 t1 idx1, id2)适用 于查询块中的索引idx1 和idx2表中。 t1qb3

查询块名称是标识符，并遵循关于哪些名称有效以及如何引用它们的通常规则（请参阅 第 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") */ ...) ...

8.9.4 索引提示

8.9.4索引提示

索引提示为优化器提供有关如何在查询处理期间选择索引的信息。此处描述的索引提示与第 8.9.3 节“优化器提示”中描述的优化器提示不同 。索引和优化器提示可以单独使用，也可以一起使用。

索引提示仅适用于SELECT andUPDATE语句。

索引提示在表名之后指定。（有关在语句中指定表的一般语法 SELECT，请参阅 第 13.2.9.2 节，“JOIN 子句”。）引用单个表的语法，包括索引提示，如下所示：

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:

    index_hint [index_hint] ...

index_hint:

    USE {INDEX|KEY}

      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])

  | {IGNORE|FORCE} {INDEX|KEY}

      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:

    index_name [, index_name] ...

该提示告诉MySQL仅使用一个命名索引来查找表中的行。替代语法告诉MySQL不要使用某些特定的索引或索引。如果显示MySQL使用了可能索引列表中的错误索引， 这些提示很有用。USE INDEX (index_list)IGNORE INDEX (index_list)EXPLAIN

提示的FORCE INDEX行为类似于，此外假定表扫描 非常昂贵。换句话说，只有在无法使用命名索引之一来查找表中的行时才使用表扫描。 USE INDEX (index_list)

每个提示都需要索引名称，而不是列名称。要引用主键，请使用name PRIMARY。要查看表的索引名称，请使用SHOW INDEX语句或 INFORMATION_SCHEMA.STATISTICS 表。

index_name值不必是完整的索引名称 。它可以是索引名称的明确前缀。如果前缀不明确，则会发生错误。

例子：

SELECT * FROM table1 USE INDEX (col1_index,col2_index)

  WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)

  WHERE col1=1 AND col2=2 AND col3=3;

索引提示的语法具有以下特征：

• 省略 index_listfor在语法上是有效的USE INDEX，这意味着“不使用索引。” 省略orindex_list是 语法错误。 FORCE INDEXIGNORE INDEX
• FOR您可以通过向提示添加子句来 指定索引提示的范围 。这为查询处理的各个阶段提供了对优化器选择执行计划的更细粒度的控制。要仅影响MySQL决定如何在表中查找行以及如何处理连接时使用的索引，请使用FOR JOIN. 要影响对行进行排序或分组的索引使用，请使用FOR ORDER BY或 FOR GROUP BY。
• 您可以指定多个索引提示：

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;

在多个提示中命名相同的索引不是错误（即使在相同的提示中）：

SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);

但是，对于同一个表 ，混合USE INDEX 和 是错误的：FORCE INDEX

SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);

如果索引提示不包含FOR子句，则提示的范围适用于语句的所有部分。例如，这个提示：

IGNORE INDEX (i1)

相当于这种提示组合：

IGNORE INDEX FOR JOIN (i1)

IGNORE INDEX FOR ORDER BY (i1)

IGNORE INDEX FOR GROUP BY (i1)

在MySQL 5.0中，没有FOR子句的提示范围仅适用于行检索。FOR要在不存在子句时使服务器使用此旧行为，请old在服务器启动时启用系统变量。请注意在复制设置中启用此变量。使用基于语句的二进制日志记录，源和副本具有不同的模式可能会导致复制错误。

处理索引提示时，它们按类型（USE, FORCE, IGNORE）和范围（FOR JOIN, FOR ORDER BY, FOR GROUP BY）收集在单个列表中。例如：

SELECT * FROM t1

  USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);

相当于：

SELECT * FROM t1

   USE INDEX (i1,i2) IGNORE INDEX (i2);

然后按以下顺序将索引提示应用于每个范围：

1. {USE|FORCE} INDEX如果存在则应用。（如果不是，则使用优化器确定的索引集。）
2. IGNORE INDEX应用于上一步的结果。例如，以下两个查询是等效的：

SELECT * FROM t1 USE INDEX (i1);

对于FULLTEXT搜索，索引提示的工作方式如下：

• 对于自然语言模式搜索，索引提示会被默默忽略。例如，IGNORE INDEX(i1)在没有警告的情况下被忽略并且索引仍然被使用。
• 对于布尔模式搜索，带有FOR ORDER BY或FOR GROUP BY的索引提示会被忽略。FOR JOIN尊重有或没有FOR修饰符的索引提示。与提示如何应用于非FULLTEXT搜索相反，提示用于查询执行的所有阶段（查找行和检索、分组和排序）。即使为非FULLTEXT索引给出提示也是如此。

例如，以下两个查询是等效的：

SELECT * FROM t