type索引类型:
system > const > eq_ref > ref > range > index > all优化级别从左往右递减,没有索引的⼀般为’all’,需要对type进⾏优化前提是有索引。其中’system’和’const’只是理想型,实际只能达到’ref’和’range’。
注意:这⾥主要针对MySQL5.6进⾏讲解,与其他版本有区别,但是原理过程⼀致。
创建两张表teacher和teacherCard
# 教师表
create table teacher(
tid int(5),
tname varchar(20),
tcid int(5)
);
# 教师卡
create table teacherCard(
tcid int(5),
tcdesc varchar(20)
);
# 插⼊教师信息
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');
# 教师表添加主键索引
alter table teacher add constraint pk_tid primary key(tid);
1.system
衍⽣表只有⼀条数据的主查询;衍⽣表通过主键查询只有⼀条数据,再通过这条数据进⾏主查询。
# 子查询为主键查询
explain select * from (select * from test01 where tid =1)
⼦查询通过主键查询得出⼀条数据(该条数据构成衍⽣表),通过衍⽣表的数据进⾏主查询,其衍⽣表的索引类型为system。
2.const
仅仅能查到⼀条数据的SQL,⽤于primary key 或 unique的索引(其他索引类型不属于)
# 主键查询只有⼀条数据的情况,类型为 const
explain select * from test01 where tid =1
3.eq_ref
唯⼀性索引,表索引与另外表的主键进⾏关联,两张表之间每条数据要⼀⼀对应(每个都要⼀⼀对应,不能⼀个对应多个,不能没有对应),查询的数据在表中是唯⼀性,不能有重复。
# 给 teacherCard 添加主键
alter table teacherCard add constraint pk_tcid primary key(tcid);
# 对 teacher 表进⾏索引唯⼀查询
explain select t.tcid from teacher t, teacherCard tc where t.tcid = tc.tcid;
主表(没有外键的表)为eq_ref:
4.ref
⾮唯⼀线性索引,对于每个索引键的查询返回的数据为0或多条。
# 给 teacher 表的 tname 的字段添加索引
alter table teacher add index tname_index (tname);
# 根据 tname = tz 查询出两条数据
explain select * from teacher where tname ='tz';
根据tname索引直接查询出来的值为ref类型。
5.range
检查指定范围⾏,where后⾯是⼀个范围查询(between、in、>、<、=等)。
# 查看range类型的索引
explain select * from teacher t where t.tid in (1,2);
explain select * from teacher where tid between 1 and 2;
explain select * from teacher where tid <3;
根据tname索引直接查询出来的值为ref类型。
6.index
查询索引中的所有数据。
#查询索引的所有数据,其中tname就是索引
explain select tname from teacher
7.all
查询表中的所有数据,或者根据不是索引的字段查询。
#直接查询
explain select * from teacher;
type类型总结:
system/const:结果只有⼀条数据。
eq_ref:结果多条,但是每条数据都是唯⼀的。
ref:结果多条,但是查询到的数据可以是多条,且数据可以重复。
range:根究索引字段进⾏范围查询。