索引重整與效率

轉載自索引重整與效率

剛接觸程式設計工作時，只知道設定索引是一件很重要的事
依著前輩的指點，囫圇吞棗般只會把常query或是關鍵欄位設定為索引...
直到一次，不知情的同事將一個近百萬筆資料table的欄位中，
連包含非key值的長字串資料欄位全都設為索引之後....
這動作一下..效能盡失，最後嚴重拖垮整個資料庫服務
此時，我才知道「會不會」設索引，才是一件重要的事~~~
最近利用DBCC SHOWCONTIG這指令去觀查幾個資料量較多的table
在觀察數據的同時，對於DBCC INDEXDEFRAG和DBCC DBREINDEX兩指令又有一新認知。
step1.執行 DBCC SHOWCONTIG(my_table)
單純針對table進行層級掃描先取得分頁相關資訊
- 掃描的分頁................................: 64
- 掃描的範圍..............................: 11
- 範圍切換..............................: 12
- 每個範圍的平均分頁........................: 5.8
- 掃描密度 [最佳次數:實際次數].......: 33.54% [4:13]
- 邏輯掃描片段 ..................: 53.13%
- 範圍掃描片段 ...................: 36.36%
- 每個分頁的平均可用位元組.....................: 376.5
- 平均分頁密度 (全滿).....................: 95.35%
step2.執行 DBCC INDEXDEFRAG (MyDB, 'my_table',PK_my_table)
執行破碎頁面的整理後，掃描密度明顯地變大，分頁程度變得更緊密更平均。
邏輯掃描片段(cluster index叢集索引的葉層分頁狀況)愈大表示指標的指向愈紊亂，
也就是說「失序」頁面的比例，重整破碎頁面後確實有得到明顯的效果。
- 掃描的分頁................................: 63
- 掃描的範圍..............................: 9
- 範圍切換..............................: 8
- 每個範圍的平均分頁........................: 7.0
- 掃描密度 [最佳次數:實際次數].......: 88.89% [8:9]
- 邏輯掃描片段 ..................: 0.00%
- 範圍掃描片段 ...................: 44.44%
- 每個分頁的平均可用位元組.....................: 254.0
- 平均分頁密度 (全滿).....................: 96.86%
step3.執行 DBCC DBREINDEX (my_table,'' ,90)
執行索引的重整，並依需求重新設定填滿因子
(這個TABLE 查詢的比例多，資料異動機率並不是很頻繁，因此設為90%)
發現掃描密度、範圍掃描片段、分頁平均可用位元組得到更多的改善空間。
- 掃描的分頁................................: 68
- 掃描的範圍..............................: 9
- 範圍切換..............................: 8
- 每個範圍的平均分頁........................: 7.6
- 掃描密度 [最佳次數:實際次數].......: 100.00% [9:9]
- 邏輯掃描片段 ..................: 0.00%
- 範圍掃描片段 ...................: 0.00%
- 每個分頁的平均可用位元組.....................: 830.6
- 平均分頁密度 (全滿).....................: 89.74%
後記：
索引重整就像梳頭髮一樣，原本雜亂的頭髮愈梳他就會愈順
定期整理這些資料量大的table，讓index分頁的b-tree目錄在搜尋資料時發揮應有的效能，
索引一旦建立之後，應該更需要去維護它，放著不管最終反而變成是影響效能的因素之一。
但是索引並不是完全只有好處，異動頻繁的table除了不要建立太多的索引之外
對於索引的填滿率(填滿因子)更需注意，保留適當的空間，讓新刪修頻繁的table展現最好的效益。
微軟的官方文件中也有提到....
下一版的 Microsoft SQL Server 將不再提供此功能。
請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。 請改用 ALTER INDEX。