SQL优化 | IN和EXISTS谁的效率更高

有开发小哥咨询了一个问题,挺有意思的,记录一下分享给有需要的朋友。

问题:

IN和EXISTS是SQL使用的最多的语法之一,虽然作用是一样的,但是谁的效率更胜一筹呢?

先不着急下结论,下面我们就通过一组测试,看看在不同场景下,使用哪个效率更高。

测试

分析执行计划:

IN的执行计划:

  • 执行A表的查询,查询条件是A.flag在结果集B里面,可以使用到A表的索引flag;
  • 执行B表的子查询,得到结果集B,可以使用到B表的索引B_id。

EXISTS的执行计划:

  • 先将A表所有记录取到;
  • 逐行针对A表的记录,去关联B表,判断B表的子查询是否有返回数据;
  • 如果子查询有返回数据,则将A当前记录返回到结果集。

A相当于取全表数据遍历,B可以使用到索引。可以看到本次测试IN效率高于EXISTS。

再看执行计划:

IN的执行计划:

EXISTS的执行计划:

当子查询结果集很大,而外部表较小的时候,Exists采用了Block Nested Loop(Block 嵌套循环),查询效率会优于IN。

可以看到本次EXISTS效率比IN高。

IN原理在in的执行中,内表得到结果集,再和外表匹配。外表会对所有的内表结果集匹配,也就是说:如果外表有10,内表有1000,就会执行10*1000次。以此类推在内表比较大的时候,用in方法会效率比较低。

select * from 外表 a where id in (select 相关id from 内表) IN的执行类似如下:

EXISTS原理

exists的执行过程中,并没有对每一条内表的数据都进行查询,而是存在该条数据的时候会将结果集存起来,到最后的时候同一输出结果集。


select a.* from 外表 a where exists(select 1 from 内表 b where a.id=b.id) 的EXISTS的执行语句如下:

两个测试两个结果,不同业务场景要具体分析:

IN执行原理:IN是做外表和内表通过Hash连接,先查询子表,再查询主表,不管子查询是否有数据,都对子查询进行全部匹配。

EXISTS执行原理:EXISTS是外表做loop循环,先主查询,再子查询,然后去子查询中匹配,如果匹配到就退出子查询返回true,将结果放到结果集。

IN语法在内表和外表上都使用到索引;EXISTS查询仅内表上可以使用到索引,外表会全表扫描。

当子查询结果集很大,而外部表较小的时候,EXISTS的Block Nested Loop(Block 嵌套循环)的作用开始显现,查询效率会优于IN;否则IN 的外表索引优势占主要作用,此时IN的查询效率会优于EXISTS。

综上所述:子查询结果集越大用EXISTS,子查询结果集越小用IN。


觉得本文有用,请转发、点赞或点击“在看”聚焦技术与人文,分享干货,共同成长更多内容请关注“数据与人”

为您推荐

发表评论

您的电子邮箱地址不会被公开。