EXPLAIN字段详解&测试

       测试数据库来自mysql示例数据库(https://launchpad.net/test-db)。所用的表为sakila.film以及sakila.film_actor和sakila.language。表结构分别为：mysql

       CREATE TABLE `film` (react

  `film_id` smallint(5) unsigned NOT NULLAUTO_INCREMENT,算法

  `title` varchar(255) NOT NULL,sql

  `description` text,数据库

  `release_year` year(4) DEFAULT NULL,缓存

  `language_id` tinyint(3) unsigned NOT NULL,mysql优化

  `original_language_id` tinyint(3) unsignedDEFAULT NULL,ide

  `rental_duration` tinyint(3) unsigned NOT NULLDEFAULT '3',函数

  `rental_rate` decimal(4,2) NOT NULL DEFAULT'4.99',oop

  `length` smallint(5) unsigned DEFAULT NULL,

  `replacement_cost` decimal(5,2) NOT NULLDEFAULT '19.99',

  `rating` enum('G','PG','PG-13','R','NC-17')DEFAULT 'G',

  `special_features`set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULTNULL,

  `last_update` timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`film_id`),

  KEY `idx_title` (`title`),

  KEY `idx_fk_language_id` (`language_id`),

  KEY `idx_fk_original_language_id`(`original_language_id`),

  CONSTRAINT `fk_film_language` FOREIGN KEY(`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,

  CONSTRAINT `fk_film_language_original`FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ONUPDATE CASCADE

) ENGINE=InnoDBAUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

 

CREATE TABLE`film_actor` (

  `actor_id` smallint(5) unsigned NOT NULL,

  `film_id` smallint(5) unsigned NOT NULL,

  `last_update` timestamp NOT NULL DEFAULTCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`actor_id`,`film_id`),

  KEY `idx_fk_film_id` (`film_id`),

  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY(`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,

  CONSTRAINT `fk_film_actor_film` FOREIGN KEY(`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE

) ENGINE=InnoDBDEFAULT CHARSET=utf8

 

id

     *id能够认为是查询序列号，每个id表明一个select，一组相同的id能够认为是一个查询里分开进行的几步（如关联查询），解析顺序在explain生成表中由上至下顺序解析，不一样的id表明不一样子查询，id越大优先级越高，越先被解析。根据mysql官方文档的解释，id是selectidentifier，即select标识符，解析顺序并不表明执行顺序，可是通常来说相差不大。

  *id相同：explain selectfilm_actor.actor_id from film inner join film_actor using(film_id) order by film_actor.actor_idlimit 100;

       *id不一样：explain select film_actor.actor_id from film innerjoin film_actor using(film_id)

union all select film_actor.actor_id fromfilm_actor where film_id = 1;

 

       这里因为使用了union all，因此union操做时不会使用默认选项distinct，在重复选项不影响时能够提升速度。

select_type

       *SIMPLE:查询不含union或者子查询（关联查询仍然是simple）。

       explainselect t2.actor_id from film as t1 inner join film_actor as t2 using(film_id)order by t2.actor_id limit 100;

       若是不使用straight_join，那么链接查询的执行顺序并不必定是提交语句里的顺序，优化器会根据实际状况选择合适的链接顺序。肯定顺序后按照顺序进行嵌套循环查询。      

       *PRIMARY:包含任何复杂的子部分查询如子查询、union等，那么primary值的是该查询语句最外层的查询。

       *SUBQUERY：位于select或者where后面的子查询为subquery。有些子查询能够转换为链接查询，

       *DERIVED：位于from后面的子查询，也叫作派生表，mysql会递归执行并把结果放在一个临时表中。

       *UNION：出如今union以后的select。

       explain select t1.actor_id from (select *from film_actor where film_id<100)t1 where t1.actor_id=(select actor_id fromactor order by actor_id limit 1) union all select film_actor.actor_id fromfilm_actor where film_id = 1;

       这个语句首先执行id为4的查询（即select_type为union）随后执行id为3和2的子查询，最后执行primary外部查询。   

        *DEPENDENT UNION&DEPENDENT SUBQUERY：关联子查询，同union和subquery出现位置同样，可是受到外部查询影响，好比该查询某参数要等于外部查询某个参数。

       explain select language_id from filmwhere exists(select * from film_actor where actor_id=1 andfilm_actor.film_id=film.film_id) union select language_id from language wherefilm.language_id = language.language_id;

       *UNCACHEABLE SUBQUERY&UNCACHEABLEUNION：不能被缓存的子查询或者union，每一行的结果必须即时交由外部查询过滤。

table

       *table表示查询的表名，若是使用了别名，那么这儿显示的就是别名，若是为null则表明不涉及表操做，若是为<>括起来，好比<derivedN>则表明该表是id为N的查询产生的临时表，<unionM,N>表示该表为id为M和N union以后产生的临时表。

type

       *type表示访问方式，从好到坏依次为：NULL,system,const,eq_ref,ref，fulltext,ref_or_null,unique_subquery,range,

index_merge,index,ALL。

*NULL：mysql在优化过程当中经过访问引擎统计信息直接获得结果，不用访问表或者索引，好比获取myisam表的行数，或者从索引列选取最小值。

*const，system：mysql会将查询可能的部分转换为常量。它们的区别是，system的表中仅有一行，而const则是表中仅有一行匹配行。

explain select film_id from (select * fromfilm where film_id=1)t1 union select min(film_id) from film;

通常来说，const和system是能达到的最好的查询效率，通常来讲可以达到ref或者eq_ref就不错了，range是所可以容忍的最低底线，若是再低就须要考虑优化语句了。

*eq_ref：使用多表链接时，仅返回一行记录，即便用primary key或者unique key。

explain select film.film_id from film,film_actor wherefilm.film_id=film_

actor.film_id;

*ref：使用非惟一索引或者惟一索引的前缀等值匹配时。能够返回多行，但仅匹配一个值。

explain select * from film where language_id=2;

*ref_or_null：与ref相似，只是增长了null值的比较。

*fulltext：全文索引，优先级很高，与普通索引同时存在时，mysql优先选择全文索引，因为导入的示例数据库没有MyISAM的表，故没法测试该type了。

*unique_subquery：用于where中in形式的子查询，子查询返回不重复的惟一值

*index_subquery：相似unique_subquery，不过能够返回重复的值。

explain select * from film where film_id in(select film_id fromfilm_actor where actor_id=1 or actor_id=2);

当在where语句使用in()子查询时，mysql会把上面的语句优化(也许不必定是优化，有时候用in过滤大部分数据后在用外部查询效率更高)成以下：

select * from film where exists(select film_id from film_actor whereactor_id=1 or actor_id=2 and film.film_id=film_actor.film_id);因此先执行的是exists中的dependentsubquery。

*range：索引范围扫描，当索引使用<,>,is null,between,in,like时出现。

explain select * from film where title like  'love%';

*index_merge：查询使用了两个以上的索引，mysql优化后对结果去交集或者并集。

当有复杂的AND或OR条件时，可能须要转换才能让优化器识别：(x or y) and z转换成 (x and z) or (y and z)。另外，当同时出现index_merge和range时，mysql会选择range：(x and y) or z<30;

explain select * from film where language_id=2 or title like 'love%';

*index：索引全表扫描，用索引把全表从头至尾扫一遍。当用force index或者覆盖索引全表扫描时出现（以下）。

explain select film_id from film;

*ALL：全表扫描，而后在server层过滤记录。

possible_keys

*指出mysql在查询中可能会使用的索引，但不必定会使用，mysql会根据统计信息选出代价最小的索引。代价通常指找到所需的行记录所须要查询的页数量（并非行数量，由于mysql以页为基本单位，可能会出现行数较少，可是须要I/O的页却较多的状况），页的数量越多代价越大，性能也就越差。

key

       *在查询中实际使用的索引，若没有使用则显示NULL。根据官方文档，key中实际使用的索引可能并不会在possible_keys中出现，当某个索引是须要查找的列的覆盖索引，且mysql找不到更好的索引去查询时，会使用该索引进行索引全表扫描，虽然比较慢，但总比普通的全表扫描，且须要随机磁盘I/O好得多。

key_len

       *表示使用的索引在表定义中的长度，好比film表中主键为smallint，则为2，language_id为tinyint则为1，若没有使用索引，则为NULL。经过key_len能够知道复合索引中的那几列在查询中使用了。

ref

       *根据官方文档，ref是指用来与key中所选索引列比较的常量(const)或者链接查询列（显示为该列名字）。当ref的值为func时，表示索引比较的值来自函数或者算数式。ref为NULL时表示没有使用索引。

       explainselect * from film where film_id in(select film_id from film_actor where actor_id=1);

       因为mysql优化器将外部查询压入in中的子查询，于是ref有两行。

rows

       *表示优化器认为须要扫描多少行才能获得目标结果。对于innodb来讲这是一个估计值，innodb并不知道一页中有多少行数据，于是优化器会经过随机读取一些页求平均值来估计行数。

filtered    

       *表示存储引擎返回的数据在server层过滤后，剩下多少知足查询的记录数量的比例。即实际剩下的行数为rows xfiltered/100。

extra

       *这是mysql认为很关键，可是又不该该出如今前面所述字段的信息。

       *constrow not found:当所查找的表为空时出现。

       *impossiblewhere:当where条件不可能发生时出现。

       explainselect * from (select * from film where 1=2)t1;

      

       能够看到子查询where条件不可能达成，于是返回的表为空。

       *deletingall rows：某些引擎支持一种更快的删除全部行记录的方法，好比myisam引擎表，我猜是由于myisam将数据单独放在一个文件的缘由。

       *distinct:当发现第一个匹配行后中止为当前行组合搜索更多的行。

       *impossiblehaving：相似impossible where。having条件过滤没有结果，或者始终宣布出任何列（直接返回已有查询的结果集）。

       explainselect * from film group by language_id having 1=2;

      

       *loosescan：半链接优化的loosescan机制被启用。loosescan子查询中的字段做为一个索引且外部select语句能够与不少的内部select记录相匹配，从而便有经过索引堆记录进行分组的效果。

       *notable used： 查询只有一个from dual，没有真实表的from条件。

       explainselect 1 from dual;

      

       *notexists:mysql可以从一个查询中使用一个left join优化，当从该表中找到一行数据与已找出数据行对应，则不在查找更多行。

       explainselect * from film left join film_actor on film.film_id=film_actor.film_idwhere film_actor.film_id is null;

      

       *selecttable optimized away：当查询语句被优化成一个常量时出现。

       explainselect min(film_id) from film;

      

       *usingfilesort:当排序不能使用索引时出现，须要产生临时表进行排序。当临时表较小时，存储在内存中用快速排序进行排序，当临时表较大时则分段存储在硬盘中，每段快速排序，而后在内存中合并排序。排序算法有两种，一种排序时仅用须要排序的字段，这样能够存储更多的行在内存中，但缺点是须要两次传输；一种是排序时把全部列都传输过来，只须要一次传输，但内存消耗较大。当查询全部列长度不超过max_length_for_sort_data时用单次传输排序，超过则两次传输排序。

       explainselect * from film order by length limit 100;

      

       *usingindex：获取查询所需列仅须要从索引中便可获取，不须要从磁盘中I/O获取。即便用了覆盖索引，能够大大减小I/O所消耗的时间，提升效率。对于innodb来讲，次级索引能够由此获益，从而能够不用多查询一次主键索引表。

       explainselect film_id from film;

      

       *usingindex condition:根据官方文档，对于不能使用索引的部分列，若是正在使用的索引中包括了这些列，那么就会用ICP优化将where过滤条件压入引擎层中，在使用索引时就过滤掉部份内容，从而减小I/O，对于innodb，次级索引能够由此获益，由于主键索引已经包含数据，所以不能经过他减小I/O。他与using index&using where的区别是后者索引列已经包含全部内容，不须要回表I/O了，所以后者的性能通常优于前者。

       *usingindex for group by：相似using index，mysql不须要再回表查询，而且group by依据索引就能够实现，是最快的group by操做，其次是使用索引（但不是覆盖索引）的group by操做，而后是不使用的索引的group by操做，这种会产生临时表，速度最慢。

       explainselect film_id from film group by film_id;

      

       explainselect film_id from film group by length;

             

       能够看到不使用索引group by产生了临时表，并使用了filesort，这是由于group by暗含sort操做，若是不须要可使用order by null优化。

       *usingjoin buffer：mysql表链接算法有Nestedloop join，这种是每次外表获取一行做为内部查询的过滤条件，这样外表有多少条，内部就要循环多少次，形成了屡次表I/O；还有Block Nested loop join，这种是一次把join_buffer_size行数据存入join buffer中，而后join buffer中全部数据与内存循环比较，这样大大减小表I/O次数。

       explainselect * from film inner join film_actor using(film_id);

      

       发现extra为空，用show variables like ‘optimizer%’;发现没有block_nested_on选项。

       用show variables like 'version%'；查看发现mysql版本为5.5.不支持BLN算法。

      

       估计是由于163的debian8软件源中尚未高版本mysql。

       *usingMRR：优化器经过MRR（Multi-Range ReadOptimization）算法读取表数据。MRR经过将随机I/O转换为顺序I/O以下降查询过程当中的I/O开销。对于次级索引，先将次级索引表获得的次级索引和主键的结果集根据主键排序，而后在主键索引表中访问时就变成了顺序I/O。

       explainselect * from film where title like 'love%' order by film_id;

       *usingunion &using sort_union& using intersect:当使用index_merge索引时可能会使用这三种算法中的一种，intersect出如今当对多个索引结果集求交集时即AND，union出如今多个索引结果集求并集时即OR，sort_union则出如今索引结果集的数据可能无序时，须要对结果集排序（根据unique的列排序以保证惟一性）后求并集（应该是为了去重）。

       explainselect * from film_actor where actor_id>2 and film_id=1;

      

       explainselect * from film where title like 'love%' or film_id<2;

      

       能够看到使用了两个次级索引的范围或者最左前缀查询其结果可能无序，于是使用了sort_union算法。

       通常来说index_merge索引效率不高，排在range后面，因此应该尽可能让索引覆盖到where的全部列，而不是使用多个单个索引。

       *usingtemporary：为了执行下一步查询，mysql会建立一个临时表来存储已有的结果，通常出如今未使用索引的group by或者order by或者distinct或者from后面的子查询。这个字段前面出现过，就再也不测试了。通常来讲，应该尽可能避免使用临时表，临时表没有索引而且占用空间，若是是硬盘临时表I/O还要浪费大量时间，对于group by或者order by后面的列，应该尽可能包含在索引中，它们判断是否使用索引的方法和复合索引同样。

       *usingwhere：用来过滤哪些行记录用来与接下来的表匹配或者返回给客户端。根据个人理解，通常没法使用索引的where条件，或者使用索引进行范围查询的会返回给server层进行where过滤，这时候会出现using where；不过实际上若是使用了ICP，那么这些过滤条件会压入到引擎层。

       explainselect * from film where language_id=1 and length<100;

      

       这个查询length没有索引，因此会在server层进行判断，这样致使了返回不少不须要的数据，于是对于这种using where，若是length<100的行数比例不多，且查询使用该列次数较多，就应该尽可能把该列包含进索引中。

       *zerolimit:查询条件中有limit 0而且没有能够选择的任何行。