Mysql的优化（一）嵌套查询与连接查询对比

缘由

一个我喜欢的小姐姐突然咨询了我关于连接查询与嵌套查询该使用哪个，我只能通过我的多年秃头经验给出要使用链连接查询，但是我并不能讲出准确的原因，事后粗浅了解了原理后记录下来给小姐姐，毕竟不能丢了我舔狗的尊严，如有不对地方欢迎大家指出。

变强警告

问题

我们在查询多个表的时候常用的两种查询方式

表

没有添加任何索引

连接查询

SELECT
	student.student_name 
FROM
	student
	JOIN score ON student.id = score.student_id 
WHERE
	score.score_num > 10

嵌套查询

SELECT
	student_name 
FROM
	student 
WHERE
	id IN (
	SELECT student_id FROM score WHERE score_num > 10)

这两种应该选择哪个呢？

数据库查询原理与流程

弄清楚这个答案之前，我们先粗略了解下数据库查询流程

其他的我们暂且不谈，这里面第四步是优化器这是这次的重点。我们所写的sql语句不是我们写什么样，数据库就执行什么样，数据哭不相信我们，他需要经过他的优化器，优化成他认为成本最小的语句（比如cpu占用率最小等等），但有时候这个成本最小的语句并不是我们理解的查询最快的语句。

对比

我们先看下这两种的数据库执行计划，mysql可以通过explain命令进行你的sql语句预执行计划来帮助你分析调优sql。

连接查询

从这里可以看出连接查询使用了两步执行计划。一步all，一步ref

嵌套查询

从这里可以看出嵌套查询执行计划分成两组三步，两步是ALL，一步是eq_ref。

但是各位看官有没有发现，他是先走的扫描student，再走的扫描score。这和我们猜想的先走括号里面的缩小查询范围的猜想不一样（毕竟不是数学题，先计算括号里面的）。
这就是优化器做的好事，这个嵌套查询语句在数据库进行执行的时候被优化器由外到内给优化啦。
优化后结果

SELECT
	student.student_name 
FROM
	student 
WHERE
	EXISTS 
	( SELECT * FROM score WHERE score.score_num > 10 AND score.student_id = student.id );

大家可以尝试下去执行这个sql的执行计划，你会发现一模一样。

关于这个EXISTS这个比较少用的关键字的用法大家可以自己去查询一下，毕竟我都解释清楚啦，我的小姐姐就不会打电话过来问我啦。

这就导致了我们想缩小查询范围达到提高查询效率的初衷得以落空，反而如果外表（就是我这里的student表，不是指小姐姐的外表）比较大的话会大大拖累我们sql执行效率。
同时执行子查询，会产生一张所谓的虚表（临时表），存在对性能的浪费。
嵌套查询也不方便我们同时查询多个表的数据。
如果你关联的表更多了之后，你会发现对比更加明显。

读后思考

如果我们给score或者student添加上合适的索引，比如score的student_id添加索引会怎么样，该如何抉择