IN和EXISTS谁的效率高

关于IN和EXISTS性能的优劣其实测试是最好的办法，但是我们首先需要了解IN和EXISTS在查询过程中的工作原理。

IN原理

在in()的执行中，先执行内表得到结果集，再执行外表。外表会对所有的内表结果集匹配，也就是说：如果外表有100，内表有10000，就会执行100*10000次。所以在内表比较大的时候，不合适用in()方法，效率比较低。

select * from 外表 a where id in (select 相关id from 内表) IN的执行类似如下：

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;

EXISTS原理

exists()的执行过程中，并没有对每一条内表的数据都进行查询，而是存在该条数据的时候会将结果集存起来，到最后的时候同一输出结果集。

select a.* from 外表 a where exists(select 1 from 内表 b where a.id=b.id) 的EXISTS的执行语句如下：

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;

设:外表A，内表B。

A表有10000条记录,B表有1000000条记录, 那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。

A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果。

总结

1、IN查询在内部表和外部表上都可以使用到索引；

2、EXISTS查询仅内部表上可以使用到索引，外表会全表扫描；当子查询结果集很大，而外部表较小的时候，EXISTS的Block Nested Loop(Block 嵌套循环)的作用开始显现，查询效率会优于IN；

3、当子查询结果集较小，而外部表很大的时候，EXISTS的Block嵌套循环优化效果不明显，IN 的外表索引优势占主要作用，此时IN的查询效率会优于EXISTS。

子查询结果集越大用EXISTS，子查询结果集越小用IN。