正文

MySQL的Join到底能不能用
經(jīng)常聽(tīng)到2種觀(guān)點(diǎn)：

公司主營(yíng)業(yè)務(wù)：成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)、移動(dòng)網(wǎng)站開(kāi)發(fā)等業(yè)務(wù)。幫助企業(yè)客戶(hù)真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開(kāi)放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶(hù)帶來(lái)驚喜。創(chuàng)新互聯(lián)推出安仁免費(fèi)做網(wǎng)站回饋大家。

• join性能低，盡量少用
• 多表join時(shí)，變?yōu)槎鄠€(gè)SQL進(jìn)行多次查詢(xún)

其實(shí)對(duì)于上面的觀(guān)點(diǎn)一定程度上是正確的，但不是完全正確。但之所以流傳這么廣，主要還是沒(méi)有搞清楚實(shí)際狀態(tài)，而根據(jù)實(shí)際使用中總結(jié)出來(lái)的一些模糊規(guī)律。只有了解的MySQL的Join實(shí)際執(zhí)行方式，就會(huì)知道上面2種觀(guān)點(diǎn)是一種模糊的規(guī)律，這種規(guī)律并不能指導(dǎo)我們實(shí)際開(kāi)發(fā)。下面就說(shuō)說(shuō)MySQL的實(shí)際join執(zhí)行方式。

MySQL的Join是如何執(zhí)行的

join可以說(shuō)一種集合的運(yùn)算，比如left join,right join,inner join,full join,outer join，cross join等，這些集合間的計(jì)算關(guān)系對(duì)應(yīng)在高中數(shù)學(xué)集合里面的交集，并集，補(bǔ)集，全集等。但在實(shí)際的代碼中，join運(yùn)算基本上是通過(guò)多層循環(huán)來(lái)實(shí)現(xiàn)的。

舉一個(gè)例子，假設(shè)有t1,t2兩張表，表結(jié)構(gòu)分別如下：

createtablet1(
idintnotnullAUTO_INCREMENT,
usernamevarchar(20)notnulldefault'',
ageintnotnulldefault0,
PRIMARYkey(`id`)
)ENGINE=INNODBDEFAULTCHARSET=UTF8MB4;

createtablet2(
idintnotnullauto_increment,
usernamevarchar(20)notnulldefault'',
scoreintnotnulldefalut0,
primarykey(`id`)
))ENGINE=INNODBDEFAULTCHARSET=UTF8MB4;

假設(shè)t1有100條數(shù)據(jù)，t2表有200條數(shù)

查詢(xún)sql為：

selectt1.*,t2.*fromt1leftjoint2on(t1.username=t2.username)

那么這條SQL的執(zhí)行步驟如下：

1. 從表t1中取一行數(shù)據(jù)r1
2. 從r1中，取出字段username到表t2中查詢(xún)
3. 取出表t2中滿(mǎn)足條件的行，跟r1組成一行，作為結(jié)果集的一部份
4. 重復(fù)執(zhí)行步驟1,2,3,直到表t1的所以數(shù)據(jù)循環(huán)完畢

基本上先遍歷t，1,然后根據(jù)t1中的每行數(shù)據(jù)中的username，去表t2中查找滿(mǎn)足條件的記錄?；揪褪?層循環(huán)。

如何優(yōu)化join查詢(xún)

從上面可以看出，join本質(zhì)是循環(huán)，這里的開(kāi)銷(xiāo)如下：

1. 遍歷t1數(shù)據(jù)，讀取數(shù)據(jù)為t1表的行數(shù)，假設(shè)行數(shù)為n,則復(fù)雜度也為n
2. 根據(jù)t1的匹配字段username去t2中一行一行的查詢(xún)數(shù)據(jù)
3. 這個(gè)過(guò)程，因?yàn)镸ySQL的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)為二叉樹(shù)，時(shí)間復(fù)雜度為log2(m) m為t2表的總行數(shù)
4. 那么總復(fù)雜度近似為 n+n(2log2(m))

從上面的步驟可以看出，優(yōu)化方向：

1. 降低t1查詢(xún)時(shí)的開(kāi)銷(xiāo)，主要是磁盤(pán)io開(kāi)銷(xiāo)，避免全表掃描，用索引
2. 降低t2查詢(xún)時(shí)的開(kāi)銷(xiāo)，也用索引
3. 將數(shù)據(jù)量多的表做被驅(qū)動(dòng)表，小表作驅(qū)動(dòng)表，m取了對(duì)數(shù)，大表數(shù)據(jù)量大對(duì)復(fù)雜度的影響沒(méi)有線(xiàn)性增長(zhǎng)
4. 緩存t1表，不用每次去磁盤(pán)load,比如一次緩存100條，那么能顯著降低磁盤(pán)讀數(shù)據(jù)次數(shù)，t2每次與緩存中的t1數(shù)據(jù)進(jìn)行比較
5. 隨機(jī)磁盤(pán)讀比較耗費(fèi)磁盤(pán)性能，轉(zhuǎn)為順序讀，因?yàn)槎鏄?shù)的存儲(chǔ)結(jié)構(gòu)，每次非主鍵查找，有一個(gè)回表的動(dòng)作，即根據(jù)主鍵再次查詢(xún)需要的數(shù)據(jù)

優(yōu)化的基本方法：

1. 減少循環(huán)次數(shù)，減少磁盤(pán)IO次數(shù)，變隨機(jī)IO為順序IO
2. 其實(shí)MySQL針對(duì)上面的優(yōu)化方法有對(duì)應(yīng)的算法：
3. Simple Nested Loop Join 最普通的循環(huán)，這個(gè)要避免
4. Block Nested Loop Join 主要是針對(duì)t2表上沒(méi)有索引，在步驟2將t2中的每一行數(shù)據(jù)跟join buffer數(shù)據(jù)做對(duì)比，這樣將磁盤(pán)操作轉(zhuǎn)為內(nèi)存操作進(jìn)行比較，但是如果被驅(qū)動(dòng)表的數(shù)據(jù)比較大的話(huà)，也影響性能，主要是cache pool被占滿(mǎn)，導(dǎo)致MySQL性能下降
5. Index Nested Join 就是都通過(guò)主鍵進(jìn)行查找關(guān)聯(lián)，這種性能比較好

Batched Key Access Join 這個(gè)是 Index Nested Join上做的優(yōu)化，因?yàn)榛乇淼拇嬖?，隨機(jī)操作io也很耗費(fèi)性能，這個(gè)算法的核心在于通過(guò)輔助索引去查找時(shí)，將得到的主鍵進(jìn)行排序，然后按照主鍵遞增的順序進(jìn)行查找，對(duì)磁盤(pán)的讀接近順序讀，從而優(yōu)化性能

到底要不用Join

從上面的分析我們可以看到，用Join還是可行的，只要性能可控且在接受范圍內(nèi)，還是能減少代碼復(fù)雜度的。需要避免的是join的表沒(méi)有索引，不然這樣的SQL發(fā)線(xiàn)上是災(zāi)難性的。

總結(jié)

Join還是可以大膽的使用，只要把握好幾個(gè)原則：

1. 盡量讓join的列是索引列，而且最好是類(lèi)型相同,盡可能是主鍵索引

2. 盡量將小表做驅(qū)動(dòng)表（這一點(diǎn)MySQL在5.6某個(gè)版本后能自動(dòng)完成）

3. 養(yǎng)成將寫(xiě)好的SQL進(jìn)行explain的好習(xí)慣，觀(guān)察SQL的執(zhí)行過(guò)程

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線(xiàn)，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性?xún)r(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專(zhuān)為企業(yè)上云打造定制，能夠滿(mǎn)足用戶(hù)豐富、多元化的應(yīng)用場(chǎng)景需求。

名稱(chēng)欄目：一文搞懂MySQL的Join，聊一聊秒殺架構(gòu)設(shè)計(jì)-創(chuàng)新互聯(lián)
分享鏈接：http://weahome.cn/article/diieoh.html