共計(jì) 2953 個(gè)字符，預(yù)計(jì)需要花費(fèi) 8 分鐘才能閱讀完成。

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

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)前 loop 到的 A 的這條記錄，反之如果 exists 里的條件語(yǔ)句不能返回記錄行，條件為假，則當(dāng)前 loop 到的 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 類(lèi)似于 exists 的遍歷方式，也是 loop 外表，然后判斷 exists 條件

3)in 是把外表 user 結(jié)果集和內(nèi)表 department 結(jié)果集做 hash 連接(應(yīng)該說(shuō)類(lèi)似 hash join，因?yàn)?MySQL 不支持 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)注丸趣 TV 行業(yè)資訊頻道，感謝大家的支持。