MySQL索引类型（type）分析

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

子查询通过主键查询得出一条数据（该条数据构成衍生表），通过衍生表的数据进行主查询，其衍生表的索引类型为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;

范围查询指定的索引，其索引类型为range：

6.index

查询索引中的所有数据。

# 查询索引的所有数据，其中tname就是索引
explain select tname from teacher;

7.all

查询表中的所有数据，或者根据不是索引的字段查询。

# 直接查询
explain select * from teacher;

type类型总结：

• system/const：结果只有一条数据。
• eq_ref：结果多条，但是每条数据都是唯一的。
• ref：结果多条，但是查询到的数据可以是多条，且数据可以重复。
• range：根究索引字段进行范围查询。

参考链接：https://www.bilibili.com/video/BV1es411u7we?p=9