SQL server 海量數(shù)據(jù)庫(kù)查詢(xún)優(yōu)化及分頁(yè)算法

1、SQL server海量數(shù)據(jù)庫(kù)查詢(xún)優(yōu)化及分頁(yè)算法在以下的文章中，我將以“辦公自動(dòng)化”系統(tǒng)為例，探討如何在有著1000萬(wàn)條 數(shù)據(jù)的MS SQL SERVER數(shù)據(jù)庫(kù)中實(shí)現(xiàn)快速的數(shù)據(jù)提取和數(shù)據(jù)分頁(yè)。以下代碼說(shuō)明 了我們實(shí)例中數(shù)據(jù)庫(kù)的“紅頭文件”一表的部分?jǐn)?shù)據(jù)結(jié)構(gòu)：CREATE TABLE dbo.TGongwen ( -TGongwen 是紅頭文件表名Gid int IDENTITY (1,1) NOT NULL ,-本表的id號(hào)，也是主鍵title varchar (80) COLLATE Chinese_PRC_CI_AS NULL ,hiiiiaiiiiaaiinii iiiaiiminlin

2、nminuniinnminnullHim u m ma in in min ,A-紅頭文件的標(biāo)題fariqi datetime NULL , imiiaiiiiaaiinii iiiaiiminlinnminuniinnminnullHim u m ma in in min -發(fā)布日期neibuYonghu varchar (70) COLLATE Chinese_PRC_CI_AS NULL ,-發(fā)布用戶(hù)reader varchar (900) COLLATE Chinese_PRC_CI_AS NULL ,-需要瀏覽的用戶(hù)。每個(gè)用戶(hù)中間用分隔符“，”分開(kāi)ON PRIMARY TEXTIMA

3、GE_ON PRIMARYGO下面，我們來(lái)往數(shù)據(jù)庫(kù)中添加1000萬(wàn)條數(shù)據(jù)：declare i intset i=1while i=250000begininsert into Tgongwen(fariqi,neibuyonghu,reader,title) values(2004-2-5,通信科，通信科，辦公室,王局長(zhǎng)，劉局長(zhǎng),張局長(zhǎng),admin, 刑偵支隊(duì)，特勤支隊(duì)，交巡警支隊(duì)，經(jīng)偵支隊(duì)，戶(hù)政科，治安支隊(duì),外事科，這是 最先的25萬(wàn)條記錄)set i=i+1endGOdeclare i intset i=1while i=250000begininsert into Tgongwen(fa

4、riqi,neibuyonghu,reader,title) values(2004-9T6,辦公室，辦公室，通信科,王局長(zhǎng)，劉局長(zhǎng)，張局長(zhǎng),admin, 刑偵支隊(duì)，特勤支隊(duì)，交巡警支隊(duì)，經(jīng)偵支 隊(duì)，戶(hù)政科,外事科，這是中間的25 萬(wàn)條記錄)set i=i+1endGOdeclare h intset h=1while h=100begindeclare i intset i=2002while i=2003begindeclare j intset j=0while j50begindeclare k intset k=0while k50begininsert into Tgongwen(

5、fariqi,neibuyonghu,reader,title) values(cast(i as varchar(4)+-8-15 3:+cast(j as varchar(2)+:+cast(j as varchar(2),通信科，辦公室，通信科,王局長(zhǎng)，劉局長(zhǎng),張局長(zhǎng),admin，刑偵支 隊(duì)，特勤支隊(duì)，交巡警支隊(duì)，經(jīng)偵支隊(duì),戶(hù)政科，外事 科，這是最后的50萬(wàn)條記 錄)set k=k+1endset j=j+1endIset i=i+1endset h=h+1I endGOdeclare i intset i=1Iwhile i dateadd(day,-90,getdate()I用時(shí)：5

6、3763毫秒(54秒)將聚合索引建立在日期列(fariqi) 上:select gid,fariqi,neibuyonghu,title from Tgongwenwhere fariqi dateadd(day,-90,getdate()用時(shí)：2423毫秒(2秒)雖然每條語(yǔ)句提取出來(lái)的都是25萬(wàn)條數(shù)據(jù)，各種情況的差異卻是巨大的， 特別是將聚集索引建立在日期列時(shí)的差異。事實(shí)上，如果您的數(shù)據(jù)庫(kù)真的有1000 萬(wàn)容量的話(huà)，把主鍵建立在ID列上，就像以上的第1、2種情況，在網(wǎng)頁(yè)上的表 現(xiàn)就是超時(shí)，根本就無(wú)法顯示。這也是我摒棄ID列作為聚集索引的一個(gè) 最重要 的因素。得出以上速度的方法是：在各個(gè)sel

7、ect語(yǔ)句前加：declare d datetimeset d=getdate()并在select語(yǔ)句后加：select 語(yǔ)句執(zhí)行花費(fèi)時(shí)間(毫秒)=datediff(ms,d,getdate()2、只要建立索引就能顯著提高查詢(xún)速度事實(shí)上，我們可以發(fā)現(xiàn)上面的例子中，第2、3條語(yǔ)句完全相同，且建立索 引的字段也相同;不同的僅是前者在fariqi字段上建立的是非聚合索引，后者在 此字段上建立的是聚合索引，但查詢(xún)速度卻有著天壤之別。所以，并非是在任何 字段上簡(jiǎn)單地建立索引就能提高查詢(xún)速度。從建表的語(yǔ)句中，我們可以看到這個(gè)有著1000萬(wàn)數(shù)據(jù)的表中fariqi字段有 5003個(gè)不同記錄。在此字段上建立聚合

8、索引是再合適不過(guò)了。在現(xiàn)實(shí)中，我們 每天都會(huì)發(fā)幾個(gè)文件，這幾個(gè)文件的發(fā)文日期就相同，這完全符合建立聚集索引 要求的：“既不能絕大多數(shù)都相同，又不能只有極少數(shù)相同”的規(guī)則。由此看 來(lái)，我們建立“適當(dāng)”的聚合索引對(duì)于我們提高查詢(xún)速度是非常重要的。3、把所有需要提高查詢(xún)速度的字段都加進(jìn)聚集索引，以提高查詢(xún)速度上面已經(jīng)談到：在進(jìn)行數(shù)據(jù)查詢(xún)時(shí)都離不開(kāi)字段的是“日期”還有用戶(hù)本身 的“用戶(hù)名”。既然這兩個(gè)字段都是如此的重要，我們可以把他們合并起來(lái)，建 立一個(gè)復(fù)合索引(compound index)。很多人認(rèn)為只要把任何字段加進(jìn)聚集索引，就能提高查詢(xún)速度，也有人感到 迷惑：如果把復(fù)合的聚集索引字段分開(kāi)查詢(xún)，

9、那么查詢(xún)速度會(huì)減慢嗎?帶著這個(gè) 問(wèn)題，我們來(lái)看一下以下的查詢(xún)速度(結(jié)果集都是25萬(wàn)條數(shù)據(jù))：(日期列fariqi 首先排在復(fù)合聚集索引的起始列，用戶(hù)名neibuyonghu排在 后列)select gid,fariqi,neibuyonghu,title from Tgongwen where fariq i2004-5-5查詢(xún)速度：2513毫秒 I ! I ( ! (llllll I ! ! ! (I select gid,fariqi,neibuyonghu,title from Tgongwen where fariq i2004-5-5 and neibuyonghu=辦公室查詢(xún)速度：

10、2516毫秒select gid,fariqi,neibuyonghu,title from Tgongwen where neibu yonghu=辦公室查詢(xún)速度：60280毫秒從以上試驗(yàn)中，我們可以看到如果僅用聚集索引的起始列作為查詢(xún)條件和同 時(shí)用到復(fù)合聚集索引的全部列的查詢(xún)速度是幾乎一樣的，甚至比用上全部的復(fù) 合索引列還要略快(在查詢(xún)結(jié)果集數(shù)目一樣的情況下)；而如果僅用復(fù)合聚集索引 的非起始列作為查詢(xún)條件的話(huà)，這個(gè)索引是不起任何作用的。當(dāng)然，語(yǔ)句1、2 的查詢(xún)速度一樣是因?yàn)椴樵?xún)的條目數(shù)一樣，如果復(fù)合索引的所有列都用上，而 且查詢(xún)結(jié)果少的話(huà)，這樣就會(huì)形成“索引覆蓋”，因而性能可以達(dá)到最優(yōu)。

11、同時(shí)， 請(qǐng)記住：無(wú)論您是否經(jīng)常使用聚合索引的其他列，但其前導(dǎo)列一定要是使用最 頻繁的列。（四）其他書(shū)上沒(méi)有的索引使用經(jīng)驗(yàn)總結(jié)1、用聚合索引比用不是聚合索引的主鍵速度快下面是實(shí)例語(yǔ)句：（都是提取25萬(wàn)條數(shù)據(jù)）select gid,fariqi,neibuyonghu,reader,title ariqi=2004-9-16fromTgongwenwhere f使用時(shí)間：3326毫秒select gid,fariqi,neibuyonghu,reader,title id2004-1-1用時(shí)：6343毫秒（提取100萬(wàn)條）select gid,fariqi,neibuyonghu,reader,t

12、itle from Tgongwen where f ariqi2004-6-6用時(shí)：3170毫秒（提取50萬(wàn)條）select gid,fariqi,neibuyonghu,reader,title from Tgongwen where f ariqi=2004-9-16用時(shí)：3326毫秒（和上句的結(jié)果一模一樣。如果采集的數(shù)量一樣，那么用大 于號(hào)和等于號(hào)是一樣的）select gid,fariqi,neibuyonghu,reader,title from Tgongwen where f ariqi2004TT and fariqi2004TT order by fariqi用時(shí)：6390

13、毫秒select gid,fariqi,neibuyonghu,reader,title from Tgongwen where f ariqi 10000和執(zhí)行：select * from table1 where tID 10000 and name=zhangsan，一些人不知道以上兩條語(yǔ)句的執(zhí)行效率是否一樣，因?yàn)槿绻?jiǎn)單的從語(yǔ)句先 后上看，這兩個(gè)語(yǔ)句的確是不一樣，如果tID是一個(gè)聚合索引，那么后一句僅 僅從表的10000條以后的記錄中查找就行了;而前一句則要先從全表中查找看有 幾個(gè)name=zhangsan的，而后再根據(jù)限制條件條件tID& gt;10000來(lái)提出查詢(xún) 結(jié)果。事實(shí)上，這

14、樣的擔(dān)心是不必要的oSQL SERVER中有一個(gè)“查詢(xún)分析優(yōu)化器”， 它可以計(jì)算出where子句中的搜索條件并確定哪個(gè)索引能縮小表掃描的搜索空 間，也就是說(shuō)，它能實(shí)現(xiàn)自動(dòng)優(yōu)化。雖然查詢(xún)優(yōu)化器可以根據(jù)where子句自動(dòng)的進(jìn)行查詢(xún)優(yōu)化，但大家仍然有必 要了解一下“查詢(xún)優(yōu)化器”的工作原理，如非這樣，有時(shí)查詢(xún)優(yōu)化器就會(huì)不按照 您的本意進(jìn)行快速查詢(xún)。在查詢(xún)分析階段，查詢(xún)優(yōu)化器查看查詢(xún)的每個(gè)階段并決定限制需要掃描的數(shù) 據(jù)量是否有用。如果一個(gè)階段可以被用作一個(gè)掃描參數(shù)(SARG)，那么就稱(chēng)之為可 優(yōu)化的，并且可以利用索引快速獲得所需數(shù)據(jù)。SARG的定義：用于限制搜索的一個(gè)操作，因?yàn)樗ǔＪ侵敢粋€(gè)特定的匹配，

15、 一個(gè)值得范圍內(nèi)的匹配或者兩個(gè)以上條件的AND連接。形式如下：列名操作符常數(shù)或變量常數(shù)或變量操作符列名列名可以出現(xiàn)在操作符的一邊，而常數(shù)或變量出現(xiàn)在操作符的另一邊。如：Name=張三價(jià)格50005000價(jià)格Name=張三 and 價(jià)格5000如果一個(gè)表達(dá)式不能滿(mǎn)足SARG的形式，那它就無(wú)法限制搜索的范圍了，也 就是SQL SERVER必須對(duì)每一行都判斷它是否滿(mǎn)足WHERE子句中的所有條件。所 以一個(gè)索引對(duì)于不滿(mǎn)足SARG形式的表達(dá)式來(lái)說(shuō)是無(wú)用的。介紹完SARG后，我們來(lái)總結(jié)一下使用SARG以及在實(shí)踐中遇到的和某些資料上 結(jié)論不同的經(jīng)驗(yàn)：1、Like語(yǔ)句是否屬于SARG取決于所使用的通配符的類(lèi)型

16、如：name like 張 ，這就屬于SARG而：name like 張，就不屬于SARG。原因是通配符%在字符串的開(kāi)通使得索引無(wú)法使用。2、or會(huì)引起全表掃描Name=張三 and價(jià)格5000符號(hào)SARG，而：Name=張三 or價(jià) 格5000則不符合SARG。使用or會(huì)引起全表掃描。3、非操作符、函數(shù)引起的不滿(mǎn)足SARG形式的語(yǔ)句不滿(mǎn)足SARG形式的語(yǔ)句最典型的情況就是包括非操作符的語(yǔ)句，如:NOT、！二、 、！、！、NOT EXISTS、NOT IN、NOT LIKE等，另外還有函數(shù)。下面就是幾 個(gè)不滿(mǎn)足SARG形式的例子：ABS（價(jià)格）5000Name like 三有些表達(dá)式，如：WH

17、ERE 價(jià)格*25000SQL SERVER也會(huì)認(rèn)為是SARG, SQL SERVER會(huì)將此式轉(zhuǎn)化為：WHERE 價(jià)格2500/2但我們不推薦這樣使用，因?yàn)橛袝r(shí)SQL SERVER不能保證這種轉(zhuǎn)化與原始表 達(dá)式是完全等價(jià)的。4、IN的作用相當(dāng)與OR語(yǔ)句：Select * from table1 where tid in (2,3)和Select * from table1 where tid=2 or tid=3是一樣的，都會(huì)引起全表掃描，如果tid上有索引，其索引也會(huì)失效。5、盡量少用NOT6、exists和in的執(zhí)行效率是一樣的很多資料上都顯示說(shuō)，exists要比in的執(zhí)行效率要高，同時(shí)應(yīng)

18、盡可能的用 not exists來(lái)代替not in。但事實(shí)上，我試驗(yàn)了一下，發(fā)現(xiàn)二者無(wú)論是前面帶 不帶not，二者之間的執(zhí)行效率都是一樣的。因?yàn)樯婕白硬樵?xún)，我們?cè)囼?yàn)這次用 SQL SERVER自帶的pubs數(shù)據(jù)庫(kù)。運(yùn)行前我們可以把SQL SERVER的statistics I/O 狀態(tài)打開(kāi)。select title,price from titles where title_id in (select t itle_id from sales where qty30)該句的執(zhí)行結(jié)果為：表sales。掃描計(jì)數(shù)18，邏輯讀56次，物理讀0次，預(yù)讀0次。表titles。掃描計(jì)數(shù)1，邏輯讀2次，物理讀

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

20、下的。但有的資料介紹說(shuō)，用函數(shù)charindex()來(lái)代替LIKE速 度會(huì)有大的提升，經(jīng)我試驗(yàn)，發(fā)現(xiàn)這種說(shuō)明也是錯(cuò)誤的：select gid,title,fariqi,reader from tgongwen where charindex(刑偵支隊(duì)，reader)0 and fariqi2004-5-5I用時(shí)：7秒，另外：掃描計(jì)數(shù)4,邏輯讀7155次，物理讀0次，預(yù)讀0次。select gid,title,fariqi,reader from tgongwen where reader like % + 刑偵支隊(duì)+ % and fariqi2004-5-5用時(shí)：7秒，另外：掃描計(jì)數(shù)4,邏輯讀

21、7155次，物理讀0次，預(yù)讀0次。8、union并不絕對(duì)比or的執(zhí)行效率高我們前面已經(jīng)談到了在where子句中使用or會(huì)引起全表掃描，一般的，我 所見(jiàn)過(guò)的資料都是推薦這里用union來(lái)代替or。事實(shí)證明，這種說(shuō)法對(duì)于大部 分都是適用的。select gid,fariqi,neibuyonghu,reader,title from Tgongwen where f ariqi=2004-9-16 or gid9990000用時(shí)：68秒。掃描計(jì)數(shù)1，邏輯讀404008次，物理讀283次，預(yù)讀392163 次。select gid,fariqi,neibuyonghu,reader,title from Tgongw