从原理浅析MySQL中exists和in的区别（如何选用exists和in）

面试中，MySQL中exists和in的区别是一个常见的问题。我看大部分的八股文中，一般只是简单抛出结论：

子查询表小的用in，子查询表大的用exists。

---

为什么“子查询表小的用in，子查询表大的用exists”

为何如此呢？可以从原理角度来剖析：

以下两个语句执行结果是一样的：

select * from t1 where exists (select 1 from t2 where t1.id = t2.id);
select * from t1 where id in (select id from t2);

但执行过程的区别在于：

• exists子句会对外表（即t1）用loop逐条记录查询，每次查询都会查看exists中的select语句，如果select子句返回记录行（无论返回记录行是多少，只要能返回），exists就会返回true，则外表中的当前记录就会被检索出来；如果select子句没有返回记录行，exists就会返回false，则外表中的当前记录就会被丢弃。——exist子句循环每次取出外表中的一条记录用来执行exists中的语句查内表，是先查外表，再查内表（相关子查询）。
• in查询相当于多个or条件的叠加。in子句需要先将子查询的记录全部查出来。注意in子句中的子查询返回的结果集必须只有一个字段。假设子查询返回的结果集有m条记录，在进行m次查询。——in子句是先执行in中的子句查出来内表的结果，然后外表针对内表查出来的结果一个个遍历匹配。即先查内表，再查外表（不相关子查询）。

基于以上的认识：

• exists只有内表可以用上索引，外层循环必须要走一个遍历过程；而in内表和外表都可以用上索引，因为in本质上属于多个条件查询的并集(or)。

• 如何选用exists和in？

  • 当两个表的大小相当时，用exists和in的效率差别不大。
  • 如果两个表一个大一个小，则子查询表(即内表)大的用exists，子查询表(即内表)小的用in。
    • 其实就是”小表驱动大表“的思想：用exist时外表是驱动表，用in时内表是驱动表。
    • MySQL的外连接就利用了”小表驱动大表“的思想做自动优化，因此有时候会发现LEFT JOIN左侧的不是驱动表而是被驱动表，其实就是MySQL优化器的功能。同理，内连接也有类似的情况。

---

MySQL 中“小表驱动大表”的思想

最后感觉还可以补充提一下MySQL中采用的“小表驱动大表”的思想，下面是自己总结的一些点，关于如何理解MySQL的“小表驱动大表”，大家可以辩证的参考（因为我感觉这一块从定性分析的角度来看是比较模糊的，如果各位有心可以定量分析看看）：

• 可以减少被驱动表的IO次数。由于连接查询时一般采用嵌套循环的方法，则被驱动表会被遍历很多次。而驱动表和被驱动表都是会加入buffer pool中的，但一般情况下，一个buffer pool的容量是装不下整张表的若干数据页的，会用LRU算法变体来淘汰部分数据页（MySQL buffer pool 的 LRU）。所以下轮嵌套循环时，表中前面的记录对应的数据页早已被淘汰了，又要重新从内存中加载。所以每轮嵌套循环每加载一个数据页基本都是要做IO，而使用小表驱动大表可以减少嵌套循环的轮数进而减少被驱动表IO次数。
  • 事实上，并不能排除有可能小表会非常小，以至于一个buffer pool能完全装下，此时“大表驱动小表”可能反而会比“小表驱动大表”要好，因为此时被驱动表全程仅需一次IO（最初加入buffer pool的那次）。
• 可以减少全表扫描带来的锁定和阻塞的风险。无论是大表还是小表，我们都需要尽可能减少全表扫描的次数和持续时间，因为带锁查询时全表扫描会对遍历过的所有位置上锁，影响其它操作（update等）。使用大表做被驱动表，小表作为驱动表，可以减少对大表全表扫描的次数，已经是最优地避免全表扫描带来的负面影响的考虑了。
• 可以减少join buffer空间，节省内存资源。如果被驱动表上join的列没有索引，那么每轮嵌套循环必须要老老实实全表扫描完被驱动表一次，因此此时MySQL将采取优化策略将多轮嵌套循环合并为一轮。具体做法是一次取驱动表的多条记录放入一个叫 join buffer 的缓存区，然后嵌套循环中会直接将被驱动表中的每一条记录一次性与join buffer中的多条驱动表记录进行匹配。这样就减少了嵌套循环的轮数。而只要驱动表是小表，那么join buffer也可以更小，因为小表中的记录数更少。

---

参考：

• EXISTS 和 IN 的区别
• IN和EXISTS的区别和使用
• 《MySQL是怎样运行的》