首發(fā):
長(zhǎng)沙快付
版權(quán)所有,未經(jīng)許可嚴(yán)禁轉(zhuǎn)載
如果查詢涉及多張表，在優(yōu)化器確定了每個(gè)表最恰當(dāng)?shù)脑L問(wèn)方法后，下一步就是確定將這些表聯(lián)結(jié)起來(lái)的最佳方法以及最恰當(dāng)?shù)捻樞颉１碇g的關(guān)系通過(guò)WHERE子句中的條件來(lái)定義，若未定義則會(huì)產(chǎn)生笛卡爾積。
聯(lián)結(jié)的方法有：嵌套循環(huán)聯(lián)結(jié)、排序-合并聯(lián)結(jié)、散列聯(lián)結(jié)及笛卡爾聯(lián)結(jié)。
每個(gè)聯(lián)結(jié)方法都會(huì)選擇一對(duì)表，所訪問(wèn)的第一張表通常被稱(chēng)為驅(qū)動(dòng)表（the driving table），訪問(wèn)的第二張表則被稱(chēng)為內(nèi)層表或被驅(qū)表（inner或driven-to table）。
優(yōu)化器會(huì)根據(jù)WHERE子句篩選后得到的表行數(shù)進(jìn)行估算，大?。▔K、數(shù)據(jù)行及字節(jié)）最小的表通常被作為驅(qū)動(dòng)表。
通俗來(lái)講，就是“小表驅(qū)動(dòng)大表”。在FROM子句中，最小的表放在最后，作為驅(qū)動(dòng)表來(lái)使用。“小表驅(qū)動(dòng)大表”在兩表都沒(méi)有索引時(shí)常見(jiàn)，可能你跟我一樣認(rèn)為小驅(qū)大或大驅(qū)小沒(méi)有差別。這里
長(zhǎng)沙做網(wǎng)站為了方便理解舉個(gè)例子，但不一定正確，如：T1表有10行，T2表有10000行，1個(gè)塊可以放10行數(shù)據(jù)。那么，以T1為驅(qū)動(dòng)，則T1掃描1個(gè)塊，內(nèi)層T2掃描10次，共掃描塊為（1+1000*10=10001）；以T2為驅(qū)動(dòng)，則T2掃描1000個(gè)塊，內(nèi)層T1掃描10000次，共掃描塊為（1000+1*10000=11000）。所以，你看到了，小表驅(qū)動(dòng)大表掃描塊數(shù)更少。也就是說(shuō)，表聯(lián)結(jié)時(shí)循環(huán)塊是固定的，主要差別在于掃描驅(qū)動(dòng)表的塊數(shù)。
有兩種情況可能驅(qū)動(dòng)表并非最小的表：
當(dāng)優(yōu)化器可以確定聯(lián)結(jié)的列在其中一張表基于UNIQUE或PRIMARY KEY約束時(shí)，即存在索引時(shí)，沒(méi)有索引的表將被作為驅(qū)動(dòng)表。
當(dāng)使用外聯(lián)結(jié)時(shí)，外聯(lián)結(jié)的表必須放在所聯(lián)結(jié)表的后面。
嵌套循環(huán)聯(lián)結(jié)
如果結(jié)果集大小有限并且聯(lián)結(jié)條件列在其中一表上是索引時(shí)采用這種方法最高效。嵌套循環(huán)聯(lián)結(jié)的運(yùn)算成本主要是讀取外層表（驅(qū)動(dòng)表）中的每一行并將其與所匹配的內(nèi)層表中的行聯(lián)結(jié)所需要的成本（這個(gè)不好理解，還是理解我上面舉的例子吧）。
當(dāng)數(shù)據(jù)行經(jīng)過(guò)外層條件篩選并被確認(rèn)匹配后，這些行就會(huì)逐個(gè)進(jìn)入到內(nèi)層循環(huán)。然后基于聯(lián)結(jié)列進(jìn)行逐行檢查是否與被聯(lián)結(jié)表中的某一行相匹配，如果匹配就會(huì)被傳遞到查詢計(jì)劃的下一步或者如果沒(méi)有更多步驟直接被包含在最終結(jié)果集中。
這種聯(lián)結(jié)的強(qiáng)大之處在于使用的內(nèi)存非常少，因?yàn)閿?shù)據(jù)集一次只加工一行，所需要的開(kāi)支也非常小。
在執(zhí)行計(jì)劃中NESTED LOOPS表明使用了嵌套循環(huán)聯(lián)結(jié)。
下面舉例說(shuō)明當(dāng)聯(lián)結(jié)列在基本一表是索引時(shí)另一表作為驅(qū)動(dòng)表的情形：
select empno,ename,dname,loc
from emp,dept
where emp.deptno = dept.deptno;
在本例中，盡管dept在FROM子句的最后，優(yōu)化器也會(huì)選擇emp將作為驅(qū)動(dòng)表（外層表），dept作為被驅(qū)表（內(nèi)層表）。首先對(duì)emp表進(jìn)行全表掃描，所有塊通過(guò)多塊讀方式讀出，再逐行訪問(wèn)，并將聯(lián)結(jié)列（deptno）傳遞給內(nèi)層循環(huán)針對(duì)dept表的查詢。對(duì)于內(nèi)聯(lián)結(jié)，每一行在dept表的deptno列有匹配值的數(shù)據(jù)行都將被返回。對(duì)于外聯(lián)結(jié)，emp表的每一行都將被返回，dept表中無(wú)法匹配的列將用NULL值來(lái)填充。
優(yōu)化器之所以不選擇dept表（即小又在FROM子句最后）作為驅(qū)動(dòng)表，原因如下：
emp表在deptno列上沒(méi)有索引，訪問(wèn)它的唯一方法就是全表掃描。如果將dept表選為驅(qū)動(dòng)表，對(duì)于dept表中的每一行都要在emp表中進(jìn)行全表掃描。但若使用emp表作為驅(qū)動(dòng)表，只需要對(duì)emp進(jìn)行一次全表掃描，對(duì)于dept表則采用deptno上的索引進(jìn)行索引掃描。針對(duì)索引而言，deptno又正好是主鍵列，所以采用INDEX UNIQUE SCAN速度極快。
當(dāng)然，我們也可以通過(guò)hint提示強(qiáng)制優(yōu)化器使用某張表為驅(qū)動(dòng)表，語(yǔ)法如下：
select /*+ ordered use_nl(dept emp) */ empno,ename,dname,loc
from emp,dept
where emp.deptno = dept.deptno;
排序-合并聯(lián)結(jié)
排序-合并聯(lián)結(jié)獨(dú)立地讀取需要聯(lián)結(jié)的兩張表，分別對(duì)表中數(shù)據(jù)行按聯(lián)結(jié)列進(jìn)行排序，然后對(duì)排序后的行集進(jìn)行合并。當(dāng)然，在數(shù)據(jù)行排序前會(huì)經(jīng)過(guò)WHERE子句篩選。
使用這種聯(lián)結(jié)方式對(duì)排序的開(kāi)銷(xiāo)非常大，尤其是內(nèi)存不足而使用臨時(shí)磁盤(pán)空間時(shí)。但一旦數(shù)據(jù)行排序完成，合并的過(guò)程是非常快的。
在合并時(shí)，數(shù)據(jù)庫(kù)輪流操作兩個(gè)列表，比較最上面的數(shù)據(jù)行，丟棄在排序隊(duì)列中比另一個(gè)列表中的最上面的行出現(xiàn)得早的數(shù)據(jù)行，并只返回匹配的行。
在執(zhí)行計(jì)劃中，MERGE JOIN表明使用了排序-合并聯(lián)結(jié)。
在對(duì)兩個(gè)表進(jìn)行排序時(shí)，其實(shí)又加到了之前講過(guò)的數(shù)據(jù)訪問(wèn)方式的選擇。一般而言，在聯(lián)結(jié)列在某表中正好是索引時(shí)，則可通過(guò)INDEX FULL SCAN來(lái)掃描，這樣將避免排序操作。沒(méi)有索引的表則只能通過(guò)全表掃描，然后進(jìn)行排序了。
這種聯(lián)結(jié)方式適用于：
數(shù)據(jù)篩選條件有限并返回有限數(shù)據(jù)行的查詢；
沒(méi)有可用的更直接訪問(wèn)數(shù)據(jù)的索引；
在條件為非等式的時(shí)候，如謂語(yǔ)有between等；
如果數(shù)據(jù)行源非常大，這種聯(lián)結(jié)方式可能是唯一可行的選擇；
同樣，我們可以通過(guò)hint提示強(qiáng)制使用這種聯(lián)結(jié)方式，如：
select /*+ ordered */ empno,ename,dname,loc
from emp,dept
where emp.deptno = dept.deptno;
散列聯(lián)結(jié)
前面講過(guò)，排序-合并聯(lián)結(jié)用來(lái)處理特定的非等式條件，而散列聯(lián)結(jié)則只有在等值聯(lián)結(jié)時(shí)才能進(jìn)行。散列聯(lián)結(jié)與排序-合并聯(lián)結(jié)類(lèi)似，建立散列表所需要的數(shù)據(jù)塊被讀取，然后剩下的工作將會(huì)針對(duì)放在內(nèi)存或臨時(shí)磁盤(pán)空間的散列數(shù)據(jù)來(lái)進(jìn)行。散列聯(lián)結(jié)具體工作方式如下：
首先，兩表都經(jīng)過(guò)WHERE子句篩選得到行數(shù)據(jù)。然后基于表和索引的統(tǒng)計(jì)信息，被確定為返回最少行數(shù)的表被完全散列化（對(duì)聯(lián)結(jié)列運(yùn)用hash函數(shù)）到內(nèi)存中。這個(gè)散列表包含了原表的所有數(shù)據(jù)行并被基于聯(lián)結(jié)鍵的散列值（hash值）的隨機(jī)函數(shù)載入到“散列桶”中。只要有足夠的內(nèi)存空間，這個(gè)散列表將一直放在內(nèi)存中。如果沒(méi)有足夠的內(nèi)存，散列表將會(huì)被寫(xiě)入臨時(shí)磁盤(pán)空間。
其次，讀取另一張較大的表并對(duì)聯(lián)結(jié)鍵應(yīng)用散列函數(shù)，然后利用得到的散列值跟內(nèi)存中散列表進(jìn)行匹配以尋找存放有相應(yīng)行數(shù)據(jù)的“散列桶”。如果匹配成功，則返回這一行數(shù)據(jù)，否則丟棄。較大的表只讀取一次，并檢查其中的每一行來(lái)匹配。這與嵌套循環(huán)聯(lián)結(jié)不同之處在于此處內(nèi)層表被多次讀取（內(nèi)層表被多次讀取的不應(yīng)該是NESTED LOOP嗎，而HASH JOIN好像才是內(nèi)層表只被讀一次吧）。
在執(zhí)行計(jì)劃中，散列聯(lián)結(jié)用HASH JOIN來(lái)表示。
在散列聯(lián)結(jié)的執(zhí)行計(jì)劃中，較小的散列表列在前面而探測(cè)表列在后面。決定哪個(gè)表最小的不僅取決于數(shù)據(jù)行數(shù)還取決于這些行的大小，因?yàn)檎麄€(gè)行都必須要存放在散列表中。
當(dāng)數(shù)據(jù)行源較大并且結(jié)果集也較大的情況下將更傾向于考慮散列聯(lián)結(jié)，或者如果要聯(lián)結(jié)的其中一張表確定總是返回同一數(shù)據(jù)行源，也可能會(huì)選用散列聯(lián)結(jié)因?yàn)檫@樣僅訪問(wèn)一次這張表。如果在這種情況下選用嵌套循環(huán)聯(lián)結(jié)，這個(gè)數(shù)據(jù)行源就會(huì)被一遍一遍地訪問(wèn)，需要比單獨(dú)訪問(wèn)一次多做很多工作。最后，如果較小的表可以放到內(nèi)存中，散列聯(lián)結(jié)也會(huì)很受歡迎。
笛卡爾聯(lián)結(jié)
笛卡爾聯(lián)結(jié)發(fā)生在當(dāng)一張表中的所有行與另一張表中的所有行聯(lián)結(jié)的時(shí)候。
在執(zhí)行計(jì)算中，MERGE JOIN CARTESIAN表明使用笛卡爾聯(lián)結(jié)。
笛卡爾聯(lián)結(jié)可能會(huì)導(dǎo)致得到很多重復(fù)行的結(jié)果集，此時(shí)若使用distinct雖然可以去除重復(fù)行，但代價(jià)極高，需要先排序再卻重。所以我們最好避免產(chǎn)生笛卡爾積。
外聯(lián)結(jié)
外聯(lián)結(jié)需要外聯(lián)結(jié)表作為驅(qū)動(dòng)表，這意味著有可能不能選用更加優(yōu)化的聯(lián)結(jié)執(zhí)行順序。因此，使用外聯(lián)結(jié)要格外小心，因?yàn)樗倪x用有可能會(huì)影響到整個(gè)執(zhí)行計(jì)劃的性能。
