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