Explain参数解析
explain的所有参数
id： 编号
select_type: 查询类型
table: 表
type： 类型
possible_keys:预测用的索引
key: 实际使用到的索引
key_len: 实际使用的索引的长度
ref： 表之间的引用
rows: 通过索引查询到的数据两
Extra： 额外的信息
插入测试的SQL语句
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');
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表（3条数据），再执行teacherCard表（3条数据），再执行course表（4条数据）
INSERT INTO teacher VALUES(4,'ta',4);
INSERT INTO teacher VALUES(5,'tb',5);
INSERT INTO teacher VALUES(6,'tc',6);
查询表的顺序变了，
表的执行顺序，引数量的个数改变而改变：笛卡尔积（数据量小的表优先计算，针对id值相同的情况）
再删除course的两条数据
EXPLAIN
SELECT tc.tcdesc FROM teacherCard tc WHERE tc.tcid = (SELECT tid FROM teacher t WHERE t.tid=
(SELECT c.tid FROM course c WHERE c.cname='java')
)
id值不同：id值越大越优先查询 id值相同，从上往下执行 。有相同，有不同，先根据id大小，再根据从上往下
2.select_type的分析（查询类型）
PRIMARY: 包含子查询SQL中的主查询 （最外层）
SUBQUERY：包含子查询SQL中的子查询（非最外层）
SIMPLE： 简单查询（不包含子查询，union ----EXPLAIN SELECT * FROM teacher ）
DERIVED： 衍生查询（使用到了临时表）
a.from子查询只有一张表
EXPLAIN
SELECT cr.cname FROM ( SELECT * FROM course WHERE tid IN(1,2) ) cr
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：上例
union result:上例
3.type的分析（索引类型）
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique>subquery>index_subquery
优化的前提是有索引
system>const>eq_ref>ref>range>index>all
其中：system，const只是理想情况，实际能达到 ref>range
system：只有一条数据的系统表：或衍生表只有一条数据的主查询（忽略）
CREATE TABLE test01( tid INT(3),tname VARCHAR(20)) 主键tid
INSERT INTO test01 VALUE(1,'a')
explain
select * from (select * from test01) t where tid=1
const：仅仅能查到一条数据的sql，用于primay（必须） 获 unique 索引 （类型与索引类型有关）
explain
select * from test01 where tid='1'
alter table test01 drop primary key
alter table test01 add index bobo(tid) 如果不是主键索引，则不是const
eq_ref：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多，不能少）
常见于唯一或主键索引
EXPLAIN SELECT t.tcid FROM teacher t,teacherCard tc WHERE t.tcid = tc.tcid
查询的结果和数据的条数都是一直，如果teacher表的数据个数 和 连接查询的数据个数都是一致（都是3条数据 ）
ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
准备数据
INSERT INTO teacherCard VALUE (4,'tz222',4)
给tname添加索引
EXPLAIN SELECT * FROM teacher WHERE tname = 'tz'
range：检索指定范围的行，where后面是一个范围查询 （between,> < >=, 特殊in有时候会失效，转为无索引）
EXPLAIN SELECT * FROM teacher t WHERE t.tid IN (1,2)
EXPLAIN SELECT * FROM teacher t WHERE t.tid <3
EXPLAIN SELECT * FROM teacher t WHERE t.tid BETWEEN 1 AND 3
index：查询全部索引中数据
EXPLAIN SELECT tid FROM teacher
ALL:查询全部表中的数据
EXPLAIN SELECT cid FROM course
（4）possible_keys : 可能用到的索引 ，有时候准。有时候不准
(5) key : 实际使用到的索引
（6）key_len: 可能用到的索引 ，有时候准。有时候不准
索引的长度是否被完全使用 （a,b,c）
CREATE TABLE test_k1(
NAME CHAR(20) NOT NULL DEFAULT ''
)
EXPLAIN SELECT * FROM test_k1 WHERE NAME =''"
utf中：一个字符占3个字节
EXPLAIN SELECT * FROM test_k1 WHERE name1 =''"
删除表的两个索引后
ALTER TABLE test_k1 ADD INDEX name_name1_index(NAME,name1);
EXPLAIN SELECT * FROM test_k1 WHERE NAME =''"
EXPLAIN SELECT * FROM test_k1 WHERE name1 =''
EXPLAIN
SELECT * FROM test_k1 WHERE name2 =''"
20*3 + 1null +2（代表可变长度）
utf: 1个字符占3个字节
gbk： 1个字符占2个字节
latin：1个字符占1个字节
（7）ref:注意与type中的ref区分
作用:指明当前表所 参照的 字段
select ... where a.c = b.x ;(其中b.X可以是常量，const)
EXPLAIN SELECT * FROM course c,teacher t WHERE c.tid = t.tid AND t.tname ='tw'
（8）rows ：被索引优化查询的 数据个数
（9）Extra：
(i) using filesort：性能消耗大：需要额外的一次排序（查询 常见于orderby）
小结：对于单索引，如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找的不是同一个字段，则会
EXPLAIN SELECT * FROM test01 WHERE a1='' ORDER BY a1
EXPLAIN SELECT * FROM test01 WHERE a1='' ORDER BY a2
复合索引
给a1，a2，a3增加复合索引
explain select * from test02 where a1='' order by a3
EXPLAIN SELECT * FROM test01 WHERE a1='' ORDER BY a3 ---filesort
EXPLAIN SELECT * FROM test01 WHERE a2='' ORDER BY a3 ---filesort
EXPLAIN SELECT * FROM test01 WHERE a1='' ORDER BY a2 ---无 filesort
where哪些字段，就order by哪些字段
避免：where 和order by按照复合索引的顺序使用，不要跨列或无序使用
(ii) using temporary：性能损耗大，用到了临时表 （常见于 group by 中）
已经有表了，却不是使用，有创建出来一张表
EXPLAIN SELECT * FROM `test02` WHERE a1 IN ('1','2','3') GROUP BY a1
EXPLAIN SELECT * FROM `test02` WHERE a1 IN ('1','2','3') GROUP BY a2
根据a1查，却不根据a1分组，查询那些列，旧根据那些列group by
EXPLAIN SELECT * FROM `test02` WHERE a1=2 AND a2=3 GROUP BY a2,a2
这是没有using Temporary
EXPLAIN SELECT * FROM `test02` WHERE a1=2 AND a2=3 GROUP BY a3
核心：额外多使用一张表
(iii) using Index 性能提升了，索引覆盖了，原因，不读取源文件，只从索引文件中获取数据，不需要回表查询
例如test02中有一个复合索引（a1,a2）
EXPLAIN SELECT a1,a3 FROM `test02` WHERE a1 ='' OR a3=''
EXPLAIN SELECT a1,a3 FROM `test02` WHERE a1 ='' OR a3=''
EXPLAIN SELECT a1,a3 FROM `test02`
有where possible_keys为null，没有则 possible key 和 key 都不为空
(iv) using where 会回原表中查
（V）impossible where： where子句永远为false
EXPLAIN SELECT * FROM test02 WHERE a1=1 AND a1=2
创建test04（a1,a2,a3,a4）
EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a2=2 AND a3=3 AND a4=4
EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a4=4 AND a2=2 AND a3=3
虽然你写的sql 不太好，优化器会自动帮你改
EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a2=2 AND a4=4 order by a3
刚好 1 2 4（失效） 3 没有跨列
a1，a2不需要回表查询using index，而a4
因为失效了，需要回表查询所以使用了using where
EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a4=4 ORDER BY a3
刚好 1 4（失效） 3 跨列了所以用到了using filesort
以上sql多了一次额外的排序和查找，不需要跨列使用，EXPLAIN SELECT a1,a2,a3,a4 FROM test03 WHERE a1=1 AND a4=4 ORDER BY a2,a3
没有using filesort
总结：如果（a,b,c,d）复合索引，和使用的顺序全部一直，则复合索引全部使用。如果部分一致，则部分使用
