最近很多论坛又开始讨论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 的话
where not exists(select 1 from test where sort = a.sort and sid > a.sid)
比
where sid in (select max(sid) from test where sort = a.sort)
的执行效率要高三倍以上。具体的执行时间忘记了。但是结果我记得很清楚。在此之前我一直推崇第二种写法,后来就改第一种了。
in和exists的sql执行效率分析,再简单举一个例子:
insert @t select'a'
union all select'b'
union all select'c'
union all select'd'
union all select'e'
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
--b语句exists的sql写法
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执行效率有较清晰的了解。
相关推荐
IN、EXISTS、NOT IN、NOT EXISTS 是 SQL 中四种常用的条件判断运算符,它们之间的区别主要体现在使用场景、执行效率和语法结构上。 IN IN 是一种条件判断运算符,用于判断某个值是否存在于一个列表中。其基本语法...
在 SQL 中,in 和 exists 的使用场景可以互换,但是在某些情况下,exists 的效率可能要高于 in。例如: ```sql SELECT ID, NAME FROM A WHERE ID IN (SELECT AID FROM B); ``` 这个查询将返回表 A 中所有与表 B 相...
使用EXISTS和NOT EXISTS可以提高查询的效率,避免了使用NOT IN和IN的低效率。同时,EXISTS和NOT EXISTS也可以使查询语句变得更加简洁和易于理解。 总结 本文探讨了使用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句...
对于以上两种情况,in是在内存里遍历比较,而exists需要查询数据库,所以当B表数据量较大时,exists效率优于in。 1、select * from A where id in (select id from B); in()只执行一次,它查出B表中的所有id字段并...
以下是一些优化SQL执行效率的关键方法: 1. **避免在`WHERE`子句中使用子查询**:子查询可能会导致性能下降,尤其是在循环或嵌套查询中。尽量通过连接(JOIN)操作或者预先计算结果来替代子查询。 2. **优化时间...
以下是一些提高SQL执行效率的关键技巧: 1. **IN 和 EXISTS 的使用**: - `IN` 通常用于在子查询中检查某个值是否存在于另一张表的特定列中。当子查询返回的数据量不大时,`IN` 是一个高效的选择。 - `EXISTS` 则...
本文将深入探讨EXISTS谓词的工作原理、用法以及其与IN和JOIN操作符的区别。 一、EXISTS谓词的基本概念 EXISTS谓词用于测试一个子查询是否至少返回一行数据,而不是关心返回的具体数据内容。其基本语法结构如下: ...
Oracle SQL编写规范是指在编写SQL语句时的一些基本原则和方法,旨在提高SQL语句的执行效率和可读性。以下是Oracle SQL编写规范的详细解释: 1. 尽量用索引,特别在从大量数据中返回小部分数据时 使用索引可以大大...
而自动内存管理则简化了数据库管理员的工作,自动调整内存分配以适应工作负载,从而可能间接影响`not in`和`not exists`的执行效率。 在进行SQL优化时,除了选择合适的查询语法,还需要考虑数据库的配置和最新特性...
SQL查询分析器是数据库管理系统中的一个重要工具,它用于解析、优化和执行SQL语句。在“sql查询分析器分离版本”这个主题下,我们可以探讨以下几个关键知识点: 1. **SQL查询分析器的基本功能**: SQL查询分析器是...
以上知识点在"SQL Server性能优化.doc"和"SQL Server 2000数据库优化方案参考.doc"中可能有更详细的解释和案例分析。深入理解并实践这些优化策略,将有助于提升SQL Server的性能,从而改善整个系统的运行效率。
在数据库管理中,SQL语句的优化是提升系统性能的关键环节。执行计划是数据库解析SQL语句后生成的一种详细步骤,它展示了数据是如何被检索、...通过深入了解SQL执行计划,我们可以更好地诊断问题,实现高效的数据查询。
在SQL Server中,优化SQL语句是提升数据库性能的关键环节。在处理大量数据时,尤其需要注意避免使用效率较低的操作符,...在编写SQL时,不仅要考虑语句的可读性和简洁性,还要兼顾执行效率,特别是在处理大规模数据时。
在Oracle数据库中,`MINUS`、`IN` 和 `EXISTS` 是三种不同的SQL查询操作符,它们各自用于特定的数据处理场景。了解并熟练掌握这些操作符对于优化查询性能和编写复杂的SQL语句至关重要。 1. **MINUS 操作符** `MINUS...
在编写 SQL 语句时,应该将值和列的类型匹配,否则可能会导致全表扫描,降低 SQL 语句的执行效率。 10. 不要在应用程序中使用关联子查询 在应用程序中,不应该使用关联子查询,而应该使用联机视图来代替,以提高 ...
合理的数据范式化、适当的数据冗余以及合适的分区策略都能提升SQL执行效率。 通过以上策略,我们可以对SQL语句进行有效优化,提高数据库系统的整体性能。然而,优化是一个持续的过程,需要根据实际情况不断调整和...
在MySQL中,`IN` 和 `EXISTS` 都是用来在查询中筛选满足特定条件的行,但它们的工作原理和性能表现有所不同。了解这两者的差异对于优化SQL查询至关重要。 首先,`IN` 关键字用于在主查询中比较某个列的值是否存在于...
SQL查询分析是数据库管理中的重要环节,用于优化和理解复杂的查询结构,确保数据检索的高效性和准确性。在原生DELPHI开发的环境下,这样的绿色小程序可以帮助开发者更直观地理解和改进SQL语句,提高应用程序的性能。...