關(guān)于In與Exists的比較于样，先說(shuō)結(jié)論槽奕，歸納出IN 和Exists的適用場(chǎng)景：

1）IN查詢(xún)?cè)趦?nèi)部表和外部表上都可以使用到索引大磺。
2）Exists查詢(xún)僅在內(nèi)部表上可以使用到索引给郊。
3）當(dāng)子查詢(xún)結(jié)果集很大菩浙，而外部表較小的時(shí)候，Exists的Block Nested Loop(Block 嵌套循環(huán))的作用開(kāi)始顯現(xiàn)睬愤，并彌補(bǔ)外部表無(wú)法用到索引的缺陷片仿，查詢(xún)效率會(huì)優(yōu)于IN。
4）當(dāng)子查詢(xún)結(jié)果集較小戴涝，而外部表很大的時(shí)候，Exists的Block嵌套循環(huán)優(yōu)化效果不明顯，IN 的外表索引優(yōu)勢(shì)占主要作用啥刻，此時(shí)IN的查詢(xún)效率會(huì)優(yōu)于Exists奸鸯。
5）網(wǎng)上的說(shuō)法不準(zhǔn)確，即表的規(guī)模不是看內(nèi)部表和外部表可帽，而是外部表和子查詢(xún)結(jié)果集娄涩。
6）最后一點(diǎn)，也是最重要的一點(diǎn)：世間沒(méi)有絕對(duì)的真理映跟，掌握事物的本質(zhì)蓄拣，針對(duì)不同的場(chǎng)景進(jìn)行實(shí)踐驗(yàn)證才是最可靠有效的方法。

以下是原文努隙，之前和我一起討論這個(gè)問(wèn)題的朋友在跟他公司DBA討論并做了幾次實(shí)驗(yàn)之后整理的文章如下：

背景介紹

最近在寫(xiě)SQL語(yǔ)句時(shí)球恤，對(duì)選擇IN 還是Exists 猶豫不決，于是把兩種方法的SQL都寫(xiě)出來(lái)對(duì)比一下執(zhí)行效率荸镊，發(fā)現(xiàn)IN的查詢(xún)效率比Exists高了很多咽斧，于是想當(dāng)然的認(rèn)為IN的效率比Exists好，但本著尋根究底的原則躬存，我想知道這個(gè)結(jié)論是否適用所有場(chǎng)景张惹，以及為什么會(huì)出現(xiàn)這個(gè)結(jié)果。
網(wǎng)上查了一下相關(guān)資料岭洲，大體可以歸納為：外部表小宛逗，內(nèi)部表大時(shí)，適用Exists盾剩；外部表大雷激，內(nèi)部表小時(shí)，適用IN彪腔。那我就困惑了侥锦，因?yàn)槲业腟QL語(yǔ)句里面，外表只有1W級(jí)別的數(shù)據(jù)德挣，內(nèi)表有30W級(jí)別的數(shù)據(jù)恭垦，按網(wǎng)上的說(shuō)法應(yīng)該是Exists的效率會(huì)比IN高的，但我的結(jié)果剛好相反８裥帷番挺！
“沒(méi)有調(diào)查就沒(méi)有發(fā)言權(quán)”！于是我開(kāi)始研究IN 和Exists的實(shí)際執(zhí)行過(guò)程屯掖，從實(shí)踐的角度出發(fā)玄柏，在根本上去尋找原因，于是有了這篇博文分享贴铜。

實(shí)驗(yàn)數(shù)據(jù)

我的實(shí)驗(yàn)數(shù)據(jù)包括兩張表：t_author表 和 t_poetry表粪摘。
對(duì)應(yīng)表的數(shù)據(jù)量：
t_author表瀑晒，13355條記錄；
t_poetry表徘意，289917條記錄苔悦。
對(duì)應(yīng)的表結(jié)構(gòu)如下：

CREATE TABLE `t_poetry` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`poetry_id` bigint(20) NOT NULL COMMENT '詩(shī)詞id',
`poetry_name` varchar(200) NOT NULL COMMENT '詩(shī)詞名稱(chēng)',
`author_id` bigint(20) NOT NULL COMMENT '作者id'
PRIMARY KEY (`id`),
UNIQUE KEY `pid_idx` (`poetry_id`) USING BTREE,
KEY `aid_idx` (`author_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=291270 DEFAULT CHARSET=utf8mb4


CREATE TABLE `t_author` (
`id` int(15) NOT NULL AUTO_INCREMENT,
`author_id` bigint(20) NOT NULL,
`author_name` varchar(32) NOT NULL,
`dynasty` varchar(16) NOT NULL,
`poetry_num` int(8) NOT NULL DEFAULT '0'
PRIMARY KEY (`id`),
UNIQUE KEY `authorid_idx` (`author_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13339 DEFAULT CHARSET=utf8mb4

執(zhí)行計(jì)劃分析

IN 執(zhí)行過(guò)程

sql示例：select * from tabA where tabA.x in (select x from tabB where y>0 );
其執(zhí)行計(jì)劃：
（1）執(zhí)行tabB表的子查詢(xún)，得到結(jié)果集B椎咧，可以使用到tabB表的索引y玖详；
（2）執(zhí)行tabA表的查詢(xún)，查詢(xún)條件是tabA.x在結(jié)果集B里面勤讽，可以使用到tabA表的索引x蟋座。

Exists執(zhí)行過(guò)程

sql示例：select *from tabA where exists (select *from tabB where y>0);
其執(zhí)行計(jì)劃：
（1）先將tabA表所有記錄取到。
（2）逐行針對(duì)tabA表的記錄脚牍，去關(guān)聯(lián)tabB表向臀，判斷tabB表的子查詢(xún)是否有返回?cái)?shù)據(jù)，5.5之后的版本使用Block Nested Loop(Block 嵌套循環(huán))莫矗。
（3）如果子查詢(xún)有返回?cái)?shù)據(jù)飒硅，則將tabA當(dāng)前記錄返回到結(jié)果集。
tabA相當(dāng)于取全表數(shù)據(jù)遍歷作谚，tabB可以使用到索引始赎。

實(shí)驗(yàn)過(guò)程

實(shí)驗(yàn)針對(duì)相同結(jié)果集的IN和Exists 的SQL語(yǔ)句進(jìn)行分析于购。
包含IN的SQL語(yǔ)句：

select *from t_author ta where author_id in 
(select author_id from t_poetry tp where tp.poetry_id>3650 );

包含Exists的SQL語(yǔ)句：

select *from t_author ta where exists 
(select * from t_poetry tp where tp.poetry_id>3650 and tp.author_id=ta.author_id);

第一次實(shí)驗(yàn)

數(shù)據(jù)情況

t_author表德迹，13355條記錄野哭；t_poetry表，子查詢(xún)篩選結(jié)果集 where poetry_id>293650 眨唬，121條記錄会前；

執(zhí)行結(jié)果

使用exists耗時(shí)0.94S， 使用in耗時(shí)0.03S匾竿，IN 效率高于Exists瓦宜。

原因分析

對(duì)t_poetry表的子查詢(xún)結(jié)果集很小，且兩者在t_poetry表都能使用索引岭妖，對(duì)t_poetry子查詢(xún)的消耗基本一致临庇。兩者區(qū)別在于，使用 in 時(shí)昵慌，t_author表能使用索引:

MySQL查詢(xún)語(yǔ)句中的IN 和Exists 對(duì)比分析

使用exists時(shí)假夺，t_author表全表掃描:

MySQL查詢(xún)語(yǔ)句中的IN 和Exists 對(duì)比分析

在子查詢(xún)結(jié)果集較小時(shí)，查詢(xún)耗時(shí)主要表現(xiàn)在對(duì)t_author表的遍歷上斋攀。

第二次實(shí)驗(yàn)

數(shù)據(jù)情況

t_author表已卷，13355條記錄；t_poetry表淳蔼，子查詢(xún)篩選結(jié)果集 where poetry_id>3650 侧蘸，287838條記錄裁眯；

執(zhí)行時(shí)間

使用exists耗時(shí)0.12S， 使用in耗時(shí)0.48S讳癌，Exists 效率高于IN未状。

原因分析

兩者的索引使用情況跟第一次實(shí)驗(yàn)是一致的，唯一區(qū)別是子查詢(xún)篩選結(jié)果集的大小不同析桥，但實(shí)驗(yàn)結(jié)果已經(jīng)跟第一次的不同了。這種情況下子查詢(xún)結(jié)果集很大艰垂，我們看看mysql的查詢(xún)計(jì)劃：
使用in時(shí)泡仗，由于子查詢(xún)結(jié)果集很大，對(duì)t_author和t_poetry表都接近于全表掃描猜憎，此時(shí)對(duì)t_author表的遍歷耗時(shí)差異對(duì)整體效率影響可以忽略娩怎，執(zhí)行計(jì)劃里多了一行<auto_key>，在接近全表掃描的情況下胰柑，mysql優(yōu)化器選擇了auto_key來(lái)遍歷t_author表：

MySQL查詢(xún)語(yǔ)句中的IN 和Exists 對(duì)比分析

使用exists時(shí)截亦，數(shù)據(jù)量的變化沒(méi)有帶來(lái)執(zhí)行計(jì)劃的改變，但由于子查詢(xún)結(jié)果集很大柬讨，5.5以后的MySQL版本在exists匹配查詢(xún)結(jié)果時(shí)使用的是Block Nested-Loop（Block嵌套循環(huán)崩瓤，引入join buffer，類(lèi)似于緩存功能）開(kāi)始對(duì)查詢(xún)效率產(chǎn)生顯著影響踩官，尤其針對(duì)<font color=red>子查詢(xún)結(jié)果集很大</font>的情況下能顯著改善查詢(xún)匹配效率：

MySQL查詢(xún)語(yǔ)句中的IN 和Exists 對(duì)比分析

實(shí)驗(yàn)結(jié)論

根據(jù)上述兩個(gè)實(shí)驗(yàn)及實(shí)驗(yàn)結(jié)果却桶，我們可以較清晰的理解IN 和Exists的執(zhí)行過(guò)程，并歸納出IN 和Exists的適用場(chǎng)景：

• IN查詢(xún)?cè)趦?nèi)部表和外部表上都可以使用到索引蔗牡；
• Exists查詢(xún)僅在內(nèi)部表上可以使用到索引颖系；
• 當(dāng)子查詢(xún)結(jié)果集很大，而外部表較小的時(shí)候辩越，Exists的Block Nested Loop(Block 嵌套循環(huán))的作用開(kāi)始顯現(xiàn)嘁扼，并彌補(bǔ)外部表無(wú)法用到索引的缺陷，查詢(xún)效率會(huì)優(yōu)于IN黔攒。
• 當(dāng)子查詢(xún)結(jié)果集較小趁啸，而外部表很大的時(shí)候，Exists的Block嵌套循環(huán)優(yōu)化效果不明顯亏钩，IN 的外表索引優(yōu)勢(shì)占主要作用莲绰，此時(shí)IN的查詢(xún)效率會(huì)優(yōu)于Exists。
• 網(wǎng)上的說(shuō)法不準(zhǔn)確姑丑，即表的規(guī)模不是看內(nèi)部表和外部表蛤签，而是外部表和子查詢(xún)結(jié)果集。
• 最后一點(diǎn)栅哀，也是最重要的一點(diǎn)：世間沒(méi)有絕對(duì)的真理震肮，掌握事物的本質(zhì)称龙，針對(duì)不同的場(chǎng)景進(jìn)行實(shí)踐驗(yàn)證才是最可靠有效的方法。

實(shí)驗(yàn)過(guò)程中發(fā)現(xiàn)的問(wèn)題補(bǔ)充

僅對(duì)不同數(shù)據(jù)集情況下的上述exists語(yǔ)句分析時(shí)發(fā)現(xiàn)戳晌，數(shù)據(jù)集越大鲫尊，消耗的時(shí)間反而變小，覺(jué)得很奇怪沦偎。
具體查詢(xún)條件為：
where tp.poetry_id>3650疫向，耗時(shí)0.13S
where tp.poetry_id>293650，耗時(shí)0.46S
可能原因：條件值大豪嚎，查詢(xún)?cè)娇亢笊ν眨枰闅v的記錄越多，造成最終消耗越多的時(shí)間侈询。這個(gè)解釋有待進(jìn)一步驗(yàn)證后再補(bǔ)充舌涨。

原文在MySQL查詢(xún)語(yǔ)句中的IN 和Exists 對(duì)比分析