SQL Server構造模型及查詢最佳化技術

在《資料庫原理》裡面，對聚簇索引的解釋是:聚簇索引的順序就是資料的實體儲存體順序，而對非聚簇索引的解釋是:索引順序與資料物理排列順序無關。正式因為如此，所以一個表最多隻能有一個聚簇索引。

不過這個定義太抽象了。在SQL Server中，索引是通過二叉樹的資料結構來描述的，我們可以這麼理解聚簇索引：索引的分葉節點就是資料節點。而非聚簇索引的分葉節點仍然是索引節點，只不過有一個指標指向對應的資料區塊。如：

非聚簇索引

 

聚簇索引

聚簇索引與非聚簇索引的本質區別到底是什嗎？什麼時候用聚簇索引，什麼時候用非聚簇索引？

這是一個很複雜的問題，很難用三言兩語說清楚。我在這裡從SQL Server索引最佳化查詢的角度簡單談談(如果對這方面感興趣的話，可以讀一讀微軟出版的《Microsoft SQL Server 2000資料庫編程》第3單元的資料結構引論以及第6、13、14單元)。

一、索引塊與資料區塊的區別

大家都知道，索引可以提高檢索效率，因為它的二叉樹結構以及佔用空間小，所以訪問速度塊。讓我們來算一道數學題：如果表中的一條記錄在磁碟上佔用1000位元組的話，我們對其中10位元組的一個欄位建立索引，那麼該記錄對應的索引塊的大小隻有10位元組。我們知道，SQL Server的最小空間配置單位是“頁（Page）”，一個頁在磁碟上佔用8K空間，那麼這一個頁可以儲存上述記錄8條，但可以儲存索引800條。現在我們要從一個有8000條記錄的表中檢索符合某個條件的記錄，如果沒有索引的話，我們可能需要遍曆8000條×1000位元組/8K位元組=1000個頁面才能夠找到結果。如果在檢索欄位上有上述索引的話，那麼我們可以在8000條×10位元組/8K位元組=10個頁面中就檢索到滿足條件的索引塊，然後根據索引塊上的指標逐一找到結果資料區塊，這樣IO訪問量要少的多。

二、索引最佳化技術

是不是有索引就一定檢索的快呢？答案是否。有些時候用索引還不如不用索引快。比如說我們要檢索上述表中的所有記錄，如果不用索引，需要訪問8000條×1000位元組/8K位元組=1000個頁面，如果使用索引的話，首先檢索索引，訪問8000條×10位元組/8K位元組=10個頁面得到索引檢索結果，再根據索引檢索結果去對應資料頁面，由於是檢索所有資料，所以需要再訪問8000條×1000位元組/8K位元組=1000個頁面將全部資料讀取出來，一共訪問了1010個頁面，這顯然不如不用索引快。

SQL Server內部有一套完整的資料檢索最佳化技術，在上述情況下，SQL Server的查詢計劃（Search Plan）會自動使用表掃描的方式檢索資料而不會使用任何索引。那麼SQL Server是怎麼知道什麼時候用索引，什麼時候不用索引的呢？SQL Server除了日常維護資料資訊外，還維護著資料統計資訊，是資料庫屬性頁面的一個：

我們可以看到，SQL Server自動維護統計資訊，這些統計資訊包括資料密度資訊以及資料分布資訊，這些資訊協助SQL Server決定如何制定查詢計劃以及查詢是是否使用索引以及使用什麼樣的索引（這裡就不再解釋它們到底如何協助SQL Server建立查詢計劃的了）。我們還是來做個實驗。建立一張表：tabTest(ID, unqValue，intValue)，其中ID是整形自動編號主索引，unqValue是uniqueidentifier類型，在上面建立普通索引，intValue 是整形，不建立索引。之所以掛上一個沒有索引的intValue欄位，就是防止SQL Server使用索引覆蓋查詢最佳化技術，這樣實驗就起不到作用了。向表中錄入10000條隨機記錄，代碼如下：

CREATE TABLE [dbo].[tabTest] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [unqValue] [uniqueidentifier] NOT NULL ,
 [intValue] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tabTest] WITH NOCHECK ADD 
 CONSTRAINT [PK_tabTest] PRIMARY KEY  CLUSTERED 
 (
  [ID]
 )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[tabTest] ADD 
 CONSTRAINT [DF_tabTest_unqValue] DEFAULT (newid()) FOR [unqValue]
GO

CREATE  INDEX [IX_tabTest_unqValue] ON [dbo].[tabTest]([unqValue]) ON [PRIMARY]
GO

declare @i int
declare @v int

set @i=0
while @i<10000
begin
    set @v=rand()*1000    
    insert into tabTest ([intValue]) values (@v)
    set @i=@i+1
end

然後我們執行兩個查詢並查看執行計畫，（在查詢分析器的查詢菜單中可以開啟查詢計劃，同時圖上第一個查詢的GUID是我從資料庫中找的，大家做實驗的時候可以根據自己資料庫中的值來定）：

可以看出，在第一個查詢中，SQL Server使用了IX_tabTest_unqValue索引，根據箭頭方向，電腦先在索引範圍內找，找到後，使用Bookmark Lookup將索引節點映射到資料節點上，最後給出SELECT結果。在第二個查詢中，系統直接遍曆表給出結果，不過它使用了聚簇索引，為什麼呢？不要忘了，聚簇索引的頁節點就是資料節點！這樣使用聚簇索引會更快一些（不受資料刪除、更新留下的儲存空洞的影響，直接遍曆資料是要跳過這些空洞的）。

下面，我們在SQL Server中將ID欄位的聚簇索引更改為非聚簇索引，然後再執行select * from tabTest，這回我們看到的執行計畫變成了：

SQL Server沒有使用任何索引，而是直接執行了Table Scan，因為只有這樣，檢索效率才是最高的。

三、聚簇索引與非聚簇索引的本質區別

現在可以討論聚簇索引與非聚簇索引的本質區別了。正如本文最前面的兩個圖所示，聚簇索引的分葉節點就是資料節點，而非聚簇索引的頁節點仍然是索引檢點，並保留一個連結指向對應資料區塊。

還是通過一道數學題來看看它們的區別吧：假設有一8000條記錄的表，表中每條記錄在磁碟上佔用1000位元組，如果在一個10位元組長的欄位上建立非聚簇索引主鍵，需要二叉樹節點16000個（這16000個節點中有8000個分葉節點，每個頁節點都指向一個資料記錄），這樣資料將佔用8000條×1000位元組/8K位元組=1000個頁面；索引將佔用16000個節點×10位元組/8K位元組=20個頁面，共計1020個頁面。

同樣一張表，如果我們在對應欄位上建立聚簇索引主鍵，由於聚簇索引的頁節點就是資料節點，所以索引節點僅有8000個，佔用10個頁面，資料仍然佔有1000個頁面。

下面我們看看在執行插入操作時，非聚簇索引的主鍵為什麼比聚簇索引主鍵要快。主鍵約束要求主鍵不能出現重複，那麼SQL Server是怎麼知道不出現重複的呢？唯一的方法就是檢索。對於非聚簇索引，只需要檢索20個頁面中的16000個節點就知道是否有重複，因為所有主鍵索引值在這16000個索引節點中都包含了。但對於聚簇索引，索引節點僅僅包含了8000個中間節點，至於會不會出現重複必須檢索另外8000個頁資料節點才知道，那麼相當於檢索10+1000=1010個頁面才知道是否有重複。所以聚簇索引主鍵的插入速度要比非聚簇索引主鍵的插入速度慢很多。

讓我們再來看看資料檢索的效率，如果對上述兩表進行檢索，在使用索引的情況下（有些時候SQL Server執行計畫會選擇不使用索引，不過我們這裡姑且假設一定使用索引），對於聚簇索引檢索，我們可能會訪問10個索引頁面外加1000個資料頁面得到結果（實際情況要比這個好），而對於非聚簇索引，系統會從20個頁面中找到合格節點，再映射到1000個資料頁面上（這也是最糟糕的情況），比較一下，一個訪問了1010個頁面而另一個訪問了1020個頁面，可見檢索效率差異並不是很大。所以不管非聚簇索引也好還是聚簇索引也好，都適合排序，聚簇索引僅僅比非聚簇索引快一點。

結語

好了，寫了半天，手都累了。關於聚簇索引與非聚簇索引效率問題的實驗就不做了，感興趣的話可以自己使用查詢分析器對查詢計劃進行分析。SQL Server是一個很複雜的系統，尤其是索引以及查詢最佳化技術，Oracle就更複雜了。瞭解索引以及查詢背後的事情不是什麼壞事，它可以協助我們更為深刻的瞭解我們的系統。