mysql中in和exists区别

mysql中in和exists区别

1. exists 和 in

1.1 解释

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

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效率会更高。

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://xianyum.cn/archives/inexist

Buy me a cup of coffee ☕.