sql优化：Exists和in的效率哪个高

拼搏现实的明天。 2022-05-19 06:14 364阅读 0赞

参考http://zjwlonger.iteye.com/blog/1921763，[https://blog.csdn.net/u011627980/article/details/50370566][https_blog.csdn.net_u011627980_article_details_50370566]  
说一说，我们工作和面试最常被提问的也是最常用的操作—sql优化，老鸟和新手的最大差别就是在优化这方面，又要找工作了不得不整理一下知识重新出发。

### 1.系统要求进行SQL优化，对效率比较低的SQL进行优化，使其运行效率更高，其中要求对SQL中的部分**in/not in**修改为**exists/not exists** ###

修改方法如下：  
in的SQL语句

SELECT 
    id, category_id, htmlfile, title,convert(varchar(20),begintime,112) as pubtime
    FROM tab_oa_pub 
    WHERE is_check=1 and category_id in (select id from tab_oa_pub_cate where no='1')
    order by begintime desc

修改为exists的SQL语句

SELECT 
    id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
    FROM tab_oa_pub WHERE is_check=1 and
    exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1')
    order by begintime desc

### 2.分析一下exists真的就比in的效率高吗？ ###

我们先讨论IN和EXISTS。
    select * from t1 where x in ( select y from t2 )
    事实上可以理解为：
    select *
      from t1, ( select distinct y from t2 ) t2
     where t1.x = t2.y;
    ——如果你有一定的SQL优化经验，从这句很自然的可以想到t2绝对不能是个大表，因为需要对t2进行全表的“唯一排序”，如果t2很大这个排序的性能是不可忍受的。但是t1可以很大，为什么呢？最通俗的理解就是因为t1.x=t2.y可以走索引。但这并不是一个很好的解释。试想，如果t1.x和t2.y都有索引，我们知道索引是种有序的结构，因此t1和t2之间最佳的方案是走merge join。另外，如果t2.y上有索引，对t2的排序性能也有很大提高。
    select * from t1 where exists ( select null from t2 where y = x )
    可以理解为：
    for x in ( select * from t1 )
    loop
       if ( exists ( select null from t2 where y = x.x )
       then
          OUTPUT THE RECORD!
       end if
    end loop
    ——这个更容易理解，t1永远是个表扫描！因此t1绝对不能是个大表，而t2可以很大，因为y=x.x可以走t2.y的索引。
    综合以上对IN/EXISTS的讨论，我们可以得出一个基本通用的结论：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

### 继续分析 ###

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。  
一直以来认为exists比in效率高的说法是不准确的。  
如果查询的两个表大小相当，那么用in和exists差别不大。  
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：  
例如：表A（小表），表B（大表）  
1：

select * from A where cc in (select cc from B)

效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)

效率高，用到了B表上cc列的索引。  
相反的  
2：

select * from B where cc in (select cc from A)

效率高，用到了B表上cc列的索引；

select * from B where exists(select cc from A where cc=B.cc)

效率低，用到了A表上cc列的索引。

### not in 和not exists ###

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；  
而not extsts 的子查询依然能用到表上的索引。  
所以无论那个表大，用not exists都比not in要快。  
in 与 =的区别

select name from student where name in ('zhang','wang','li','zhao');

与

select name from student where name='zhang' or name='li' or name='wang' or name='zhao'

的结果是相同的。

### 3.在Oracle SQL中取数据时有时要用到in 和 exists 那么他们有什么区别呢？ ###

1 性能上的比较  
比如Select \* from T1 where x in ( select y from T2 )  
执行的过程相当于:  
select \*  
from t1, ( select distinct y from t2 ) t2  
where t1.x = t2.y;

相对的

select \* from t1 where exists ( select null from t2 where y = x )  
执行的过程相当于:  
for x in ( select \* from t1 )  
loop  
if ( exists ( select null from t2 where y = x.x )  
then  
OUTPUT THE RECORD  
end if  
end loop  
表 T1 不可避免的要被完全扫描一遍

分别适用在什么情况?  
以子查询 ( select y from T2 )为考虑方向  
如果子查询的结果集很大需要消耗很多时间，但是T1比较小执行( select null from t2 where y = x.x )非常快，那么exists就比较适合用在这里  
相对应得子查询的结果集比较小的时候就应该使用in.

--------------------

微信公众号:程序国度  
[我的GitHub][GitHub]  
QQ：2541692705  
邮箱：loyalwilliams@163.com  
微信扫一扫关注我，没事可以玩玩小游戏  
![20190521165139570.png][]  
  
  
我想去流浪，我想去读书，若有机会，江湖再见  
扫一扫，领取红包，就当奖励你我付出的努力  
![20181213140254471.png][]

[https_blog.csdn.net_u011627980_article_details_50370566]: https://blog.csdn.net/u011627980/article/details/50370566
[GitHub]: https://github.com/LoyalWilliams
[20190521165139570.png]: /images/20220519/ab8fc8384cb54ec7ae3344c3664f30f2.png
[20181213140254471.png]: /images/20220519/a3f034c2b6774f5999549a598f6612de.png