MySQL索引. ref_MySQL索引类型（type）分析

╰半夏微凉° 2023-01-12 07:59 155阅读 0赞

type索引类型：

ststem > 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 teacheradd constraint tid\_pk primary key(tid);

1.system

衍生表只有一条数据的主查询；衍生表通过主键查询只有一条数据，再通过这条数据进行主查询。

\# 子查询为主键查询

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

子查询通过主键查询得出一条数据(该条数据构成衍生表)，通过衍生表的数据进行主查询，其衍生表的索引类型为system。

![b2998771304c1bc5c628f86aaa3f4017.png][]

2.const

仅仅能查到一条数据的SQL，用于primary key 或 unique的索引(其他索引类型不属于)。

\# 主键查询只有一条数据的情况，类型为const

explain select \* from test01 where tid = 1;

![910a0268956d088f9a0b558f243647a9.png][]

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：

![54f5effdb0adafe6b0ab6f2a5d8412c5.png][]

4.ref

非唯一线性索引，对于每个索引键的查询返回的数据为0或多条。

\# 给teacher表的tname的字段添加索引

alter table teacher add index tname\_index (tname);

\# 根据tname = tz查询出两条数据

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

根据tname索引直接查询出来的值为ref类型。

![57d9a9734ac5a2a1c9138b75440f7aef.png][]

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：

![fd7fe306e58a0180fd6c3d4a8a8f2b03.png][]

6.index

查询索引中的所有数据。

\# 查询索引的所有数据，其中tname就是索引

explain select tname from teacher;

![13ad003f83f53a65bbf54e3697849af4.png][]

7.all

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

\# 直接查询

explain select \* from teacher;

type类型总结：

system/const：结果只有一条数据。

eq\_ref：结果多条，但是每条数据都是唯一的。

ref：结果多条，但是查询到的数据可以是多条，且数据可以重复。

range：根究索引字段进行范围查询。

[b2998771304c1bc5c628f86aaa3f4017.png]: /images/20221119/ccbaad296d1847fdaa25ddb9a9cd2357.png
[910a0268956d088f9a0b558f243647a9.png]: /images/20221119/d904247ec92349a8ba9e9eb0d01a30ff.png
[54f5effdb0adafe6b0ab6f2a5d8412c5.png]: /images/20221119/8ea7c5d5c141476f94a7cbec3b1a3cc2.png
[57d9a9734ac5a2a1c9138b75440f7aef.png]: /images/20221119/c780a2032a3d4de9b6f5e3c7a5a0478c.png
[fd7fe306e58a0180fd6c3d4a8a8f2b03.png]: /images/20221119/62065709196b4cd6ac785ab2dc29d64d.png
[13ad003f83f53a65bbf54e3697849af4.png]: /images/20221119/105c18d59dff4f56812155a3da24641c.png