轉(zhuǎn)自：精品唯居
表展示
首先捏检，查詢(xún)中涉及到的兩個(gè)表梁丘，一個(gè)user和一個(gè)order表择浊，具體表的內(nèi)容如下：
user表：
order表：
in
確定給定的值是否與子查詢(xún)或列表中的值相匹配追城。in在查詢(xún)的時(shí)候，首先查詢(xún)子查詢(xún)的表故觅，然后將內(nèi)表和外表做一個(gè)笛卡爾積谐区，然后按照條件進(jìn)行篩選。所以相對(duì)內(nèi)表比較小的時(shí)候逻卖，in的速度較快。
具體sql語(yǔ)句如下：
1SELECT 2* 3FROM 4`user` 5WHERE 6`user`.idIN ( 7SELECT 8`order`.user_id 9FROM10`order`11)
這條語(yǔ)句很簡(jiǎn)單昭抒，通過(guò)子查詢(xún)查到的user_id 的數(shù)據(jù)评也，去匹配user表中的id然后得到結(jié)果。該語(yǔ)句執(zhí)行結(jié)果如下：
它的執(zhí)行流程是什么樣子的呢灭返？讓我們一起來(lái)看一下盗迟。
首先，在數(shù)據(jù)庫(kù)內(nèi)部熙含，查詢(xún)子查詢(xún)罚缕，執(zhí)行如下代碼：
SELECT? ? ? ? ? ? `order`.user_idFROM? ? ? ? ? ? `order`
執(zhí)行完畢后，得到結(jié)果如下：
此時(shí)糖耸，將查詢(xún)到的結(jié)果和原有的user表做一個(gè)笛卡爾積富纸，結(jié)果如下：
此時(shí)滞乙，再根據(jù)我們的user.id IN order.user_id的條件，將結(jié)果進(jìn)行篩選（既比較id列和user_id 列的值是否相等腌乡，將不相等的刪除）。最后夜牡，得到兩條符合條件的數(shù)據(jù)与纽。
exists
指定一個(gè)子查詢(xún)，檢測(cè)行的存在。遍歷循環(huán)外表急迂，然后看外表中的記錄有沒(méi)有和內(nèi)表的數(shù)據(jù)一樣的影所。匹配上就將結(jié)果放入結(jié)果集中。
具體sql語(yǔ)句如下：
1SELECT 2`user`.* 3FROM 4`user` 5WHERE 6EXISTS ( 7SELECT 8`order`.user_id 9FROM10`order`11WHERE12`user`.id=`order`.user_id13)
這條sql語(yǔ)句的執(zhí)行結(jié)果和上面的in的執(zhí)行結(jié)果是一樣的僚碎。
但是猴娩，不一樣的是它們的執(zhí)行流程完全不一樣：
使用exists關(guān)鍵字進(jìn)行查詢(xún)的時(shí)候，首先听盖，我們先查詢(xún)的不是子查詢(xún)的內(nèi)容胀溺，而是查我們的主查詢(xún)的表，也就是說(shuō)皆看，我們先執(zhí)行的sql語(yǔ)句是：
SELECT?`user`.*?FROM?`user`
得到的結(jié)果如下：
然后仓坞，根據(jù)表的每一條記錄，執(zhí)行以下語(yǔ)句腰吟，依次去判斷where后面的條件是否成立：
EXISTS (
? ? ? ? SELECT? ? ? ? ? ? `order`.user_idFROM? ? ? ? ? ? `order`
? ? ? ? WHERE? ? ? ? ? ? `user`.id=`order`.user_id? ? )
如果成立則返回true不成立則返回false无埃。如果返回的是true的話(huà)，則該行結(jié)果保留毛雇，如果返回的是false的話(huà)嫉称，則刪除該行，最后將得到的結(jié)果返回灵疮。
區(qū)別及應(yīng)用場(chǎng)景
in 和 exists的區(qū)別: 如果子查詢(xún)得出的結(jié)果集記錄較少织阅，主查詢(xún)中的表較大且又有索引時(shí)應(yīng)該用in, 反之如果外層的主查詢(xún)記錄較少，子查詢(xún)中的表大震捣，又有索引時(shí)使用exists荔棉。其實(shí)我們區(qū)分in和exists主要是造成了驅(qū)動(dòng)順序的改變(這是性能變化的關(guān)鍵)，如果是exists蒿赢，那么以外層表為驅(qū)動(dòng)表润樱，先被訪(fǎng)問(wèn)，如果是IN羡棵，那么先執(zhí)行子查詢(xún)壹若，所以我們會(huì)以驅(qū)動(dòng)表的快速返回為目標(biāo)，那么就會(huì)考慮到索引及結(jié)果集的關(guān)系了 皂冰，另外IN時(shí)不對(duì)NULL進(jìn)行處理店展。
in 是把外表和內(nèi)表作hash 連接，而exists是對(duì)外表作loop循環(huán)秃流，每次loop循環(huán)再對(duì)內(nèi)表進(jìn)行查詢(xún)壁查。一直以來(lái)認(rèn)為exists比in效率高的說(shuō)法是不準(zhǔn)確的。
not in 和not exists
如果查詢(xún)語(yǔ)句使用了not in 那么內(nèi)外表都進(jìn)行全表掃描剔应，沒(méi)有用到索引睡腿；而not extsts 的子查詢(xún)依然能用到表上的索引语御。所以無(wú)論那個(gè)表大，用not exists都比not in要快席怪。
