in和exists的区别分析
select * from A where id in (select id from B);
select * from A where exists (select 1 from B where A.id=B.id);
对于以上两种情况,in是在内存里遍历比较,而exists需要查询数据库,所以当B表数据量较大时,exists效率优于in。
当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次
结论:IN()适合B表比A表数据小的情况
当B表比A表数据大时适合使用exists(),因为它没有那么多遍历操作,只需要再执行一次查询就行。
结论:EXISTS()适合B表比A表数据大的情况
1、IN()语句内部工作原理
IN()只执行一次,它查出B表中的所有id字段并缓存起来。之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录。
它的查询过程类似于以下过程:
List resultSet={};
Array A=(select * from A);
Array B=(select id 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) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;
可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次
例1:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历100001000000次,效率很差。
例2:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000100次,遍历次数大大减少,效率大大提升。
结论:IN()适合B表比A表数据小的情况
2、EXISTS()语句内部工作原理
exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是其内查询语句的结果集空或者非空,空则返回false,非空则返回true。
它的查询过程类似于以下过程:
List resultSet={};
Array A=(select * from A);
for(int i=0;i<A.length;i++) {
if(exists(A[i].id) { //执行select 1 from B where B.id=A.id是否有记录返回
resultSet.add(A[i]);
}
}
return resultSet;
当B表比A表数据大时适合使用exists(),因为它没有那么多遍历操作,只需要再执行一次查询就行。
例1:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
例2:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果。
例3:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快。
结论:EXISTS()适合B表比A表数据大的情况
使用情况分析
当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用。
EXISTS与IN的使用效率的问题,通常情况下采用exists要比in效率高,因为IN有些MySQL的版本是不走索引的,但是in后面的索引会失效。
“A\B表的ID 字段都要添加索引 ", 因为 id 查询会 Using Index,直接查询索引树。 但要看实际情况具体使用:
- IN适合于外表大而内表小的情况;
- EXISTS适合于外表小而内表大的情况。
EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False。