正文
MySQL的Join到底能不能用
經(jīng)常聽(tīng)到2種觀點(diǎn):
- join性能低,盡量少用
- 多表join時(shí),變?yōu)槎鄠€(gè)SQL進(jìn)行多次查詢
其實(shí)對(duì)于上面的觀點(diǎn)一定程度上是正確的,但不是完全正確。但之所以流傳這么廣,主要還是沒(méi)有搞清楚實(shí)際狀態(tài),而根據(jù)實(shí)際使用中總結(jié)出來(lái)的一些模糊規(guī)律。只有了解的MySQL的Join實(shí)際執(zhí)行方式,就會(huì)知道上面2種觀點(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ù)
查詢sql為:
selectt1.*,t2.*fromt1leftjoint2on(t1.username=t2.username)
那么這條SQL的執(zhí)行步驟如下:
- 從表t1中取一行數(shù)據(jù)r1
- 從r1中,取出字段username到表t2中查詢
- 取出表t2中滿足條件的行,跟r1組成一行,作為結(jié)果集的一部份
- 重復(fù)執(zhí)行步驟1,2,3,直到表t1的所以數(shù)據(jù)循環(huán)完畢
基本上先遍歷t,1,然后根據(jù)t1中的每行數(shù)據(jù)中的username,去表t2中查找滿足條件的記錄。基本就是2層循環(huán)。
如何優(yōu)化join查詢
從上面可以看出,join本質(zhì)是循環(huán),這里的開(kāi)銷(xiāo)如下:
- 遍歷t1數(shù)據(jù),讀取數(shù)據(jù)為t1表的行數(shù),假設(shè)行數(shù)為n,則復(fù)雜度也為n
- 根據(jù)t1的匹配字段username去t2中一行一行的查詢數(shù)據(jù)
- 這個(gè)過(guò)程,因?yàn)镸ySQL的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)為二叉樹(shù),時(shí)間復(fù)雜度為log2(m) m為t2表的總行數(shù)
- 那么總復(fù)雜度近似為 n+n(2log2(m))
從上面的步驟可以看出,優(yōu)化方向:
- 降低t1查詢時(shí)的開(kāi)銷(xiāo),主要是磁盤(pán)io開(kāi)銷(xiāo),避免全表掃描,用索引
- 降低t2查詢時(shí)的開(kāi)銷(xiāo),也用索引
- 將數(shù)據(jù)量多的表做被驅(qū)動(dòng)表,小表作驅(qū)動(dòng)表,m取了對(duì)數(shù),大表數(shù)據(jù)量大對(duì)復(fù)雜度的影響沒(méi)有線性增長(zhǎng)
- 緩存t1表,不用每次去磁盤(pán)load,比如一次緩存100條,那么能顯著降低磁盤(pán)讀數(shù)據(jù)次數(shù),t2每次與緩存中的t1數(shù)據(jù)進(jìn)行比較
- 隨機(jī)磁盤(pán)讀比較耗費(fèi)磁盤(pán)性能,轉(zhuǎn)為順序讀,因?yàn)槎鏄?shù)的存儲(chǔ)結(jié)構(gòu),每次非主鍵查找,有一個(gè)回表的動(dòng)作,即根據(jù)主鍵再次查詢需要的數(shù)據(jù)
優(yōu)化的基本方法:
- 減少循環(huán)次數(shù),減少磁盤(pán)IO次數(shù),變隨機(jī)IO為順序IO
- 其實(shí)MySQL針對(duì)上面的優(yōu)化方法有對(duì)應(yīng)的算法:
- Simple Nested Loop Join 最普通的循環(huán),這個(gè)要避免
- 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ù)比較大的話,也影響性能,主要是cache pool被占滿,導(dǎo)致MySQL性能下降
- Index Nested Join 就是都通過(guò)主鍵進(jìn)行查找關(guān)聯(lián),這種性能比較好
Batched Key Access Join 這個(gè)是 Index Nested Join上做的優(yōu)化,因?yàn)榛乇淼拇嬖冢S機(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ā)線上是災(zāi)難性的。
總結(jié)
Join還是可以大膽的使用,只要把握好幾個(gè)原則:
- 盡量讓join的列是索引列,而且最好是類(lèi)型相同,盡可能是主鍵索引
- 盡量將小表做驅(qū)動(dòng)表(這一點(diǎn)MySQL在5.6某個(gè)版本后能自動(dòng)完成)
- 養(yǎng)成將寫(xiě)好的SQL進(jìn)行explain的好習(xí)慣,觀察SQL的執(zhí)行過(guò)程