SQL Server索引 - 叢集索引、非叢集索引、非聚集唯一索引 <第八篇>

標籤：情況   使用   失敗   ons   工作   product   同名   add   過濾   

叢集索引、非叢集索引、非聚集唯一索引

　　我們都知道建立適當的索引能夠提高查詢速度，最佳化查詢。先說明一下，無論是叢集索引還是非叢集索引都是B樹結構。

• 　　叢集索引預設與主鍵相匹配，在設定主鍵時，SQL Server會預設在主鍵列建立叢集索引。但是可以手動更改為在任意一個列建立叢集索引，然後在另一個欄位或多個欄位上定義主鍵。這時主鍵將會被作為一個唯一的非叢集索引(唯一索引)被建立。通過指定NONCLUSTERED關鍵字就可以做到。

  CREATE TABLE MyTableKeyExample{　　Column1 int IDENTITY  KEY NONCLUSTERED,　　Column2 int }

• 　　叢集索引實際上裝載了SQL Server的資料記錄行，叢集索引的葉級就是資料行，所以到達了叢集索引的葉級就到達了資料。
• 　　為表聲明主鍵或唯一約束時，SQL Server會自動建立與之對應的唯一索引。
• 　　叢集索引的列最好就是自增的，因為根據區段-頁的理論，如果叢集索引列是自增的，那麼添加資料的時候，所見是放在索引的最後，不會發生由中間插入的情況，這樣就不會引起頁面分割。而如果索引列不是自增的，添加資料的時候，還要按順序找到該條記錄的位置，並且插入，如果插入比較頻繁，還可能會經常引起頁面分割。
• 　　叢集索引的最佳資料類型，smallint、int、bigint、datetme。
• 　　最好避免組合叢集索引。

　　索引(index)是除了表之外的另一個重要的、使用者定義的儲存在資料庫裡的資料結構。當根據索引碼的值搜尋資料時，索引提供了對資料的快速存取。事實上，沒有索引資料庫也能夠根據SELECT語句通過表掃描成功地檢索到結果，但是隨著表變得越來越大，使用“適當”的索引的效果就越來越明顯。但如果使用索引時不認真考慮其實現過程，索引反而有可能會降低資料庫的工作效能。建立主鍵時會自動建立叢集索引，除非當前表中已經含有了叢集索引或是建立主鍵時指定了NONCLUSTERED關鍵字。

　　叢集索引、非叢集索引、非聚集唯一索引：

　　SqlServer提供了兩種索引：叢集索引和非叢集索引。聚集的作用就是將某一列（或是多列）的物理順序改變為和邏輯順序相一致。

　　叢集索引(CLUSTERED)與非叢集索引(NONCLUSTERED)的區別：

　　其實，我們的漢語字典的本文本身就是一個叢集索引(按照拼音的英文字母排序) 比如，我們要查“安”字 ，就會很自然地翻開字典的前幾頁，因為“安”的拼音是“an”，而按照拼音排序漢字的字典是以英文字 母“a”開頭並以“z”結尾的，那麼“安”字就自然地排在字典的前部。如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那麼就說明您的字典中沒有這個字；同樣的，如果查“張”字，那您也會將您的 字典翻到最後部分，因為“張”的拼音是“zhang”。也就是說，字典的本文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的內容。我們把這種本文內容本身就是一種按照一定規則排列的目錄稱為“叢集索引”。 這也是為什麼一張表只能 夠有一個叢集索引的原因。因為一張表只能夠按一種方式排序。 其中叢集索引的葉級節點就是資料。你可以理解為有很多列火車，按照火車頭索引(排序)，火車頭後面跟著的就是資料。
　　如果您認識某個字，您可以快速地從自動中查到這個字。但您也可能會遇到您不認識的字，不知道它的發音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據“偏旁部首”查到您要找的字，然後根據這個字後的頁碼直接翻到某頁來找到您要找的字。但您結合“部首目錄”和“檢字表”而查到的字的排序並不是真正的本文的排序方法，比如您查“張”字，我們可以看到在查部首之後的檢字表中“張” 的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是 “弩”字，頁面390頁。很顯然，這些字並不是真正的分別位於“張”字的上下方，現在您看到的連續的“馳、張、弩” 三字實際上就是他們在非叢集索引中的排序，是字典本文中的字在非叢集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結果，然後再翻到您所需要的頁碼。我們把這種目錄純粹是目錄，本文純粹是本文的排序方式稱為“非叢集索引”。

　　實際上如果表上有叢集索引，則非叢集索引中儲存著叢集索引的引用，然後通過利用叢集索引來擷取資料。因此這就是為什麼返回少量資料的時候使用非叢集索引的效能較好，而返回大量的資料的時候使用非叢集索引還不如直接全表掃描來得快。而如果表上沒有叢集索引的時候，則引用行號。

　　非叢集索引需要額外的空間進行儲存，按照被索引列進行叢集索引，並在B樹的葉子節點包含指向非叢集索引所在表的指標。非叢集索引也是B樹結構，另外一個單獨的B樹。

　　唯一索引：

 　　當主鍵建立時如果不設定為叢集索引，那麼就一定是唯一的非叢集索引。實際上，唯一索引，故名思議就是它要求該列上的值是唯一的。

　　叢集索引列可以重複，非叢集索引列也可以重複。這就是為什麼要有唯一這個東東了。聲明唯一索引的文法很簡單，只是多了個UNIQUE關鍵字。

　　CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_Name] ON Production.Product ( [Name] );

　　唯一索引有很多限制和特性。下面詳細學習下唯一索引。

　　為表聲明主鍵或唯一約束時，SQL Server會自動建立與之對應的唯一索引。定義一個唯一約束時，SQL Server會自動建立一個與之同名的唯一索引，要刪除索引必須先刪除約束。但刪除約束，刪除約束也會導致與之關聯的索引被刪除，也就是說，不能刪除唯一索引，要刪除索引只有刪除唯一約束這個辦法。

　　唯一索引依賴於唯一約束，刪除唯一索引必須刪除唯一約束。另外SQL Server又在建立唯一約束時又預設建立唯一索引。

　　總結起來就是：唯一索引與唯一約束始終一同存在。

　　因為定義一個主鍵或是定義約束會導致索引被建立，所以你必須在約束定義時就給出必要的索引資訊，因此上面ALTER TABLE語句中包含了”CLUSTERED”關鍵字。

ALTER TABLE Production.Product ADD CONSTRAINT PK_Product_ProductID PRIMARY KEY CLUSTERED ( ProductID );

　　 如果唯一索引或約束所約束的列在當前的表中已經含有了重複值，那麼建立索引會失敗。而當唯一索引建立成功後，所有違反這個約束的INSERT、UPDATE語句都會失敗。

訊息 2601，層級 14，狀態 1，第 1 行 不能在具有唯一索引 ‘AK_Product_Name‘ 的對象 ‘Production.Product‘ 中插入重複鍵的行。 語句已終止。 

　　唯一約束和唯一索引並沒有顯著的區別。建立獨立的唯一索引和使用唯一約束對於資料的驗證方式並無區別。查詢最佳化工具也不會區分唯一索引是由約束建立還是手工建立。然而以資料完整性為目標的話，最好建立約束，這使得對應的索引的目標一目瞭然。

　　過濾唯一索引，當我們需要既允許多個NULL值，又不允許重複的時候，可以使用這個：

　　CREATE UNIQUE NONCLUSTERED INDEX xx on 　　ProductDemo(<索引列>)　　--指定索引列　　where <索引列>!=null)　　--過濾條件

SQL Server索引 - 叢集索引、非叢集索引、非聚集唯一索引 <第八篇>