SQL优化【查询器优化·一】

• 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选项，这是默认值 。
 JSON格式以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 EXPLAIN输出格式

该EXPLAIN语句提供有关MySQL如何执行语句的信息。
 EXPLAIN作品有 SELECT， DELETE， INSERT， REPLACE，和 UPDATE语句。


EXPLAIN为SELECT语句中使用的每个表返回一行信息 。
它按照MySQL在处理语句时读取它们的顺序列出输出中的表。
MySQL使用嵌套循环连接方法解析所有连接。
这意味着MySQL从第一个表中读取一行，然后在第二个表，第三个表等中找到匹配的行。
处理完所有表后，MySQL将通过表列表输出所选列和回溯，直到找到有更多匹配行的表。
下一行从该表中读取，并且该过程继续下一个表。


EXPLAIN输出包括分区信息。
另外，对于SELECT 语句，EXPLAIN生成扩展信息，可以按照SHOW WARNINGS以下 方式显示 EXPLAIN（参见 第8.8.3节“扩展EXPLAIN输出格式”）。


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


您不能在相同的语句中一起使用弃用PARTITIONS 和EXTENDED关键字 EXPLAIN。
另外，这些关键字都不能与FORMAT选项一起使用 。


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


EXPLAIN输出列

EXPLAIN加入类型

解释额外信息

EXPLAIN输出解释

EXPLAIN输出列
本部分介绍由产生的输出列 EXPLAIN。
后面几节提供了关于type 和 Extra 列的更多信息 。


每个输出行都EXPLAIN 提供有关一个表的信息。
每行包含表8.1“汇总输出列”中汇总的值 ，并在表格后面详细介绍。
列名显示在表格的第一列中; 第二列提供FORMAT=JSON使用时输出中显示的等效属性名称 。


表8.1 EXPLAIN输出列

柱	JSON名称	含义
id	select_id	该SELECT标识符
select_type	没有	该SELECT类型
table	table_name	输出行的表格
partitions	partitions	匹配的分区
type	access_type	连接类型
possible_keys	possible_keys	可能的索引选择
key	key	该指数实际选择
key_len	key_length	所选键的长度
ref	ref	列与索引进行比较
rows	rows	要检查的行的估计
filtered	filtered	按表条件过滤的行的百分比
Extra	没有	附加信息

注意
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，除非它是 SIMPLE或PRIMARY。
表中还显示了JSON名称（如果适用）。


select_type 值	JSON名称	含义
SIMPLE	没有	简单SELECT（不使用 UNION或子查询）
PRIMARY	没有	最 SELECT
UNION	没有	第二次或以后的SELECT声明 UNION
DEPENDENT UNION	dependent（true）	第二个或更晚的SELECT语句 UNION依赖于外部查询
UNION RESULT	union_result	结果UNION。

SUBQUERY	没有	首先SELECT在子查询中
DEPENDENT SUBQUERY	dependent（true）	首先SELECT在子查询中，依赖于外部查询
DERIVED	没有	派生表
MATERIALIZED	materialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable（false）	无法缓存结果的子查询，必须对外部查询的每一行重新评估
UNCACHEABLE UNION	cacheable（false）	第二个或更高版本选择UNION 属于不可缓存的子查询（请参阅 UNCACHEABLE SUBQUERY）
DEPENDENT通常表示使用相关的子查询。
请参见 第13.2.10.7节“相关子查询”。


DEPENDENT SUBQUERY评估与评估不同UNCACHEABLE SUBQUERY。
因为DEPENDENT SUBQUERY，子查询对于来自外部上下文的变量的每个不同值集合仅重新评估一次。
因为 UNCACHEABLE SUBQUERY子查询是针对外部上下文的每一行重新评估的。


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


当您指定FORMAT=JSON时 EXPLAIN，输出没有直接等价于的单个属性 select_type; 该 query_block属性对应于给定的SELECT。
相当于SELECT刚刚显示的大多数子查询类型的属性都可用（示例 materialized_from_subquery适用 MATERIALIZED），并在适当时显示。
SIMPLE或者没有JSON等价物 PRIMARY。


select_type非SELECT语句 的值显示受影响的表的语句类型。
例如，select_type是 DELETE对 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，len_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列表示将根据表条件过滤的表行的估计百分比。
即，rows 显示检查的估计行数， rows× filtered/ 100显示将与先前表连接的行数。


Extra （JSON名称：无）

此列包含有关MySQL如何解析查询的其他信息。
有关不同值的说明，请参阅 EXPLAIN 附加信息。


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


EXPLAIN加入类型
该type列 EXPLAIN输出介绍如何联接表。
在JSON格式的输出中，这些被作为access_type属性的值查找。
以下列表描述了从最佳类型到最差类型的连接类型：

system

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


const

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


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

SELECT * FROM tbl_nameWHERE primary_key= 1;

SELECT * FROM tbl_name
  WHERE primary_key_part1= 1 AND primary_key_part2= 2;
eq_ref

从这张表中读取一行，用于前面表格的每行组合。
除了 system和 const类型之外，这是最好的连接类型。
它在索引的所有部分被连接使用并且索引是a PRIMARY KEY或UNIQUE NOT NULL索引时使用。


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

选择*从ref_table，在other_table
  哪里ref_table。
key_column= other_table。
column;

选择*从ref_table，在other_table
  哪里ref_table。
key_column_part1= other_table。
column
  和ref_table。
key_column_part2= 1;
ref

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


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

SELECT * FROM ref_tableWHERE key_column= expr;

选择*从ref_table，在other_table
  哪里ref_table。
key_column= other_table。
column;

选择*从ref_table，在other_table
  哪里ref_table。
key_column_part1= other_table。
column
  和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= exprOR key_column是NULL;
请参见第8.2.1.12节“IS NULL优化”。


index_merge

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


unique_subquery

这种类型取代 了以下形式的eq_ref一些 IN子查询：

valueIN（primary_key从single_table哪里选择some_expr）
unique_subquery 只是一个索引查找函数，它可以完全替代子查询以提高效率。


index_subquery

这种连接类型与 unique_subquery。
它取代了IN子查询，但它适用于以下形式的子查询中的非唯一索引：

valueIN（key_column从single_table哪里选择some_expr）
range

只有在给定范围内的行才会被检索，使用索引来选择行。
的key 输出行中的列指示使用哪个索引。
将key_len包含已使用的时间最长的关键部分。
该ref列 NULL适用于此类型。


range当一个键列使用任何的相比于恒定可使用 =， <>， >， >=， <， <=， IS NULL， <=>， BETWEEN，或 IN()运营商：

SELECT * FROM tbl_name
  WHERE key_column= 10;

SELECT * FROM tbl_name
  WHERE key_column10和20之间;

SELECT * FROM tbl_name
  WHERE key_columnIN（10,20,30）;

SELECT * FROM tbl_name
  WHERE key_part1= 10 AND key_part2IN（10,20,30）;
index

该index联接类型是一样的 ALL，只是索引树被扫描。
这发生在两个方面：

如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则只扫描索引树。
在这种情况下，该Extra专栏说 Using index。
仅索引扫描通常比ALL由于索引大小通常小于表数据而更快 。


使用索引中的读取来执行全表扫描，以按索引顺序查找数据行。
 Uses index没有出现在 Extra列中。


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


ALL

全表扫描是针对先前表中的每一行组合完成的。
如果表格是没有标记的第一个表格const，通常情况下并不好 ，而在其他所有情况下通常 都很糟糕。
通常情况下，您可以ALL通过添加索引来避免 这些索引，这些索引可以基于来自较早表的常量值或列值从表中检索行。


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


如果您希望尽可能快地查询查询，请查找和的Extra值列，或者在JSON格式的输出中查找for 和 等于的属性 。
 Using filesortUsing temporaryEXPLAINusing_filesortusing_temporary_tabletrue

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

该表被引用为table可以下推到NDB内核的连接中的子 节点。
仅在启用了下推连接时才适用于NDB群集。
有关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）

半连接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（和 system）表，并注意到该WHERE子句总是错误的。


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 的SELECT声明。


No tables used（JSON属性： message）

该查询没有FROM子句，或者有一个 FROM DUAL子句。


对于INSERT或 REPLACE语句， EXPLAIN当没有SELECT 部分时显示此值。
例如，它似乎是EXPLAIN INSERT INTO t VALUES(10)因为这相当于 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检查是否可以使用range或 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）

这表示半连接Duplicate Weedout策略的临时表使用情况。


unique row not found（JSON属性： message）

对于像这样的查询，没有行满足 索引条件或表上的条件。
 SELECT ... FROM tbl_nameUNIQUEPRIMARY KEY

Using filesort（JSON属性： using_filesort）

MySQL必须执行额外的传递以了解如何按排序顺序检索行。
排序是按照连接类型遍历所有行并存储排序键和指向与该WHERE子句匹配的所有行的行的指针。
然后对键进行排序，并按排序顺序检索行。
请参见 第8.2.1.13节“按优化排序”。


Using index（JSON属性： using_index）

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


对于InnoDB具有用户定义的聚簇索引的表格，即使列中Using index不存在， 也可以使用该索引Extra。
如果type是 index和 key是，就是这种情况 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找到了一个索引，可用于检索某个GROUP BY或 某个DISTINCT查询的所有列，而无需对实际表进行任何额外的磁盘访问。
此外，索引以最有效的方式使用，因此对于每个组，只有少数索引条目被读取。
有关详细信息，请参见 第8.2.1.14节“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。


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且表连接类型为ALL或 ，则 查询中可能有问题index。


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


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

此产品适用于NDB 表只。
这意味着NDB集群正在使用条件下推优化来提高非索引列和常量之间的直接比较效率。
在这种情况下，条件被“ 推下 ”到集群的数据节点，并在所有数据节点上同时进行评估。
这消除了通过网络发送不匹配的行的需要，并且可以在情况下推可能但未被使用的情况下将这些查询加速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.CurrentDPerson，
               tt.RecordVolume，tt.DPPrinted，et.COUNTRY，
               et_1.COUNTRY，do.CUSTNAME
        FROM tt，et，et et al，do
        WHERE tt.SubmitTime是NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;
对于这个例子，做出以下假设：

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


表	柱	数据类型
tt	ActualPC	CHAR(10)
tt	AssignedPC	CHAR(10)
tt	ClientID	CHAR(10)
et	EMPLOYID	CHAR(15)
do	CUSTNMBR	CHAR(15)
这些表具有以下索引。


表	指数
tt	ActualPC
tt	AssignedPC
tt	ClientID
et	EMPLOYID （首要的关键）
do	CUSTNMBR （首要的关键）
该tt.ActualPC值不是均匀分布的。


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

表类型possible_keys键key_len参考行额外
et ALL PRIMARY NULL NULL NULL 74
做所有主要的空NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
t ALL ALL AssignedPC，NULL NULL NULL 3872
           客户端ID，
           ActualPC的
      范围为每条记录检查（索引映射：0x23）
因为type是 ALL为每个表，这个输出表明MySQL正在生成的所有表的笛卡儿积; 也就是每行的组合。
这需要相当长的时间，因为必须检查每个表中行数的乘积。
对于手头的情况，该产品为74×2135×74×3872 = 45,268,558,720行。
如果桌子更大，你只能想象需要多长时间。


这里的一个问题是，如果MySQL声明为相同的类型和大小，MySQL可以更有效地在列上使用索引。
在这种情况下，VARCHAR与 CHAR被认为是相同的，如果它们被声明为相同的大小。
 tt.ActualPC被声明为 CHAR(10)和et.EMPLOYID 是CHAR(15)，所以有一个长度不匹配。


以固定柱长度上的不同，使用 ALTER TABLE加长 ActualPC从10个字符到15个字符：

MySQL的> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
现在tt.ActualPC， et.EMPLOYID都是 VARCHAR(15)。
EXPLAIN再次执行该 语句会产生以下结果：

表类型possible_keys键key_len参考行额外
tt ALL AssignedPC，NULL NULL NULL 3872使用
             ClientID，其中
             ActualPC的
做所有主要的空NULL NULL 2135
      每个记录检查范围（索引图：0x1）
et_1 ALL PRIMARY NULL NULL NULL 74
      每个记录检查范围（索引图：0x1）
et eq_ref主要小计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生成如下所示的输出：

表类型possible_keys键key_len参考行额外
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC，ActualPC 15 et.EMPLOYID 52使用
             ClientID，其中
             ActualPC的
et_1 eq_ref初级小学15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
在这一点上，查询几乎尽可能地被优化。
剩下的问题是，默认情况下，MySQL假定tt.ActualPC 列中的值是均匀分布的，而tt表不是这种情况。
幸运的是，很容易告诉MySQL分析密钥分发：

MySQL的> ANALYZE TABLE tt;
通过额外的索引信息，连接是完美的，并 EXPLAIN产生这样的结果：

表类型possible_keys键key_len参考行额外
tt ALL AssignedPC NULL NULL NULL 3872使用
             ClientID，其中
             ActualPC的
et eq_ref主要小计15 tt.ActualPC 1
et_1 eq_ref初级小学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输出格式

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


可以通过SHOW WARNINGS以下语句 显示的扩展信息 EXPLAIN仅用于 SELECT语句。
 SHOW WARNINGS显示其他可解释语句（一个空的结果DELETE， INSERT， REPLACE，和 UPDATE）。


注意
在较旧的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
        表：t1
         键入：index
possible_keys：NULL
          键：主键
      key_len：4
          ref：NULL
         行数：4
     过滤：100.00
        额外：使用索引
*************************** 2. row ******************** *******
           ID：2
  select_type：SUBQUERY
        表格：t2
         键入：index
possible_keys：a
          关键：a
      key_len：5
          ref：NULL
         行数：3
     过滤：100.00
        额外：使用索引
设置2行，1警告（0.00秒）

MySQL的> SHOW WARNINGS\G
*************************** 1. row ******************** *******
  级别：注意
   代码：1003
消息：/ * select＃1 * / select`test`.`t1`.`a` as`a`，
         <in_optimizer>（`test`.`t1`.`a``，`test`.``t1`.`a` in
         （<materialize>（/ *选择＃2 * /选择`test`.`t2`.`a`
         from`test`.`t2` where 1 has 1），
         <primary_index_lookup>（`test`.```t1`.`a` in
         <auto_key>上的<临时表>
         where（（'test`.`t1`.`a` =`materialized-subquery`.`a`）））））AS`t1.a
         IN（SELECT t2.a FROM t2）`from`test`.`t1`
一排（0.00秒）
由于显示的语句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 或system类型时，涉及这些表中的列的表达式由优化器提前评估，而不是显示的语句的一部分。
但是，FORMAT=JSON有些 const表访问显示为ref使用const值的访问。

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

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

EXPLAIN [ options]连接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 |
+ ----------------- +
一排（0.00秒）

MySQL的> EXPLAIN FOR CONNECTION 373;
错误1889（HY000）：EXPLAIN FOR CONNECTION命令受支持
仅适用于SELECT / UPDATE / INSERT / DELETE / REPLACE
该Com_explain_other状态变量表示的数 EXPLAIN FOR CONNECTION执行的语句。

• 8.8.5估计查询性能

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


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


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


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


上述讨论并不意味着您的应用程序性能会通过日志缓慢退化 N。
只要所有东西都被操作系统或MySQL服务器缓存，随着表变大，事情变得稍微慢一些。
数据变得太大而无法缓存后，事情开始变得缓慢，直到您的应用程序仅受磁盘查找（日志增加N）的约束 。
为避免这种情况，请在数据增长时增加密钥缓存大小。
对于MyISAM 表，键缓存大小由key_buffer_size系统变量控制 。
请参见第5.1.1节“配置服务器”。