今天就跟大家聊聊有關(guān)MySQL中exists 和in的區(qū)別是什么，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

成都創(chuàng)新互聯(lián)于2013年創(chuàng)立，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都網(wǎng)站建設(shè)、成都做網(wǎng)站網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元陸豐做網(wǎng)站,已為上家服務(wù),為陸豐各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:028-86922220

MySQL中in和exists的性能優(yōu)劣以及各自的檢索數(shù)據(jù)的過(guò)程 ，以下面的語(yǔ)句為例子：

select * from user a where name='liuwenhe' and exists (select stuid from department b where depname='yunwei' and a.stuid =b.stuid );

select * from user where name='liuwenhe' and stuid in (select stuid from department where depname='yunwei');

MySQL exists和in檢索數(shù)據(jù)的過(guò)程：

1.首先說(shuō)下exists檢索過(guò)程,

注意其中A代表(user a where name='liuwenhe')的結(jié)果集，B代表(department b where depname='yunwei' )的結(jié)果集：

exists對(duì)外表A用loop逐條查詢，每次查詢都會(huì)去驗(yàn)證exists的條件語(yǔ)句(也就是exists后面括號(hào)里面的語(yǔ)句)，當(dāng) exists里的條件語(yǔ)句能夠返回記錄行時(shí)(只要能返回結(jié)果即可，不管你查詢的是什么內(nèi)容!!!)，條件就為真，就會(huì)返回當(dāng)前l(fā)oop到的A的這條記錄，反之如果exists里的條件語(yǔ)句不能返回記錄行，條件為假，則當(dāng)前l(fā)oop到的A的這條記錄被丟棄，注意：exists的條件就像一個(gè)bool條件，當(dāng)能返回結(jié)果集則為true，不能返回結(jié)果集則為 false;

對(duì)于exists的檢索過(guò)程可以用下面的腳本概括：

for ($i = 0; $i < count(A); $i++) {

$a = get_record(A, $i); #從A表逐條獲取記錄

if (B.id = $a[id]) #如果子條件成立，即返回true

$result[] = $a; 　　}

return $result;

例如：

select * from user where exists (select stuid from department where depname='yunwei' );

對(duì)user表的記錄逐條取出，由于exists條件中的select stuid from department where depname='yunwei' 永遠(yuǎn)能返回記錄行，那么user表的所有記錄都將被加入結(jié)果集，所以與 select * from user;是一樣的

例如：

select * from user where exists (select stuid from department where depname='yunwei+' );

not exists與exists相反，也就是當(dāng)exists條件有結(jié)果集返回時(shí)，loop到的記錄將被丟棄，否則將loop到的記錄加入結(jié)果集

總的來(lái)說(shuō)，如果user表結(jié)果集有n條記錄，那么exists查詢就是將這n條記錄逐條取出，然后判斷n遍exists條件 。

2.關(guān)于in子查詢的檢索過(guò)程：

MySQL先將子查詢結(jié)果存入臨時(shí)表T(可能在內(nèi)存中，也可能磁盤(pán)中)，確保子查詢只執(zhí)行一次，該表不記錄重復(fù)數(shù)據(jù)且采用哈希索引遍歷數(shù)據(jù)，然后通過(guò)T表的數(shù)據(jù)去遍歷外表，通過(guò)關(guān)聯(lián)關(guān)系得到外表的需要的數(shù)據(jù)，in查詢相當(dāng)于多個(gè)or條件的疊加，這個(gè)比較好理解，比如下面的查詢

select * from user where userId in (1, 2, 3);

等效于

select * from user where userId = 1 or userId = 2 or userId = 3;

not in與in相反，如下

select * from user where userId not in (1, 2, 3);

等效于

select * from user where userId != 1 and userId != 2 and userId != 3;

總的來(lái)說(shuō)，in查詢就是先將子查詢條件的記錄全都查出來(lái)，假設(shè)結(jié)果集為B，共有m條記錄，

然后在將子查詢條件的結(jié)果集分解成m個(gè)，再進(jìn)行m次主查詢，值得一提的是，in查詢的子條件返回結(jié)果必須只有一個(gè)字段，例如

select * from user where userId in (select id from B);

而不能是

select * from user where userId in (select id, age from B);

而exists就沒(méi)有這個(gè)限制

exists和in的性能

select * from user a where name='liuwenhe' and exists (select stuid from department b where depname='yunwei' and a.stuid =b.stuid );

select * from user where name='liuwenhe' and stuid in (select stuid from department where depname='yunwei');

1)根據(jù)前面介紹的檢索數(shù)據(jù)的過(guò)程，可以知道，針對(duì)上面的兩條sql中exists這種方式，是需要遍歷user表name='liuwenhe'的所有數(shù)據(jù)行N，并且判斷exists條件N次;并且如果department表的stuid 有索引，exists子查詢可以使用連接關(guān)系(也就是stuid)上的索引;所以exists方式適合 user表的結(jié)果集小，子查詢的結(jié)果集大的情況; 子查詢可以使用關(guān)聯(lián)關(guān)系列上的索引，所以效率高，故內(nèi)表大的適合使用exists;

2)not exists類似于exists的遍歷方式，也是loop外表，然后判斷exists條件

3)in是把外表user結(jié)果集和內(nèi)表department結(jié)果集做hash連接(應(yīng)該說(shuō)類似hash join，因?yàn)镸ySQL不支持hash join的方式)，先查詢內(nèi)表department結(jié)果集，再把內(nèi)表結(jié)果集與外表結(jié)果集匹配，對(duì)外表可以使用關(guān)系索引(也就是stuid列上的索引)，而內(nèi)表結(jié)果集多大都需要查詢，也就是說(shuō)department where depname='yunwei'的結(jié)果集D多大，都得遍歷全部的D，不可避免，故外表大的使用in，可加快效率。 主查詢可以使用關(guān)聯(lián)關(guān)系列上的索引，所以效率高，故外表結(jié)果集合大的適合使用in;

3)如果用not in ，和in一樣，內(nèi)表結(jié)果集需要全部掃描，由于not in ，所以外表的結(jié)果集也需要權(quán)標(biāo)掃描，都無(wú)法使用關(guān)系列上的索引(這種!=的范圍查詢無(wú)法使用任何索引)，效率低，可考慮使用not exists，也可使用A left join B on A.id=B.id where B.id is null 進(jìn)行優(yōu)化。

總結(jié)：

exists先對(duì)外表結(jié)果集loop循環(huán)再對(duì)內(nèi)表結(jié)果集進(jìn)行查詢。一直大家都認(rèn)為exists比in語(yǔ)句的效率要高，這種說(shuō)法其實(shí)是不準(zhǔn)確的。這個(gè)是要區(qū)分環(huán)境的。如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大。 如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢表結(jié)果集大的用exists，如果外表結(jié)果集大的則適合使用in，然后就是網(wǎng)絡(luò)中說(shuō)的外表的和內(nèi)表大的說(shuō)法也不準(zhǔn)確，應(yīng)該是外表結(jié)果集和內(nèi)表結(jié)果集合的大小，至于結(jié)果集前面已經(jīng)解釋過(guò)了。

看完上述內(nèi)容，你們對(duì)mysql中exists 和in的區(qū)別是什么有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝大家的支持。