資料庫索引的基礎知識

一、理解索引的結構

  索引在資料庫中的作用類似於目錄在書籍中的作用，用來提高查找資訊的速度。使用索引查找資料，無需對整表進行掃描，可以快速找到所需資料。微軟的SQL SERVER提供了兩種索引：聚集索引（clustered index，也稱聚類索引、簇集索引）和非聚集索引（nonclustered index，也稱非聚類索引、非簇集索引）。

  SQL Server 中資料存儲的基本單位是頁（Page）。資料庫中的資料檔案（.mdf 或 .ndf）分配的磁碟空間可以從邏輯上劃分成頁（從 0 到 n 連續編號）。磁片 I/O 操作在頁級執行。也就是說，SQL Server 每次讀取或寫入資料的最少資料單位是資料頁。

  下面我們先簡單的瞭解一下索引的體系結構：

  1. 聚集索引結構

  在 SQL Server 中，索引是按 B 樹結構進行組織的。

  聚集索引單個分區中的結構:

--建立UserAddDate聚集索引

CREATE CLUSTERED INDEX [IX_AddDate] ON [User]

(

 [AddDate] ASC

)   

  聚集索引（Clustered Index）特點

·         聚集索引的葉節點就是實際的資料頁

·         聚集索引中的排序順序僅僅表示資料頁鏈在邏輯上是有序的。而不是按照順序物理的存儲在磁片上

·         行的物理位置和行在索引中的位置是相同的

·         每個表只能有一個聚集索引

·         聚集索引的平均大小大約為表大小的5%左右

  2. 非聚集索引結構

  非聚集索引與聚集索引具有相同的 B 樹結構，它們之間的顯著差別在於以下兩點：

  1. 基礎資料表的資料行不按非聚集鍵的順序排序和存儲。

  2. 非聚集索引的葉層是由索引頁而不是由資料頁組成。

  下圖示意了單個分區中的非聚集索引結構：

  包含列的索引

  通過將包含列（稱為非鍵列）添加到索引的葉級，可以擴展非聚集索引的功能。鍵列存儲在非聚集索引的所有級別，而非鍵列僅存儲在葉級別。

  下面舉個簡單的例子來說明一下聚集索引和非聚集索引的區別：

  我們有一本漢語字典，可以把它的正文本身看做是一個聚集索引，它是按照漢字拼音的開頭字母排序的，不再需要查找其他目錄。當遇到不認識的字時，需要結合“部首目錄”和“檢字表”， 先找到目錄中的結果，然後再翻到您所需要的頁碼。通過這種方法查到的目錄中字的排序並不是真正的正文的排序方法。把這種看做是一個非聚集索引。

  另外，請注意每個表只能有一個聚集索引。

--建立UserAddDate非聚集索引

CREATE NONCLUSTERED INDEX [IX_AddDate] ON [User]

(

 [AddDate] ASC

)   

  非聚集索引 （Unclustered Index） 特點

·         非聚集索引的頁，不是資料，而是指向資料頁的頁。

·         若未指定索引類型，則預設為非聚集索引。

·         葉節點頁的次序和表的物理存儲次序不同

·         每個表最多可以有249個非聚集索引

·         在非聚集索引創建之前創建聚集索引（否則會引發索引重建）

  二、選擇建立哪種索引

  1. 何時創建聚集索引更能提高性能

  Clustered Index會提高大多數table的性能，尤其是當它滿足以下條件時：

  獨特， 狹窄， 持續增長的，最好是只向上增加。例如：

·         Identity

·         Date, identity

·         GUID (only when using newsequentialid() function) 

  2. 非聚集索引提高性能的方法

  非聚集索引由於B樹的節點不是具體資料頁，有時候由於這個原因，會導致非聚集索引甚至不如表遍歷來的快。但是，非聚集索引有個特性，如果你要查詢的內容，在非聚集索引中以及被覆蓋到了，則不需要繼續到聚集索引，或者RID(heap結構中的行識別字)中去尋找資料了，這時候就可以很大的提高性能，這就是覆蓋面(Covering) 的問題。

  由於聚集索引葉子節點就是具體資料，所以聚集索引的覆蓋率是100%， 通過提高覆蓋面來提高性能的問題也就只有非聚集索引（Nonclustered Indexes）才存在。

  當查詢中所有的columns都包括在index上時，我們說這 index covers the query. Columns的順序在此不重要(Select 時候的順序不重要，但是Index 建立的順序可得小心了)。

  在 SQL Server 2005 中，為了提高這種 Covering 帶來的好處，甚至可以通過將非鍵列添加到非聚集索引的葉級別來擴展非聚集索引的功能。

  補充：只有查詢在具有高度選擇性的情況下，非聚集索引才有優勢。

  三、使用聚集索引或非聚集索引的場景 (注：優先順序依次為推薦，應，不應)

  四、主鍵和聚集索引的比較

  以下是一些大眾點評網中測試使用的示例：

CHECKPOINT

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

declare @d datetime

set @d=getdate()

SELECT * FROM User WHERE AddDate>'2008-06-01' AND AddDate<'2008-06-10'

select [語句執行花費時間(毫秒)]=datediff(ms,@d,getdate())

--(45077 行受影響)

--表'User'。掃描計數1，邏輯讀取1103 次，物理讀取2 次，預讀1090 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

--2543

CHECKPOINT

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

declare @d datetime

set @d=getdate()

SELECT * FROM User WITH (INDEX=IX_AddDate) WHERE AddDate>'2008-06-01' AND AddDate<'2008-06-10'

select [語句執行花費時間(毫秒)]=datediff(ms,@d,getdate())

--(45077 行受影響)

--表'User'。掃描計數1，邏輯讀取45165 次，物理讀取133 次，預讀141 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

--3860

  五、使用索引的代價

·         索引需要佔用資料表以外的物理存儲空間

·         創建索引和維護索引要花費一定的時間

·         當對表進行更新操作時，索引需要被重建，這樣降低了資料的維護速度。