資料庫索引，你該瞭解的幾件事

1．資料庫的資料存放區

1.1檔案：

我們一旦建立一個資料庫，都會產生兩個檔案：

DataBaseName.mdf: 主檔案，這是資料庫中的資料最終存放的地方。

DataBaseName.ldf:記錄檔，由資料操作產生的一系列日誌記錄。

1.2分區：

在一個給定的檔案中，為表和索引分配空間的基本存放裝置單位。 1個區佔64KB,由8個連續的頁組成。 如果一個分區已滿，但需存一條新的記錄，那麼該記錄將佔用整個新分區的空間。

1.3 頁：

分區中的一個分配單位。這是實際資料行最終存放的地方。 頁用於儲存資料行。

Sql Server有多種類型的頁：

Data, Index,BLOB,GAM(Global Allocation Map),SGAM,PFS(Page Free Space),IAM(Index Allocation Map),BCM(Bulk Changed Map)等。

BKJIA資料庫頻道向您推薦：MySQL索引:資料庫效能的雙刃劍

2． 索引

2.1.1索引

索引是與表或視圖關聯的磁碟上結構，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列產生的鍵。這些鍵儲存在一個結構B 樹）中，使 SQL Server 可以快速有效地尋找與索引值關聯的行。

通俗點說，索引與表或視圖相關，旨在加快檢索速度。索引本身佔據儲存空間，通過索引，資料便會以B樹形式儲存。因此也加快了查詢速度。

2.1.2叢集索引

叢集索引根據資料行的索引值在表或視圖中排序和儲存這些資料行。索引定義中包含叢集索引列。每個表只能有一個叢集索引，因為資料行本身只能按一個順序排序。只有當表包含叢集索引時，表中的資料行才按排序次序儲存。如果表具有叢集索引，則該表稱為聚集表。如果表沒有叢集索引，則其資料行儲存在一個稱為堆的無序結構中。

通俗點說，叢集索引的頁儲存的是實際資料。每個表只能建立唯一的叢集索引，但也可以沒有。

如果建立叢集索引，那麼表中資料以B樹形式儲存資料。

對於叢集索引的理解，打個比方，即英文字典的單詞編排。 英文字典單詞以A,B,C,D….X,Y,Z的形式順序編排，如果我們尋找 Good 單詞，我們首先定位到G，然後定位o – o-d. 最終尋找到Good，便是good實際存在的地方。

建叢集索引需要至少相當該表120%的附加空間，以存放該表的副本和索引中間頁。

2.1.3非叢集索引

非叢集索引具有獨立於資料行的結構。非叢集索引包含非叢集索引索引值，並且每個索引值項都有指向包含該索引值的資料行的指標。

從非叢集索引中的索引行指向資料行的指標稱為行定位器。行定位器的結構取決於資料頁是儲存在堆中還是聚集表中。對於堆，行定位器是指向行的指標。對於聚集表，行定位器是叢集索引鍵。

通俗點說，非叢集索引的頁儲存的是不是實際資料，而是實際資料的地址。一個表可以存在多個非叢集索引。在Sql Server2005中，每個表最多可以建立249個，而在Sql server2008中，則最多可以建立999個非叢集索引。

對於非叢集索引的理解，即新華字典的“偏旁部首”查字法。遇到您不認識的字，不知道它的發音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據“偏旁部首”查到您要找的字，然後根據這個字後的頁碼直接翻到某頁來找到您要找的字。但您結合“部首目錄”和“檢字表”而查到的字的排序並不是真正的本文的排序方法，比如您查“張”字，我們可以看到在查部首之後的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是“弩”字，頁面是390頁。很顯然，這些字並不是真正的分別位於“張”字的上下方，現在您看到的連續的“馳、張、弩”三字實際上就是他們在非叢集索引中的排序，是字典本文中的字在非叢集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結果，然後再翻到您所需要的頁碼。我們把這種目錄純粹是目錄，本文純粹是本文的排序方式稱為“非叢集索引”。

2.1.4 覆蓋索引：

覆蓋索引是指那些索引項目中包含查尋所需要的全部資訊的非叢集索引，這種索引之所以比較快也正是因為索引頁中包含了查尋所必須的資料,不需去訪問資料頁。 如果非聚簇索引中包含結果資料,那麼它的查詢速度將快於叢集索引。

但是由於覆蓋索引的索引項目比較多,要佔用比較大的空間。而且update 操作會引起索引值改變。所以如果潛在的覆蓋查詢並不常用或不太關鍵，則覆蓋索引的增加反而會降低效能。

2.1.5 主鍵和索引

主鍵：表通常具有包含唯一標識表中每一行的值的一列或一組列。這樣的一列或多列稱為表的主鍵 (PK)，用於強製表的實體完整性。在建立或修改表時，您可以通過定義 PRIMARY KEY 約束來建立主鍵。 它是一種唯一索引。

下面是一個簡單的比較表

	主鍵	叢集索引
用途	強製表的實體完整性	對資料行的排序，方便查詢用
一個表多少個	一個表最多一個主鍵	一個表最多一個叢集索引
是否允許多個欄位來定義	一個主鍵可以多個欄位來定義	一個索引可以多個欄位來定義
是否允許 null 資料行出現	如果要建立的資料列中資料存在null，無法建立主鍵。 建立表時指定的 PRIMARY KEY 約束列隱式轉換為 NOT NULL。	沒有限制建立叢集索引的列一定必須 not null . 也就是可以列的資料是 null 參看最後一項比較
是否要求資料必須唯一	要求資料必須唯一	資料即可以唯一，也可以不唯一。看你定義這個索引的 UNIQUE 設定。 這一點需要看後面的一個比較，雖然你的資料列可能不唯一，但是系統會替你產生一個你看不到的唯一列）
建立的邏輯	資料庫在建立主鍵同時，會自動建立一個唯一索引。 如果這個表之前沒有叢集索引，同時建立主鍵時候沒有強制指定使用非叢集索引，則建立主鍵時候，同時建立一個唯一的叢集索引	如果未使用 UNIQUE 屬性建立叢集索引，資料庫引擎 將向表自動添加一個四位元組 uniqueifier 列。 必要時，資料庫引擎 將向行自動添加一個 uniqueifier 值，使每個鍵唯一。此列和列值供內部使用，使用者不能查看或訪問。

2.2 索引的儲存結構

2.1.1 整表掃描和索引掃描

整表掃描和索引掃描是Sql Server資料庫檢索到資料的唯一的兩種方式。除此之外，沒有第三種方式供Sql Server檢索到資料。

整表掃描

最直接的檢索方式， Sql Server進行表掃描時，會從表頭開始掃描，直到整個表結束。 當找到合格記錄，便把該記錄存在結果集中。對於小資料量的表，這是一種很快捷的方式。如果沒有為表建立索引，那麼Sql server便按這種方式檢索資料。

索引掃描

如果為表建立了索引，在進行檢索前，Sql Server最佳化器會根據查詢條件，從可用的索引中選擇最佳化的索引。檢索時，便會遍曆B樹，當找到合格記錄，便把該記錄存在結果集中。因此，檢索大資料量的表，使用索引相對於整表掃描會顯著地提高效能。

2.1.2 B-Tree

2.2.3 叢集索引

葉子節點存放的是實際的資料。索引的進入點存放在master->sys.indexes中。

2.2.4 非叢集索引

2．4．1 堆上的非叢集索引Non-clustered index on heap）

與叢集索引很類似。

不同處在：

葉子節點存放的不是實際資料，而是指向實際資料的指標。檢索速度非常接近於叢集索引，比起叢集索引，實際上只是多一步由根據指標檢索到實際資料的過程。

2．4．2 聚集表上的非叢集索引

3． 管理索引

3．1 建立

 

  
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]  
  
 
  
INDEX <index name> ON <table or view name>(<column name> [ASC|DESC] [,...n])  
  
INCLUDE (<column name> [, ...n])  
  
[WITH 
  
[PAD_INDEX = { ON | OFF }]  
  
[[,] FILLFACTOR = <fillfactor>]  
  
[[,] IGNORE_DUP_KEY = { ON | OFF }]  
  
[[,] DROP_EXISTING = { ON | OFF }]  
  
[[,] STATISTICS_NORECOMPUTE = { ON | OFF }]  
  
[[,] SORT_IN_TEMPDB = { ON | OFF }]  
  
[[,] ONLINE = { ON | OFF }  
  
[[,] ALLOW_ROW_LOCKS = { ON | OFF }  
  
[[,] ALLOW_PAGE_LOCKS = { ON | OFF }  
  
[[,] MAXDOP = <maximum degree of parallelism>  
  
]  
  
[ON {<filegroup> | <partition scheme name> | DEFAULT }] 
 

3．2 修改

 

  
ALTER INDEX { <name of index> | ALL }  
  
ON <table or view name>  
  
{ REBUILD  
  
[ [ WITH (  
  
[ PAD_INDEX = { ON | OFF } ]  
  
| [[,] FILLFACTOR = <fillfactor>  
  
| [[,] SORT_IN_TEMPDB = { ON | OFF } ]  
  
| [[,] IGNORE_DUP_KEY = { ON | OFF } ]  
  
| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]  
  
| [[,] ONLINE = { ON | OFF } ]  
  
| [[,] ALLOW_ROW_LOCKS = { ON | OFF } ]  
  
| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]  
  
| [[,] MAXDOP = <max degree of parallelism>  
  
) ]  
  
| [ PARTITION = <partition number>  
  
[ WITH ( <partition rebuild index option>  
  
[ ,...n ] ) ] ] ]  
  
| DISABLE  
  
| REORGANIZE  
  
[ PARTITION = <partition number> ]  
  
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]  
  
| SET ([ ALLOW_ROW_LOCKS= { ON | OFF } ]  
  
| [[,] ALLOW_PAGE_LOCKS = { ON | OFF } ]  
  
| [[,] IGNORE_DUP_KEY = { ON | OFF } ]  
  
| [[,] STATISTICS_NORECOMPUTE = { ON | OFF } ]  
  
)  
  
} [ ; ] 
 

3．3 刪除

 

  
DROP INDEX <table name>.<index name> 
 

4． 使用索引應注意十麼

1）叢集索引通常速度優於非叢集索引

2) 建索引時應考慮是否有足夠的空間。索引佔據空間,平均約1.2倍資料庫本身大小。

3) 在經常用於查詢或彙總條件的欄位上建立叢集索引。這類查詢條件包括 between, >, <,group by, max,min, count等。

4) 不要在經常作為插入，且插入欄位無序的列上建立叢集索引。 插入資料行會涉及分頁，rebuild索引會消耗大量時間。參考文末"一個不恰當使用叢集索引的例子"。   

5) 在值高度的唯一性欄位上建立索引。不能在諸如性別的欄位上建立索引。

6) 只有作為索引的第一個列包含在查詢條件中，該索引才的作用。

打個比方，我們用偏旁+部首來查漢字，那麼偏旁首先必須包括在查詢條件中，只有先定位偏旁，再結合部首，才能發揮偏旁+部首來檢索的快速功效。

7) 刪除一直不用的索引。特別是對於刪除和修改比較頻繁的資料表，必須考慮如何精華索引。

原文標題：漫談資料庫之索引

連結：http://www.cnblogs.com/Ring1981/archive/2010/09/15/1826997.html