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

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

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

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

4、二、何時使用募集索弓或非聚集索引下面的表總結(jié)了何時使用聚集索引或非聚集索引（很重要）:動作描述使用聚集索 引使用非聚集索引列經(jīng)常被分組排序應(yīng)應(yīng)返回某范圍內(nèi)的數(shù)據(jù)應(yīng)不應(yīng)一個或極少不同值不應(yīng)不應(yīng)小數(shù)目的不同值應(yīng)不應(yīng)大數(shù)目的不同值不應(yīng)應(yīng)頻繁更新的列不應(yīng)應(yīng)外鍵列應(yīng)應(yīng)主鍵列應(yīng)應(yīng)頻繁修改索弓列不應(yīng)應(yīng)事實上，我們可以通過前面聚集索引和非聚集索弓的定義的例？來理解上表。如：返回某范圍內(nèi)勺數(shù)據(jù)一項 比如您 的某個表有一個時間列，恰好您把聚合索引建立在了該列，這時您查詢2004年1月1日至2004年10月1日之間的全部 數(shù)據(jù)時 這個速度就將是很快勺，因為您的這本字典正文是按日期進行排序的 聚類索弓只需要找到要檢索

5、的所有數(shù)據(jù)中 的開頭和結(jié)商據(jù)即可；而不像非聚集索引必須先查到目錄中查到每一項數(shù)據(jù)對應(yīng)的頁碼，然后再根據(jù)頁碼查到具體內(nèi) 容。三、結(jié)合實際，談索弓使用的誤區(qū)理論的目的是應(yīng)用。雖然我們岡才列出了何時應(yīng)使用聚集索弓或非聚集索引，但在實踐中以上規(guī)則卻很容易被忽視或 不能根據(jù)實際情況進行綜合分析。下面我們將根據(jù)在實踐中遇到的實際問題來談一下索引使用的誤區(qū) 以便于大家掌握索 引建立勺方法1、主鍵就是聚集索引這種想法筆者認為是極端錯誤的 是對聚集索引的一種浪費。雖然SQL SERVER認是在主鍵上建立聚集索引血通常，我們會在每個表中都建立一個D歹U，以區(qū)分每條數(shù)據(jù)，并且這個D列是自動增大的，步長一般為1。我們

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

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

8、瀏覽過了，這樣做只能徒增數(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萬條):僅在主鍵上建立聚集索引，并且不劃分時間段：Select gid,fariqi

9、,neibuyonghu,titlfe2om tgongwen用時：128470毫秒(即：128秒)在主鍵上建立聚集索引，在fariq上建立非聚集索引：select gid,fariqi,neibuyonghu,titlfrom Tgongwenwhere fariqi dateadd(day,-90,getdate()用時：53763毫秒(54秒)將聚合索弓建立在0期列(fariq)上：select gid,fariqi,neibuyonghu,titlfe:om Tgongwenwhere fariqi dateadd(day,-90,getdate()用時：2423毫秒(2秒)雖然每條語

10、句提取出來的都是25萬條數(shù)據(jù)，各種情況的差異卻是巨大的，特別是將聚集索弓建立在0期列時勺差異。 事實上 如果您的數(shù)據(jù)庫真的有1000萬容量的話 把主鍵建立在ID列上 就像以上勺第1、2種情況 在網(wǎng)頁上勺表現(xiàn) 就是超時根本就無法顯示。這也是我摒棄ID列作為聚集索引的一個最重要勺因秦得出以上速度的方法是：在各個sel ect語向前加：declare d datetimeset d=getdate()并在 selects句后 加：select 語句執(zhí)行花費時間(毫秒)=datediff(ms,d,getdate()2、只要建立索引就能顯著提高查詢速度事實上，我們可以發(fā)現(xiàn)上面的例？中，第2、3條語句完

11、全相同，且建立索引的字段也相同；不同的僅是前者在farqi 字段上建立的是非聚合索引，后者在此字建立的是聚合索引，但查詢速度卻有著天壤之別。所以 并非是在(壬何字段 上簡單也建立索弓就能提高查詢速度從建表的語句中，我們可以看到這個有著1000萬數(shù)據(jù)的表中farqi字段有5003個不同記錄 在此字段上建立聚合索 引是再合適不過了。在現(xiàn)實中，我們每天者會發(fā)幾個文件，這幾個文件的發(fā)文日期就相同 這完全符合建立聚集索弓要求 的：“既不能絕大多數(shù)都相同，又不能只有極少數(shù)相同的規(guī)則由此看來我們建立適當?shù)木酆纤饕缬谖覀兲岣卟樵?速度是非常重要的。3、把所有需要提高查詢速度的字段都加進聚集如I，以提高查詢速

12、度上面已經(jīng)談到在進行數(shù)據(jù)查詢時都離不開字段的是日期還有用戶本身的、用戶名。既然這兩個字段都是如此的重 要，我們可以把他們合并起來建立一個復(fù)合索引(compoundindex)。彳艮多人認為只要把任何字段加雌集如I，就能提高查詢速度 也有人感到迷惑：如果把復(fù)合的聚集索引字段分開查 詢，那吆查詢速度會減慢馬？帶著這個問題 我們來看一下以下的查詢速度 結(jié)果集都是25萬條數(shù)據(jù))：(日期列farqi 首先排在復(fù)合聚集索引的起始列，用戶名nebuyonghu排在后列：select gid,fariqi,neibuyonghu,title fTgongwenwhere fariqi2004-5-5查詢速度：

13、2513毫秒select gid,fariqi,neibuyonghu,title fTgongwenwhere fariqi2004-5-5and neibuyonghu=辦公室查詢速度：2516毫秒select gid,fariqi,neibuyonghu,title fTgongwenwhere neibuyonghu=辦公室查詢速度：60280毫秒從以上試驗中，我們可以看到如果僅用聚集索引的起始列作為查詢條件和同時用到復(fù)合聚集索引的全部列的查詢速度 是幾乎一樣的 甚至比用上全部的復(fù)合索弓列還要略快(在查詢結(jié)果集數(shù)目一樣的情況下)；而如果僅用復(fù)合聚集索引的 非起始列作為查詢條件的話，這個

14、索引是不起任何作用的。當然，語句1、2的查詢速如樣是因為查詢的條目數(shù)一樣， 如果復(fù)合索引的所有列者用上，而且查詢結(jié)果少的話，這樣就會形成索引覆蓋，因而性能可以達到最優(yōu)。同時，請記住 無論您是否經(jīng)常使用聚合索引的其他列，但其前導(dǎo)列一定要是使用最頻繁的列四、其佛上沒有的索引使曜驗總結(jié)1、用聚合索引比用不是聚合索引的主鍵速度快下面是實例語句：(都是提取25萬條數(shù)掘selectgid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere fariqi=2004-9-16使用時間：3326毫秒select gid,fariqi,neibuyonghu,reade

15、r,titleomTgongwenwhere gid2004-1-1用時：6343毫秒提取100萬條）selectgid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere fariqi2004-6-6用時：3170毫秒提取50萬條）selectgid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere fariqi=2004-9-16用時：3326毫秒和上句的結(jié)果一模一樣如果采集勺數(shù)量一樣那所燈號和等于號是一樣的）select gid,fariqi,neibuyonghu,reader,titleom T

16、gongwenwhere fariqi2004-1Tand fariqi2004-1-1order by fariqi用時：6390毫秒select gid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere fariqi 10000和執(zhí)行：select * from tablel where tID 10000 aiHine=zhangsan一些人不知道以上兩條語句的執(zhí)行效率是否一樣，因為如果簡單的從語句先后上看，這兩個語句勺確是不頂，如果 tID是一個聚合索引那么后一句僅僅從表的10000條以后的記錄中查找就行了；而前一句則要先從全表中查找看有幾

17、個 name=zhangsar的，而后再根據(jù)限制條件條件10000來提出查詢結(jié)果事實上，這樣的擔心是不必要勺。SQL SERVER中有一個查詢分析優(yōu)化器，它可以計算出where子句中勺搜索條件 并確定哪個索引能縮小表日描的搜索空間，也就是說，它能實現(xiàn)自動優(yōu)化。雖然查詢優(yōu)化器可以根據(jù)where子句自動勺進行查詢優(yōu)化，但大家仍然有必要了解一下查詢優(yōu)化器的工作原理，如 非這樣 有時查詢優(yōu)化器就會不按照您的本意進行快速查詢。在查詢分析階段，查詢優(yōu)化器查看查詢的每個階段并決定限制需要掃描勺致?lián)渴欠裼杏?。如果一個階段可以被用作 一個掃描參數(shù)（SARG），那么就稱之為可優(yōu)化的，并且可以利用索引快速獲得所需

18、數(shù)據(jù)。SARG的定義 用于限制搜索的一個操作 因為它通常是指個特定白匹配，一個值得范圍內(nèi)的匹配或者兩個以上條 件的AND連接。形式如下：列名操作符 常數(shù)或變量 或帝數(shù)或變量 操作符列名列名可以出現(xiàn)在操作符勺一邊 而常數(shù)或變量出現(xiàn)在操作符的另一邊。如：Name=張三價 格50005 000價格Name=張三and 價格5000如果一個表達式不官滿足，人日6的形式，那它就無法限制搜索的范圍了，也就是SQL SERVER必須對每一行都判斷它 是否滿足WHERE子句中的所有條件所以一個索彌寸于不滿足SARG形式的表達武來說是無用的。介紹完SARG后，我們來總結(jié)一下使用SARG以及在實踐中遇到的和某些資

19、料上結(jié)論不同勺經(jīng)驗1、Like語句是否屬于SARG取決于所使用的通配符的類型如：name like張，這就屬于SARG而：name like張，就不屬于SARG,原因是通配符在字符串的開通使得索引無法使用2、or會引起全表掃描Name=張三and價格5000符號SARG而：Name=三, or價格5000則不符合SARG使用or會引起全表 掃描。3、非操作符、函數(shù)引起勺不滿足SARG形式的語句不滿足SARG形式的語句最典型勺情況就是包括非操作符的語句，如：NOT、!=. 、！、！、NOT EXISTS、NO T IN、NOT LIKE等，另外還有函數(shù)下面就是幾個不滿足，人日6形式的例子ABS（

20、價 格）5000Name like 三有些表達式，如：WHERE 價格*25000SQL SERVER也會認為是SARG SQL SERVE會將此式?；癁椋篧HERE 價格2500/2但我們不推薦這樣使用，因為有時SQL SERVER不能保證這種轉(zhuǎn)化與原始表達式是完全等價的。4、IN的作用相當與OR 語句：Select * from tablel where tid in (2,3)和Select * from table1 where tid=2 otrid=3是一樣的，者會引起全表掃描如果tid上有索引，其索引也會失效。5、盡量少用NOT6、exists和in的執(zhí)行效率是一樣的彳艮多資料上

21、都顯示說exSts要比n的執(zhí)行效率要高，同時應(yīng)盡可能的用not exsts來代替notn。但事實上我試 驗了一下，發(fā)現(xiàn)二者無論是前面帶不帶not二者之間的執(zhí)行效率都是一樣血因為涉及？查詢，我們試驗這次用SQL S ERVER自帶的pubs數(shù)據(jù)庫運行前我們可以把SQL SERVER的statisticsI/O狀態(tài)打開select title,pricefrom titles where title_id (rselect title_id from sales wh(qte y30)該句的執(zhí)行結(jié)果為表sales。掃描計數(shù)18,邏輯讀56次，物里讀0次，預(yù)讀0次。表titles。掃描計數(shù)1，邏輯讀

22、2次，物理讀0次，預(yù)賣0次。select title,pricfrom titleswhere exists(select * from saleswhere sales.title_id=titles.title_aindl qty30)第二句勺執(zhí)行結(jié)果為：表sales。掃描計數(shù)18,邏輯讀56次，物里讀0次，預(yù)讀0次。表titles。掃描計數(shù)1，邏輯讀2次，物理讀0次，預(yù)賣0次。我們從此可以看到用exists和用n的執(zhí)行效率是一樣的。7、用函數(shù)charindex和前面加通配符的LIKE執(zhí)行效率一樣前面，我們談到，如果在LKE前面加上通配符，那么將會引起全表掃描 所以其執(zhí)行效率是低下的。但有

23、的資料 介紹說用函數(shù)charindex來代替LKE速度會有大的提升經(jīng)我試驗發(fā)現(xiàn)這種說明也是錯誤的：selectgid,title,fariqi,reader from tgongwenwhere charindex (刑偵支M,reader)0 and fariqi2004-5-5用時：7秒，另外：掃描計數(shù)4，邏輯讀7155次，物理讀0次，預(yù)賣0次。selectgid,title,fariqi,reader from tgongwenwhere reader like %+刑偵支隊+ % andariqi2004-5-5用時：7秒，另外：掃描計數(shù)4，邏輯讀7155次，物理讀0次，預(yù)賣0次。8、

24、unOn并不絕對比0的執(zhí)行效率高我們前面已經(jīng)談到了在where子句中使用。會引起全表掃描 一般勺，我所見過的資料都是推薦這里用unOn來代 替。r。事實證明，這種說法對于大部分都是適用勺。select gid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere fariqi=2004-9-16 or gid9990000用時：68秒。掃描計數(shù)1，邏輯賣404008次，物理讀283次，預(yù)賣392163次。selectgid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere fariqi=2004-9-16

25、unionselect gid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere gid9990000用時：9秒。掃描計數(shù)8,邏輯賣67489次，物理讀216次，預(yù)讀7499次?？磥?，用unOn在通常情況下比用or的效率要高的多。但經(jīng)過試驗，筆者發(fā)現(xiàn)如果or兩邊的查詢列是一樣的話，那么用unOn則反倒和用or的執(zhí)行速度差很多，雖然這里u nion掃描的是索引，而or掃描的是全表select gid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere fariqi=2004-9-16 or fariqi=2

26、004-2-5用時：6423毫秒。掃描計數(shù)2，邏輯讀14726次，物理讀1次，預(yù)讀7176次。selectgid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere fariqi=2004-9-16 unionselectgid,fariqi,neibuyonghu,reader,titleom Tgongwenwhere fariqi=2004-2-5用時：11640毫秒。掃描計數(shù)8，邏輯讀14806次，物理讀108次，預(yù)讀1144次。9、字段提取要按照需多少、提多少的原則避免select *我們來做一個試驗：select top 10000gid

27、,fariqi,reader,title from tgongwen order by gid desc用時：4673毫秒select top 10000gid,fariqi,title from tgongwen order by gid desc用時：1376毫秒select top 10000gid,fariqi from tgongwen order gb.yi desc用時：80毫秒由此看來 我們每少提虹個字段，數(shù)據(jù)的提取速度就會有相應(yīng)的提升。提升勺速度還要看您舍棄的字段的大小來判 斷。10、count(*不 比count字段)慢某些資料上說 用*會統(tǒng)計所有列 顯然要比個世界勺列名效

28、率低這種說法其實是沒有根據(jù)血 我們來看select count(*) fronTgongwen用時：1500毫秒select count(gid) from Tgongwen用時：1483毫秒select count(fariqi) from Tgongwen用時：3140毫秒select count(title) from Tgongwen用時：52050毫秒從以上可以看出，如果用count(*和用count住鍵)的速度是相當?shù)模鴆ount(*卻比其他壬何除主鍵以外的字段匯總 速度要快，而且字段越長 匯總的速度就越曼。我想，如果用count(*) SQL SERVER可能會自動查找最小字段

29、來匯總 的。當然，如果您直接寫coun鍵)將會來勺更直接些。11、order by按聚集索弓列排序效率最高我們來看（gid是主鍵，farqi是聚合索引列）:select top 10000gid,fariqi,reader,title from tgongwen用時：196毫秒。掃描計數(shù)1,邏輯賣289次，物理讀1次，預(yù)讀1527次。select top 10000gid,fariqi,reader,title from tgongwen order by gid asc用時：4720毫秒。掃描計數(shù)1,邏輯讀41956次，物理賣0次，預(yù)賣1287次。select top 10000gid,fa

30、riqi,reader,title from tgongwen order by gid desc用時：4736毫秒。掃描計數(shù)1,邏輯讀55350次，物理賣10次，預(yù)賣775次。select top 10000gid,fariqi,reader,title from tgongwen order by fariqi asc用時：173毫秒。掃描計數(shù)1，邏輯讀290次，物理賣0次，預(yù)賣0次。select top 10000gid,fariqi,reader,title from tgongwen order by fariqi desc用時：156毫秒。掃描計數(shù)1，邏輯讀289次，物理賣0次，預(yù)

31、賣0次。從以上我們可以看出 不排序的速度以及邏輯讀次數(shù)都是和orderby聚集索弓列的速度是相當?shù)?但這些者叱“or der by非聚集索引歹列的查詢速度是快得多的同時，按照某個字段進行排序的時候，無論是正序還是到序，速度是基本相當?shù)摹?2、高效的TOP事實上，在查詢和提取超大容量的數(shù)據(jù)集時，影響數(shù)據(jù)庫響應(yīng)時間的最大因素不是數(shù)據(jù)查找，而是物理的V0操作 如：select top 10* from （select top 10000gid,fariqi,title from tgongwenwhere neibuyonghu=辦公室order bygid desc）as aorder bygid

32、 asc這條語句 從理論上講，整條語句的執(zhí)行時間應(yīng)訝匕子句的執(zhí)行時間長，但事實相反 因為，子句執(zhí)行后返回的是1 0000條記錄 而整條語句僅返回10條語句，所以影向數(shù)據(jù)庫響應(yīng)時間最大勺因素是物理I/O操作。而限制物理I/O操作 此處的最有效方法之一就是使用TOP關(guān)鍵詞了。TOP關(guān)鍵詞是SQL SERVER中經(jīng)過系統(tǒng)優(yōu)化過勺一個用來提取前幾條或 前幾個百分比數(shù)據(jù)的詞。經(jīng)筆者在實踐中的應(yīng)用發(fā)現(xiàn)TOP確實很好用，效率也艮高。但這個詞在另外一個大型數(shù)據(jù)庫O RACLE中卻沒有，這不能說不是一個遺憾，雖然在。中可以用其他方法（如：rownumber來解決 在以后的關(guān)于 、實現(xiàn)千萬級數(shù)據(jù)的分頁顯示存儲過程

33、勺勺討論中，我們就將用到TOP這個關(guān)鍵司。至此為止 我們上面討論了如何實現(xiàn)從大容量的數(shù)據(jù)庫中快速地查詢出您所需要的數(shù)據(jù)方法。當然，我們介紹的這些 方法都是軟方法，在實踐中，我們還要考慮各種硬咽素，如：網(wǎng)絡(luò)性能、服務(wù)器的性能、操作系統(tǒng)的性能 甚至網(wǎng)卡、 交換機等。（待續(xù).）SQL Server索引結(jié)構(gòu)及其使用三）日期：2006-07-15來源：vc矢口識庫作者：freedk|字體：大中小實現(xiàn)小嬲量和每量數(shù)據(jù)的通用分頁顯示存陪過程建立一個Web應(yīng)用，分頁瀏覽功能必不可少。這個問題是數(shù)據(jù)庫處理中十分常見的問題。經(jīng)典的數(shù)據(jù)分頁方法是:ADO紀錄集分頁法 也就是利用ADO自帶的分頁功能(利用游標)來實現(xiàn)

34、分頁。但這種分頁方法僅適用于較小數(shù)據(jù)量的情 形，因為游標本身有缺點 游標是存放在內(nèi)存中，很費內(nèi)存。游標一建立，就將相關(guān)的記錄鎖住，直到取消游標 游標提 供了對特定集合中逐行掃描的手段，一般使用游標來遂亍遍歷數(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ù)的方法為概就是俄羅

35、斯存儲過程。這個存儲過程用了游標，由 于游標勺局限性，所以這個方法并沒有得至大家的普遍認可。后來，有人改造了此存儲過呈，下面的存儲過程就是結(jié)合我們的辦公自動化實例寫的分頁存睹過呈：CREATE procedure pagination】(pagesize int, 夾面大小，如每頁存儲20條記錄pageindex int-當前頁碼)asset nocountonbegindeclare indextable table(id iidtentity(1,1),nid int) 定-義表變量declare PageLowerBound in定義此頁的底碼declare PageUpperBound

36、 in定義此頁的頂碼set PageLowerBound=(pageindex-1)*pagesizeset PageUpperBound=PageLowerBound+pagesizeset rowcount PageUpperBoundinsert into indextable(nid) selecjid from TGongwenwhere fariqi dateadd(day,-365,getdate() ordefabyqi descselect O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,indextable t

37、 where O.gid=t.nid artiidPageLowerBoundand t.id或200于是就有了如下分頁方案select top 頁大小 * from table1 where id (select max(id) from (select top(頁碼-1)*頁大小)idfrom table1 order by id) as T ) order byid在選擇即不重復(fù)值，又容易分辨大小的列時，我們通常會選擇主鍵。下表列出了筆者用有著1000萬數(shù)據(jù)的辦公自動 化系列的表 在以GD (GD是主鍵，但并不是聚集索引。)為排序列、提取gd,farqi,title字段，分別頃1、10.

38、 10 0、500、100。1萬、10萬、25萬、50萬頁為例 測試以上三種分頁方案的執(zhí)行速度：單位 毫秒)頁碼方案1方案2方案31603076104616631001076720130500540129438310001711047025010000247964500140100000383264228315532500002814012872023305000001216861278467168從上表中，我們可以看出，三種儡過程在擁亍100頁以下的分頁命令時，都是可以信佃的速度都很好。但第一種 方案在執(zhí)行分頁1000頁以上后，速度就降了下來 第二種方案大約是在執(zhí)行分頁1萬頁以上后速度開始降

39、了下來。而第 三種方案卻始終沒有大的降勢 后勁仍然很足。在確定了第三種分頁方案后，我們可以據(jù)此寫一個存儲過程。大家知道SQL SERVER的存儲溯是事先編譯好的SQ L語句，它的執(zhí)行效率要比通過WEB頁面?zhèn)鱽淼腟QL語句的執(zhí)行效率要高下面的存儲過程不僅含有分頁方案，還會根 據(jù)頁面?zhèn)鱽淼膮?shù)來確定是否進行數(shù)據(jù)總數(shù)統(tǒng)計。-獲取指定頁的數(shù)據(jù)CREATE PROCEDURE paginationstblName varchar(255), 表名strGetFields varchar(1000) = *,需要返回的歹 UfldName varchar(255) = ,排-序的字段名PageSizein

40、t = 10,-頁尺寸PageIndex int= 1,-頁碼doCount bit = 0,-返回記錄總數(shù)，非0值則返回OrderType bit= 0,-設(shè)置排序類型，非0值則降序strWherevarchar(1500)= 查詢條件(注意：不要加 where) ASdeclare strSQL varchar(5000)主語句declare strTmp varchar(110) 臨寸變量declare strOrder varchar(40-排序類型 if doCount!= 0 begin if strWhere !=set strSQL= select count(*) aTota

41、l from + tblName + wherestrWhere elseset strSQL= select count(*) aTotal from + tblName + end-以上代碼的意思是如果doCoun傳遞過來的不是0，就執(zhí)行總數(shù)統(tǒng)計。以下的所有代碼IKdoCount為0的情況else begin if OrderType !=0 beginset strTmp= (select maxset strOrder = order by + fldName +asc endif PageIndex = 1beginif strWhere !=set strSQL= select t

42、op + str(PageSizHe*) + strGetFields+ from + tblName+ where + strWhere + + strOrder elseset strSQL= select top + str(PageSizHe*) + strGetFields+ from + tblName + +trOrder-如果是第一頁就擁亍以上代碼這樣會加快執(zhí)行速度end else begin-以下代馬賦予了 strSQL以真正執(zhí)行的SQL代碼set strSQL= select top + str(PageSiz+s) + strGetFields+ from + tblNa

43、me + Where + fldName + +用strTmp + (fldName+ )from (select top + str(PageIndex-1)*PageSize) + fldName+ from + tblName + + strOr(+et) as tblTmp)strOrder if strWhere != set strSQL= select top + str(PageSizHe*) + strGetFields+ from + tblName + Where + fldName + +用strTmp + ( + fldName + ) from (seletop +

44、 str(PageIndex-1)*PageSize) + + fldName + from + tblName + where + strWh+re + strOrder+ ) as tblTmp) and+ strWhere + + strOrder end end exec (strSQL) GO上面的這個存儲過程是一個通用的存儲過程 其注釋已寫在其中了。在大數(shù)據(jù)量的情況下 特另是在查詢最后幾頁的 時候，查詢時間一般不會超過9秒；而用其他存儲過程 在實踐中就會導(dǎo)致超由所以這個存儲過程非常適用于大容量數(shù) 據(jù)庫的查詢。筆者希望能夠通過對以上存睹過呈的解析，能給大家?guī)硪欢ǖ膯⑹荆⒔o工作帶來一定的效率提升，同時 希望同亍提出更優(yōu)秀的實時數(shù)據(jù)分頁算法。(待續(xù).)SQL Server索引結(jié)構(gòu)及撇用四)日期:2006-07-15來源：日期:2006-07-15來源：vc矢口識庫作者：freedk字體:大中小聚集索弓的重要性和如何選擇聚集索引在上一節(jié)勺標題中，筆者寫的是 實現(xiàn)小數(shù)據(jù)量和海量數(shù)據(jù)的通用分頁顯示存儲過程 這是因為在將本存儲過程應(yīng)用 于辦公自動化系統(tǒng)的實踐中時，筆者發(fā)現(xiàn)這第三種存儲過程在小數(shù)據(jù)量的情況下，有