④ 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 ;
结果如下:
⑤ union:union之后的表称之为union表,如上例
⑥ union result:告诉我们,哪些表之间使用了union查询
3)type关键字的使用说明:索引类型
type关键字可以很好的说明,你写的SQL语句好不好。system、const是我们达不到的理想状况,实际上只能优化到index --> range --> ref这个级别,也可以看到ALL是最差的一个级别。 对type进行优化的前提,是你得创建索引。
① 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 ;
结果如下:
可以发现:当tid字段去掉主键索引,换为普通索引后,优化级别就不是const了。
③ eq_ref
唯一性索引,对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多 、不能0),并且查询结果和表中数据条数必须一致。
此种情况常见于唯一索引和主键索引。
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
查询全部源表中的数据(暴力扫描全表)
注意:cid是索引字段,因此查询索引字段,只需要扫描索引表即可。但是tid不是索引字段,查询非索引字段,需要暴力扫描整个源表,会消耗更多的资源。