本篇內(nèi)容介紹了“in, not in , exists , not exists它們有什么區(qū)別”的有關(guān)知識(shí)，在實(shí)際案例的操作過程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
專注于為中小企業(yè)提供成都網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)岳池免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了超過千家企業(yè)的穩(wěn)健成長(zhǎng)，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。
用法講解
為了方便，我們創(chuàng)建兩張表 t1 和 t2 。并分別加入一些數(shù)據(jù)。(id為主鍵，name為普通索引)
重慶分公司
重慶分公司
-- t1 DROP TABLE IF EXISTS `t1`; CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_t1_name` (`name`(191)) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1009 DEFAULT CHARSET=utf8mb4; INSERT INTO `t1` VALUES ('1001', '張三', '北京'), ('1002', '李四', '天津'), ('1003', '王五', '北京'), ('1004', '趙六', '河北'), ('1005', '杰克', '河南'), ('1006', '湯姆', '河南'), ('1007', '貝爾', '上海'), ('1008', '孫琪', '北京'); -- t2 DROP TABLE IF EXISTS `t2`; CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_t2_name`(`name`(191)) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1014 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; INSERT INTO `t2` VALUES (1001, '張三', '北京'); INSERT INTO `t2` VALUES (1004, '趙六', '河北'); INSERT INTO `t2` VALUES (1005, '杰克', '河南'); INSERT INTO `t2` VALUES (1007, '貝爾', '上海'); INSERT INTO `t2` VALUES (1008, '孫琪', '北京'); INSERT INTO `t2` VALUES (1009, '曹操', '魏國(guó)'); INSERT INTO `t2` VALUES (1010, '劉備', '蜀國(guó)'); INSERT INTO `t2` VALUES (1011, '孫權(quán)', '吳國(guó)'); INSERT INTO `t2` VALUES (1012, '諸葛亮', '蜀國(guó)'); INSERT INTO `t2` VALUES (1013, '典韋', '魏國(guó)');
那么，對(duì)于當(dāng)前的問題，就很簡(jiǎn)單了，用 not in 或者 not exists 都可以把 t1 表中比 t2 表多出的那部分?jǐn)?shù)據(jù)給挑出來(lái)。(當(dāng)然，t2 比 t1 多出來(lái)的那部分不算)
這里假設(shè)用 name 來(lái)匹配數(shù)據(jù)。
select * from t1 where name not in (select name from t2); 或者用 select * from t1 where not exists (select name from t2 where t1.name=t2.name);
得到的結(jié)果都是一樣的。
但是，需要注意的是，not in 和 not exists 還是有不同點(diǎn)的。
在使用 not in 的時(shí)候，需要保證子查詢的匹配字段是非空的。如，此表 t2 中的 name 需要有非空限制。如若不然，就會(huì)導(dǎo)致 not in 返回的整個(gè)結(jié)果集為空。
例如，我在 t2 表中加入一條 name 為空的數(shù)據(jù)。
INSERT INTO `t2` VALUES (1014, NULL, '魏國(guó)');
則此時(shí)，not in 結(jié)果就會(huì)返回空。
另外需要明白的是， exists 返回的結(jié)果是一個(gè) boolean 值 true 或者 false ，而不是某個(gè)結(jié)果集。因?yàn)樗魂P(guān)心返回的具體數(shù)據(jù)是什么，只是外層查詢需要拿這個(gè)布爾值做判斷。
區(qū)別是，用 exists 時(shí)，若子查詢查到了數(shù)據(jù)，則返回真。用 not exists 時(shí)，若子查詢沒有查到數(shù)據(jù)，則返回真。
由于 exists 子查詢不關(guān)心具體返回的數(shù)據(jù)是什么。因此，以上的語(yǔ)句完全可以修改為如下，
-- 子查詢中 name 可以修改為其他任意的字段，如此處改為 1 。 select * from t1 where not exists (select 1 from t2 where t1.name=t2.name);
從執(zhí)行效率來(lái)說(shuō)，1 > column > * 。因此推薦用 select 1。(準(zhǔn)確的說(shuō)應(yīng)該是常量值)
in, exists 執(zhí)行流程
1、 對(duì)于 in 查詢來(lái)說(shuō)，會(huì)先執(zhí)行子查詢，如上邊的 t2 表，然后把查詢得到的結(jié)果和外表 t1 做笛卡爾積，再通過條件進(jìn)行篩選(這里的條件就是指 name 是否相等)，把每個(gè)符合條件的數(shù)據(jù)都加入到結(jié)果集中。
sql 如下，
select * from t1 where name in (select name from t2);
偽代碼如下：
for(x in A){ for(y in B){ if(condition is true) {result.add();} } }這里的 condition 其實(shí)就是對(duì)比兩張表中的 name 是否相同。
2、對(duì)于 exists 來(lái)說(shuō)，是先查詢遍歷外表 t1 ，然后每次遍歷時(shí)，再檢查在內(nèi)表是否符合匹配條件，即檢查是否存在 name 相等的數(shù)據(jù)。
sql 如下，
select * from t1 where name exists (select 1 from t2);
偽代碼如下：
for(x in A){ if(exists condition is true){result.add();} }對(duì)應(yīng)于此例，就是從 id 為 1001 開始遍歷 t1 表 ，然后遍歷時(shí)檢查 t2 中是否有相等的 name 。
如 id=1001時(shí)，張三存在于 t2 表中，則返回 true，把 t1 中張三的這條記錄加入到結(jié)果集，繼續(xù)下次循環(huán)。id=1002 時(shí)，李四不在 t2 表中，則返回 false，不做任何操作，繼續(xù)下次循環(huán)。直到遍歷完整個(gè) t1 表。
是否走索引?
針對(duì)網(wǎng)上說(shuō)的 in 和 exists 不走索引，那么究竟是否如此呢?
我們?cè)?MySQL 5.7.18 中驗(yàn)證一下。(注意版本號(hào)哦)
單表查詢
首先，驗(yàn)證單表的最簡(jiǎn)單的情況。我們就以 t1 表為例，id為主鍵， name 為普通索引。
分別執(zhí)行以下語(yǔ)句，
explain select * from t1 where id in (1001,1002,1003,1004); explain select * from t1 where id in (1001,1002,1003,1004,1005); explain select * from t1 where name in ('張三','李四'); explain select * from t1 where name in ('張三','李四','王五');為什么我要分別查不同的 id 個(gè)數(shù)呢?看截圖，
會(huì)驚奇的發(fā)現(xiàn)，當(dāng) id 是四個(gè)值時(shí)，還走主鍵索引。而當(dāng) id 是五個(gè)值時(shí)，就不走索引了。這就很耐人尋味了。
再看 name 的情況，
同樣的當(dāng)值多了之后，就不走索引了。
所以，我猜測(cè)這個(gè)跟匹配字段的長(zhǎng)度有關(guān)。按照漢字是三個(gè)字節(jié)來(lái)計(jì)算，且程序設(shè)計(jì)中喜歡用2的n次冪的尿性，這里大概就是以 16 個(gè)字節(jié)為分界點(diǎn)。
然而，我又以同樣的數(shù)據(jù)，去我的服務(wù)器上查詢(版本號(hào) 5.7.22)，發(fā)現(xiàn)四個(gè)id值時(shí)，就不走索引了。因此，估算這里的臨界值為 12 個(gè)字節(jié)。
不管怎樣，這說(shuō)明了，在 MySQL 中應(yīng)該對(duì) in 查詢的字節(jié)長(zhǎng)度是有限制的。(沒有官方確切說(shuō)法，所以，僅供參考)
多表涉及子查詢
我們主要是去看當(dāng)前的這個(gè)例子中的兩表查詢時(shí)， in 和 exists 是否走索引。
一、分別執(zhí)行以下語(yǔ)句，主鍵索引(id)和普通索引(name)，在 in , not in 下是否走索引。
explain select * from t1 where id in (select id from t2); --1 explain select * from t1 where name in (select name from t2); --2 explain select * from t1 where id not in (select id from t2); --3 explain select * from t1 where name not in (select name from t2); --4
結(jié)果截圖如下，
1、t1 走索引，t2 走索引。
1
2、t1 不走索引，t2不走索引。(此種情況，實(shí)測(cè)若把name改為唯一索引，則t1也會(huì)走索引)
2
3、t1 不走索引，t2走索引。
3
4、t1不走索引，t2不走索引。
4
我滴天，這結(jié)果看起來(lái)亂七八糟的，好像走不走索引，完全看心情。
但是，我們發(fā)現(xiàn)只有第一種情況，即用主鍵索引字段匹配，且用 in 的情況下，兩張表才都走索引。
這個(gè)到底是不是規(guī)律呢?有待考察，且往下看。
二、接下來(lái)測(cè)試，主鍵索引和普通索引在 exists 和 not exists 下的情況。sql如下，
explain select * from t1 where exists (select 1 from t2 where t1.id=t2.id); explain select * from t1 where exists (select 1 from t2 where t1.name=t2.name); explain select * from t1 where not exists (select 1 from t2 where t1.id=t2.id); explain select * from t1 where not exists (select 1 from t2 where t1.name=t2.name);
這個(gè)結(jié)果就非常有規(guī)律了，且看，
有沒有發(fā)現(xiàn)， t1 表哪種情況都不會(huì)走索引，而 t2 表是有索引的情況下就會(huì)走索引。為什么會(huì)出現(xiàn)這種情況?
其實(shí)，上一小節(jié)說(shuō)到了 exists 的執(zhí)行流程，就已經(jīng)說(shuō)明問題了。
它是以外層表為驅(qū)動(dòng)表，無(wú)論如何都會(huì)循環(huán)遍歷的，所以會(huì)全表掃描。而內(nèi)層表通過走索引，可以快速判斷當(dāng)前記錄是否匹配。
效率如何?
針對(duì)網(wǎng)上說(shuō)的 exists 一定比 in 的執(zhí)行效率高，我們做一個(gè)測(cè)試。
分別在 t1，t2 中插入 100W，200W 條數(shù)據(jù)。
我這里，用的是自定義函數(shù)來(lái)循環(huán)插入，語(yǔ)句參考如下，(沒有把表名抽離成變量，因?yàn)槲覜]有找到方法，尷尬)
-- 傳入需要插入數(shù)據(jù)的id開始值和數(shù)據(jù)量大小，函數(shù)返回結(jié)果為最終插入的條數(shù)，此值正常應(yīng)該等于數(shù)據(jù)量大小。 -- id自增，循環(huán)往 t1 表添加數(shù)據(jù)。這里為了方便，id、name取同一個(gè)變量，address就為北京。 delimiter // drop function if exists insert_datas1// create function insert_datas1(in_start int(11),in_len int(11)) returns int(11) begin declare cur_len int(11) default 0; declare cur_id int(11); set cur_id = in_start; while cur_len < in_len do insert into t1 values(cur_id,cur_id,'北京'); set cur_len = cur_len + 1; set cur_id = cur_id + 1; end while; return cur_len; end // delimiter ; -- 同樣的，往 t2 表插入數(shù)據(jù) delimiter // drop function if exists insert_datas2// create function insert_datas2(in_start int(11),in_len int(11)) returns int(11) begin declare cur_len int(11) default 0; declare cur_id int(11); set cur_id = in_start; while cur_len < in_len do insert into t2 values(cur_id,cur_id,'北京'); set cur_len = cur_len + 1; set cur_id = cur_id + 1; end while; return cur_len; end // delimiter ;
在此之前，先清空表里的數(shù)據(jù)，然后執(zhí)行函數(shù)，
select insert_datas1(1,1000000);
對(duì) t2 做同樣的處理，不過為了兩張表數(shù)據(jù)有交叉，就從 70W 開始，然后插入 200W 數(shù)據(jù)。
select insert_datas2(700000,2000000);
在家里的電腦，實(shí)際執(zhí)行時(shí)間，分別為 36s 和 74s。
不知為何，家里的電腦還沒有在 Docker 虛擬機(jī)中跑的腳本快。。害，就這樣湊合著用吧。
等我有了新歡錢，就把它換掉，哼哼。
同樣的，把上邊的執(zhí)行計(jì)劃都執(zhí)行一遍，進(jìn)行對(duì)比。我這里就不貼圖了。
in 和 exists 孰快孰慢
為了方便，主要拿以下這兩個(gè) sql 來(lái)對(duì)比分析。
select * from t1 where id in (select id from t2); select * from t1 where exists (select 1 from t2 where t1.id=t2.id);
執(zhí)行結(jié)果顯示，兩個(gè) sql 分別執(zhí)行 1.3s 和 3.4s 。
注意此時(shí)，t1 表數(shù)據(jù)量為 100W， t2 表數(shù)據(jù)量為 200W 。
按照網(wǎng)上對(duì) in 和 exists 區(qū)別的通俗說(shuō)法，
如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大;如果兩個(gè)表中一個(gè)較小一個(gè)較大，則子查詢表大的用exists，子查詢表小的用in;
對(duì)應(yīng)于此處就是：
當(dāng) t1 為小表， t2 為大表時(shí)，應(yīng)該用 exists ，這樣效率高。
當(dāng) t1 為大表，t2 為小表時(shí)，應(yīng)該用 in，這樣效率較高。
而我用實(shí)際數(shù)據(jù)測(cè)試，就把第一種說(shuō)法給推翻了。因?yàn)楹苊黠@，t1 是小表，但是 in 比 exists 的執(zhí)行速度還快。
為了繼續(xù)測(cè)驗(yàn)它這個(gè)觀點(diǎn)，我把兩個(gè)表的內(nèi)表外表關(guān)系調(diào)換一下，讓 t2 大表作為外表，來(lái)對(duì)比查詢，
select * from t2 where id in (select id from t1); select * from t2 where exists (select 1 from t1 where t1.id=t2.id);
執(zhí)行結(jié)果顯示，兩個(gè) sql 分別執(zhí)行 1.8s 和 10.0s 。
是不是很有意思。可以發(fā)現(xiàn)，
對(duì)于 in 來(lái)說(shuō)，大表小表調(diào)換了內(nèi)外層關(guān)系，執(zhí)行時(shí)間并無(wú)太大區(qū)別。一個(gè)是 1.3s，一個(gè)是 1.8s。
對(duì)于 exists 來(lái)說(shuō)，大小表調(diào)換了內(nèi)外層關(guān)系，執(zhí)行時(shí)間天壤之別，一個(gè)是 3.4s ，一個(gè)是 10.0s，足足慢了兩倍。
一、以查詢優(yōu)化器維度對(duì)比。
為了探究這個(gè)結(jié)果的原因。我去查看它們分別在查詢優(yōu)化器中優(yōu)化后的 sql 。
-- 此為 5.7 寫法，如果是 5.6版本，需要用 explain extended ... explain select * from t1 where id in (select id from t2); -- 本意為顯示警告信息。但是和 explain 一塊兒使用，就會(huì)顯示出優(yōu)化后的sql。需要注意使用順序。 show warnings;
-- 此為 5.7 寫法，如果是 5.6版本，需要用 explain extended ...explain select * from t1 where id in (select id from t2);-- 本意為顯示警告信息。但是和 explain 一塊兒使用，就會(huì)顯示出優(yōu)化后的sql。需要注意使用順序。show warnings;
在結(jié)果 Message 里邊就會(huì)顯示我們要的語(yǔ)句。
-- message 優(yōu)化后的sql select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name`,`test`.`t1`.`address` AS `address` from `test`.`t2` join `test`.`t1` where (`test`.`t2`.`id` = `test`.`t1`.`id`)
可以發(fā)現(xiàn)，這里它把 in 轉(zhuǎn)換為了 join 來(lái)執(zhí)行。
這里沒有用 on，而用了 where，是因?yàn)楫?dāng)只有 join 時(shí)，后邊的 on 可以用 where 來(lái)代替。即 join on 等價(jià)于 join where 。
PS： 這里我們也可以發(fā)現(xiàn)，select * 最終會(huì)被轉(zhuǎn)化為具體的字段，知道為什么我們不建議用 select * 了吧。
同樣的，以 t2 大表為外表的查詢情況，也查看優(yōu)化后的語(yǔ)句。
explain select * from t2 where id in (select id from t1); show warnings;
我們會(huì)發(fā)現(xiàn)，它也會(huì)轉(zhuǎn)化為 join 的。
select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`address` AS `address` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`id` = `test`.`t1`.`id`)
這里不再貼 exists 的轉(zhuǎn)化 sql ，其實(shí)它沒有什么大的變化。
二、以執(zhí)行計(jì)劃維度對(duì)比。
我們?cè)僖詧?zhí)行計(jì)劃維度來(lái)對(duì)比他們的區(qū)別。
explain select * from t1 where id in (select id from t2); explain select * from t2 where id in (select id from t1); explain select * from t1 where exists (select 1 from t2 where t1.id=t2.id); explain select * from t2 where exists (select 1 from t1 where t1.id=t2.id);
執(zhí)行結(jié)果分別為，
1
2
3
4
可以發(fā)現(xiàn)，對(duì)于 in 來(lái)說(shuō)，大表 t2 做外表還是內(nèi)表，都會(huì)走索引的，小表 t1 做內(nèi)表時(shí)也會(huì)走索引。看它們的 rows 一列也可以看出來(lái)，前兩張圖結(jié)果一樣。
對(duì)于 exists 來(lái)說(shuō)，當(dāng)小表 t1 做外表時(shí)，t1 全表掃描，rows 近 100W;當(dāng) 大表 t2 做外表時(shí)， t2 全表掃描，rows 近 200W 。這也是為什么 t2 做外表時(shí)，執(zhí)行效率非常低的原因。
因?yàn)閷?duì)于 exists 來(lái)說(shuō)，外表總會(huì)執(zhí)行全表掃描的，當(dāng)然表數(shù)據(jù)越少越好了。
最終結(jié)論： 外層大表內(nèi)層小表，用in。外層小表內(nèi)層大表，in和exists效率差不多(甚至 in 比 exists 還快，而并不是網(wǎng)上說(shuō)的 exists 比 in 效率高)。
not in 和 not exists 孰快孰慢
此外，實(shí)測(cè)對(duì)比 not in 和 not exists 。
explain select * from t1 where id not in (select id from t2); explain select * from t1 where not exists (select 1 from t2 where t1.id=t2.id); explain select * from t1 where name not in (select name from t2); explain select * from t1 where not exists (select 1 from t2 where t1.name=t2.name); explain select * from t2 where id not in (select id from t1); explain select * from t2 where not exists (select 1 from t1 where t1.id=t2.id); explain select * from t2 where name not in (select name from t1); explain select * from t2 where not exists (select 1 from t1 where t1.name=t2.name);
小表做外表的情況下。對(duì)于主鍵來(lái)說(shuō)， not exists 比 not in 快。對(duì)于普通索引來(lái)說(shuō)， not in 和 not exists 差不了多少，甚至 not in 會(huì)稍快。
大表做外表的情況下，對(duì)于主鍵來(lái)說(shuō)， not in 比 not exists 快。對(duì)于普通索引來(lái)說(shuō)， not in 和 not exists 差不了多少，甚至 not in 會(huì)稍快。
感興趣的同學(xué)，可自行嘗試。以上邊的兩個(gè)維度(查詢優(yōu)化器和執(zhí)行計(jì)劃)分別來(lái)對(duì)比一下。
join 的嵌套循環(huán) (Nested-Loop Join)
為了理解為什么這里的 in 會(huì)轉(zhuǎn)換為 join ，我感覺有必要了解一下 join 的三種嵌套循環(huán)連接。
1、簡(jiǎn)單嵌套循環(huán)連接，Simple Nested-Loop Join ，簡(jiǎn)稱 SNLJ
join 即是 inner join ，內(nèi)連接，它是一個(gè)笛卡爾積，即利用雙層循環(huán)遍歷兩張表。
我們知道，一般在 sql 中都會(huì)以小表作為驅(qū)動(dòng)表。所以，對(duì)于 A，B 兩張表，若A的結(jié)果集較少，則把它放在外層循環(huán)，作為驅(qū)動(dòng)表。自然，B 就在內(nèi)層循環(huán)，作為被驅(qū)動(dòng)表。
簡(jiǎn)單嵌套循環(huán)，就是最簡(jiǎn)單的一種情況，沒有做任何優(yōu)化。
因此，復(fù)雜度也是最高的，O(mn)。偽代碼如下，
for(id1 in A){ for(id2 in B){ if(id1==id2){ result.add(); } } }2、索引嵌套循環(huán)連接，Index Nested-Loop Join ，簡(jiǎn)稱 INLJ
看名字也能看出來(lái)了，這是通過索引進(jìn)行匹配的。外層表直接和內(nèi)層表的索引進(jìn)行匹配，這樣就不需要遍歷整個(gè)內(nèi)層表了。利用索引，減少了外層表和內(nèi)層表的匹配次數(shù)。
所以，此種情況要求內(nèi)層表的列要有索引。
偽代碼如下，
for(id1 in A){ if(id1 matched B.id){ result.add(); } }3、塊索引嵌套連接，Block Nested-Loop Join ，簡(jiǎn)稱 BNLJ
塊索引嵌套連接，是通過緩存外層表的數(shù)據(jù)到 join buffer 中，然后 buffer 中的數(shù)據(jù)批量和內(nèi)層表數(shù)據(jù)進(jìn)行匹配，從而減少內(nèi)層循環(huán)的次數(shù)。
以外層循環(huán)100次為例，正常情況下需要在內(nèi)層循環(huán)讀取外層數(shù)據(jù)100次。如果以每10條數(shù)據(jù)存入緩存buffer中，并傳遞給內(nèi)層循環(huán)，則內(nèi)層循環(huán)只需要讀取10次(100/10)就可以了。這樣就降低了內(nèi)層循環(huán)的讀取次數(shù)。
所以，這里轉(zhuǎn)化為 join，可以用到索引嵌套循環(huán)連接，從而提高了執(zhí)行效率。
“in, not in , exists , not exists它們有什么區(qū)別”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
