SQLServer索引結(jié)構(gòu)及其使用

1、SQLServer索引結(jié)構(gòu)及其使用、深入淺出理解索引結(jié)構(gòu)實際上，您可以把索引理解為一種特殊的目錄。微軟的SQLSERVER提供了兩種索引：聚集索引（clusteredindex，也稱聚類索引、簇集索引）和非聚集索引（nonclusteredindex，也稱非聚類索引、非簇集索引）。下面，我們舉例來說明一下聚集索引和非聚集索引的區(qū)別：其實，我們的漢語字典的正文本身就是一個聚集索引。比如，我們要查“安”字，就會很自然地翻開字典的前幾頁，因為“安”的拼音是“an”，而按照拼音排序漢字的字典是以英文字母“a”開頭并以“z”結(jié)尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”開頭的部分仍

2、然找不到這個字，那么就說明您的字典中沒有這個字；同樣的，如果查“張”字，那您也會將您的字典翻到最后部分，因為“張”的拼音是“zhang”。也就是說，字典的正文部分本身就是一個目錄,您不需要再去查其他目錄來找到您需要找的內(nèi)容。我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為“聚集索引”。如果您認識某個字，您可以快速地從自動中查到這個字。但您也可能會遇到您不認識的字，不知道它的發(fā)音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據(jù)“偏旁部首”查到您要找的字，然后根據(jù)這個字后的頁碼直接翻到某頁來找到您要找的字。但您結(jié)合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方

3、法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是“弩”字，頁面是390頁。很顯然，這些字并不是真正的分別位于“張”字的上下方，現(xiàn)在您看到的連續(xù)的“馳、張、弩”三字實際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結(jié)果，然后再翻到您所需要的頁碼。我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。通過以上例子，我們可以理解到什么是“聚集索引”和“非聚集索引”。進一步引申一下，我們可以很容易

4、的理解：每個表只能有一個聚集索引，因為目錄只能按照一種方法進行排序。二、何時使用聚集索引或非聚集索引面的表總結(jié)了何時使用聚集索引或非聚集索引（很重要）：動作描述使用聚集索引使用非聚集索引列經(jīng)常被分組掃E序應應返回某氾圍內(nèi)的數(shù)據(jù)應不應一個或極少小同值不應不應小數(shù)目的不同值應不應尢數(shù)目的不同值不應應頻鑒更新的列不應應外德列應應主德列應應頻鑒修改索引列不應應事實上，我們可以通過前面聚集索引和非聚集索引的定義的例子來理解上表。如：返回某范圍內(nèi)的數(shù)據(jù)一項。比如您的某個表有一個時間列，恰好您把聚合索引建立在了該列，這時您查詢2004年1月1日至2004年10月1日之間的全部數(shù)據(jù)時，這個速度就將是很快的，因

5、為您的這本字典正文是按日期進行排序的，聚類索引只需要找到要檢索的所有數(shù)據(jù)中的開頭和結(jié)尾數(shù)據(jù)即可；而不像非聚集索引，必須先查到目錄中查到每一項數(shù)據(jù)對應的頁碼，然后再根據(jù)頁碼查到具體內(nèi)容。三、結(jié)合實際，談索引使用的誤區(qū)理論的目的是應用。雖然我們剛才列出了何時應使用聚集索引或非聚集索引，但在實踐中以上規(guī)則卻很容易被忽視或不能根據(jù)實際情況進行綜合分析。下面我們將根據(jù)在實踐中遇到的實際問題來談一下索引使用的誤區(qū)，以便于大家掌握索引建立的方法。1、主鍵就是聚集索引這種想法筆者認為是極端錯誤的，是對聚集索引的一種浪費。雖然SQLSERVER默認是在主鍵上建立聚集索引的。通常，我們會在每個表中都建立一個ID列

6、，以區(qū)分每條數(shù)據(jù)，并且這個ID列是自動增大的，步長一般為1。我們的這個辦公自動化的實例中的列Gid就是如此。此時，如果我們將這個列設為主鍵，SQLSERVER會將此列默認為聚集索引。這樣做有好處，就是可以讓您的數(shù)據(jù)在數(shù)據(jù)庫中按照ID進行物理排序，但筆者認為這樣做意義不大。顯而易見，聚集索引的優(yōu)勢是很明顯的，而每個表中只能有一個聚集索引的規(guī)則，這使得聚集索引變得更加珍貴。從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最大好處就是能夠根據(jù)查詢要求，迅速縮小查詢范圍，避免全表掃描。在實際應用中，因為ID號是自動生成的，我們并不知道每條記錄的ID號，所以我們很難在實踐中用ID號來進行查詢。

7、這就使讓ID號這個主鍵作為聚集索引成為一種資源浪費。其次，讓每個ID號都不同的字段作為聚集索引也不符合“大數(shù)目的不同值情況下不應建立聚合索引”規(guī)則；當然，這種情況只是針對用戶經(jīng)常修改記錄內(nèi)容，特別是索引項的時候會負作用，但對于查詢速度并沒有影響。在辦公自動化系統(tǒng)中，無論是系統(tǒng)首頁顯示的需要用戶簽收的文件、會議還是用戶進行文件查詢等任何情況下進行數(shù)據(jù)查詢都離不開字段的是“日期”還有用戶本身的“用戶名”。通常，辦公自動化的首頁會顯示每個用戶尚未簽收的文件或會議。雖然我們的where語句可以僅僅限制當前用戶尚未簽收的情況，但如果您的系統(tǒng)已建立了很長時間，并且數(shù)據(jù)量很大，那么，每次每個用戶打開首頁的時

8、候都進行一次全表掃描，這樣做意義是不大的，絕大多數(shù)的用戶1個月前的文件都已經(jīng)瀏覽過了，這樣做只能徒增數(shù)據(jù)庫的開銷而已。事實上，我們完全可以讓用戶打開系統(tǒng)首頁時，數(shù)據(jù)庫僅僅查詢這個用戶近3個月來未閱覽的文件，通過“日期”這個字段來限制表掃描，提高查詢速度。如果您的辦公自動化系統(tǒng)已經(jīng)建立的2年，那么您的首頁顯示速度理論上將是原來速度8倍，甚至更快。在這里之所以提到“理論上”三字，是因為如果您的聚集索引還是盲目地建在ID這個主鍵上時，您的查詢速度是沒有這么高的，即使您在“日期”這個字段上建立的索引（非聚合索引）。下面我們就來看一下在1000萬條數(shù)據(jù)量的情況下各種查詢的速度表現(xiàn)（3個月內(nèi)的數(shù)據(jù)為25萬

9、條）：（1）僅在主鍵上建立聚集索引，并且不劃分時間段：Selectgid,fariqi,neibuyonghu,titlefromtgongwen用時：128470毫秒（即：128秒）（2）在主鍵上建立聚集索引，在fariq上建立非聚集索引：selectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqidateadd（day,-90,getdate（）用時：53763毫秒（54秒）（3）將聚合索引建立在日期列（fariqi）上:selectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqid

10、ateadd（day,-90,getdate（）用時：2423毫秒（2秒）雖然每條語句提取出來的都是25萬條數(shù)據(jù)，各種情況的差異卻是巨大的，特別是將聚集索引建立在日期列時的差異。事實上，如果您的數(shù)據(jù)庫真的有1000萬容量的話，把主鍵建立在ID列上，就像以上的第1、2種情況，在網(wǎng)頁上的表現(xiàn)就是超時，根本就無法顯示。這也是我摒棄ID列作為聚集索引的一個最重要的因素。得出以上速度的方法是：在各個select語句前加：declareddatetimesetd=getdate（）并在select語句后加：select語句執(zhí)行花費時間(毫秒)=datediff(ms,d,getdate()2、只要建立索引

11、就能顯著提高查詢速度事實上，我們可以發(fā)現(xiàn)上面的例子中，第2、3條語句完全相同，且建立索引的字段也相同；不同的僅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查詢速度卻有著天壤之別。所以，并非是在任何字段上簡單地建立索引就能提高查詢速度。從建表的語句中，我們可以看到這個有著1000萬數(shù)據(jù)的表中fariqi字段有5003個不同記錄。在此字段上建立聚合索引是再合適不過了。在現(xiàn)實中，我們每天都會發(fā)幾個文件，這幾個文件的發(fā)文日期就相同，這完全符合建立聚集索引要求的：“既不能絕大多數(shù)都相同，又不能只有極少數(shù)相同”的規(guī)則。由此看來，我們建立“適當”的聚合索引對于我們提高查詢

12、速度是非常重要的。3、把所有需要提高查詢速度的字段都加進聚集索引，以提高查詢速度上面已經(jīng)談到：在進行數(shù)據(jù)查詢時都離不開字段的是“日期”還有用戶本身的“用戶名”。既然這兩個字段都是如此的重要，我們可以把他們合并起來，建立一個復合索引（compoundindex）。很多人認為只要把任何字段加進聚集索引，就能提高查詢速度，也有人感到迷惑：如果把復合的聚集索引字段分開查詢，那么查詢速度會減慢嗎？帶著這個問題，我們來看一下以下的查詢速度（結(jié)果集都是25萬條數(shù)據(jù)）：（日期列fariqi首先排在復合聚集索引的起始列，用戶名neibuyonghu排在后列）：（1）selectgid,fariqi,neibuy

13、onghu,titlefromTgongwenwherefariqi2004-5-5查詢速度：2513毫秒（2）selectgid,fariqi,neibuyonghu,titlefromTgongwenwherefariqi2004-5-5andneibuyonghu=辦公室查詢速度：2516毫秒（3）selectgid,fariqi,neibuyonghu,titlefromTgongwenwhereneibuyonghu=辦公室查詢速度：60280毫秒從以上試驗中，我們可以看到如果僅用聚集索引的起始列作為查詢條件和同時用到復合聚集索引的全部列的查詢速度是幾乎一樣的，甚至比用上全部的復合索

14、引列還要略快（在查詢結(jié)果集數(shù)目一樣的情況下）；而如果僅用復合聚集索引的非起始列作為查詢條件的話，這個索引是不起任何作用的。當然，語句1、2的查詢速度一樣是因為查詢的條目數(shù)一樣，如果復合索引的所有列都用上，而且查詢結(jié)果少的話，這樣就會形成“索引覆蓋”，因而性能可以達到最優(yōu)。同時，請記住：無論您是否經(jīng)常使用聚合索引的其他列，但其前導列一定要是使用最頻繁的列。四、其他書上沒有的索引使用經(jīng)驗總結(jié)改善SQL語句很多人不知道SQL語句在SQLSERVER中是如何執(zhí)行的，他們擔心自己所寫的SQL語句會被SQLSERVER誤解。比如：select*fromtable1wherename=zhangsanand

15、tID10000和執(zhí)行:select*fromtable1wheretID10000andname=zhangsan一些人不知道以上兩條語句的執(zhí)行效率是否一樣，因為如果簡單的從語句先后上看，這兩個語句的確是不一樣，如果tID是一個聚合索引，那么后一句僅僅從表的10000條以后的記錄中查找就行了；而前一句則要先從全表中查找看有幾個name=zhangsan的，而后再根據(jù)限制條件條件tID10000來提出查詢結(jié)果。事實上，這樣的擔心是不必要的。SQLSERVER中有一個“查詢分析優(yōu)化器”，它可以計算出where子句中的搜索條件并確定哪個索引能縮小表掃描的搜索空間，也就是說，它能實現(xiàn)自動優(yōu)化。雖然查

16、詢優(yōu)化器可以根據(jù)where子句自動的進行查詢優(yōu)化，但大家仍然有必要了解一下“查詢優(yōu)化器”的工作原理，如非這樣，有時查詢優(yōu)化器就會不按照您的本意進行快速查詢。在查詢分析階段，查詢優(yōu)化器查看查詢的每個階段并決定限制需要掃描的數(shù)據(jù)量是否有用。如果一個階段可以被用作一個掃描參數(shù)(SARG)，那么就稱之為可優(yōu)化的，并且可以利用索引快速獲得所需數(shù)據(jù)。SARG的定義：用于限制搜索的一個操作，因為它通常是指一個特定的匹配，一個值得范圍內(nèi)的匹配或者兩個以上條件的AND連接。形式如下：列名操作符常數(shù)或變量或常數(shù)或變量操作符列名列名可以出現(xiàn)在操作符的一邊，而常數(shù)或變量出現(xiàn)在操作符的另一邊。如：Name張三價格500

17、05000價格Name=張三and價格5000如果一個表達式不能滿足SARG的形式，那它就無法限制搜索的范圍了，也就是SQLSERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件。所以一個索引對于不滿足SARG形式的表達式來說是無用的。介紹完SARG后，我們來總結(jié)一下使用SARG以及在實踐中遇到的和某些資料上結(jié)論不同的經(jīng)驗：1、Like語句是否屬于SARG取決于所使用的通配符的類型女如namelike張，,這就屬于SARG而：namelike%張，就不屬于SARG。原因是通配符%在字符串的開通使得索引無法使用。2、or會引起全表掃描Name-張三5and價格5000符號SARG，而

18、：Name張三，or價格5000則不符合SARG。使用or會引起全表掃描。3、非操作符、函數(shù)引起的不滿足SARG形式的語句不滿足SARG形式的語句最典型的情況就是包括非操作符的語句，女如NOT、！=、！、！、NOTEXISTS、NOTIN、NOTLIKE等，另外還有函數(shù)。下面就是幾個不滿足SARG形式的例子：ABS(價格)5000Namelike%三5有些表達式，如：WHERE價格*25000SQLSERVER也會認為是SARG，SQLSERVER會將此式轉(zhuǎn)化為：WHERE價格2500/2但我們不推薦這樣使用，因為有時SQLSERVER不能保證這種轉(zhuǎn)化與原始表達式是完全等價的。4、IN的作用相

19、當與OR語句：Select*fromtable1wheretidin(2,3)和Select*fromtable1wheretid=2ortid=3是一樣的，都會引起全表掃描，如果tid上有索引，其索引也會失效。5、盡量少用NOT6、exists和in的執(zhí)行效率是一樣的很多資料上都顯示說，exists要比in的執(zhí)行效率要高，同時應盡可能的用notexists來代替notin。但事實上，我試驗了一下，發(fā)現(xiàn)二者無論是前面帶不帶not,二者之間的執(zhí)行效率都是一樣的。因為涉及子查詢，我們試驗這次用SQLSERVER自帶的pubs數(shù)據(jù)庫。運行前我們可以把SQLSERVER的statisticsI/O狀態(tài)

20、打開：selecttitle,pricefromtitleswheretitle_idin(selecttitle_idfromsaleswhereqty30)該句的執(zhí)行結(jié)果為：表sales。掃描計數(shù)18,邏輯讀56次，物理讀0次，預讀0次。表titles。掃描計數(shù)1，邏輯讀2次，物理讀0次，預讀0次。selecttitle,pricefromtitleswhereexists(select*fromsaleswheresales.title_id=titles.title_idandqty30)第二句的執(zhí)行結(jié)果為：表sales。掃描計數(shù)18,邏輯讀56次，物理讀0次，預讀0次。表titles

21、。掃描計數(shù)1，邏輯讀2次，物理讀0次，預讀0次。我們從此可以看到用exists和用in的執(zhí)行效率是一樣的。7、用函數(shù)charindex()和前面加通配符的LIKE執(zhí)行效率一樣前面，我們談到，如果在LIKE前面加上通配符,那么將會引起全表掃描，所以其執(zhí)行效率是低下的。但有的資料介紹說，用函數(shù)charindex()來代替LIKE速度會有大的提升，經(jīng)我試驗，發(fā)現(xiàn)這種說明也是錯誤的：selectgid,title,fariqi,readerfromtgongwenwherecharindex(刑偵支隊,reader)0andfariqi2004-5-5用時：7秒，另外：掃描計數(shù)4，邏輯讀7155次，物

22、理讀0次，預讀0次。selectgid,title,fariqi,readerfromtgongwenwherereaderlike%+刑偵支隊+%andfariqi2004-5-5用時：7秒，另外：掃描計數(shù)4，邏輯讀7155次，物理讀0次，預讀0次。8、union并不絕對比or的執(zhí)行效率高我們前面已經(jīng)談到了在where子句中使用or會引起全表掃描，一般的，我所見過的資料都是推薦這里用union來代替or。事實證明，這種說法對于大部分都是適用的。selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi=2004-9-16o

23、rgid9990000用時：68秒。掃描計數(shù)1，邏輯讀404008次，物理讀283次，預讀392163次。selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi=2004-9-16unionselectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwheregid9990000用時：9秒。掃描計數(shù)8，邏輯讀67489次，物理讀216次，預讀7499次?？磥?，用union在通常情況下比用or的效率要高的多。但經(jīng)過試驗，筆者發(fā)現(xiàn)如果or兩邊的查詢列是一樣的話，那么用union則

24、反倒和用or的執(zhí)行速度差很多，雖然這里union掃描的是索引，而or掃描的是全表。selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi=2004-9-16orfariqi=2004-2-5用時：6423毫秒。掃描計數(shù)2，邏輯讀14726次，物理讀1次，預讀7176次。selectgid,fariqi,neibuyonghu,reader,titlefromTgongwenwherefariqi=2004-9-16unionselectgid,fariqi,neibuyonghu,reader,titlefromTgon

25、gwenwherefariqi=2004-2-5用時：11640毫秒。掃描計數(shù)8，邏輯讀14806次，物理讀108次，預讀1144次。9、字段提取要按照“需多少、提多少”的原則，避免“select*”我們來做一個試驗：selecttop10000gid,fariqi,reader,titlefromtgongwenorderbygiddesc用時：4673毫秒selecttop10000gid,fariqi,titlefromtgongwenorderbygiddesc用時：1376毫秒selecttop10000gid,fariqifromtgongwenorderbygiddesc用時：8

26、0毫秒由此看來，我們每少提取一個字段，數(shù)據(jù)的提取速度就會有相應的提升。提升的速度還要看您舍棄的字段的大小來判斷。10、count(*)不比count(字段)慢某些資料上說：用*會統(tǒng)計所有列，顯然要比一個世界的列名效率低。這種說法其實是沒有根據(jù)的。我們來看：selectcount(*)fromTgongwen用時：1500毫秒selectcount(gid)fromTgongwen用時：1483毫秒selectcount(fariqi)fromTgongwen用時：3140毫秒selectcount（title）fromTgongwen用時：52050毫秒從以上可以看出，如果用count（*）和

27、用count（主鍵）的速度是相當?shù)?，而count（*）卻比其他任何除主鍵以外的字段匯總速度要快，而且字段越長，匯總的速度就越慢。我想，如果用count（*），SQLSERVER可能會自動查找最小字段來匯總的。當然，如果您直接寫count（主鍵）將會來的更直接些。11、orderby按聚集索引列排序效率最高我們來看：（gid是主鍵，fariqi是聚合索引列）：selecttop10000gid,fariqi,reader,titlefromtgongwen用時：196毫秒。掃描計數(shù)1，邏輯讀289次，物理讀1次，預讀1527次。selecttop10000gid,fariqi,reader,ti

28、tlefromtgongwenorderbygidasc用時：4720毫秒。掃描計數(shù)1，邏輯讀41956次，物理讀0次，預讀1287次。selecttop10000gid,fariqi,reader,titlefromtgongwenorderbygiddesc用時：4736毫秒。掃描計數(shù)1，邏輯讀55350次，物理讀10次，預讀775次實現(xiàn)小數(shù)據(jù)量和海量數(shù)據(jù)的通用分頁顯示存儲過程建立一個Web應用，分頁瀏覽功能必不可少。這個問題是數(shù)據(jù)庫處理中十分常見的問題。經(jīng)典的數(shù)據(jù)分頁方法是:ADO紀錄集分頁法，也就是利用ADO自帶的分頁功能（利用游標）來實現(xiàn)分頁。但這種分頁方法僅適用于較小數(shù)據(jù)量的情形，

29、因為游標本身有缺點：游標是存放在內(nèi)存中，很費內(nèi)存。游標一建立，就將相關的記錄鎖住，直到取消游標。游標提供了對特定集合中逐行掃描的手段，一般使用游標來逐行遍歷數(shù)據(jù)，根據(jù)取出數(shù)據(jù)條件的不同進行不同的操作。而對于多表和大表中定義的游標（大的數(shù)據(jù)集合）循環(huán)很容易使程序進入一個漫長的等待甚至死機。更重要的是，對于非常大的數(shù)據(jù)模型而言，分頁檢索時，如果按照傳統(tǒng)的每次都加載整個數(shù)據(jù)源的方法是非常浪費資源的?，F(xiàn)在流行的分頁方法一般是檢索頁面大小的塊區(qū)的數(shù)據(jù)，而非檢索所有的數(shù)據(jù)，然后單步執(zhí)行當前行。最早較好地實現(xiàn)這種根據(jù)頁面大小和頁碼來提取數(shù)據(jù)的方法大概就是“俄羅斯存儲過程”。這個存儲過程用了游標，由于游標的局

30、限性，所以這個方法并沒有得到大家的普遍認可。后來，網(wǎng)上有人改造了此存儲過程，下面的存儲過程就是結(jié)合我們的辦公自動化實例寫的分頁存儲過程：CREATEprocedurepagination1(pagesizeint,-頁面大小，如每頁存儲20條記錄pageindexint-當前頁碼)assetnocountonbegindeclareindextabletable(idintidentity(1,1),nidint)-定義表變量declarePageLowerBoundint-定義此頁的底碼declarePageUpperBoundint-定義此頁的頂碼setPageLowerBound=(pa

31、geindex-1)*pagesizesetPageUpperBound=PageLowerBound+pagesizesetrowcountPageUpperBoundinsertintoindextable(nid)selectgidfromTGongwenwherefariqidateadd(day,-365,getdate()orderbyfariqidescselectO.gid,O.mid,O.title,O.fadanwei,O.fariqifromTGongwenO,indextabletwhereO.gid=t.nidandt.idPageLowerBoundandt.id”

32、或“200于是就有了如下分頁方案：selecttop頁大小*fromtable1whereid（selectmax（id）from（selecttop（頁碼-1）*頁大?。﹊dfromtable1orderbyid）asT）orderbyid在選擇即不重復值，又容易分辨大小的列時，我們通常會選擇主鍵。下表列出了筆者用有著1000萬數(shù)據(jù)的辦公自動化系統(tǒng)中的表，在以GID（GID是主鍵，但并不是聚集索引。）為排序列、提取gid,fariqi,title字段，分別以第1、10、100、500、1000、1萬、10萬、25萬、50萬頁為例，測試以上三種分頁方案的執(zhí)行速度：(單位：毫秒)旬碼方案1方案2

33、方案31603076104616631001076720130500540129438310001711047025010000247964500140100000383264228315532500002814012872023305000001216861278467168從上表中，我們可以看出，三種存儲過程在執(zhí)行100頁以下的分頁命令時，都是可以信任的，速度都很好。但第一種方案在執(zhí)行分頁1000頁以上后，速度就降了下來。第二種方案大約是在執(zhí)行分頁1萬頁以上后速度開始降了下來。而第三種方案卻始終沒有大的降勢，后勁仍然很足。在確定了第三種分頁方案后，我們可以據(jù)此寫一個存儲過程。大家知道SQL

34、SERVER的存儲過程是事先編譯好的SQL語句，它的執(zhí)行效率要比通過WEB頁面?zhèn)鱽淼腟QL語句的執(zhí)行效率要高。下面的存儲過程不僅含有分頁方案，還會根據(jù)頁面?zhèn)鱽淼膮?shù)來確定是否進行數(shù)據(jù)總數(shù)統(tǒng)計。-獲取指定頁的數(shù)據(jù)：CREATEPROCEDUREpagination3tblNamevarchar(255),-表名strGetFieldsvarchar(1000)=*,-需要返回的列fldNamevarchar(255)=,-排序的字段名PageSizeint=10,-頁尺寸PageIndexint=1,-頁碼doCountbit=0,-返回記錄總數(shù),非0值則返回OrderTypebit=0,-設置

35、排序類型,非0值則降序strWherevarchar(1500)=-查詢條件(注意:不要加where)ASdeclarestrSQLvarchar(5000)-主語句declarestrTmpvarchar(110)-臨時變量declarestrOrdervarchar(400)-排序類型ifdoCount!=0beginifstrWhere!=setstrSQL=selectcount(*)asTotalfrom+tblName+where+strWhereelsesetstrSQL=selectcount(*)asTotalfrom+tblName+end-以上代碼的意思是如果doCoun

36、t傳遞過來的不是0,就執(zhí)行總數(shù)統(tǒng)計。以下的所有代碼都是doCount為0的情況：elsebeginifOrderType!=0beginsetstrTmp=(selectmaxsetstrOrder=orderby+fldName+ascendifPageIndex=1beginifstrWhere!=setstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+where+strWhere+strOrderelsesetstrSQL=selecttop+str(PageSize)+strGetFields+from+tblName+s

37、trOrder-如果是第一頁就執(zhí)行以上代碼，這樣會加快執(zhí)行速度聚集索引的重要性和如何選擇聚集索引在上一節(jié)的標題中，筆者寫的是：實現(xiàn)小數(shù)據(jù)量和海量數(shù)據(jù)的通用分頁顯示存儲過程。這是因為在將本存儲過程應用于“辦公自動化”系統(tǒng)的實踐中時，筆者發(fā)現(xiàn)這第三種存儲過程在小數(shù)據(jù)量的情況下，有如下現(xiàn)象：1、分頁速度一般維持在1秒和3秒之間。2、在查詢最后一頁時，速度一般為5秒至8秒，哪怕分頁總數(shù)只有3頁或30萬頁。雖然在超大容量情況下，這個分頁的實現(xiàn)過程是很快的，但在分前幾頁時，這個13秒的速度比起第一種甚至沒有經(jīng)過優(yōu)化的分頁方法速度還要慢，借用戶的話說就是“還沒有ACCESS數(shù)據(jù)庫速度快”，這個認識足以導致用戶放棄使用您開發(fā)的系統(tǒng)。筆者就此分析了一下，原來產(chǎn)生這種現(xiàn)象的癥結(jié)是如此的簡單，但又如此的重要：排序的字段不是聚集索引！本篇文章的題目是：“查詢優(yōu)化及分頁算法方案”。筆者只所以把“查詢優(yōu)