.jpg)
本篇文章是mysql的進(jìn)階學(xué)習(xí)，給大家詳細(xì)介紹一下join連接的原理，以及join的3種算法，希望對大家有所幫助！
我們經(jīng)常在多表查詢的時候使用join 去連接多個表，其實(shí)join的效率比不好還是應(yīng)該盡量避免使用的，其本質(zhì)就是各個表之間循環(huán)匹配的，mysql中只支持一種join算法Nested-Loop Join(循環(huán)嵌套連接)，但是其有多種變種的算法，其實(shí)就是提高join的執(zhí)行效率。【相關(guān)推薦：mysql視頻教程】
1. Simple Nested-Loop Join(簡單嵌套循環(huán)連接)
Simple Nested-Loop join(NLJ)算法從循環(huán)中的第一個表中一次讀取一行，將每一行傳遞給一個嵌套循環(huán)，該嵌套循環(huán)中匹配數(shù)據(jù)是否一致。例如驅(qū)動表User,被驅(qū)動表UserInfo 的sql是 select * from User u left join User_info info on u.id = info.user_id，其實(shí)就是我們常用的for循環(huán)，偽代碼的邏輯應(yīng)該是
for(User?u:Users){ ????for(UserInfo?info:UserInfos){ ????????if(u.id?==?info.userId){ ????????????//?得到匹配數(shù)據(jù) ????????} ????} }
簡單粗暴的算法，每次從User表中取出一條數(shù)據(jù)，然后掃描User_info中的所有記錄匹配，最后合并數(shù)據(jù)返回。
假如驅(qū)動表User有10條數(shù)據(jù)，被驅(qū)動表UserInfo也有10條數(shù)據(jù)，那么實(shí)際上驅(qū)動表User會被掃描10次，而被驅(qū)動表會被掃描10*10=100次(每掃描一次驅(qū)動表，就會掃描全部的被驅(qū)動表)，這種效率是很低的，對數(shù)據(jù)庫的開銷比較大，尤其是被驅(qū)動表。每一次掃描其實(shí)就是從硬盤中讀取數(shù)據(jù)加載到內(nèi)存中,也就是一次IO，目前IO是最大的瓶頸
2. Index Nested-Loop Join(索引嵌套循環(huán)連接)
索引嵌套循環(huán)是使用索引減少掃描的次數(shù)來提高效率的，所以要求非驅(qū)動表上必須有索引才行。
在查詢的時候，驅(qū)動表(User) 會根據(jù)關(guān)聯(lián)字段的索引進(jìn)行查詢，當(dāng)索引上找到符合的值，才會進(jìn)行回表查詢。如果非驅(qū)動表(User_info)的關(guān)聯(lián)字段(user_id)是主鍵的話，查詢效率會非常高(主鍵索引結(jié)構(gòu)的葉子結(jié)點(diǎn)包含了完整的行數(shù)據(jù)(InnoDB))，如果不是主鍵，每次匹配到索引后都需要進(jìn)行一次回表查詢(根據(jù)二級索引(非主鍵索引)的主鍵ID進(jìn)行回表查詢)，性能肯定弱于主鍵的查詢。
上圖中的索引查詢之后不一定會回表，什么情況下會回表，這個要看索引查詢到的字段能不能滿足查詢需要的字段，具體可以參考之前的文章：你需要知道的一些索引基礎(chǔ)知識 和 B+樹的索引知識
3. Block Nested-Loop Join(緩存塊嵌套循環(huán)連接)
如果存在索引，那么會使用index的方式進(jìn)行join，如果join的列沒有索引，被驅(qū)動表要掃描的次數(shù)太多了，每次訪問被驅(qū)動表，其表中的記錄都會被加載到內(nèi)存中，然后再從驅(qū)動表中取一條與其匹配，匹配結(jié)束后清除內(nèi)存，然后再從驅(qū)動表中加載一條記錄 然后把被驅(qū)動表的記錄在加載到內(nèi)存匹配，這樣周而復(fù)始，大大增加了IO的次數(shù)。為了減少被驅(qū)動表的IO次數(shù)，就出現(xiàn)了Block Nested-Loop Join的方式。
不再是逐條獲取驅(qū)動表的數(shù)據(jù)，而是一塊一塊的獲取，引入了join buffer緩沖區(qū)，將驅(qū)動表join相關(guān)的部分?jǐn)?shù)據(jù)列(大小是join buffer的限制)緩存到j(luò)oin buffer中，然后全表掃描被驅(qū)動表，被驅(qū)動表的每一條記錄一次性和join buffer中的所有驅(qū)動表記錄進(jìn)行匹配（內(nèi)存中操作），將簡單嵌套循環(huán)中的多次比較合并成一次，降低了非驅(qū)動表的訪問頻率。
驅(qū)動表能不能一次加載完，要看join buffer能不能存儲所有的數(shù)據(jù)，默認(rèn)情況下join_buffer_size=256k,查詢的時候Join Buffer 會緩存所有參與查詢的列而不是只有join的列，在一個有N個join關(guān)聯(lián)的sql中會分配N-1個join buffer。所以查詢的時候盡量減少不必要的字段，可以讓join buffer中可以存放更多的列。
可以調(diào)整join_buffer_size的緩存大小show variables like ‘%join_buffer%’這個值可以根據(jù)實(shí)際情況更改。
使用Block Nested-Loop Join算法需要開啟優(yōu)化器管理配置的optimizer_switch的設(shè)置block_nested_loop為on，默認(rèn)是開啟的。可以通過 show variables like ‘%optimizer_switch%’ 查看block_nested_loop狀態(tài)。
以上三種算法了解即可，其實(shí)實(shí)際工作中只要我們能都用好索引就不錯了，即使是join的連接也要注意關(guān)聯(lián)字段是否建立索引，還是要善于使用索引來提供查詢效率。
原文地址：https://juejin.cn/post/7014105037517357093作者：紀(jì)先生
更多編程相關(guān)知識，請訪問：mysql視頻教程！！
.png)
