?

一、深入淺出理解索引結(jié)構(gòu)
二、改善SQL語(yǔ)句
三、實(shí)現(xiàn)小數(shù)據(jù)量和海量數(shù)據(jù)的通用分頁(yè)顯示存儲(chǔ)過(guò)程

聚集索引的重要性和如何選擇聚集索引

　　在 上一節(jié) 的標(biāo)題中，筆者寫(xiě)的是：實(shí)現(xiàn)小數(shù)據(jù)量和海量數(shù)據(jù)的通用分頁(yè)顯示存儲(chǔ)過(guò)程。這是因?yàn)樵趯⒈敬鎯?chǔ)過(guò)程應(yīng)用于“辦公自動(dòng)化”系統(tǒng)的實(shí)踐中時(shí)，筆者發(fā)現(xiàn)這第三種存儲(chǔ)過(guò)程在小數(shù)據(jù)量的情況下，有如下現(xiàn)象：

1、分頁(yè)速度一般維持在1秒和3秒之間。

2、在查詢最后一頁(yè)時(shí)，速度一般為5秒至8秒，哪怕分頁(yè)總數(shù)只有3頁(yè)或30萬(wàn)頁(yè)。

　　雖然在超大容量情況下，這個(gè)分頁(yè)的實(shí)現(xiàn)過(guò)程是很快的，但在分前幾頁(yè)時(shí)，這個(gè)1－3秒的速度比起第一種甚至沒(méi)有經(jīng)過(guò)優(yōu)化的分頁(yè)方法速度還要慢，借用戶的話說(shuō)就是“還沒(méi)有ACCESS數(shù)據(jù)庫(kù)速度快”，這個(gè)認(rèn)識(shí)足以導(dǎo)致用戶放棄使用您開(kāi)發(fā)的系統(tǒng)。
　　筆者就此分析了一下，原來(lái)產(chǎn)生這種現(xiàn)象的癥結(jié)是如此的簡(jiǎn)單，但又如此的重要：排序的字段不是聚集索引！
　　本篇文章的題目是：“查詢優(yōu)化及分頁(yè)算法方案”。筆者只所以把“查詢優(yōu)化”和“分頁(yè)算法”這兩個(gè)聯(lián)系不是很大的論題放在一起，就是因?yàn)槎叨夹枰粋€(gè)非常重要的東西――聚集索引。
在前面的討論中我們已經(jīng)提到了，聚集索引有兩個(gè)最大的優(yōu)勢(shì)：

1、以最快的速度縮小查詢范圍。

2、以最快的速度進(jìn)行字段排序。

　　第1條多用在查詢優(yōu)化時(shí)，而第2條多用在進(jìn)行分頁(yè)時(shí)的數(shù)據(jù)排序。

　　而聚集索引在每個(gè)表內(nèi)又只能建立一個(gè)，這使得聚集索引顯得更加的重要。聚集索引的挑選可以說(shuō)是實(shí)現(xiàn)“查詢優(yōu)化”和“高效分頁(yè)”的最關(guān)鍵因素。
　　但要既使聚集索引列既符合查詢列的需要，又符合排序列的需要，這通常是一個(gè)矛盾。筆者前面“索引”的討論中，將fariqi，即用戶發(fā)文日期作為了聚集索引的起始列，日期的精確度為“日”。這種作法的優(yōu)點(diǎn)，前面已經(jīng)提到了，在進(jìn)行劃時(shí)間段的快速查詢中，比用ID主鍵列有很大的優(yōu)勢(shì)。
　　但在分頁(yè)時(shí)，由于這個(gè)聚集索引列存在著重復(fù)記錄，所以無(wú)法使用max或min來(lái)最為分頁(yè)的參照物，進(jìn)而無(wú)法實(shí)現(xiàn)更為高效的排序。而如果將ID主鍵列作為聚集索引，那么聚集索引除了用以排序之外，沒(méi)有任何用處，實(shí)際上是浪費(fèi)了聚集索引這個(gè)寶貴的資源。
　　為解決這個(gè)矛盾，筆者后來(lái)又添加了一個(gè)日期列，其默認(rèn)值為getdate()。用戶在寫(xiě)入記錄時(shí)，這個(gè)列自動(dòng)寫(xiě)入當(dāng)時(shí)的時(shí)間，時(shí)間精確到毫秒。即使這樣，為了避免可能性很小的重合，還要在此列上創(chuàng)建UNIQUE約束。將此日期列作為聚集索引列。
　　有了這個(gè)時(shí)間型聚集索引列之后，用戶就既可以用這個(gè)列查找用戶在插入數(shù)據(jù)時(shí)的某個(gè)時(shí)間段的查詢，又可以作為唯一列來(lái)實(shí)現(xiàn)max或min，成為分頁(yè)算法的參照物。
　　經(jīng)過(guò)這樣的優(yōu)化，筆者發(fā)現(xiàn)，無(wú)論是大數(shù)據(jù)量的情況下還是小數(shù)據(jù)量的情況下，分頁(yè)速度一般都是幾十毫秒，甚至0毫秒。而用日期段縮小范圍的查詢速度比原來(lái)也沒(méi)有任何遲鈍。聚集索引是如此的重要和珍貴，所以筆者總結(jié)了一下，一定要將聚集索引建立在：

1、您最頻繁使用的、用以縮小查詢范圍的字段上；

2、您最頻繁使用的、需要排序的字段上。

結(jié)束語(yǔ)

　　本篇文章匯集了筆者近段在使用數(shù)據(jù)庫(kù)方面的心得，是在做“辦公自動(dòng)化”系統(tǒng)時(shí)實(shí)踐經(jīng)驗(yàn)的積累。希望這篇文章不僅能夠給大家的工作帶來(lái)一定的幫助，也希望能讓大家能夠體會(huì)到分析問(wèn)題的方法；最重要的是，希望這篇文章能夠拋磚引玉，掀起大家的學(xué)習(xí)和討論的興趣，以共同促進(jìn)，共同為公安科技強(qiáng)警事業(yè)和金盾工程做出自己最大的努力。
　　最后需要說(shuō)明的是，在試驗(yàn)中，我發(fā)現(xiàn)用戶在進(jìn)行大數(shù)據(jù)量查詢的時(shí)候，對(duì)數(shù)據(jù)庫(kù)速度影響最大的不是內(nèi)存大小，而是CPU。在我的P4 2.4機(jī)器上試驗(yàn)的時(shí)候，查看“資源管理器”，CPU經(jīng)常出現(xiàn)持續(xù)到100%的現(xiàn)象，而內(nèi)存用量卻并沒(méi)有改變或者說(shuō)沒(méi)有大的改變。即使在我們的HP ML 350 G3服務(wù)器上試驗(yàn)時(shí)，CPU峰值也能達(dá)到90%，一般持續(xù)在70%左右。
　　本文的試驗(yàn)數(shù)據(jù)都是來(lái)自我們的HP ML 350服務(wù)器。服務(wù)器配置：雙Inter Xeon 超線程 CPU 2.4G，內(nèi)存1G，操作系統(tǒng)Windows Server 2003 Enterprise Edition，數(shù)據(jù)庫(kù)SQL Server 2000 SP3

SQL Server 索引結(jié)構(gòu)及其使用（四）