1）查看执行计划
语法：explain + SQL语句
eg：explain select * from tb;
2）“执行计划”中需要知道的几个“关键字”
id：编号select_type：查询类型table：表type：类型possible_keys：预测用到的索引key：实际使用的索引key_len：实际使用索引的长度ref：表之间的引用rows：通过索引查询到的数据量Extra：额外的信息
建表语句和插入数据：
# 建表语句
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,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;
explain执行计划常用关键字详解
1）id关键字的使用说明
① 案例：查询课程编号为2 或 教师证编号为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);
结果如下：
接着，在往teacher表中增加几条数据。
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);
结果如下：
a b c
2 3 4
最终：2 * 3 * 4 = 6 * 4 = 24
c b a
4 3 2
最终：4 * 3 * 2 = 12 * 2 = 24
分析：最终执行的条数，虽然是一致的。但是中间过程，有一张临时表是6，一张临时表是12，很明显6 < 12，对于内存来说，数据量越小越好，因此优化器肯定会选择第一种执行顺序。
结论：id值相同，从上往下顺序执行。表的执行顺序因表数量的改变而改变。
② 案例：查询教授SQL课程的老师的描述(desc)
# 查看执行计划
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 c.cname = 'sql')
);
结果如下：
结论：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') ;
结果如下：
结论：id值有相同，又有不同。id值越大越优先；id值相同，从上往下顺序执行。
2）select_type关键字的使用说明：查询类型
explain select * from teacher;
结果如下：
② primary：包含子查询的主查询(最外层)
③ subquery：包含子查询的主查询(非最外层)
④ derived：衍生查询(用到了临时表)
a.在from子查询中，只有一张表；
b.在from子查询中，如果table1 union table2，则table1就是derived表；
explain select cr.cname
from ( select * from course where tid = 1 union select * from course where tid = 2 ) cr ;
结果如下：
① system
源表只有一条数据(实际中，基本不可能)；
衍生表只有一条数据的主查询(偶尔可以达到)。
② const
仅仅能查到一条数据的SQL ,仅针对Primary key或unique索引类型有效。
explain select tid from test01 where tid =1 ;
结果如下：
create index test01_index on test01(tid) ;
# 再次查看执行计划
explain select tid from test01 where tid =1 ;
结果如下：
delete from teacher where tcid >= 4;
alter table teacherCard add constraint pk_tcid primary key(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 ;
结果如下：
总结：以上SQL，用到的索引是t.tcid，即teacher表中的tcid字段；如果teacher表的数据个数和连接查询的数据个数一致（都是3条数据），则有可能满足eq_ref级别；否则无法满足。条件很苛刻，很难达到。
④ ref
非唯一性索引，对于每个索引键的查询，返回匹配的所有行（可以0，可以1，可以多）
准备数据：
# 添加索引
alter table teacher add index index_name (tname) ;
# 查看执行计划
explain select * from teacher where tname = 'tz';
结果如下：
⑤ range
检索指定范围的行 ,where后面是一个范围查询(between, >, <, >=, in)
in有时候会失效，从而转为无索引时候的ALL
# 添加索引
alter table teacher add index tid_index (tid) ;
# 查看执行计划：以下写了一种等价SQL写法，查看执行计划
explain select t.* from teacher t where t.tid in (1,2) ;
explain select t.* from teacher t where t.tid <3 ;
结果如下：
⑥ index
查询全部索引中的数据(扫描整个索引)
⑦ ALL
查询全部源表中的数据(暴力扫描全表)
# 先给course表的cname字段，添加一个索引
create index cname_index on course(cname);
# 查看执行计划
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') ;
结果如下：
有一点需要注意的是：如果possible_key/key是NULL，则说明没用索引。
5）key_len
索引的长度，用于判断复合索引是否被完全使用(a,b,c)。
① 新建一张新表，用于测试
# 创建表
create table test_kl
(
name char(20) not null default ''
);
# 添加索引
alter table test_kl add index index_name(name) ;
# 查看执行计划
explain select * from test_kl where name ='' ;
结果如下：
结果分析：如果索引字段可以为null，则mysql底层会使用1个字节用于标识。
③ 删除原来的索引name和name1，新增一个复合索引
# 删除原来的索引name和name1
drop index index_name on test_kl ;
drop index index_name1 on test_kl ;
# 增加一个复合索引
create index name_name1_index on test_kl(name,name1);
# 查看执行计划
explain select * from test_kl where name1 = '' ; --121
explain select * from test_kl where name = '' ; --60
结果如下：
# 新增一个字段name2，name2可以为null
alter table test_kl add column name2 varchar(20) ;
# 给name2字段，设置为索引字段
alter table test_kl add index name2_index(name2) ;
# 查看执行计划
explain select * from test_kl where name2 = '' ;
结果如下：
结果分析：key_len = 20 + 1 + 2，这个20 + 1我们知道，这个2又代表什么呢？原来varchar属于可变长度，在mysql底层中，用2个字节标识可变长度。
6）ref
这里的ref的作用，指明当前表所参照的字段。
注意与type中的ref值区分。在type中，ref只是type类型的一种选项值。
# 给course表的tid字段，添加一个索引
create index tid_index on course(tid);
# 查看执行计划
explain select * from course c,teacher t
where c.tid = t.tid
and t.tname = 'tw';
结果如下：
explain select *
from course c,teacher t
where c.tid = t.tid
and t.tname = 'tz' ;
结果如下：
8）extra
表示其他的一些说明，也很有用。
① using filesort：针对单索引的情况
当出现了这个词，表示你当前的SQL性能消耗较大。表示进行了一次“额外”的排序。常见于order by语句中。
什么是“额外”的排序？
为了讲清楚这个，我们首先要知道什么是排序。我们为了给某一个字段进行排序的时候，首先你得先查询到这个字段，然后在将这个字段进行排序。
紧接着，我们查看如下两个SQL语句的执行计划。
# 新建一张表，建表同时创建索引
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 ;
explain select * from test02 where a1 ='' order by a2 ;
结果如下：
# 删除test02的索引
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) ;
# 查看下面SQL语句的执行计划
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 ;
结果如下：
结果分析：复合索引的顺序是(a1,a2,a3)，可以看到a1在最左边，因此a1就叫做“最佳左前缀”，如果要使用后面的索引字段，必须先使用到这个a1字段。对于explain1，where后面我们使用a1字段，但是后面的排序使用了a3，直接跳过了a2，属于跨列；对于explain2，where后面我们使用了a2字段，直接跳过了a1字段，也属于跨列；对于explain3，where后面我们使用a1字段，后面使用的是a2字段，因此没有出现【using filesort】。
③ using temporary
当出现了这个词，也表示你当前的SQL性能消耗较大。这是由于当前SQL用到了临时表。一般出现在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
结果如下：
编写过程
select dinstinct ..from ..join ..on ..where ..group by ..having ..order by ..limit ..
解析过程
from .. on.. join ..where ..group by ..having ..select dinstinct ..order by ..limit ..
很显然，where后是group by，然后才是select。基于此，我们再查看如下两个SQL语句的执行计划。
explain select * from test03 where a2=2 and a4=4 group by a2,a4;
explain select * from test03 where a2=2 and a4=4 group by a3;
分析如下：对于第一个执行计划，where后面是a2和a4，接着我们按照a2和a4分组，很明显这两张表已经有了，直接在a2和a4上分组就行了。但是对于第二个执行计划，where后面是a2和a4，接着我们却按照a3分组，很明显我们没有a3这张表，因此有需要再来一张临时表a3。因此就会出现using temporary。
④ using index
当你看到这个关键词，恭喜你，表示你的SQL性能提升了。
using index称之为“索引覆盖”。
当出现了using index，就表示不用读取源表，而只利用索引获取数据，不需要回源表查询。
只要使用到的列，全部出现在索引中，就是索引覆盖。
# 删除test02中的复合索引idx_a1_a2_a3
drop index idx_a1_a2_a3 on test02;
# 重新创建一个复合索引idx_a1_a2
create index idx_a1_a2 on test02(a1,a2);
# 查看执行计划
explain select a1,a3 from test02 where a1='' or a3= '' ;
explain select a1,a2 from test02 where a1='' and a2= '' ;
结果如下：
explain select a1,a2 from test02 where a1='' or a2= '' ;
explain select a1,a2 from test02;如果用到了索引覆盖(using index时)，会对possible_keys和key造成影响：
a.如果没有where，则索引只出现在key中；
b.如果有where，则索引 出现在key和possible_keys中。
⑤ using where
表示需要【回表查询】，表示既在索引中进行了查询，又回到了源表进行了查询。
# 删除test02中的复合索引idx_a1_a2
drop index idx_a1_a2 on test02;
# 将a1字段，新增为一个索引
create index a1_index on test02(a1);
# 查看执行计划
explain select a1,a3 from test02 where a1="" and a3="" ;
结果如下：
结果分析：我们既使用了索引a1，表示我们使用了索引进行查询。但是又对于a3字段，我们并没有使用索引，因此对于a3字段，需要回源表查询，这个时候出现了using where。
⑥ impossible where(了解)
当where子句永远为False的时候，会出现impossible where
# 查看执行计划
explain select a1 from test02 where a1="a" and a1="b" ;
结果如下：
原创文章，作者：修行
，如若转载，请注明作者昵称：修行及出处：https://www.xiuxingstudio.com/computer/database/2785.html
