MySQL版本：5.7.19
在文末会有对应的附录，测试所用到的SQL。
EXPLAIN语句提供关于MySQL执行语句的信息。EXPLAIN对SELECT,DELETE,INSERT,REPLACE和UPDATE语句都有效。
EXPLAIN在每个表的SELECT语句中使用将返回一行信息，
EXPLAIN Output Columns
当我们执行形如下面的语句时
explain select * from t_user where id = 1;
我们可以得到下面的结果：
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
下面是对各个列的解释：
| 列 | 说明 |
|---|---|
| id |
SELECT 的标识符 |
| select_type |
SELECT 的类型 |
| table | 输出行对应的表 |
| partitions | 匹配的分区 |
| type | 连接类型 |
| possible_keys | 可能选择的索引 |
| key | 实际选中的索引 |
| key_len | 使用到的索引的长度 |
| ref | 索引参照的列 |
| rows | 估算的检查的行数 |
| filtered | 表条件过滤后的行百分比 |
| Extra | 额外的信息 |
- id
SELECT 标识。SELECT查询的序列号。如果查询使用了union，这个值将会出现空。这种情况下，这行EXPLAIN中的table显示的值类似于<unionM,N>，其中M和N对应的id的值。
- id 全相同：按照排列顺序执行
- id 不全相同：不同的按从大到小执行，相同的按排列顺序执行
- id 全不同：按id从大到小执行
- select_type
查询类型，可以是下表中的任何一种情况。
| 类型值 | 解释 |
|---|---|
| SIMPLE | 简单查询（没有使用union或子查询） |
| PRIMARY | 外层查询： union或者子查询中最外层的部分 |
| UNION | 第二个或者更后的union查询语句 |
| DEPENDENT UNION | 第二个或者更后的union查询语句，依赖外部查询 |
| UNION RESULT | union的结果 |
| SUBQUERY | 第一个子查询 |
| DEPENDENT SUQUERY | 第一个子查询，依赖外部查询 |
| DERIVED | 派生表查询（from子句中的子查询） |
| MATERIALIZED | 具体化的子查询 |
| UNCACHEABLE SUBQUERY | 不能缓存结果的子查询且必须重新评估每一行的外部查询 |
| UNCACHEABLE UNION | 第二个或更后的union查询，且属于uncacheable subquery |
- table
输出行展示的表名称，也可能是下列值之一:
<unionM,N>: union 查询会出现<derivedN>: 这行指的是派生表的结果且这行的id的值是N。派生表可能的结果，例如：FROM条件是一个子查询。<subqueryN>:这行指的是物化子查询的结果且这行的id的值为N。
- partitions
查询到的记录所对应的分区，如果不是分区表，则对应的值为null。
- type
连接类型，具体的分析请看下文中的 EXPLAIN Join Types 。
- passible_keys
possible_keys 表示可能使用到的索引。
如果这列为 NULL，表示没有没有相关的索引。如果你想提高查询的性能，你需要检查下 WHERE 条件，查看是否有必要创建索引。
查看索引可以使用 SHOW INDEX FROM tbl_name。
- key
显示实际上用到的索引，一般都能在possible_keys中找到对应的索引。
在key中显示的索引，在possible_keys中却没有对应的索引。那么可能查询的字段刚好被一个索引包含。比如：select email, realname, idcard from t_user_detail where realname = '';，我们刚好创建了一个组合索引index(email, realname, idcard)。
如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引
- key_len
使用到的索引的长度，越短越好。如果没有key，那么ken_len也为NULL。
- ref
ref展示哪些列或者常量和在key中显示的索引相比较从表中选择行。
如果值是func，则使用的值是某个函数的结果。要查看哪个函数，请在EXPLAIN后使用SHOW_WARNINGS告查看扩展的EXPLAIN输出。函数实际上可能是一个运算符，例如算术运算符。
- rows
rows 表示MySQL认为必须检查执行查询的行数。
对于InnoDB表，这个数字是一个估计值，并不总是准确的。
- filtered
filtered 表示：被表条件过滤的表行估算比。即：rows中的实际执行的估算行数，rows × filtered / 100，显示的行数将于前面的表连接。
- Extra
额外的信息：包含关于MySQL如何解决查询的附加信息。
EXPLAIN Join Types
EXPLAIN中的type列描述表是如何连接的，以下描述连接类型，从最好到最坏：
- system
表只有唯一的一行（= system table）。是const连接类型的一种特殊情况。
- const
在开始查询读取时，表中最多只有一个匹配的行。因为只有一条记录，优化器可以将行中的列值认作为常量。查询速度很快因为只需要读取一次。
const被使用的情况：当查询条件是主键或者是具有唯一索引的字段时。比如下面这个例子
explain select * from t_user where id = 1;
- eq_ref
对于每个来自于前面的表的记录，从该表中读取唯一一行。除了system和const类型，这个是效率最高的了。它被用于当索引的所有的部分都被连接使用时，并且这个索引是主键索引或者是非空的唯一索引。
eq_ref可被用于=操作符相比较的索引列，和表列对应的比较值可以是一个常量或者是一个表达式
explain select * from t_user u left join t_department d on u.dept_id = d.id;
- ref
对于每个来自于前面的表的记录，所有匹配的行从这张表中取出。ref使用于索引的最左前缀或者这个索引不是主键或者唯一索引(换句话说，基于这个键值的连接不能选择出唯一的一条记录)。
ref可以用于使用=或<=>操作符的带索引的列。
# 使用最左前缀
explain select * from t_user_detail u where u.idcard = '123456789012345678';
- fulltext
该join使用的了全文索引。
- ref_or_null
这种连接类型类似ref，但是MySQL的条件中有额外搜索包含null的值。
explain select * from student where email = '态度' or email is null;
NOTE : 这里有个问题，当非NULL数据小于6条时，
type值为ALL，而不是ref_or_null。
- index_merge
一个表中出现使用到了两个索引，mysql的优化器会将其优化合并。
explain select * from t_user_detail where realname = '' or address = '北京';
NOTE : 数据要大于等于7条，不然为
ALL。
- unique_subquery
这种类型替换eq_ref，用于IN的子查询中。
value IN (SELECT primary_key FROM single_table WHERE some_expr)
- index_subquery
这种类型类似于unique_subquery。不同点在于：子查询中是非唯一索引。
- range
在给定范围的行里面检索。key列显示了使用的索引，ref列为NULL。
当表列和一个常量进行比较，使用了=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN() 操作符会用到range
explain select * from t_user where id > 1;
explain select * from t_user where id between 1 and 3;
explain select * from t_user where mobile like '123%';
explain select * from t_user where id in(1, 3, 2);
- index
这种类型类似于ALL，不同点：这种遍历的是索引树.
explain select address from t_user_detail;
- ALL
遍历全表，知道找到结果。
explain select * from t_user where gender = 1;
EXPLAIN Extra Information
- using where
使用了where查询
explain select * from t_user where gender = 1;
- using index
使用到了覆盖索引
explain select idcard, realname, email from t_user_detail;
- using join buffer
使用了连接缓存
explain select * from t_user where dept_id in (select id from t_department);
- using file sort
在文件内进行了排序操作。比较耗性能，需要优化。一般是使用了group by 或者 order by会出现这种情况。
explain select * from t_user order by age;
附录
drop table if exists t_department;
drop table if exists t_user;
drop table if exists t_user_detail;
drop table if exists student;
-- 部门表
create table t_department(
id int not null auto_increment comment '主键id',
dept_name varchar(20) not null default '' comment '部门名称',
primary key (id)
) engine innodb charset utf8;
alter table t_department comment '部门表';
-- 用户表
create table t_user(
id int not null auto_increment comment '主键，自增',
mobile varchar(20) not null default '' comment '手机号',
passwd varchar(16) not null default '' comment '密码',
nickname varchar(20) not null default '' comment '昵称',
gender tinyint not null default 2 comment '性别：0=女，1=男，2=未知',
age tinyint not null default 0 comment '年龄',
birthday date not null default '1997-01-01' comment '出生年月',
remark varchar(128) not null default '' comment '备注',
dept_id int not null default 0 comment '部门id，取自t_department.id',
create_time datetime not null default '2017-09-23 00:00:00' comment '创建时间',
primary key(id)
) engine innodb charset utf8;
alter table t_user comment '用户表';
# 在mobile列上设置唯一索引
alter table t_user add unique index uniq_mobile(mobile);
# 在age列上设置普通索引
alter table t_user add index idx_age(age);
alter table t_user add index idx_create_time(create_time);
-- 用户详情表
create table t_user_detail(
id int not null auto_increment comment '主键id',
user_id int not null comment '用户id，取自t_user.id',
email varchar(64) not null default '' comment '邮箱',
address varchar(64) not null default '' comment '地址',
realname varchar(64) not null default '' comment '实名',
idcard varchar(18) not null default '' comment '身份证号',
primary key(id)
) engine innodb charset utf8;
alter table t_user comment '用户信息扩展表';
# user_id设置唯一索引
alter table t_user_detail add unique index uniq_user_id(user_id);
# 创建符合索引
alter table t_user_detail add index idx_idcard_realname_email(idcard, realname, email);
# 创建普通索引
alter table t_user_detail add index idx_address(address);
alter table t_user_detail add index idx_realname(realname);
-- 学生表
create table student(
id int primary key auto_increment,
sname varchar(20),
age tinyint(3) unsigned,
phone varchar(20),
email varchar(30)
);
alter table student add index idx_email(email);
#### 数据插入
-- 新增部门数据
insert into t_department(id, dept_name) values (1, '天猫部');
insert into t_department(id, dept_name) values (2, '支付宝部');
-- 新增用户信息
insert into t_user(id, dept_id, mobile, passwd, nickname, gender, age, birthday, remark, create_time) values(1, 1, '15333333333', '123456', 'zhangsan', 1, 23, '1993-03-03', '我是张三', '2017-09-26 00:00:00');
insert into t_user(id, dept_id, mobile, passwd, nickname, gender, age, birthday, remark, create_time) values(2, 2, '17444444444', '123456', 'lisi', 1, 22, '1994-04-04', '我是李四', '2017-09-27 00:00:00');
insert into t_user(id, dept_id, mobile, passwd, nickname, gender, age, birthday, remark, create_time) values(3, 2, '18555555555', '123456', 'wangwu', 0, 21, '1995-05-05', '我是王五', '2017-09-28 00:00:00');
insert into t_user(id, dept_id, mobile, passwd, nickname, gender, age, birthday, remark, create_time) values(4, 1, '13771005123', '123456', 'cuihua', 0, 20, '1996-06-06', '我是翠花', '2017-09-29 00:00:00');
insert into t_user(id, dept_id, mobile, passwd, nickname, gender, age, birthday, remark, create_time) values(5, 1, '18714385401', '123456', 'haoren', 2, 25, '1991-01-01', '我是好人', '2017-09-30 00:00:00');
insert into t_user(id, dept_id, mobile, passwd, nickname, gender, age, birthday, remark, create_time) values(6, 1, '15879831234', '123456', 'erya', 2, 25, '1991-01-01', '二丫', '2017-09-25 00:00:00');
insert into t_user(id, dept_id, mobile, passwd, nickname, gender, age, birthday, remark, create_time) values(7, 1, '13879831234', '123456', 'sansha', 2, 25, '1991-01-01', '三傻', '2017-09-24 00:00:00');
-- 新增用户详情
insert into t_user_detail(id, user_id, email, address, realname, idcard) values(1, 1, 'zhangsan@gmail.com', '北京', '张三', '123456789012345678');
insert into t_user_detail(id, user_id, email, address, realname, idcard) values(2, 2, 'lisi@gmail.com', '上海', '李四', '098765432109876544');
insert into t_user_detail(id, user_id, email, address, realname, idcard) values(3, 3, 'wangwu@gmail.com', '广州', '王五', '212345678901234567');
insert into t_user_detail(id, user_id, email, address, realname, idcard) values(4, 4, 'cuihua@baidu.com', '深圳', '翠花', '672645284761534902');
insert into t_user_detail(id, user_id, email, address, realname, idcard) values(5, 5, 'haoren@green.com', '杭州', '好人', '369382766947859838');
insert into t_user_detail(id, user_id, email, address, realname, idcard) values(6, 6, 'erya@qq.com', '武汉', '二丫', '562336123987343123');
insert into t_user_detail(id, user_id, email, address, realname, idcard) values(7, 7, 'sansha@163.com', '南京', '三傻', '982336123987343123');
-- 新增学生信息
insert into student(sname,age,phone,email) values('zhangsan',24,'13812345678','zhangsan@qq.com');
insert into student(sname,age,phone,email) values('zhangxiaosan',25,'13312345678','zhangxiaosan@qq.com');
insert into student(sname,age,phone,email) values('lisi',26,'13812345674','lisi@qq.com');
insert into student(sname,age,phone,email) values('lixiaosi',27,'13812345675','lixiaosi@qq.com');
insert into student(sname,age,phone,email) values('wangwu',28,'13812345676','wangwu@qq.com');
insert into student(sname,age,phone,email) values('wangxiaowu',30,'13612345677','wangxiaowu@qq.com');
insert into student(sname,age,phone,email) values(null, null, null, null);
