「EXPLAIN | DESCRIBE」

  CREATED BY JENKINSBOT

内容:介绍MySQL中的EXPLAIN和DESCRIBE

事实上,DESCRIBE和EXPLAIN是同义词。但是,DESCRIBE通常用于表结构信息,而EXPLAIN用户获取“查询执行计划”(即MySQL如何执行查询)。
所以,本文内容分为两部分:一部分介绍”如何获取表结构信息”;另外一部分介绍”如何获取执行计划信息”;

EXPLAIN功能有限,并不会总说出真相。但可以很好的解释查询是如何执行的。

相关引用内容:

DESCRIBE和EXPLAIN语法

鉴于二者的角色不同,所以将其区分开解释,但是要记住他们两个是可以互相替换的。下面是语法格式:

-- DESC

{DESCRIBE | DESC}
    tbl_name [col_name | wild]

-- EXPLAIN

{EXPLAIN}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

DESCRIBE:如何获取表结构信息

DESCRIBE主要用于显示TABLE的信息,看如下Demo:

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

DESCRIBE也可以用于显示VIEW的信息。

默认情况下,DESCRIBE显示TABLE中所有列的信息。
如果给出col_name(列名)则仅显示指定列的信息。
wild是匹配字符串,它可以包含SQL中的`%’和`_’通配符。如果给出wild,则仅显示wild的列名。如果wild中不包含空格或其他特殊字符,wild就不需要使用双引号引起来。

DESCRIBE实际是SHOW COLUMNS的简写。SHOW COLUMNS提供了更多关于列的信息。关于SHOW COLUMN的内容,查看: 《SHOW语法》

EXPLAIN:如何获取执行计划

EXPLAIN提供了有关如何执行语句的信息:

  • EXPLAIN只对SELECT, DELETE, INSERT, REPLACE, UPDATE有效。
  • 当EXPLAIN解释SQL的时候,MySQL会显示从优化器获取的语句执行计划。执行计划解释了MySQL如何处理语句,以及表是如何连接的以及以什么顺序。
  • 如果EXPLAIN和FOR CONNECTION connection_id一起使用,将显示在命名连接中执行的语句的执行计划。获取命名连接的执行计划: https://dev.mysql.com/doc/refman/5.7/en/explain-for-connection.html
  • 对于SELECT语句,EXPLAIN还给出了附加的执行计划信息。注意:在旧版本的MySQL中,附加信息的显示需要使用EXPLAIN EXTENDED,但是新版本中的EXTENDED是为了兼容,以后会删除这个关键字。关于扩展的EXPLAIN的输出格式:https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html
  • EXPLAIN也可用于检查涉及分区表的查询。注意:和EXTENDED一样,也是为了兼容,以后会删除这个关键字。关于“获取分区信息”查看:https://dev.mysql.com/doc/refman/5.7/en/partitioning-info.html
  • FORMAT选项用于指定输出格式。format_name的取值:TRADITIONAL,以表格格式输出(默认格式);JOSN,以JOSN格式输出。
  • MySQL5.6之前,如果要解释的语句中包含子查询,那么会执行这个子查询,然后完成对外层查询优化。MySQL5.6之后取消了这个限制。

“优化器跟踪”有时可以提供与EXPLAIN的信息互补的信息。但是,“优化器跟踪”格式和内容可能会在版本之间发生更改。详情查看:https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

如果你相信某个问题是由于索引没有使用而产生的,那么运行ANALYZE TABLE来更新表统计信息(例如键的基数),这能影响优化器所做的选择。详情查看:https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html

EXPLAIN的一些局限性

EXPLAIN中不会显示存储过程、触发器、UDF对查询的影响。
不支持对存储过程的分析。
查询计划的信息也不够全面。
查询执行中的特定优化也不会显示出来。
也不会区分具有相同名字的事物。如:filesort可能表示临时文件或者内存排序。Using temporary可能是内存临时表,也可能是磁盘临时表。

EXPLAIN的输出格式

EXPLAIN返回SELECT中使用的每张表的信息。输出的表的顺序,按照MySQL读取的顺序显示出来。MySQL使用“嵌套循环连接“(nested-loop join)方法连接所有的表,意思是说:MySQL从第一张表中读取一行,然后依次在第二张表中查找匹配的行,第三张……,依次类推。当所有表处理完之后,MySQL输出选择的行并通过表列表回溯,直到找到有更多匹配行的表。从该表中读取下一行,并且继续下一个表。

EXPLAIN的输出包括分区信息。此外,对于SELECT语句,EXPLAIN会生成扩展信息,可以在EXPLAIN之后,使用SHOW WARNINGS显示,详细内容查看:https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html

EXPLAIN输出的列

输出格式又两种,但是这两种的格式输出的”列名”有所不同,所以在下面中开头为TRADITIONAL格式的”列名”,而括号中JOSN name为JSON格式的”列名”。

以下是对输出列的详细说明:

id (JSON name: select_id)

SELECT的标识符,查询中SELECT的顺序号。

如果该行引用其他行的UNION结果,则该值可以为NULL。在这种情况下,显示的值类似于<union
M,N>,表示该行指的是ID值为M和N的行的并集。

select_type (JSON name: none)

SELECT的类型。JSON格式的EXPLAIN将SELECT类型显示为query_block的属性,除非是SIMPLE或PRIMARY。

SELECT的类型值见下表:

  • SIMPLE(JSON name: None):简单SELECT,未使用UNION或子查询。
  • PRIMARY(JSON name: None):如果查询包含了复杂的子部分,则PRIMARY标识的是最外层的SELECT。
  • UNION(JSON name: None):表示在UNION中第二或之后SELECT。
  • DEPENDENT UNION(JSON name: dependent (true)):在UNION中第二或之后SELECT,依赖于外层查询。
  • UNION RESULT(JSON name: union_result):UNION的结果。UNION结果会输出到一个临时表中,然后MySQL将临时表中的数据读出来。
  • SUBQUERY(JSON name: None):子查询中的第一个SELECT。
  • DEPENDENT SUBQUERY(JSON name: dependent (true)):在子查询中第一个SELECT,依赖于外层查询。
  • DERIVED(JSON name: None):表示是FROM中的子查询。MySQL将查询结果会放在一个临时表中,这个表称为“派生表”。
  • MATERIALIZED(JSON name: materialized_from_subquery):物化子查询。
  • UNCACHEABLE SUBQUERY(JSON name: cacheable (false)):不能缓存结果的子查询,并且为外层查询的每一行进行重新评估。比如使用了RNAD()。注意这里说的缓存并不是查询缓存。
  • UNCACHEABLE UNION(JSON name: cacheable (false)):UNION中第二个或之后的查询,属于不可缓存的子查询。

DEPENDENT通常意味着使用相关子查询的。
https://dev.mysql.com/doc/refman/5.7/en/correlated-subqueries.html

DEPENDENT SUBQUERY评估与UNCACHEABLE SUBQUERY评估不同。对于DEPENDENT SUBQUERY,对于从其外部上下文的变量的每组不同值,子查询仅被重新评估一次。对于UNCACHEABLE SUBQUERY,为外层查询的每一行,子查询重新评估。

当使用EXPLAIN指定FORMAT=JSON时,输出没有直接等同于select_type的单个属性;query_block属性对应于给定的SELECT;属性等同于刚才显示的大多数SELECT子查询类型(比如:MATERIALIZED的materialized_from_subquery for),并在适当时显示。对于SIMPLE或PRIMARY,没有JSON等价物。

非SELECT语句的select_type值显示“受影响的表”的语句类型。例如,对于DELETE语句,select_type为DELETE。

table (JSON name: table_name)

被访问的表的表名(或者别名)。可能值如下:

partitions (JSON name: partitions)

查询匹配的记录所在的分区。未分区的表显示NULL。
https://dev.mysql.com/doc/refman/5.7/en/partitioning-info.html

type (JSON name: access_type)

type的值表示了MySQL连接表的方式,准确的说是MySQL如何查找表中的行。

关于type的相关解释,请看下面的
“EXPLAIN的Join类型”

possible_keys (JSON name: possible_keys)

MySQL在表中可以使用的索引。此列完全独立于EXPLAIN输出中显示的表的顺序。这表示在实际中,possible_key中的某些键可能无法在生成的表顺序中使用。

如果此列为NULL(或在JSON格式的输出中未定义),则不存在相关索引。在这种情况下,可以通过检查WHERE子句来检查是否引用适用于索引的某些列或列来提高查询的性能。如果是,请创建一个适当的索引并再次使用EXPLAIN检查查询。
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

SHOW INDEX FROM tbl_name用于查看表信息。

key (JSON name: key)

key表示MySQL实际决定使用的索引。如果MySQL决定使用possible_keys的其中一个索引来查找行,则该索引将做为key值列出。

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

对于InnoDB,即使查询也SELECT了主键,辅助索引也可能覆盖所选列,因为InnoDB将主键值与每个辅助索引存储。如果key为NULL,则MySQL找不到更有效地执行查询的索引。

要强制MySQL使用或忽略在possible_keys中列出的索引,请在查询中使用FORCE INDEX,USE INDEX或IGNORE INDEX。
https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

对于MyISAM表,运行ANALYZE TABLE有助于优化器选择更好的索引。使用myisamchk -analyze也可以。


https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html


https://dev.mysql.com/doc/refman/5.7/en/myisam-table-maintenance.html

key_len (JSON name: key_length)

key_len列表示MySQL决定使用的key的长度。 key_len的值能够确定MySQL实际使用的多部分key的多少部分。如果key为NULL,则key_len列也表示为NULL。由于key存储格式,对于可能为空的列而言,对于NOT NULL列的关键长度要大一个。

ref (JSON name: ref):

显示哪个列或常量用于和key中命名的索引比较,以在表中用于查询行。

如果值为func,则使用的值是某些函数的结果。要查看是哪个函数,在EXPLAIN之后使用SHOW WARNINGS来查看扩展的EXPLAIN输出。该函数实际上可能是算术运算符之类的运算符。

rows (JSON name: rows)

MySQL认为在执行查询时必须检查的行数量。在InnoDB中这个一个估计值。

filtered (JSON name: filtered)

表示将由表条件过滤的表行的预估百分比。也就是说,rows显示了所检查的行数,并且rows×filtered/100表示​​将与先前的表join的行数。

Extra (JSON name: none)

MySQL解析查询的附加信息。

没有单独的JSON属性对应于Extra列;但是,此列中可能发生的值将显示为JSON属性,或作为message属性的文本。

有关EXPLAIN的Extra信息,查看下面的
“EXPLAIN的附加信息”

EXPLAIN的Join类型

JOIN类型有如下几种:

system

该表只有一行(= system table)。这是const连接类型的特殊情况。

const

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

将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时,将使用const类型。在以下查询中,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

为前面表中的每一行的组合,从当前表中读取一行。除了system和const类型之外,这是最好的连接类型。当连接使用索引的所有部分时使用该索引,并且索引是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

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

ref可用于使用=或<=>运算符进行比较的索引列。在下面的例子中,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;

index_merge

此连接类型表示使用
索引合并优化。在这种情况下,key列包含了使用的索引列表,key_len包含所使用索引的最长关键部分的列表。

unique_subquery

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

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包含使用的最长的key部分。此类型的ref列为NULL。

当使用任何一个=,<>,>,> =,<,<=,IS NULL,<=>,BETWEEN或IN运算符将key列与常量进行比较时,使用range类型。如下:

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

与ALL一样,只是MySQL扫描表的时候按照索引的次序进行。有两种方式会导致索引树被扫描发生:

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

当查询仅使用作为单个索引的一部分的列时,MySQL可以使用这种JOIN类型。

ALL

全表扫描。这通常不好,如果表是第一个没有标记为const的表,通常在所有其他情况下都非常糟糕。通常,可以根据早期表中的常量值或列值添加从表中启用行检索的索引来避免ALL。

EXPLAIN的附加信息

Extra中显示的是MySQL解析查询的附加信息。

如果你希望你的查询尽可能的块一些,那你要留意Extra中是否使用了Using filesort、Using temporary,或者JOSN格式中的using_filesort、using_temporary_table是否为true。

Child of ‘table’ pushed join@1 (JSON: message text)

该表被引用为可以向下推送到NDB内核的联接中的表的子对象。当下拉式连接启用时,仅适用于NDB群集。有关更多信息和示例,请参阅ndb_join_pushdown服务器系统变量的说明。
https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-options-variables.html#sysvar_ndb_join_pushdown

const row not found (JSON property: const_row_not_found)

对于诸如SELECT … FROM tbl_name的查询,该表为空。

Deleting all rows (JSON property: message)

对于DELETE,一些存储引擎(如MyISAM)支持一种处理方法,可以简单而快速地删除所有表行。如果引擎使用此优化,则会显示此值。

Distinct (JSON property: distinct)

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

FirstMatch(tbl_name) (JSON property: first_match)

为tal_name使用“半连接FirstMatch”连接快捷策略

Full scan on NULL key (JSON property: message)

当优化程序不能使用索引查找访问方法时,这会做为子查询优化作为后备策略发生。

Impossible HAVING (JSON property: message)

HAVING子句始终为false,不能select任何行。

Impossible WHERE (JSON property: message)

WHERE子句始终为false,不能select任何行。

Impossible WHERE noticed after reading const tables (JSON property: message)

MySQL读取了所有的const(和system)表,并发现WHERE子句总是为false。

LooseScan(m..n) (JSON property: message)

使用半连接LooseScan策略。 m和n是关键部分号。

No matching min/max row (JSON property: message)

没有行满足SELECT MIN(…)FROM … WHERE condition的查询条件。

no matching row in const table (JSON property: message)

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

No matching rows after partition pruning (JSON property: message)

对于DELETE或UPDATE,优化器在分区修剪后没有发现任何删除或更新。对于SELECT语句,它与Impossible WHERE的含义相似。

No tables used (JSON property: 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 property: 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,并使用t1.id的值查找t2中的行。如果MySQL在t2中找到一个匹配的行,它会知道t2.id永远不会为NULL,并且不扫描t2中具有相同id值的其余行。换句话说,对于t1中的每一行,MySQL只需要在t2中只执行一次查找,而不考虑在t2中实际匹配的行数。

Plan isn’t ready yet (JSON property: none)

当优化器尚未完成为命名连接中执行的语句创建执行计划时,此值与EXPLAIN FOR CONNECTION一起出现。如果执行计划输出包含多行,则根据优化程序在确定完整执行计划时的进度,它们中的任何一个或全部可以具有此Extra值。

Range checked for each record (index map: N) (JSON property: message)

MySQL发现没有使用好的索引,但是发现在前面的表的列值已知之后,可能会使用一些索引。对于上表中的每一行组合,MySQL检查是否可以使用range或index_merge访问方法来检索行。这不是很快,但是比执行没有索引的连接更快。

索引的编号从1开始,按照与表的SHOW INDEX所示相同的顺序。索引映射值N是指示哪些索引是候选的位掩码值。例如,0x19(二进制11001)的值意味着将考虑索引1,4和5。

Scanned N databases (JSON property: message)

这表示在处理对INFORMATION_SCHEMA表的查询时服务器执行多少个目录扫描。N的值可以为0、1、all。


https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html

Select tables optimized away (JSON property: message)

优化器确定:
1)最多应返回一行。

2)要生成此行,必须读取一组确定性的行。当在优化阶段(例如通过读取索引行)可以读取要读取的行时,在查询执行期间不需要读取任何表。

当查询被隐式分组(包含聚合函数,但不包含GROUP BY子句)时,满足第一个条件。当使用每个索引执行一行查找时,满足第二个条件。读取的索引数量决定要读取的行数。

考虑以下隐含分组查询:

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

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

如果要读取的行不是确定性的,则不会发生此额外值。考虑这个查询:

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)的存储引擎,对于缺少WHERE子句或始终为true并且没有GROUP BY子句的COUNT(*)查询,可能会出现此Extra值。 (这是一个隐式分组的查询的实例,存储引擎影响是否可以读取确定性行数。)

Skip_open_table, Open_frm_only, Open_full_table (JSON property: message)

适用于INFORMATION_SCHEMA表查询的“文件打开优化”。


https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html

Skip_open_table:表格文件不需要打开。信息已经通过扫描数据库目录在查询中可用。

Open_frm_only:只需要打开表的.frm文件。

Open_full_table:未优化的信息查找。必须打开.frm,.MYD和.MYI文件。

Start temporary, End temporary (JSON property: message)

表示对半连接重复Weedout策略使用临时表。

unique row not found (JSON property: message)

对于诸如SELECT … FROM tbl_name的查询,没有行满足表上的UNIQUE索引或PRIMARY KEY的条件。

Using filesort (JSON property: using_filesort)

MySQL必须做一个额外的通行,以了解如何按排序顺序检索行。排序通过根据连接类型遍历所有行,并将排序键和指针存储到与WHERE子句匹配的所有行的行。然后,键被排序,并按排序顺序检索行。


https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

Using index (JSON property: using_index)

表示使用了
覆盖索引(索引中包含了所有需要查询的字段的值)。当查询仅使用作为单个索引的一部分的列时,可以使用此策略。

对于具有用户定义的聚簇索引的InnoDB表,即使Extra中没有显示Using index,也可以使用覆盖索引。如果type是index,而key是PRIMARY,说的就是这种情况。

Using index condition (JSON property: using_index_condition)

通过访问索引元组并首先测试它们来读取表以确定是否读取完整的表行。以这种方式,索引信息用于延迟(“下推”)读取完整的表行,除非有必要。


https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html

Using index for group-by (JSON property: using_index_for_group_by)

与Using index表访问方法类似,group-by的Using index表示MySQL发现了一个索引,可用于检索GROUP BY或DISTINCT查询的所有列,而不需要对实际表进行任何额外的磁盘访问。另外,以最有效的方式使用索引,因此对于每个组,只读取少数索引条目。
https://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access) (JSON property: using_join_buffer)

来自早期连接的表将部分读入连接缓冲区,然后从缓冲区中使用它们的行来执行与当前表的连接。 (块嵌套循环)表示使用块嵌套循环算法,(批量key访问)表示使用批量key访问算法。也就是说,来自EXPLAIN输出的前一行的表中的键将被缓冲,并且匹配的行将从使用连接缓冲区出现的行表示的表中批量获取。

在JSON格式的输出中,using_join_buffer的值始终是块嵌套循环或批量key访问中的一个。

Using MRR (JSON property: message)

使用多范围读取优化策略读取表格。
https://dev.mysql.com/doc/refman/5.7/en/mrr-optimization.html

Using sort_union(…), Using union(…), Using intersect(…) (JSON property: message)

这些表示特定的算法,显示如何为index_merge连接类型合并索引扫描。


https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

Using temporary (JSON property: using_temporary_table)

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

Using where (JSON property: attached_condition)

WHERE子句用于限制哪些行与下一个表匹配或发送到客户端。

除非想检查或者获取所有的行,否则的话,如果Extra的值不是Using where并且连接类型为ALL或index的话,在查询中可能存在某些问题。

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

Using where with pushed condition (JSON property: message)

此项目仅适用于NDB表。这意味着NDB群集正在使用条件下推优化来提高非索引列和常量之间直接比较的效率。在这种情况下,条件被“推下”到集群的数据节点,并在所有数据节点上同时进行评估。这样就不需要通过网络发送不匹配的行,并且可以将这种查询加速到5到10倍的情况下,这种情况下可以但不使用条件下推。

Zero limit (JSON property: message)

该查询具有LIMIT 0子句,无法选择任何行。

EXPLAIN的输出解释

通过使用EXPLAIN输出的行列中的值的乘积,可以很好地表明连接的好坏。这应该大致告诉你MySQL必须检查多少行才能执行查询。如果使用max_join_size系统变量限制查询,则此产生的行还用于确定要执行的多表SELECT语句以及要中止哪一个。https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html

以下示例显示了如何根据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;

对于此示例,进行以下假设:

被比较的列的声明如下:


Table Column Data Type

tt ActualPC CHAR(10)

tt AssignedPC CHAR(10)

tt ClientID CHAR(10)

et EMPLOYID CHAR(15)

do CUSTNMBR CHAR(15)

这些表具有以下索引:


Table Index

tt ActualPC

tt AssignedPC

tt ClientID

et EMPLOYID (primary key)

do CUSTNMBR (primary key)

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)

因为每个表的类型都是ALL,所以这个输出表明MySQL正在生成所有表的笛卡儿乘积;也就是每行的组合。这需要相当长的时间,因为必须检查每个表中的行数的乘积。就目前情况而言,该产品为74×2135×74×3872 = 45,268,558,720行。如果table较大,您只能想象需要多长时间。

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

要修复列长度之间的差异,请使用ALTER TABLE将ActualPC从10个字符延长到15个字符:

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

这不是完美的,但是更好:行值的乘积小于74。这个版本在几秒钟内执行。

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

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来分析key分发:

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

来自EXPLAIN的输出中的rows列是MySQL连接优化器的一个受过教育的猜测。通过将行乘积与查询返回的实际行数进行比较,检查数字是否接近真实。如果数字完全不同,您可以通过在SELECT语句中使用STRAIGHT_JOIN来获得更好的性能,并尝试在FROM子句中以不同的顺序列出表。 (但是,STRAIGHT_JOIN可能会阻止使用索引,因为它禁用半连接转换。https://dev.mysql.com/doc/refman/5.7/en/semi-joins.html

在某些情况下,可以执行EXPLAIN SELECT与子查询一起修改数据的语句;https://dev.mysql.com/doc/refman/5.7/en/derived-tables.html