Mysql性能优化之正确使用in和exists

我们在平时写sql的时候经常会用到in和exists这两个关键字，并且在大多数时候这两个关键字是可以达到相同的目的。那么在什么情况下使用in，什么情况下使用exists才会使得我们的sql查询效率最高呢？

实际上，in关键字是将外表和内表做哈希连接（hash join），而exists关键是是对外表做一次循环（loop），然后每次再对内表进行查询。

知晓了原理之后，我们再来举个实际的例子：

select * from member t1 where t1.id in (select t2.memberid from account t2);

select * from member t1 where exists (select 1 from account t2 where t2.memberid=t1.id);

我们假设该有的索引我们都建立的前提下，来分析sql语句执行的效率：

第一句sql的用到了t1.id这个索引，但是t2表是全表扫描，它的执行顺序是：先全表扫描t2表，将结果缓存起来，然后再遍历t1表将满足条件的记录加入结果集，我们可以得出这句sql需要执行的次数为t2表的记录数乘以t1表的记录数。

第二句sql用到了t2.memberid这个索引，并且不需要缓存exists子语句中的结果集，所以它需要执行的次数为t1表的记录数。

因此使用exists关键字的效率是优于in关键字的，不过当子语句中的结果集非常小的时候，两者效率相差不大。

那么除了in和exists，对应的还有not in和not exists，我们同样具个例子分析一下。

select * from member t1 where t1.id not in (select t2.memberid from account t2);

select * from member t1 where not exists (select 1 from account t2 where t2.memberid=t1.id);

根据对in和exists的分析思路，我们就可以得出以下结论：

not in在sql上的实际表现就是多个”x!=y and x!=z”的组合，我们知道”!=”是不会走索引的，所以not in在任何情况下都不会使用索引。

而对于not exists，它实际是和exists一样，使用到了t2.memberid这个索引，并且不会缓存exists的结果集，所以它的执行次数为t1表的记录数。

所以，对于not exists关键字来讲，在任何情况下它的执行效率都是高于not in关键字的。