SQL优化-explain的用法（实例解析）

前面写了两篇博客，关于单表和多表的优化，这两篇博客的基础其实就是explain，explain是必须掌握的知识点，我曾尝试过死记硬背，但效果甚微，还是实践来的实在。耗费一周左右时间，每晚花两小时，将explain的常见用法结合实例归纳整理了出来，收获挺大。
下面开始本文就一些实例来解析explain的用法。
注意：环境是MySQL5.7（各版本会有差异）

准备数据：
本文sql就不用管性能好不好，标不标准了，主要研究explain的用法。

create table course(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher(
tid int(3),
tname varchar(20),
tcid int(3)
);
create table teacherCard(
tcid int(3),
tcdesc varchar(200)
);
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tezdesc');
insert into teacherCard values(2,'twdesc');
insert into teacherCard values(3,'tldesc');

查询课程编号为2或教师证编号为3的老师信息：

select t.* from teacher t,course c,teacherCard tc where t.tid = c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid = 3);
explain select t.* from teacher t,course c,teacherCard tc where t.tid = c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid = 3);

1.id

（1）id值相同，从上往下顺序执行。------t3-tc3-c4

insert into teacher values(4,'ta',4);
insert into teacher values(5,'tb',5);
insert into teacher values(6,'tc',6);
explain select t.* from teacher t,course c,teacherCard tc where t.tid = c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid = 3);
//tc3-t6-c4

表的执行顺序会根据数据量的改变而改变。原因是：比较笛卡尔积的大小。—小的先执行
(5.5中则是tc3-c4-t6，真的是笛卡尔积小的先执行)
验证：

delete from course where cid>2;
explain select t.* from teacher t,course c,teacherCard tc where t.tid = c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid = 3);

（2）id值不同，id值越大月优先查询（本质：在嵌套子查询时，先查内层，再差外层）
查询教授SQL课程的老师的描述（desc）

explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid and t.tcid = tc.tcid and c.cname = 'sql';

将以上多表查询改成子查询形式

explain select tc.tcdesc from teacherCard tc where tc.tcid = (
	select t.tcid from teacher t where t.tid = (
		select c.tid from course c where cname = 'sql'
	)
);
//c-t-tc

（3）id值有相同，有不同（子查询+多表形式），id值越大越优先；id值相同，从上往下顺序执行

explain select t.tname,tc.tcdesc from teacher t,teacherCard tc where t.tcid = tc.tcid and t.tid = (select c.tid from course c where cname = 'sql');
//c-t-tc

2.select_type:查询类型

PRIMARY :包含子查询SQL中的 主查询（最外层）
SUBQUERY:包含子查询SQL中的 子查询（非最外层）
SIMPLE:简单查询（不包含子查询、union）
DERIVED:衍生查询（使用到了临时表）
a.在from子查询中只有一张表

explain select cr.cname from (select * from course where tid in(1,2)) cr;

（我这5.7版本如下图并未显示derived,5.5中上面的语句会有显示）
b.在from子查询中，如果有table1 union table2，则（左表）table1就是derived，（右表）table2就是union

 explain select cr.cname from (select * from course where tid = 1 union select * from course where tid = 2) cr;

如下图所示：

UNION:上例中的table2就是union
UNION RESULT:告知开发人员，哪些表之间存在union查询

3.table

实际在哪张表中查询，这里的表可能是实际存在的表，也可能是临时表（如衍生表（derived）、union表等）

4.type：索引类型/类型

类型很多，这里只罗列常见常用的几个
system>const>eq_ref>ref>range>index>all（性能由高到低）
其中：system,const只是理想情况；实际能达到ref>range
（1）system:只有一条数据的系统表或衍生表只有一条数据的主查询

create table test01(
 tid int(3),
 tname varchar(20)
);
insert into test01 values(1,'a');
//增加索引
alter table test01 add constraint tid_pk primary key(tid);
explain select * from (select * from test01)t where tid = 1;

(5.7没有这里是const,而5.5是system)

（2）const:仅仅能查到一条数据的SQL，并且用于Primary key或union索引（类型与索引类型有关）
explain select tid from test01 where tid = 1;—const
验证：
alter table test01 drop primary key;
create index test01_index on test01(tid);/alter table test01 add index test01_index(tid);
explain select tid from test01 where tid = 1;—不是const

（3）eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0）
常见于唯一索引和主键索引
验证：
用teacher和teacherCard两张表关联查询
在teacherCard表tcid(与teacher外键关联)上添加主键：

alter table teacherCard add constraint pk_tcid primary key(tcid);

由于外键在语法上允许重复，而eq_ref需要数据唯一，所以也给teacher中tcid加上唯一索引：

alter table teacher add constraint uk_tcid unique index(tcid);

explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid;
//type没有出现预期中的eq_ref

type没有出现预期中的eq_ref的原因分析和处理：
ref没有得到eq_ref，因为出现了0的情况，t表有六条数据，tc表只有三条数据，对应着还有三条数据为空，即为0；删了t表后三行就得到eq_ref。
以上SQL，用到的索引是t.tcid，即teacher表中的tcid字段；如果teacher的数据个数和连接查询的数据个数一致（都是三条数据），则有可能（a-b-c和a-c-b就不满足，只能数据相同而且顺序相同）满足eq_ref级别；否则无法满足。

（4）ref:非唯一性索引，对于每个索引键的查询，返回匹配的所有行（可以是0，多个）
准备数据：

insert into teacher values(4,'tz',4);//为了有两个tz数据
insert into teacherCard values(4,'tzxxx');

测试：

alter table teacher add index index_name(tname);
explain select * from teacher where tname = 'tz';//type---ref

（5）range:检索指定范围的行，where后面是一个范围查询（between,in,>,<,>=等）

alter table teacher add index tid_index(tid);
explain select t.* from teacher t where t.tid in (1,2);
//可能得到all(没有用到索引，因为in有时候会失效，从而没用到索引)
explain select t.* from teacher t where t.tid > 3;
//range
explain select t.* from teacher t where t.tid < 3;
//range
explain select t.* from teacher t where t.tid between 1 and 2;
//range

（6）index:查询全部索引中的数据
explain select tid from teacher;
//tid是索引，只需要扫描索引表，不需要所有表中的所有数据
（7）all:查询全部表中的数据
explain select cid from course;
//cid不是索引，需要全表扫描，即需要所有表中的所有数据
小结：
system/const:结果只有一条数据
eq_ref:结果多条，但是每条数据是唯一的
ref:结果多条，但是每条数据是0条或多条

5.possible_key

可能用到的索引，是一种预测，不准。

6.key

实际用到的索引
—possible_key/key如果是null,则是说明没有用到索引

7.key_len:索引的长度

作用：
常用于判断复合索引是否被完全使用

create table test_k1(
 name char(20) not null default ''
);
//一个字段的情况
alter table test_k1 add index index_name(name);
explain select * from test_k1 where name = ''; 
//key_len:60
//在utf8:1个字符占三个字节

alter table test_k1 add column name char(20); 
//name1可以为null
alter table test_k1 add index index_name1(name1);
explain select * from test_k1 where name1 = '';
//key_len:61
//如果索引字段可以为null,则MySQL底层会使用1个字节用于标识name1可以为null。

//把两个索引都删了：
drop index index_name on test_k1;
drop index index_name1 on test_k1;
//增加一个复合索引
alter table test_k1 add index name_name1_index(name,name1);
explain select * from test_k1 where name1 = '';
//key_len:121---组合索引要用到第二个，那必然用到了前面的索引，所以：20*3+20*3+1=121
explain select * from test_k1 where name = '';
//key_len:60---用组合索引的第一个索引即可，所以：20*3 = 60

varchar(20)的情况:
alter table test_k1 add column name2 varchar(20);
//可以为null

alter table test_k1 add index name2_index(name2);
explain select * from test_k1 where name2 = '';
//key_len:63
//20*3 = 60 + 1(null) +2(用2个字节 标识可变长度---char是固定长度，而varchar是可变长度，要用两个字节标识) = 63

utf8:1个字符3个字节
gbk:1个字符2个字节
latin:1个字符1个字节

8.ref

注意与type中的ref区分
作用：
指明当前表所参照的字段
select…where a.c = b.x;(其中b.x可以是常量，则ref:const)

explain select * from course c,teacher t where c.tid = t.tid and t.tname = 'tw';---检查两张表中的字段（这里用到的三个字段）是否有索引，没有的加上索引
alter table course add index tid_index(tid);

5.5
5.7

9.rows

被索引优化查询的数据个数（实际通过索引查到的数据个数）;
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。
explain select * from course c,teacher t where c.tid = t.tid and t.tname = ‘tz’;

10.Extra

（1）using filesort:性能消耗大；需要“额外”的一次排序（查询）—常见于order by语句中
排序：排序之前必然是先查询
实例：

create table test02(
 a1 char(3),
 a2 char(3),
 a3 char(3),
 index idx_a1(a1),
 index idx_a2(a2),
 index idx_a3(a3)
);

explain select * from test02 where a1 = '' order by a1;
//using where
//给a1排序之前先查询了a1，所以不用using filesort

explain select * from test02 where a1 = '' order by a2;
//using where,using filesort
//给a2排序之前只查询了a1，而a2没有查询，所以额外对a2的一次查询然后才能完成a2的排序，即用到using filesort

小结：对于单索引，如果排序和查找同一个字段，则不会出现using filesort;如果排序和查找不是同一个字段，则会出现using filesort;
避免：where哪些字段就order by哪些字段

复合索引：不能跨列（最佳左前缀）

drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;

alter table test02 add index idx_a1_a2_a3(a1,a2,a3);
explain select * from test02 where a1='' order by a3;//using filesort
explain select * from test02 where a2='' order by a3;//using filesort
explain select * from test02 where a1='' order by a2;//没有using filesort

小结：避免：where和order by按照复合索引的顺序使用，不要跨列或无序使用

（2）using temporary：性能损耗大，用到了临时表。
需要额外再多使用一张表，一般出现在group by语句中；
出现原因：
已经有表了，但不使用，必须再来一张表（临时表）。
看到性能损耗大的就不是优化了而是干掉

explain select a1 from test02 where a1 in('1','2','3') group by a1;
explain select a1 from test02 where a1 in('1','2','3') group by a2;
//using temporary(这里也是因为a2不是索引才会出现using temporary)

避免：查询哪些列，就根据那些列group by

（3）using index：性能提升；也叫索引覆盖（覆盖索引）。
原因：不读取原文件，只从索引文件中获取数据（不需要回表查询 ）
只要使用到的列全部都在索引中，就是索引覆盖using index
实例：
test02中有a1a2a3的复合索引

explain select a1,a2 from test02 where a1='' or a2='';
//using index

drop index index_a1_a2_a3 on test02;
alter table test02 index idx_a1_a2(a1,a2);

explain select a1,a3 from test02 where a1='' or a3='';
//using where,没有using index---a1,a2两个索引无法覆盖a1,a3

如果用到了索引覆盖（using index时），会对possible_keys和key造成影响：
a.如果没有where,则索引只出现在key中；
b.如果有where，则索引出现在key和possible_key中。

explain select a1,a2 from test02 where a1='' or a2='';
explain select a1,a2 from test02;

（4）using where(需要回表查询)
假设age是索引列，而name不是索引列
但查询语句select age,name from…where age=…，此语句中必须回原表查Name，因此会显示using where。

explain select a1,a3 from test02 where a3='';
//using where

（5）impossible where:where子句永远为false

explain select * from test02 where a1='x' and a1='y';
//impossible where