sql优化--in和exists效率

朱雀 2021-05-18 20:36 734阅读 0赞

### in 和exists ###

in是把外表和内表作hash join连接，而exists 是对外表作nested loop 循环，每次loop 循环再对内表进行查询。

一直以来认为exists 比in 效率高的说法是不准确的。如果查询的两个表大小相当，那么用in 和exists 差别不大。

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：

表A（小表），表B（大表）1：

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

效率低，用到了A 表上cc 列的索引；因为A表的数据本身就小，索引的作用就不明显

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

效率高，用到了B 表上cc 列的索引。因为B表的数据量大，索引的作用较明显

相反的2：

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

效率高，用到了B 表上cc 列的索引；因为B表的数据量大，索引的作用较明显

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

效率低，用到了A 表上cc 列的索引。因为A表的数据本身就小，索引的作用就不明显

### not in 和not exists ###

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；

而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，对于not in 和not exists，用not exists 都比not in 要快。

===================================================================================

系统要求进行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

分析一下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和exists的区别与SQL执行效率分析   
  
本文对in和exists的区别与SQL执行效率进行了全面整理分析……   
  
最近很多论坛又开始讨论in和exists的区别与SQL执行效率的问题，   
本文特整理一些in和exists的区别与SQL执行效率分析   
  
SQL中in可以分为三类：   
  
　　1、形如select \* from t1 where f1 in ('a','b')，应该和以下两种比较效率   
  
　　select \* from t1 where f1='a' or f1='b'   
  
　　或者 select \* from t1 where f1 ='a' union all select \* from t1 f1='b'   
  
　　你可能指的不是这一类，这里不做讨论。   
  
　　2、形如select \* from t1 where f1 in (select f1 from t2 where t2.fx='x')，   
  
　　其中子查询的where里的条件不受外层查询的影响，这类查询一般情况下，自动优化会转成exist语句，也就是效率和exist一样。   
  
　　3、形如select \* from t1 where f1 in (select f1 from t2 where t2.fx=t1.fx)，   
  
　　其中子查询的where里的条件受外层查询的影响，这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少，一般认为效率不如exists。   
  
　　除了第一类in语句都是可以转化成exists 语句的SQL，一般编程习惯应该是用exists而不用in，而很少去考虑in和exists的执行效率.   
  
in和exists的SQL执行效率分析   
  
　　A，B两个表，   
  
　　(1)当只显示一个表的数据如A，关系条件只一个如ID时，使用IN更快：   
  
　　select \* from A where id in (select id from B)   
  
　　(2)当只显示一个表的数据如A，关系条件不只一个如ID，col1时，使用IN就不方便了，可以使用EXISTS：   
  
　　select \* from A   
  
　　where exists (select 1 from B where id = A.id and col1 = A.col1)   
  
　　(3)当只显示两个表的数据时，使用IN，EXISTS都不合适，要使用连接：   
  
　　select \* from A left join B on id = A.id   
  
　　所以使用何种方式，要根据要求来定。   
  
　　这是一般情况下做的测试：   
  
　　这是偶的测试结果:   
  
　　set statistics io on    
　　select \* from sysobjects where exists (select 1 from syscolumns where id=syscolumns.id)    
　　select \* from sysobjects where id in (select id from syscolumns )    
　　set statistics io off    
  
　(47 行受影响)   
  
　　表'syscolpars'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 2 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
  
　　表'sysschobjs'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
  
　　(1 行受影响)   
  
　　(44 行受影响)   
  
　　表'syscolpars'。扫描计数 47，逻辑读取 97 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
  
　　表'sysschobjs'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
  
　　(1 行受影响)   
  
　　set statistics io on    
　　select \* from syscolumns where exists (select 1 from sysobjects where id=syscolumns.id)    
　　select \* from syscolumns where id in (select id from sysobjects )    
　　set statistics io off    
  
  
　　(419 行受影响)   
  
　　表'syscolpars'。扫描计数 1，逻辑读取 10 次，物理读取 0 次，预读 15 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
  
　　表'sysschobjs'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
  
　　(1 行受影响)   
  
　　(419 行受影响)   
  
　　表'syscolpars'。扫描计数 1，逻辑读取 10 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
  
　　表'sysschobjs'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。   
  
　　(1 行受影响)   
  
　　测试结果(总体来讲exists比in的效率高):   
  
　　效率:条件因素的索引是非常关键的   
  
　　把syscolumns 作为条件:syscolumns 数据大于sysobjects   
  
　　用in   
  
　　扫描计数 47，逻辑读取 97 次，   
  
　　用exists   
  
　　扫描计数 1，逻辑读取 3 次   
  
　　把sysobjects作为条件:sysobjects的数据少于syscolumns   
  
　　exists比in多预读 15 次   
  
  
　　对此我记得还做过如下测试：   
  
　　表   
  
　　test   
  
　　结构   
  
　　id int identity(1,1), --id主键\\自增   
  
　　sort int, --类别,每一千条数据为一个类别   
  
　　sid int --分类id   
  
　　插入600w条数据   
  
　　如果要查询每个类别的最大sid 的话

![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]select \* from test a    
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]　　where not exists(select 1 from test where sort = a.sort and sid > a.sid)

比

![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]select \* from test a    
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]　　where sid in (select max(sid) from test where sort = a.sort)

的执行效率要高三倍以上。具体的执行时间忘记了。但是结果我记得很清楚。在此之前我一直推崇第二种写法，后来就改第一种了。   
  
  
in和exists的sql执行效率分析,再简单举一个例子：

![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]declare @t table(id int identity(1,1), v varchar(10))   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]insert @t select'a'   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]union all select'b'   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]union all select'c'   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]union all select'd'   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]union all select'e'   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]union all select'b'   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]union all select'c'   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]\--a语句in的sql写法   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]select \* from @t where v in (select v from @t group by v having count(\*)>1)   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]\--b语句exists的sql写法   
![aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY][]select \* from @t a where exists(select 1 from @t where id!=a.id and v=a.v)

两条语句功能都是找到表变量@t中,v含有重复值的记录.   
  
　　第一条sql语句使用in,但子查询中与外部没有连系.   
  
　　第二条sql语句使用exists,但子查询中与外部有连系.   
  
　　大家看SQL查询计划,很清楚了.   
  
　　selec v from @t group by v having count(\*)> 1   
  
　　这条Sql语句,它的执行不依赖于主查询主句(我也不知道怎么来描述in外面的和里面的,暂且这么叫吧,大家明白就行)   
  
　　那么,SQL在查询时就会优化,即将它的结果集缓存起来   
  
　　即缓存了   
  
　　v   
  
　　---   
  
　　b   
  
　　c   
  
　　后续的操作,主查询在每处理一步时,相当于在处理 where v in('b','c') 当然,语句不会这么转化, 只是为了说明意思,也即主查询每处理一行(记为currentROW时,子查询不会再扫描表, 只会与缓存的结果进行匹配   
  
　　而   
  
　　select 1 from @t where id!=a.id and v=a.v   
  
　　这一句,它的执行结果依赖于主查询中的每一行.   
  
　　当处理主查询第一行时 即 currentROW(id=1)时, 子查询再次被执行 select 1 from @t where id!=1 and v='a' 扫描全表,从第一行记 currentSubROW(id=1) 开始扫描,id相同,过滤,子查询行下移,currentSubROW(id=2)继续,id不同,但v值不匹配,子查询行继续下移...直到currentSubROW(id=7)没找到匹配的, 子查询处理结束,第一行currentROW(id=1)被过滤,主查询记录行下移   
  
　　处理第二行时,currentROW(id=2), 子查询 select 1 from @t where id!=2 and v='b' ,第一行currentSubROW(id=1)v值不匹配,子查询下移,第二行,id相同过滤,第三行,...到第六行,id不同,v值匹配, 找到匹配结果,即返回,不再往下处理记录. 主查询下移.   
  
　　处理第三行时，以此类推...   
  
　　sql优化中，使用in和exist？ 主要是看你的筛选条件是在主查询上还是在子查询上。   
  
　　通过分析，相信大家已经对in和exists的区别、in和exists的SQL执行效率有较清晰的了解。

[aHR0cHM6Ly93d3cuY25ibG9ncy5jb20vSW1hZ2VzL091dGxpbmluZ0luZGljYXRvcnMvTm9uZS5naWY]: /images/20210518/38238a5be4c749ddbf0e94d0578d3378.png