索引的設計準則

索引的設計準則

設定太多的索引也是有’弊’的，所以索引設計準則，也就是只應該替必要欄位建立索引：

1.      經常被用來搜尋資料記錄的欄位

最好是辨識度高的欄位(即資料內容重複性低的欄位)。

還有一項重要技巧，就是「涵蓋式查詢所提昇的執行速度最顯著」。比方說，替欄位A、B與C的組合建立一個索引，而在查詢中只提取B與C的內容，則此一查詢便是一個涵蓋式查詢。

又例如，替資料表employee的Id、Name與Birthday三個欄位的組合建立一個索引，則下列各個查詢皆屬於涵蓋式查詢：

SELECT Id FROM employee

SELECT Id FROM employee WHERE Id LIKE ‘M%’

SELECT Id FROM employee WHERE Name LIKE ‘許%’

SELECT Id, Name, Birthday FROM employee

SELECT Id, Name, Birthday FROM employee

WHERE Id LIKE ‘M%’ AND

                            Name LIKE ‘許%’ AND

                            Birthday BETWEEN ‘1973/1/1’ AND ‘1973/12/31’

  

   涵蓋式查詢之所以能將存取速度提昇到最高點，主因是查詢要提取的所有資料皆位於索引本身的索引分頁(Index Page)中，而不需去參考資料表所在的資料分頁，因此能有效降低整體I/O傳輸而提昇存取速度。

  

2.      PRIMARY KEY條件約束所定義之作為主索引鍵的欄位(此索引由SQL Server自動建立)。

3.      套用UNIQUE條件約束的欄位(此索引由SQL Server自動建立)。

4.      FOREIGN KEY條件約束所定義之作為外部索引鍵的欄位。

5.      於查詢中被用來連結資料表的欄位。

6.      經常被用來作為排序基準的欄位。

除了上述狀況外的欄位，其它欄位都不應該建立索引。此外，SQL Server亦不允許替bit、text、ntext與image資料型態的欄位建立索引。

※為什麼要慎選用來建立索引欄位？ 不能浮濫建立的主要原因如下：

1.      索引會佔用磁碟空間，建立不必要的索引只會形成浪費。

因此最好不要替資料內容超過20個位元組的欄位建立索引。

2.      如果索引過多，將因為必須更新相關的索引而使得新增、修改與刪除資料記錄的速度減慢。

因此對於一個經常性作例行性變動的資料表而言，則應謹慎地建立確實必要的索引，否則只會拖垮應用程式的執行速度。

3.      索引建立之後是需要維護的，都需要時間與資源的，因此物建立使用頻率很低的索引，以免耗用過多維護成本。

4.      儘量不要替資料內容重複率很高的欄位建立索引，因為它所帶來的效益很少。

事實上要設計出好的索引可能是一項複雜、費時費力、且不斷在嘗試錯誤中不停修正的工作。於是可使用索引微調精靈來輔助完成此一工作。

※索引的類型

如果以儲存架構來區分，則有「叢集索引」(Clustered Index)與「非叢集索引」(Non Clustered Index)的區別；如果以資料的獨一性來區別，則有「獨一索引」(Unique Index)與「非獨一索引」(NonUnique Index)的不同；若以鍵值欄的個數來區隔，則有單一鍵值索引與多重鍵值索引的分別。

所謂的單一鍵值索引，意指替某一欄位建立索引；而多重鍵值索引，則是指替多個欄位的組合建立索引。

※叢集索引(Clustered Index)

如果經常要搜尋某欄位內容介於某一個範圍的資料記錄(例如：查詢出生日期介於1967/1/1～1977/12/31的員工資料)，亦或是經常要以特定次序來存取欄位的內容(例如：以年齡大小來列出員工資料)，則替此類欄位建立叢集索引將能有效提昇存取速度。

資料表之資料記錄實際存放的次序將會與叢集索引中相對應之鍵值的實際存放次序完全相同。

所以叢集索引會改變資料表之資料記錄的存放次序，並使之與叢集索引中之鍵值的存放次序相同。