下面講講關(guān)于查詢(xún)MySQL語(yǔ)句時(shí)選擇IN還是Exists，文字的奧妙在于貼近主題相關(guān)。所以，閑話就不談了，我們直接看下文吧，相信看完查詢(xún)MySQL語(yǔ)句時(shí)選擇IN還是Exists這篇文章你一定會(huì)有所受益。 

創(chuàng)新互聯(lián)是一家專(zhuān)注于成都網(wǎng)站制作、成都網(wǎng)站建設(shè)與策劃設(shè)計(jì),龍陵網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專(zhuān)注于網(wǎng)站建設(shè)十余年,網(wǎng)設(shè)計(jì)領(lǐng)域的專(zhuān)業(yè)建站公司;建站業(yè)務(wù)涵蓋:龍陵等地區(qū)。龍陵做網(wǎng)站價(jià)格咨詢(xún):18980820575

實(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表能使用索引:

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

在子查詢(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ì)劃里多了一行，在接近全表掃描的情況下，mysql優(yōu)化器選擇了auto_key來(lái)遍歷t_author表：

使用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ì)子查詢(xún)結(jié)果集很大的情況下能顯著改善查詢(xún)匹配效率：

實(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)確。其實(shí)“表的規(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è)娇亢?，需要遍歷的記錄越多，造成最終消耗越多的時(shí)間。這個(gè)解釋有待進(jìn)一步驗(yàn)證后再補(bǔ)充。

對(duì)于以上查詢(xún)MySQL語(yǔ)句時(shí)選擇IN還是Exists相關(guān)內(nèi)容，大家還有什么不明白的地方嗎？或者想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。