3.3慢SQL 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)
);

Query-12
explain select * from Employee A,Manager B where A.MID=B.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条件过滤。