在開發(fā)中常聽到的 left join，Inner JOIN ，right join ，cross join 這些連接都是數(shù)據(jù)庫的邏輯連接，那么數(shù)據(jù)庫在執(zhí)行這些連接的時(shí)候，數(shù)據(jù)庫是如何在底層實(shí)現(xiàn)表的關(guān)聯(lián)的呢？這就是物理連接
目前大部分關(guān)系型數(shù)據(jù)庫支持3種物理連接(mysql 貌似到5.6未知僅支持嵌套連接)
數(shù)據(jù)庫的物理連接：
數(shù)據(jù)庫接兩個(gè)表時(shí)候，會(huì)考慮3種方法：嵌套連接，合并連接，散列連接。3種方法的連接都有一個(gè)表被選為外表（只掃描一次），一個(gè)表被選為內(nèi)表（坑內(nèi)掃描多次）。如果查詢連接了多個(gè)表，每次查詢只能連接2各表，并保存把中間結(jié)果，用戶連接另外的表。
1、嵌套連接：外表只掃描一次，而針對(duì)外表的每一行都需要掃描一次內(nèi)表。（注意：連接不能用long或lob字段）
2、合并連接：合并連接需要一個(gè)等式連接詞（A.col1=B.col1），還要求根據(jù)連接列對(duì)兩個(gè)連接的表進(jìn)行排序。合并連接內(nèi)表和外表都只掃描一次，有時(shí)情況下需要再次掃描內(nèi)表的部分值，例如，外表存在重復(fù)值的情況下。所以優(yōu)化器通常選擇重復(fù)值較少的表作為外表。
3、散列連接：散列連接需要一個(gè)或多個(gè)等式連接謂詞，其中每個(gè)謂詞的類型相同。char類型長度必須相同。decimal進(jìn)度必須相同。散列連接可以處理多個(gè)等式謂詞。對(duì)于散列連接，首先掃描內(nèi)表（也稱為構(gòu)件表 build table），表中的行被復(fù)制到內(nèi)存緩沖區(qū)。根據(jù)“散列鍵（hash key）”，這些緩沖區(qū)被分為幾個(gè)分區(qū)，如果內(nèi)存中沒有足夠的空間容納整個(gè)表，則有些分區(qū)被寫入磁盤上的臨時(shí)表。然后掃描外表。然后掃描外表（探測(cè)表）。對(duì)于外表中的每一行，對(duì)連接列應(yīng)用同意散列算法。如果所獲得的散列鍵與行的散列鍵相匹配，讀取分區(qū)里面的所有行并應(yīng)用連接謂詞。如果與探測(cè)表行匹配的分區(qū)在內(nèi)存中，則讀取分區(qū)并應(yīng)用連接謂詞將立即進(jìn)行。如果分區(qū)被寫入臨時(shí)表，則探測(cè)行也別寫入臨時(shí)表。最后，匹配統(tǒng)一分區(qū)行和對(duì)應(yīng)分區(qū)被一起處理。

優(yōu)化器使用3種連接的場(chǎng)景：
嵌套連接：
對(duì)于被連接的數(shù)據(jù)子集較小的情況，nested loop連接是個(gè)較好的選擇。nested loop就是掃描一個(gè)表，每讀到一條記錄，就根據(jù)索引去另一個(gè)表里面查找，沒有索引一般就不會(huì)是 nested loops。
一般在nested loop中， 驅(qū)動(dòng)表滿足條件結(jié)果集不大，被驅(qū)動(dòng)表的連接字段要有索引，這樣就走nstedloop。如果驅(qū)動(dòng)表返回記錄太多，就不適合nested loops了。如果連接字段沒有索引，則適合走h(yuǎn)ash join，因?yàn)椴恍枰饕?br />Inner table被Outer table驅(qū)動(dòng)，outer table返回的每一行都要在inner table中檢索到與之匹配的行。
Outer table: 小表、驅(qū)動(dòng)表
Inner table: 被驅(qū)動(dòng)表、大表

哈希連接： 哈希連接是做大數(shù)據(jù)集連接時(shí)的常用方式。優(yōu)化器掃描小表（或數(shù)據(jù)源），利用連接鍵（也就是根據(jù)連接字段計(jì)算hash值）在內(nèi)存中建立hash表，然后掃描大表，每讀到一條記錄就來探測(cè)hash表一次，找出與hash表匹配的行。
當(dāng)小表可以全部放入內(nèi)存中，其成本接近全表掃描兩個(gè)表的成本之和。如果表很大不能完全放入內(nèi)存，這時(shí)優(yōu)化器會(huì)將它分割成若干不同的分區(qū)，不能放入內(nèi)存的部分就把該分區(qū)寫入磁盤的臨時(shí)段，此時(shí)要有較大的臨時(shí)段從而盡量提高I/O 的性能。臨時(shí)段中的分區(qū)都需要換進(jìn)內(nèi)存做hash join。這時(shí)候成本接近于全表掃描小表+分區(qū)數(shù)*全表掃描大表的代價(jià)和。
至于兩個(gè)表都進(jìn)行分區(qū)，其好處是可以使用parallel query，就是多個(gè)進(jìn)程同時(shí)對(duì)不同的分區(qū)進(jìn)行join，然后再合并。但是復(fù)雜。
以下條件下hash join可能有優(yōu)勢(shì)：
兩個(gè)巨大的表之間的連接。
在一個(gè)巨大的表和一個(gè)小表之間的連接。

合并連接：
對(duì)連接的每個(gè)表做table access full；
對(duì)table access full的結(jié)果按照連接鍵進(jìn)行排序；
進(jìn)行merge join對(duì)排序結(jié)果進(jìn)行合并。
Sort merge join性能開銷幾乎都在前兩步。相較于前兩種連接，合并連接的速度最快的。但也看數(shù)據(jù)庫的索引和數(shù)據(jù)量情況。

通常情況下河西連接的效果都比合并連接要好，然而如果行源已經(jīng)被排過序，在執(zhí)行合并連接時(shí)不需要再排序了，這時(shí)合并連接的性能會(huì)優(yōu)于哈希連接。
在全表掃描比索引范圍掃描再通過rowid進(jìn)行表訪問更可取的情況下，合并會(huì)比嵌套性能更佳。

各類關(guān)系型數(shù)據(jù)庫都有相應(yīng)參數(shù)可以強(qiáng)制指定那一種連接進(jìn)行測(cè)試。。。

轉(zhuǎn)載于:https://www.cnblogs.com/vansky/p/8900552.html

總結(jié)

以上是生活随笔為你收集整理的关系型数据库的物理连接的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。