有开发小哥咨询了一个问题,挺有意思的,记录一下分享给有需要的朋友。
问题:
IN和EXISTS是SQL使用的最多的语法之一,虽然作用是一样的,但是谁的效率更胜一筹呢?
先不着急下结论,下面我们就通过一组测试,看看在不同场景下,使用哪个效率更高。
测试
![SQL优化 | IN和EXISTS谁的效率更高插图 SQL优化 | IN和EXISTS谁的效率更高插图](http://www.shujuyr.com/wp-content/uploads/2021/12/image-25.png)
![SQL优化 | IN和EXISTS谁的效率更高插图1 SQL优化 | IN和EXISTS谁的效率更高插图1](http://www.shujuyr.com/wp-content/uploads/2021/12/image-26.png)
分析执行计划:
IN的执行计划:
![SQL优化 | IN和EXISTS谁的效率更高插图2 SQL优化 | IN和EXISTS谁的效率更高插图2](http://www.shujuyr.com/wp-content/uploads/2021/12/9683499ca36b2de61ef192072e129ce8-1024x106.png)
- 执行A表的查询,查询条件是A.flag在结果集B里面,可以使用到A表的索引flag;
- 执行B表的子查询,得到结果集B,可以使用到B表的索引B_id。
EXISTS的执行计划:
![SQL优化 | IN和EXISTS谁的效率更高插图3 SQL优化 | IN和EXISTS谁的效率更高插图3](http://www.shujuyr.com/wp-content/uploads/2021/12/e775847c2bf769ffbb8059803563e67c-1024x106.png)
- 先将A表所有记录取到;
- 逐行针对A表的记录,去关联B表,判断B表的子查询是否有返回数据;
- 如果子查询有返回数据,则将A当前记录返回到结果集。
A相当于取全表数据遍历,B可以使用到索引。可以看到本次测试IN效率高于EXISTS。
![SQL优化 | IN和EXISTS谁的效率更高插图4 SQL优化 | IN和EXISTS谁的效率更高插图4](http://www.shujuyr.com/wp-content/uploads/2021/12/image-27.png)
再看执行计划:
IN的执行计划:
![SQL优化 | IN和EXISTS谁的效率更高插图5 SQL优化 | IN和EXISTS谁的效率更高插图5](http://www.shujuyr.com/wp-content/uploads/2021/12/22149296a1211977d9e29d08b8547f31-1024x98.png)
EXISTS的执行计划:
![SQL优化 | IN和EXISTS谁的效率更高插图6 SQL优化 | IN和EXISTS谁的效率更高插图6](http://www.shujuyr.com/wp-content/uploads/2021/12/ca04da8b0252b941f181878e83d45b06-1024x98.png)
当子查询结果集很大,而外部表较小的时候,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的执行类似如下:
![SQL优化 | IN和EXISTS谁的效率更高插图7 SQL优化 | IN和EXISTS谁的效率更高插图7](http://www.shujuyr.com/wp-content/uploads/2021/12/image-28.png)
exists的执行过程中,并没有对每一条内表的数据都进行查询,而是存在该条数据的时候会将结果集存起来,到最后的时候同一输出结果集。
select a.* from 外表 a where exists(select 1 from 内表 b where a.id=b.id) 的EXISTS的执行语句如下:
![SQL优化 | IN和EXISTS谁的效率更高插图8 SQL优化 | IN和EXISTS谁的效率更高插图8](http://www.shujuyr.com/wp-content/uploads/2021/12/image-29.png)
两个测试两个结果,不同业务场景要具体分析:
IN执行原理:IN是做外表和内表通过Hash连接,先查询子表,再查询主表,不管子查询是否有数据,都对子查询进行全部匹配。
EXISTS执行原理:EXISTS是外表做loop循环,先主查询,再子查询,然后去子查询中匹配,如果匹配到就退出子查询返回true,将结果放到结果集。
IN语法在内表和外表上都使用到索引;EXISTS查询仅内表上可以使用到索引,外表会全表扫描。
当子查询结果集很大,而外部表较小的时候,EXISTS的Block Nested Loop(Block 嵌套循环)的作用开始显现,查询效率会优于IN;否则IN 的外表索引优势占主要作用,此时IN的查询效率会优于EXISTS。
综上所述:子查询结果集越大用EXISTS,子查询结果集越小用IN。
觉得本文有用,请转发、点赞或点击“在看”聚焦技术与人文,分享干货,共同成长更多内容请关注“数据与人”
![SQL优化 | IN和EXISTS谁的效率更高插图9 SQL优化 | IN和EXISTS谁的效率更高插图9](http://www.shujuyr.com/wp-content/uploads/2021/12/数据与人.jpg)