子查詢就是在一條查詢語(yǔ)句中還有其它的查詢語(yǔ)句，主查詢得到的結(jié)果依賴于子查詢的結(jié)果。

子查詢的子語(yǔ)句可以在一條sql語(yǔ)句的FROM，JOIN，和WHERE后面，本文主要針對(duì)在WHERE后面使用子查詢與表連接查詢的性能做出一點(diǎn)分析。

對(duì)于表連接查詢和子查詢性能的討論眾說(shuō)紛紜，普遍認(rèn)為的是表連接查詢的性能要高于子查詢。本文將從實(shí)驗(yàn)的角度，對(duì)這兩種查詢的性能做出驗(yàn)證，并就實(shí)驗(yàn)結(jié)果分析兩種查詢手段的執(zhí)行流程對(duì)性能的影響。

首先準(zhǔn)備兩張表

1，訪問(wèn)日志表mm_log有150829條記錄(相關(guān)sql文件已放在文章結(jié)尾的鏈接中)。

2，用戶表mm_member有373條記錄(相關(guān)sql文件已放在文章結(jié)尾的鏈接中)。

現(xiàn)在要求我們根據(jù)這兩張表查出2017-02-06那天有那些用戶登錄過(guò)系統(tǒng)。

我們先來(lái)看一下使用表連接查詢

SELECTSQL_NO_CACHE mm.*

FROMmm_member mmJOINmm_log mlONmm.id=ml.member_idWHEREml.access_timeLIKE '%2017-02-06%'

GROUP BYml.member_id;

這里使用了 SQL_NO_CACHE 是因?yàn)橐啻螆?zhí)行這條sql語(yǔ)句，并計(jì)算出這條sql查詢所耗費(fèi)的平均時(shí)間，所以要關(guān)掉mysql的查詢緩存，防止多次執(zhí)行從緩存中讀取數(shù)據(jù)。

mm.*是取GROUP BYml.member_id分組后的諸多臨時(shí)表的第一行數(shù)據(jù)，相關(guān)用法及原理請(qǐng)參見(jiàn)我的另一篇博客(http://www.cnblogs.com/cdf-opensource-007/p/6502556.html)

對(duì)這條sql語(yǔ)句執(zhí)行了10次，查詢所耗費(fèi)的平均時(shí)間在0.120s左右。

查詢結(jié)果：(一共有5個(gè)用戶訪問(wèn)過(guò)系統(tǒng))

至于以上這條sql的執(zhí)行流程已經(jīng)在前幾篇博客中描述的很詳細(xì)了，這里就不再做敘述了。

下面使用WHERE后使用子查詢的方式實(shí)現(xiàn)

SELECTSQL_NO_CACHE mm.usernameFROMmm_member mmWHEREmm.idIN(SELECT ml.member_id FROM mm_log ml WHERE ml.access_time LIKE '%2017-02-06%' GROUP BY ml.member_id);

當(dāng)我第一次運(yùn)行這條sql語(yǔ)句的時(shí)候，等了十幾秒一直沒(méi)有結(jié)果，我以為我的電腦死機(jī)，可Navicat顯示處理中，最后40多秒的時(shí)候才運(yùn)行出結(jié)果，接連運(yùn)行了好多次在都是41秒左右出結(jié)果。

我們看到執(zhí)行結(jié)果同上。那么使用子查詢的性能到底低在哪里呢？

我的第一種推測(cè)是子語(yǔ)句：SELECT ml.member_id FROM mm_log ml WHERE ml.access_time LIKE '%2017-02-06%' GROUP BY ml.member_id耗費(fèi)了大量的查詢時(shí)間，因?yàn)閙m_log這張表中有150829條記錄。

把子語(yǔ)句單拿出來(lái)運(yùn)行一下，發(fā)現(xiàn)子語(yǔ)句的運(yùn)行時(shí)間也就在0.111s左右。

SELECT SQL_NO_CACHE member_id FROM mm_log ml WHERE ml.access_time LIKE '%2017-02-06%' GROUP BY ml.member_id;

這就說(shuō)明我的第一種推測(cè)是不合理的。

那就分析下在WHERE后使用子查詢IN的執(zhí)行原理：

1，IN后面跟的子查詢語(yǔ)句的執(zhí)行結(jié)果只能有一列是用來(lái)和IN前面的主表的字段匹配的，在這里指的是mm.id。

2，一條帶有子查詢的sql語(yǔ)句首先執(zhí)行的是子語(yǔ)句，主表的數(shù)據(jù)行按照IN前面主表的字段依次跟子查詢的結(jié)果進(jìn)行匹配，子查詢中結(jié)果中有該數(shù)據(jù)行對(duì)應(yīng)字段的值，則返回true,該行被WHERE篩選出來(lái)。沒(méi)有則返回false，該行不被篩選。

3，那么按照2的說(shuō)法，子查詢的效率應(yīng)該也不低啊，子語(yǔ)句的耗時(shí)在0.111s左右，而且主表mm_member和子語(yǔ)句的查詢結(jié)果相匹配的次數(shù)，肯定是要少于表連接查詢時(shí)數(shù)據(jù)行間匹配的次數(shù)的，但實(shí)驗(yàn)結(jié)果顯示使用子查詢的性能確實(shí)很低。

所以我有了第二種推測(cè)，主表mm_member數(shù)據(jù)行的每一行在與IN后面子語(yǔ)句的結(jié)果相匹配時(shí)，子語(yǔ)句都會(huì)重新執(zhí)行一次，也就是說(shuō)子語(yǔ)句第一次執(zhí)行時(shí)，不會(huì)在內(nèi)存中有緩存。這類似與使用了兩個(gè)FOR循環(huán)嵌套，外層的FOR循環(huán)每拿出一個(gè)值，內(nèi)層的FOR循環(huán)都要遍歷一次。

那么根據(jù)以上的推測(cè)，拿主表mm_member的數(shù)據(jù)行數(shù)乘以子語(yǔ)句的執(zhí)行時(shí)間就應(yīng)該是整個(gè)查詢的時(shí)間。

mm_member的數(shù)據(jù)行數(shù)：373

多次執(zhí)行子語(yǔ)句算出平均時(shí)間在：0.111s

整個(gè)查詢耗時(shí)的理論時(shí)間：41.403s

多次執(zhí)行整個(gè)查詢得出實(shí)際查詢時(shí)間的平均值：40.834s

計(jì)算誤差：(理論值-實(shí)際值)÷理論值 = 1.37%

誤差還是在可以接受的范圍內(nèi)的，可以證明以上的推測(cè)。

根據(jù)以上的實(shí)驗(yàn)，我們可以得出的結(jié)論是，表連接查詢的性能是要高于子查詢的。

另外，對(duì)于在子查詢中使用IN的性能高還是是用EXITS的性能高，有一種普遍的說(shuō)法是：

1，在外表大，內(nèi)表小，外表中有索引的情況下，使用IN。

2，在外表小，內(nèi)表大，內(nèi)表中有索引的情況下，使用EXITS。

先介紹一下EXITS的用法，剛好本例符合外表小內(nèi)表大的情況，就以本例介紹一下。看下SQL:

SELECTSQL_NO_CACHE mm.*

FROMmm_member mmWHERE

EXISTS(SELECT * FROM mm_log ml WHERE mm.id = ml.member_id AND ml.access_time LIKE '%2017-02-06%');

EXITS又簡(jiǎn)稱代入查詢，就是把主表的每一行代入子表的每一行進(jìn)行檢驗(yàn)，一旦子表中有符合的數(shù)據(jù)行就返回true，就可以取得主表中代入檢驗(yàn)的那一行數(shù)據(jù)，否則返回false，不可以取得主表中代入檢驗(yàn)的那一行數(shù)據(jù)。同IN不同的是，EXITS后的子語(yǔ)句不查詢出結(jié)果，所以說(shuō)SELECT后面的字段沒(méi)有意義，一般使用*代替，由于EXITS的這種機(jī)制，當(dāng)子表數(shù)據(jù)量比較大且有冗余字段的時(shí)候就很有可能避免了對(duì)子表的全表掃描，不像IN那樣每次主表數(shù)據(jù)行來(lái)匹配都要進(jìn)行全表掃描，并返回結(jié)果。所以說(shuō)EXITS類似于兩個(gè)FOR循環(huán)嵌套時(shí)，內(nèi)層的FOR循環(huán)里面有 if(xxx){ break; }這種語(yǔ)法。

以上sql執(zhí)行時(shí)間的平均在34秒左右，比使用IN要快上一些，但是跟表連接查詢還不能比。

但是，在表與表之間沒(méi)有關(guān)聯(lián)關(guān)系時(shí)，就只能使用IN了。

最后說(shuō)一點(diǎn)，我們作為程序員，研究問(wèn)題還是要仔細(xì)深入一點(diǎn)的。當(dāng)你對(duì)原理了解的有夠透徹，開(kāi)發(fā)起來(lái)也就得心應(yīng)手了，很多開(kāi)發(fā)中的問(wèn)題和疑惑也就迎刃而解了，而且在面對(duì)其他問(wèn)題的時(shí)候也可做到觸類旁通。當(dāng)然在開(kāi)發(fā)中沒(méi)有太多的時(shí)間讓你去研究原理，開(kāi)發(fā)中要以實(shí)現(xiàn)功能為前提，可等項(xiàng)目上線的后，你有大把的時(shí)間或者空余的時(shí)間，你大可去刨根問(wèn)底，深入的去研究一項(xiàng)技術(shù)，為覺(jué)得這對(duì)一名程序員的成長(zhǎng)是很重要的事情。

總結(jié)

以上是生活随笔為你收集整理的mysql extis_SQL -- 联接查询，子查询，Extis 效率浅析的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。