在做性能優(yōu)化時，經(jīng)常需要創(chuàng)建索引，維護索引，或重建，或重組；在創(chuàng)建索引時，索引的數(shù)據(jù)頁有時需要填充滿，有時需要預(yù)留一定比例的空閑空間；在分析查詢的執(zhí)行計劃之后，推薦創(chuàng)建覆蓋索引（covering index），優(yōu)化查詢語句，使用執(zhí)行計劃通過Index Seek來獲取少量數(shù)據(jù)等，這些都是索引優(yōu)化不得不知的要點。
一，索引的重組（Reorganize）和重建（Rebuild）
在SQL Server中，索引（Index）是B-Tree（balance tree）結(jié)構(gòu)，每個Page之間都有雙向指針鏈接在一起。Index是在table結(jié)構(gòu)之外，獨立存在的存儲結(jié)構(gòu)。Index能使查詢性能帶來飛躍的主要原因是：Index 結(jié)構(gòu)更小，能夠更快加載到內(nèi)存；Index ey物理順序和邏輯一致，數(shù)據(jù)的預(yù)讀取能夠提高數(shù)據(jù)的加載速度，SQL Server 每次讀取操作都會將物理物理相鄰的多個Page一起加載到內(nèi)存。
BTree結(jié)構(gòu)決定 Index 的葉子節(jié)點，從左到右使依次增大，如圖是Index的葉子節(jié)點，左邊的Index Key最小，右邊的Index Key最大：
如果更新數(shù)據(jù)導(dǎo)致index key變化，例如，將index key 由4變更為9，那么必須將9放置在8之后，10之前，如果8所在的Page有空間容納9，那么SQL Server只需要將9移動到8之后，原來的4被刪除，這會降低原page中數(shù)據(jù)存儲的密度，造成一個碎片（fragment），即：3和5之間存在空閑空間，但是物理順序和邏輯順序還是一致的。
如果8和10所在的page不能容納9，那么 SQL Server 選擇最節(jié)省，最有效的方式：拆分Page。試想，如果不拆分page，那么，5，6，7，8 這幾個數(shù)據(jù)行都要向前移動，為9騰挪空間。在SQL Server中，數(shù)據(jù)移動是十分浪費IO，內(nèi)存和CPU資源的，IO必須在CPU的調(diào)控下進行。
拆分Page是指分配一個新的Page，將8所在的Page上的數(shù)據(jù)的一半（后一半，或前一半）移動在新的Page上，如圖，將page中的后一半移動在新的page上，通過指針連接在一起，保持邏輯順序的一致性，但是物理順序已經(jīng)不連續(xù)了。
對于Index Key移動之后，其物理順序和邏輯順序仍然保持一致，這會導(dǎo)致索引出現(xiàn)碎片，數(shù)據(jù)存儲的密度降低；而拆分page，不僅將page存儲數(shù)據(jù)的密度降低一半，而且數(shù)據(jù)的物理順序和邏輯順序，導(dǎo)致SQL Server的預(yù)讀取操作效果下降。針對Index的這兩種情況，根據(jù)Index的碎片率，對Index 進行重組（Reorganize）或重建（Rebuild）。
1，索引Reorganize 和 Rebuild的過程
Rebuild 是重新創(chuàng)建，將索引占用的原有存儲空間釋放，重新申請空間來創(chuàng)建索引結(jié)構(gòu)，這意味著，SQL Server存儲引起需要為索引結(jié)構(gòu)分配新的數(shù)據(jù)頁，在重建索引結(jié)構(gòu)時，索引占用的存儲空間是其實際大小的2倍還多。
Reorganize 是重新組織索引結(jié)構(gòu)的葉子節(jié)點。在重組索引時，SQL Server存儲引擎首先按照原有的填充因子（Fillfactor），壓縮索引結(jié)構(gòu)的葉子節(jié)點；然后，使用相同的數(shù)據(jù)頁，把索引結(jié)構(gòu)的葉子節(jié)點重新組織，使葉子節(jié)點符合索引定義的邏輯順序。重組索引不會分配新的數(shù)據(jù)頁，只占用索引最初占用的存儲空間。
ALTER INDEX { index_name | ALL }ON schema.tableREBUILD | REORGANIZE2, 重建索引
在重建索引時，SQL Server 存儲引擎使用索引的定義元數(shù)據(jù)，就是說，按照索引鍵（index key），索引類型（index type），唯一屬性和排序方向重新創(chuàng)建索引。
重建索引，將使被disable的索引重新啟用；
重建聚集索引時，不會重建與之關(guān)聯(lián)的非聚集索引（nonclustered index），除非指定all關(guān)鍵字；all關(guān)鍵字指定基礎(chǔ)表中的所有索引。
如果指定all關(guān)鍵字，而基礎(chǔ)表（underlying table）是堆，那么重建索引的操作對基礎(chǔ)表沒有任何影響；而與基礎(chǔ)表相關(guān)的所有非聚集索引都將會重建；
在重新創(chuàng)建索引（Rebuild）時，如果沒有指定索引選項（Index Option），Rebuild操作使用默認的索引選項。在SQL Server 2012版本中，共有11個索引選項，其中5個索引選項的元數(shù)據(jù)存儲在sys.indexes 中，分別是 ignore_dup_key、fill_factor、is_padded、allow_row_locks、allow_page_locks，其他6個索引選項使用默認值，其默認值（Default value）都是“否定的”或0，如下列表所示：
SORT_IN_TEMPDB : Default OFFSTATISTICS_NORECOMPUTE : Default OFFDROP_EXISTING: Default OFFONLINE: Default OFFDATA_COMPRESSION : Default NONE MAXDOP: 0
查看 sys.indexes 存儲的索引選項：
View Code
3，重組索引
重組索引結(jié)構(gòu)的葉子節(jié)點，由于SQL Server默認以聯(lián)機方式重組索引，這意味著在索引重組事務(wù)執(zhí)行的過程中，SQL Server 存儲引擎不會長期阻塞表鎖，能夠?qū)A(chǔ)表（underlying table）執(zhí)行更新操作。如果索引選項ALLOW_PAGE_LOCKS設(shè)置為OFF，或索引被禁用，那么不能重組索引。
二，索引的填充屬性（FillFactor 和 PAD_INDEX）
在Create Index時，必須考慮屬性FillFactor 和 PAD_INDEX的設(shè)置，這兩個屬性只在create index 或 rebuild index時起作用，表示索引頁的填充程度，在索引結(jié)構(gòu)中，按照結(jié)點在BTree結(jié)構(gòu)中的位置，索引頁分為：葉級結(jié)點，中間結(jié)點。一個數(shù)據(jù)頁（Page）的大小是8KB。
FillFactor 屬性指定索引葉級結(jié)點的填充百分比，微軟建議設(shè)置FillFactor=90；
Pad_index 屬性是boolean 類型，指定是否使用 FillFactor 來填充索引的中間節(jié)點；默認值是OFF；
在插入數(shù)據(jù)時，SQL Server 盡可能使用Page全部的空間，而不會考慮填充屬性；填充屬性只影響索引的創(chuàng)建；
PAD_INDEX = { ON | OFF }FILLFACTOR = fillfactor在創(chuàng)建索引時，設(shè)置FillFactor=90，這意味著，SQL Server 在create index 或 rebuild index時，不是將索引頁的全部空間用完，而是使用Page空間的90%，預(yù)留10%的空閑空間，預(yù)留的Page內(nèi)部的空間叫做內(nèi)部碎片（Internal Fragmentation）。預(yù)留一定比例的Fragmentation的作用是：減少Page拆分。預(yù)留比例需要折衷查詢和更新操作：預(yù)留空間太多，能夠減少Page Split，提高數(shù)據(jù)更新速度，但預(yù)留空間過多導(dǎo)致索引碎片（Index Fragmentation）太多，降低查詢性能；預(yù)留空間太少，能夠最大限度減少Index fragmentation，提高查詢查詢性能，但是一旦數(shù)據(jù)更新，會導(dǎo)致Page Split，產(chǎn)生外部碎片，降低查詢性能。建議預(yù)留10% 的空間，這樣既能提高數(shù)據(jù)更新的速度，也能兼顧數(shù)據(jù)的更新。
1，索引中間節(jié)點的填充屬性PAD_INDEX
PAD_INDEX屬性表示中間節(jié)點填滿的程度，100減去該屬性值，就是索引頁預(yù)留的空閑空間的百分比，默認值是OFF；如果設(shè)置為ON，那么在索引 create 或 rebuild時，SQL Server 將使用FillFactor百分比來填充中間節(jié)點，因此，需要指定FILLFACTOR。
2，索引葉子節(jié)點的填充屬性 FILLFACTOR
FillFactor選項是一個整數(shù)值，有效值是從1到100，該屬性表示葉子節(jié)點填滿的程度，該屬性的默認值是0，和FillFactor=100行為相同，表示在創(chuàng)建索引或重建索引時，數(shù)據(jù)頁不會預(yù)留空閑空間。
3，推薦在創(chuàng)建索引時，顯式指定填充屬性
填充屬性只在創(chuàng)建索引（create），或重建索引（rebuild）時起作用。如果填充因子在1和100之間，那么創(chuàng)建聚集索引將分配更多的數(shù)據(jù)頁，帶來的好處是減少了頁拆分操作的次數(shù)。
例如，在創(chuàng)建索引（create），或重建索引（rebuild）時，指定FillFactor=80，表示每個葉子節(jié)點留下20%的空閑空間，當新的數(shù)據(jù)插入到基礎(chǔ)表（underlying table）時，SQL Server使用該20%的空閑空間來容納新的數(shù)據(jù)。
在創(chuàng)建index 或重建 index時，指定 fillfactor 和 pad_index 屬性:
View Code
三，索引的查找（Index Seek）和掃描（Index Scan）
索引是B樹結(jié)構(gòu)，在執(zhí)行查詢時，Index Seek是指SQL Server從索引結(jié)構(gòu)的根節(jié)點(Root Node），逐級向葉節(jié)點（Leaf Node）查找；在查找到相應(yīng)葉子節(jié)點后，取出葉子節(jié)點的數(shù)據(jù)。對于聚集索引，葉子節(jié)點是整個表的數(shù)據(jù)，Index Seek能夠獲取到所有列的數(shù)據(jù)，而對于非聚集索引，葉子節(jié)點存儲的是索引列的數(shù)據(jù)，如果索引有包含列，那么葉子節(jié)點中也存儲包含列的數(shù)據(jù)，Index Seek只能獲取索引列和包含列的數(shù)據(jù)；如果查詢還需要返回其他列的數(shù)據(jù)，那么SQL Server必須根據(jù)索引葉子節(jié)點包含的“行地址”信息到基礎(chǔ)表（或聚集索引）中去獲取數(shù)據(jù)，這就是進行書簽查找（key lookup）。Index Seek用于從大數(shù)據(jù)量的表中返回少量記錄的查詢。
Index Scan是直接遍歷索引樹的所有葉子節(jié)點，對于包含聚集索引的基礎(chǔ)表，只能進行Index Seek或Index Scan，因為，聚集索引的葉子包含所有的數(shù)據(jù)。對于堆表，當需要返回所有列的數(shù)據(jù)，SQL Server有時會選擇執(zhí)行Table Scan。Table Scan是對全表進行逐行的掃描，即使數(shù)據(jù)表中只有一行數(shù)據(jù)匹配，也會將所有數(shù)據(jù)匹配一遍，微軟建議，始終在數(shù)據(jù)表上創(chuàng)建聚集索引。
參考文檔：
Reorganize and Rebuild Indexes
Index Seek和Index Scan的區(qū)別以及適用情況
SQL SERVER – Index Seek vs. Index Scan – Diffefence and Usage – A Simple Note
--業(yè)精于勤而荒于嬉，行成于思而毀于隨--
--歡迎轉(zhuǎn)載，轉(zhuǎn)載請注明出處--
http://www.cnblogs.com/ljhdo/p/5136024.html
