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

數(shù)據(jù)

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

使用索引的指導(dǎo)原則

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

(1).要索引的列

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

(2).不需要索引的列

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

選擇合適的聚集索引

在為每個(gè)表選擇聚集索引時(shí)，需考慮表是如何使用的。

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

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

(2).排序

時(shí)常因報(bào)表而排序的表、因聚合而分組的表或搜索特定范圍數(shù)據(jù)的表，在排序的列上建立聚集索引是很有好處的。當(dāng)需要返回表的　很多列并且非聚集索引又不起作用時(shí)，使用聚集索引很有用處。例如，郵件列表以郵編建立聚集索引就很有用，因?yàn)楸仨毚蛴〔⒁灾付ǖ捻樞驊?yīng)用郵件的標(biāo)簽。

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

SQL Server使用聚集索引值作為每個(gè)非聚集索引中的行標(biāo)識(shí)符。聚集索引值可以在表結(jié)構(gòu)中重復(fù)使用很多次。
為了避免因?yàn)榇蟮木奂饕古c它們相關(guān)的非聚集索引變得更大或更慢，可以執(zhí)行以下操作：
·限制在聚集索引中列的數(shù)目
·通過使用varchar數(shù)據(jù)類型來代替char數(shù)據(jù)類型，以減少字符的平均數(shù)
·盡可能地使用最小的數(shù)據(jù)類型，例如用tinyint代替int

使用索引以支持查詢

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

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

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

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

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


決定選擇性

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

(1).定義選擇性

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

(2).評(píng)估選擇性

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

創(chuàng)建組合索引

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

文章出處

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

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