轉(zhuǎn)自于
http://www.cppblog.com/zuoyinbo/archive/2008/06/03/52075.html
1.概念
索引是在數(shù)據(jù)庫(kù)表或者視圖上創(chuàng)建的對(duì)象，目的是為了加快對(duì)表或視圖的查詢的速度（簡(jiǎn)單理解）。
索引是一個(gè)單獨(dú)的、物理的數(shù)據(jù)庫(kù)結(jié)構(gòu)，它是某個(gè)表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識(shí)這些值的數(shù)據(jù)頁(yè)的邏輯指針清單（深刻理解）。
按照存儲(chǔ)方式分為：聚集與非聚集索引（需要重視和區(qū)別的概念，后面詳解）
按照維護(hù)與管理索引角度分為：唯一索引、復(fù)合索引和系統(tǒng)自動(dòng)創(chuàng)建的索引（相對(duì)簡(jiǎn)單，如下解釋：）
1).唯一索引：惟一索引可以確保索引列不包含重復(fù)的值.
可以用多個(gè)列，但是索引可以確保索引列中每個(gè)值組合都是唯一的，
即下面的姓不能有重復(fù)，同時(shí)名也不能有重復(fù)：
姓 名
李 二
張 三
王 五
語(yǔ)法： create unique index idxempid on emp(姓,名)
2).復(fù)合索引：如果在兩上以上的列上創(chuàng)建一個(gè)索引，則稱為復(fù)合索引。
那么，不可能有兩行的姓和名是重復(fù)的，即上面的表沒(méi)有兩行其姓和名的組合是一樣的。
語(yǔ)法： create index indxfullname on emp(姓,名)
3).系統(tǒng)自建的索引：在使用T_sql語(yǔ)句創(chuàng)建表的時(shí)候使用PRIMARY KEY或UNIQUE約束時(shí)，會(huì)在表上
自動(dòng)創(chuàng)建一個(gè)惟一索引，自動(dòng)創(chuàng)建的索引是無(wú)法刪除的。
語(yǔ)法：
create table ABC
( empID int PRIMARY KEY,
firstname varchar(50) UNIQUE,
lastname varchar(50) UNIQUE,
) /*這樣的結(jié)果就出來(lái)了三個(gè)索引，但只有一個(gè)聚集索引empID*/
索引的結(jié)構(gòu)是由：根節(jié)點(diǎn)--->非葉節(jié)點(diǎn)--->非葉節(jié)點(diǎn)--->葉節(jié)點(diǎn)（注意索引在數(shù)據(jù)庫(kù)引擎中所用的
內(nèi)部數(shù)據(jù)結(jié)構(gòu)一般是B+樹(shù)，參考后文）
聚集索引和非聚集索引——
用一個(gè)現(xiàn)實(shí)中的例子說(shuō)明以助理解。我們的漢語(yǔ)字典的正文本身就是一個(gè)聚集索引。比如，我們要查“安”字，就會(huì)很自然地翻開(kāi)字典的前幾頁(yè)，因?yàn)?#8220;安”的拼音是“an”，而按照拼音排序漢字的字典是以英文字母“a”開(kāi)頭并以“z”結(jié)尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”開(kāi)頭的部分仍然找不到這個(gè)字，那么就說(shuō)明您的字典中沒(méi)有這個(gè)字；同樣的，如果查“張”字，那您也會(huì)將您的字典翻到最后部分，因?yàn)?#8220;張”的拼音是“zhang”。也就是說(shuō)，字典的正文部分本身就是一個(gè)目錄，您不需要再去查其他目錄來(lái)找到您需要找的內(nèi)容。
我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為“聚集索引”。
如果您認(rèn)識(shí)某個(gè)字，您可以快速地從自動(dòng)中查到這個(gè)字。但您也可能會(huì)遇到您不認(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è)碼。
我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。
2.性質(zhì)及使用方法
1)聚集索引：表中存儲(chǔ)的數(shù)據(jù)按照索引的順序存儲(chǔ)，檢索效率比普通索引高，索引占用硬盤(pán)
存儲(chǔ)空間小（1%左右），但對(duì)數(shù)據(jù)新增/修改/刪除的速度影響比較大（降低）。
特點(diǎn)：
(1) 無(wú)索引，數(shù)據(jù)無(wú)序
(2) 有索引，數(shù)據(jù)與索引同序
(3) 數(shù)據(jù)會(huì)根據(jù)索引鍵的順序重新排列數(shù)據(jù)
(4) 一個(gè)表只能有一個(gè)索引
(5) 葉節(jié)點(diǎn)的指針指向的數(shù)據(jù)也在同一位置存儲(chǔ)
語(yǔ)法：create CLUSTERED INDEX idxempID ON emp(empID)
2)非聚集索引：不影響表中的數(shù)據(jù)存儲(chǔ)順序，檢索效率比聚集索引低，索引占用硬盤(pán)存儲(chǔ)
空間大（30%~40%），對(duì)數(shù)據(jù)新增/修改/刪除的影響很少。
特點(diǎn)：
(1) 一個(gè)表可以最多可以創(chuàng)建249個(gè)非聚集索引
(2) 先建聚集索引才能創(chuàng)建非聚集索引
(3) 非聚集索引數(shù)據(jù)與索引不同序
(4) 數(shù)據(jù)與非聚集索引在不同位置
(5) 非聚集索引在葉節(jié)點(diǎn)上存儲(chǔ)，在葉節(jié)點(diǎn)上有一個(gè)“指針”直接指向要查詢的數(shù)據(jù)區(qū)域
(6) 數(shù)據(jù)不會(huì)根據(jù)非聚集索引鍵的順序重新排列數(shù)據(jù)
語(yǔ)法：create NONCLUSTERED INDEX idximpID ON emp(empID)
創(chuàng)建索引的方法：
1)企業(yè)管理器中
(1)右擊某個(gè)表，所有任務(wù)---管理索引，打開(kāi)管理索引，單擊“新建”就可以創(chuàng)建索引
(2)在設(shè)計(jì)表中進(jìn)行設(shè)計(jì)表，管理索引/鍵
(3)在關(guān)系圖中，添加表后右擊關(guān)系圖中的某個(gè)表，就有“索引/鍵”
(4)通過(guò)向?qū)?數(shù)據(jù)庫(kù)---創(chuàng)建索引向?qū)?br />
(5)通過(guò)T-SQL語(yǔ)句
2)能過(guò)“索引優(yōu)化向?qū)?#8221;來(lái)優(yōu)化索引的向?qū)Вㄟ^(guò)它可以決定選擇哪些列做為索引列
何時(shí)應(yīng)使用聚集索引或非聚集索引
|
動(dòng)作描述
|
使用聚集索引
|
使用非聚集索引
|
|
列經(jīng)常被分組排序
|
應(yīng)
|
應(yīng)
|
|
返回某范圍內(nèi)的數(shù)據(jù)
|
應(yīng)
|
不應(yīng)
|
|
一個(gè)或極少不同值
|
不應(yīng)
|
不應(yīng)
|
|
小數(shù)目的不同值
|
應(yīng)
|
不應(yīng)
|
|
大數(shù)目的不同值
|
不應(yīng)
|
應(yīng)
|
|
頻繁更新的列
|
不應(yīng)
|
應(yīng)
|
|
外鍵列
|
應(yīng)
|
應(yīng)
|
|
主鍵列
|
應(yīng)
|
應(yīng)
|
|
頻繁修改索引列
|
不應(yīng)
|
應(yīng)
|
3.數(shù)據(jù)庫(kù)引擎中索引的內(nèi)部結(jié)構(gòu)
有必要先說(shuō)明一下數(shù)據(jù)庫(kù)引擎，
這部分是較深的內(nèi)容，需要有一定的數(shù)據(jù)庫(kù)理論知識(shí)和數(shù)據(jù)結(jié)構(gòu)與算法知識(shí)，數(shù)據(jù)結(jié)構(gòu)和算法告訴我們，對(duì)索引關(guān)鍵字進(jìn)行快速查找時(shí)要使用樹(shù)形數(shù)據(jù)結(jié)構(gòu)，在數(shù)據(jù)庫(kù)引擎中，索引通常用B+樹(shù)來(lái)表示，google發(fā)現(xiàn)這方面的文章較少，后面找到相關(guān)詳細(xì)資料會(huì)補(bǔ)充。
4.
主鍵、索引、聚集索引和非聚集索引
1)主鍵 (PK)
唯一標(biāo)識(shí)表中的所有行的一個(gè)列或一組列。主鍵不允許空值。不能存在具有相同的主鍵值的兩個(gè)
行，因此主鍵值總是唯一標(biāo)識(shí)單個(gè)行。表中可以有不止一個(gè)鍵唯一標(biāo)識(shí)行，每個(gè)鍵都稱作候選鍵。只有
一個(gè)候選鍵可以選作表的主鍵，所有其它候選鍵稱作備用鍵。盡管表不要求具有主鍵，但定義主鍵是很
好的做法。 在規(guī)范化的表中，每行中的所有數(shù)據(jù)值都完全依賴于主鍵。例如，在以 EmployeeID 作為
主鍵的規(guī)范化的 employee 表中，所有列都應(yīng)包含與某個(gè)特定職員相關(guān)的數(shù)據(jù)。該表不具有
DepartmentName 列，因?yàn)椴块T(mén)的名稱依賴于部門(mén) ID，而不是職員 ID。
2)索引
關(guān)系數(shù)據(jù)庫(kù)中基于鍵值提供對(duì)表的行中數(shù)據(jù)的快速訪問(wèn)的數(shù)據(jù)庫(kù)對(duì)象。索引還可以在表的行上強(qiáng)制唯
一性。SQL Server 支持聚集索引和非聚集索引。對(duì)表的主鍵自動(dòng)進(jìn)行索引。在全文搜索中，全文索引
存儲(chǔ)關(guān)于重要詞和這些詞在給定列中的位置的信息。
如果某列有多行包含 NULL 值，則不能在該列上創(chuàng)建唯一索引。同樣，如果列的組合中有多行包
含 NULL 值，則不能在多個(gè)列上創(chuàng)建唯一索引。在創(chuàng)建索引時(shí)，這些被視為重復(fù)的值。
3)聚集索引
在創(chuàng)建聚集索引時(shí)，將會(huì)對(duì)表進(jìn)行復(fù)制，對(duì)表中的數(shù)據(jù)進(jìn)行排序，然后刪除原始的表。因此，數(shù)據(jù)庫(kù)
上必須有足夠的空閑空間，以容納數(shù)據(jù)復(fù)本。默認(rèn)情況下，表中的數(shù)據(jù)在創(chuàng)建索引時(shí)排序。但是，如果
因聚集索引已經(jīng)存在，且正在使用同一名稱和列重新創(chuàng)建，而數(shù)據(jù)已經(jīng)排序，則會(huì)重建索引，而不是從
頭創(chuàng)建該索引，以自動(dòng)跳過(guò)排序操作。重建操作會(huì)檢查行是否在生成索引時(shí)進(jìn)行了排序。如果有任何行
排序不正確，即會(huì)取消操作，不創(chuàng)建索引。
4)非聚集索引
非聚集索引與課本中的索引類似。數(shù)據(jù)存儲(chǔ)在一個(gè)地方，索引存儲(chǔ)在另一個(gè)地方，索引帶有指針指向
數(shù)據(jù)的存儲(chǔ)位置。索引中的項(xiàng)目按索引鍵值的順序存儲(chǔ)，而表中的信息按另一種順序存儲(chǔ)（這可以由聚
集索引規(guī)定）。如果在表中未創(chuàng)建聚集索引，則無(wú)法保證這些行具有任何特定的順序。
打開(kāi)設(shè)計(jì)表界面里面有個(gè)鑰匙就是主鍵的意思，當(dāng)你聲明一列為主鍵的時(shí)候數(shù)據(jù)庫(kù)實(shí)際上就是生成一個(gè)
唯一的索引，查詢優(yōu)化器實(shí)際上是根據(jù)列上有沒(méi)有唯一索引來(lái)保證列的唯一性而不是根據(jù)列是否被聲明為主鍵。
聚集索引一個(gè)表只有一個(gè)，實(shí)際上它的葉子節(jié)點(diǎn)就是數(shù)據(jù)頁(yè)，比非聚集索引速度快，占用的空間小，大概只有表的1％左右。如果在聲明的時(shí)候沒(méi)有選擇UNIQUE選項(xiàng)，則在插入數(shù)據(jù)的時(shí)候會(huì)自動(dòng)生成一個(gè)唯一標(biāo)示符。
非聚集索引一個(gè)表可以有多個(gè)，一個(gè)3層的非聚簇索引要查詢6次才可以找到真實(shí)數(shù)據(jù)，因?yàn)槠淙~子節(jié)點(diǎn)并不是真實(shí)數(shù)據(jù)，而是標(biāo)識(shí)（如果表上有聚集索引則為聚集索引，如沒(méi)有，則為實(shí)際數(shù)據(jù)的頁(yè)號(hào)），非聚集索引通常占用空間比較大，表的30－40％。