By

Contents

数据库版本
MySQL 5.6.26

1、先看两个表的数据

TABLE IF EXISTS `course`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `course` (
  `student_id` int(11),
  `course` varchar(20)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `course` VALUES ('1', '标题');
INSERT INTO `course` VALUES ('2', '标题');
INSERT INTO `course` VALUES ('3', '标题');

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11),
  `name` varchar(20)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('1', 'lily');
INSERT INTO `student` VALUES ('2', 'lucy');
INSERT INTO `student` VALUES ('3', 'nacy');
INSERT INTO `student` VALUES ('4', 'hanmeimei');

2、奇怪的sql查询结果

3、为什么course中不存在id或者name字段，子查询不报错；

4、实际上是不是这样的

5、整条语句中子查询返回的是什么？

Mysql 子查询的实现并不是我们想象的那样。

1
select * from student where id in (select student_id from course);

对于上面的sql，我们一般会认为是这样执行:
猜想1:

1
2
3
4
select student_id from course;
result: 1,2,3

select * from student where id in(1,2,3)

事实上不是这样，Mysql会把外层表压入到子查询中，具体的执行计划可以使用

explain extended查看：

1
2
3
4
5
6
7
8
9
EXPLAIN EXTENDED
select * from student where id in (select student_id from course);

SHOW WARNINGS;

select `test`.`student`.`id` AS `id`,`test`.`student`.`name` AS `name` 
from `test`.`student` 
where <in_optimizer>(
`test`.`student`.`id`,<exists>(select 1 from `test`.`course` where (<cache>(`test`.`student`.`id`) = `test`.`course`.`student_id`)));

Mysql处理后等价于：

1
select * from student where exists (select 1 from course where course.student_id = student.id);

查看执行计划，可以看到mysql对于student表全表扫描，然后按照id逐条执行子查询。这和我们的猜想1完全相反。

到现在并没有解释LZ的问题，我们可以猜想下。
猜想2:既然是先全表扫描然后逐条子查询，是不是子查询的变量都是来自全表扫描时的局部变量？我们再看一下执行计划：

EXTENDED
1
2
3
4
5
select * from student where id in (select id from course);

show WARNINGS;

select `test`.`student`.`id` AS `id`,`test`.`student`.`name` AS `name` from `test`.`student` where <in_optimizer>(`test`.`student`.`id`,<exists>(select 1 from `test`.`course` where (<cache>(`test`.`student`.`id`) = `test`.`student`.`id`)));

通过查询计划可以清晰的看到，真正执行的时候，子查询的变量都是全表扫描时的局部变量student.id，这也就解释了“为什么course中不存在id或者name字段，子查询不报错”，是因为查询的优化，导致根本不是使用的course的id或者name字段。

Contents