【MySQL】 exist 与in 的区别

试用场景

• exist：适合 子查询中表数据大于外查询表中数据的业务场景
• in： 适合外部表数据大于子查询的表数据的业务场景

描述

　　in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

　  in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

差异

两者在sql中执行的差别:

• exist： 先执行外部查询语句，然后在执行子查询，子查询中它每次都会去执行数据库的查询，执行次数等于外查询的数据数量。查询数据库比较频繁(记住这点),如果b表再id上加了索引也会走索引。

select * from a where exist(select 1 from b.a_id=a.id);

       //外部查询
       Object[] out={select *  from a};
        List<Object> result=new ArrayList();
       for（int i=0;i<out.size();i++）{
              //子查询(内查询)
               //1 去查询数据库
               // 2 判断外部数据的值执行第一步是是否能查到数据，返回 ture或者false 
              // 3 如果第二部为true
              if（exiset(out[i].id)）{//执行  select * fron b where b.a_id=a.id;  会执行 out.size();次
                   result.add(out[i]));
               } 
       }

所以：如果a表中的数据越大那么 子查询查询的次数就会越多，这样对效率就很慢。

例如:

1. 表a中100000条数据,表b中100条数据，查询数据库次数=1(表a查一次)+100000(子查询：查询表b的次数) ，一共 100001次
2. 表a中 100条数据，表b100000条，查询数据库次数=1(表a查一次)+100(子查询次数)，一共 101次。

可见只有当子查询的表数量远远大于外部表数据的是否用exist查询效率好。

• in: 先查询 in()子查询的数据(1次)，并且将数据放进内存里(不需要多次查询),然后外部查询的表再根据查询的结果进行查询过滤,最后返回结果。