索引的概念
索引的用途：我們對(duì)數(shù)據(jù)查詢及處理速度已成為衡量應(yīng)用系統(tǒng)成敗的標(biāo)準(zhǔn)，而采用索引來加快數(shù)據(jù)處理速度通常是最普遍采用的優(yōu)化方法。
索引是什么：數(shù)據(jù)庫(kù)中的索引類似于一本書的目錄，在一本書中使用目錄可以快速找到你想要的信息，而不需要讀完全書。在數(shù)據(jù)庫(kù)中，數(shù)據(jù)庫(kù)程序使用索引可以重啊到表中的數(shù)據(jù)，而不必掃描整個(gè)表。書中的目錄是一個(gè)字詞以及各字詞所在的頁(yè)碼列表，數(shù)據(jù)庫(kù)中的索引是表中的值以及各值存儲(chǔ)位置的列表。
索引的利弊：查詢執(zhí)行的大部分開銷是I/O，使用索引提高性能的一個(gè)主要目標(biāo)是避免全表掃描，因?yàn)槿頀呙栊枰獜拇疟P上讀取表的每一個(gè)數(shù)據(jù)頁(yè)，如果有索引指向數(shù)據(jù)值，則查詢只需要讀少數(shù)次的磁盤就行啦。所以合理的使用索引能加速數(shù)據(jù)的查詢。但是索引并不總是提高系統(tǒng)的性能，帶索引的表需要在數(shù)據(jù)庫(kù)中占用更多的存儲(chǔ)空間，同樣用來增刪數(shù)據(jù)的命令運(yùn)行時(shí)間以及維護(hù)索引所需的處理時(shí)間會(huì)更長(zhǎng)。所以我們要合理使用索引，及時(shí)更新去除次優(yōu)索引。
數(shù)據(jù)表的基本結(jié)構(gòu)
一個(gè)新表被創(chuàng)建之時(shí)，系統(tǒng)將在磁盤中分配一段以8K為單位的連續(xù)空間，當(dāng)字段的值從內(nèi)存寫入磁盤時(shí)，就在這一既定空間隨機(jī)保存，當(dāng)一個(gè) 8K用完的時(shí)候，數(shù)據(jù)庫(kù)指針會(huì)自動(dòng)分配一個(gè)8K的空間。這里，每個(gè)8K空間被稱為一個(gè)數(shù)據(jù)頁(yè)（Page），又名頁(yè)面或數(shù)據(jù)頁(yè)面，并分配從0-7的頁(yè)號(hào), 每個(gè)文件的第0頁(yè)記錄引導(dǎo)信息，叫文件頭（File header）；每8個(gè)數(shù)據(jù)頁(yè)（64Ｋ）的組合形成擴(kuò)展區(qū)（Extent），稱為擴(kuò)展。全部數(shù)據(jù)頁(yè)的組合形成堆（Heap）。
SQLS規(guī)定行不能跨越數(shù)據(jù)頁(yè)，所以，每行記錄的最大數(shù)據(jù)量只能為8K。這就是char和varchar這兩種字符串類型容量要限制在8K以內(nèi)的 原因，存儲(chǔ)超過8K的數(shù)據(jù)應(yīng)使用text類型，實(shí)際上，text類型的字段值不能直接錄入和保存，它只是存儲(chǔ)一個(gè)指針，指向由若干8K的文本數(shù)據(jù)頁(yè)所組成 的擴(kuò)展區(qū)，真正的數(shù)據(jù)正是放在這些數(shù)據(jù)頁(yè)中。
頁(yè)面有空間頁(yè)面和數(shù)據(jù)頁(yè)面之分。
當(dāng)一個(gè)擴(kuò)展區(qū)的8個(gè)數(shù)據(jù)頁(yè)中既包含了空間頁(yè)面又包括了數(shù)據(jù)或索引頁(yè)面時(shí)，稱為混合擴(kuò)展（Mixed Extent）,每張表都以混合擴(kuò)展開始；反之，稱為一致擴(kuò)展（Uniform Extent），專門保存數(shù)據(jù)及索引信息。
表被創(chuàng)建之時(shí)，SQLS在混合擴(kuò)展中為其分配至少一個(gè)數(shù)據(jù)頁(yè)面，隨著數(shù)據(jù)量的增長(zhǎng)，SQLS可即時(shí)在混合擴(kuò)展中分配出7個(gè)頁(yè)面，當(dāng)數(shù)據(jù)超過8個(gè)頁(yè)面時(shí)，則從一致擴(kuò)展中分配數(shù)據(jù)頁(yè)面。
空間頁(yè)面專門負(fù)責(zé)數(shù)據(jù)空間的分配和管理，包括：PFS頁(yè)面（Page free space）：記錄一個(gè)頁(yè)面是否已分配、位于混合擴(kuò)展還是一致擴(kuò)展以及頁(yè)面上還有多少可用空間等信息；GAM頁(yè)面（Global allocation map）和SGAM頁(yè)面(Secodary global allocation map)：用來記錄空閑的擴(kuò)展或含有空閑頁(yè)面的混合擴(kuò)展的位置。SQLS綜合利用這三種類型的頁(yè)面文件在必要時(shí)為數(shù)據(jù)表創(chuàng)建新空間；
數(shù)據(jù)頁(yè)或索引頁(yè)則專門保存數(shù)據(jù)及索引信息，SQLS使用4種類型的數(shù)據(jù)頁(yè)面來管理表或索引：它們是IAM頁(yè)、數(shù)據(jù)頁(yè)、文本/圖像頁(yè)和索引頁(yè)。
在WINDOWS中，我們對(duì)文件執(zhí)行的每一步操作，在磁盤上的物理位置只有系統(tǒng)（system）才知道；SQL SERVER沿襲了這種工作方式，在插入數(shù)據(jù)的過程中，不但每個(gè)字段值在數(shù)據(jù)頁(yè)面中的保存位置是隨機(jī)的，而且每個(gè)數(shù)據(jù)頁(yè)面在“堆”中的排列位置也只有系統(tǒng) （system）才知道。
這是為什么呢？眾所周知，OS之所以能管理DISK，是因?yàn)樵谙到y(tǒng)啟動(dòng)時(shí)首先加載了文件分配表：FAT（File Allocation Table），正是由它管理文件系統(tǒng)并記錄對(duì)文件的一切操作，系統(tǒng)才得以正常運(yùn)行；同理，作為管理系統(tǒng)級(jí)的SQL SERVER,也有這樣一張類似FAT的表存在，它就是索引分布映像頁(yè)：IAM（Index Allocation Map）。
IAM的存在，使SQLS對(duì)數(shù)據(jù)表的物理管理有了可能。
IAM頁(yè)從混合擴(kuò)展中分配，記錄了8個(gè)初始頁(yè)面的位置和該擴(kuò)展區(qū)的位置，每個(gè)IAM頁(yè)面能管理512,000個(gè)數(shù)據(jù)頁(yè)面，如果數(shù)據(jù)量太 大，SQLS也可以增加更多的IAM頁(yè)，可以位于文件的任何位置。第一個(gè)IAM頁(yè)被稱為FirstIAM,其中記錄了以后的IAM頁(yè)的位置。
數(shù)據(jù)頁(yè)和文本/圖像頁(yè)互反，前者保存非文本/圖像類型的數(shù)據(jù)，因?yàn)樗鼈兌疾怀^8K的容量，后者則只保存超過8K容量的文本或圖像類型數(shù)據(jù)。而索 引頁(yè)顧名思義，保存的是與索引結(jié)構(gòu)相關(guān)的數(shù)據(jù)信息。了解頁(yè)面的問題有助我們下一步準(zhǔn)確理解SQLS維護(hù)索引的方式，如頁(yè)拆分、填充因子等。
頁(yè)分裂
一半的數(shù)據(jù)將保留在老頁(yè)面，而另一半將放入新頁(yè)面，并且新頁(yè)面可能被分配到任何可用的頁(yè)。所以，頻繁頁(yè)分裂，后果很嚴(yán)重，將使物理表產(chǎn)生大量數(shù)據(jù)碎片，導(dǎo)致直接造成I/O效率的急劇下降，最后，停止SQLS的運(yùn)行并重建索引將是我們的唯一選擇!
填充因子
索引的一個(gè)特性，定義該索引每頁(yè)上的可用空間量。FILLFACTOR（填充因子）適應(yīng)以后表數(shù)據(jù)的擴(kuò)展并減小了頁(yè)拆分的可能性。填充因子是從0到100的百分比數(shù)值，設(shè)為100時(shí)表示將數(shù)據(jù)頁(yè)填滿。只有當(dāng)不會(huì)對(duì)數(shù)據(jù)進(jìn)行更改時(shí)(例如 只讀表中)才用此設(shè)置。值越小則數(shù)據(jù)頁(yè)上的空閑空間越大，這樣可以減少在索引增長(zhǎng)過程中進(jìn)行頁(yè)分裂的需要，但這一操作需要占用更多的硬盤空間。填充因子指定不當(dāng)，會(huì)降低數(shù)據(jù)庫(kù)的讀取性能，其降低量與填充因子設(shè)置值成反比。
索引的分類
SQL SERVER中有多種索引類型。
按存儲(chǔ)結(jié)構(gòu)區(qū)分：“聚集索引（又稱聚類索引，簇集索引）”，“分聚集索引（非聚類索引，非簇集索引）”
按數(shù)據(jù)唯一性區(qū)分：“唯一索引”，“非唯一索引”
按鍵列個(gè)數(shù)區(qū)分：“單列索引”，“多列索引”。
聚集索引
聚集索引是一種對(duì)磁盤上實(shí)際數(shù)據(jù)重新組織以按指定的一列或多列值排序。像我們用到的漢語(yǔ)字典，就是一個(gè)聚集索引，比如要查“張”，我們自然而然就翻到字典的后面百十頁(yè)。然后根據(jù)字母順序跟查找出來。這里用到微軟的平衡二叉樹算法，即首先把書翻到大概二分之一的位置，如果要找的頁(yè)碼比該頁(yè)的頁(yè)碼小，就把書向前翻到四分之一處，否則，就把書向后翻到四分之三的地方，依此類推，把書頁(yè)續(xù)分成更小的部分，直至正確的頁(yè)碼。
由于聚集索引是給數(shù)據(jù)排序，不可能有多種排法，所以一個(gè)表只能建立一個(gè)聚集索引?？茖W(xué)統(tǒng)計(jì)建立這樣的索引需要至少相當(dāng)與該表120%的附加空間，用來存放該表的副本和索引中間頁(yè)，但是他的性能幾乎總是比其它索引要快。
由于在聚集索引下，數(shù)據(jù)在物理上是按序排列在數(shù)據(jù)頁(yè)上的，重復(fù)值也排在一起，因而包含范圍檢查（bentween,<,><=,>=）或使用group by 或order by的查詢時(shí)，一旦找到第一個(gè)鍵值的行，后面都將是連在一起，不必在進(jìn)一步的搜索，避免啦大范圍的掃描，可以大大提高查詢速度。
非聚集索引
sqlserver默認(rèn)情況下建立的索引是非聚集索引，他不重新組織表中的數(shù)據(jù)，而是對(duì)每一行存儲(chǔ)索引列值并用一個(gè)指針指向數(shù)據(jù)所在的頁(yè)面。他像漢語(yǔ)字典中的根據(jù)‘偏旁部首’查找要找的字，即便對(duì)數(shù)據(jù)不排序，然而他擁有的目錄更像是目錄，對(duì)查取數(shù)據(jù)的效率也是具有的提升空間，而不需要全表掃描。
一個(gè)表可以擁有多個(gè)非聚集索引，每個(gè)非聚集索引根據(jù)索引列的不同提供不同的排序順序。
創(chuàng)建索引
語(yǔ)法
復(fù)制代碼
CREATE [UNIQUE] [CLUSTERED| NonCLUSTERED ]
INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]
[[,]IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]
[[,]SORT_IN_TEMPDB]
]
[ ON filegroup ]
復(fù)制代碼
CREATE INDEX命令創(chuàng)建索引各參數(shù)說明如下：
UNIQUE：用于指定為表或視圖創(chuàng)建唯一索引，即不允許存在索引值相同的兩行。
CLUSTERED：用于指定創(chuàng)建的索引為聚集索引。
NONCLUSTERED：用于指定創(chuàng)建的索引為非聚集索引。
index_name：用于指定所創(chuàng)建的索引的名稱。
table：用于指定創(chuàng)建索引的表的名稱。
view：用于指定創(chuàng)建索引的視圖的名稱。
ASC|DESC：用于指定具體某個(gè)索引列的升序或降序排序方向。
Column：用于指定被索引的列。
PAD_INDEX：用于指定索引中間級(jí)中每個(gè)頁(yè)（節(jié)點(diǎn)）上保持開放的空間。
FILLFACTOR = fillfactor：用于指定在創(chuàng)建索引時(shí)，每個(gè)索引頁(yè)的數(shù)據(jù)占索引頁(yè)大小的百分比，fillfactor的值為1到100。
IGNORE_DUP_KEY：用于控制當(dāng)往包含于一個(gè)唯一聚集索引中的列中插入重復(fù)數(shù)據(jù)時(shí)SQL Server所作的反應(yīng)。
DROP_EXISTING：用于指定應(yīng)刪除并重新創(chuàng)建已命名的先前存在的聚集索引或者非聚集索引。
STATISTICS_NORECOMPUTE：用于指定過期的索引統(tǒng)計(jì)不會(huì)自動(dòng)重新計(jì)算。
SORT_IN_TEMPDB：用于指定創(chuàng)建索引時(shí)的中間排序結(jié)果將存儲(chǔ)在 tempdb 數(shù)據(jù)庫(kù)中。
ON filegroup：用于指定存放索引的文件組。
例子：
復(fù)制代碼
--表bigdata創(chuàng)建一個(gè)名為idx_mobiel的非聚集索引，索引字段為mobiel
create index idx_mobiel
on bigdata(mobiel)
--表bigdata創(chuàng)建一個(gè)名為idx_id的唯一聚集索引，索引字段為id
--要求成批插入數(shù)據(jù)時(shí)忽略重復(fù)值，不重新計(jì)算統(tǒng)計(jì)信息，填充因子為40
create unique clustered index idx_id
on bigdata(id)
with pad_index,
fillfactor=40,
ignore_dup_key,
statistics_norecompute
復(fù)制代碼
管理索引
復(fù)制代碼
Exec sp_helpindex BigData --查看索引定義
Exec sp_rename 'BigData.idx_mobiel','idx_big_mobiel' --將索引名由'idx_mobiel' 改為'idx_big_mobiel'
drop index BigData.idx_big_mobiel --刪除bigdata表中的idx_big_mobiel索引
dbcc showcontig(bigdata,idx_mobiel) --檢查bigdata表中索引idx_mobiel的碎片信息
dbcc indexdefrag(Test,bigdata,idx_mobiel) --整理test數(shù)據(jù)庫(kù)中bigdata表的索引idx_mobiel上的碎片
update statistics bigdata --更新bigdata表中的全部索引的統(tǒng)計(jì)信息
復(fù)制代碼
索引的設(shè)計(jì)原則
對(duì)于一張表來說索引的有無和建立什么樣的索引，要取決與where字句和Join表達(dá)式中。
一般來說建立索引的原則包括以下內(nèi)容：
系統(tǒng)一般會(huì)給逐漸字段自動(dòng)建立聚集索引。
有大量重復(fù)值且經(jīng)常有范圍查詢和排序、分組的列，或者經(jīng)常頻繁訪問的列，考慮建立聚集索引。
在一個(gè)經(jīng)常做插入操作的表中建立索引，應(yīng)使用fillfactor(填充因子)來減少頁(yè)分裂，同時(shí)提高并發(fā)度降低死鎖的發(fā)生。如果在表為只讀表，填充因子可設(shè)為100.
在選擇索引鍵時(shí)，盡可能采用小數(shù)據(jù)類型的列作為鍵以使每個(gè)索引頁(yè)能容納盡可能多的索引鍵和指針，通過這種方式，可使一個(gè)查詢必需遍歷的索引頁(yè)面降低到最小，此外，盡可能的使用整數(shù)做為鍵值，因?yàn)檎麛?shù)的訪問速度最快。
索引的用途：我們對(duì)數(shù)據(jù)查詢及處理速度已成為衡量應(yīng)用系統(tǒng)成敗的標(biāo)準(zhǔn)，而采用索引來加快數(shù)據(jù)處理速度通常是最普遍采用的優(yōu)化方法。
索引是什么：數(shù)據(jù)庫(kù)中的索引類似于一本書的目錄，在一本書中使用目錄可以快速找到你想要的信息，而不需要讀完全書。在數(shù)據(jù)庫(kù)中，數(shù)據(jù)庫(kù)程序使用索引可以重啊到表中的數(shù)據(jù)，而不必掃描整個(gè)表。書中的目錄是一個(gè)字詞以及各字詞所在的頁(yè)碼列表，數(shù)據(jù)庫(kù)中的索引是表中的值以及各值存儲(chǔ)位置的列表。
索引的利弊：查詢執(zhí)行的大部分開銷是I/O，使用索引提高性能的一個(gè)主要目標(biāo)是避免全表掃描，因?yàn)槿頀呙栊枰獜拇疟P上讀取表的每一個(gè)數(shù)據(jù)頁(yè)，如果有索引指向數(shù)據(jù)值，則查詢只需要讀少數(shù)次的磁盤就行啦。所以合理的使用索引能加速數(shù)據(jù)的查詢。但是索引并不總是提高系統(tǒng)的性能，帶索引的表需要在數(shù)據(jù)庫(kù)中占用更多的存儲(chǔ)空間，同樣用來增刪數(shù)據(jù)的命令運(yùn)行時(shí)間以及維護(hù)索引所需的處理時(shí)間會(huì)更長(zhǎng)。所以我們要合理使用索引，及時(shí)更新去除次優(yōu)索引。
數(shù)據(jù)表的基本結(jié)構(gòu)
一個(gè)新表被創(chuàng)建之時(shí)，系統(tǒng)將在磁盤中分配一段以8K為單位的連續(xù)空間，當(dāng)字段的值從內(nèi)存寫入磁盤時(shí)，就在這一既定空間隨機(jī)保存，當(dāng)一個(gè) 8K用完的時(shí)候，數(shù)據(jù)庫(kù)指針會(huì)自動(dòng)分配一個(gè)8K的空間。這里，每個(gè)8K空間被稱為一個(gè)數(shù)據(jù)頁(yè)（Page），又名頁(yè)面或數(shù)據(jù)頁(yè)面，并分配從0-7的頁(yè)號(hào), 每個(gè)文件的第0頁(yè)記錄引導(dǎo)信息，叫文件頭（File header）；每8個(gè)數(shù)據(jù)頁(yè)（64Ｋ）的組合形成擴(kuò)展區(qū)（Extent），稱為擴(kuò)展。全部數(shù)據(jù)頁(yè)的組合形成堆（Heap）。
SQLS規(guī)定行不能跨越數(shù)據(jù)頁(yè)，所以，每行記錄的最大數(shù)據(jù)量只能為8K。這就是char和varchar這兩種字符串類型容量要限制在8K以內(nèi)的 原因，存儲(chǔ)超過8K的數(shù)據(jù)應(yīng)使用text類型，實(shí)際上，text類型的字段值不能直接錄入和保存，它只是存儲(chǔ)一個(gè)指針，指向由若干8K的文本數(shù)據(jù)頁(yè)所組成 的擴(kuò)展區(qū)，真正的數(shù)據(jù)正是放在這些數(shù)據(jù)頁(yè)中。
頁(yè)面有空間頁(yè)面和數(shù)據(jù)頁(yè)面之分。
當(dāng)一個(gè)擴(kuò)展區(qū)的8個(gè)數(shù)據(jù)頁(yè)中既包含了空間頁(yè)面又包括了數(shù)據(jù)或索引頁(yè)面時(shí)，稱為混合擴(kuò)展（Mixed Extent）,每張表都以混合擴(kuò)展開始；反之，稱為一致擴(kuò)展（Uniform Extent），專門保存數(shù)據(jù)及索引信息。
表被創(chuàng)建之時(shí)，SQLS在混合擴(kuò)展中為其分配至少一個(gè)數(shù)據(jù)頁(yè)面，隨著數(shù)據(jù)量的增長(zhǎng)，SQLS可即時(shí)在混合擴(kuò)展中分配出7個(gè)頁(yè)面，當(dāng)數(shù)據(jù)超過8個(gè)頁(yè)面時(shí)，則從一致擴(kuò)展中分配數(shù)據(jù)頁(yè)面。
空間頁(yè)面專門負(fù)責(zé)數(shù)據(jù)空間的分配和管理，包括：PFS頁(yè)面（Page free space）：記錄一個(gè)頁(yè)面是否已分配、位于混合擴(kuò)展還是一致擴(kuò)展以及頁(yè)面上還有多少可用空間等信息；GAM頁(yè)面（Global allocation map）和SGAM頁(yè)面(Secodary global allocation map)：用來記錄空閑的擴(kuò)展或含有空閑頁(yè)面的混合擴(kuò)展的位置。SQLS綜合利用這三種類型的頁(yè)面文件在必要時(shí)為數(shù)據(jù)表創(chuàng)建新空間；
數(shù)據(jù)頁(yè)或索引頁(yè)則專門保存數(shù)據(jù)及索引信息，SQLS使用4種類型的數(shù)據(jù)頁(yè)面來管理表或索引：它們是IAM頁(yè)、數(shù)據(jù)頁(yè)、文本/圖像頁(yè)和索引頁(yè)。
在WINDOWS中，我們對(duì)文件執(zhí)行的每一步操作，在磁盤上的物理位置只有系統(tǒng)（system）才知道；SQL SERVER沿襲了這種工作方式，在插入數(shù)據(jù)的過程中，不但每個(gè)字段值在數(shù)據(jù)頁(yè)面中的保存位置是隨機(jī)的，而且每個(gè)數(shù)據(jù)頁(yè)面在“堆”中的排列位置也只有系統(tǒng) （system）才知道。
這是為什么呢？眾所周知，OS之所以能管理DISK，是因?yàn)樵谙到y(tǒng)啟動(dòng)時(shí)首先加載了文件分配表：FAT（File Allocation Table），正是由它管理文件系統(tǒng)并記錄對(duì)文件的一切操作，系統(tǒng)才得以正常運(yùn)行；同理，作為管理系統(tǒng)級(jí)的SQL SERVER,也有這樣一張類似FAT的表存在，它就是索引分布映像頁(yè)：IAM（Index Allocation Map）。
IAM的存在，使SQLS對(duì)數(shù)據(jù)表的物理管理有了可能。
IAM頁(yè)從混合擴(kuò)展中分配，記錄了8個(gè)初始頁(yè)面的位置和該擴(kuò)展區(qū)的位置，每個(gè)IAM頁(yè)面能管理512,000個(gè)數(shù)據(jù)頁(yè)面，如果數(shù)據(jù)量太 大，SQLS也可以增加更多的IAM頁(yè)，可以位于文件的任何位置。第一個(gè)IAM頁(yè)被稱為FirstIAM,其中記錄了以后的IAM頁(yè)的位置。
數(shù)據(jù)頁(yè)和文本/圖像頁(yè)互反，前者保存非文本/圖像類型的數(shù)據(jù)，因?yàn)樗鼈兌疾怀^8K的容量，后者則只保存超過8K容量的文本或圖像類型數(shù)據(jù)。而索 引頁(yè)顧名思義，保存的是與索引結(jié)構(gòu)相關(guān)的數(shù)據(jù)信息。了解頁(yè)面的問題有助我們下一步準(zhǔn)確理解SQLS維護(hù)索引的方式，如頁(yè)拆分、填充因子等。
頁(yè)分裂
一半的數(shù)據(jù)將保留在老頁(yè)面，而另一半將放入新頁(yè)面，并且新頁(yè)面可能被分配到任何可用的頁(yè)。所以，頻繁頁(yè)分裂，后果很嚴(yán)重，將使物理表產(chǎn)生大量數(shù)據(jù)碎片，導(dǎo)致直接造成I/O效率的急劇下降，最后，停止SQLS的運(yùn)行并重建索引將是我們的唯一選擇!
填充因子
索引的一個(gè)特性，定義該索引每頁(yè)上的可用空間量。FILLFACTOR（填充因子）適應(yīng)以后表數(shù)據(jù)的擴(kuò)展并減小了頁(yè)拆分的可能性。填充因子是從0到100的百分比數(shù)值，設(shè)為100時(shí)表示將數(shù)據(jù)頁(yè)填滿。只有當(dāng)不會(huì)對(duì)數(shù)據(jù)進(jìn)行更改時(shí)(例如 只讀表中)才用此設(shè)置。值越小則數(shù)據(jù)頁(yè)上的空閑空間越大，這樣可以減少在索引增長(zhǎng)過程中進(jìn)行頁(yè)分裂的需要，但這一操作需要占用更多的硬盤空間。填充因子指定不當(dāng)，會(huì)降低數(shù)據(jù)庫(kù)的讀取性能，其降低量與填充因子設(shè)置值成反比。
索引的分類
SQL SERVER中有多種索引類型。
按存儲(chǔ)結(jié)構(gòu)區(qū)分：“聚集索引（又稱聚類索引，簇集索引）”，“分聚集索引（非聚類索引，非簇集索引）”
按數(shù)據(jù)唯一性區(qū)分：“唯一索引”，“非唯一索引”
按鍵列個(gè)數(shù)區(qū)分：“單列索引”，“多列索引”。
聚集索引
聚集索引是一種對(duì)磁盤上實(shí)際數(shù)據(jù)重新組織以按指定的一列或多列值排序。像我們用到的漢語(yǔ)字典，就是一個(gè)聚集索引，比如要查“張”，我們自然而然就翻到字典的后面百十頁(yè)。然后根據(jù)字母順序跟查找出來。這里用到微軟的平衡二叉樹算法，即首先把書翻到大概二分之一的位置，如果要找的頁(yè)碼比該頁(yè)的頁(yè)碼小，就把書向前翻到四分之一處，否則，就把書向后翻到四分之三的地方，依此類推，把書頁(yè)續(xù)分成更小的部分，直至正確的頁(yè)碼。
由于聚集索引是給數(shù)據(jù)排序，不可能有多種排法，所以一個(gè)表只能建立一個(gè)聚集索引?？茖W(xué)統(tǒng)計(jì)建立這樣的索引需要至少相當(dāng)與該表120%的附加空間，用來存放該表的副本和索引中間頁(yè)，但是他的性能幾乎總是比其它索引要快。
由于在聚集索引下，數(shù)據(jù)在物理上是按序排列在數(shù)據(jù)頁(yè)上的，重復(fù)值也排在一起，因而包含范圍檢查（bentween,<,><=,>=）或使用group by 或order by的查詢時(shí)，一旦找到第一個(gè)鍵值的行，后面都將是連在一起，不必在進(jìn)一步的搜索，避免啦大范圍的掃描，可以大大提高查詢速度。
非聚集索引
sqlserver默認(rèn)情況下建立的索引是非聚集索引，他不重新組織表中的數(shù)據(jù)，而是對(duì)每一行存儲(chǔ)索引列值并用一個(gè)指針指向數(shù)據(jù)所在的頁(yè)面。他像漢語(yǔ)字典中的根據(jù)‘偏旁部首’查找要找的字，即便對(duì)數(shù)據(jù)不排序，然而他擁有的目錄更像是目錄，對(duì)查取數(shù)據(jù)的效率也是具有的提升空間，而不需要全表掃描。
一個(gè)表可以擁有多個(gè)非聚集索引，每個(gè)非聚集索引根據(jù)索引列的不同提供不同的排序順序。
創(chuàng)建索引
語(yǔ)法
復(fù)制代碼
CREATE [UNIQUE] [CLUSTERED| NonCLUSTERED ]
INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[with[PAD_INDEX][[,]FILLFACTOR=fillfactor]
[[,]IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]
[[,]SORT_IN_TEMPDB]
]
[ ON filegroup ]
復(fù)制代碼
CREATE INDEX命令創(chuàng)建索引各參數(shù)說明如下：
UNIQUE：用于指定為表或視圖創(chuàng)建唯一索引，即不允許存在索引值相同的兩行。
CLUSTERED：用于指定創(chuàng)建的索引為聚集索引。
NONCLUSTERED：用于指定創(chuàng)建的索引為非聚集索引。
index_name：用于指定所創(chuàng)建的索引的名稱。
table：用于指定創(chuàng)建索引的表的名稱。
view：用于指定創(chuàng)建索引的視圖的名稱。
ASC|DESC：用于指定具體某個(gè)索引列的升序或降序排序方向。
Column：用于指定被索引的列。
PAD_INDEX：用于指定索引中間級(jí)中每個(gè)頁(yè)（節(jié)點(diǎn)）上保持開放的空間。
FILLFACTOR = fillfactor：用于指定在創(chuàng)建索引時(shí)，每個(gè)索引頁(yè)的數(shù)據(jù)占索引頁(yè)大小的百分比，fillfactor的值為1到100。
IGNORE_DUP_KEY：用于控制當(dāng)往包含于一個(gè)唯一聚集索引中的列中插入重復(fù)數(shù)據(jù)時(shí)SQL Server所作的反應(yīng)。
DROP_EXISTING：用于指定應(yīng)刪除并重新創(chuàng)建已命名的先前存在的聚集索引或者非聚集索引。
STATISTICS_NORECOMPUTE：用于指定過期的索引統(tǒng)計(jì)不會(huì)自動(dòng)重新計(jì)算。
SORT_IN_TEMPDB：用于指定創(chuàng)建索引時(shí)的中間排序結(jié)果將存儲(chǔ)在 tempdb 數(shù)據(jù)庫(kù)中。
ON filegroup：用于指定存放索引的文件組。
例子：
復(fù)制代碼
--表bigdata創(chuàng)建一個(gè)名為idx_mobiel的非聚集索引，索引字段為mobiel
create index idx_mobiel
on bigdata(mobiel)
--表bigdata創(chuàng)建一個(gè)名為idx_id的唯一聚集索引，索引字段為id
--要求成批插入數(shù)據(jù)時(shí)忽略重復(fù)值，不重新計(jì)算統(tǒng)計(jì)信息，填充因子為40
create unique clustered index idx_id
on bigdata(id)
with pad_index,
fillfactor=40,
ignore_dup_key,
statistics_norecompute
復(fù)制代碼
管理索引
復(fù)制代碼
Exec sp_helpindex BigData --查看索引定義
Exec sp_rename 'BigData.idx_mobiel','idx_big_mobiel' --將索引名由'idx_mobiel' 改為'idx_big_mobiel'
drop index BigData.idx_big_mobiel --刪除bigdata表中的idx_big_mobiel索引
dbcc showcontig(bigdata,idx_mobiel) --檢查bigdata表中索引idx_mobiel的碎片信息
dbcc indexdefrag(Test,bigdata,idx_mobiel) --整理test數(shù)據(jù)庫(kù)中bigdata表的索引idx_mobiel上的碎片
update statistics bigdata --更新bigdata表中的全部索引的統(tǒng)計(jì)信息
復(fù)制代碼
索引的設(shè)計(jì)原則
對(duì)于一張表來說索引的有無和建立什么樣的索引，要取決與where字句和Join表達(dá)式中。
一般來說建立索引的原則包括以下內(nèi)容：
系統(tǒng)一般會(huì)給逐漸字段自動(dòng)建立聚集索引。
有大量重復(fù)值且經(jīng)常有范圍查詢和排序、分組的列，或者經(jīng)常頻繁訪問的列，考慮建立聚集索引。
在一個(gè)經(jīng)常做插入操作的表中建立索引，應(yīng)使用fillfactor(填充因子)來減少頁(yè)分裂，同時(shí)提高并發(fā)度降低死鎖的發(fā)生。如果在表為只讀表，填充因子可設(shè)為100.
在選擇索引鍵時(shí)，盡可能采用小數(shù)據(jù)類型的列作為鍵以使每個(gè)索引頁(yè)能容納盡可能多的索引鍵和指針，通過這種方式，可使一個(gè)查詢必需遍歷的索引頁(yè)面降低到最小，此外，盡可能的使用整數(shù)做為鍵值，因?yàn)檎麛?shù)的訪問速度最快。
樂發(fā)網(wǎng)超市批發(fā)網(wǎng)提供超市貨源信息,超市采購(gòu)進(jìn)貨渠道。超市進(jìn)貨網(wǎng)提供成都食品批發(fā),日用百貨批發(fā)信息、微信淘寶網(wǎng)店超市采購(gòu)信息和超市加盟信息.打造國(guó)內(nèi)超市采購(gòu)商與批發(fā)市場(chǎng)供應(yīng)廠商搭建網(wǎng)上批發(fā)市場(chǎng)平臺(tái),是全國(guó)批發(fā)市場(chǎng)行業(yè)中電子商務(wù)權(quán)威性網(wǎng)站。
本文內(nèi)容整合網(wǎng)站：百度百科、知乎、淘寶平臺(tái)規(guī)則
