1．?dāng)?shù)據(jù)庫(kù)的數(shù)據(jù)存儲(chǔ)

1.1文件：

我們一旦創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，都會(huì)生成兩個(gè)文件：

DataBaseName.mdf: 主文件，這是數(shù)據(jù)庫(kù)中的數(shù)據(jù)最終存放的地方。

DataBaseName.ldf:日志文件，由數(shù)據(jù)操作產(chǎn)生的一系列日志記錄。

1.2分區(qū)：

在一個(gè)給定的文件中，為表和索引分配空間的基本存儲(chǔ)單位。 1個(gè)區(qū)占64KB,由8個(gè)連續(xù)的頁(yè)組成。 如果一個(gè)分區(qū)已滿(mǎn)，但需存一條新的記錄，那么該記錄將占用整個(gè)新分區(qū)的空間。

1.3 頁(yè)：

分區(qū)中的一個(gè)分配單位。這是實(shí)際數(shù)據(jù)行最終存放的地方。 頁(yè)用于存儲(chǔ)數(shù)據(jù)行。

Sql Server有多種類(lèi)型的頁(yè)：

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

51CTO數(shù)據(jù)庫(kù)頻道向您推薦：MySQL索引:數(shù)據(jù)庫(kù)性能的雙刃劍

2． 索引

2.1.1索引

索引是與表或視圖關(guān)聯(lián)的磁盤(pán)上結(jié)構(gòu)，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵。這些鍵存儲(chǔ)在一個(gè)結(jié)構(gòu)（B 樹(shù)）中，使 SQL Server 可以快速有效地查找與鍵值關(guān)聯(lián)的行。

通俗點(diǎn)說(shuō)，索引與表或視圖相關(guān)，旨在加快檢索速度。索引本身占據(jù)存儲(chǔ)空間，通過(guò)索引，數(shù)據(jù)便會(huì)以B樹(shù)形式存儲(chǔ)。因此也加快了查詢(xún)速度。

2.1.2聚集索引

聚集索引根據(jù)數(shù)據(jù)行的鍵值在表或視圖中排序和存儲(chǔ)這些數(shù)據(jù)行。索引定義中包含聚集索引列。每個(gè)表只能有一個(gè)聚集索引，因?yàn)閿?shù)據(jù)行本身只能按一個(gè)順序排序。只有當(dāng)表包含聚集索引時(shí)，表中的數(shù)據(jù)行才按排序順序存儲(chǔ)。如果表具有聚集索引，則該表稱(chēng)為聚集表。如果表沒(méi)有聚集索引，則其數(shù)據(jù)行存儲(chǔ)在一個(gè)稱(chēng)為堆的無(wú)序結(jié)構(gòu)中。

通俗點(diǎn)說(shuō)，聚集索引的頁(yè)存儲(chǔ)的是實(shí)際數(shù)據(jù)。每個(gè)表只能建立唯一的聚集索引，但也可以沒(méi)有。

如果建立聚集索引，那么表中數(shù)據(jù)以B樹(shù)形式存儲(chǔ)數(shù)據(jù)。

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

建聚集索引需要至少相當(dāng)該表120%的附加空間，以存放該表的副本和索引中間頁(yè)。

2.1.3非聚集索引

非聚集索引具有獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引包含非聚集索引鍵值，并且每個(gè)鍵值項(xiàng)都有指向包含該鍵值的數(shù)據(jù)行的指針。

從非聚集索引中的索引行指向數(shù)據(jù)行的指針?lè)Q為行定位器。行定位器的結(jié)構(gòu)取決于數(shù)據(jù)頁(yè)是存儲(chǔ)在堆中還是聚集表中。對(duì)于堆，行定位器是指向行的指針。對(duì)于聚集表，行定位器是聚集索引鍵。

通俗點(diǎn)說(shuō)，非聚集索引的頁(yè)存儲(chǔ)的是不是實(shí)際數(shù)據(jù)，而是實(shí)際數(shù)據(jù)的地址。一個(gè)表可以存在多個(gè)非聚集索引。在Sql Server2005中，每個(gè)表最多可以建立249個(gè)，而在Sql server2008中，則最多可以建立999個(gè)非聚集索引。

對(duì)于非聚集索引的理解，即新華字典的“偏旁部首”查字法。遇到您不認(rèn)識(shí)的字，不知道它的發(fā)音，這時(shí)候，您就不能按照剛才的方法找到您要查的字，而需要去根據(jù)“偏旁部首”查到您要找的字，然后根據(jù)這個(gè)字后的頁(yè)碼直接翻到某頁(yè)來(lái)找到您要找的字。但您結(jié)合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁(yè)碼是672頁(yè)，檢字表中“張”的上面是“馳”字，但頁(yè)碼卻是63頁(yè)，“張”的下面是“弩”字，頁(yè)面是390頁(yè)。很顯然，這些字并不是真正的分別位于“張”字的上下方，現(xiàn)在您看到的連續(xù)的“馳、張、弩”三字實(shí)際上就是他們?cè)诜蔷奂饕械呐判颍亲值湔闹械淖衷诜蔷奂饕械挠成?。我們可以通過(guò)這種方式來(lái)找到您所需要的字，但它需要兩個(gè)過(guò)程，先找到目錄中的結(jié)果，然后再翻到您所需要的頁(yè)碼。我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱(chēng)為“非聚集索引”。

2.1.4 覆蓋索引：

覆蓋索引是指那些索引項(xiàng)中包含查尋所需要的全部信息的非聚集索引，這種索引之所以比較快也正是因?yàn)樗饕?yè)中包含了查尋所必須的數(shù)據(jù),不需去訪(fǎng)問(wèn)數(shù)據(jù)頁(yè)。 如果非聚簇索引中包含結(jié)果數(shù)據(jù),那么它的查詢(xún)速度將快于聚集索引。

但是由于覆蓋索引的索引項(xiàng)比較多,要占用比較大的空間。而且update 操作會(huì)引起索引值改變。所以如果潛在的覆蓋查詢(xún)并不常用或不太關(guān)鍵，則覆蓋索引的增加反而會(huì)降低性能。

2.1.5 主鍵和索引

主鍵：表通常具有包含唯一標(biāo)識(shí)表中每一行的值的一列或一組列。這樣的一列或多列稱(chēng)為表的主鍵 (PK)，用于強(qiáng)制表的實(shí)體完整性。在創(chuàng)建或修改表時(shí)，您可以通過(guò)定義 PRIMARY KEY 約束來(lái)創(chuàng)建主鍵。 它是一種唯一索引。

下面是一個(gè)簡(jiǎn)單的比較表

2.2 索引的存儲(chǔ)結(jié)構(gòu)

2.1.1 整表掃描和索引掃描

整表掃描和索引掃描是Sql Server數(shù)據(jù)庫(kù)檢索到數(shù)據(jù)的唯一的兩種方式。除此之外，沒(méi)有第三種方式供Sql Server檢索到數(shù)據(jù)。

整表掃描

最直接的檢索方式， Sql Server進(jìn)行表掃描時(shí)，會(huì)從表頭開(kāi)始掃描，直到整個(gè)表結(jié)束。 當(dāng)找到符合條件的記錄，便把該記錄存在結(jié)果集中。對(duì)于小數(shù)據(jù)量的表，這是一種很快捷的方式。如果沒(méi)有為表創(chuàng)建索引，那么Sql server便按這種方式檢索數(shù)據(jù)。

索引掃描

如果為表創(chuàng)建了索引，在進(jìn)行檢索前，Sql Server優(yōu)化器會(huì)根據(jù)查詢(xún)條件，從可用的索引中選擇***化的索引。檢索時(shí)，便會(huì)遍歷B樹(shù)，當(dāng)找到符合條件的記錄，便把該記錄存在結(jié)果集中。因此，檢索大數(shù)據(jù)量的表，使用索引相對(duì)于整表掃描會(huì)顯著地提高性能。

2.1.2 B-Tree

2.2.3 聚集索引

   葉子節(jié)點(diǎn)存放的是實(shí)際的數(shù)據(jù)。索引的入口點(diǎn)存放在master->sys.indexes中。

2.2.4 非聚集索引

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

與聚集索引很類(lèi)似。

不同處在：

葉子節(jié)點(diǎn)存放的不是實(shí)際數(shù)據(jù)，而是指向?qū)嶋H數(shù)據(jù)的指針。檢索速度非常接近于聚集索引，比起聚集索引，實(shí)際上只是多一步由根據(jù)指針檢索到實(shí)際數(shù)據(jù)的過(guò)程。

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

3． 管理索引

3．1 創(chuàng)建

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． 使用索引應(yīng)注意十么

1）聚集索引通常速度優(yōu)于非聚集索引

2) 建索引時(shí)應(yīng)考慮是否有足夠的空間。索引占據(jù)空間,平均約1.2倍數(shù)據(jù)庫(kù)本身大小。

3) 在經(jīng)常用于查詢(xún)或聚合條件的字段上建立聚集索引。這類(lèi)查詢(xún)條件包括 between, >, <,group by, max,min, count等。

4) 不要在經(jīng)常作為插入，且插入字段無(wú)序的列上建立聚集索引。 插入數(shù)據(jù)行會(huì)涉及分頁(yè)，rebuild索引會(huì)消耗大量時(shí)間。參考文末"一個(gè)不恰當(dāng)使用聚集索引的例子"。   

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

6) 只有作為索引的***個(gè)列包含在查詢(xún)條件中，該索引才的作用。

打個(gè)比方，我們用偏旁+部首來(lái)查漢字，那么偏旁首先必須包括在查詢(xún)條件中，只有先定位偏旁，再結(jié)合部首，才能發(fā)揮偏旁+部首來(lái)檢索的快速功效。

7) 刪除一直不用的索引。特別是對(duì)于刪除和修改比較頻繁的數(shù)據(jù)表，必須考慮如何精華索引。

原文標(biāo)題：漫談數(shù)據(jù)庫(kù)之索引

鏈接：http://www.cnblogs.com/Ring1981/archive/2010/09/15/1826997.html

【編輯推薦】

1. MySQL的數(shù)據(jù)類(lèi)型和建庫(kù)策略詳解
2. MySQL索引分類(lèi)和各自用途
3. MySQL性能優(yōu)化實(shí)戰(zhàn)