快捷導(dǎo)航

MySQL JOIN關(guān)聯(lián)查詢的原理及優(yōu)化

更新時(shí)間：2022年08月22日 10:46:52 作者：劉Java

這篇文章主要介紹了MySQL JOIN關(guān)聯(lián)查詢的原理及優(yōu)化，文章圍繞主題展開詳細(xì)的內(nèi)介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下

1 關(guān)聯(lián)查詢的執(zhí)行

關(guān)聯(lián)查詢的執(zhí)行過程是：先遍歷關(guān)聯(lián)表t1（驅(qū)動(dòng)表，全表掃描），然后根據(jù)從表t1中取出的每行數(shù)據(jù)中的a值，去表t2（被關(guān)聯(lián)表，被驅(qū)動(dòng)表）中查找滿足條件的記錄，可以走t2的索引搜索。在形式上，這個(gè)過程就跟我們寫程序時(shí)的嵌套查詢類似，并且可以用上被驅(qū)動(dòng)表的索引，所以我們稱之為“Index Nested-Loop Join”，簡(jiǎn)稱NLJ。在join語句的執(zhí)行流程中，驅(qū)動(dòng)表是走全表掃描，而被驅(qū)動(dòng)表是走索引樹搜索。

假設(shè)被驅(qū)動(dòng)表的行數(shù)是M。每次在被驅(qū)動(dòng)表查一行數(shù)據(jù)，要先搜索索引a，再搜索主鍵索引。每次搜索一棵樹近似復(fù)雜度是以2為底的M的對(duì)數(shù)，記為log2M，所以在被驅(qū)動(dòng)表上查一行的時(shí)間復(fù)雜度是 2*log2M。

假設(shè)驅(qū)動(dòng)表的行數(shù)是N，執(zhí)行過程就要掃描驅(qū)動(dòng)表N行，然后對(duì)于每一行，到被驅(qū)動(dòng)表上匹配一次。

因此整個(gè)執(zhí)行過程，近似復(fù)雜度是 N + N2log2M。顯然，N對(duì)掃描行數(shù)的影響更大，因此應(yīng)該讓小表來做驅(qū)動(dòng)表：N擴(kuò)大1000倍的話，掃描行數(shù)就會(huì)擴(kuò)大1000倍；而M擴(kuò)大1000倍，掃描行數(shù)擴(kuò)大不到10倍。

結(jié)論：如果使用join語句的話，需要讓小表做驅(qū)動(dòng)表，并且被驅(qū)動(dòng)表的關(guān)聯(lián)字段應(yīng)該建立索引。一般來說，除非有其他理由，否則只需要在關(guān)聯(lián)順序中的第二個(gè)表的相應(yīng)列上創(chuàng)建索引，即在被驅(qū)動(dòng)的表的關(guān)聯(lián)字段簡(jiǎn)歷索引。

2 沒有索引的算法

如果，被驅(qū)動(dòng)表的關(guān)聯(lián)字段沒有使用索引，那么MySQL將使用另一種Block Nested-Loop Join算法。

把表t1的數(shù)據(jù)讀入線程內(nèi)存join_buffer中，這只會(huì)將查詢需要返回的列放入，如果我們的語句中寫的是select *，就會(huì)把整個(gè)表t1放入了內(nèi)存；
掃描表t2，把表t2中的每一行取出來，跟join_buffer中的數(shù)據(jù)做對(duì)比，滿足join條件的，作為結(jié)果集的一部分返回。

這個(gè)過程的流程圖如下：

對(duì)應(yīng)地，這條SQL語句的explain結(jié)果的Extra字段中將會(huì)展示：Block Nested Loop。在這個(gè)過程中，對(duì)表t1和t2都做了一次全表掃描，因此總的掃描行數(shù)是量表的數(shù)據(jù)總和M+N。由于join_buffer是以無序數(shù)組的方式組織的，因此對(duì)表t2中的每一行，都要做100次判斷，總共需要在內(nèi)存中做的判斷次數(shù)是：M* N次。

假設(shè)小表的行數(shù)是N，大表的行數(shù)是M，那么在這個(gè)算法里：

兩個(gè)表都做一次全表掃描，所以總的掃描行數(shù)是M+N；
內(nèi)存中的判斷次數(shù)是M*N，雖然不需要讀盤，但是需要占用大量CPU進(jìn)行計(jì)算。

可以看到，調(diào)換這兩個(gè)算式中的M和N沒差別，因此這時(shí)候選擇大表還是小表做驅(qū)動(dòng)表，執(zhí)行耗時(shí)是一樣的。

join_buffer的大小是由參數(shù)join_buffer_size設(shè)定的，默認(rèn)值是256k。如果放不下表t1的所有數(shù)據(jù)話，策略很簡(jiǎn)單，就是將t1的數(shù)據(jù)分段放入、比較，假設(shè)表t1被分成了兩次放入join_buffer中，那么會(huì)導(dǎo)致表t2會(huì)被掃描兩次。雖然分成兩次放入join_buffer，但是內(nèi)存中判斷等值條件的次數(shù)還是不變的，依然是M*N次。

假設(shè)，驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是N，需要分K段才能完成算法流程，K大于等于1，被驅(qū)動(dòng)表的數(shù)據(jù)行數(shù)是M。注意，這里的K不是常數(shù)，N越大K就會(huì)越大。

所以，在這個(gè)算法的執(zhí)行過程中：

掃描行數(shù)是 N+K*M；
內(nèi)存判斷 N*M次。

可以看到，如果join_buffer_size沒有足夠大（這是常見的情況），那么N越小，這樣K就更小，掃描的行數(shù)才會(huì)更少，因此仍然應(yīng)該讓小表當(dāng)驅(qū)動(dòng)表。而且K也是影響掃描行數(shù)的關(guān)鍵因素，這個(gè)值越小越好，如果N不變，那么影響K的就是join_buffer_size的大小。join_buffer_size越大，一次可以放入的行越多，分成的段數(shù)K也就越少，對(duì)被驅(qū)動(dòng)表的全表掃描次數(shù)就越少。

因此，如果你的join語句很慢，除了讓小表當(dāng)驅(qū)動(dòng)表，還有就把join_buffer_size改大。

如果確定“小表”呢？除了總行數(shù)之外，還應(yīng)該是兩個(gè)表按照各自的條件過濾，過濾完成之后，再計(jì)算參與join的各個(gè)字段的總數(shù)據(jù)量（因?yàn)檫€要放入內(nèi)存中），數(shù)據(jù)量小的那個(gè)表，就是“小表”，應(yīng)該作為驅(qū)動(dòng)表。

實(shí)際在查詢優(yōu)化時(shí)，如果join不是使用的Index Nested-Loop Join算法，則應(yīng)該盡量改為使用該算法。

到此這篇關(guān)于MySQL JOIN關(guān)聯(lián)查詢的原理及優(yōu)化的文章就介紹到這了,更多相關(guān)MySQL JOIN關(guān)聯(lián)查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: