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

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

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

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

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

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

2 沒有索引的算法

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

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

這個過程的流程圖如下：

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

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

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

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

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

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

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

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

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

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

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

實際在查詢優(yōu)化時，如果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)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論