海量數(shù)據(jù)庫(kù)的查詢優(yōu)化及分頁(yè)算法方案集合1/2第2/2頁(yè)
應(yīng)
應(yīng)
主鍵列
應(yīng)
應(yīng)
頻繁修改索引列
不應(yīng)
應(yīng)
事實(shí)上,我們可以通過(guò)前面聚集索引和非聚集索引的定義的例子來(lái)理解上表。如:返回某范圍內(nèi)的數(shù)據(jù)一項(xiàng)。比如您的某個(gè)表有一個(gè)時(shí)間列,恰好您把聚合索引建立在了該列,這時(shí)您查詢2004年1月1日至2004年10月1日之間的全部數(shù)據(jù)時(shí),這個(gè)速度就將是很快的,因?yàn)槟倪@本字典正文是按日期進(jìn)行排序的,聚類(lèi)索引只需要找到要檢索的所有數(shù)據(jù)中的開(kāi)頭和結(jié)尾數(shù)據(jù)即可;而不像非聚集索引,必須先查到目錄中查到每一項(xiàng)數(shù)據(jù)對(duì)應(yīng)的頁(yè)碼,然后再根據(jù)頁(yè)碼查到具體內(nèi)容。
(三)結(jié)合實(shí)際,談索引使用的誤區(qū)
理論的目的是應(yīng)用。雖然我們剛才列出了何時(shí)應(yīng)使用聚集索引或非聚集索引,但在實(shí)踐中以上規(guī)則卻很容易被忽視或不能根據(jù)實(shí)際情況進(jìn)行綜合分析。下面我們將根據(jù)在實(shí)踐中遇到的實(shí)際問(wèn)題來(lái)談一下索引使用的誤區(qū),以便于大家掌握索引建立的方法。
1、主鍵就是聚集索引
這種想法筆者認(rèn)為是極端錯(cuò)誤的,是對(duì)聚集索引的一種浪費(fèi)。雖然SQL SERVER默認(rèn)是在主鍵上建立聚集索引的。
通常,我們會(huì)在每個(gè)表中都建立一個(gè)ID列,以區(qū)分每條數(shù)據(jù),并且這個(gè)ID列是自動(dòng)增大的,步長(zhǎng)一般為1。我們的這個(gè)辦公自動(dòng)化的實(shí)例中的列Gid就是如此。此時(shí),如果我們將這個(gè)列設(shè)為主鍵,SQL SERVER會(huì)將此列默認(rèn)為聚集索引。這樣做有好處,就是可以讓您的數(shù)據(jù)在數(shù)據(jù)庫(kù)中按照ID進(jìn)行物理排序,但筆者認(rèn)為這樣做意義不大。
顯而易見(jiàn),聚集索引的優(yōu)勢(shì)是很明顯的,而每個(gè)表中只能有一個(gè)聚集索引的規(guī)則,這使得聚集索引變得更加珍貴。
從我們前面談到的聚集索引的定義我們可以看出,使用聚集索引的最大好處就是能夠根據(jù)查詢要求,迅速縮小查詢范圍,避免全表掃描。在實(shí)際應(yīng)用中,因?yàn)镮D號(hào)是自動(dòng)生成的,我們并不知道每條記錄的ID號(hào),所以我們很難在實(shí)踐中用ID號(hào)來(lái)進(jìn)行查詢。這就使讓ID號(hào)這個(gè)主鍵作為聚集索引成為一種資源浪費(fèi)。其次,讓每個(gè)ID號(hào)都不同的字段作為聚集索引也不符合“大數(shù)目的不同值情況下不應(yīng)建立聚合索引”規(guī)則;當(dāng)然,這種情況只是針對(duì)用戶經(jīng)常修改記錄內(nèi)容,特別是索引項(xiàng)的時(shí)候會(huì)負(fù)作用,但對(duì)于查詢速度并沒(méi)有影響。
在辦公自動(dòng)化系統(tǒng)中,無(wú)論是系統(tǒng)首頁(yè)顯示的需要用戶簽收的文件、會(huì)議還是用戶進(jìn)行文件查詢等任何情況下進(jìn)行數(shù)據(jù)查詢都離不開(kāi)字段的是“日期”還有用戶本身的“用戶名”。
通常,辦公自動(dòng)化的首頁(yè)會(huì)顯示每個(gè)用戶尚未簽收的文件或會(huì)議。雖然我們的where語(yǔ)句可以僅僅限制當(dāng)前用戶尚未簽收的情況,但如果您的系統(tǒng)已建立了很長(zhǎng)時(shí)間,并且數(shù)據(jù)量很大,那么,每次每個(gè)用戶打開(kāi)首頁(yè)的時(shí)候都進(jìn)行一次全表掃描,這樣做意義是不大的,絕大多數(shù)的用戶1個(gè)月前的文件都已經(jīng)瀏覽過(guò)了,這樣做只能徒增數(shù)據(jù)庫(kù)的開(kāi)銷(xiāo)而已。事實(shí)上,我們完全可以讓用戶打開(kāi)系統(tǒng)首頁(yè)時(shí),數(shù)據(jù)庫(kù)僅僅查詢這個(gè)用戶近3個(gè)月來(lái)未閱覽的文件,通過(guò)“日期”這個(gè)字段來(lái)限制表掃描,提高查詢速度。如果您的辦公自動(dòng)化系統(tǒng)已經(jīng)建立的2年,那么您的首頁(yè)顯示速度理論上將是原來(lái)速度8倍,甚至更快。
在這里之所以提到“理論上”三字,是因?yàn)槿绻木奂饕€是盲目地建在ID這個(gè)主鍵上時(shí),您的查詢速度是沒(méi)有這么高的,即使您在“日期”這個(gè)字段上建立的索引(非聚合索引)。下面我們就來(lái)看一下在1000萬(wàn)條數(shù)據(jù)量的情況下各種查詢的速度表現(xiàn)(3個(gè)月內(nèi)的數(shù)據(jù)為25萬(wàn)條):
(1)僅在主鍵上建立聚集索引,并且不劃分時(shí)間段:
Select gid,fariqi,neibuyonghu,title from tgongwen
用時(shí):128470毫秒(即:128秒)
(2)在主鍵上建立聚集索引,在fariq上建立非聚集索引:
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用時(shí):53763毫秒(54秒)
(3)將聚合索引建立在日期列(fariqi)上:
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用時(shí):2423毫秒(2秒)
雖然每條語(yǔ)句提取出來(lái)的都是25萬(wàn)條數(shù)據(jù),各種情況的差異卻是巨大的,特別是將聚集索引建立在日期列時(shí)的差異。事實(shí)上,如果您的數(shù)據(jù)庫(kù)真的有1000萬(wàn)容量的話,把主鍵建立在ID列上,就像以上的第1、2種情況,在網(wǎng)頁(yè)上的表現(xiàn)就是超時(shí),根本就無(wú)法顯示。這也是我摒棄ID列作為聚集索引的一個(gè)最重要的因素。
得出以上速度的方法是:在各個(gè)select語(yǔ)句前加:declare @d datetime
set @d=getdate()
并在select語(yǔ)句后加:
select [語(yǔ)句執(zhí)行花費(fèi)時(shí)間(毫秒)]=datediff(ms,@d,getdate())
2、只要建立索引就能顯著提高查詢速度
事實(shí)上,我們可以發(fā)現(xiàn)上面的例子中,第2、3條語(yǔ)句完全相同,且建立索引的字段也相同;不同的僅是前者在fariqi字段上建立的是非聚合索引,后者在此字段上建立的是聚合索引,但查詢速度卻有著天壤之別。所以,并非是在任何字段上簡(jiǎn)單地建立索引就能提高查詢速度。
從建表的語(yǔ)句中,我們可以看到這個(gè)有著1000萬(wàn)數(shù)據(jù)的表中fariqi字段有5003個(gè)不同記錄。在此字段上建立聚合索引是再合適不過(guò)了。在現(xiàn)實(shí)中,我們每天都會(huì)發(fā)幾個(gè)文件,這幾個(gè)文件的發(fā)文日期就相同,這完全符合建立聚集索引要求的:“既不能絕大多數(shù)都相同,又不能只有極少數(shù)相同”的規(guī)則。由此看來(lái),我們建立“適當(dāng)”的聚合索引對(duì)于我們提高查詢速度是非常重要的。
3、把所有需要提高查詢速度的字段都加進(jìn)聚集索引,以提高查詢速度
上面已經(jīng)談到:在進(jìn)行數(shù)據(jù)查詢時(shí)都離不開(kāi)字段的是“日期”還有用戶本身的“用戶名”。既然這兩個(gè)字段都是如此的重要,我們可以把他們合并起來(lái),建立一個(gè)復(fù)合索引(compound index)。
很多人認(rèn)為只要把任何字段加進(jìn)聚集索引,就能提高查詢速度,也有人感到迷惑:如果把復(fù)合的聚集索引字段分開(kāi)查詢,那么查詢速度會(huì)減慢嗎?帶著這個(gè)問(wèn)題,我們來(lái)看一下以下的查詢速度(結(jié)果集都是25萬(wàn)條數(shù)據(jù)):(日期列fariqi首先排在復(fù)合聚集索引的起始列,用戶名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毫秒
從以上試驗(yàn)中,我們可以看到如果僅用聚集索引的起始列作為查詢條件和同時(shí)用到復(fù)合聚集索引的全部列的查詢速度是幾乎一樣的,甚至比用上全部的復(fù)合索引列還要略快(在查詢結(jié)果集數(shù)目一樣的情況下);而如果僅用復(fù)合聚集索引的非起始列作為查詢條件的話,這個(gè)索引是不起任何作用的。當(dāng)然,語(yǔ)句1、2的查詢速度一樣是因?yàn)椴樵兊臈l目數(shù)一樣,如果復(fù)合索引的所有列都用上,而且查詢結(jié)果少的話,這樣就會(huì)形成“索引覆蓋”,因而性能可以達(dá)到最優(yōu)。同時(shí),請(qǐng)記?。簾o(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 from Tgongwen where fariqi='2004-9-16'
使用時(shí)間:3326毫秒
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000
使用時(shí)間:4470毫秒
這里,用聚合索引比用不是聚合索引的主鍵速度快了近1/4。
2、用聚合索引比用一般的主鍵作order by時(shí)速度快,特別是在小數(shù)據(jù)量情況下
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
這里,用聚合索引比用一般的主鍵作order by時(shí),速度快了3/10。事實(shí)上,如果數(shù)據(jù)量很小的話,用聚集索引作為排序列要比使用非聚集索引速度快得明顯的多;而數(shù)據(jù)量如果很大的話,如10萬(wàn)以上,則二者的速度差別不明顯。
3、使用聚合索引內(nèi)的時(shí)間段,搜索時(shí)間會(huì)按數(shù)據(jù)占整個(gè)數(shù)據(jù)表的百分比成比例減少,而無(wú)論聚合索引使用了多少個(gè)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'
用時(shí):6343毫秒(提取100萬(wàn)條)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'
用時(shí):3170毫秒(提取50萬(wàn)條)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'
用時(shí):3326毫秒(和上句的結(jié)果一模一樣。如果采集的數(shù)量一樣,那么用大于號(hào)和等于號(hào)是一樣的)
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'
用時(shí):3280毫秒
4 、日期列不會(huì)因?yàn)橛蟹置氲妮斎攵鴾p慢查詢速度
下面的例子中,共有100萬(wàn)條數(shù)據(jù),2004年1月1日以后的數(shù)據(jù)有50萬(wàn)條,但只有兩個(gè)不同的日期,日期精確到日;之前有數(shù)據(jù)50萬(wàn)條,有5000個(gè)不同的日期,日期精確到秒。
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毫秒
(五)其他注意事項(xiàng)
“水可載舟,亦可覆舟”,索引也一樣。索引有助于提高檢索性能,但過(guò)多或不當(dāng)?shù)乃饕矔?huì)導(dǎo)致系統(tǒng)低效。因?yàn)橛脩粼诒碇忻考舆M(jìn)一個(gè)索引,數(shù)據(jù)庫(kù)就要做更多的工作。過(guò)多的索引甚至?xí)?dǎo)致索引碎片。
所以說(shuō),我們要建立一個(gè)“適當(dāng)”的索引體系,特別是對(duì)聚合索引的創(chuàng)建,更應(yīng)精益求精,以使您的數(shù)據(jù)庫(kù)能得到高性能的發(fā)揮。
當(dāng)然,在實(shí)踐中,作為一個(gè)盡職的數(shù)據(jù)庫(kù)管理員,您還要多測(cè)試一些方案,找出哪種方案效率最高、最為有效。
二、改善SQL語(yǔ)句
很多人不知道SQL語(yǔ)句在SQL SERVER中是如何執(zhí)行的,他們擔(dān)心自己所寫(xiě)的SQL語(yǔ)句會(huì)被SQL SERVER誤解。比如:
select * from table1 where name='zhangsan' and tID > 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>10000來(lái)提出查詢結(jié)果。
事實(shí)上,這樣的擔(dān)心是不必要的。SQL SERVER中有一個(gè)“查詢分析優(yōu)化器”,它可以計(jì)算出where子句中的搜索條件并確定哪個(gè)索引能縮小表掃描的搜索空間,也就是說(shuō),它能實(shí)現(xiàn)自動(dòng)優(yōu)化。
雖然查詢優(yōu)化器可以根據(jù)where子句自動(dòng)的進(jìn)行查詢優(yōu)化,但大家仍然有必要了解一下“查詢優(yōu)化器”的工作原理,如非這樣,有時(shí)查詢優(yōu)化器就會(huì)不按照您的本意進(jìn)行快速查詢。
在查詢分析階段,查詢優(yōu)化器查看查詢的每個(gè)階段并決定限制需要掃描的數(shù)據(jù)量是否有用。如果一個(gè)階段可以被用作一個(gè)掃描參數(shù)(SARG),那么就稱(chēng)之為可優(yōu)化的,并且可以利用索引快速獲得所需數(shù)據(jù)。
SARG的定義:用于限制搜索的一個(gè)操作,因?yàn)樗ǔJ侵敢粋€(gè)特定的匹配,一個(gè)值得范圍內(nèi)的匹配或者兩個(gè)以上條件的AND連接。形式如下:
列名 操作符 <常數(shù) 或 變量>
或
<常數(shù) 或 變量> 操作符列名
列名可以出現(xiàn)在操作符的一邊,而常數(shù)或變量出現(xiàn)在操作符的另一邊。如:
Name='張三'
價(jià)格>5000
5000<價(jià)格
Name='張三' and 價(jià)格>5000
如果一個(gè)表達(dá)式不能滿足SARG的形式,那它就無(wú)法限制搜索的范圍了,也就是SQL SERVER必須對(duì)每一行都判斷它是否滿足WHERE子句中的所有條件。所以一個(gè)索引對(duì)于不滿足SARG形式的表達(dá)式來(lái)說(shuō)是無(wú)用的。
介紹完SARG后,我們來(lái)總結(jié)一下使用SARG以及在實(shí)踐中遇到的和某些資料上結(jié)論不同的經(jīng)驗(yàn):
1、Like語(yǔ)句是否屬于SARG取決于所使用的通配符的類(lèi)型
如: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ù)引起的不滿足SARG形式的語(yǔ)句
不滿足SARG形式的語(yǔ)句最典型的情況就是包括非操作符的語(yǔ)句,如:NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等,另外還有函數(shù)。下面就是幾個(gè)不滿足SARG形式的例子:
ABS(價(jià)格)<5000
Name like ‘%三'
有些表達(dá)式,如:
WHERE 價(jià)格*2>5000
SQL 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、盡量少用NOT
6、exists 和 in 的執(zhí)行效率是一樣的
很多資料上都顯示說(shuō),exists要比in的執(zhí)行效率要高,同時(shí)應(yīng)盡可能的用not exists來(lái)代替not in。但事實(shí)上,我試驗(yàn)了一下,發(fā)現(xiàn)二者無(wú)論是前面帶不帶not,二者之間的執(zhí)行效率都是一樣的。因?yàn)樯婕白硬樵儯覀冊(cè)囼?yàn)這次用SQL SERVER自帶的pubs數(shù)據(jù)庫(kù)。運(yùn)行前我們可以把SQL SERVER的statistics I/O狀態(tài)打開(kāi)。
(1)select title,price from titles where title_id in (select title_id from sales where qty>30)
該句的執(zhí)行結(jié)果為:
表 'sales'。掃描計(jì)數(shù) 18,邏輯讀 56 次,物理讀 0 次,預(yù)讀 0 次。
表 'titles'。掃描計(jì)數(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 qty>30)
第二句的執(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í)行效率是低下的。但有的資料介紹說(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 fariqi>'2004-5-5'
用時(shí):7秒,另外:掃描計(jì)數(shù) 4,邏輯讀 7155 次,物理讀 0 次,預(yù)讀 0 次。
select gid,title,fariqi,reader from tgongwen where reader like '%' + '刑偵支隊(duì)' + '%' and fariqi>'2004-5-5'
用時(shí):7秒,另外:掃描計(jì)數(shù) 4,邏輯讀 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 fariqi='2004-9-16' or gid>9990000
用時(shí):68秒。掃描計(jì)數(shù) 1,邏輯讀 404008 次,物理讀 283 次,預(yù)讀 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秒。掃描計(jì)數(shù) 8,邏輯讀 67489 次,物理讀 216 次,預(yù)讀 7499 次。
看來(lái),用union在通常情況下比用or的效率要高的多。
但經(jīng)過(guò)試驗(yàn),筆者發(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'
用時(shí):6423毫秒。掃描計(jì)數(shù) 2,邏輯讀 14726 次,物理讀 1 次,預(yù)讀 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毫秒。掃描計(jì)數(shù) 8,邏輯讀 14806 次,物理讀 108 次,預(yù)讀 1144 次。
9、字段提取要按照“需多少、提多少”的原則,避免“select *”
我們來(lái)做一個(gè)試驗(yàn):
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),我們每少提取一個(gè)字段,數(shù)據(jù)的提取速度就會(huì)有相應(yīng)的提升。提升的速度還要看您舍棄的字段的大小來(lái)判斷。
10、count(*)不比count(字段)慢
某些資料上說(shuō):用*會(huì)統(tǒng)計(jì)所有列,顯然要比一個(gè)世界的列名效率低。這種說(shuō)法其實(shí)是沒(méi)有根據(jù)的。我們來(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毫秒
從以上可以看出,如果用count(*)和用count(主鍵)的速度是相當(dāng)?shù)?,而count(*)卻比其他任何除主鍵以外的字段匯總速度要快,而且字段越長(zhǎng),匯總的速度就越慢。我想,如果用count(*), SQL SERVER可能會(huì)自動(dòng)查找最小字段來(lái)匯總的。當(dāng)然,如果您直接寫(xiě)count(主鍵)將會(huì)來(lái)的更直接些。
11、order by按聚集索引列排序效率最高
我們來(lái)看:(gid是主鍵,fariqi是聚合索引列)
select top 10000 gid,fariqi,reader,title from tgongwen
用時(shí):196 毫秒。 掃描計(jì)數(shù) 1,邏輯讀 289 次,物理讀 1 次,預(yù)讀 1527 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
用時(shí):4720毫秒。 掃描計(jì)數(shù) 1,邏輯讀 41956 次,物理讀 0 次,預(yù)讀 1287 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
用時(shí):4736毫秒。 掃描計(jì)數(shù) 1,邏輯讀 55350 次,物理讀 10 次,預(yù)讀 775 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
用時(shí):173毫秒。 掃描計(jì)數(shù) 1,邏輯讀 290 次,物理讀 0 次,預(yù)讀 0 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
用時(shí):156毫秒。 掃描計(jì)數(shù) 1,邏輯讀 289 次,物理讀 0 次,預(yù)讀 0 次。
從以上我們可以看出,不排序的速度以及邏輯讀次數(shù)都是和“order by 聚集索引列” 的速度是相當(dāng)?shù)?,但這些都比“order by 非聚集索引列”的查詢速度是快得多的。
同時(shí),按照某個(gè)字段進(jìn)行排序的時(shí)候,無(wú)論是正序還是倒序,速度是基本相當(dāng)?shù)摹?
12、高效的TOP
事實(shí)上,在查詢和提取超大容量的數(shù)據(jù)集時(shí),影響數(shù)據(jù)庫(kù)響應(yīng)時(shí)間的最大因素不是數(shù)據(jù)查找,而是物理的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
這條語(yǔ)句,從理論上講,整條語(yǔ)句的執(zhí)行時(shí)間應(yīng)該比子句的執(zhí)行時(shí)間長(zhǎng),但事實(shí)相反。因?yàn)?,子句?zhí)行后返回的是10000條記錄,而整條語(yǔ)句僅返回10條語(yǔ)句,所以影響數(shù)據(jù)庫(kù)響應(yīng)時(shí)間最大的因素是物理I/O操作。而限制物理I/O操作此處的最有效方法之一就是使用TOP關(guān)鍵詞了。TOP關(guān)鍵詞是SQL SERVER中經(jīng)過(guò)系統(tǒng)優(yōu)化過(guò)的一個(gè)用來(lái)提取前幾條或前幾個(gè)百分比數(shù)據(jù)的詞。經(jīng)筆者在實(shí)踐中的應(yīng)用,發(fā)現(xiàn)TOP確實(shí)很好用,效率也很高。但這個(gè)詞在另外一個(gè)大型數(shù)據(jù)庫(kù)ORACLE中卻沒(méi)有,這不能說(shuō)不是一個(gè)遺憾,雖然在ORACLE中可以用其他方法(如:rownumber)來(lái)解決。在以后的關(guān)于“實(shí)現(xiàn)千萬(wàn)級(jí)數(shù)據(jù)的分頁(yè)顯示存儲(chǔ)過(guò)程”的討論中,我們就將用到TOP這個(gè)關(guān)鍵詞。
到此為止,我們上面討論了如何實(shí)現(xiàn)從大容量的數(shù)據(jù)庫(kù)中快速地查詢出您所需要的數(shù)據(jù)方法。當(dāng)然,我們介紹的這些方法都是“軟”方法,在實(shí)踐中,我們還要考慮各種“硬”因素,如:網(wǎng)絡(luò)性能、服務(wù)器的性能、操作系統(tǒng)的性能,甚至網(wǎng)卡、交換機(jī)等。
三、實(shí)現(xiàn)小數(shù)據(jù)量和海量數(shù)據(jù)的通用分頁(yè)顯示存儲(chǔ)過(guò)程
建立一個(gè)web 應(yīng)用,分頁(yè)瀏覽功能必不可少。這個(gè)問(wèn)題是數(shù)據(jù)庫(kù)處理中十分常見(jiàn)的問(wèn)題。經(jīng)典的數(shù)據(jù)分頁(yè)方法是:ADO 紀(jì)錄集分頁(yè)法,也就是利用ADO自帶的分頁(yè)功能(利用游標(biāo))來(lái)實(shí)現(xiàn)分頁(yè)。但這種分頁(yè)方法僅適用于較小數(shù)據(jù)量的情形,因?yàn)橛螛?biāo)本身有缺點(diǎn):游標(biāo)是存放在內(nèi)存中,很費(fèi)內(nèi)存。游標(biāo)一建立,就將相關(guān)的記錄鎖住,直到取消游標(biāo)。游標(biāo)提供了對(duì)特定集合中逐行掃描的手段,一般使用游標(biāo)來(lái)逐行遍歷數(shù)據(jù),根據(jù)取出數(shù)據(jù)條件的不同進(jìn)行不同的操作。而對(duì)于多表和大表中定義的游標(biāo)(大的數(shù)據(jù)集合)循環(huán)很容易使程序進(jìn)入一個(gè)漫長(zhǎng)的等待甚至死機(jī)。
更重要的是,對(duì)于非常大的數(shù)據(jù)模型而言,分頁(yè)檢索時(shí),如果按照傳統(tǒng)的每次都加載整個(gè)數(shù)據(jù)源的方法是非常浪費(fèi)資源的?,F(xiàn)在流行的分頁(yè)方法一般是檢索頁(yè)面大小的塊區(qū)的數(shù)據(jù),而非檢索所有的數(shù)據(jù),然后單步執(zhí)行當(dāng)前行。
最早較好地實(shí)現(xiàn)這種根據(jù)頁(yè)面大小和頁(yè)碼來(lái)提取數(shù)據(jù)的方法大概就是“俄羅斯存儲(chǔ)過(guò)程”。這個(gè)存儲(chǔ)過(guò)程用了游標(biāo),由于游標(biāo)的局限性,所以這個(gè)方法并沒(méi)有得到大家的普遍認(rèn)可。
后來(lái),網(wǎng)上有人改造了此存儲(chǔ)過(guò)程,下面的存儲(chǔ)過(guò)程就是結(jié)合我們的辦公自動(dòng)化實(shí)例寫(xiě)的分頁(yè)存儲(chǔ)過(guò)程:
CREATE procedure pagination1
(@pagesize int, --頁(yè)面大小,如每頁(yè)存儲(chǔ)20條記錄
@pageindex int --當(dāng)前頁(yè)碼
)
as
set nocount on
begin
declare @indextable table(id int identity(1,1),nid int) --定義表變量
declare @PageLowerBound int --定義此頁(yè)的底碼
declare @PageUpperBound int --定義此頁(yè)的頂碼
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
以上存儲(chǔ)過(guò)程運(yùn)用了SQL SERVER的最新技術(shù)――表變量。應(yīng)該說(shuō)這個(gè)存儲(chǔ)過(guò)程也是一個(gè)非常優(yōu)秀的分頁(yè)存儲(chǔ)過(guò)程。當(dāng)然,在這個(gè)過(guò)程中,您也可以把其中的表變量寫(xiě)成臨時(shí)表:CREATE TABLE #Temp。但很明顯,在SQL SERVER中,用臨時(shí)表是沒(méi)有用表變量快的。所以筆者剛開(kāi)始使用這個(gè)存儲(chǔ)過(guò)程時(shí),感覺(jué)非常的不錯(cuò),速度也比原來(lái)的ADO的好。但后來(lái),我又發(fā)現(xiàn)了比此方法更好的方法。
筆者曾在網(wǎng)上看到了一篇小短文《從數(shù)據(jù)表中取出第n條到第m條的記錄的方法》,全文如下:
從publish 表中取出第 n 條到第 m 條的記錄:
相關(guān)文章
Hadoop 2.x偽分布式環(huán)境搭建詳細(xì)步驟
這篇文章主要為大家詳細(xì)介紹了Hadoop 2.x偽分布式環(huán)境搭建詳細(xì)步驟,感興趣的朋友可以參考一下2016-05-05SQLServer與Access常用SQL函數(shù)區(qū)別
SQLServer Access SQL函數(shù)2009-06-06大數(shù)據(jù)開(kāi)發(fā)phoenix連接hbase流程詳解
HBase是一個(gè)分布式的、面向列的開(kāi)源數(shù)據(jù)庫(kù),該技術(shù)來(lái)源于?Fay?Chang?所撰寫(xiě)的Google論文“Bigtable:一個(gè)結(jié)構(gòu)化數(shù)據(jù)的分布式存儲(chǔ)系統(tǒng)”。就像Bigtable利用了Google文件系統(tǒng)(File?System)所提供的分布式數(shù)據(jù)存儲(chǔ)一樣,HBase在Hadoop之上提供了類(lèi)似于Bigtable的能力2022-11-11sql2005可實(shí)時(shí)監(jiān)測(cè)數(shù)據(jù)庫(kù)版本控制SQL的觸發(fā)器
用于sql2005實(shí)時(shí)監(jiān)測(cè)數(shù)據(jù)庫(kù)版本控制SQL的觸發(fā)器2008-10-10如何在Navicat新建連接、新建數(shù)據(jù)庫(kù)以及導(dǎo)入數(shù)據(jù)庫(kù)
Navicat是圖形化操作MySQL的強(qiáng)大工具,但是當(dāng)數(shù)據(jù)庫(kù)的服務(wù)器沒(méi)有開(kāi)放3306端口給辦公網(wǎng)絡(luò)時(shí),在辦公網(wǎng)使用navicat連接數(shù)據(jù)庫(kù)是連不上的,下面這篇文章主要給大家介紹了關(guān)于如何在Navicat新建連接、新建數(shù)據(jù)庫(kù)以及導(dǎo)入數(shù)據(jù)庫(kù)的相關(guān)資料,需要的朋友可以參考下2023-05-05一些關(guān)于數(shù)據(jù)存儲(chǔ)和查詢優(yōu)化的想法
今天咨詢了一下高手,關(guān)于數(shù)據(jù)存儲(chǔ)和查詢的問(wèn)題,最終目的就是快,大家可以適當(dāng)?shù)氖褂?/div> 2012-05-05Navicat?Premium自定義?sql?標(biāo)簽的創(chuàng)建方式
Navicat 中可以自定義一下sql語(yǔ)句的標(biāo)簽,方便開(kāi)發(fā)者使用,這篇文章主要介紹了Navicat?Premium自定義sql標(biāo)簽的創(chuàng)建方式,包括自定義標(biāo)簽創(chuàng)建方式,結(jié)合示例代碼給大家介紹的非常詳細(xì),需要的朋友可以參考下2022-09-09大數(shù)據(jù)時(shí)代的數(shù)據(jù)庫(kù)選擇:SQL還是NoSQL?
執(zhí)行大數(shù)據(jù)項(xiàng)目的企業(yè)面對(duì)的關(guān)鍵決策之一是使用哪個(gè)數(shù)據(jù)庫(kù),SQL還是NoSQL?SQL有著驕人的業(yè)績(jī),龐大的安裝基礎(chǔ);而NoSQL正在獲得可觀的收益,且有很多支持者。我們來(lái)看看兩位專(zhuān)家對(duì)這個(gè)問(wèn)題的看法2014-03-03最新評(píng)論