程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> 海量數據庫查詢語句

海量數據庫查詢語句

編輯：MSSQL

海量數據庫查詢語句。本站提示廣大學習愛好者：（海量數據庫查詢語句）文章只能為提供參考，不一定能成為您想要的結果。以下是海量數據庫查詢語句正文

以下代碼解釋了我們實例中數據庫的“紅頭文件”一表的部門數據構造： CREATE TABLE [dbo].[TGongwen] (    --TGongwen是紅頭文件表名
   [Gid] [int] IDENTITY (1, 1) NOT NULL ,
--本表的id號，也是主鍵
   [title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
--紅頭文件的題目
   [fariqi] [datetime] NULL ,
--宣布日期
   [neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
--宣布用戶
   [reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS NULL ,
--須要閱讀的用戶。每一個用戶中央用分隔符“,”離開
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

上面，我們交往數據庫中添加1000萬條數據：

declare @i int
set @i=1
while @i<=250000
begin
insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','通訊科','通訊科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,治安支隊,外事科','這是最早的25萬筆記錄')
set @i=@i+1
end
GO

declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','辦公室','辦公室,通訊科,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,外事科','這是中央的25萬筆記錄')

set @i=@i+1

end

declare @h int
set @h=1
while @h<=100
begin
declare @i int
set @i=2002
while @i<=2003
begin
declare @j int
        set @j=0
        while @j<50
            begin
declare @k int
            set @k=0
            while @k<50
            begin
    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),'通訊科','辦公室,通訊科,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,外事科','這是最初的50萬筆記錄')
            set @k=@k+1
            end
set @j=@j+1
        end
set @i=@i+1
end
set @h=@h+1
end
GO declare @i int
set @i=1
while @i<=9000000
begin
    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','通訊科','通訊科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,治安支隊,外事科','這是最初添加的900萬筆記錄')
    set @i=@i+1000000
end
GO

經由過程以上語句，我們創立了25萬條由通訊科於2004年2月5日宣布的記載，25萬條由辦公室於2004年9月6日宣布的記載，2002年和2003年各100個2500條雷同日期、分歧分秒的由通訊科宣布的記載（共50萬條），還有由通訊科於2004年5月5日宣布的900萬筆記錄，算計1000萬條。

1、因情制宜，樹立“恰當”的索引

樹立“恰當”的索引是完成查詢優化的重要條件。

索引（index）是除表以外另外一主要的、用戶界說的存儲在物理介質上的數據構造。當依據索引碼的值搜刮數據時，索引供給了對數據的疾速拜訪。現實上，沒有索引,數據庫也能依據SELECT語句勝利地檢索到成果，但跟著表變得愈來愈年夜，應用“恰當”的索引的後果就愈來愈顯著。留意，在這句話中，我們用了“恰當”這個詞，這是由於，假如應用索引時不賣力斟酌其完成進程，索引既可以進步也會損壞數據庫的任務機能。

（一）深刻淺出懂得索引構造

現實上，您可以把索引懂得為一種特別的目次。微軟的SQL SERVER供給了兩種索引：集合索引（clustered index，也稱聚類索引、叢聚索引）和非集合索引（nonclustered index，也稱非聚類索引、非叢聚索引）。上面，我們舉例來講明一下集合索引和非集合索引的差別：

其實，我們的漢語字典的注釋自己就是一個集合索引。好比，我們要查“安”字，就會很天然地掀開字典的前幾頁，由於“安”的拼音是“an”，而依照拼音排序漢字的字典是以英文字母“a”開首並以“z”開頭的，那末“安”字就天然地排在字典的前部。假如您翻完了一切以“a”開首的部門依然找不到這個字，那末就解釋您的字典中沒有這個字；異樣的，假如查“張”字，那您也會將您的字典翻到最初部門，由於“張”的拼音是“zhang”。也就是說，字典的注釋部門自己就是一個目次，您不須要再去查其他目次來找到您須要找的內容。

我們把這類注釋內容自己就是一種依照必定規矩分列的目次稱為“集合索引”。

假如您熟悉某個字，您可以疾速地從主動中查到這個字。但您也能夠會碰到您不熟悉的字，不曉得它的發音，這時候候，您就不克不及依照適才的辦法找到您要查的字，而須要去依據“偏旁部首”查到您要找的字，然後依據這個字後的頁碼直接翻到某頁來找到您要找的字。但您聯合“部首目次”和“檢字表”而查到的字的排序其實不是真實的注釋的排序辦法，好比您查“張”字，我們可以看到在查部首以後的檢字表中“張”的頁碼是672頁，檢字表中“張”的下面是“馳”字，但頁碼倒是63頁，“張”的上面是“弩”字，頁面是390頁。很明顯，這些字其實不是真實的分離位於“張”字的高低方，如今您看到的持續的“馳、張、弩”三字現實上就是他們在非集合索引中的排序，是字典注釋中的字在非集合索引中的映照。我們可以經由過程這類方法來找到您所須要的字，但它須要兩個進程，先找到目次中的成果，然後再翻到您所須要的頁碼。

我們把這類目次純潔是目次，注釋純潔是注釋的排序方法稱為“非集合索引”。

經由過程以上例子，我們可以懂得到甚麼是“集合索引”和“非集合索引”。

進一步引伸一下，我們可以很輕易的懂得：每一個表只能有一個集合索引，由於目次只能依照一種辦法停止排序。

（二）什麼時候應用集合索引或非集合索引

上面的表總結了什麼時候應用集合索引或非集合索引（很主要）。

舉措描寫

應用集合索引

應用非集合索引

列常常被分組排序

應

前往某規模內的數據

應

不該

一個或少少分歧值

不該

小數量的分歧值

應

不該

年夜數量的分歧值

不該

應

頻仍更新的列

不該

應

外鍵列

應

主鍵列

應

頻仍修正索引列

不該

應

現實上，我們可以經由過程後面集合索引和非集合索引的界說的例子來懂得上表。如：前往某規模內的數據一項。好比您的某個表有一個時光列，正好您把聚合索引樹立在了該列，這時候您查詢2004年1月1日至2004年10月1日之間的全體數據時，這個速度就將是很快的，由於您的這本字典注釋是按日期停止排序的，聚類索引只須要找到要檢索的一切數據中的開首和開頭數據便可；而不像非集合索引，必需先查到目次中查到每項數據對應的頁碼，然後再依據頁碼查到詳細內容。

（三）聯合現實，談索引應用的誤區

實際的目標是運用。固然我們適才列出了什麼時候應應用集合索引或非集合索引，但在理論中以上規矩卻很輕易被疏忽或不克不及依據現實情形停止綜合剖析。上面我們將依據在理論中碰到的現實成績來談一下索引應用的誤區，以便於年夜家控制索引樹立的辦法。

1、主鍵就是集合索引

這類設法主意筆者以為是極端毛病的，是對集合索引的一種糟蹋。固然SQL SERVER默許是在主鍵上樹立集合索引的。

平日，我們會在每一個表中都樹立一個ID列，以辨別每條數據，而且這個ID列是主動增年夜的，步長普通為1。我們的這個辦公主動化的實例中的列Gid就是如斯。此時，假如我們將這個列設為主鍵，SQL SERVER會將此列默許為集合索引。如許做有利益，就是可讓您的數據在數據庫中依照ID停止物理排序，但筆者以為如許做意義不年夜。

不言而喻，集合索引的優勢是很顯著的，而每一個表中只能有一個集合索引的規矩，這使得集合索引變得加倍名貴。

從我們後面談到的集合索引的界說我們可以看出，應用集合索引的最年夜利益就是可以或許依據查詢請求，敏捷減少查詢規模，防止全表掃描。在現實運用中，由於ID號是主動生成的，我們其實不曉得每筆記錄的ID號，所以我們很難在理論頂用ID號來停止查詢。這就使讓ID號這個主鍵作為集合索引成為一種資本糟蹋。其次，讓每一個ID號都分歧的字段作為集合索引也不相符“年夜數量的分歧值情形下不該樹立聚合索引”規矩；固然，這類情形只是針對用戶常常修正記載內容，特殊是索引項的時刻會負感化，但關於查詢速度並沒有影響。

在辦公主動化體系中，不管是體系首頁顯示的須要用戶簽收的文件、會議照樣用戶停止文件查詢等任何情形下停止數據查詢都離不開字段的是“日期”還有效戶自己的“用戶名”。

平日，辦公主動化的首頁會顯示每一個用戶還沒有簽收的文件或會議。固然我們的where語句可以僅僅限制以後用戶還沒有簽收的情形，但假如您的體系已樹立了很長時光，而且數據量很年夜，那末，每次每一個用戶翻開首頁的時刻都停止一次全表掃描，如許做意義是不年夜的，絕年夜多半的用戶1個月前的文件都曾經閱讀過了，如許做只能徒增數據庫的開支罷了。現實上，我們完整可讓用戶翻開體系首頁時，數據庫僅僅查詢這個用戶近3個月來未閱覽的文件，經由過程“日期”這個字段來限制表掃描，進步查詢速度。假如您的辦公主動化體系曾經樹立的2年，那末您的首頁顯示速度實際大將是本來速度8倍，乃至更快。

在這裡之所以提到“實際上”三字，是由於假如您的集合索引照樣自覺地建在ID這個主鍵上時，您的查詢速度是沒有這麼高的，即便您在“日期”這個字段上樹立的索引（非聚合索引）。上面我們就來看一下在1000萬條數據量的情形下各類查詢的速度表示（3個月內的數據為25萬條）：

（1）僅在主鍵上樹立集合索引，而且不劃分時光段：

Select gid,fariqi,neibuyonghu,title from tgongwen

用時：128470毫秒（即：128秒）

（2）在主鍵上樹立集合索引，在fariq上樹立非集合索引：

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

用時：53763毫秒（54秒）

（3）將聚合索引樹立在日期列（fariqi）上：

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

用時：2423毫秒（2秒）

固然每條語句提掏出來的都是25萬條數據，各類情形的差別倒是偉大的，特殊是將集合索引樹立在日期列時的差別。現實上，假如您的數據庫真的有1000萬容量的話，把主鍵樹立在ID列上，就像以上的第1、2種情形，在網頁上的表示就是超時，基本就沒法顯示。這也是我摒棄ID列作為集合索引的一個最主要的身分。

得出以上速度的辦法是：在各個select語句前加：declare @d datetime

set @d=getdate()

並在select語句後加：

select [語句履行消費時光(毫秒)]=datediff(ms,@d,getdate())

2、只需樹立索引就可以明顯進步查詢速度

現實上，我們可以發明下面的例子中，第2、3條語句完整雷同，且樹立索引的字段也雷同；分歧的僅是前者在fariqi字段上樹立的長短聚合索引，後者在此字段上樹立的是聚合索引，但查詢速度卻有著天地之別。所以，並不是是在任何字段上簡略地樹立索引就可以進步查詢速度。

從建表的語句中，我們可以看到這個有著1000萬數據的表中fariqi字段有5003個分歧記載。在此字段上樹立聚合索引是再適合不外了。在實際中，我們天天都邑發幾個文件，這幾個文件的發文日期就雷同，這完整相符樹立集合索引請求的：“既不克不及絕年夜多半都雷同，又不克不及只要少少數雷同”的規矩。由此看來，我們樹立“恰當”的聚合索引關於我們進步查詢速度長短常主要的。

3、把一切須要進步查詢速度的字段都加進集合索引，以進步查詢速度

下面曾經談到：在停止數據查詢時都離不開字段的是“日期”還有效戶自己的“用戶名”。既然這兩個字段都是如斯的主要，我們可以把他們歸並起來，樹立一個復合索引（compound index）。

許多人以為只需把任何字段加進集合索引，就可以進步查詢速度，也有人覺得困惑：假如把復合的集合索引字段離開查詢，那末查詢速度會減慢嗎？帶著這個成績，我們來看一下以下的查詢速度（成果集都是25萬條數據）：（日期列fariqi起首排在復合集合索引的肇端列，用戶名neibuyonghu排在後列）

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

查詢速度：2513毫秒

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

查詢速度：2516毫秒

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

查詢速度：60280毫秒

從以上實驗中，我們可以看到假如僅用集合索引的肇端列作為查詢前提和同時用到復合集合索引的全體列的查詢速度是簡直一樣的，乃至比用上全體的復合索引列還要略快（在查詢成果集數量一樣的情形下）；而假如僅用復合集合索引的非肇端列作為查詢前提的話，這個索引是不起任何感化的。固然，語句1、2的查詢速度一樣是由於查詢的條目數一樣，假如復合索引的一切列都用上，並且查詢成果少的話，如許就會構成“索引籠罩”，因此機能可以到達最優。同時，請記住：不管您能否常常應用聚合索引的其他列，但其前導列必定如果應用最頻仍的列。

（四）其他書上沒有的索引應用經歷總結

1、用聚合索引比用不是聚合索引的主鍵速度快

上面是實例語句：（都是提取25萬條數據）

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

應用時光：3326毫秒

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

應用時光：4470毫秒

這裡，用聚合索引比用不是聚合索引的主鍵速度快了近1/4。

2、用聚合索引比用普通的主鍵作order by時速度快，特殊是在小數據量情形下

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

用時：12936

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

用時：18843

這裡，用聚合索引比用普通的主鍵作order by時，速度快了3/10。現實上，假如數據量很小的話，用集合索引作為排序列要比應用非集合索引速度快得顯著的多；而數據量假如很年夜的話，如10萬以上，則兩者的速度差異不顯著。

3、應用聚合索引內的時光段，搜刮時光會按數據占全部數據表的百分比成比例削減，而不管聚合索引應用了若干個

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

用時：6343毫秒（提取100萬條）

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

用時：3170毫秒（提取50萬條）

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

用時：3326毫秒（和上句的成果如出一轍。假如收集的數目一樣，那末用年夜於號和等於號是一樣的）

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

用時：3280毫秒

4 、日期列不會由於有分秒的輸出而減慢查詢速度

上面的例子中，共有100萬條數據，2004年1月1日今後的數據有50萬條，但只要兩個分歧的日期，日期准確到日；之前稀有據50萬條，有5000個分歧的日期，日期准確到秒。

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

用時：6390毫秒

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

用時：6453毫秒

（五）其他留意事項

“水可載舟，亦可覆舟”，索引也一樣。索引有助於進步檢索機能，但過量或欠妥的索引也會招致體系低效。由於用戶在表中每加進一個索引，數據庫就要做更多的任務。過量的索引乃至會招致索引碎片。

所以說，我們要樹立一個“恰當”的索引系統，特殊是對聚合索引的創立，更應千錘百煉，以使您的數據庫能獲得高機能的施展。

固然，在理論中，作為一個失職的數據庫治理員，您還要多測試一些計劃，找出哪一種計劃效力最高、最為有用。

2、改良SQL語句

許多人不曉得SQL語句在SQL SERVER中是若何履行的，他們擔憂本身所寫的SQL語句會被SQL SERVER誤會。好比：

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

和履行:

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

一些人不曉得以上兩條語句的履行效力能否一樣，由於假如簡略的從語句前後上看，這兩個語句切實其實是紛歧樣，假如tID是一個聚合索引，那末後一句僅僅從表的10000條今後的記載中查找就好了；而前一句則要先從全表中查找看有幾個name='zhangsan'的，爾後再依據限制前提前提tID>10000來提出查詢成果。

現實上，如許的擔憂是不用要的。SQL SERVER中有一個“查詢剖析優化器”，它可以盤算出where子句中的搜刮前提並肯定哪一個索引能減少表掃描的搜刮空間，也就是說，它能完成主動優化。

固然查詢優化器可以依據where子句主動的停止查詢優化，但年夜家依然有需要懂得一下“查詢優化器”的任務道理，如非如許，有時查詢優化器就會不依照您的本意停止疾速查詢。

在查詢剖析階段，查詢優化器檢查查詢的每一個階段並決議限制須要掃描的數據量能否有效。假如一個階段可以被用作一個掃描參數（SARG），那末就稱之為可優化的，而且可以應用索引疾速取得所需數據。

SARG的界說：用於限制搜刮的一個操作，由於它平日是指一個特定的婚配，一個值得規模內的婚配或許兩個以上前提的AND銜接。情勢以下：

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

或

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

列名可以湧現在操作符的一邊，而常數或變量湧現在操作符的另外一邊。如：

Name='張三'

價錢>5000

5000<價錢

Name='張三' and 價錢>5000

假如一個表達式不克不及知足SARG的情勢，那它就沒法限制搜刮的規模了，也就是SQL SERVER必需對每行都斷定它能否知足WHERE子句中的一切前提。所以一個索引關於不知足SARG情勢的表達式來講是無用的。

引見完SARG後，我們來總結一下應用SARG和在理論中碰到的和某些材料上結論分歧的經歷：

1、Like語句能否屬於SARG取決於所應用的通配符的類型

如：name like ‘張%' ，這就屬於SARG

而：name like ‘%張' ,就不屬於SARG。

緣由是通配符%在字符串的守舊使得索引沒法應用。

2、or 會惹起全表掃描

Name='張三' and 價錢>5000 符號SARG，而：Name='張三' or 價錢>5000 則不相符SARG。應用or會惹起全表掃描。

3、非操作符、函數惹起的不知足SARG情勢的語句

不知足SARG情勢的語句最典范的情形就是包含非操作符的語句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，別的還有函數。上面就是幾個不知足SARG情勢的例子：

ABS(價錢)<5000

Name like ‘%三'

有些表達式，如：

WHERE 價錢*2>5000

SQL SERVER也會以為是SARG，SQL SERVER會將此式轉化為：

WHERE 價錢>2500/2

但我們不推舉如許應用，由於有時SQL SERVER不克不及包管這類轉化與原始表達式是完整等價的。

4、IN 的感化相當與OR

語句：

Select * from table1 where tid in (2,3)

和

Select * from table1 where tid=2 or tid=3

是一樣的，都邑惹起全表掃描，假如tid上有索引，其索引也會掉效。

5、盡可能罕用NOT

6、exists 和 in 的履行效力是一樣的

許多材料上都顯示說，exists要比in的履行效力要高，同時應盡量的用not exists來取代not in。但現實上，我實驗了一下，發明兩者不管是後面帶不帶not，兩者之間的履行效力都是一樣的。由於觸及子查詢，我們實驗此次用SQL SERVER自帶的pubs數據庫。運轉前我們可以把SQL SERVER的statistics I/O狀況翻開。

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

該句的履行成果為：

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

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

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

第二句的履行成果為：

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

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

我們從此可以看到用exists和用in的履行效力是一樣的。

7、用函數charindex()和後面加通配符%的LIKE履行效力一樣

後面，我們談到，假如在LIKE後面加上通配符%，那末將會惹起全表掃描，所以其履行效力是低下的。但有的材料引見說，用函數charindex()來取代LIKE速度會有年夜的晉升，經我實驗，發明這類解釋也是毛病的：

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

用時：7秒，別的：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

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

用時：7秒，別的：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

8、union其實不相對比or的履行效力高

我們後面曾經談到了在where子句中應用or會惹起全表掃描，普通的，我所見過的材料都是推舉這裡用union來取代or。現實證實，這類說法關於年夜部門都是實用的。

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

用時：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

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

看來，用union在平日情形下比用or的效力要高的多。

但經由實驗，筆者發明假如or雙方的查詢列是一樣的話，那末用union則反倒和用or的履行速度差許多，固然這裡union掃描的是索引，而or掃描的是全表。

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

用時：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'

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

9、字段提取要依照“需若干、提若干”的准繩，防止“select *”

我們來做一個實驗：

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

用時：4673毫秒

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

用時：1376毫秒

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

用時：80毫秒

由此看來，我們每少提取一個字段，數據的提取速度就會有響應的晉升。晉升的速度還要看您捨棄的字段的年夜小來斷定。

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

某些材料上說：用*會統計一切列，明顯要比一個世界的列名效力低。這類說法實際上是沒有依據的。我們來看：

select count(*) from Tgongwen

用時：1500毫秒

select count(gid) from Tgongwen

用時：1483毫秒

select count(fariqi) from Tgongwen

用時：3140毫秒

select count(title) from Tgongwen

用時：52050毫秒

從以上可以看出，假如用count(*)和用count(主鍵)的速度是相當的，而count(*)卻比其他任何除主鍵之外的字段匯總速度要快，並且字段越長，匯總的速度就越慢。我想，假如用count(*)， SQL SERVER能夠會主動查找最小字段來匯總的。固然，假如您直接寫count(主鍵)將會來的更直接些。

11、order by按集合索引列排序效力最高

我們來看：（gid是主鍵，fariqi是聚合索引列）

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

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

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

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

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

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

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

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

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

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

從以上我們可以看出，不排序的速度和邏輯讀次數都是和“order by 集合索引列” 的速度是相當的，但這些都比“order by 非集合索引列”的查詢速度是快很多的。

同時，依照某個字段停止排序的時刻，不管是正序照樣倒序，速度是根本相當的。

12、高效的TOP

現實上，在查詢和提取超年夜容量的數據集時，影響數據庫呼應時光的最年夜身分不是數據查找，而是物理的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

這條語句，從實際上講，整條語句的履行時光應當比子句的履行時光長，但現實相反。由於，子句履行後前往的是10000筆記錄，而整條語句僅前往10條語句，所以影響數據庫呼應時光最年夜的身分是物理I/O操作。而限制物理I/O操作此處的最有用辦法之一就是應用TOP症結詞了。TOP症結詞是SQL SERVER中經由體系優化過的一個用來提取前幾條或前幾個百分比數據的詞。經筆者在理論中的運用，發明TOP確切很好用，效力也很高。但這個詞在別的一個年夜型數據庫ORACLE中卻沒有，這不克不及說不是一個遺憾，固然在ORACLE中可以用其他辦法（如：rownumber）來處理。在今後的關於“完成萬萬級數據的分頁顯示存儲進程”的評論辯論中，我們就將用到TOP這個症結詞。

到此為止，我們下面評論辯論了若何完成從年夜容量的數據庫中疾速地查詢出您所須要的數據辦法。固然，我們引見的這些辦法都是“軟”辦法，在理論中，我們還要斟酌各類“硬”身分，如：收集機能、辦事器的機能、操作體系的機能，乃至網卡、交流機等。

3、完成小數據量和海量數據的通用分頁顯示存儲進程

樹立一個web 運用，分頁閱讀功效必弗成少。這個成績是數據庫處置中非常罕見的成績。經典的數據分頁辦法是:ADO 記載集分頁法，也就是應用ADO自帶的分頁功效（應用游標）來完成分頁。但這類分頁辦法僅實用於較小數據量的情況，由於游標自己出缺點：游標是寄存在內存中，很費內存。游標一樹立，就將相干的記載鎖住，直到撤消游標。游標供給了對特定聚集中逐行掃描的手腕，普通應用游標來逐行遍歷數據，依據掏出數據前提的分歧停止分歧的操作。而關於多表和年夜表中界說的游標（年夜的數據聚集）輪回很輕易使法式進入一個漫長的期待乃至逝世機。

更主要的是，關於異常年夜的數據模子而言，分頁檢索時，假如依照傳統的每次都加載全部數據源的辦法長短常糟蹋資本的。如今風行的分頁辦法普通是檢索頁面年夜小的塊區的數據，而非檢索一切的數據，然後單步履行以後行。

最早較好地完成這類依據頁面年夜小和頁碼來提取數據的辦法年夜概就是“俄羅斯存儲進程”。這個存儲進程用了游標，因為游標的局限性，所以這個辦法並沒有獲得年夜家的廣泛承認。

後來，網上有人改革了此存儲進程，上面的存儲進程就是聯合我們的辦公主動化實例寫的分頁存儲進程：

CREATE procedure pagination1
(@pagesize int,  --頁面年夜小，如每頁存儲20筆記錄
@pageindex int   --以後頁碼
)
as
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int)  --界說表變量
declare @PageLowerBound int  --界說此頁的底碼
declare @PageUpperBound int  --界說此頁的頂碼
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

以上存儲進程應用了SQL SERVER的最新技巧――表變量。應當說這個存儲進程也是一個異常優良的分頁存儲進程。固然，在這個進程中，您也能夠把個中的表變量寫成暫時表：CREATE TABLE #Temp。但很顯著，在SQL SERVER中，用暫時表是沒有效表變量快的。所以筆者剛開端應用這個存儲進程時，感到異常的不錯，速度也比本來的ADO的好。但後來，我又發明了比此辦法更好的辦法。

筆者曾在網上看到了一篇小短文《從數據表中掏出第n條到第m條的記載的辦法》，全文以下：

從publish 表中掏出第 n 條到第 m 條的記載：

SELECT TOP m-n+1 *
FROM publish
WHERE (id NOT IN
　　　　(SELECT TOP n-1 id
　　　　 FROM publish))

id 為publish 表的症結字

我其時看到這篇文章的時刻，真的是精力為之一振，認為思緒異常得好。比及後來，我在作辦公主動化體系（ASP.NET+ C#＋SQL SERVER）的時刻，溘然想起了這篇文章，我想假如把這個語句改革一下，這便可能是一個異常好的分頁存儲進程。因而我就滿網上找這篇文章，沒想到，文章還沒找到，卻找到了一篇依據此語句寫的一個分頁存儲進程，這個存儲進程也是今朝較為風行的一種分頁存儲進程，我很懊悔沒有搶先把這段文字改革成存儲進程：

CREATE PROCEDURE pagination2
(
@SQL nVARCHAR(4000),    --不帶排序語句的SQL語句
@Page int,              --頁碼
@RecsPerPage int,       --每頁包容的記載數
@ID VARCHAR(255),       --須要排序的不反復的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

其實，以上語句可以簡化為：

SELECT TOP 頁年夜小 *
FROM Table1
WHERE (ID NOT IN
          (SELECT TOP 頁年夜小*頁數 id
         FROM 表
         ORDER BY id))
ORDER BY ID

但這個存儲進程有一個致命的缺陷，就是它含有NOT IN字樣。固然我可以把它改革為：

SELECT TOP 頁年夜小 *
FROM Table1
WHERE not exists
(select * from (select top (頁年夜小*頁數) * from table1 order by id) b where b.id=a.id )
order by id

即，用not exists來取代not in，但我們後面曾經談過了，兩者的履行效力現實上是沒有差別的。

既便如斯，用TOP 聯合NOT IN的這個辦法照樣比用游標要來得快一些。

固然用not exists其實不能搶救上個存儲進程的效力，但應用SQL SERVER中的TOP症結字倒是一個異常明智的選擇。由於分頁優化的終究目標就是防止發生過年夜的記載集，而我們在後面也曾經提到了TOP的優勢，經由過程TOP 便可完成對數據量的掌握。

在分頁算法中，影響我們查詢速度的症結身分有兩點：TOP和NOT IN。TOP可以進步我們的查詢速度，而NOT IN會減慢我們的查詢速度，所以要進步我們全部分頁算法的速度，就要完全改革NOT IN，同其他辦法來替換它。

我們曉得，簡直任何字段，我們都可以經由過程max(字段)或min(字段)來提取某個字段中的最年夜或最小值，所以假如這個字段不反復，那末便可以應用這些不反復的字段的max或min作為分水嶺，使其成為分頁算法平分開每頁的參照物。在這裡，我們可以用操作符“>”或“<”號來完成這個任務，使查詢語句相符SARG情勢。如：

Select top 10 * from table1 where id>200

因而就有了以下分頁計劃：

select top 頁年夜小 *
from table1
where id>
      (select max (id) from
      (select top ((頁碼-1)*頁年夜小) id from table1 order by id) as T
       )
  order by id

在選擇即不反復值，又輕易分辯年夜小的列時，我們平日會選擇主鍵。下表列出了筆者用有著1000萬數據的辦公主動化體系中的表，在以GID（GID是主鍵，但其實不是集合索引。）為排序列、提取gid,fariqi,title字段，分離以第1、10、100、500、1000、1萬、10萬、25萬、50萬頁為例，測試以上三種分頁計劃的履行速度：（單元：毫秒）

頁碼計劃1 計劃2 計劃3

1 60 30 76

10 46 16 63

100 1076 720 130

500 540 12943 83

1000 17110 470 250

1萬 24796 4500 140

10萬 38326 42283 1553

25萬 28140 128720 2330

50萬 121686 127846 7168

從上表中，我們可以看出，三種存儲進程在履行100頁以下的分頁敕令時，都是可以信賴的，速度都很好。但第一種計劃在履行分頁1000頁以上後，速度就降了上去。第二種計劃年夜約是在履行分頁1萬頁以上後速度開端降了上去。而第三種計劃卻一直沒有年夜的降勢，潛力依然很足。

在肯定了第三種分頁計劃後，我們可以據此寫一個存儲進程。年夜家曉得SQL SERVER的存儲進程是事前編譯好的SQL語句，它的履行效力要比經由過程WEB頁面傳來的SQL語句的履行效力要高。上面的存儲進程不只含有分頁計劃，還會依據頁面傳來的參數來肯定能否停止數據總數統計。

-- 獲得指定頁的數據

CREATE PROCEDURE pagination3
@tblName   varchar(255),       -- 表名
@strGetFields varchar(1000) = '*',  -- 須要前往的列
@fldName varchar(255)='',      -- 排序的字段名
@PageSize   int = 10,          -- 頁尺寸
@PageIndex  int = 1,           -- 頁碼
@doCount  bit = 0,   -- 前往記載總數, 非 0 值則前往
@OrderType bit = 0,  -- 設置排序類型, 非 0 值則降序
@strWhere  varchar(1500) = ''  -- 查詢前提 (留意: 不要加 where)
AS
declare @strSQL   varchar(5000)       -- 主語句
declare @strTmp   varchar(110)        -- 暫時變量
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
--以上代碼的意思是假如@doCount傳遞過去的不是0，就履行總數統計。以下的一切代碼都是@doCount為0的情形
else
begin

if @OrderType != 0
begin
    set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
--假如@OrderType不是0，就履行降序，這句很主要！
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
--假如是第一頁就履行以上代碼，如許會加速履行速度
end
else
begin
--以下代碼付與了@strSQL以真正履行的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

下面的這個存儲進程是一個通用的存儲進程，其正文已寫在個中了。

在年夜數據量的情形下，特殊是在查詢最初幾頁的時刻，查詢時光普通不會跨越9秒；而用其他存儲進程，在理論中就會招致超時，所以這個存儲進程異常實用於年夜容量數據庫的查詢。

筆者願望可以或許經由過程對以上存儲進程的解析，能給年夜家帶來必定的啟發，並給任務帶來必定的效力晉升，同時願望同業提出更優良的及時數據分頁算法。

4、集合索引的主要性和若何選擇集合索引

在上一節的題目中，筆者寫的是：完成小數據量和海量數據的通用分頁顯示存儲進程。這是由於在將本存儲進程運用於“辦公主動化”體系的理論中時，筆者發明這第三種存儲進程在小數據量的情形下，有以下景象：

1、分頁速度普通保持在1秒和3秒之間。

2、在查詢最初一頁時，速度普通為5秒至8秒，哪怕分頁總數只要3頁或30萬頁。

固然在超年夜容量情形下，這個分頁的完成進程是很快的，但在分前幾頁時，這個1－3秒的速度比起第一種乃至沒有經由優化的分頁辦法速度還要慢，借用戶的話說就是“還沒有ACCESS數據庫速度快”，這個熟悉足以招致用戶廢棄應用您開辟的體系。

筆者就此剖析了一下，本來發生這類景象的關鍵是如斯的簡略，但又如斯的主要：排序的字段不是集合索引！

本篇文章的標題是：“查詢優化及分頁算法計劃”。筆者只所以把“查詢優化”和“分頁算法”這兩個接洽不是很年夜的論題放在一路，就是由於兩者都須要一個異常主要的器械――集合索引。

在後面的評論辯論中我們曾經提到了，集合索引有兩個最年夜的優勢：

1、以最快的速度減少查詢規模。

2、以最快的速度停止字段排序。

第1條多用在查詢優化時，而第2條多用在停止分頁時的數據排序。

而集合索引在每一個表內又只能樹立一個，這使得集合索引顯得加倍的主要。集合索引的遴選可以說是完成“查詢優化”和“高效分頁”的最症結身分。

但要既使集合索引列既相符查詢列的須要，又相符排序列的須要，這平日是一個抵觸。

筆者後面“索引”的評論辯論中，將fariqi，即用戶發文日期作為了集合索引的肇端列，日期的准確度為“日”。這類作法的長處，後面曾經提到了，在停止劃時光段的疾速查詢中，比用ID主鍵列有很年夜的優勢。

但在分頁時，因為這個集合索引列存在側重復記載，所以沒法應用max或min來最為分頁的參照物，進而沒法完成更加高效的排序。而假如將ID主鍵列作為集合索引，那末集合索引除用以排序以外，沒有任何用途，現實上是糟蹋了集合索引這個名貴的資本。

為處理這個抵觸，筆者後來又添加了一個日期列，其默許值為getdate()。用戶在寫入記載時，這個列主動寫入其時的時光，時光准確到毫秒。即便如許，為了不能夠性很小的重合，還要在此列上創立UNIQUE束縛。將此日期列作為集合索引列。

有了這個時光型集合索引列以後，用戶就既可以用這個列查找用戶在拔出數據時的某個時光段的查詢，又可以作為獨一列來完成max或min，成為分頁算法的參照物。

經由如許的優化，筆者發明，不管是年夜數據量的情形下照樣小數據量的情形下，分頁速度普通都是幾十毫秒，乃至0毫秒。而用日期段減少規模的查詢速度比本來也沒有任何緩慢。

集合索引是如斯的主要和名貴，所以筆者總結了一下，必定要將集合索引樹立在：

1、您最頻仍應用的、用以減少查詢規模的字段上；

2、您最頻仍應用的、須要排序的字段上。

這個假如是我在csdn裡看到的誰人的話，那末這裡有幾個處所說的是有成績的。
好比
“主鍵就是集合索引
這類設法主意筆者以為是極端毛病的，是對集合索引的一種糟蹋。固然SQL SERVER默許是在主鍵上樹立集合索引的。”
我認為應當給主鍵加上集合索引。

他的測試數據也有成績，統一天內的記載竟然有25萬條，2004年5月5日的居然到達了900萬筆記錄。
如許的測試數據就招致了測試成果的廣泛性。
也就是說他的測試發生的結論能夠只合適1000萬筆記錄、不屈均散布的情形，卻不合適幾十萬筆記錄、均勻散布的情形。