索引的重要性

索引的重要性數(shù)據(jù)庫性能優(yōu)化中索引絕對是一個重量級的因素，可以說，索引使用不當，其它優(yōu)化措施將毫無意義。聚簇索弓l（ClusteredIndex）和非聚簇索引（Non-ClusteredIndex）最通俗的解釋是:聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，而對非聚簇索引的索引順序與數(shù)據(jù)物理排列順序無關。舉例來說，你翻到新華字典的漢字''爬”那一頁就是P開頭的部分，這就是物理存儲順序（聚簇索弓）；而不用你到目錄，找到漢字“爬”所在的頁碼，然后根據(jù)頁碼找到這個字（非聚簇索弓）。下表給出了何時使用聚簇索引與非聚簇索引:動作使用聚簇索引使用非聚簇索引列經(jīng)常被分組排序應應返回某范圍內(nèi)的數(shù)據(jù)應不應一個或極少不同值不應不應小數(shù)目的不同值應不應大數(shù)目的不同值不應應頻繁更新的列不應應外鍵列應應主鍵列應應頻繁修改索引列不應應聚簇索弓的唯一性正式聚簇索引的順序就是數(shù)據(jù)的物理存儲順序，所以一個表最多只能有一個聚簇索引，因為物理存儲只能有一個順序。正因為一個表最多只能有一個聚簇索引，所以它顯得更為珍貴，一個表設置什么為聚簇索引對性能很關鍵。初學者最大的誤區(qū)：把主鍵自動設為聚簇索引因為這是SQLServer的默認主鍵行為，你設置了主鍵，它就把主鍵設為聚簇索引，而一個表最多只能有一個聚簇索引，所以很多人就把其他索引設置為非聚簇索引。這個是最大的誤區(qū)。甚至有的主鍵又是無意義的自動增量字段，那樣的話Clusteredindex對效率的幫助，完全被浪費了。剛才說到了，聚簇索引性能最好而且具有唯一性，所以非常珍貴，必須慎重設置。一般要根據(jù)這個表最常用的SQL查詢方式來進行選擇，某個字段作為聚簇索引，或組合聚簇索引，這個要看實際情況。事實上，建表的時候，先需要設置主鍵，然后添加我們想要的聚簇索引，最后設置主鍵,SQLServer就會自動把主鍵設置為非聚簇索引（會自動根據(jù)情況選擇）。如果你已經(jīng)設置了主鍵為聚簇索引，必須先刪除主鍵，然后添加我們想要的聚簇索引，最后恢復設置主鍵即可。記住我們的最終目的就是在相同結(jié)果集情況下，盡可能減少邏輯IO。我們先從一個實際使用的簡單例子開始。一個簡單的表：CREATETABLE[dbo].[Table1]([ID][int]IDENTITY(1,1)NOTNULL,[Data1][int]NOTNULLDEFAULT((0)),[Data2][int]NOTNULLDEFAULT((0)),[Data3][int]NOTNULLDEFAULT((0)),[Name1][nvarchar](50)NOTNULLDEFAULT(''),[Name2][nvarchar](50)NOTNULLDEFAULT(''),[Name3][nvarchar](50)DEFAULT(''),[DTAt][datetime]NOTNULLDEFAULT(getdate()))復制代碼來點測試數(shù)據(jù)(10w條)：?declare@i intset@i=1while@i< 100000begininsertintoTable1([Data1],[Data2],[Data3],[Name1],[Name2],[Name3])values(@i,2*@i,3*@i,CAST(@iASNVARCHAR(50)),CAST(2*@iASNVARCHAR(50)),CAST(3*@iASNVARCHAR(50)))set@i=@i+1endupdatetable1setdtat=DateAdd(s,data1,dtat)復制代碼打開查詢分析器的IO統(tǒng)計和時間統(tǒng)計：SETSTATISTICSIOON;SETSTATISTICSTIMEON;復制代碼顯示實際的“執(zhí)行計劃”：Query^reject-cclsWindowCommunityHelpCanrecticr?OpenServerinCbje匚tExplorerAlt-FSSpecifyVaIliesfo「-emplate=araitieters...Ctrl+Shift+MExecute巴J=arseCtrl+F5■CancelExecutingQueryAlt+BreakgJDisplayEstimatedExecuticr=1日rCtrl-.聊AnalyzeQueryirDatabaseErgireTuringAdviser/DesignQueryirEditor...Ctrl-Shift-QIncludeActu日丨Executicr=lanCtrl-M呀』IncludeCFentStatisticsShift+Alt-SResetClientstatistics；回SQ.CMCMedeResults-c卜QueryCpticns...我們最常用的SQL查詢是這樣的：?SELECT*FROMTable1WHEREData1=2ORDERBYDTAtDESC;復制代碼先在Tablel設主鍵ID,系統(tǒng)自動為該主鍵建立了聚簇索引。然后執(zhí)行該語句，結(jié)果是：

Table'Tablel'.Scancount1,logicalreads911,physicalreads0,read-aheadreads0,loblogicalreads0,lobphysicalreads0,lobread-aheadreads0.SQLServerExecutionTimes:CPUtime=16ms,elapsedtime=7ms.復制代碼Results||Messages|6Executionplaneery1:Scerycost(r已Lativetot■匕已batch): 13D%SZLZ2T*FROM:Tati已二：WHERZ:Data!：=31::三二三僉3Y：3TAt：3ZSCa SELECT

Cast;:O%n]a SELECT

Cast;:O%n]SortCast:7ClusteredIndexScan[IndexTest].[dbo]-[Tablel]?[PK_Tabl.?CasXz93%然后我們在Data1和DTat字段分別建立非聚簇索引：CREATENONCLUSTEREDINDEX[N_Data1]ON[dbo].[Table1]?([Data1]ASC)WITH(SORT_IN_TEMPDB=OFF,DROP_EXISTING=OFF,IGNORE_DUP_KEY=OFF,ONLINE=OFF)ON[PRIMARY]CREATENONCLUSTEREDINDEX[N_DTat]ON[dbo].[Table1]?([DTAt]ASC)WITH(SORT_IN_TEMPDB=OFF,DROP_EXISTING=OFF,IGNORE_DUP_KEY=OFF,ONLINE=OFF)ON[PRIMARY]復制代碼再次執(zhí)行該語句，結(jié)果是：Table'Table1'.Scancount1,logicalreads5,physicalreads0,read-aheadreads0,loblogicalreads0,lobphysicalreads0,lobread-aheadreads0.SQLServerExecutionTimes:CPUtime=0ms,elapsedtime=39ms.

□Results||□Results||1^Messages|fiExecutionplanScery二：Sceryco31(r已Lativetothebatch):二二■：■生SZLZST*FROM:Tati已二：WHERZ:Data!：=310R3ZR3￥ :3TAt:3Z5CSELECTCost:0%可以看到設立了索引反而沒有任何性能的提升而且消耗的時間更多了，繼續(xù)調(diào)整。然后我們刪除所有非聚簇索引，并刪除主鍵，這樣所有索引都刪除了。建立組合索引Datal和DTAt,最后加上主鍵：CREATECLUSTEREDINDEX[C_Data1_DTat]ON[dbo].[Table1]([Data1]ASC,[DTAt]ASC)WITH(SORT_IN_TEMPDB=OFF,DROP_EXISTING=OFF,IGNORE_DUP_KEY=OFF,ONLINE=OFF)ON[PRIMARY]復制代碼再次執(zhí)行語句：Table'Table1'.Scancount1,logicalreads3,physicalreads0,read-aheadreads0,loblogicalreads0,lobphysicalreads0,lobread-aheadreads0.SQLServerExecutionTimes:CPUtime=0ms,elapsedtime=1ms.復制代碼可以看到只有聚簇索引seek了，消除了indexscan和nestedloop,而且執(zhí)行時間也只有1ms,達到了最初優(yōu)化的目的。組合索引小結(jié)小結(jié)以上的調(diào)優(yōu)實踐，要注意聚簇索引的選擇。首先我們要找到我們最多用到的SQL查詢，像本例就是那句類似的組合條件查詢的情況，這種情況最好使用組合聚簇索引，而且最多用到的字段要放在組合聚簇索引的前面，否則的話就索引就不會有好的效果，看下例：3ZLZCZ* TdbleL DTAt= ();<ihinResults為Messages1ExecutionplanQuery1:Queryco31(relativetottiebatch):1QO%SELECT*FROMTablelWHEREDTAt=getdate();SELECTCast;:0SELECTCast;:0%NestedLoops七(InnerJoin)Cast：:14%IndejcScan[IndejcTest].[dho]?[Tablel].[PK_TabL.?Cost:85%ClusteredIridesSeek[IndekTest].[dho]-[Tablel]-[CJ3ataL..Cost：z1%查詢條件落在組合索引的第二個字段上，引起了indexscan，效果很不好，執(zhí)行時間是：Table'Tablel'.Scancount1,logicalreads238,physicalreads0,read-aheadreads0,loblogicalreads0,lobphysicalreads0,lobread-aheadreads0.SQLServerExecutionTimes:CPUtime=16ms,elapsedtime=22ms.而如果僅查詢條件是第一個字段也沒有問題，因為組合索引最左前綴原則，實踐如下：Table'Tablel'.Scancount1,logicalreads3,physicalreads0,read-aheadreads0,loblogicalreads0,lobphysicalreads0,lobread-aheadreads0.SQLServerExecutionTimes:CPUtime=0ms,elapsedtime=1ms.復制代碼從中可以看出，最多用到的字段要放在組合聚簇索引的前面。Indexseek為什么比Indexscan好？索引掃描也就是遍歷B樹，而seek是B樹查找直接定位。Indexscan多半是出現(xiàn)在索引列在表達式中。數(shù)據(jù)庫引擎無法直接確定你要的列的值，所以只能掃描整個索引進行計算。indexseek就要好很多?數(shù)據(jù)庫引擎只需要掃描幾個分支節(jié)點就可以定位到你要的記錄。回過來，如果聚集索引的葉子節(jié)點就是記錄，那么ClusteredIndexScan就基本等同于fulltablescan。一些優(yōu)化原則1、 缺省情況下建立的索引是非聚簇索引，但有時它并不是最佳的。在非群集索引下，數(shù)據(jù)在物理上隨機存放在數(shù)據(jù)頁上。合理的索引設計要建立在對各種查詢的分析和預測上。一般來說：有大量重復值、且經(jīng)常有范圍查詢（>,<，>=,<=）和orderby、groupby發(fā)生的列，可考慮建立群集索引；經(jīng)常同時存取多列，且每列都含有重復值可考慮建立組合索引；組合索引要盡量使關鍵查詢形成索引覆蓋，其前導列一定是使用最頻繁的列。索引雖有助于提高性能但不是索引越多越好，恰好相反過多的索引會導致系統(tǒng)低效。用戶在表中每加進一個索引，維護索引集合就要做相應的更新工作。2、 ORDERBY和GROUPBY使用ORDERBY和GROUPBY短語，任何一種索引都有助于SELECT的性能提高。3、 多表操作在被實際執(zhí)行前，查詢優(yōu)化器會根據(jù)連接條件，列出幾組可能的連接方案并從中找出系統(tǒng)開銷最小的最佳方案。連接條件要充份考慮帶有索引的表、行數(shù)多的表；內(nèi)外表的選擇可由公式：外層表中的匹配行數(shù)*內(nèi)層表中每一次查找的次數(shù)確定，乘積最小為最佳方案。4、任何對列的操作都將導致表掃描，它包括數(shù)據(jù)庫函數(shù)、計算表達式等等，查詢時要盡可能將操作移至等號右邊。5、IN、OR子句常會使用工作表，使索引失效。如果不產(chǎn)生大量重復值，可以考慮把子句拆開。拆開的子句中應該包含索引。Sql的優(yōu)化原則2：1、 只要能滿足你的需求，應盡可能使用更小的數(shù)據(jù)類型：例如使用MEDIUMINT代替INT2、 盡量把所有的列設置為NOTNULL,如果你要保存NULL，手動去設置它，而不是把它設為默認值。3、 盡量少用VARCHAR、TEXT、BLOB類型4、 如果你的數(shù)據(jù)只有你所知的少量的幾個。最好使用ENUM類型使用SQLServerProfiler找出數(shù)據(jù)庫中性能最差的SQL首先打開SQLServerProfiler:然后點擊工具欄“NewTrace”，使用默認的模板，點擊RUN。也許會有扌報錯："onlyTrueTypefontsaresupported.ThereidnotaTrueTypefont'。不用怕，點擊Tools菜單->Options,重新選擇一個字體例如Vendana即可。（這個是微軟的一個bug）運行起來以后，SQLServerProfiler會監(jiān)控數(shù)據(jù)庫的活動，所以最好在你需要監(jiān)控的數(shù)據(jù)庫上多做些操作。等覺得差不多了，點擊停止。然后保存trace結(jié)果到文件或者table。這里保存到Table：在菜單、'File"-''Saveas'-“Tracetable",例如輸入一個master數(shù)據(jù)庫的新的table名：profileTrace，保存即可。找到最耗時的SQL：usemasterselect*fromprofiletraceorderbydurationdesc;復制代碼找到了性能瓶頸，接下來就可以有針對性的一個個進行調(diào)優(yōu)了。

對使用SQLServerProfiler的更多信息可以參考：/KB/database/DiagnoseProblemsSQLServer.aspx使用SQLServerDatabaseEngineTuningAdvisor數(shù)據(jù)庫引擎優(yōu)化顧問使用上述的SQLServerProfiler得到了trace還有一個好處就是可以用到這個優(yōu)化顧問。用它可以偷點懶，得到SQLServer給您的優(yōu)化顧問，例如這個表需要加個索引什么的…首先打開數(shù)據(jù)庫引擎優(yōu)化顧問：5MicrosoftSQLServerManagementStudioCorrectObjectExplorermasterJ.Ne//Query匣鶴忸蟄」FileEdi:Vie-?/Query^rejee:-oclsWindowCoimnunityHelpCorrectObjectExplorermasterJ.Ne//Query匣鶴忸蟄」SQ_Ser/erPre;i