設計有用的索引是改善查詢性能最重要的方面之一，它要求理解索引的結構及理解數(shù)據(jù)是如何使用的。

數(shù)據(jù)

在創(chuàng)建索引前，必須對數(shù)據(jù)有全面的了解，包括以下內(nèi)容：
·邏輯和物理設計
·數(shù)據(jù)特征
·如何使用數(shù)據(jù)
為了設計有用和有效索引，必須依賴于對用戶所發(fā)出查詢的分析。對用戶如何訪問數(shù)據(jù)的不準確分析會導致查詢響應慢或不必要的表鎖定。通過以下觀察應該知道用戶如何訪問數(shù)據(jù)：
·所執(zhí)行的查詢類型
·通常執(zhí)行的查詢頻率
全面了解用戶的數(shù)據(jù)要求有助于確定對哪些列進行索引和創(chuàng)建哪一類的索引。可能需要犧牲某個查詢的速度以使另一個查詢獲得較好的查詢性能。

使用索引的指導原則

事務環(huán)境、數(shù)據(jù)特征和數(shù)據(jù)的使用決定了要指定創(chuàng)建索引的列。索引的有用性直接與查詢返回行的比例有關，低比例或高選擇性是比較有效的。

(1).要索引的列

在經(jīng)常要搜索的列上創(chuàng)建索引，例如：
·主鍵所在的列
·外鍵或在聯(lián)接表中經(jīng)常使用的列
·搜索鍵值范圍的列
·以排序次序訪問的列
·在聚合過程中被劃分為一組的列

(2).不需要索引的列

在以下情況不需要索引列：
·在查詢中很少引用的列
·包含很少惟一值的列。例如，在具有兩個值-男和女的列上的索引，返回行在所有行中占了較高的百分比。
·由text、ntext或image數(shù)據(jù)類型定義的列，具有這些數(shù)據(jù)類型的列不能進行索引。

選擇合適的聚集索引

在為每個表選擇聚集索引時，需考慮表是如何使用的。

(1).經(jīng)常要更新的表

當優(yōu)化向經(jīng)常使用的表插入數(shù)據(jù)這一操作的性能時，可以考慮在主鍵標識列上創(chuàng)建聚集索引。通過強制將數(shù)據(jù)插入表末尾處的一小組頁面中，可以加快速度。頻繁地訪問可以使這些頁保存在內(nèi)存中。

(2).排序

時常因報表而排序的表、因聚合而分組的表或搜索特定范圍數(shù)據(jù)的表，在排序的列上建立聚集索引是很有好處的。當需要返回表的　很多列并且非聚集索引又不起作用時，使用聚集索引很有用處。例如，郵件列表以郵編建立聚集索引就很有用，因為必須打印并以指定的順序應用郵件的標簽。

(3).列的長度和數(shù)據(jù)類型

SQL Server使用聚集索引值作為每個非聚集索引中的行標識符。聚集索引值可以在表結構中重復使用很多次。
為了避免因為大的聚集索引而使與它們相關的非聚集索引變得更大或更慢，可以執(zhí)行以下操作：
·限制在聚集索引中列的數(shù)目
·通過使用varchar數(shù)據(jù)類型來代替char數(shù)據(jù)類型，以減少字符的平均數(shù)
·盡可能地使用最小的數(shù)據(jù)類型，例如用tinyint代替int

使用索引以支持查詢

查詢的性能取決于你的索引設計的好壞。另外，編寫帶有能有效地利用索引列的搜索參數(shù)的查詢也很重要。

(1).使用搜索參數(shù)

搜索參數(shù)可以限制精確匹配的搜索、范圍值的搜索，也可以限制通過AND運算符連接起來的兩個或多個條目的組合的搜索。搜索參數(shù)包括一個常數(shù)表達式，該表達式通過使用操作符作用于列。在編寫包括有搜索參數(shù)的查詢時，可以增加查詢優(yōu)化器使用索引的機會。

(2).編寫較好的搜索參數(shù)

如果表達式?jīng)]有限制搜索，就可以認為該表達式?jīng)]有搜索參數(shù)。很多情況下，可以重新編寫查詢，把沒有搜索參數(shù)的表達式變?yōu)閹в兴阉鲄?shù)的表達式。
為了限制搜索，必須執(zhí)行以下操作：
·在查詢中指定Where子句
·檢驗Where子句是否限制了行的數(shù)目
·檢驗在每個引用該查詢的表中是否都存在表達式
·避免使用放在前面的通配符
下邊顯示了有效的搜索參數(shù)：


決定選擇性

在討論索引時，經(jīng)常使用的概念和術語是選擇性。在確定對哪些列進行索引和選擇要創(chuàng)建的索引類型時，必須考慮數(shù)據(jù)值的可選擇性如何。

(1).定義選擇性

選擇性是從通過查詢訪問或返回的行數(shù)所占從表中的行的百分比衍生而來的。查詢優(yōu)化器能夠確定Select、Update和Delete語句的選擇性。創(chuàng)建索引時，應該把它們創(chuàng)建在：
·聯(lián)接操作或Where子句經(jīng)常引用的列上
·具有高選擇性的數(shù)據(jù)上
在高選擇性中，搜索標準限制了返回的行數(shù)在所有行中占有比較低的百分比。返回一條記錄可獲得最高的選擇性。
在低選擇性中，按搜索標準將返回的行在表中所有行中占有比較高的百分比。

(2).評估選擇性

通過評估某個表中的特定查詢所返回的行的數(shù)目與表中行的總數(shù)目的關系，可以確定查詢的選擇性。

創(chuàng)建組合索引

組合索引指定多個列作為鍵值。在下面兩種情況下，可以創(chuàng)建組合索引：
·當兩列或多列最適合作為一個鍵進行搜索時
·如果查詢僅引用索引中的列時
例如，電話號碼目錄是使用組合索引的最好例子，該目錄是按姓氏組織的，在姓氏中，又是按名組織的，因為經(jīng)常存在具有相同姓氏的記錄。
在創(chuàng)建組合索引時，需要考慮以下事項和原則：
·最多可以有16個列組合到一個組合索引中。構成組合索引的列的總長度 不能超過900字節(jié)
·在組合索引中的所有列都必須來自于同一個表，除非該索引在視圖上創(chuàng)建
·先定義惟一性最高的列。在Create INDEX語句中定義的第一列稱作最高順序
·為了讓查詢優(yōu)化器使用組合索引，必須在查詢的Where子句中引用組合索引的第一列
·在(column1，column2)上的索引與在(column2，column1)上的索引不同-每一個索引都有不同的列序。有些列包含較高選擇性數(shù)據(jù)或返回行的百分比最低，這樣的列通常決定了列的順序。
·對具有多個列作為鍵的表來說，組合索引是很有用的
·使用組合索引可以增強查詢性能，并減少在表上創(chuàng)建的索引數(shù)目
·在同一列上的多個索引通常沒有多大用處

文章出處

[1].《數(shù)據(jù)庫程序設計-SQL Server2000 數(shù)據(jù)庫程序設計》微軟公司著,高等教育出版社,P326-P329,P340

該文章在 2011/3/13 0:19:40 編輯過