【性能】关于in的疑惑

路人乙e 2010-08-06 09:29:00
在SQL优化众多的教程中，都是建议尽量避免使用in关键字（至少我看到的是这样）
可是我在实验中发现，in关键字不但没有降低效率，反而比其它方法的效率都要高！
看一下语句：
a) update tba set time=getdate() where id in (select id from tbb)
b) update tba set time=getdate() from tbb where tba.id=tbb.id

语句a比语句b执行速度快了近20倍！
这是怎么回事，难道是SQL2008对IN做了特殊处理？
...全文
468 23 打赏 收藏 转发到动态 举报
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
haitao 2010-09-01
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 haiwer 的回复:]
二楼语句与搂主的一样

应该考虑
update tba set time=getdate() where exists (select 1 from tbb where tbb.id = tba.id)

连接曼的原因很可能是因为tbb的id不唯一，造成tba的一个id对应tbb的多条记录，做了多次更新，也就是说，你的例子是个特殊情况
[/Quote]

对于这种 第一层的字段传入第二层 的sql写法，一直觉得不自在
update tba set time=getdate() where exists (select 1 from tbb where tbb.id = tba.id)
tba.id是第一层的字段，会不会：第一层的所有记录（假设1万条）的每一个id，都执行一次第二层的sql？这样效率会高？
或者，sql引擎会自动优化？？
haitao 2010-09-01
  • 打赏
  • 举报
 回复
我也觉得mssql对一些以前的禁忌做过优化，使得它们也不慢了
当然，也是一定前提下的

我实际感觉最多的就是left join，索引正当的话，一点都不慢

关于in，我是希望以后语法能增强为：(f1,f2,...,fn) in ((x1,x2,...,xn),(y1,y2,...,yn))
路人乙e 2010-08-10
  • 打赏
  • 举报
 回复
没人在吗？有人给解释一下吗？
路人乙e 2010-08-06
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 obuntu 的回复:]

这跟索引的情况关系很大。看下执行计划就知道了。。
凡事无绝对。
而且你找的例子，都是非关联子查询，有时候确实会比较快。
不知道哪里看到的不要使用IN的文章，发出来共享下。。

9楼应该说的差不多了。
[/Quote]
几乎有关SQL优化的文章，都会提及in关键字，in的作用相当于or，而or会引起全表扫描，导致索引失效

我不得不再次提及以下SQL语句：
a) update tba set time=getdate() where id in (select id from tbb)
b) update tba set time=getdate() from tbb where tba.id=tbb.id
结果不是速度一样，而是“语句a比语句b执行速度快了近20倍！”
这点如何解释？

还有7楼代码，使用了“效率很低”的NOT IN（别说教程中不是这样说的），但是执行速度呢？
谁能针对这2个例子给个解释？
obuntu 2010-08-06
  • 打赏
  • 举报
 回复
这跟索引的情况关系很大。看下执行计划就知道了。。
凡事无绝对。
而且你找的例子，都是非关联子查询，有时候确实会比较快。
不知道哪里看到的不要使用IN的文章，发出来共享下。。

9楼应该说的差不多了。
昵称被占用了 2010-08-06
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 sq_zhuyi 的回复:]
引用 4 楼 haiwer 的回复:

二楼语句与搂主的一样

应该考虑
update tba set time=getdate() where exists (select 1 from tbb where tbb.id = tba.id)

连接曼的原因很可能是因为tbb的id不唯一，造成tba的一个id对应tbb的多条记录，做了多次更新，也就是说，你的例子是个特殊情况


……
[/Quote]

这两个意思完全不同，没有可比性
feixianxxx 2010-08-06
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 feilniu 的回复:]

SQL code

--肯定式逻辑
SELECT A.* FROM A INNER JOIN B ON A.SomethingID = B.ID
SELECT * FROM A WHERE SomethingID IN (SELECT ID FROM B)
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE B.ID = A.Somethin……
[/Quote]
你确定在否定逻辑下 NOT IN < LEFT JOIN?
feilniu 2010-08-06
  • 打赏
  • 举报
 回复 


--肯定式逻辑

SELECT A.* FROM A INNER JOIN B ON A.SomethingID = B.ID

SELECT * FROM A WHERE SomethingID IN (SELECT ID FROM B)

SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE B.ID = A.SomethingID)

--否定式逻辑

SELECT A.* FROM A LEFT JOIN B ON A.SomethingID = B.ID WHERE B.ID IS NULL

SELECT * FROM A WHERE SomethingID NOT IN (SELECT ID FROM B)

SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE B.ID = A.SomethingID)

在肯定式逻辑中：
1. JOIN的方式，只有当B.ID是唯一时才与其它两种方式等价。
2. 当B.ID唯一时，三种方式的效率基本相同（查询计划很可能是一样的）。
3. 当B.ID不唯一时，执行时间：EXISTS <= IN < JOIN。
在否定式逻辑中：
1. IN的方式，如果B.ID存在NULL的记录，查询返回空结果集。
2. 通常情况下，执行时间：NOT EXISTS < NOT IN < LEFT JOIN。

综上：
IN的好处是逻辑直观简单（通常是独立子查询）；缺点是只能判断单字段，并且当NOT IN时效率较低，而且NULL会导致不想要的结果。
EXISTS的好处是效率高，可以判断单字段和组合字段，并不受NULL的影响；缺点是逻辑稍微复杂（通常是相关子查询）。
JOIN用在这种场合，往往是吃力不讨好。JOIN的用途是联接两个表，而不是判断一个表的记录是否在另一个表。

p.s. 我以前对EXISTS有所忽视，现在发现这个语句实在好用。
路人乙e 2010-08-06
  • 打赏
  • 举报
 回复
另外很重要的一点，我的实验环境只是sql server 2008，以前用2000的时候记得in的效率是很低的，所以我才怀疑是2008对in做了特殊处理
路人乙e 2010-08-06
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 haiwer 的回复:]

二楼语句与搂主的一样

应该考虑
update tba set time=getdate() where exists (select 1 from tbb where tbb.id = tba.id)

连接曼的原因很可能是因为tbb的id不唯一，造成tba的一个id对应tbb的多条记录，做了多次更新，也就是说，你的例子是个特殊情况
[/Quote]

如果是特殊情况我就不会拿来问了，所有的语句、任何情况下（我涉及到的）都是IN的效率比较高，包括很有名的top+order排序:
a) select top 100 * from (select top 100000 * from seller_goods order by gds_id desc) a
order by gds_id asc

b) select top 100 * from seller_goods where gds_id not in
(select top 100000 gds_id from seller_goods order by gds_id desc)
order by gds_id desc

虽然语句b用到了not in，但语句b却比语句a快10多倍

:: in 关联的一般都是聚集索引列（起码我实验用的是）
永生天地 2010-08-06
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 haiwer 的回复:]
二楼语句与搂主的一样

应该考虑
update tba set time=getdate() where exists (select 1 from tbb where tbb.id = tba.id)

连接曼的原因很可能是因为tbb的id不唯一，造成tba的一个id对应tbb的多条记录，做了多次更新，也就是说，你的例子是个特殊情况
[/Quote]
支持，lz查询一下看看
华夏小卒 2010-08-06
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 haiwer 的回复:]
二楼语句与搂主的一样

应该考虑
update tba set time=getdate() where exists (select 1 from tbb where tbb.id = tba.id)

连接曼的原因很可能是因为tbb的id不唯一，造成tba的一个id对应tbb的多条记录，做了多次更新，也就是说，你的例子是个特殊情况
[/Quote]试了下，果然哦，还没注意到这个问题，
我一直以为from 后边，要2个表名都加上的呢
昵称被占用了 2010-08-06
  • 打赏
  • 举报
 回复
二楼语句与搂主的一样

应该考虑
update tba set time=getdate() where exists (select 1 from tbb where tbb.id = tba.id)

连接曼的原因很可能是因为tbb的id不唯一，造成tba的一个id对应tbb的多条记录，做了多次更新，也就是说，你的例子是个特殊情况

hao1hao2hao3 2010-08-06
  • 打赏
  • 举报
 回复
in语句不能一概的这么理解，还要看两个表的结构以及表中的记录数，如果这里tbb记录行很少，但是列很多，当然会出现这样的情况了，而且第二条语句确实有问题。
华夏小卒 2010-08-06
  • 打赏
  • 举报
 回复 
update tba 

set time=getdate() 

from tba,tbb 

where tba.id=tbb.id
华夏小卒 2010-08-06
  • 打赏
  • 举报
 回复
b) update tba set time=getdate() from tbb where tba.id=tbb.id
这个语句有问题吧
路人乙e 2010-08-06
  • 打赏
  • 举报
 回复
sql 2008对in做了优化，这正是我所怀疑的

如果真做了优化，又做了怎样的优化，为什么速度提升这么多？
（in当然一般用在索引或数字列上）

我相信其它数据库（如oracle, mysql）中in的效率依然很低，但是我没做测试
xiaoku 2010-08-06
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 sq_zhuyi 的回复:]
引用 12 楼 obuntu 的回复:

这跟索引的情况关系很大。看下执行计划就知道了。。
凡事无绝对。
而且你找的例子，都是非关联子查询，有时候确实会比较快。
不知道哪里看到的不要使用IN的文章，发出来共享下。。

9楼应该说的差不多了。

几乎有关SQL优化的文章，都会提及in关键字，in的作用相当于or，而or会引起全表扫描，导致索引失效

我不得不再次提及以下SQL语……
[/Quote]

几乎有关SQL优化的文章，都会提及in关键字，in的作用相当于or，而or会引起全表扫描，导致索引失效
-- 我想这里你理解上的错误，一般in 用在常量上才有你说的效果。在2008中，a in (select b)这样的结构都做了优化了。

7楼的两个语句效果是不一样的。

还提一点：不同的数据结构与数据量，语句的执行效果是不一样的！
zzz1975 2010-08-06
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 feilniu 的回复:]
SQL code

--肯定式逻辑
SELECT A.* FROM A INNER JOIN B ON A.SomethingID = B.ID
SELECT * FROM A WHERE SomethingID IN (SELECT ID FROM B)
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE B.ID = A.Somet……
[/Quote]

好彻底
hao1hao2hao3 2010-08-06
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 xys_777 的回复:]
值得推荐讨论
[/Quote]

对！让版主来推荐一下。
加载更多回复（3）
EXISTS、IN与JOIN性能分析 （实用）
原文出处：https://blog.csdn.net/caomiao2006/article/details/52099450?utm_medium=distribute.pc_relevant.none-task-blog-title-2&spm=1001.2101.3001.4242 EXISTS、IN与JOIN，都可以用来实现形如“查询A表中在（或不在）B表中的记录”的查询逻辑。 在论坛上看到很多人对此有所误解（如关于in的疑惑、用外连接和IsNull代替notin两帖），...
EXISTS、IN与JOIN性能分析
EXISTS、IN与JOIN，都可以用来实现形如“查询A表中在（或不在）B表中的记录”的查询逻辑。 在论坛上看到很多人对此有所误解（如关于in的疑惑、用 外连接 和 Is Null 代替 not in两帖），特做一简单测试。 测试结果： 测试代码较长，附于本帖最后。 图表中百分数表示同一组3个查询的执行时间比例。红色表示3个语句中最慢，绿色表示3个语句中最快的，并列则没加颜色。
oracle in和exists、not in和not exists原理和性能探究
对于in和exists、not in和not exists还是有很多的人有疑惑，更有甚者禁用not in，所有的地方都要用not exists，它真的高效吗？通过下面的使用我们来证明。 先制造一些数据 SQL> drop table test1 purge; SQL> drop table test2 purge; SQL> create table test1 as select * f
性能分析之性能建模简述
文章目录一、前言二、建模的定义三、业务模型和测试模型四、监控模型五、分析模型六、失效模型七、排队模型八、总结 一、前言 经常在性能领域里听到建模这个词，也看到有些人写了一些文章或 PPT 描述建模。今天在网上也搜索了一下，看到有五花八门的内容。我也写一下我对性能建模的理解。 二、建模的定义 在一开始做性能项目的时候，就有些疑惑，到底什么是性能建模呢。搜索了下建模的定义： 建模就是建立模型，就是为了理解事物而对事物做出的一种抽象，是对事物的一种无歧义的书面描述。 建立系统模型的过程，又称模型化。建模是研究系
sql 中or与in的查询效率对比
原文链接：http://blog.chinaunix.net/uid-20639775-id-3416737.html 在网上一直看到的是or和in的效率没啥区别，一直也感觉是这样，前几天刚好在看《mysql数据库开发的36条军规》的文章，里面提到了or和in的效率问题，文中提到or的效率为O(n)，而in的效率为O(logn), 当n越大的时候效率相差越明显。今天刚好有时间决定对心中的疑惑进行...
疑难问题

22,210

社区成员

121,729

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章