Mysql中exists和in的区别

最近在看各种大牛博客中关于MySQL处理海量数据时用到的一些优化查询方法，其中涉及到exists和in的使用效率的对比。

现在有两个表A，B表。

这条语句适合于A表比B表大

select * from A where id in (select id from B);

这条语句适合于B表比A表大

select * from A where id exists (select id from B where A.id = B.id);

exists是外表用loop逐条查询，每次查询都会看子查询的语句，当exists中能够返回记录行，则条件为真，返回当前loop到的这条记录，反之，该记录就会被丢弃。所以exists的条件就相当于是一个bool条件，当能返回记录为true，反之为false.他的查询过程类似于下面这个过程。

List res=[];
Array A= (select * from A);

for (int i = 0;i < A.length;i++){
   if(exists(A[i].id)){
       res.add(A[i]);
   }
}
return res;

因此当B表中的数据比A表中的数据大时，适合使用exists()，因为不需要遍历很多次，只需要执行一次即可。

比如：A表中有10000条记录，B表中有10000000条记录，那么最多遍历10000次。

A有10000条记录，B表中有1000000000条记录，那么最多还是遍历10000次。

使用了in语句，in()只执行一次，查出B表中的所有id字段缓存起来，之后，检查A表的id是否与B表的id相等，如果相等则将记录加入结果集，直到遍历完A表中的所有记录。它的查询过程如下：

List res = [];
Array A = (select * from A);
Array B = (select * from B);
for (int i = 0;i < A.length;i++){
  for(int j = 0;j < B.length;j++){
    if(A[i].id==B[j].id){
       res.add(A[i]);
       break;
    }
  }
}
return res;

in查询也相当于多个or条件的叠加。比如select * from A where id in(1,2,3) 等价于 select * from A where id = 1 or id = 2 or id = 3; 

因此可以看出，当B表数据较大不适合使用in(),因为它会遍历B表中数据。

比如：A表中有10000条记录，B表中有10000000条记录，那么最多遍历10000*10000000次。

A有10000条记录，B表中有100条记录，那么最多遍历10000*100次。

下面再来看看not exists()和not in()

1、select * from A where  id not exists (select id from B where A.id = B.id);

2、select * from A where  id not in (select id from B); 等价于select * from A where A.id != 1 and A.id != 2 and A.id != 3;

not in 是一个范围查询，无法使用任何索引，等于说对于A表中的记录都要在B表中遍历一次。而not exists依然可以使用表中的索引。

因此 无论哪个表大，not exists比not in 效率要高。