EXPLAIN字段详解&测试

最新推荐文章于 2024-08-17 11:26:03 发布

piriineos

最新推荐文章于 2024-08-17 11:26:03 发布

阅读量3.4k

点赞数 5

分类专栏： mysql 文章标签： mysql explain 测试

本文链接：https://blog.csdn.net/qq_38125183/article/details/80647660

版权

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

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

CREATE TABLE `film` (

`film_id` smallint(5) unsigned NOT NULLAUTO_INCREMENT,

`title` varchar(255) NOT NULL,

`description` text,

`release_year` year(4) DEFAULT NULL,

`language_id` tinyint(3) unsigned NOT NULL,

`original_language_id` tinyint(3) unsignedDEFAULT NULL,

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

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

`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代表一个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;