in和exists的區(qū)別與SQL執(zhí)行效率分析
本文對in和exists的區(qū)別與SQL執(zhí)行效率進行了全面整理分析……
最近很多論壇又開始討論in和exists的區(qū)別與SQL執(zhí)行效率的問題，
本文特整理一些 in和exists的區(qū)別與SQL執(zhí)行效率分析
SQL中in可以分為三類：
1、形如select * from t1 where f1 in ('a','b')，應(yīng)該和以下兩種比較效率
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里的條件不受外層查詢的影響，這類查詢一般情況下，自動優(yōu)化會轉(zhuǎn)成exist語句，也就是效率和exist一樣。
3、形如select * from t1 where f1 in (select f1 from t2 where t2.fx=t1.fx)，
其中子查詢的where里的條件受外層查詢的影響，這類查詢的效率要看相關(guān)條件涉及的字段的索引情況和數(shù)據(jù)量多少，一般認為效率不如exists。
除了第一類in語句都是可以轉(zhuǎn)化成exists 語句的SQL，一般編程習慣應(yīng)該是用exists而不用in，而很少去考慮in和exists的執(zhí)行效率.
in和exists的SQL執(zhí)行效率分析
A，B兩個表，
(1)當只顯示一個表的數(shù)據(jù)如A，關(guān)系條件只一個如ID時，使用IN更快：
select * from A where id in (select id from B)
(2)當只顯示一個表的數(shù)據(jù)如A，關(guān)系條件不只一個如ID，col1時，使用IN就不方便了，可以使用EXISTS：
select * from A
where exists (select 1 from B where id = A.id and col1 = A.col1)
(3)當只顯示兩個表的數(shù)據(jù)時，使用IN，EXISTS都不合適，要使用連接：
select * from A left join B on id = A.id
所以使用何種方式，要根據(jù)要求來定。
這是一般情況下做的測試：
這是偶的測試結(jié)果:
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'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 2 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
表'sysschobjs'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
(1 行受影響)
(44 行受影響)
表'syscolpars'。掃描計數(shù) 47，邏輯讀取 97 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
表'sysschobjs'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 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'。掃描計數(shù) 1，邏輯讀取 10 次，物理讀取 0 次，預(yù)讀 15 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
表'sysschobjs'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
(1 行受影響)
(419 行受影響)
表'syscolpars'。掃描計數(shù) 1，邏輯讀取 10 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
表'sysschobjs'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
(1 行受影響)
測試結(jié)果(總體來講exists比in的效率高):
效率:條件因素的索引是非常關(guān)鍵的
把syscolumns 作為條件:syscolumns 數(shù)據(jù)大于sysobjects
用in
掃描計數(shù) 47，邏輯讀取 97 次，
用exists
掃描計數(shù) 1，邏輯讀取 3 次
把sysobjects作為條件:sysobjects的數(shù)據(jù)少于syscolumns
exists比in多預(yù)讀 15 次
對此我記得還做過如下測試：
表
test
結(jié)構(gòu)
id int identity(1,1), --id主鍵\自增
sort int, --類別,每一千條數(shù)據(jù)為一個類別
sid int --分類id
插入600w條數(shù)據(jù)
如果要查詢每個類別的最大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)?
的執(zhí)行效率要高三倍以上。具體的執(zhí)行時間忘記了。但是結(jié)果我記得很清楚。在此之前我一直推崇第二種寫法，后來就改第一種了。
in和exists的sql執(zhí)行效率分析,再簡單舉一個例子：
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含有重復(fù)值的記錄.
第一條sql語句使用in,但子查詢中與外部沒有連系.
第二條sql語句使用exists,但子查詢中與外部有連系.
大家看SQL查詢計劃,很清楚了.
selec v from @t group by v having count(*)> 1
這條Sql語句,它的執(zhí)行不依賴于主查詢主句(我也不知道怎么來描述in外面的和里面的,暫且這么叫吧,大家明白就行)
那么,SQL在查詢時就會優(yōu)化,即將它的結(jié)果集緩存起來
即緩存了
v
---
b
c
后續(xù)的操作,主查詢在每處理一步時,相當于在處理 where v in('b','c') 當然,語句不會這么轉(zhuǎn)化, 只是為了說明意思,也即主查詢每處理一行(記為currentROW時,子查詢不會再掃描表, 只會與緩存的結(jié)果進行匹配
而
select 1 from @t where id!=a.id and v=a.v
這一句,它的執(zhí)行結(jié)果依賴于主查詢中的每一行.
當處理主查詢第一行時 即 currentROW(id=1)時, 子查詢再次被執(zhí)行 select 1 from @t where id!=1 and v='a' 掃描全表,從第一行記 currentSubROW(id=1) 開始掃描,id相同,過濾,子查詢行下移,currentSubROW(id=2)繼續(xù),id不同,但v值不匹配,子查詢行繼續(xù)下移...直到currentSubROW(id=7)沒找到匹配的, 子查詢處理結(jié)束,第一行currentROW(id=1)被過濾,主查詢記錄行下移
處理第二行時,currentROW(id=2), 子查詢 select 1 from @t where id!=2 and v='b' ,第一行currentSubROW(id=1)v值不匹配,子查詢下移,第二行,id相同過濾,第三行,...到第六行,id不同,v值匹配, 找到匹配結(jié)果,即返回,不再往下處理記錄. 主查詢下移.
處理第三行時，以此類推...
sql優(yōu)化中，使用in和exist？ 主要是看你的篩選條件是在主查詢上還是在子查詢上。
通過分析，相信大家已經(jīng)對in和exists的區(qū)別、in和exists的SQL執(zhí)行效率有較清晰的了解。
本文對in和exists的區(qū)別與SQL執(zhí)行效率進行了全面整理分析……
最近很多論壇又開始討論in和exists的區(qū)別與SQL執(zhí)行效率的問題，
本文特整理一些 in和exists的區(qū)別與SQL執(zhí)行效率分析
SQL中in可以分為三類：
1、形如select * from t1 where f1 in ('a','b')，應(yīng)該和以下兩種比較效率
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里的條件不受外層查詢的影響，這類查詢一般情況下，自動優(yōu)化會轉(zhuǎn)成exist語句，也就是效率和exist一樣。
3、形如select * from t1 where f1 in (select f1 from t2 where t2.fx=t1.fx)，
其中子查詢的where里的條件受外層查詢的影響，這類查詢的效率要看相關(guān)條件涉及的字段的索引情況和數(shù)據(jù)量多少，一般認為效率不如exists。
除了第一類in語句都是可以轉(zhuǎn)化成exists 語句的SQL，一般編程習慣應(yīng)該是用exists而不用in，而很少去考慮in和exists的執(zhí)行效率.
in和exists的SQL執(zhí)行效率分析
A，B兩個表，
(1)當只顯示一個表的數(shù)據(jù)如A，關(guān)系條件只一個如ID時，使用IN更快：
select * from A where id in (select id from B)
(2)當只顯示一個表的數(shù)據(jù)如A，關(guān)系條件不只一個如ID，col1時，使用IN就不方便了，可以使用EXISTS：
select * from A
where exists (select 1 from B where id = A.id and col1 = A.col1)
(3)當只顯示兩個表的數(shù)據(jù)時，使用IN，EXISTS都不合適，要使用連接：
select * from A left join B on id = A.id
所以使用何種方式，要根據(jù)要求來定。
這是一般情況下做的測試：
這是偶的測試結(jié)果:
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'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 2 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
表'sysschobjs'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
(1 行受影響)
(44 行受影響)
表'syscolpars'。掃描計數(shù) 47，邏輯讀取 97 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
表'sysschobjs'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 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'。掃描計數(shù) 1，邏輯讀取 10 次，物理讀取 0 次，預(yù)讀 15 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
表'sysschobjs'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
(1 行受影響)
(419 行受影響)
表'syscolpars'。掃描計數(shù) 1，邏輯讀取 10 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
表'sysschobjs'。掃描計數(shù) 1，邏輯讀取 3 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。
(1 行受影響)
測試結(jié)果(總體來講exists比in的效率高):
效率:條件因素的索引是非常關(guān)鍵的
把syscolumns 作為條件:syscolumns 數(shù)據(jù)大于sysobjects
用in
掃描計數(shù) 47，邏輯讀取 97 次，
用exists
掃描計數(shù) 1，邏輯讀取 3 次
把sysobjects作為條件:sysobjects的數(shù)據(jù)少于syscolumns
exists比in多預(yù)讀 15 次
對此我記得還做過如下測試：
表
test
結(jié)構(gòu)
id int identity(1,1), --id主鍵\自增
sort int, --類別,每一千條數(shù)據(jù)為一個類別
sid int --分類id
插入600w條數(shù)據(jù)
如果要查詢每個類別的最大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執(zhí)行效率分析,再簡單舉一個例子：
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)?
第一條sql語句使用in,但子查詢中與外部沒有連系.
第二條sql語句使用exists,但子查詢中與外部有連系.
大家看SQL查詢計劃,很清楚了.
selec v from @t group by v having count(*)> 1
這條Sql語句,它的執(zhí)行不依賴于主查詢主句(我也不知道怎么來描述in外面的和里面的,暫且這么叫吧,大家明白就行)
那么,SQL在查詢時就會優(yōu)化,即將它的結(jié)果集緩存起來
即緩存了
v
---
b
c
后續(xù)的操作,主查詢在每處理一步時,相當于在處理 where v in('b','c') 當然,語句不會這么轉(zhuǎn)化, 只是為了說明意思,也即主查詢每處理一行(記為currentROW時,子查詢不會再掃描表, 只會與緩存的結(jié)果進行匹配
而
select 1 from @t where id!=a.id and v=a.v
這一句,它的執(zhí)行結(jié)果依賴于主查詢中的每一行.
當處理主查詢第一行時 即 currentROW(id=1)時, 子查詢再次被執(zhí)行 select 1 from @t where id!=1 and v='a' 掃描全表,從第一行記 currentSubROW(id=1) 開始掃描,id相同,過濾,子查詢行下移,currentSubROW(id=2)繼續(xù),id不同,但v值不匹配,子查詢行繼續(xù)下移...直到currentSubROW(id=7)沒找到匹配的, 子查詢處理結(jié)束,第一行currentROW(id=1)被過濾,主查詢記錄行下移
處理第二行時,currentROW(id=2), 子查詢 select 1 from @t where id!=2 and v='b' ,第一行currentSubROW(id=1)v值不匹配,子查詢下移,第二行,id相同過濾,第三行,...到第六行,id不同,v值匹配, 找到匹配結(jié)果,即返回,不再往下處理記錄. 主查詢下移.
處理第三行時，以此類推...
sql優(yōu)化中，使用in和exist？ 主要是看你的篩選條件是在主查詢上還是在子查詢上。
通過分析，相信大家已經(jīng)對in和exists的區(qū)別、in和exists的SQL執(zhí)行效率有較清晰的了解。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
