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

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

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

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

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

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

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

SC (S#，C#，Grade) 學號 ，課程號，分數(shù)

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

要求：要求檢索出學習課程號為C2的學生學號與姓名

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

方法一：連接查詢

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

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

方法二：嵌套查詢

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

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

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

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

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

在關(guān)系代數(shù)表達式中盡可能早地執(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í)行的時候，它們會被優(yōu)化成方法二的語句去執(zhí)行。

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