SQLServer索引結(jié)構(gòu)及其使用（一 一、深入淺出理解索 。微軟的SQLSERVER提供了 索引和非索引的區(qū)別： 在字典的前部。如果您翻完了所有以“a”開頭的部分仍然找不到這個(gè)字，那 來(lái)找到您需要找的內(nèi)容。把這 稱為“索引”。的字，而需要去根據(jù)“偏旁部首”查到您要找的字，然后根據(jù)這個(gè)字后的頁(yè)碼 字的排序并不是真正的正文的排序方法，比如您查“張”字，可以看到在”三字實(shí)際上就是他們?cè)诜撬饕械呐判颍亲值湔闹械淖衷诜?中的結(jié)果，然后再翻到您所需要的頁(yè)碼。把這種 正文純粹是正文的排序方式稱為“非索引”。通過(guò)以上例子，可以理解到“索引”和“非索引”。進(jìn)一步引申一下，可以很容易的理解：每個(gè)表只能有一個(gè)索引，因?yàn)槎⒑螘r(shí)使用索引或非索下面的表總結(jié)了何時(shí)使 索引或 索引（很重要使用索使用非引應(yīng)應(yīng)應(yīng)應(yīng)應(yīng)應(yīng)應(yīng)應(yīng)應(yīng)應(yīng)應(yīng)事實(shí)上，可以通過(guò)前面索引和非索引的定義的例子來(lái)理解上的，聚類索引只需要找到要檢索的所有數(shù)據(jù)中的開頭和結(jié)尾數(shù)據(jù)即可；而不像非索引，必須先查到 到具體內(nèi)容。三、結(jié)合實(shí)際，談索引使用的理論的目的是應(yīng)用。雖然剛才列出了何時(shí)應(yīng)使用索引或非索1、主鍵就是索 SERVER默認(rèn)是在主鍵上建立 如此。此時(shí)，如果這個(gè)列設(shè)為主鍵，SQLSERVER會(huì)將此列默認(rèn)為索規(guī)則，這使得索引變得更加珍貴。ID號(hào)是自動(dòng)生成的，并不知道每條記錄的ID號(hào)，所以很難在實(shí)踐中用樣做只能徒增數(shù)據(jù)庫(kù)的開銷而已。事實(shí)上，完全可以讓用戶打開系統(tǒng)首頁(yè)時(shí)，數(shù)據(jù)庫(kù)僅僅查詢這個(gè)用戶近3個(gè)月來(lái)未閱覽的文件，通過(guò)“日期”這個(gè)字限2的首頁(yè)顯示速度理論上將是原來(lái)速度8倍，甚至更快。下各種查詢的速度表現(xiàn)（325）：僅在主鍵上建立索引，并且不劃分時(shí)間段：Selectgid,fariqi,neibuyonghu,titlefromtgongwen用時(shí)：128470毫秒（即：128秒）selectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqi>dateadd(day,-90,getdate())selectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqi>dateadd(day,-90,getdate())selectdeclare@ddatetimeset@d=getdate()select事實(shí)上，可以發(fā)現(xiàn)上面的例子中，第2、3條語(yǔ)句完全相同，且建立索從建表的語(yǔ)句中可以看到這個(gè)有著1000萬(wàn)數(shù)據(jù)的表中fariqi字段有要求的：“既不能絕大多數(shù)都相同，又不能只有極少數(shù)相同”的規(guī)則。由此看來(lái)，建立“適當(dāng)”的聚合索引對(duì)于提高查詢速度是非常重要的。一個(gè)復(fù)合索引（compoundindex）。問(wèn)題，來(lái)看一下以下的查詢速度（結(jié)果集都是25萬(wàn)條數(shù)據(jù)）：（日期列fariqi首先排在復(fù)合索引的起始列，用戶名neibuyonghu排在后列）：selectgid,fariqi,neibuyonghu,titlefromTgongwenwhereselectgid,fariqi,neibuyonghu,titlefromwherefariqi>''2004-5-5''andselectgid,fariqi,neibuyonghu,titlefromTgongwenwhere引列還要略快（在查詢結(jié)果集數(shù)目一樣的情況下）；而如果僅用復(fù)合索引請(qǐng)記?。簾o(wú)論您是否經(jīng)常使用聚合索引的其他列，但其前導(dǎo)列一定要是使用最四、其他書上沒(méi)有的索引使用經(jīng)selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwhereselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwhere2、用聚合索引比用一般的主鍵作orderby時(shí)速度快，特別是在小數(shù)據(jù)量情況下selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenorderbyfariqiselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenorderby用時(shí)多；而數(shù)據(jù)量如果很大的話，如10萬(wàn)以上，則二者的速度差別不明顯。selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwhereselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwhereselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwhereselectgid,fariqi,neibuyonghu,reader,titlefromwherefariqi>''2004-1-1''fariqi<''2004-6-selectgid,fariqi,neibuyonghu,reader,titlefromwherefariqi>''2004-1-1''orderbyselectgid,fariqi,neibuyonghu,reader,titlefromwherefariqi<''2004-1-1''orderby用時(shí)：6453毫秒做的工作。過(guò)多的索引甚至?xí)?dǎo)致索引碎片。二、改善SQLSQLSQLSERVER誤解。比如：select*fromtable1wherename=''zhangsan''andtID>select*fromtable1wheretID>10000andtID是一個(gè)聚合索引，那么后一句僅僅10000幾個(gè)name=''zhangsan''的，而后再根據(jù)限制條件條件tID>10000來(lái)提出查where雖然查詢優(yōu)化器可以根據(jù)where個(gè)值得范圍內(nèi)的匹配或者兩個(gè)以上條件的AND連接。形式如下：Name=’’and價(jià)格如果一個(gè)表達(dá)式不能滿足SARG的形式，那它就無(wú)法限制搜索的范圍了，也就是SQLSERVER必須對(duì)每一行都判斷它是否滿足WHERE子句中的所有條件。所以一個(gè)索引對(duì)于不滿足SARG形式的表達(dá)式來(lái)說(shuō)是無(wú)用的。介紹完SARG后，來(lái)總結(jié)一下使用SARG以及在實(shí)踐中遇到的和某些資料如：namelike‘張%’，這就屬于SARG而：namelikeSARG2、orName=’’and價(jià)格>5000符號(hào)SARG，而：Name=’’or價(jià)格>5000則不符合SARG。使用or會(huì)引起全表掃描。面就是幾個(gè)不滿足SARG形式的例子：ABS(NamelikeWHERE*2>5000WHERE價(jià)格Select*fromtable1wheretidin和Select*fromtable1wheretid=2or6、existsin很多資料上都顯示說(shuō)，exists要比in的執(zhí)行效率要高，同時(shí)應(yīng)盡可能的用notexistsnotin。但事實(shí)上，我試驗(yàn)了一下，發(fā)現(xiàn)二者無(wú)論是前面帶not，二者之間的執(zhí)行效率都是一樣的。因?yàn)樯婕白硬樵?，試?yàn)這次用selecttitle,pricefromtitleswheretitle_idin(selecttitle_idfromsaleswhereqty>30)''sales''。掃描計(jì)數(shù)18，5600''titles''。掃描計(jì)數(shù)1，200selecttitle,pricefromwhereexists(select*fromwheresales.title_id=titles.title_idand''sales''。掃描計(jì)數(shù)18，5600''titles''1，200從此可以看到用existsin的執(zhí)行效率是一樣的。selectgid,title,fariqi,readerfromwherecharindex(''刑偵支隊(duì)'',reader)>0and用時(shí)：74，715500selectgid,title,fariqi,readerfromwherereaderlikeand用時(shí)：74，715500前面已經(jīng)談到了在where子句中使用or會(huì)引起全表掃描，一般的，我所見過(guò)的資料都是這里用union來(lái)代替or。事實(shí)證明，這種說(shuō)法對(duì)于大部selectgid,fariqi,neibuyonghu,reader,titlefromwherefariqi=''2004-9- selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwhereselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwhere用時(shí)：98，674892167499看來(lái)，用unionor的效率要高的多。orunion則反orunionor掃描的是全selectgid,fariqi,neibuyonghu,reader,titlefromwherefariqi=''2004-9-16''orfariqi=''2004-2-selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwhereselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwhereselecttop10000gid,fariqi,reader,titlefromtgongwenorderbygidselecttop10000gid,fariqi,titlefromtgongwenorderbygidselecttop10000gid,fariqifromtgongwenorderbygid說(shuō)法其實(shí)是沒(méi)有根據(jù)的。來(lái)看：selectcount(*)fromselectcount(gid)fromselectcount(fariqi)fromselectcount(title)fromcount(*)count(主鍵)的速度是相當(dāng)?shù)?，而速度就越慢。，如果用count(*)，SQLSERVER可能會(huì)自動(dòng)查找最小字selecttop10000gid,fariqi,reader,titlefrom用時(shí)：196128911527selecttop10000gid,fariqi,reader,titlefromtgongwenorderbygidascselecttop10000gid,fariqi,reader,titlefromtgongwenorderbygiddescselecttop10000gid,fariqi,reader,titlefromtgongwenorderbyfariqi用時(shí)：1731，29000selecttop10000gid,fariqi,reader,titlefromtgongwenorderbyfariqi用時(shí)：1561，邏輯讀289次，物理讀0次，預(yù)讀0次。從以上可以看出，不排序的速度以及邏輯讀次數(shù)都是和“orderby素不是數(shù)據(jù)查找，而是物理的I/0操作。如：selecttop10*fromselecttop10000gid,fariqi,titlefromtgongwenwhereneibuyonghu=''''orderbygiddesc)asaorderbygidasc此處的最有效方法之一就是使用TOP了。TOP是SQLSERVER中經(jīng)TOPrownumber）來(lái)解決。在以后的關(guān)于“實(shí)現(xiàn)千萬(wàn)級(jí)數(shù)據(jù)的分頁(yè)顯示過(guò)程”的中，就將用到TOP這個(gè)。三、實(shí)現(xiàn)小數(shù)據(jù)量和海量數(shù)據(jù)的通用分頁(yè)顯示過(guò)建立一個(gè)Web應(yīng)用，分頁(yè)瀏覽功能必不可少。這個(gè)問(wèn)題是數(shù)據(jù)庫(kù)處理中十分常見的問(wèn)題。經(jīng)典的數(shù)據(jù)分頁(yè)方法是:ADO集分頁(yè)法，也就是利用ADO自情形，因?yàn)橛螛?biāo)本身有缺點(diǎn)：游標(biāo)是存放在內(nèi)存中，很費(fèi)內(nèi)存。游標(biāo)立，般使用游標(biāo)來(lái)逐行遍歷數(shù)據(jù)，根據(jù)取出數(shù)據(jù)條件的不同進(jìn)行不同的操作。而對(duì)CREATEprocedure(@pagesizeint,--頁(yè)面大小，如每頁(yè)20條記@pageindexint)setnocountondeclare@indextabletable(idintidentity(1,1),nidint)declare@PageLowerBoundint--定義此頁(yè)的底碼declare@PageUpperBoundint--定義此頁(yè)的頂碼set@PageLowerBound=(@pageindex-1)*@pagesizesetrowcount@PageUpperBoundinsertinto@indextable(nid)selectgidfromwherefariqi>dateadd(day,-365,getdate())orderbyselectO.gid,O.mid,O.title,O.fadanwei,O.fariqifromTGongwenO,@indextabletwhereO.gid=t.nidandandt.id<=@PageUpperBoundorderbysetnocount以上過(guò)程運(yùn)用了SQLSERVER的技術(shù)――表變量。應(yīng)該說(shuō)這個(gè)的表變量寫成臨時(shí)表：CREATETABLE#TempSQLSERVERpublishnmSELECTTOPm-n+1FROMpublishWHERE(idNOTIN(SELECTTOPn-1FROMpublish))idpublish等到后來(lái)，我在作辦公自動(dòng)化系統(tǒng)（ASP.NET+C#＋SQLSERVER）的時(shí)候，忽然過(guò)程。于是我就滿網(wǎng)上找這篇文章，沒(méi)想到，文章還沒(méi)找到，卻找到了一CREATEPROCEDUREpagination2@SQLnVARCHAR(4000),SQL@Pageint,@RecsPerPageint,@IDVARCHAR(255),ID@SortVARCHAR(255)DECLARE@StrSET@Str=''SELECTTOP''+CAST(@RecsPerPageASVARCHAR(20))+''*FROM(''+@SQL+'')TWHERET.''+@ID+''NOTIN(SELECTTOP''+CAST((@RecsPerPage*(@ORDERBY''+@SortPRINTEXECsp_ExecuteSql@StrSELECTTOP頁(yè)大小FROMTable1WHERE(IDNOTIN(SELECTTOP頁(yè)大小*頁(yè)數(shù)idFROM表ORDERORDERBYSELECTTOP頁(yè)大小FROMTable1WHEREnotb.id=a.id)orderby即，用notexists來(lái)代替notin，但前面已經(jīng)談過(guò)了，二者的執(zhí)行效率實(shí)際上是沒(méi)有區(qū)別的。既便如此，用TOP結(jié)合NOTIN的這個(gè)方法還是比用游大的記錄集，而面也已經(jīng)提到了TOP的優(yōu)勢(shì)，通過(guò)TOP即可實(shí)現(xiàn)對(duì)數(shù)在分頁(yè)算法中，影響查詢速度的關(guān)鍵因素有兩點(diǎn)：TOP和NOTIN。TOP可以提高的查詢速度，而NOTIN會(huì)減慢的查詢速度，所以要提高整個(gè)分頁(yè)算法的速度，就要徹底改造NOTIN，同其他方法來(lái)替代它。知道，幾乎任何字段，都可以通過(guò)max(字段)或min(字提取某個(gè)字段中的最大或最小值，所以如果這個(gè)字段不重復(fù)，那么就可以利用這些在這里，可以用操作符“>”或“<”號(hào)來(lái)完成這個(gè)使命，使查詢語(yǔ)句符合SARG形式。如：Selecttop10*fromtable1whereselecttop*fromtable1where(selectmax(id)(selecttop((頁(yè)碼-1)*頁(yè)大小)idfromtable1orderbyid)as)orderby在選擇即不重復(fù)值，又容易分辨大小的列時(shí)，通常會(huì)選擇主鍵。下表GI（GI但并不是索引。）gid,fariqi,title110、100、500、1000、1萬(wàn)、10萬(wàn)、25萬(wàn)、50萬(wàn)頁(yè)為例，測(cè)試以上三種分頁(yè)方頁(yè)都是可以信任的，速度都很好。但第案在執(zhí)行分頁(yè)1000頁(yè)以上后，速度就降了下來(lái)。第二種方案大約是在執(zhí)行分頁(yè)1萬(wàn)頁(yè)以上后速度開始降了下來(lái)。而第CREATEPROCEDUREpagination3@tblNamevarchar(255),@strGetFieldsvarchar(1000)''*'',@fldNamevarchar(255)='''',--排序的字段名@PageSizeint=10,--頁(yè)尺寸@PageIndexint1,@doCountbit0,0@OrderTypebit0,0@strWherevarchar(1500)where)declare@strSQLvarchar(5000)--主語(yǔ)句declare@strTmpvarchar(110)--臨時(shí)變量declare@strOrdervarchar(400)if@doCount!=0if@strWhereset@strSQL="selectcount(*)asTotalfrom["+@tblName+"]whereset@strSQL="selectcount(*)asTotalfrom["+@tblName+"]"所有代碼都是@doCount為0的情況：if@OrderType!=0set@strTmp="<(selectset@strOrder="orderby["+@fldName+"]set@strTmp=">(selectset@strOrder="orderby["+@fldName+"]asc"if@PageIndex=1if@strWhere!=set@strSQL="selecttop"+str(@PageSize)+""+@strGetFields+"from["+@tblName+"]where"+@strWhere+""set@strSQL="selecttop"+str(@PageSize)+""+@strGetFields+"from["+@tblName+"]"+@strOrderset@strSQL="selecttop"+str(@PageSize)+""+@strGetFields+"from+@tblName+"]where["+@fldName+"]"+@strTmp+"(["+@fldName+from(selecttop"+str((@PageIndex-1)*@PageSize)+"["+@fldName+"]from[