MySQL执行计划

查询执行计划：

explain +SQL语句
explain select * from tb ;

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

查询课程编号为2 或 教师证编号为3 的老师信息
explain +sql:
(1)id: id值相同，从上往下 顺序执行。 t3-tc3-c4

			  tc3--c4-t6

表的执行顺序 因数量的个数改变而改变的原因： 笛卡儿积

	a 	 b  	 c
	4	3	 2   =  			2*3=6 * 4   =24
							3*4=12* 2   =24

数据小的表 优先查询；
查询课程编号为2 或 教师证编号为3 的老师信息

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

多表链接 内连接（两表取交集），外链接（有主表，主表中有数据要展示，字表无匹配数据 展示为null）
多表连接 where 来做join 创建了两张表的笛卡尔积，所有可能的组合都被创建出来，在过滤正确的数据，多做很多工作
inner join 只产生目标结果

规则

• id: id值相同，从上往下 顺序执行。
• 数据小的表 优先查询；
• id值不同：id值越大越优先查询(本质：在嵌套子查询时，先查内层 再查外层)

type:索引类型

system>const>eq_ref>ref>range>index>all
其中：system,const只是理想情况；实际能达到 ref>range

system
只有一条数据的系统表 ；或 衍生表只有一条数据的主查询

explain select * from (select * from test01 )t where tid =1 ;

test01表中只有一条数据
const
仅仅能查到一条数据的SQL ,用于Primary key 或unique索引

explain select tid from test01 where tid =1 ;

eq_ref
唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）
select … from …where name = … .常见于唯一索引 和主键索引。

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，多）

explain select * from teacher 	where tname = 'tz';

range
检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ，从而转为 无索引all)

explain select t.* from teacher t where t.tid in (1,2) ;
explain select t.* from teacher t where t.tid <3 ;

index
查询全部索引中数据

explain select tid from teacher ; 

tid 是索引， 只需要扫描索引表，不需要所有表中的所有数据
all
查询全部表中的数据

explain select cid from course ;  

cid不是索引，需要全表所有，即需要所有表中的所有数据

总结：
system/const: 结果只有一条数据
eq_ref:结果多条；但是每条数据是唯一的 ；
ref：结果多条；但是每条数据是是0或多条 ；

Extra：

using filesort
性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。排序：先查询

explain select * from test02 where a1 ='' order by a2 ; --using filesort

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

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

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 ;
explain select *from test02 where a2='' order by a1 ; --using filesort

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

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

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

using index
性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据 （不需要回表查询）只要使用到的列 全部都在索引中，就是索引覆盖using index
例如：test02表中有一个复合索引(a1,a2,a3)

explain select a1,a2 from test02 where a1='' or a2= '' ; --using index   
		
drop index idx_a1_a2_a3 on test02;
alter table test02 add index idx_a1_a2(a1,a2) ;
explain select a1,a3 from test02 where a1='' or a3= '' ;--未使用using index 

using where
假设age是索引列
但查询语句select age,name from …where age =…,此语句中必须回原表查Name，因此会显示using where.

explain select a1,a3 from test02 where a3 = '' ; --a3需要回原表查询

impossible where

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