1、possible_key 与 key
possible_key:显示可能应用在这张表上的索引,一个或者多个
key:实际使用的索引,如果为NULL,则没有使用索引,即全局扫描
2、数据准备
分别创建student、course、student_course表:
create table student
(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
insert into student
values (null, '黛绮丝', '2000100101'),
(null, '谢逊', '2000100102'),
(null, '殷天正', '2000100103'),
(null, '韦一笑', '2000100104');
create table course
(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '课程名称'
) comment '课程表';
insert into course
values (null, 'Java'),
(null, 'PHP'),
(null, 'MySQL'),
(null, 'Hadoop');
create table student_course
(
id int auto_increment comment '主键' primary key,
studentId int not null comment '学生ID',
courseId int not null comment '课程ID',
constraint fk_courseid foreign key (courseId) references course (id),
constraint fk_studentid foreign key (studentId) references student (id)
) comment '学生课程中间表';
insert into student_course
values (null, 1, 1),
(null, 1, 2),
(null, 1, 3),
(null, 2, 2),
(null, 2, 3),
(null, 3, 4);
执行以下操作:
select s.*,c.* from student s,student_course sc,course c where s.id = sc.studentId and c.id = sc.courseId;
s.id = sc.studentId
3、然后执行explain进行分析:
explain select s.*,c.* from student s,student_course sc,course c where s.id = sc.studentId and c.id = sc.courseId;
注释:id值相同时,执行表的顺序是自上而下,所以是 s -> sc -> c
由于第一个where语句为:
s.id = sc.studentId
(1) id为student的表,所以当前student表可能使用到的索引possible_key为PRIMARY,为什么实际上key的值为NULL呢?
我认为:为了搜索学生所选修的课程,要遍历student_course表,不需要用到索引,故为NULL
(2)sc.studentId 和 sc.courseId 故系统觉得可能会用到相应的索引possible_keys 为 fk_courseid 和fk_studentid ,但是sc.studentId 和 sc.courseId是被查询的,所以无需用到索引值
(3)再对于c表,c.id 系统认为可能用到的索引possible_key 为 PRIMARY ,实际上确实用到了,为什么它不像(1)呢?
因为s.id = sc.studentId 一旦成立,那么学生在学生选课表student_course中肯定有一个记录,这样c.id = sc.courseId就不用遍历了,根据主键就可以索引到相应的课程了。
再来一个:
select * from student s where s.id in (select studentId from student_course sc where sc.courseId = (select id from course c where c.name = 'MySQL'));
explain select * from student s where s.id in (select studentId from student_course sc where sc.courseId = (select id from course c where c.name = 'MySQL'));
注释:🆔不同的时候,表从大到小执行, c -> sc -> subquery2 -> s
(1)第一个子查询c.name 无索引,并且是全搜,possible_key = key = null
(2)子查询查出来的值为MySQL课程的id值,sc可能用到的索引possible_key为fk_courseid 、fk_studentid,由sc.courseId = (subquery)可以,通过courseId的索引在选课表student_course中唯一确认一条记录,所以实际用到的索引key为fk_courseid
(3) 生成一个子查询表subquery2
(4)s.id = <subquery2>.studentId,可能会用到的索引possible_key为PRIMARY,由于<subquery2>表中的studentId是唯一的,因为每个人在同一时刻不能同时有两次相同的选课记录,所以可以根据主键索引到s表相应的学生信息记录,故其实际了PRIMARY作为key。
补充:eq_ref:唯一索引或主键查询
,对应每个索引建,表中只有一条记录与之匹配 【A表扫描每一行B表只有一行匹配满足】,所以type符合eq_ref
ref : 与索引比较的列,所以s.id = <subquery2>.studentId 的意思是,s.id 是与<subquery2>.studentId得到相应的值。