最近在看各种大牛博客中关于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;
比如: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 效率要高。