exist 和 in 到底如何选择

子查询优化

有索引 大表驱动小表

select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);in select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno);

用 exists 是否存在,存在返回一条记录,exists 是作为一个查询判断用,所以 select 后返回什么不重要。

exists select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno);

用 exists 是否存在,存在返回一条记录,exists 是作为一个查询判断用,所以 select 后返回什么不重要。

select sql_no_cache sum(sal) from emp inner join dept on emp.deptno=dept.deptno;

 

 

 

 

有索引 小表驱动大表

select sql_no_cache sum(e.sal) from (select * from emp where id

select sql_no_cache sum(e.sal) from (select * from emp where id

select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

 

有索引小驱动大表 性能优于 大表驱动小表性能优于 有索引小驱动大表 性能优于 大表驱动小表

无索引 小表驱动大表

select sql_no_cache sum(e.sal) from (select * from emp where id

select sql_no_cache sum(e.sal) from (select * from emp where id

select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

 

 

 

无索引大表驱动小表

select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno);

select sql_no_cache sum(sal) from emp inner join dept on emp.deptno=dept.deptno;

总结

有索引:

inner join > in > exist

无索引

小表驱动大表,因为 join 方式需要distinct,没有索引distinct消耗性能,所以 exist性能最佳

exist > in > join

大表驱动小表

in 和 exist 的性能性能接近,都比较糟糕, exist 稍微好一点

inner join 使用了 join buffer 会快很多

left join 则最慢

exist:

先执行外部查询语句,然后在执行子查询,子查询中它每次都会去执行数据库的查询,执行次数等于外查询的数据数量。查询数据库比较频繁(记住这点),如果b表再id上加了索引也会走索引

in: 

先查询 in()子查询的数据(1次),并且将数据放进内存里(不需要多次查询),然后外部查询的表再根据查询的结果进行查询过滤

EXISTS与IN的使用效率:通常情况下采用exists要比in效率高,因为IN不走索引,但要看实际情况具体使用:
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

 

 

 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页