最佳化 SQL Server 索引的小技巧

在本文中，我將說明如何用SQL Server的工具來最佳化資料庫索引的使用，本文還涉及到有關索引的一般性知識。
　　
　　 關於索引的常識
　　
　　影響到資料庫效能的最大因素就是索引。由於該問題的複雜性，我只可能簡單的談談這個問題，不過關於這方面的問題，目前有好幾本不錯的書籍可供你參閱。我在這裡只討論兩種SQL Server索引，即clustered索引和nonclustered索引。當考察建立什麼類型的索引時，你應當考慮資料類型和儲存這些資料的column。同樣，你也必須考慮資料庫可能用到的查詢類型以及使用的最為頻繁的查詢類型。
　　
　　索引的類型

　　如果column儲存了高度相關的資料，並且常常被順序訪問時，最好使用clustered索引，這是因為如果使用clustered索引，SQL Server會在物理上按升序（預設）或者降序重排資料列，這樣就可以迅速的找到被查詢的資料。同樣，在搜尋控制在一定範圍內的情況下，對這些column也最好使用clustered索引。這是因為由於物理上重排資料，每個表格上只有一個clustered索引。
　　
　　與上面情況相反，如果columns包含的資料相關性較差，你可以使用nonculstered索引。你可以在一個表格中使用高達249個nonclustered索引——儘管我想象不出實際應用場合會用的上這麼多索引。
　　
　　當表格使用主關鍵字（primary keys），預設情況下SQL Server會自動對包含該關鍵字的column(s)建立一個專屬的cluster索引。很顯然，對這些column(s)建立專屬索引意味著主關鍵字的唯一性。當建立外關鍵字（foreign key）關係時，如果你打算頻繁使用它，那麼在外關鍵字cloumn上建立nonclustered索引不失為一個好的方法。如果表格有clustered索引，那麼它用一個鏈表來維護資料頁之間的關係。相反，如果表格沒有clustered索引，SQL Server將在一個堆棧中儲存資料頁。
　　
　 　資料頁

　　當索引建立起來的時候，SQLServer就建立資料頁（datapage），資料頁是用以加速搜尋的指標。當索引建立起來的時候，其對應的填滿因數也即被設定。設定填滿因數的目的是為了指示該索引中資料頁的百分比。隨著時間的推移，資料庫的更新會消耗掉已有的空閑空間，這就會導致頁被拆分。頁面分割的後果是降低了索引的效能，因而使用該索引的查詢會導致資料存放區的支離破碎。當建立一個索引時，該索引的填滿因數即被設定好了，因此填滿因數不能動態維護。
　　
　　為了更新資料頁中的填滿因數，我們可以停止舊有索引並重建索引，並重新設定填滿因數（注意：這將影響到當前資料庫的運行，在重要場合請謹慎使用）。DBCC INDEXDEFRAG和DBCC DBREINDEX是清除clustered和nonculstered索引片段的兩個命令。INDEXDEFRAG是一種線上操作（也就是說，它不會阻塞其它表格動作，如查詢），而DBREINDEX則在物理上重建索引。在絕大多數情況下，重建索引可以更好的消除片段，但是這個優點是以阻塞當前發生在該索引所在表格上其它動作為代價換取來得。當出現較大的片段索引時，INDEXDEFRAG會花上一段比較長的時間，這是因為該命令的運行是基於小的互動塊（transactional block）。
　　
　　填滿因數

　　當你執行上述措施中的任何一個，資料庫引擎可以更有效返回編入索引的資料。關於填滿因數（fillfactor）話題已經超出了本文的範疇，不過我還是提醒你需要注意那些打算使用填滿因數建立索引的表格。
　　
　　在執行查詢時，SQL Server動態選擇使用哪個索引。為此，SQL Server根據每個索引上分布在該關鍵字上的統計量來決定使用哪個索引。值得注意的是，經過日常的資料庫活動（如插入、刪除和更新表格），SQL Server用到的這些統計量可能已經“到期”了，需要更新。你可以通過執行DBCC SHOWCONTIG來查看統計量的狀態。當你認為統計量已經“到期”時，你可以執行該表格的UPDATE STATISTICS命令，這樣SQL Server就重新整理了關於該索引的資訊了。
　　
　　建立資料庫維護計劃

　　SQL Server提供了一種簡化並自動維護資料庫的工具。這個稱之為資料庫維護計劃嚮導（Database Maintenance Plan Wizard ，DMPW）的工具也包括了對索引的最佳化。如果你運行這個嚮導，你會看到關於資料庫中關於索引的統計量，這些統計量作為日誌工作並定時更新，這樣就減輕了手工重建索引所帶來的工作量。如果你不想自動定期重新整理索引統計量，你還可以在DMPW中選擇重新組織資料和資料頁，這將停止舊有索引並按特定的填滿因數重建索引。