sql优化之in与exists

在我们开发过程中，初期可能不会去太关注我们自己写的sql语句的效率怎么样，因为总是觉得可以拿取到数据就算是ok了，可能也会注意一下你的sql执行的时间，但是在开发的过程中因为数据量的原因，其实你不去了解一下sql的一些优化的手段其实是无法感知你的sql的效率问题。我之前博客也写过一篇关于sql的优化一些需要注意的点，而我们本节主要是针对in与exists的区别以及什么时候使用in，什么时候使用exists。

in与exists的区别

• in:in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，他是先将数据督导内存中，然后取与外表匹配。他要执行的次数是外表的长度*内表结果的长度
• exists:exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引，他只需要执行的次数是外表的长度。
  可能说完之后还是一脸懵逼，那么接下来我们会通过一个简单的例子来加以说明：
  首先我们有a表和b表
  a表有以下几个字段
  id（学生id），name（学生姓名），age（学生年龄）
  b表有以下几个字段
  course（课程名称），studentId（学生ID）
  规定一个学生可以选择n们课程，然后我们需要查出学修了某些课程的所有学生的信息，
  通常对于上面的东西，我们有两种方式实现:
  使用in方式实现

   select * from a where id in(select studentId where name='xxx')

使用exists实现

 select * from a where exists (select studentId where name='xxx' and a.id=studentId)

in想必大家用的很多吧，原理也是很简单就是和我们上面一样，而exists的原理就不一样了，他是通过子查询返回的数据是否为null，如果不为null，那么就会将当前的数据加入结果集，因此我们select * from a的时候，我们是从第一条数据开始执行的，每次执行都会去执行exists的子查询，如果不为null就加入结果集，反之则不加入，直到最后执行完主查询为止。

可能我们从网上去找sql优化的时候就会发现他会发现他们都建议我们将in换成exists。但是在仔细去找的时候，又会发现使用in和exists是分不同的场景的，主要是以下场景：
1、a表的数据少于b表的数据那么就使用exists
2、如果a标的数据多余b表的数据，那么就使用in
3、如果一样其实是没有区别的
然后我就按照这个逻辑去弄了，顺便调试了下，发现没有什么区别（这个是数据量很少很少的情况下），然后我就闲着没事，我就去增加了大数据（这时候a表的数据为2w条，b表的数据为（30w条）），这时候我就继续去用这两种方法去做实验，如果按照我们之前的逻辑的话，那么就必须使用exists来，但是事实上我发现了使用exists时的查询耗时的时间是in的十几倍，这时候我就纳闷了，
使用in查询结果耗时

使用exists查询结果耗时：

这时候我就猜想他说b表数据要大于a表的意思是不是指查找过后的数据，我就抱着这种想法去试一下，这时候我在次增加了b表的数据，但是这次是增加某门课程的数据（不同的学生）增加了10w条，然后我在次使用exists和in去查找，这个时候就发现exists的查询效率比in高好多。然后发现别人是不是写错了，
事实上别人没有写错，只是我们误解了，既然子查询查出来的数据要大于a表，那么b表的数据也就一定高于a表，因此他们的说发也没问题。
使用in的场景：a表的数据大于b表的数据（关联查询后的数据）
使用 exists的场景：a表的数据小于b表的数据（关联查询后的数据）
如果数据量相同in和exists都可以使用

既然说到了in和exists，那么我们就顺便带过一下not in 和not exists。
当我们要使用not in的时候建议直接将他替换成not exists，不论a表的数据与b表的数据相差多大。
有的人会问为什么？
其实很简单。我们数据库只会告诉你数据在哪里，而不会告诉你数据不在哪里，因此使用not in一定会导致索引失效，那 not exists为什么不会失效呢？因为not exists其实就相当于我们查询出来的数据取反，即有和无，他本身是不参与sql的执行，因此他是不会导致所以失效的。