首页 >数据库 >mysql教程 >sql中 in , not in , exists , not exists效率分析_MySQL

sql中 in , not in , exists , not exists效率分析_MySQL

WBOY
WBOY原创
2016-05-30 17:09:491309浏览

in和exists执行时,in是先执行子查询中的查询,然后再执行主查询。而exists查询它是先执行主查询,即外层表的查询,然后再执行子查询。

 

exists 和 in 在执行时效率单从执行时间来说差不多,exists要稍微优于in。在使用时一般应该是用exists而不用in

 

如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。IN时不对NULL进行处理。

 

not exists 和 not in 比较时,not exists 的效率比较高。

 

为了说明测试结果,我把emp1表中的数据到了315392条。emp2中删除只有2条件数据。测试的依据是执行的时间来说明的。

 

emp1中的数据记录情况。

 

SQL> select count(*) from emp1;

 

  COUNT(*)

 

----------

 

315392

 

emp2中的数据记录情况:

 

SQL> select count(*) from emp2;

 

 

 

  COUNT(*)

 

----------

 

   2

 

1、  执行exists查询,要求在emp1中查询出所有存在于emp2的数据总数

 

 SQL> select count(*) from emp1 where exists ( select null from emp2 where emp1.ename = emp2.ename);

 

  COUNT(*)

 

----------

 

     45056

 

执行次数十次,最大的一次为0.125S

 

2、    使用not exists查询出所在不在emp2中的数据总数

 

SQL> select count(*) from emp1 where not exists ( select null from emp2 where emp1.ename = emp2.ename);

 

     COUNT(*)

 

----------

 

270336

 

执行次数十次,最大的一次为0.141S

 

3、执行in 查询,要求在emp1中查询出所有存在于emp2的数据总数

 

SQL> select count(*) from emp1 where ename in ( select ename from emp2);

 

  COUNT(*)

 

----------

 

     45056

 

执行十次,最大的一次为0.141S

 

4、使用not in查询出所在不在emp2中的数据总数

 

SQL> select count(*) from emp1 where ename not in ( select ename from emp2 );

 

  COUNT(*)

 

----------

 

270336

 

执行十次,最长一次为0.328S

 

5、使用in查询,调用外层与子查询的位置,要求查询出存在于emp2中,且存在于emp1中的数据记录数

 

SQL> select count(*) from emp2 where ename in (select ename from emp1 );

 

  COUNT(*)

 

----------

 

 2

 

执行次数十次,最长的一次为0.047S

 

6、使用exists查询,调用外层与子查询的位置,要求查询出存在于emp2中,且存在于emp1中的数据记录数

 

SQL> select count(*) from emp2 where ename in (select ename from emp1 );

 

  COUNT(*)

 

----------

 

 2

 

执行次数十次,最长的一次为0.047S

 

综上所述:在使用in 和 exists时,个人觉得,效率差不多。而在not in 和 not exists比较时,not exists的效率要比not in的效率要高。

 

当使用in时,子查询where条件不受外层的影响,自动优化会转成exist语句,它的效率和exist一样。(没有验证)

 

如select * from t1 where f1 in (select f1 from t2 where t2.fx='x') 这时,认为in 和 exists效率一样。

 

IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn