MySQL 对于explain中possible_key，与key的思考

friendly e

已于 2022-10-15 17:53:53 修改

阅读量3.6k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-10-15 17:53:33 首次发布

本文链接：https://blog.csdn.net/weixin_59141600/article/details/127337292

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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得到相应的值。