mysql中in和exists区别

1. exists 和 in

1.1 解释

IN 是把外表和内表作hash join 连接，而 EXISTS 是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为 EXISTS 比IN 的效率高的说法是不准确。如果查询的两个表大小（数据量）相当，EXISTS 和 IN 的效率差别不大

1.2 分析

# sql语句1
select count(1) from A a where a.b_id in(select b.id from B b);
# sql语句2
select count(1) from A a where exists (select 1 from B b where b.id = a.b_id);

• in：先查询in后面的b表，然后在去a表过滤，也就是先执行子查询，等结果出来后，在遍历主查询，其根据是b_id和id相当查询的
• exists：主查询是内层循环，先查询出a表，a表数据相当于外层循环，然后在判断b_id和b表中的id是否相等，相等才保留数据，查询b表就是内层循环

其sql语句1执行类似如下过程：

List resultSet=[];
Array A=(select count(1) from A a);
Array B=(select b.id from B b);

for(int i=0;i<A.length;i++) {
   for(int j=0;j<B.length;j++) {
      if(A[i].bId==B[j].id) {
         resultSet.add(A[i]);
         break;
      }
   }
}
return resultSet;

其sql语句2执行类似如下过程：

List resultSet=[];
Array A=(select count(1) from A a)

for(int i=0;i<A.length;i++) {
   if(exists(A[i].id) {    //执行select 1 from B b where b.id=a.b_id是否有记录返回
       resultSet.add(A[i]);
   }
}
return resultSet;

1.3 总结

in：先执行子查询，也就是in()所包含的语句。子查询查询出数据以后，将前面的查询分为n次普通查询(n表示在子查询中返回的数据行数)

exist：如果子查询查到数据，就返回布尔值true；如果没有，就返回布尔值false。返回布尔值true则将该条数据保存下来，否则就舍弃掉。也就是说exists查询，是查询出一条数据就执行一次子查询

小表驱动大表。

in适合于外表大而内表小的情况，exists适合于外表小而内表大的情况。

2. not exists和not in

A：select * from t1 a where not exists (select * from t2 b where b.id = a.id)
B：select * from t1 a where a.id not in (select b.id from t2 b)

对于A，和exists一样，用到了t2上的id索引，exists()执行次数为t1.length，不缓存exists()的结果集。
而对于B，因为not in实质上等于!= and != ···，因为!=不会使用索引，故not in不会使用索引。
因此，不管t1和t2大小如何，均使用not exists效率会更高。