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 id = tc.tcid and(c.cid =id =3);
explain select t.*from teacher t,course c,teacherCard tc where t.tid = c.tid id = tc.tcid and(c.cid =id =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 id = tc.tcid and(c.cid =id =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 id = tc.tcid and(c.cid =id =3);
（2）id值不同，id值越⼤⽉优先查询（本质：在嵌套⼦查询时，先查内层，再差外层）
查询教授SQL课程的⽼师的描述（desc）
explain desc from teacherCard tc,course c,teacher t where c.tid = t.tid id = tc.tcid and came ='sql';
将以上多表查询改成⼦查询形式
explain desc from teacherCard tc id =(
id from teacher t where t.tid =(
select c.tid from course c where cname ='sql'
)
);
//c-t-tc
（3）id值有相同，有不同（⼦查询+多表形式），id值越⼤越优先；id值相同，从上往下顺序执⾏
explain desc from teacher t,teacherCard tc id = 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 crame 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 crame from(select*from course where tid =1union select*from course where tid =2) cr;
如下图所⽰：
UNION：上例中的table2就是union
UNION RESULT：告知开发⼈员，哪些表之间存在union查询
3.table
实际在哪张表中查询，这⾥的表可能是实际存在的表，也可能是临时表（如衍⽣表（derived）、union表等）
类型很多，这⾥只罗列常见常⽤的⼏个
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 id from teacher t,teacherCard tc id = 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 between1and2;
//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
sql中delete用法
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个字节
注意与type中的ref区分
作⽤：
指明当前表所参照的字段
select…where a.c = b.x;(其中b.x可以是常量，则ref:const)
explain select*from course c,teacher t where c.tid = t.tid ame ='tw';---检查两张表中的字段（这⾥⽤到的三个字段）是否有索引，没有的加上索引alter table course add index tid_index(tid);
5.5
5.7
被索引优化查询的数据个数（实际通过索引查到的数据个数）;
估算出结果集⾏数，表⽰MySQL根据表统计信息及索引选⽤情况，估算的到所需的记录所需要读取的⾏数。
explain select * from course c,teacher t where c.tid = t.tid ame = ‘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哪些字段

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。