SQL Server資料庫效能最佳化之索引篇【轉】

標籤：blog   http   io   os   使用   ar   java   strong   sp   

http://www.blogjava.net/allen-zhe/archive/2010/07/23/326966.html 效能最佳化之索引篇

近期項目需要， 做了一段時間的SQL Server效能最佳化，遇到了一些問題，也積累了一些經驗，現總結一下，與君共用。SQL Server效能最佳化涉及到許多方面，如良好的系統和資料庫設計，優質的SQL編寫，合適的資料表索引設計，甚至各種硬體因素：網路效能、伺服器的效能、 作業系統的效能，甚至網卡、交換器等。這篇文章主要講到如何改善索引，還將有另一篇討論如何改善SQL語句。

    首先需要強調一下，水能載舟，亦能覆舟。建立“適當”的索引是實現查詢最佳化的首要前提。
    當根據索引碼的值搜尋資料時，索引提供了對資料的快速存取。事實上，沒有索引,資料庫也能根據SELECT語句成功地檢索到結果，但隨著表變得越來越大，使用“適當”的索引的效果就越來越明顯。索引有助於提高檢索效能，但過多或不當的索引也會導致系統低效。因為使用者在表中每加進一個索引，資料庫就要做更多的工作。過多的索引甚至會導致索引片段。所以，要建立一個“適當”的索引體系，特別是對彙總索引的建立，更應精益求精，以使資料庫能得到高效能的發揮。

    簡述SQL Server的索引
    SQL Server提供了兩種索引：叢集索引（clustered index，也稱聚類索引、簇集索引）和非叢集索引（nonclustered index，也稱非聚類索引、非簇集索引）。
叢集索引確定表中資料的物理順序。叢集索引類似於電話簿，後者按姓氏排列資料。由於叢集索引規定資料在表中的實體儲存體順序，因此一個表只能包含一個叢集索引。但該索引可以包含多個列（複合式索引），就像電話簿按姓氏和名字進行組織一樣。
    叢集索引對於那些經常要搜尋範圍值的列特別有效。使用叢集索引找到包含第一個值的行後，便可以確保包含後續索引值的行在物理相鄰。例如，如果應用程式執行的一個查詢經常檢索某一日期範圍內的記錄，則使用叢集索引可以迅速找到包含開始日期的行，然後檢索表中所有相鄰的行，直到到達結束日期。這樣有助於提高此類查詢的效能。同樣，如果對從表中檢索的資料進行排序時經常要用到某一列，則可以將該表在該列上聚集（物理排序），避免每次查詢該列時都進行排序，從而節省成本。
    非叢集索引與課本中的索引類似。資料存放區在一個地方，索引儲存在另一個地方，索引帶有指標指向資料的儲存位置。索引中的項目按索引索引值的順序儲存，而表中的資訊按另一種順序儲存（這可以由叢集索引規定）。如果在表中未建立叢集索引，則無法保證這些行具有任何特定的順序。
    更詳細的介紹請參考MSDN上關於索引的介紹。http://msdn.microsoft.com/zh-cn/library/ms189271.aspx 

    使用SQL Server的索引
    問題又來了，既然分了兩種索引，何時何種情況用何種索引？那就看看下錶吧。簡單的說就是：對於小數目的不同值，或列經常被分組排序，或需要返回某範圍內的資料時使用叢集索引；對於大數目的不同值，或列經常被分組排序，或列被頻繁更新時使用非叢集索引。

	使用叢集索引	使用非叢集索引
列經常被分組排序	應	應
返回某範圍內的資料	應	不應
一個或極少不同值	不應	不應
小數目的不同值	應	不應
大數目的不同值	不應	應
頻繁更新的列	不應	應
外鍵列	應	應
主鍵列	應	應
頻繁修改索引列	不應	應

       如何改善索引的一些經驗：

1. 索引首先要滿足你的應用中最關鍵或者是被很多使用者頻繁執行的查詢。
    若某個查詢每月僅執行一次，要考慮是否值得為其涉及表建立了索引。要知道在當月的其它時間資料庫系統對該索引的維護開銷是要超過滿足該查詢的表掃描的開銷的。所以，好鋼用在刀刃上，好索引用在關鍵頻繁的查詢上。

2. 在經常進行串連，但是沒有指定為外鍵的列上建立索引。
    在巢狀查詢中，對錶的順序存取對查詢效率可能產生致命的影響。比如採用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那麼這個查詢就要查詢10億行資料。避免這種情況的主要方法就是對串連的列進行索引。例如下面的一條SQL，串連這兩個表：tblA（id, c1, c2, …）和tblB（id, …），就需要分別在兩個表的id欄位上建立索引。

select min(c1) from tblA where c1 > 1 -- 會觸發Clustered Index Seek。
select min(c1) from tblA where c1 > 1 and c2 = 2 -- 會觸發Clustered Index Seek。
select min(c1) from tblA where c1 > 1 and c3 < 3 -- 會觸發Clustered Index Scan。
select min(c1) from tblA where c2 = 2 and c3 < 3 -- 會觸發Clustered Index Scan。
select min(c1) from tblA where c1 > 1 and c2 = 2 and c3 < 3 -- 會觸發Clustered Index Seek，且形成了索引覆蓋。

    其中Clustered Index Scan的執行計畫


    其中Clustered Index Seek的執行計畫


    如此可見形成索引覆蓋的必要性。

6. 非叢集索引與精確尋找的默契
    對於某個表中的某個欄位存在大數目的不同值時，為該欄位建個非叢集索引會達到意想不到的效果。因為資料庫系統在搜尋資料值時，先對非叢集索引進行搜尋，找到資料值在表中的位置，然後從該位置直接檢索資料。因為索引包含描述查詢所搜尋的資料值在表中的精確位置的條目，這也是為什麼非叢集索引是精確匹配查詢的最佳方法。例如，在employee表為emp_id列建了非叢集索引，要搜尋其僱員ID (emp_id) > 1000的所有人，SQL   Server會在索引中直接跳到emp_id = 1000這樣一個條目之後，列出匹配的emp_id列在表中的頁和行，然後直接轉到該頁該行。

7. 如果你是皮爾斯，SQL Server 的執行計畫就是朗多
    SQL Server 2005的Microsoft SQL Server Management Studio和Database Engine Tuning Advisor（DETA）是非常好的效能調試助手，可以使用它們對SQL語句調優，查看估計的執行計畫開銷，用DETA產生最佳化建議，採納或參考索引最佳化部分。
    需要注意的是，對於估計的執行計畫，不要過於關注裡面顯示的開銷比例，而實際上這個有時會誤導。我在實際最佳化過程中就被發現，一個index scan的執行項開銷只佔25%，另一個鍵尋找的開銷佔50%，而鍵尋找部分根本沒有可最佳化的，SEEK謂詞就是ID=XXX這個建立在主鍵上的尋找。而仔細分析可以看到，後者CPU開銷0.00015，I/O開銷0.0013。而前者呢，CPU開銷1.4xxxx,I/O開銷也遠大於後者。因此，最佳化重點應該放在前者。
    網上這類的文章很多，這裡就不做贅述了。可以參考一篇較早的文章：SQL Server效能調優入門（圖文版）
    
    另外還有一篇不錯的文章，共用在這裡：探討如何在有著1000萬條資料的MS SQL SERVER資料庫中實現快速的資料提取和資料分頁 

SQL Server資料庫效能最佳化之索引篇【轉】