首先理解mysql中sql语句如果有 exist和in 是怎么去执行的
先给个结论后面再去慢慢解释:
exist适合 子查询中表数据大于外查询表中数据的业务场景
in:适合外部表数据大于子查询的表数据的业务场景
in 和 exists的区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索引及结果集的关系了 ,另外IN时不对NULL进行处理。
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的
两者在sql中执行的差别:
exist: 先执行外部查询语句,然后在执行子查询,子查询中它每次都会去执行数据库的查询,执行次数等于外查询的数据数量。查询数据库比较频繁(记住这点),如果b表再id上加了索引也会走索引
select * from a where exist(select 1 from b.a_id=a.id);
//外部查询
Object[] out={select * from a};
List<Object> result=new ArrayList();
for(int i=0;i<out.size();i++){
//子查询(内查询)
//1 去查询数据库
// 2 判断外部数据的值执行第一步是是否能查到数据,返回 ture或者false
// 3 如果第二部为true
if(exiset(out[i].id)){//执行 select * fron b where b.a_id=a.id; 会执行 out.size();次
result.add(out[i]));
}
}
所以如果a表中的数据越大那么 子查询查询的次数就会越多,这样对效率就很慢
例如:
1 表a中100000条数据,表b中100条数据,查询数据库次数=1(表a查一次)+100000(子查询:查询表b的次数) ,一共 100001次
2 表a中 100条数据,表b100000条,查询数据库次数=1(表a查一次)+100(子查询次数),一共 101次
可见只有当子查询的表数量远远大于外部表数据的是否用exist查询效率好
in: 先查询 in()子查询的数据(1次),并且将数据放进内存里(不需要多次查询),然后外部查询的表再根据查询的结果进行查询过 滤,最后返回结果
其实可以看出这个地方是没有走索引的,全局扫描,是外部查询一条一条跟子查询的值比较的,但是这样也是比exist的效率快,因为减少了查数据库的次数
比如:
select * from a where a.id in( select a_id from b )
Object[] out={select * from a};
Object[] in={select * from a};
List<Object> result=new ArrayList();
for(int i =0; i<>out.size();i++)
{
for (int j = 0 ; j<in.size(),j++){
if(out[i].id=in[j]){
result.add(out[i]));
}
}
}
这个的操作主要是在内存进行,不会进行数据库的多次查询。
------------------后补----------------------
发现个问题: 其实使用in是 a表并不会走索引,索引觉得不管时a,b表哪个表数据量大用 in还是exist都觉得无所谓,反正子查询加了索引会走索引,exist相反更加耗时,因为会多次查询数据库, 这一结论后面再论证