首頁技術(shù)文章正文

Java培訓(xùn)之關(guān)于嵌套查詢和連接查詢的效率問題

更新時(shí)間:2016年08月29日17時(shí)22分來源:傳智播客Java培訓(xùn)學(xué)院瀏覽次數(shù):

這幾天給部門寫講稿，總是講到連接和嵌套的問題，連接好像大多數(shù)用得多一點(diǎn)，感覺和我們的思維很接近，不容易接受嵌套的思路，但實(shí)際上，在相同的問題上，我們用嵌套，會獲得比連接高十倍的效率。

下面我們來做一個(gè)例子說明 blog上貼圖真不舒服，就把圖都省略了。。。。

現(xiàn)在設(shè)教學(xué)數(shù)據(jù)庫中有三個(gè)基本表

S (S#，Sname,Age,Sex) 學(xué)號，姓名，年齡，性別

SC (S#，C#，Grade) 學(xué)號 ，課程號，分?jǐn)?shù)

C(C#，Cname,Teacher) 課程號，課程名，任課老師

要求：要求檢索出學(xué)習(xí)課程號為C2的學(xué)生學(xué)號與姓名

下面我們用連接和嵌套二種方法并執(zhí)行并跟蹤，看它們的執(zhí)行效果，

方法一：連接查詢

SELECT S.S#,SNAME FROM S,SC WHERE S.S# = SC.S# AND C# = 'C2'

我們來分析方法一的查詢過程：先對s和sc做笛卡爾積，得到一個(gè)S的行數(shù)+SC行數(shù)的二維表，然后對二該表進(jìn)行逐行掃描，本例中也就是對一個(gè)9+21 =30 行的表進(jìn)行掃描。從查詢分析器我們看到，在數(shù)據(jù)庫中的邏輯處理是Inner join ，實(shí)際上數(shù)據(jù)庫進(jìn)行了哈希匹配的操作，在進(jìn)行這項(xiàng)操作的時(shí)候預(yù)計(jì)成本達(dá)到0.017847（這個(gè)cpu成本究竟指什么我還不是很清楚，但可以肯定的是它是個(gè)資源消耗指標(biāo)），預(yù)計(jì)子樹成本為0.0931

方法二：嵌套查詢

SELECT S#,SNAMEFROM S WHERE S# IN (SELECT S# FROM SC WHERE C# = 'C2')

我們來分析方法二的查詢過程，數(shù)據(jù)庫先檢索選修出課程為C2的學(xué)生，得到一個(gè)6行的二維表，再對該6行數(shù)據(jù)和S表進(jìn)行掃描檢索。

從上圖可以看出進(jìn)行物理上的嵌套循環(huán)操作，cpu成本僅需要0.00038，執(zhí)行成本僅需要0.000131，預(yù)計(jì)子樹成本減小到0.0769。

從以上分析可以看出，方法二和方法一同樣可以達(dá)到檢索出選修了課程C2的學(xué)生姓名和學(xué)號，但是方法二消耗資源要要精減得多，速度要快，成本比方法降低非常多。因?yàn)榉椒ǘ冗M(jìn)行子結(jié)果選擇操作，再對子結(jié)果進(jìn)行查詢，這樣對于時(shí)間和空間的開銷都要小得多，所以我們可以看到，連接的消耗是很大的。

從這個(gè)例子，我們重申上節(jié)課提出的優(yōu)化策略的第一條：

在關(guān)系代數(shù)表達(dá)式中盡可能早地執(zhí)行選擇操作

題外：此題還可以有二種寫法：

select s#,sname from s where exists (select * from sc where sc.s# = s.s# and c# = 'c2')

和

Select s#,sname from s where ‘c2’ in (select c# from sc where s# = s.s#)

這二種寫法的效率和方法二是一模一樣的，在sql內(nèi)部執(zhí)行的時(shí)候，它們會被優(yōu)化成方法二的語句去執(zhí)行。

本文版權(quán)歸傳智播客Java培訓(xùn)學(xué)院所有，歡迎轉(zhuǎn)載，轉(zhuǎn)載請注明作者出處。謝謝！
作者：傳智播客Java培訓(xùn)學(xué)院
首發(fā)：http://metathetuscanyresort.com/javaee