MySQL Explain详解

最近慢慢接触MySQL，了解如何优化它也迫在眉睫了，话说工欲善其事，必先利其器。最近我就打算了解下几个优化MySQL中经常用到的工具。今天就简单介绍下EXPLAIN。
内容导航

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra

环境准备
MySQL版本：

创建测试表
复制代码

CREATE TABLE people(
id bigint 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 bigint,
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 key(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几列。
id

Query-2
explain select zipcode from (select * from people a) b;

id是用来顺序标识整个查询中SELELCT 语句的，通过上面这个简单的嵌套查询可以看到id越大的语句越先执行。该值可能为NULL，如果这一行用来说明的是其他行的联合结果，比如UNION语句：

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

select_type

SELECT语句的类型，可以有下面几种。

SIMPLE

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

PRIMARY

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

UNION

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

DERIVED

派生表SELECT语句中FROM子句中的SELECT语句。见Query-2。

UNION RESULT

一个UNION查询的结果。见Query-3。

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 );

Query-4中select id from people where zipcode = 200000的select_type为DEPENDENT UNION。你也许很奇怪这条语句并没有依赖外部的查询啊。

这里顺带说下MySQL优化器对IN操作符的优化，优化器会将IN中的uncorrelated subquery优化成一个correlated subquery（关于correlated subquery参见这里）。

SELECT … FROM t1 WHERE t1.a IN (SELECT b FROM t2);

类似这样的语句会被重写成这样：

SELECT … FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

所以Query-4实际上被重写成这样：

Query-5
explain select * from people o where exists (select id from people where zipcode = 100000 and id = o.id union select id from people where zipcode = 200000 and id = o.id);

题外话：有时候MySQL优化器这种太过“聪明” 的做法会导致WHERE条件包含IN()的子查询语句性能有很大损失。可以参看《高性能MySQL第三版》6.5.1关联子查询一节。

SUBQUERY

子查询中第一个SELECT语句。

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

DEPENDENT SUBQUERY

和DEPENDENT UNION相对UNION一样。见Query-5。

除了上述几种常见的select_type之外还有一些其他的这里就不一一介绍了，不同MySQL版本也不尽相同。

table

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

Query-7
explain select * from (select * from (select * from people a) b ) c;

可以看到如果指定了别名就显示的别名。

N就是id值，指该id值对应的那一步操作的结果。

还有