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