解析SQL Server三大算法的I/O成本_Mssql數據庫教程

推薦：怎樣利用SQL Server 2005中的模板參數
如果你用SQL Server 2005 Management Studio建立函數或存儲過程，你會注意到這些新窗口中都是模板。通常，你可以獲得一個散布著標記的框架。 列表A是一個通過擴張對象瀏覽器(object explorer)中可編程性節點而建立的實例，選擇存儲過程，然后右擊并選擇新的

1. Nested Loop Join(嵌套循環聯結)

算法：

其思路相當的簡單和直接：對于關系R的每個元組 r 將其與關系S的每個元組 s 在JOIN條件的字段上直接比較并篩選出符合條件的元組。寫成偽代碼就是：

代價：

被聯結的表所處內層或外層的順序對磁盤I/O開銷有著非常重要的影響。而CPU開銷相對來說影響較小，主要是元組讀入內存以后(in-memory)的開銷，是 O (n * m)

對于I/O開銷，根據 page-at-a-time 的前提條件，I/O cost = M M * N，

翻譯一下就是 I/O的開銷 = 讀取M頁的I/O開銷 M次讀取N頁的I/O開銷。

2. Sort-Merge Join (排序合并聯結)

Nested Loop一般在兩個集合都很大的情況下效率就相當差了，而Sort-Merge在這種情況下就比它要高效不少，尤其是當兩個集合的JOIN字段上都有聚集索引(clustered index)存在時，Sort-Merge性能將達到最好。

算法：

基本思路也很簡單(復習一下數據結構中的合并排序吧)，主要有兩個步驟：

a.按JOIN字段進行排序

b.對兩組已排序集合進行合并排序，從來源端各自取得數據列后加以比較(需要根據是否在JOIN字段有重復值做特殊的“分區”處理)

代價：(主要是I/O開銷)

有兩個因素左右Sort-Merge的開銷：JOIN字段是否已排序 以及 JOIN字段上的重復值有多少。

◆最好情況下(兩列都已排序且至少有一列沒有重復值)：O (n m) 只需要對兩個集合各掃描一遍。(這里的m，n如果都能用到索引那就更好了)

◆最差情況下(兩列都未排序且兩列上的所有值都相同)：O (n * log n m * log m n * m) 兩次排序以及一次全部元組間的笛卡爾乘積

3. Hash Join (哈希聯結)

Hash Join在本質上類似于兩列都有重復值時的Sort-Merge的處理思想——分區(patitioning)。但它們也有區別：Hash Join通過哈希來分區(每一個桶就是一個分區)而Sort-Merge通過排序來分區(每一個重復值就是一個分區)。

值得注意的是，Hash Join與上述兩種算法之間的較大區別同時也是一個較大限制是它只能應用于等值聯結(equality join)，這主要是由于哈希函數及其桶的確定性及無序性所導致的。

算法：

基本的Hash Join算法由以下兩步組成：

同nested loop，在執行計劃中build input位于上方，probe input位于下方。

hash join操作分兩個階段完成：build(構造)階段和probe(探測)階段。

a.Build Input Phase： 基于JOIN字段，使用哈希函數h2為較小的S集合構建內存中(in-memory)的哈希表，相同鍵值的以linked list組成一個桶(bucket)

b.Probe Input Phase： 在較大的R集合上對哈希表進行核對以完成聯結。

代價：

值得注意的是對于大集合R的每個元組 r ，hash bucket中對應 r 的那個bucket中的每個元組都需要與 r 進行比較，這也是算法最耗時的地方所在。

CPU開銷是O (m n * b) b是每個bucket的平均元組數量。

總結：

三種join方法，都是擁有兩個輸入，優化的基本原則：

1.避免大數據的hash join，(hash join適合低并發情況，他占用內存和io是很大的);

2.盡量將其轉化為高效的merge join、nested loop join。可能使用的手段有表結構設計、索引調整設計、SQL優化，以及業務設計優化。

分享：解讀SQL2005：一個很實用的函數
COALESCE 返回其參數中的第一個非空表達式，當你要在n個字段中選取某一個非空值可以用它，比如下面語句 以下為引用的內容：