携手创造，共同生长！这是我参加「日新计划 8 月更文应战」的第20天，点击查看活动概况

详细介绍了MySQL EXPLAIN履行计划的各个字段的含义以及运用办法。

调用EXPLAIN能够获取关于查询履行计划的信息，以及怎么解说输出。EXPLAIN指令是查看查询优化器怎么决议履行查询的首要办法，但该动能也有局限性，它的挑选并不总是最优的，展现的也并不一定是真相。

1 调用EXPLAIN

要运用EXPLAIN，只需求在SELECT 关键字之前添加 EXPLAIN这个词。MySQL会在查询上设置一个符号。当履行查询时，这个符号会使其回来关于在履行计划中每一步的信息，而不是真实完全的履行该句子。

它会回来一行或多行信息，显现出履行计划中的每一部分和履行的次第。在查询中，每个表的输出只有一行，若多表相关，则输出多行。别号表单算为一个表，因而假如把表和自己衔接，输出中也会有两行。这儿的表的界说十分的广：能够是一个子查询，一个 UNION 成果。

EXPLAIN有两个变种：

  1. EXPLAIN EXTENDED：看起来和正常的EXPLAIN行为相同，但他会告知服务器“逆向编译”履行计划为一个 SELECT 句子（SHOW WARNINGS 后能看到），该指令在MySQL5.0之后可用，MySQL5.1开端还额定添加一个 filtered 列。
  2. EXPLAIN PARTITIONS：假如查询根据分区表的话，将显现查询将拜访的分区。MySQL5.1以及更新的版本支持。

添加了EXPLAIN之后，MySQL或许仍然会履行部分查询，假如查询中FROM字句中包括子查询，那么MySQL实际会履行子查询的，并将其成果放在一个暂时表中，然后完结外层查询优化。

EXPLAIN 回来的仅仅个近似成果，并且还有相关是的约束：

  1. 不会告知你知道触发器、存储进程或 UDF 怎么影响查询。
  2. 不支持存储进程，虽然能够独自抽取查询进行 EXPLAIN。
  3. 不会告知你查询履行中所做的特定优化。
  4. 不会显现关于查询的履行计划的一切信息。
  5. 无法区分具有相同名字的事物，例如，它对内存排序和暂时文件排序都运用“filesort”，并且对磁盘上和内存中的暂时表都显现“Using temporary”。
  6. 或许会误导，例如：或许会对一个很小的limit查询显现全索引扫描。
  7. 只能解说select查询（5.6以后答应解说非select句子），不会对存储进程调用和INSERT、DELETE、UPDATE或其他句子做解说，但可经过重写某些非 SELECT 查询以运用 EXPLAIN。

2 EXPLAIN中的列

2.1 id

一个编号，表明select所属的行。假如查询中没有子查询或相关查询，那么只会有仅有的SELECT，每一行的该列中都将显现一个1，否则，内层的SELECT句子一般会次第编号，对应于其在原始句子中的方位。id越大履行优先级越高，id相同则认为是一组，从上往下履行，id为NULL最终履行。

例如UNION查询中最终关于暂时表的查询，它的id就为null，由于暂时表并不在原sql中呈现。

EXPLAIN select * from contacts where contact_id <1000
UNION 
select * from contacts where contact_id >99000

MySQL EXPLAIN履行计划详解

2.2 select_type

表明对应行是简略仍是杂乱的查询。

  1. SIMPLE，简略SELECT，查询不包括UNION和子查询。
  2. PRIMARY，查询中若查询包括任何杂乱的子部分，最外层的select被符号为PRIMARY。其他部分符号如下：
    1. SUBQUERY，包括在SELECT子句（不在from子句中）中的子查询的SELECT，成果不依靠于外部查询。
    2. DERIVED，包括在from子句中的子查询中的SELECT。MySQL会递归履行并将成果存放在一个暂时表中，也称为派生表，由于该暂时表是从子查询中派生来的。
    3. UNION，UNION中的第二个或后边的SELECT。第一个SELECT被符号就好像它以部分外查询来履行，因而第一个SELECT或许显现为PRIMARY。假如UNION被FROM字句中的子查询包括，那么它的第一个SELECT被符号为DERIVED。
    4. UNION RESULT，用来从UNION的匿名暂时表中检索成果的SELECT。

除了上面这些，SUBQUERY和UNION还能够被符号为DEPENDENT和UNCACHEABLE，DEPENDENT意味着SELECT 依靠与外层查询中发现的数据；UNCACHEABLE意味着SELECT 中的某些特性阻止成果被缓存于一个 Item_cache 中。

EXPLAIN select * from contacts where contact_id =99000

MySQL EXPLAIN履行计划详解

2.3 table

显现了EXPLAIN对应行正在拜访哪个表。通常情况下，它相当表明了：那就是那个表，或许该表的别号。

能够经过该列从上到下观察MySQL的相关优化器为查询挑选的相关次第。

当from字句中有子查询的时分，table列是<derivedN>的方法，N指向子查询id，这儿N总是指向EXPLAIN输出成果中的后边的一行。

当有UNION时，UNION RESULT的table列包括一个参加UNION的id列表，UNION RESULT总是呈现在UNION中一切参加行之后，例如<union 1,2>。

2.4 type

相关类型，或许说拜访类型，该字段表明MySQL决议怎么查找表中的行。

常用的拜访类型如下（功能顺次从最差到最优）：

  1. ALL：全表扫描，从头到尾的查找所需求的行。但仍然存在破例，例如运用了 LIMIT ，或许Extra 列中显现 “Using distinct/not exists”。
  2. index：跟全表扫描相同，仅仅MySQL扫描表时依照索引次第进行而不是行，首要优点是避免了排序；缺点是要承当按索引次第读取整个表的开支。这通常意味着照实依照随机次第拜访行，开支较大。假如Extra 列中显现 “using index”，说明MySQL正在运用掩盖索引，这样就不需求按索引次第拜访每一行数据，开小会少很多。
  3. range：规模扫描，就是一个有约束的索引扫描，运用一个索引来检索给定规模的行，不需求遍历全部索引。规模扫描通常呈现在between,>,<,>=等操作中。in()和OR也会显现规模扫描，但这两者其实是不同的拜访类型，功能上也有差异。此类查找的开支根ref索引拜访的开支相当。
  4. ref：索引拜访，也叫索引查找。回来一切匹配某个单个值的行，然而它或许会找到契合条件的多个行。此类索引拜访只有当运用非仅有性索引或许仅有索引的非仅有性前缀时才会产生。把他叫ref是由于他要和某个参考值相比较。这个参考值或许是一个常数，或许来自多表查询前一个表里的成果值。
  5. eq_ref：运用这种索引查找，MySQL清楚的知道最多只回来一条契合条件的记载，运用主键或许仅有值索引查找时能看到这种办法。MySQL关于这种拜访类型的优化做得十分好，由于它知道到无需估量匹配行的范文或许在找到匹配行后再持续查找（由于值不会重复）。
  6. const，system：当MySQL能对查询的某部分进行优化并将其转换成一个常量时，它就会运用这些拜访类型。比如经过将某一行的主键拜访WHERE字句的办法来查询主键：SELECT id from t where id = 1。此时MySQL就能把这个查询转换为一个常量。
  7. NULL：这种拜访办法意味着MySQ能在优化阶段分解查询句子，在履行阶段甚至不需求再拜访表或许索引。例如，从一个索引列里选取最小值能够经过独自查询索引来完结，不需求在履行时拜访表。
  8. index_merge：索引合并（index merge）。MySQL5.0之前，一个表一次只能运用一个索引，无法同时运用多个索引别离进行条件扫描。可是从5.1开端，引入了索引合并优化技能，对同一个表能够运用多个索引别离进行条件扫描，然后将它们各自的成果进行合并(intersect/union)。index merge使得我们能够运用到多个索引同时进行扫描，然后将成果进行合并。听起来好像是很好的功能，可是假如呈现了 index intersect merge，那么一般同时也意味着我们的索引树立得不太合理，由于 index intersect merge 是能够经过树立复合索引进行更一步优化的。

2.5 possible_keys

显现查询能够运用哪些索引，这是根据查询拜访的列和运用的比较操作符来判别的。该列表是在优化进程的早期创立的，因而列出来的索引关于后续实际优化进程或许是没有用的。

2.6 key

显现mysql决议采用哪一个索引来优化对该表的拜访，假如该索引没有呈现在possible_keys列中，那么MySQL选用它是出于别的的原因——例如，它或许挑选了一个掩盖索引，哪怕它没有WHERE字句。

possible_keys表明哪些索引有助于高效查找，而key表明该索引能够最小化查询成本。

假如没有挑选索引，键是NULL。要想强制MySQL运用或忽视possible_keys列中的索引，在查询中运用FORCE INDEX、USE INDEX或许IGNORE INDEX。

2.7 key_len

MySQL在索引中运用的字节数，经过这个值能够算出具体运用了索引中的哪些列，核算时需求考虑字符集，假如字段答应为 NULL，需求1字节记载是否为 NULL。

key_len显现的值为索引字段的最大或许长度，并非实际运用长度，即key_len是根据表界说核算而得，不是经过表内检索出的。

2.8 ref

这一列显现了在key列记载的索引中，表查找值所用到的列或常量，即哪些列或常量被用于查找索引列上的值。常见的有：const（常量），func，NULL，字段名（例：film.id）

2.9 rows

这一列是mysql估量要读取并检测的行数，留意这个不是成果集里的行数，而是MySQL为了找到契合查询的每一个标准的那些行而必须读取的行的平均数。

有时分该估值或许很不准确，该数字也反映不了LIMIT字句的真实查看行数。

2.10 Extra

这一列展现的是额定信息。常见的重要值如下：

  1. Using index：表明MySQL将运用掩盖索引，这产生在对表的请求列都是同一索引的部分的时分，回来的列数据只运用了索引中的信息，而没有再去拜访表中的行记载。是功能高的表现。
  2. Using index condition：在5.6版本后参加的新特性索引下推（Index Condition Pushdown，ICP），在索引遍历进程中，对索引中包括的字段先做判别（即便该字段没有运用到索引），直接过滤掉不满足条件的记载，削减回表次数。
  3. Using where：意味着MySQL服务器将在存储引擎检索行后再进行过滤。就是先经过索引读取整行数据，再按 WHRER条件进行查看，契合就留下，不契合就丢弃。查询的列未被索引掩盖。
  4. Using temporary：MySQL需求创立一张暂时表来中心成果并进一步处理，比如union、group by、distinct等，呈现这种情况一般是要进行优化的，首先是想到用索引来优化。
  5. Using filesort：MySQL会对成果运用一个外部索引排序，而不是按索引次第从表里读取行，即filesort（文件排序）。此时mysql会根据联接类型浏览一切契合条件的记载，并保存排序关键字和行指针，然后排序关键字并按次第检索行信息。这种情况下一般也是要考虑运用索引来优化的。filesort有两种，一种是内存排序，一种是磁盘排序，无法得知。
  6. Distinct: 一旦MySQL找到了与行相联合匹配的行，就不再查找了，常见于相关查询。
  7. No tables used：Query句子中运用from dual 或不含任何from子句。
  8. Using join buffer：运用了衔接缓存，join句子用到了缓冲区。

参考资料：

  1. 《 MySQL 技能内幕: InnoDB 存储引擎》
  2. 《高功能 MySQL》
  3. 《MySQL实战45讲 | 极客时间 | 丁奇》

如有需求交流，或许文章有误，请直接留言。别的希望点赞、收藏、关注，我将不连续更新各种Java学习博客！