面试中,MySQL中existsin的区别是一个常见的问题。我看大部分的八股文中,一般只是简单抛出结论:

子查询表小的用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)。

  • 如何选用existsin

    • 当两个表的大小相当时,用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也可以更小,因为小表中的记录数更少

参考:

Logo

开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!

更多推荐