MySQL查詢語句中的IN和Exists怎樣進行對比分析

這篇文章給大家介紹MySQL查詢語句中的IN和Exists怎樣進行對比分析，內(nèi)容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

背景介紹

最近在寫SQL語句時，對選擇IN 還是Exists 猶豫不決，于是把兩種方法的SQL都寫出來對比一下執(zhí)行效率，發(fā)現(xiàn)IN的查詢效率比Exists高了很多，于是想當然的認為IN的效率比Exists好，但本著尋根究底的原則，我想知道這個結(jié)論是否適用所有場景，以及為什么會出現(xiàn)這個結(jié)果。
網(wǎng)上查了一下相關(guān)資料，大體可以歸納為：外部表小，內(nèi)部表大時，適用Exists；外部表大，內(nèi)部表小時，適用IN。那我就困惑了，因為我的SQL語句里面，外表只有1W級別的數(shù)據(jù)，內(nèi)表有30W級別的數(shù)據(jù)，按網(wǎng)上的說法應(yīng)該是Exists的效率會比IN高的，但我的結(jié)果剛好相反??！
“沒有調(diào)查就沒有發(fā)言權(quán)”！于是我開始研究IN 和Exists的實際執(zhí)行過程，從實踐的角度出發(fā)，在根本上去尋找原因，于是有了這篇博文分享。

實驗數(shù)據(jù)

我的實驗數(shù)據(jù)包括兩張表：t_author表 和 t_poetry表。
對應(yīng)表的數(shù)據(jù)量：
t_author表，13355條記錄；
t_poetry表，289917條記錄。
對應(yīng)的表結(jié)構(gòu)如下：
CREATE TABLE t_poetry (
id bigint(20) NOT NULL AUTO_INCREMENT,
poetry_id bigint(20) NOT NULL COMMENT '詩詞id',
poetry_name varchar(200) NOT NULL COMMENT '詩詞名稱',
<font color=red> author_id bigint(20) NOT NULL COMMENT '作者id'</font>
PRIMARY KEY (id),
<font color=red>
UNIQUE KEY pid_idx (poetry_id) USING BTREE,
KEY aid_idx (author_id) USING BTREE</font>
) ENGINE=InnoDB AUTO_INCREMENT=291270 DEFAULT CHARSET=utf8mb4

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

執(zhí)行計劃分析

IN 執(zhí)行過程

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

Exists執(zhí)行過程

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

實驗過程

實驗針對相同結(jié)果集的IN和Exists 的SQL語句進行分析。
包含IN的SQL語句：
select   from t_author ta where author_id in
(select author_id from t_poetry tp where tp.poetry_id>3650 );
包含Exists的SQL語句：
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ù)據(jù)情況

t_author表，13355條記錄；t_poetry表，子查詢篩選結(jié)果集 where poetry_id>293650 ，121條記錄；

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

使用exists耗時0.94S， 使用in耗時0.03S，<font color=red>IN 效率高于Exists</font>。

原因分析

對t_poetry表的子查詢結(jié)果集很小，且兩者在t_poetry表都能使用索引，對t_poetry子查詢的消耗基本一致。兩者區(qū)別在于，使用 in 時，t_author表能使用索引:

使用exists時，t_author表全表掃描:

在子查詢結(jié)果集較小時，查詢耗時主要表現(xiàn)在對t_author表的遍歷上。

第二次實驗

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

t_author表，13355條記錄；t_poetry表，子查詢篩選結(jié)果集 where poetry_id>3650 ，287838條記錄；

執(zhí)行時間

使用exists耗時0.12S， 使用in耗時0.48S，<font color=red>Exists IN</font>。

原因分析

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

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

根據(jù)上述兩個實驗及實驗結(jié)果，我們可以較清晰的理解IN 和Exists的執(zhí)行過程，并歸納出IN 和Exists的適用場景：

• IN查詢在內(nèi)部表和外部表上都可以使用到索引；

• Exists查詢僅在內(nèi)部表上可以使用到索引；

• 當子查詢結(jié)果集很大，而外部表較小的時候，Exists的Block Nested Loop(Block 嵌套循環(huán))的作用開始顯現(xiàn)，并彌補外部表無法用到索引的缺陷，查詢效率會優(yōu)于IN。

• 當子查詢結(jié)果集較小，而外部表很大的時候，Exists的Block嵌套循環(huán)優(yōu)化效果不明顯，IN 的外表索引優(yōu)勢占主要作用，此時IN的查詢效率會優(yōu)于Exists。

• 網(wǎng)上的說法不準確。其實“表的規(guī)模”不是看內(nèi)部表和外部表，而是外部表和子查詢結(jié)果集。

• 最后一點，也是最重要的一點：世間沒有絕對的真理，掌握事物的本質(zhì)，針對不同的場景進行實踐驗證才是最可靠有效的方法。

實驗過程中發(fā)現(xiàn)的問題補充

僅對不同數(shù)據(jù)集情況下的上述exists語句分析時發(fā)現(xiàn)，數(shù)據(jù)集越大，消耗的時間反而變小，覺得很奇怪。
具體查詢條件為：
where tp.poetry_id>3650，耗時0.13S
where tp.poetry_id>293650，耗時0.46S
可能原因：條件值大，查詢越靠后，需要遍歷的記錄越多，造成最終消耗越多的時間。這個解釋有待進一步驗證后再補充。

關(guān)于MySQL查詢語句中的IN和Exists怎樣進行對比分析就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。