mysql explain用法详解数据库

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法，在select语句前加上explain就可以了，如：

explain select * from statuses_status where id=11;

创建测试表：

CREATE TABLE people( 

 id int auto_increment primary key, zipcode char(32) not null default , address varchar(128) not null default , lastname char(64) not null default , firstname char(64) not null default , birthdate char(10) not null default  ); CREATE TABLE people_car( people_id int, plate_number varchar(16) not null default , engine_number varchar(16) not null default , lasttime timestamp );

 插入测试数据：

insert into people 

(zipcode,address,lastname,firstname,birthdate) 

values 

(230031,anhui,zhan,jindong,1989-09-15), (100000,beijing,zhang,san,1987-03-11), (200000,shanghai,wang,wu,1988-08-25) insert into people_car (people_id,plate_number,engine_number,lasttime) values (1,A121311,12121313,2013-11-23 :21:12:21), (2,B121311,1S121313,2011-11-23 :21:12:21), (3,C121311,1211SAS1,2012-11-23 :21:12:21)

 创建索引用来测试

alter table people add index(zipcode,firstname,lastname);

 explain介绍

先从一个最简单的查询开始：

Query-1 

explain select zipcode,firstname,lastname from people;

 explain输出结果共有id,select_type,table,type,possible_keys,key,key_len,ref,rows和Extra几列。每一列分别代表什么意思呢，请看下面的解释。

select_type 表示查询中每个select语句的类型（简单 OR复杂），可以有下面几种

a.SIMPLE：最简单的SELECT查询，没有使用UNION或子查询，见Query-1。

b.PRIMARY：在嵌套的查询中是最外层的SELECT语句，在UNION查询中是最前面的SELECT语句。见Query-2和Query-3。

  查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY

Query-2 

explain select zipcode from (select * from people a) b;

Query-3 

explain select * from people where zipcode = 100000 union select * from people where zipcode = 200000;

c.UNION：UNION中第二个以及后面的SELECT语句。见Query-3。

d.DERIVED：派生表SELECT语句中FROM子句中的SELECT语句。见Query-2。

e.UNION RESULT：一个UNION查询的结果。见Query-3。

f.DEPENDENT UNION：顾名思义，首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询。

Query-4 

explain select * from people where id in (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 );

g.SUBQUERY：子查询中第一个SELECT语句。

Query-6 

explain select * from people where id = (select id from people where zipcode = 100000);

table ：显示的这一行信息是关于哪一张表的。有时候并不是真正的表名。

Query-7 

explain select * from (select * from (select * from people a) b ) c;

可以看到如果指定了别名就显示的别名。
derivedN N就是id值，指该id值对应的那一步操作的结果。
还有 unionM,N 这种类型，出现在UNION语句中，见Query-4。
注意：MySQL对待这些表和普通表一样，但是这些“临时表”是没有任何索引的。

type
type列很重要，是用来说明表与表之间是如何进行关联操作的，有没有使用索引。MySQL中“关联”一词比一般意义上的要宽泛，MySQL认为任何一次查询都是一次“关联”，并不仅仅是一个查询需要两张表才叫关联，所以也可以理解MySQL是如何访问表的。主要有下面几种类别。

const

当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。const只会用在将常量和主键或唯一索引进行比较时，而且是比较所有的索引字段。people表在id上有一个主键索引，在(zipcode,firstname,lastname)有一个二级索引。因此Query-8的type是const而Query-9并不是：

Query-8 

explain select * from people where id=1;

Query-9 

explain select * from people where zipcode = 100000;

注意下面的Query-10也不能使用const table，虽然也是主键，也只会返回一条结果。

Query-10 

explain select * from people where id 2;

system
这是const连接类型的一种特例，表仅有一行满足条件。

Query-11 

explain select * from (select * from people where id = 1 )b;

derived2 已经是一个const table并且只有一条记录。

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。 常见于主键或唯一索引扫描。eq_ref类型是除了const外最好的连接类型。
创建员工表Employee和经理表Manager

create table Employee 

 ID int auto_increment, 

 Ename varchar(32), Age int, Salary float, MID int, Primary key (ID) ); create table Manager ( MID int, Name varchar(32), Primary key(MID) );

MID对于表Manager是唯一的，主键索引，来与employee连接，故type为eq_ref。

ref

非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找。
这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或 = 操作符的带索引的列。

Query-13 

explain select * from people where zipcode=100000;

 

zipcode、firstname和lastname组成索引，这里只使用了name，即只使用了唯一性索引的一部分，故为ref。

fulltext
链接是使用全文索引进行的。一般我们用到的索引都是B树，这里就不举例说明了。
ref_or_null
该类型和ref类似。但是MySQL会做一个额外的搜索包含NULL列的操作。在解决子查询中经常使用该联接类型的优化。

range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、 、 、 =、 、 =、IS NULL、 = 、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range。

索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行， 常见于between、 、 ，IN等的查询。

Query-14 

explain select * from people where id=1 or id=2;

Query-15 

explain select * from people where id 1;

Query-16 

explain select * from people where id in (1,2);

index
该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。这个类型通常的作用是告诉我们查询是否使用索引进行排序操作。

按索引扫描表，虽然还是全表扫描，但优点是索引是有序的。index与ALL区别为index类型只遍历索引树。

Query-17 

explain select * from people order by id;

ALL
最慢的一种方式，即全表扫描。

总结

explain的type列从最差到最好依次是：
ALL：全表扫描。
index：索引扫描。
range：索引范围扫描。
ref ：非唯一性索引扫描。
eq_ref ：唯一性索引扫描。
const,system：将查询转换为一个常量。
null：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

possible_keys
possible_keys列指出MySQL能使用哪个索引在该表中找到行。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

key
key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好 。

ref
ref列显示使用哪个列或常数与key一起从表中选择行。
表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows
rows列显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。

Extra
Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，包含的信息很多，只选择几个重点的介绍下。

Using filesort
MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。

Using temporary
说明使用了临时表，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。

Not exists
MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。

Using index
说明查询是覆盖了索引的，这是好事情。MySQL直接从索引中过滤不需要的记录并返回命中的结果。这是MySQL服务层完成的，但无需再回表查询记录。

Using index condition
这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。

Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。

注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。

5724.html

mysql