數(shù)據(jù)庫的查詢優(yōu)化及分頁算法方案

1、數(shù)據(jù)庫的查詢優(yōu)化及分頁算法方案在以下的文章中，我將以“辦公自動化”系統(tǒng)為例，探討如何在有著1000萬條數(shù)據(jù)的ms sql server數(shù)據(jù)庫中實現(xiàn)快速的數(shù)據(jù)提取和數(shù)據(jù)分頁。以下代碼說明了我們實例中數(shù)據(jù)庫的“紅頭文件”一表的部分數(shù)據(jù)結(jié)構(gòu)：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 ,-發(fā)布日期 ne

2、ibuyonghu varchar (70) collate chinese_prc_ci_as null ,-發(fā)布用戶 reader varchar (900) collate chinese_prc_ci_as null ,-需要瀏覽的用戶。每個用戶中間用分隔符“,”分開) on primary textimage_on primarygo下面，我們來往數(shù)據(jù)庫中添加1000萬條數(shù)據(jù)：declare i intset i=1while i=250000begin insert into tgongwen(fariqi,neibuyonghu,reader,title) values(2004

3、-2-5,通信科,通信科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經(jīng)偵支隊,戶政科,治安支隊,外事科,這是最先的25萬條記錄) set i=i+1endgodeclare i intset i=1while i=250000begin insert into tgongwen(fariqi,neibuyonghu,reader,title) values(2004-9-16,辦公室,辦公室,通信科,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經(jīng)偵支隊,戶政科,外事科,這是中間的25萬條記錄) set i=i+1endgodeclare

4、 h intset h=1while h=100begindeclare i intset i=2002while i=2003begindeclare j int set j=0 while j50 begindeclare k int set k=0 while k50 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),通信科,辦公室,通信科,王局長,劉局長,張

5、局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經(jīng)偵支隊,戶政科,外事科,這是最后的50萬條記錄) set k=k+1 endset j=j+1 endset i=i+1endset h=h+1endgodeclare i intset i=1while i dateadd(day,-90,getdate()用時：53763毫秒（54秒）（3）將聚合索引建立在日期列（fariqi）上：select gid,fariqi,neibuyonghu,title from tgongwenwhere fariqi dateadd(day,-90,getdate()用時：2423毫秒（2秒）雖然每條語

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

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

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

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

10、）；而如果僅用復(fù)合聚集索引的非起始列作為查詢條件的話，這個索引是不起任何作用的。當然，語句1、2的查詢速度一樣是因為查詢的條目數(shù)一樣，如果復(fù)合索引的所有列都用上，而且查詢結(jié)果少的話，這樣就會形成“索引覆蓋”，因而性能可以達到最優(yōu)。同時，請記?。簾o論您是否經(jīng)常使用聚合索引的其他列，但其前導(dǎo)列一定要是使用最頻繁的列。（四）其他書上沒有的索引使用經(jīng)驗總結(jié)1、用聚合索引比用不是聚合索引的主鍵速度快下面是實例語句：（都是提取25萬條數(shù)據(jù)）select gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi=2004-9-16使用時間：

11、3326毫秒select gid,fariqi,neibuyonghu,reader,title from tgongwen where gid2004-1-1用時：6343毫秒（提取100萬條）select gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi2004-6-6用時：3170毫秒（提取50萬條）select gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi=2004-9-16用時：3326毫秒（和上句的結(jié)果一模一樣。如果采集的數(shù)量一

12、樣，那么用大于號和等于號是一樣的）select gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi2004-1-1 and fariqi2004-1-1 order by fariqi用時：6390毫秒select gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi 10000和執(zhí)行:select * from table1 where tid 10000 and name=zhangsan一些人不知道以上兩條語句的執(zhí)行效率是否一樣，因為如果簡單的

13、從語句先后上看，這兩個語句的確是不一樣，如果tid是一個聚合索引，那么后一句僅僅從表的10000條以后的記錄中查找就行了；而前一句則要先從全表中查找看有幾個name=zhangsan的，而后再根據(jù)限制條件條件tid10000來提出查詢結(jié)果。事實上，這樣的擔心是不必要的。sql server中有一個“查詢分析優(yōu)化器”，它可以計算出where子句中的搜索條件并確定哪個索引能縮小表掃描的搜索空間，也就是說，它能實現(xiàn)自動優(yōu)化。雖然查詢優(yōu)化器可以根據(jù)where子句自動的進行查詢優(yōu)化，但大家仍然有必要了解一下“查詢優(yōu)化器”的工作原理，如非這樣，有時查詢優(yōu)化器就會不按照您的本意進行快速查詢。在查詢分析階段，

14、查詢優(yōu)化器查看查詢的每個階段并決定限制需要掃描的數(shù)據(jù)量是否有用。如果一個階段可以被用作一個掃描參數(shù)（sarg），那么就稱之為可優(yōu)化的，并且可以利用索引快速獲得所需數(shù)據(jù)。sarg的定義：用于限制搜索的一個操作，因為它通常是指一個特定的匹配，一個值得范圍內(nèi)的匹配或者兩個以上條件的and連接。形式如下：列名 操作符 或 操作符列名列名可以出現(xiàn)在操作符的一邊，而常數(shù)或變量出現(xiàn)在操作符的另一邊。如：name=張三價格500050005000如果一個表達式不能滿足sarg的形式，那它就無法限制搜索的范圍了，也就是sql server必須對每一行都判斷它是否滿足where子句中的所有條件。所以一個索引對于不

15、滿足sarg形式的表達式來說是無用的。介紹完sarg后，我們來總結(jié)一下使用sarg以及在實踐中遇到的和某些資料上結(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形式的語句最典型的情況就是包括非操作符的語句，如：

16、not、!=、!、not exists、not in、not like等，另外還有函數(shù)。下面就是幾個不滿足sarg形式的例子：abs(價格)5000sql server也會認為是sarg，sql server會將此式轉(zhuǎn)化為：where 價格2500/2但我們不推薦這樣使用，因為有時sql server不能保證這種轉(zhuǎn)化與原始表達式是完全等價的。4、in 的作用相當與or語句：select * from table1 where tid in (2,3)和select * from table1 where tid=2 or tid=3是一樣的，都會引起全表掃描，如果tid上有索引，其索引也會失效

17、。5、盡量少用not6、exists 和 in 的執(zhí)行效率是一樣的很多資料上都顯示說，exists要比in的執(zhí)行效率要高，同時應(yīng)盡可能的用not exists來代替not in。但事實上，我試驗了一下，發(fā)現(xiàn)二者無論是前面帶不帶not，二者之間的執(zhí)行效率都是一樣的。因為涉及子查詢，我們試驗這次用sql server自帶的pubs數(shù)據(jù)庫。運行前我們可以把sql server的statistics i/o狀態(tài)打開。（1）select title,price from titles where title_id in (select title_id from sales where qty30)該句

18、的執(zhí)行結(jié)果為：表 sales。掃描計數(shù) 18，邏輯讀 56 次，物理讀 0 次，預(yù)讀 0 次。表 titles。掃描計數(shù) 1，邏輯讀 2 次，物理讀 0 次，預(yù)讀 0 次。（2）select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty30)第二句的執(zhí)行結(jié)果為：表 sales。掃描計數(shù) 18，邏輯讀 56 次，物理讀 0 次，預(yù)讀 0 次。表 titles。掃描計數(shù) 1，邏輯讀 2 次，物理讀 0 次，預(yù)讀 0 次。我們從此可以看到用e

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

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

21、4-9-16 or gid9990000用時：68秒。掃描計數(shù) 1，邏輯讀 404008 次，物理讀 283 次，預(yù)讀 392163 次。select gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi=2004-9-16unionselect gid,fariqi,neibuyonghu,reader,title from tgongwen where gid9990000用時：9秒。掃描計數(shù) 8，邏輯讀 67489 次，物理讀 216 次，預(yù)讀 7499 次?？磥?，用union在通常情況下比用or的效率要高的多。但經(jīng)

22、過試驗，筆者發(fā)現(xiàn)如果or兩邊的查詢列是一樣的話，那么用union則反倒和用or的執(zhí)行速度差很多，雖然這里union掃描的是索引，而or掃描的是全表。select gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi=2004-9-16 or fariqi=2004-2-5用時：6423毫秒。掃描計數(shù) 2，邏輯讀 14726 次，物理讀 1 次，預(yù)讀 7176 次。select gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi=2004-9-16un

23、ionselect gid,fariqi,neibuyonghu,reader,title from tgongwen where fariqi=2004-2-5用時：11640毫秒。掃描計數(shù) 8，邏輯讀 14806 次，物理讀 108 次，預(yù)讀 1144 次。9、字段提取要按照“需多少、提多少”的原則，避免“select *”我們來做一個試驗：select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc用時：4673毫秒select top 10000 gid,fariqi,title from tgongw

24、en order by gid desc用時：1376毫秒select top 10000 gid,fariqi from tgongwen order by gid desc用時：80毫秒由此看來，我們每少提取一個字段，數(shù)據(jù)的提取速度就會有相應(yīng)的提升。提升的速度還要看您舍棄的字段的大小來判斷。10、count(*)不比count(字段)慢某些資料上說：用*會統(tǒng)計所有列，顯然要比一個世界的列名效率低。這種說法其實是沒有根據(jù)的。我們來看：select count(*) from tgongwen用時：1500毫秒select count(gid) from tgongwen用時：1483毫秒se

25、lect count(fariqi) from tgongwen用時：3140毫秒select count(title) from tgongwen用時：52050毫秒從以上可以看出，如果用count(*)和用count(主鍵)的速度是相當?shù)?，而count(*)卻比其他任何除主鍵以外的字段匯總速度要快，而且字段越長，匯總的速度就越慢。我想，如果用count(*)， sql server可能會自動查找最小字段來匯總的。當然，如果您直接寫count(主鍵)將會來的更直接些。11、order by按聚集索引列排序效率最高我們來看：（gid是主鍵，fariqi是聚合索引列）select top 100

26、00 gid,fariqi,reader,title from tgongwen用時：196 毫秒。 掃描計數(shù) 1，邏輯讀 289 次，物理讀 1 次，預(yù)讀 1527 次。select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc用時：4720毫秒。 掃描計數(shù) 1，邏輯讀 41956 次，物理讀 0 次，預(yù)讀 1287 次。select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc用時：4736毫秒。 掃描計數(shù) 1，邏輯讀 5

27、5350 次，物理讀 10 次，預(yù)讀 775 次。select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc用時：173毫秒。 掃描計數(shù) 1，邏輯讀 290 次，物理讀 0 次，預(yù)讀 0 次。select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc用時：156毫秒。 掃描計數(shù) 1，邏輯讀 289 次，物理讀 0 次，預(yù)讀 0 次。從以上我們可以看出，不排序的速度以及邏輯讀次數(shù)都是和“order by 聚集索引列”

28、 的速度是相當?shù)模@些都比“order by 非聚集索引列”的查詢速度是快得多的。同時，按照某個字段進行排序的時候，無論是正序還是倒序，速度是基本相當?shù)摹?2、高效的top事實上，在查詢和提取超大容量的數(shù)據(jù)集時，影響數(shù)據(jù)庫響應(yīng)時間的最大因素不是數(shù)據(jù)查找，而是物理的i/0操作。如：select top 10 * from (select top 10000 gid,fariqi,title from tgongwenwhere neibuyonghu=辦公室order by gid desc) as aorder by gid asc這條語句，從理論上講，整條語句的執(zhí)行時間應(yīng)該比子句的執(zhí)行時間

29、長，但事實相反。因為，子句執(zhí)行后返回的是10000條記錄，而整條語句僅返回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ù)庫oracle中卻沒有，這不能說不是一個遺憾，雖然在oracle中可以用其他方法（如：rownumber）來解決。在以后的關(guān)于“實現(xiàn)千萬級數(shù)據(jù)的分頁顯示存儲過程”的討論中，我們就將用到top這個關(guān)鍵詞。到此為止，我們上面討論

30、了如何實現(xiàn)從大容量的數(shù)據(jù)庫中快速地查詢出您所需要的數(shù)據(jù)方法。當然，我們介紹的這些方法都是“軟”方法，在實踐中，我們還要考慮各種“硬”因素，如：網(wǎng)絡(luò)性能、服務(wù)器的性能、操作系統(tǒng)的性能，甚至網(wǎng)卡、交換機等。三、實現(xiàn)小數(shù)據(jù)量和海量數(shù)據(jù)的通用分頁顯示存儲過程建立一個web 應(yīng)用，分頁瀏覽功能必不可少。這個問題是數(shù)據(jù)庫處理中十分常見的問題。經(jīng)典的數(shù)據(jù)分頁方法是:ado 紀錄集分頁法，也就是利用ado自帶的分頁功能（利用游標）來實現(xiàn)分頁。但這種分頁方法僅適用于較小數(shù)據(jù)量的情形，因為游標本身有缺點：游標是存放在內(nèi)存中，很費內(nèi)存。游標一建立，就將相關(guān)的記錄鎖住，直到取消游標。游標提供了對特定集合中逐行掃描的手

31、段，一般使用游標來逐行遍歷數(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ù)的方法大概就是“俄羅斯存儲過程”。這個存儲過程用了游標，由于游標的局限性，所以這個方法并沒有得到大家的普遍認可。后來，網(wǎng)上有人改造了此存儲過程，下面的存儲過程就是結(jié)合我們的辦公自動化實例寫的分頁存

32、儲過程：create procedure pagination1(pagesize int, -頁面大小，如每頁存儲20條記錄pageindex int -當前頁碼)asset nocount onbegindeclare indextable table(id int identity(1,1),nid int) -定義表變量declare pagelowerbound int -定義此頁的底碼declare pageupperbound int -定義此頁的頂碼set pagelowerbound=(pageindex-1)*pagesizeset pageupperbound=pagel

33、owerbound+pagesizeset rowcount pageupperboundinsert into indextable(nid) select gid from tgongwen where fariqi dateadd(day,-365,getdate() order by fariqi descselect o.gid,o.mid,o.title,o.fadanwei,o.fariqi from tgongwen o,indextable t where o.gid=t.nidand t.idpagelowerbound and t.id”或“200于是就有了如下分頁方案：

34、select top 頁大小 *from table1where id (select max (id) from (select top (頁碼-1)*頁大小) id from table1 order by id) as t ) order by id在選擇即不重復(fù)值，又容易分辨大小的列時，我們通常會選擇主鍵。下表列出了筆者用有著1000萬數(shù)據(jù)的辦公自動化系統(tǒng)中的表，在以gid（gid是主鍵，但并不是聚集索引。）為排序列、提取gid,fariqi,title字段，分別以第1、10、100、500、1000、1萬、10萬、25萬、50萬頁為例，測試以上三種分頁方案的執(zhí)行速度：（單位：毫秒）頁

35、 碼方案1方案2方案3160307610461663100107672013050054012943831000171104702501萬24796450014010萬3832642283155325萬28140128720233050萬1216861278467168從上表中，我們可以看出，三種存儲過程在執(zhí)行100頁以下的分頁命令時，都是可以信任的，速度都很好。但第一種方案在執(zhí)行分頁1000頁以上后，速度就降了下來。第二種方案大約是在執(zhí)行分頁1萬頁以上后速度開始降了下來。而第三種方案卻始終沒有大的降勢，后勁仍然很足。在確定了第三種分頁方案后，我們可以據(jù)此寫一個存儲過程。大家知道sql server的存儲過程是事先編譯好的sql語句，它的執(zhí)行效率要比通過web頁面?zhèn)鱽淼膕ql語句的執(zhí)行效率要高