（一(yī / yì ／yí)）深入淺出(chū)理解索引結構

實際上(shàng)，您可以(yǐ)把索引理解爲(wéi / wèi)一(yī / yì ／yí)種特殊的(de)目錄。微軟的(de)SQL SERVER提供了(le／liǎo)兩種索引：聚集索引（clustered index，也(yě)稱聚類索引、簇集索引）和(hé / huò)非聚集索引（nonclustered index，也(yě)稱非聚類索引、非簇集索引）。下面，我們舉例來(lái)說(shuō)明一(yī / yì ／yí)下聚集索引和(hé / huò)非聚集索引的(de)區别：

其實，我們的(de)漢語字典的(de)正文本身就(jiù)是(shì)一(yī / yì ／yí)個(gè)聚集索引。比如，我們要(yào / yāo)查“安”字，就(jiù)會很自然地(dì / de)翻開字典的(de)前幾頁，因爲(wéi / wèi)“安”的(de)拼音是(shì)“an”，而(ér)按照拼音排序漢字的(de)字典是(shì)以(yǐ)英文字母“a”開頭并以(yǐ)“z”結尾的(de)，那麽“安”字就(jiù)自然地(dì / de)排在(zài)字典的(de)前部。如果您翻完了(le／liǎo)所有以(yǐ)“a”開頭的(de)部分仍然找不(bù)到(dào)這(zhè)個(gè)字，那麽就(jiù)說(shuō)明您的(de)字典中沒有這(zhè)個(gè)字；同樣的(de)，如果查“張”字，那您也(yě)會将您的(de)字典翻到(dào)最後部分，因爲(wéi / wèi)“張”的(de)拼音是(shì)“zhang”。也(yě)就(jiù)是(shì)說(shuō)，字典的(de)正文部分本身就(jiù)是(shì)一(yī / yì ／yí)個(gè)目錄，您不(bù)需要(yào / yāo)再去查其他(tā)目錄來(lái)找到(dào)您需要(yào / yāo)找的(de)内容。我們把這(zhè)種正文内容本身就(jiù)是(shì)一(yī / yì ／yí)種按照一(yī / yì ／yí)定規則排列的(de)目錄稱爲(wéi / wèi)“聚集索引”。

如果您認識某個(gè)字，您可以(yǐ)快速地(dì / de)從自動中查到(dào)這(zhè)個(gè)字。但您也(yě)可能會遇到(dào)您不(bù)認識的(de)字，不(bù)知道(dào)它的(de)發音，這(zhè)時(shí)候，您就(jiù)不(bù)能按照剛才的(de)方法找到(dào)您要(yào / yāo)查的(de)字，而(ér)需要(yào / yāo)去根據“偏旁部首”查到(dào)您要(yào / yāo)找的(de)字，然後根據這(zhè)個(gè)字後的(de)頁碼直接翻到(dào)某頁來(lái)找到(dào)您要(yào / yāo)找的(de)字。但您結合“部首目錄”和(hé / huò)“檢字表”而(ér)查到(dào)的(de)字的(de)排序并不(bù)是(shì)真正的(de)正文的(de)排序方法，比如您查“張”字，我們可以(yǐ)看到(dào)在(zài)查部首之(zhī)後的(de)檢字表中“張”的(de)頁碼是(shì)672頁，檢字表中“張”的(de)上(shàng)面是(shì)“馳”字，但頁碼卻是(shì)63頁，“張”的(de)下面是(shì)“弩”字，頁面是(shì)390頁。很顯然，這(zhè)些字并不(bù)是(shì)真正的(de)分别位于(yú)“張”字的(de)上(shàng)下方，現在(zài)您看到(dào)的(de)連續的(de)“馳、張、弩”三字實際上(shàng)就(jiù)是(shì)他(tā)們在(zài)非聚集索引中的(de)排序，是(shì)字典正文中的(de)字在(zài)非聚集索引中的(de)映射。我們可以(yǐ)通過這(zhè)種方式來(lái)找到(dào)您所需要(yào / yāo)的(de)字，但它需要(yào / yāo)兩個(gè)過程，先找到(dào)目錄中的(de)結果，然後再翻到(dào)您所需要(yào / yāo)的(de)頁碼。我們把這(zhè)種目錄純粹是(shì)目錄，正文純粹是(shì)正文的(de)排序方式稱爲(wéi / wèi)“非聚集索引”。

通過以(yǐ)上(shàng)例子(zǐ)，我們可以(yǐ)理解到(dào)什麽是(shì)“聚集索引”和(hé / huò)“非聚集索引”。進一(yī / yì ／yí)步引申一(yī / yì ／yí)下，我們可以(yǐ)很容易的(de)理解：每個(gè)表隻能有一(yī / yì ／yí)個(gè)聚集索引，因爲(wéi / wèi)目錄隻能按照一(yī / yì ／yí)種方法進行排序。

二、何時(shí)使用聚集索引或非聚集索引

下面的(de)表總結了(le／liǎo)何時(shí)使用聚集索引或非聚集索引（很重要(yào / yāo)）：

事實上(shàng)，我們可以(yǐ)通過前面聚集索引和(hé / huò)非聚集索引的(de)定義的(de)例子(zǐ)來(lái)理解上(shàng)表。如：返回某範圍内的(de)數據一(yī / yì ／yí)項。比如您的(de)某個(gè)表有一(yī / yì ／yí)個(gè)時(shí)間列，恰好您把聚合索引建立在(zài)了(le／liǎo)該列，這(zhè)時(shí)您查詢2004年1月1日至2004年10月1日之(zhī)間的(de)全部數據時(shí)，這(zhè)個(gè)速度就(jiù)将是(shì)很快的(de)，因爲(wéi / wèi)您的(de)這(zhè)本字典正文是(shì)按日期進行排序的(de)，聚類索引隻需要(yào / yāo)找到(dào)要(yào / yāo)檢索的(de)所有數據中的(de)開頭和(hé / huò)結尾數據即可；而(ér)不(bù)像非聚集索引，必須先查到(dào)目錄中查到(dào)每一(yī / yì ／yí)項數據對應的(de)頁碼，然後再根據頁碼查到(dào)具體内容。

三、結合實際，談索引使用的(de)誤區

理論的(de)目的(de)是(shì)應用。雖然我們剛才列出(chū)了(le／liǎo)何時(shí)應使用聚集索引或非聚集索引，但在(zài)實踐中以(yǐ)上(shàng)規則卻很容易被忽視或不(bù)能根據實際情況進行綜合分析。下面我們将根據在(zài)實踐中遇到(dào)的(de)實際問題來(lái)談一(yī / yì ／yí)下索引使用的(de)誤區，以(yǐ)便于(yú)大(dà)家掌握索引建立的(de)方法。

1、主鍵就(jiù)是(shì)聚集索引

這(zhè)種想法筆者認爲(wéi / wèi)是(shì)極端錯誤的(de)，是(shì)對聚集索引的(de)一(yī / yì ／yí)種浪費。雖然SQL SERVER默認是(shì)在(zài)主鍵上(shàng)建立聚集索引的(de)。

通常，我們會在(zài)每個(gè)表中都建立一(yī / yì ／yí)個(gè)ID列，以(yǐ)區分每條數據，并且這(zhè)個(gè)ID列是(shì)自動增大(dà)的(de)，步長一(yī / yì ／yí)般爲(wéi / wèi)1。我們的(de)這(zhè)個(gè)辦公自動化的(de)實例中的(de)列Gid就(jiù)是(shì)如此。此時(shí)，如果我們将這(zhè)個(gè)列設爲(wéi / wèi)主鍵，SQL SERVER會将此列默認爲(wéi / wèi)聚集索引。這(zhè)樣做有好處，就(jiù)是(shì)可以(yǐ)讓您的(de)數據在(zài)數據庫中按照ID進行物理排序，但筆者認爲(wéi / wèi)這(zhè)樣做意義不(bù)大(dà)。

顯而(ér)易見，聚集索引的(de)優勢是(shì)很明顯的(de)，而(ér)每個(gè)表中隻能有一(yī / yì ／yí)個(gè)聚集索引的(de)規則，這(zhè)使得聚集索引變得更加珍貴。

從我們前面談到(dào)的(de)聚集索引的(de)定義我們可以(yǐ)看出(chū)，使用聚集索引的(de)最大(dà)好處就(jiù)是(shì)能夠根據查詢要(yào / yāo)求，迅速縮小查詢範圍，避免全表掃描。在(zài)實際應用中，因爲(wéi / wèi)ID号是(shì)自動生成的(de)，我們并不(bù)知道(dào)每條記錄的(de)ID号，所以(yǐ)我們很難在(zài)實踐中用ID号來(lái)進行查詢。這(zhè)就(jiù)使讓ID号這(zhè)個(gè)主鍵作爲(wéi / wèi)聚集索引成爲(wéi / wèi)一(yī / yì ／yí)種資源浪費。其次，讓每個(gè)ID号都不(bù)同的(de)字段作爲(wéi / wèi)聚集索引也(yě)不(bù)符合“大(dà)數目的(de)不(bù)同值情況下不(bù)應建立聚合索引”規則；當然，這(zhè)種情況隻是(shì)針對用戶經常修改記錄内容，特别是(shì)索引項的(de)時(shí)候會負作用，但對于(yú)查詢速度并沒有影響。

在(zài)辦公自動化系統中，無論是(shì)系統首頁顯示的(de)需要(yào / yāo)用戶簽收的(de)文件、會議還是(shì)用戶進行文件查詢等任何情況下進行數據查詢都離不(bù)開字段的(de)是(shì)“日期”還有用戶本身的(de)“用戶名”。

通常，辦公自動化的(de)首頁會顯示每個(gè)用戶尚未簽收的(de)文件或會議。雖然我們的(de)where語句可以(yǐ)僅僅限制當前用戶尚未簽收的(de)情況，但如果您的(de)系統已建立了(le／liǎo)很長時(shí)間，并且數據量很大(dà)，那麽，每次每個(gè)用戶打開首頁的(de)時(shí)候都進行一(yī / yì ／yí)次全表掃描，這(zhè)樣做意義是(shì)不(bù)大(dà)的(de)，絕大(dà)多數的(de)用戶1個(gè)月前的(de)文件都已經浏覽過了(le／liǎo)，這(zhè)樣做隻能徒增數據庫的(de)開銷而(ér)已。事實上(shàng)，我們完全可以(yǐ)讓用戶打開系統首頁時(shí)，數據庫僅僅查詢這(zhè)個(gè)用戶近3個(gè)月來(lái)未閱覽的(de)文件，通過“日期”這(zhè)個(gè)字段來(lái)限制表掃描，提高查詢速度。如果您的(de)辦公自動化系統已經建立的(de)2年，那麽您的(de)首頁顯示速度理論上(shàng)将是(shì)原來(lái)速度8倍，甚至更快。

在(zài)這(zhè)裏之(zhī)所以(yǐ)提到(dào)“理論上(shàng)”三字，是(shì)因爲(wéi / wèi)如果您的(de)聚集索引還是(shì)盲目地(dì / de)建在(zài)ID這(zhè)個(gè)主鍵上(shàng)時(shí)，您的(de)查詢速度是(shì)沒有這(zhè)麽高的(de)，即使您在(zài)“日期”這(zhè)個(gè)字段上(shàng)建立的(de)索引（非聚合索引）。下面我們就(jiù)來(lái)看一(yī / yì ／yí)下在(zài)1000萬條數據量的(de)情況下各種查詢的(de)速度表現（3個(gè)月内的(de)數據爲(wéi / wèi)25萬條）：

（1）僅在(zài)主鍵上(shàng)建立聚集索引，并且不(bù)劃分時(shí)間段：

Select gid,fariqi,neibuyonghu,title from tgongwen

用時(shí)：128470毫秒（即：128秒）

（2）在(zài)主鍵上(shàng)建立聚集索引，在(zài)fariq上(shàng)建立非聚集索引：

select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())

用時(shí)：53763毫秒（54秒）

（3）将聚合索引建立在(zài)日期列（fariqi）上(shàng)：

select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())

用時(shí)：2423毫秒（2秒）

雖然每條語句提取出(chū)來(lái)的(de)都是(shì)25萬條數據，各種情況的(de)差異卻是(shì)巨大(dà)的(de)，特别是(shì)将聚集索引建立在(zài)日期列時(shí)的(de)差異。事實上(shàng)，如果您的(de)數據庫真的(de)有1000萬容量的(de)話，把主鍵建立在(zài)ID列上(shàng)，就(jiù)像以(yǐ)上(shàng)的(de)第1、2種情況，在(zài)網頁上(shàng)的(de)表現就(jiù)是(shì)超時(shí)，根本就(jiù)無法顯示。這(zhè)也(yě)是(shì)我摒棄ID列作爲(wéi / wèi)聚集索引的(de)一(yī / yì ／yí)個(gè)最重要(yào / yāo)的(de)因素。得出(chū)以(yǐ)上(shàng)速度的(de)方法是(shì)：在(zài)各個(gè)select語句前加：

declare @d datetime
set @d=getdate()

并在(zài)select語句後加：

select [語句執行花費時(shí)間(毫秒)]=datediff(ms,@d,getdate())

2、隻要(yào / yāo)建立索引就(jiù)能顯著提高查詢速度

事實上(shàng)，我們可以(yǐ)發現上(shàng)面的(de)例子(zǐ)中，第2、3條語句完全相同，且建立索引的(de)字段也(yě)相同；不(bù)同的(de)僅是(shì)前者在(zài)fariqi字段上(shàng)建立的(de)是(shì)非聚合索引，後者在(zài)此字段上(shàng)建立的(de)是(shì)聚合索引，但查詢速度卻有着天壤之(zhī)别。所以(yǐ)，并非是(shì)在(zài)任何字段上(shàng)簡單地(dì / de)建立索引就(jiù)能提高查詢速度。

從建表的(de)語句中，我們可以(yǐ)看到(dào)這(zhè)個(gè)有着1000萬數據的(de)表中fariqi字段有5003個(gè)不(bù)同記錄。在(zài)此字段上(shàng)建立聚合索引是(shì)再合适不(bù)過了(le／liǎo)。在(zài)現實中，我們每天都會發幾個(gè)文件，這(zhè)幾個(gè)文件的(de)發文日期就(jiù)相同，這(zhè)完全符合建立聚集索引要(yào / yāo)求的(de)：“既不(bù)能絕大(dà)多數都相同，又不(bù)能隻有極少數相同”的(de)規則。由此看來(lái)，我們建立“适當”的(de)聚合索引對于(yú)我們提高查詢速度是(shì)非常重要(yào / yāo)的(de)。

3、把所有需要(yào / yāo)提高查詢速度的(de)字段都加進聚集索引，以(yǐ)提高查詢速度

上(shàng)面已經談到(dào)：在(zài)進行數據查詢時(shí)都離不(bù)開字段的(de)是(shì)“日期”還有用戶本身的(de)“用戶名”。既然這(zhè)兩個(gè)字段都是(shì)如此的(de)重要(yào / yāo)，我們可以(yǐ)把他(tā)們合并起來(lái)，建立一(yī / yì ／yí)個(gè)複合索引（compound index）。

很多人(rén)認爲(wéi / wèi)隻要(yào / yāo)把任何字段加進聚集索引，就(jiù)能提高查詢速度，也(yě)有人(rén)感到(dào)迷惑：如果把複合的(de)聚集索引字段分開查詢，那麽查詢速度會減慢嗎？帶着這(zhè)個(gè)問題，我們來(lái)看一(yī / yì ／yí)下以(yǐ)下的(de)查詢速度（結果集都是(shì)25萬條數據）：（日期列fariqi首先排在(zài)複合聚集索引的(de)起始列，用戶名neibuyonghu排在(zài)後列）：

select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5''

查詢速度：2513毫秒

select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='辦公室'

查詢速度：2516毫秒

select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='辦公室'

查詢速度：60280毫秒

從以(yǐ)上(shàng)試驗中，我們可以(yǐ)看到(dào)如果僅用聚集索引的(de)起始列作爲(wéi / wèi)查詢條件和(hé / huò)同時(shí)用到(dào)複合聚集索引的(de)全部列的(de)查詢速度是(shì)幾乎一(yī / yì ／yí)樣的(de)，甚至比用上(shàng)全部的(de)複合索引列還要(yào / yāo)略快（在(zài)查詢結果集數目一(yī / yì ／yí)樣的(de)情況下）；而(ér)如果僅用複合聚集索引的(de)非起始列作爲(wéi / wèi)查詢條件的(de)話，這(zhè)個(gè)索引是(shì)不(bù)起任何作用的(de)。當然，語句1、2的(de)查詢速度一(yī / yì ／yí)樣是(shì)因爲(wéi / wèi)查詢的(de)條目數一(yī / yì ／yí)樣，如果複合索引的(de)所有列都用上(shàng)，而(ér)且查詢結果少的(de)話，這(zhè)樣就(jiù)會形成“索引覆蓋”，因而(ér)性能可以(yǐ)達到(dào)最優。同時(shí)，請記住：無論您是(shì)否經常使用聚合索引的(de)其他(tā)列，但其前導列一(yī / yì ／yí)定要(yào / yāo)是(shì)使用最頻繁的(de)列。

四、其他(tā)書上(shàng)沒有的(de)索引使用經驗總結

1、用聚合索引比用不(bù)是(shì)聚合索引的(de)主鍵速度快

下面是(shì)實例語句：（都是(shì)提取25萬條數據）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

使用時(shí)間：3326毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

使用時(shí)間：4470毫秒

這(zhè)裏，用聚合索引比用不(bù)是(shì)聚合索引的(de)主鍵速度快了(le／liǎo)近1/4。

2、用聚合索引比用一(yī / yì ／yí)般的(de)主鍵作order by時(shí)速度快，特别是(shì)在(zài)小數據量情況下

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

用時(shí)：12936

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

用時(shí)：18843

這(zhè)裏，用聚合索引比用一(yī / yì ／yí)般的(de)主鍵作order by時(shí)，速度快了(le／liǎo)3/10。事實上(shàng)，如果數據量很小的(de)話，用聚集索引作爲(wéi / wèi)排序列要(yào / yāo)比使用非聚集索引速度快得明顯的(de)多；而(ér)數據量如果很大(dà)的(de)話，如10萬以(yǐ)上(shàng)，則二者的(de)速度差别不(bù)明顯。

3、使用聚合索引内的(de)時(shí)間段，搜索時(shí)間會按數據占整個(gè)數據表的(de)百分比成比例減少，而(ér)無論聚合索引使用了(le／liǎo)多少個(gè)：

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'

用時(shí)：6343毫秒（提取100萬條）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'

用時(shí)：3170毫秒（提取50萬條）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

用時(shí)：3326毫秒（和(hé / huò)上(shàng)句的(de)結果一(yī / yì ／yí)模一(yī / yì ／yí)樣。如果采集的(de)數量一(yī / yì ／yí)樣，那麽用大(dà)于(yú)号和(hé / huò)等于(yú)号是(shì)一(yī / yì ／yí)樣的(de)）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'

用時(shí)：3280毫秒

4、日期列不(bù)會因爲(wéi / wèi)有分秒的(de)輸入而(ér)減慢查詢速度

下面的(de)例子(zǐ)中，共有100萬條數據，2004年1月1日以(yǐ)後的(de)數據有50萬條，但隻有兩個(gè)不(bù)同的(de)日期，日期精确到(dào)日；之(zhī)前有數據50萬條，有5000個(gè)不(bù)同的(de)日期，日期精确到(dào)秒。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi

用時(shí)：6390毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi

用時(shí)：6453毫秒

五、其他(tā)注意事項

“水可載舟，亦可覆舟”，索引也(yě)一(yī / yì ／yí)樣。索引有助于(yú)提高檢索性能，但過多或不(bù)當的(de)索引也(yě)會導緻系統低效。因爲(wéi / wèi)用戶在(zài)表中每加進一(yī / yì ／yí)個(gè)索引，數據庫就(jiù)要(yào / yāo)做更多的(de)工作。過多的(de)索引甚至會導緻索引碎片。

所以(yǐ)說(shuō)，我們要(yào / yāo)建立一(yī / yì ／yí)個(gè)“适當”的(de)索引體系，特别是(shì)對聚合索引的(de)創建，更應精益求精，以(yǐ)使您的(de)數據庫能得到(dào)高性能的(de)發揮。

當然，在(zài)實踐中，作爲(wéi / wèi)一(yī / yì ／yí)個(gè)盡職的(de)數據庫管理員，您還要(yào / yāo)多測試一(yī / yì ／yí)些方案，找出(chū)哪種方案效率最高、最爲(wéi / wèi)有效。

（二）改善SQL語句

很多人(rén)不(bù)知道(dào)SQL語句在(zài)SQL SERVER中是(shì)如何執行的(de)，他(tā)們擔心自己所寫的(de)SQL語句會被SQL SERVER誤解。比如：

select * from table1 where name='zhangsan' and tID > 10000

和(hé / huò)執行

select * from table1 where tID > 10000 and name='zhangsan'

一(yī / yì ／yí)些人(rén)不(bù)知道(dào)以(yǐ)上(shàng)兩條語句的(de)執行效率是(shì)否一(yī / yì ／yí)樣，因爲(wéi / wèi)如果簡單的(de)從語句先後上(shàng)看，這(zhè)兩個(gè)語句的(de)确是(shì)不(bù)一(yī / yì ／yí)樣，如果tID是(shì)一(yī / yì ／yí)個(gè)聚合索引，那麽後一(yī / yì ／yí)句僅僅從表的(de)10000條以(yǐ)後的(de)記錄中查找就(jiù)行了(le／liǎo)；而(ér)前一(yī / yì ／yí)句則要(yào / yāo)先從全表中查找看有幾個(gè)name='zhangsan'的(de)，而(ér)後再根據限制條件條件tID>10000來(lái)提出(chū)查詢結果。

事實上(shàng)，這(zhè)樣的(de)擔心是(shì)不(bù)必要(yào / yāo)的(de)。SQL SERVER中有一(yī / yì ／yí)個(gè)“查詢分析優化器”，它可以(yǐ)計算出(chū)where子(zǐ)句中的(de)搜索條件并确定哪個(gè)索引能縮小表掃描的(de)搜索空間，也(yě)就(jiù)是(shì)說(shuō)，它能實現自動優化。

雖然查詢優化器可以(yǐ)根據where子(zǐ)句自動的(de)進行查詢優化，但大(dà)家仍然有必要(yào / yāo)了(le／liǎo)解一(yī / yì ／yí)下“查詢優化器”的(de)工作原理，如非這(zhè)樣，有時(shí)查詢優化器就(jiù)會不(bù)按照您的(de)本意進行快速查詢。

在(zài)查詢分析階段，查詢優化器查看查詢的(de)每個(gè)階段并決定限制需要(yào / yāo)掃描的(de)數據量是(shì)否有用。如果一(yī / yì ／yí)個(gè)階段可以(yǐ)被用作一(yī / yì ／yí)個(gè)掃描參數（SARG），那麽就(jiù)稱之(zhī)爲(wéi / wèi)可優化的(de)，并且可以(yǐ)利用索引快速獲得所需數據。

SARG的(de)定義：用于(yú)限制搜索的(de)一(yī / yì ／yí)個(gè)操作，因爲(wéi / wèi)它通常是(shì)指一(yī / yì ／yí)個(gè)特定的(de)匹配，一(yī / yì ／yí)個(gè)值得範圍内的(de)匹配或者兩個(gè)以(yǐ)上(shàng)條件的(de)AND連接。形式如下：

列名 操作符 <常數>或<常數> 操作符列名

列名可以(yǐ)出(chū)現在(zài)操作符的(de)一(yī / yì ／yí)邊，而(ér)常數或變量出(chū)現在(zài)操作符的(de)另一(yī / yì ／yí)邊。如：

Name=’張三’

價格>5000

5000<價格

Name=’張三’ and 價格>5000

如果一(yī / yì ／yí)個(gè)表達式不(bù)能滿足SARG的(de)形式，那它就(jiù)無法限制搜索的(de)範圍了(le／liǎo)，也(yě)就(jiù)是(shì)SQL SERVER必須對每一(yī / yì ／yí)行都判斷它是(shì)否滿足WHERE子(zǐ)句中的(de)所有條件。所以(yǐ)一(yī / yì ／yí)個(gè)索引對于(yú)不(bù)滿足SARG形式的(de)表達式來(lái)說(shuō)是(shì)無用的(de)。

介紹完SARG後，我們來(lái)總結一(yī / yì ／yí)下使用SARG以(yǐ)及在(zài)實踐中遇到(dào)的(de)和(hé / huò)某些資料上(shàng)結論不(bù)同的(de)經驗：

1、Like語句是(shì)否屬于(yú)SARG取決于(yú)所使用的(de)通配符的(de)類型

如：name like ‘張%’ ，這(zhè)就(jiù)屬于(yú)SARG

而(ér)：name like ‘%張’ ,就(jiù)不(bù)屬于(yú)SARG。

原因是(shì)通配符%在(zài)字符串的(de)開通使得索引無法使用。

2、or 會引起全表掃描

Name=’張三’ and 價格>5000 符号SARG，而(ér)：Name=’張三’ or 價格>5000 則不(bù)符合SARG。使用or會引起全表掃描。

3、非操作符、函數引起的(de)不(bù)滿足SARG形式的(de)語句

不(bù)滿足SARG形式的(de)語句最典型的(de)情況就(jiù)是(shì)包括非操作符的(de)語句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外還有函數。下面就(jiù)是(shì)幾個(gè)不(bù)滿足SARG形式的(de)例子(zǐ)：

ABS(價格)<5000

Name like ‘%三’

有些表達式，如：

WHERE 價格*2>5000

SQL SERVER也(yě)會認爲(wéi / wèi)是(shì)SARG，SQL SERVER會将此式轉化爲(wéi / wèi)：

WHERE 價格>2500/2

但我們不(bù)推薦這(zhè)樣使用，因爲(wéi / wèi)有時(shí)SQL SERVER不(bù)能保證這(zhè)種轉化與原始表達式是(shì)完全等價的(de)。

4、IN 的(de)作用相當與OR

語句：

Select * from table1 where tid in (2,3)和(hé / huò)Select * from table1 where tid=2 or tid=3

是(shì)一(yī / yì ／yí)樣的(de)，都會引起全表掃描，如果tid上(shàng)有索引，其索引也(yě)會失效。

5、盡量少用NOT

6、exists 和(hé / huò) in 的(de)執行效率是(shì)一(yī / yì ／yí)樣的(de)

很多資料上(shàng)都顯示說(shuō)，exists要(yào / yāo)比in的(de)執行效率要(yào / yāo)高，同時(shí)應盡可能的(de)用not exists來(lái)代替not in。但事實上(shàng)，我試驗了(le／liǎo)一(yī / yì ／yí)下，發現二者無論是(shì)前面帶不(bù)帶not，二者之(zhī)間的(de)執行效率都是(shì)一(yī / yì ／yí)樣的(de)。因爲(wéi / wèi)涉及子(zǐ)查詢，我們試驗這(zhè)次用SQL SERVER自帶的(de)pubs數據庫。運行前我們可以(yǐ)把SQL SERVER的(de)statistics I/O狀态打開：

select title,price from titles where title_id in (select title_id from sales where qty>30)

該句的(de)執行結果爲(wéi / wèi)：

表 ''sales''。掃描計數 18，邏輯讀 56 次，物理讀 0 次，預讀 0 次。

表 ''titles''。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。

select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)

第二句的(de)執行結果爲(wéi / wèi)：

表 ''sales''。掃描計數 18，邏輯讀 56 次，物理讀 0 次，預讀 0 次。

表 ''titles''。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。

我們從此可以(yǐ)看到(dào)用exists和(hé / huò)用in的(de)執行效率是(shì)一(yī / yì ／yí)樣的(de)。

7、用函數charindex()和(hé / huò)前面加通配符%的(de)LIKE執行效率一(yī / yì ／yí)樣

前面，我們談到(dào)，如果在(zài)LIKE前面加上(shàng)通配符%，那麽将會引起全表掃描，所以(yǐ)其執行效率是(shì)低下的(de)。但有的(de)資料介紹說(shuō)，用函數charindex()來(lái)代替LIKE速度會有大(dà)的(de)提升，經我試驗，發現這(zhè)種說(shuō)明也(yě)是(shì)錯誤的(de)：

select gid,title,fariqi,reader from tgongwen where charindex('刑偵支隊',reader)>0 and fariqi>'2004-5-5'

用時(shí)：7秒，另外：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

select gid,title,fariqi,reader from tgongwen where reader like '%' + '刑偵支隊' + '%' and fariqi>'2004-5-5'

用時(shí)：7秒，另外：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

8、union并不(bù)絕對比or的(de)執行效率高

我們前面已經談到(dào)了(le／liǎo)在(zài)where子(zǐ)句中使用or會引起全表掃描，一(yī / yì ／yí)般的(de)，我所見過的(de)資料都是(shì)推薦這(zhè)裏用union來(lái)代替or。事實證明，這(zhè)種說(shuō)法對于(yú)大(dà)部分都是(shì)适用的(de)。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000

用時(shí)：68秒。掃描計數 1，邏輯讀 404008 次，物理讀 283 次，預讀 392163 次。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000

用時(shí)：9秒。掃描計數 8，邏輯讀 67489 次，物理讀 216 次，預讀 7499 次。

看來(lái)，用union在(zài)通常情況下比用or的(de)效率要(yào / yāo)高的(de)多。

但經過試驗，筆者發現如果or兩邊的(de)查詢列是(shì)一(yī / yì ／yí)樣的(de)話，那麽用union則反倒和(hé / huò)用or的(de)執行速度差很多，雖然這(zhè)裏union掃描的(de)是(shì)索引，而(ér)or掃描的(de)是(shì)全表。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'

用時(shí)：6423毫秒。掃描計數 2，邏輯讀 14726 次，物理讀 1 次，預讀 7176 次。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-2-5'

用時(shí)：11640毫秒。掃描計數 8，邏輯讀 14806 次，物理讀 108 次，預讀 1144 次。

9、字段提取要(yào / yāo)按照“需多少、提多少”的(de)原則，避免“select *”

我們來(lái)做一(yī / yì ／yí)個(gè)試驗：

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用時(shí)：4673毫秒

select top 10000 gid,fariqi,title from tgongwen order by gid desc

用時(shí)：1376毫秒

select top 10000 gid,fariqi from tgongwen order by gid desc

用時(shí)：80毫秒

由此看來(lái)，我們每少提取一(yī / yì ／yí)個(gè)字段，數據的(de)提取速度就(jiù)會有相應的(de)提升。提升的(de)速度還要(yào / yāo)看您舍棄的(de)字段的(de)大(dà)小來(lái)判斷。

10、count(*)不(bù)比count(字段)慢

某些資料上(shàng)說(shuō)：用*會統計所有列，顯然要(yào / yāo)比一(yī / yì ／yí)個(gè)世界的(de)列名效率低。這(zhè)種說(shuō)法其實是(shì)沒有根據的(de)。我們來(lái)看：

select count(*) from Tgongwen

用時(shí)：1500毫秒

select count(gid) from Tgongwen

用時(shí)：1483毫秒

select count(fariqi) from Tgongwen

用時(shí)：3140毫秒

select count(title) from Tgongwen

用時(shí)：52050毫秒

從以(yǐ)上(shàng)可以(yǐ)看出(chū)，如果用count(*)和(hé / huò)用count(主鍵)的(de)速度是(shì)相當的(de)，而(ér)count(*)卻比其他(tā)任何除主鍵以(yǐ)外的(de)字段彙總速度要(yào / yāo)快，而(ér)且字段越長，彙總的(de)速度就(jiù)越慢。我想，如果用count(*)， SQL SERVER可能會自動查找最小字段來(lái)彙總的(de)。當然，如果您直接寫count(主鍵)将會來(lái)的(de)更直接些。

11、order by按聚集索引列排序效率最高

我們來(lái)看：（gid是(shì)主鍵，fariqi是(shì)聚合索引列）：

select top 10000 gid,fariqi,reader,title from tgongwen

用時(shí)：196 毫秒。 掃描計數 1，邏輯讀 289 次，物理讀 1 次，預讀 1527 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc

用時(shí)：4720毫秒。 掃描計數 1，邏輯讀 41956 次，物理讀 0 次，預讀 1287 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用時(shí)：4736毫秒。 掃描計數 1，邏輯讀 55350 次，物理讀 10 次，預讀 775 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc

用時(shí)：173毫秒。 掃描計數 1，邏輯讀 290 次，物理讀 0 次，預讀 0 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc

用時(shí)：156毫秒。 掃描計數 1，邏輯讀 289 次，物理讀 0 次，預讀 0 次。

從以(yǐ)上(shàng)我們可以(yǐ)看出(chū)，不(bù)排序的(de)速度以(yǐ)及邏輯讀次數都是(shì)和(hé / huò)“order by 聚集索引列” 的(de)速度是(shì)相當的(de)，但這(zhè)些都比“order by 非聚集索引列”的(de)查詢速度是(shì)快得多的(de)。

同時(shí)，按照某個(gè)字段進行排序的(de)時(shí)候，無論是(shì)正序還是(shì)倒序，速度是(shì)基本相當的(de)。

12、高效的(de)TOP

事實上(shàng)，在(zài)查詢和(hé / huò)提取超大(dà)容量的(de)數據集時(shí)，影響數據庫響應時(shí)間的(de)最大(dà)因素不(bù)是(shì)數據查找，而(ér)是(shì)物理的(de)I/0操作。如：

select top 10 * from (
select top 10000 gid,fariqi,title from tgongwen
where neibuyonghu='辦公室'
order by gid desc) as a
order by gid asc

這(zhè)條語句，從理論上(shàng)講，整條語句的(de)執行時(shí)間應該比子(zǐ)句的(de)執行時(shí)間長，但事實相反。因爲(wéi / wèi)，子(zǐ)句執行後返回的(de)是(shì)10000條記錄，而(ér)整條語句僅返回10條語句，所以(yǐ)影響數據庫響應時(shí)間最大(dà)的(de)因素是(shì)物理I/O操作。而(ér)限制物理I/O操作此處的(de)最有效方法之(zhī)一(yī / yì ／yí)就(jiù)是(shì)使用TOP關鍵詞了(le／liǎo)。TOP關鍵詞是(shì)SQL SERVER中經過系統優化過的(de)一(yī / yì ／yí)個(gè)用來(lái)提取前幾條或前幾個(gè)百分比數據的(de)詞。經筆者在(zài)實踐中的(de)應用，發現TOP确實很好用，效率也(yě)很高。但這(zhè)個(gè)詞在(zài)另外一(yī / yì ／yí)個(gè)大(dà)型數據庫ORACLE中卻沒有，這(zhè)不(bù)能說(shuō)不(bù)是(shì)一(yī / yì ／yí)個(gè)遺憾，雖然在(zài)ORACLE中可以(yǐ)用其他(tā)方法（如：rownumber）來(lái)解決。在(zài)以(yǐ)後的(de)關于(yú)“實現千萬級數據的(de)分頁顯示存儲過程”的(de)讨論中，我們就(jiù)将用到(dào)TOP這(zhè)個(gè)關鍵詞。

到(dào)此爲(wéi / wèi)止，我們上(shàng)面讨論了(le／liǎo)如何實現從大(dà)容量的(de)數據庫中快速地(dì / de)查詢出(chū)您所需要(yào / yāo)的(de)數據方法。當然，我們介紹的(de)這(zhè)些方法都是(shì)“軟”方法，在(zài)實踐中，我們還要(yào / yāo)考慮各種“硬”因素，如：網絡性能、服務器的(de)性能、操作系統的(de)性能，甚至網卡、交換機等。

（三）實現小數據量和(hé / huò)海量數據的(de)通用分頁顯示存儲過程

建立一(yī / yì ／yí)個(gè) Web 應用，分頁浏覽功能必不(bù)可少。這(zhè)個(gè)問題是(shì)數據庫處理中十分常見的(de)問題。經典的(de)數據分頁方法是(shì):ADO 紀錄集分頁法，也(yě)就(jiù)是(shì)利用ADO自帶的(de)分頁功能（利用遊标）來(lái)實現分頁。但這(zhè)種分頁方法僅适用于(yú)較小數據量的(de)情形，因爲(wéi / wèi)遊标本身有缺點：遊标是(shì)存放在(zài)内存中，很費内存。遊标一(yī / yì ／yí)建立，就(jiù)将相關的(de)記錄鎖住，直到(dào)取消遊标。遊标提供了(le／liǎo)對特定集合中逐行掃描的(de)手段，一(yī / yì ／yí)般使用遊标來(lái)逐行遍曆數據，根據取出(chū)數據條件的(de)不(bù)同進行不(bù)同的(de)操作。而(ér)對于(yú)多表和(hé / huò)大(dà)表中定義的(de)遊标（大(dà)的(de)數據集合）循環很容易使程序進入一(yī / yì ／yí)個(gè)漫長的(de)等待甚至死機。

更重要(yào / yāo)的(de)是(shì)，對于(yú)非常大(dà)的(de)數據模型而(ér)言，分頁檢索時(shí)，如果按照傳統的(de)每次都加載整個(gè)數據源的(de)方法是(shì)非常浪費資源的(de)。現在(zài)流行的(de)分頁方法一(yī / yì ／yí)般是(shì)檢索頁面大(dà)小的(de)塊區的(de)數據，而(ér)非檢索所有的(de)數據，然後單步執行當前行。

最早較好地(dì / de)實現這(zhè)種根據頁面大(dà)小和(hé / huò)頁碼來(lái)提取數據的(de)方法大(dà)概就(jiù)是(shì)“俄羅斯存儲過程”。這(zhè)個(gè)存儲過程用了(le／liǎo)遊标，由于(yú)遊标的(de)局限性，所以(yǐ)這(zhè)個(gè)方法并沒有得到(dào)大(dà)家的(de)普遍認可。

後來(lái)，網上(shàng)有人(rén)改造了(le／liǎo)此存儲過程，下面的(de)存儲過程就(jiù)是(shì)結合我們的(de)辦公自動化實例寫的(de)分頁存儲過程：

CREATE procedure pagination1

(@pagesize int, --頁面大(dà)小，如每頁存儲20條記錄

@pageindex int --當前頁碼

)

as



set nocount on


begin

declare @indextable table(id int identity(1,1),nid int) --定義表變量

declare @PageLowerBound int --定義此頁的(de)底碼

declare @PageUpperBound int --定義此頁的(de)頂碼

set @PageLowerBound=(@pageindex-1)*@pagesize

set @PageUpperBound=@PageLowerBound+@pagesize

set rowcount @PageUpperBound

insert into @indextable(nid) select gid from TGongwen

　　　　　　where fariqi >dateadd(day,-365,getdate()) order by fariqi desc

select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t

where O.gid=t.nid and t.id>@PageLowerBound

and t.id<=@PageUpperBound order by t.id

end


set nocount off

以(yǐ)上(shàng)存儲過程運用了(le／liǎo)SQL SERVER的(de)最新技術――表變量。應該說(shuō)這(zhè)個(gè)存儲過程也(yě)是(shì)一(yī / yì ／yí)個(gè)非常優秀的(de)分頁存儲過程。當然，在(zài)這(zhè)個(gè)過程中，您也(yě)可以(yǐ)把其中的(de)表變量寫成臨時(shí)表：CREATE TABLE #Temp。但很明顯，在(zài)SQL SERVER中，用臨時(shí)表是(shì)沒有用表變量快的(de)。所以(yǐ)筆者剛開始使用這(zhè)個(gè)存儲過程時(shí)，感覺非常的(de)不(bù)錯，速度也(yě)比原來(lái)的(de)ADO的(de)好。但後來(lái)，我又發現了(le／liǎo)比此方法更好的(de)方法。

筆者曾在(zài)網上(shàng)看到(dào)了(le／liǎo)一(yī / yì ／yí)篇小短文《從數據表中取出(chū)第n條到(dào)第m條的(de)記錄的(de)方法》，全文如下：

--從publish 表中取出(chū)第 n 條到(dào)第 m 條的(de)記錄：

SELECT TOP m-n+1 *

FROM publish

WHERE (id NOT IN

　　　　(SELECT TOP n-1 id

　　　　 FROM publish))



--id 爲(wéi / wèi)publish 表的(de)關鍵字

我當時(shí)看到(dào)這(zhè)篇文章的(de)時(shí)候，真的(de)是(shì)精神爲(wéi / wèi)之(zhī)一(yī / yì ／yí)振，覺得思路非常得好。等到(dào)後來(lái)，我在(zài)作辦公自動化系統（ASP.NET+ C#＋SQL SERVER）的(de)時(shí)候，忽然想起了(le／liǎo)這(zhè)篇文章，我想如果把這(zhè)個(gè)語句改造一(yī / yì ／yí)下，這(zhè)就(jiù)可能是(shì)一(yī / yì ／yí)個(gè)非常好的(de)分頁存儲過程。于(yú)是(shì)我就(jiù)滿網上(shàng)找這(zhè)篇文章，沒想到(dào)，文章還沒找到(dào)，卻找到(dào)了(le／liǎo)一(yī / yì ／yí)篇根據此語句寫的(de)一(yī / yì ／yí)個(gè)分頁存儲過程，這(zhè)個(gè)存儲過程也(yě)是(shì)目前較爲(wéi / wèi)流行的(de)一(yī / yì ／yí)種分頁存儲過程，我很後悔沒有争先把這(zhè)段文字改造成存儲過程：

CREATE PROCEDURE pagination2

(

@SQL nVARCHAR(4000), --不(bù)帶排序語句的(de)SQL語句

@Page int, --頁碼

@RecsPerPage int, --每頁容納的(de)記錄數

@ID VARCHAR(255), --需要(yào / yāo)排序的(de)不(bù)重複的(de)ID号

@Sort VARCHAR(255) --排序字段及規則

)

AS

 

DECLARE @Str nVARCHAR(4000)

 

SET @Str=''SELECT TOP ''+CAST(@RecsPerPage AS VARCHAR(20))+'' * FROM

(''+@SQL+'') T WHERE T.''+@ID+''NOT IN (SELECT TOP''+CAST((@RecsPerPage*(@Page-1))

AS VARCHAR(20))+'' ''+@ID+'' FROM (''+@SQL+'') T9 ORDER BY''+@Sort+'') ORDER BY ''+@Sort
 
PRINT @Str

 

EXEC sp_ExecuteSql @Str

GO

--其實，以(yǐ)上(shàng)語句可以(yǐ)簡化爲(wéi / wèi)：

SELECT TOP 頁大(dà)小 *

FROM Table1 WHERE (ID NOT IN (SELECT TOP 頁大(dà)小*頁數 id FROM 表 ORDER BY id))

ORDER BY ID

--但這(zhè)個(gè)存儲過程有一(yī / yì ／yí)個(gè)緻命的(de)缺點，就(jiù)是(shì)它含有NOT IN字樣。雖然我可以(yǐ)把它改造爲(wéi / wèi)：

SELECT TOP 頁大(dà)小 *

FROM Table1 WHERE not exists

(select * from (select top (頁大(dà)小*頁數) * from table1 order by id) b where b.id=a.id )

order by id

--目前流行的(de)一(yī / yì ／yí)種分頁存儲過程

即，用not exists來(lái)代替not in，但我們前面已經談過了(le／liǎo)，二者的(de)執行效率實際上(shàng)是(shì)沒有區别的(de)。既便如此，用TOP 結合NOT IN的(de)這(zhè)個(gè)方法還是(shì)比用遊标要(yào / yāo)來(lái)得快一(yī / yì ／yí)些。

雖然用not exists并不(bù)能挽救上(shàng)個(gè)存儲過程的(de)效率，但使用SQL SERVER中的(de)TOP關鍵字卻是(shì)一(yī / yì ／yí)個(gè)非常明智的(de)選擇。因爲(wéi / wèi)分頁優化的(de)最終目的(de)就(jiù)是(shì)避免産生過大(dà)的(de)記錄集，而(ér)我們在(zài)前面也(yě)已經提到(dào)了(le／liǎo)TOP的(de)優勢，通過TOP 即可實現對數據量的(de)控制。

在(zài)分頁算法中，影響我們查詢速度的(de)關鍵因素有兩點：TOP和(hé / huò)NOT IN。TOP可以(yǐ)提高我們的(de)查詢速度，而(ér)NOT IN會減慢我們的(de)查詢速度，所以(yǐ)要(yào / yāo)提高我們整個(gè)分頁算法的(de)速度，就(jiù)要(yào / yāo)徹底改造NOT IN，同其他(tā)方法來(lái)替代它。

我們知道(dào)，幾乎任何字段，我們都可以(yǐ)通過max(字段)或min(字段)來(lái)提取某個(gè)字段中的(de)最大(dà)或最小值，所以(yǐ)如果這(zhè)個(gè)字段不(bù)重複，那麽就(jiù)可以(yǐ)利用這(zhè)些不(bù)重複的(de)字段的(de)max或min作爲(wéi / wèi)分水嶺，使其成爲(wéi / wèi)分頁算法中分開每頁的(de)參照物。在(zài)這(zhè)裏，我們可以(yǐ)用操作符“>”或“<”号來(lái)完成這(zhè)個(gè)使命，使查詢語句符合SARG形式。如：

Select top 10 * from table1 where id>200

--于(yú)是(shì)就(jiù)有了(le／liǎo)如下分頁方案：

select top 頁大(dà)小 *

from table1

where id>

(select max (id) from

(select top ((頁碼-1)*頁大(dà)小) id from table1 order by id) as T

)

order by id

在(zài)選擇即不(bù)重複值，又容易分辨大(dà)小的(de)列時(shí)，我們通常會選擇主鍵。下表列出(chū)了(le／liǎo)筆者用有着1000萬數據的(de)辦公自動化系統中的(de)表，在(zài)以(yǐ)GID（GID是(shì)主鍵，但并不(bù)是(shì)聚集索引。）爲(wéi / wèi)排序列、提取gid,fariqi,title字段，分别以(yǐ)第1、10、100、500、1000、1萬、10萬、25萬、50萬頁爲(wéi / wèi)例，測試以(yǐ)上(shàng)三種分頁方案的(de)執行速度：（單位：毫秒）

從上(shàng)表中，我們可以(yǐ)看出(chū)，三種存儲過程在(zài)執行100頁以(yǐ)下的(de)分頁命令時(shí)，都是(shì)可以(yǐ)信任的(de)，速度都很好。但第一(yī / yì ／yí)種方案在(zài)執行分頁1000頁以(yǐ)上(shàng)後，速度就(jiù)降了(le／liǎo)下來(lái)。第二種方案大(dà)約是(shì)在(zài)執行分頁1萬頁以(yǐ)上(shàng)後速度開始降了(le／liǎo)下來(lái)。而(ér)第三種方案卻始終沒有大(dà)的(de)降勢，後勁仍然很足。

在(zài)确定了(le／liǎo)第三種分頁方案後，我們可以(yǐ)據此寫一(yī / yì ／yí)個(gè)存儲過程。大(dà)家知道(dào)SQL SERVER的(de)存儲過程是(shì)事先編譯好的(de)SQL語句，它的(de)執行效率要(yào / yāo)比通過WEB頁面傳來(lái)的(de)SQL語句的(de)執行效率要(yào / yāo)高。下面的(de)存儲過程不(bù)僅含有分頁方案，還會根據頁面傳來(lái)的(de)參數來(lái)确定是(shì)否進行數據總數統計。

--獲取指定頁的(de)數據：

CREATE PROCEDURE pagination3

@tblName varchar(255), -- 表名

@strGetFields varchar(1000) = ''*'', -- 需要(yào / yāo)返回的(de)列

@fldName varchar(255)='''', -- 排序的(de)字段名

@PageSize int = 10, -- 頁尺寸

@PageIndex int = 1, -- 頁碼

@doCount bit = 0, -- 返回記錄總數, 非 0 值則返回

@OrderType bit = 0, -- 設置排序類型, 非 0 值則降序

@strWhere varchar(1500) = '''' -- 查詢條件 (注意: 不(bù)要(yào / yāo)加 where)

AS

 

declare @strSQL varchar(5000) -- 主語句

declare @strTmp varchar(110) -- 臨時(shí)變量

declare @strOrder varchar(400) -- 排序類型

 

if @doCount != 0

begin

if @strWhere !=''''

set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

else

set @strSQL = "select count(*) as Total from [" + @tblName + "]"

end

--以(yǐ)上(shàng)代碼的(de)意思是(shì)如果@doCount傳遞過來(lái)的(de)不(bù)是(shì)0，就(jiù)執行總數統計。以(yǐ)下的(de)所有代碼都是(shì)@doCount爲(wéi / wèi)0的(de)情況：

else

begin

if @OrderType != 0

begin

set @strTmp = "<(select min"

set @strOrder = " order by [" + @fldName +"] desc"

--如果@OrderType不(bù)是(shì)0，就(jiù)執行降序，這(zhè)句很重要(yào / yāo)！

end

else

begin

set @strTmp = ">(select max"

set @strOrder = " order by [" + @fldName +"] asc"

end

 

if @PageIndex = 1

begin

if @strWhere != ''''

 

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "

　　　　　　　　from [" + @tblName + "] where " + @strWhere + " " + @strOrder

else

 

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "

　　　　　　　　from ["+ @tblName + "] "+ @strOrder

--如果是(shì)第一(yī / yì ／yí)頁就(jiù)執行以(yǐ)上(shàng)代碼，這(zhè)樣會加快執行速度

end

else

begin

--以(yǐ)下代碼賦予了(le／liǎo)@strSQL以(yǐ)真正執行的(de)SQL代碼　

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["

+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "])

　　　　　　from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "]

　　　　　　from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder

 

if @strWhere != ''''

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["

+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["

+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) +" ["

+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "

+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

end

 

end

 

exec (@strSQL)

 

GO

上(shàng)面的(de)這(zhè)個(gè)存儲過程是(shì)一(yī / yì ／yí)個(gè)通用的(de)存儲過程，其注釋已寫在(zài)其中了(le／liǎo)。在(zài)大(dà)數據量的(de)情況下，特别是(shì)在(zài)查詢最後幾頁的(de)時(shí)候，查詢時(shí)間一(yī / yì ／yí)般不(bù)會超過9秒；而(ér)用其他(tā)存儲過程，在(zài)實踐中就(jiù)會導緻超時(shí)，所以(yǐ)這(zhè)個(gè)存儲過程非常适用于(yú)大(dà)容量數據庫的(de)查詢。筆者希望能夠通過對以(yǐ)上(shàng)存儲過程的(de)解析，能給大(dà)家帶來(lái)一(yī / yì ／yí)定的(de)啓示，并給工作帶來(lái)一(yī / yì ／yí)定的(de)效率提升，同時(shí)希望同行提出(chū)更優秀的(de)實時(shí)數據分頁算法。

（四）聚集索引的(de)重要(yào / yāo)性和(hé / huò)如何選擇聚集索引

在(zài)上(shàng)一(yī / yì ／yí)節的(de)标題中，筆者寫的(de)是(shì)：實現小數據量和(hé / huò)海量數據的(de)通用分頁顯示存儲過程。這(zhè)是(shì)因爲(wéi / wèi)在(zài)将本存儲過程應用于(yú)“辦公自動化”系統的(de)實踐中時(shí)，筆者發現這(zhè)第三種存儲過程在(zài)小數據量的(de)情況下，有如下現象：

1、分頁速度一(yī / yì ／yí)般維持在(zài)1秒和(hé / huò)3秒之(zhī)間。

2、在(zài)查詢最後一(yī / yì ／yí)頁時(shí)，速度一(yī / yì ／yí)般爲(wéi / wèi)5秒至8秒，哪怕分頁總數隻有3頁或30萬頁。

雖然在(zài)超大(dà)容量情況下，這(zhè)個(gè)分頁的(de)實現過程是(shì)很快的(de)，但在(zài)分前幾頁時(shí)，這(zhè)個(gè)1－3秒的(de)速度比起第一(yī / yì ／yí)種甚至沒有經過優化的(de)分頁方法速度還要(yào / yāo)慢，借用戶的(de)話說(shuō)就(jiù)是(shì)“還沒有ACCESS數據庫速度快”，這(zhè)個(gè)認識足以(yǐ)導緻用戶放棄使用您開發的(de)系統。

筆者就(jiù)此分析了(le／liǎo)一(yī / yì ／yí)下，原來(lái)産生這(zhè)種現象的(de)症結是(shì)如此的(de)簡單，但又如此的(de)重要(yào / yāo)：排序的(de)字段不(bù)是(shì)聚集索引！

本篇文章的(de)題目是(shì)：“查詢優化及分頁算法方案”。筆者隻所以(yǐ)把“查詢優化”和(hé / huò)“分頁算法”這(zhè)兩個(gè)聯系不(bù)是(shì)很大(dà)的(de)論題放在(zài)一(yī / yì ／yí)起，就(jiù)是(shì)因爲(wéi / wèi)二者都需要(yào / yāo)一(yī / yì ／yí)個(gè)非常重要(yào / yāo)的(de)東西――聚集索引。

在(zài)前面的(de)讨論中我們已經提到(dào)了(le／liǎo)，聚集索引有兩個(gè)最大(dà)的(de)優勢：

1、以(yǐ)最快的(de)速度縮小查詢範圍。

2、以(yǐ)最快的(de)速度進行字段排序。

第1條多用在(zài)查詢優化時(shí)，而(ér)第2條多用在(zài)進行分頁時(shí)的(de)數據排序。

而(ér)聚集索引在(zài)每個(gè)表内又隻能建立一(yī / yì ／yí)個(gè)，這(zhè)使得聚集索引顯得更加的(de)重要(yào / yāo)。聚集索引的(de)挑選可以(yǐ)說(shuō)是(shì)實現“查詢優化”和(hé / huò)“高效分頁”的(de)最關鍵因素。

但要(yào / yāo)既使聚集索引列既符合查詢列的(de)需要(yào / yāo)，又符合排序列的(de)需要(yào / yāo)，這(zhè)通常是(shì)一(yī / yì ／yí)個(gè)矛盾。筆者前面“索引”的(de)讨論中，将fariqi，即用戶發文日期作爲(wéi / wèi)了(le／liǎo)聚集索引的(de)起始列，日期的(de)精确度爲(wéi / wèi)“日”。這(zhè)種作法的(de)優點，前面已經提到(dào)了(le／liǎo)，在(zài)進行劃時(shí)間段的(de)快速查詢中，比用ID主鍵列有很大(dà)的(de)優勢。

但在(zài)分頁時(shí)，由于(yú)這(zhè)個(gè)聚集索引列存在(zài)着重複記錄，所以(yǐ)無法使用max或min來(lái)最爲(wéi / wèi)分頁的(de)參照物，進而(ér)無法實現更爲(wéi / wèi)高效的(de)排序。而(ér)如果将ID主鍵列作爲(wéi / wèi)聚集索引，那麽聚集索引除了(le／liǎo)用以(yǐ)排序之(zhī)外，沒有任何用處，實際上(shàng)是(shì)浪費了(le／liǎo)聚集索引這(zhè)個(gè)寶貴的(de)資源。

爲(wéi / wèi)解決這(zhè)個(gè)矛盾，筆者後來(lái)又添加了(le／liǎo)一(yī / yì ／yí)個(gè)日期列，其默認值爲(wéi / wèi)getdate()。用戶在(zài)寫入記錄時(shí)，這(zhè)個(gè)列自動寫入當時(shí)的(de)時(shí)間，時(shí)間精确到(dào)毫秒。即使這(zhè)樣，爲(wéi / wèi)了(le／liǎo)避免可能性很小的(de)重合，還要(yào / yāo)在(zài)此列上(shàng)創建UNIQUE約束。将此日期列作爲(wéi / wèi)聚集索引列。

有了(le／liǎo)這(zhè)個(gè)時(shí)間型聚集索引列之(zhī)後，用戶就(jiù)既可以(yǐ)用這(zhè)個(gè)列查找用戶在(zài)插入數據時(shí)的(de)某個(gè)時(shí)間段的(de)查詢，又可以(yǐ)作爲(wéi / wèi)唯一(yī / yì ／yí)列來(lái)實現max或min，成爲(wéi / wèi)分頁算法的(de)參照物。

經過這(zhè)樣的(de)優化，筆者發現，無論是(shì)大(dà)數據量的(de)情況下還是(shì)小數據量的(de)情況下，分頁速度一(yī / yì ／yí)般都是(shì)幾十毫秒，甚至0毫秒。而(ér)用日期段縮小範圍的(de)查詢速度比原來(lái)也(yě)沒有任何遲鈍。聚集索引是(shì)如此的(de)重要(yào / yāo)和(hé / huò)珍貴，所以(yǐ)筆者總結了(le／liǎo)一(yī / yì ／yí)下，一(yī / yì ／yí)定要(yào / yāo)将聚集索引建立在(zài)：

1、您最頻繁使用的(de)、用以(yǐ)縮小查詢範圍的(de)字段上(shàng)；

2、您最頻繁使用的(de)、需要(yào / yāo)排序的(de)字段上(shàng)。

結束語

本篇文章彙集了(le／liǎo)筆者近段在(zài)使用數據庫方面的(de)心得，是(shì)在(zài)做“辦公自動化”系統時(shí)實踐經驗的(de)積累。希望這(zhè)篇文章不(bù)僅能夠給大(dà)家的(de)工作帶來(lái)一(yī / yì ／yí)定的(de)幫助，也(yě)希望能讓大(dà)家能夠體會到(dào)分析問題的(de)方法；最重要(yào / yāo)的(de)是(shì)，希望這(zhè)篇文章能夠抛磚引玉，掀起大(dà)家的(de)學習和(hé / huò)讨論的(de)興趣，以(yǐ)共同促進，共同爲(wéi / wèi)公安科技強警事業和(hé / huò)金盾工程做出(chū)自己最大(dà)的(de)努力。

最後需要(yào / yāo)說(shuō)明的(de)是(shì)，在(zài)試驗中，我發現用戶在(zài)進行大(dà)數據量查詢的(de)時(shí)候，對數據庫速度影響最大(dà)的(de)不(bù)是(shì)内存大(dà)小，而(ér)是(shì)CPU。在(zài)我的(de)P4 2.4機器上(shàng)試驗的(de)時(shí)候，查看“資源管理器”，CPU經常出(chū)現持續到(dào)100%的(de)現象，而(ér)内存用量卻并沒有改變或者說(shuō)沒有大(dà)的(de)改變。即使在(zài)我們的(de)HP ML 350 G3服務器上(shàng)試驗時(shí)，CPU峰值也(yě)能達到(dào)90%，一(yī / yì ／yí)般持續在(zài)70%左右。

本文的(de)試驗數據都是(shì)來(lái)自我們的(de)HP ML 350服務器。服務器配置：雙Inter Xeon 超線程 CPU 2.4G，内存1G，操作系統Windows Server 2003 Enterprise Edition，數據庫SQL Server 2000 SP3

（完）

有索引情況下，insert速度一(yī / yì ／yí)定有影響，不(bù)過：
1. 你不(bù)大(dà)可能一(yī / yì ／yí)該不(bù)停地(dì / de)進行insert, SQL Server能把你傳來(lái)的(de)命令緩存起來(lái)，依次執行，不(bù)會漏掉任何一(yī / yì ／yí)個(gè)insert。
2. 你也(yě)可以(yǐ)建立一(yī / yì ／yí)個(gè)相同結構但不(bù)做索引的(de)表，insert數據先插入到(dào)這(zhè)個(gè)表裏，當這(zhè)個(gè)表中行數達到(dào)一(yī / yì ／yí)定行數再用insert table1 select * from table2這(zhè)樣的(de)命令整批插入到(dào)有索引的(de)那個(gè)表裏。

注：文章來(lái)源與網絡，僅供讀者參考！

人(rén)是(shì)有思想的(de)，這(zhè)是(shì)人(rén)與動物本質的(de)區别。人(rén)的(de)社會屬性要(yào / yāo)求我們在(zài)操守的(de)規範下實現自我價值，越有這(zhè)越給予。因此，我們要(yào / yāo)實現自己的(de)社會價值 。這(zhè)些都離不(bù)開堅定的(de)信仰，有無信仰是(shì)一(yī / yì ／yí)個(gè)在(zài)精神層面狀态好壞的(de)體現，不(bù)能覺得一(yī / yì ／yí)切都無所謂。生活是(shì)一(yī / yì ／yí)面鏡子(zǐ)，自己是(shì)什麽樣子(zǐ)很快現行。 用知識武裝自己，用信仰升華自己，用愛好裝點自己，用個(gè)性标識自己。 我就(jiù)是(shì)我，不(bù)一(yī / yì ／yí)樣的(de)煙火；我就(jiù)是(shì)我，不(bù)一(yī / yì ／yí)樣的(de)水果；我就(jiù)是(shì)我，不(bù)一(yī / yì ／yí)樣的(de)花朵；我就(jiù)是(shì)我，不(bù)一(yī / yì ／yí)樣的(de)自我。 生活寄語：越努力，越幸運。 做最好的(de)自己！