IN和EXISTS被频繁使用在SQL中,虽然作用是一样的,但是在使用效率谁更高这点上众说纷纭。下面我们就通过一组测试来看,在不同场景下,使用哪个效率更高。
测试数据:
测试1:
可以看到本次测试IN效率高于EXISTS。
再看执行计划:
IN的执行计划:
(1)执行A表的查询,查询条件是A.flag在结果集B里面,可以使用到A表的索引flag;
(2)执行B表的子查询,得到结果集B,可以使用到B表的索引B_id。
EXISTS的执行计划:
(1)先将A表所有记录取到;
(2)逐行针对A表的记录,去关联B表,判断B表的子查询是否有返回数据,5.5之后的版本使用BlockNestedLoop(Block嵌套循环);
(3)如果子查询有返回数据,则将A当前记录返回到结果集。
A相当于取全表数据遍历,B可以使用到索引。
测试2:
子查询selectflagfromBwhereB_id结果集条。
可以看到本次EXISTS效率比IN高。
再看执行计划:
两者的索引使用情况与第一次实验是一致的,当子查询结果集很大,而外部表较小的时候,Exists的BlockNestedLoop(Block嵌套循环)的作用开始显现,查询效率会优于IN。
从两次测试来看,并不能说明谁的效率更高,而应该具体情况具体分析:
首先来看IN和EXISTS的执行原理:
IN是做外表和内表通过Hash连接,先查询子表,再查询主表,不管子查询是否有数据,都对子查询进行全部匹配。
EXISTS是外表做loop循环,先主查询,再子查询,然后去子查询中匹配,如果匹配到就退出子查询返回true,将结果放到结果集。
IN原理
在in()的执行中,先执行内表得到结果集,再执行外表。外表会对所有的内表结果集匹配,也就是说:如果外表有,内表有00,就会执行*00次。所以在内表比较大的时候,不合适用in()方法,效率比较低。
select*from外表awhereidin(select相关idfrom内表)IN的执行类似如下:
EXISTS原理
exists()的执行过程中,并没有对每一条内表的数据都进行查询,而是存在该条数据的时候会将结果集存起来,到最后的时候同一输出结果集。
selecta.*from外表awhereexists(select1from内表bwherea.id=b.id)的EXISTS的执行语句如下:
设:外表A,内表B。
A表有00条记录,B表有0000条记录,那么exists()会执行00次去判断A表中的id是否与B表中的id相等。
A表有00条记录,B表有000000条记录,那么exists()还是执行00次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果。
再如:A表有00条记录,B表有条记录,那么exists()还是执行00次,还不如使用in()遍历00*次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快。
总结
1、IN查询在内部表和外部表上都可以使用到索引;
2、EXISTS查询仅内部表上可以使用到索引,外表会全表扫描;当子查询结果集很大,而外部表较小的时候,EXISTS的BlockNestedLoop(Block嵌套循环)的作用开始显现,查询效率会优于IN;
3、当子查询结果集较小,而外部表很大的时候,EXISTS的Block嵌套循环优化效果不明显,IN的外表索引优势占主要作用,此时IN的查询效率会优于EXISTS。
子查询结果集越大用EXISTS,子查询结果集越小用IN。
关于作者
陈家睿,云和恩墨MySQL技术顾问,拥有MySQLOCP、PGCE、OBCA、SCDP证书,长期服务于电信行业。现负责公司MySQL数据库、分布式数据库运维方面的技术工作;热衷于运维故障处理、备份恢复、升级迁移、性能优化的学习与分享。