北京中科医院 http://www.yushiels.com/m/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数据库、分布式数据库运维方面的技术工作；热衷于运维故障处理、备份恢复、升级迁移、性能优化的学习与分享。
转载请注明：http://www.aierlanlan.com/grrz/7303.html
