很多论坛又开始讨论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 的话
select
* from test a
where not exists(select
1 from test
where sort = a.sort and sid
> a.sid)
比
select
* from test a
where sid
in (select max(sid) from test
where sort
= a.sort)
的执行效率要高三倍以上。具体的执行时间忘记了。但是结果我记得很清楚。在此之前我一直推崇第二种写法,后来就改第一种了。
in和exists的sql执行效率分析,再简单举一个例子:
declare @t table(id
int identity(1,1), v varchar(10))
insert @t select'a'
union all select'b'
union all select'c'
union all select'd'
union all select'e'
union all select'b'
union all select'c'
--a语句in的sql写法
select
* from @t
where v in (select v from @t group by v having count(*)>1)
--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执行效率有较清晰的了解。
分享到:
相关推荐
在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT ...
对于以上两种情况,in是在内存里遍历比较,而exists需要查询数据库,所以当B表数据量较大时,exists效率优于in。 1、select * from A where id in (select id from B); in()只执行一次,它查出B表中的所有id字段并...
最近在写SQL语句时,对选择IN 还是Exists 犹豫不决,于是把两种方法的SQL都写出来对比一下执行效率,发现IN的查询效率比Exists高了很多,于是想当然的认为IN的效率比Exists好,但本着寻根究底的原则,我想知道这个...
第1章 全局在胸——用工具对...17.1.3 IN与EXISTS之争 455 17.1.4 总结探讨 457 17.2 误区背后的话题扩展 457 17.2.1 话题扩展之等价与否优先 457 17.2.2 话题扩展之颠覆误区观点 458 17.3 全书完,致读者 461
以下是一份SQL开发常见技巧的Word大纲格式列表: SQL基础技巧 规范书写: 使用缩进和空行提高语句可读性。 尽量避免不必要的子查询,采用连接JOIN...适当地使用预编译SQL语句提高执行效率。 存储过程与函数: 编写高
直至无法产生新的输出或搜索限额满→对 输出的SQL语句进行过滤,选出具有不同执行计划的SQL语句(即不同的执行效率)→对得到的SQL语句进行批量测试,找出性能最好的SQL语句。图2 优化前的SQL语句 自动优化实例 假设...
第1章 全局在胸——用工具对...17.1.3 IN与EXISTS之争 455 17.1.4 总结探讨 457 17.2 误区背后的话题扩展 457 17.2.1 话题扩展之等价与否优先 457 17.2.2 话题扩展之颠覆误区观点 458 17.3 全书完,致读者 461
EXISTS 和 IN的执行效率是一样的 6. 用函数charindex()和前面加通配符%的LIKE执行效率一样 7. UNION并不绝对比OR的执行效率高 8. 字段提取要按照“需多少、提多少”的原则,避免“SELECT *” 9. COUNT(*)不比COUNT ...
在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。(注:如果对表进行...
1.17 通过内部函数提高SQL效率 10 1.18 使用表的别名(ALIAS) 11 1.19 用EXISTS替代IN 12 1.20 用NOT EXISTS替代NOT IN 12 1.21 用表连接替换EXISTS 13 1.22 用EXISTS替换DISTINCT 13 1.23 识别’低效执行’的SQL语句...
3.1 绝大多数情况下NOT EXISTS比NOT IN 效率高 6 3.2 UNION ALL效率比UNION高很多 6 3.3 一些很耗资源的SQL操作,在不必要的情况下不要使用 6 3.4 通常联接查询比子查询的效率要高很多 7 3.5 用TABLE 索引(INDEX)...
exists和not exists查询需要内部查询和外部查询进行一个关联的条件,如果没有这个条件将是查询到的所有信息。如:id等于student.id; # some、any、all子句查询示例 查询班级的学生年龄大于班级的学生的年龄的...
一:使用where少使用having; 二:查两张以上表时,把记录少的放在右边;...十一:not exists代替 not in(not in 字句将执行一个内部的排序和合并,任何情况下,not in是最低效的,子查询中全表扫描了。为了避免使用n
sql="select * from 数据表 where 字段名 in (\'值1\',\'值2\',\'值3\')" sql="select * from 数据表 where 字段名 between 值1 and 值2" (2) 更新数据记录: sql="update 数据表 set 字段名=字段值 where 条件...
关系型数据库性能体系,设计和效率提升 1 1 前言 2 1.1目的 2 1.2预期的读者和阅读建议 2 2 数据库模型设计规范 2 2.1 数据库建模原则性规范 2 2.2 实体型之间关系认定规范 2 2.3 范式化1NF的规范 3 2.4 范式化2NF的...
§12.5 使用EXISTS和IN 148 §12.6 分离事务(Discrete Transactions ) 149 §12.7 测试SQL语句性能 151 §12.7.1 SQL_Trace实用工具 151 §12.7.2 TKPROF实用程序 151 §12.8 使用SQL_Trace和TKPROF 151 §12.8.1 ...
实验四 存储过程、触发器与索引 一、实验目的 1. 熟悉大型数据库实验环境,以MS SQL SERVER为例。 2. 掌握视图。 3. 掌握存储过程与触发器。 4. 掌握MS SQL SERVER的导入和导出。 5. 掌握MS SQL SERVER的索引。 二、...
SQL Select语句完整的执行顺序:1、from子句组装来自不同数据源的数据;2、where子句基于指定的条件对记录行进行筛选;3、group by子句将数据划分为多个分组;4、使用聚集函数进行计算;5、使用having子句筛选分组;...
SQL(Structured Query Language)结构化查询语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。 SQL语言主要包含5个部分 数据定义...
delete from a where no in (select no from b); 14、查询从多少行到多少行的记录(可以用在web开发中的分页显示) select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b ) where row_...