SQL Server優(yōu)化(4)－聚集索引的重要性和如何選擇聚集索引
當(dāng)前位置：點(diǎn)晴教程→知識管理交流
→『 技術(shù)文檔交流 』
在上一節(jié)的標(biāo)題中，筆者寫的是：實(shí)現(xiàn)小數(shù)據(jù)量和海量數(shù)據(jù)的通用分頁顯示存儲過程。 這是因為在將本存 儲過程應(yīng)用于“辦公自動化”系統(tǒng)的實(shí)踐中時，筆者發(fā)現(xiàn)這第三種存儲過程在小數(shù)據(jù)量的情況下，有如下現(xiàn)象：
1 、分頁速度一般維持在1 秒 和3 秒之間。
2 、在查詢最后一頁時，速度一般為5 秒 至8 秒，哪怕分頁總數(shù)只有3 頁或30 萬頁。
雖然在超大容量情況下，這個分頁的實(shí)現(xiàn)過程是很快的，但在分前幾頁時，這個1 －3 秒 的速度比起第一種甚至沒有經(jīng)過優(yōu)化的分頁方法速度還要慢，借用戶的話說就是“還沒有ACCESS 數(shù)據(jù)庫速 度快”，這個認(rèn)識足以導(dǎo)致用戶放棄使用您開發(fā)的系統(tǒng)。
筆者就此分析了一下，原來產(chǎn)生這種現(xiàn)象的癥結(jié)是如此的簡單，但又如此的重要：排序的字段不是聚集索引！
本篇文章的題目是：“查詢優(yōu)化及分頁算法方案”。筆者只所以把“查詢優(yōu) 化”和“分頁算法”這兩個聯(lián)系不是很大的論題放在一起，就是因為二者都需要一個非常重要的 東西 ―― 聚集索引。
在前面的討論中我們已經(jīng)提到了，聚集索引有兩個最大的優(yōu)勢：
1 、 以最快的速度縮小查詢范圍。
2 、以最快的速度進(jìn)行字段排序。
第1 條多用在查詢優(yōu)化時，而第2 條 多用在進(jìn)行分頁時的數(shù)據(jù)排序。
而聚集索引在每個表內(nèi)又只能建立一個，這使得聚集索引顯得更加的重要。聚集索引的挑選可以說是實(shí)現(xiàn)“查詢優(yōu) 化”和“高效分頁”的最關(guān)鍵因素。
但要既使聚集索引列既符合查詢列的需要，又符合排序列的需要，這通常是一個矛盾。
筆者前面“索引”的討論中，將fariqi ，即用戶發(fā)文日期作 為了聚集索引的起始列，日期的精確度為“日”。這種作法的優(yōu)點(diǎn)，前面已經(jīng)提到了，在進(jìn)行劃時間段的快速查詢中， 比用ID 主鍵列有很大的優(yōu)勢。
但在分頁時，由于這個聚集索引列存在著重復(fù)記錄，所以無法使用max 或min 來 最為分頁的參照物，進(jìn)而無法實(shí)現(xiàn)更為高效的排序。而如果將ID 主鍵列作為聚集索引，那么聚集索引除了用以排序之外，沒有任何用 處，實(shí)際上是浪費(fèi)了聚集索引這個寶貴的資源。
為解決這個矛盾，筆者后來又添加了一個日期列，其默認(rèn)值為getdate() 。 用戶在寫入記錄時，這個列自動寫入當(dāng)時的時間，時間精確到毫秒。即使這樣，為了避免可能性很小的重合，還要在此列上創(chuàng)建UNIQUE 約 束。將此日期列作為聚集索引列。
有了這個時間型聚集索引列之后，用戶就既可以用這個列查找用戶在插入數(shù)據(jù)時的某個時間段的查詢，又可以作為唯一列來實(shí)現(xiàn)max 或min ， 成為分頁算法的參照物。
經(jīng)過這樣的優(yōu)化，筆者發(fā)現(xiàn)，無論是大數(shù)據(jù)量的情況下還是小數(shù)據(jù)量的情況下，分頁速度一般都是幾十毫秒，甚至0 毫 秒。而用日期段縮小范圍的查詢速度比原來也沒有任何遲鈍。
聚集索引是如此的重要和珍貴，所以筆者總結(jié)了一下，一定要將聚集索引建立在：
1 、 您最頻繁使用的、用以縮小查詢范圍的字段上；
2 、 您最頻繁使用的、需要排序的字段上。
結(jié)束語：
本篇文章匯集了筆者近段在使用數(shù)據(jù)庫方面的心得，是在做“辦公自動化”系統(tǒng)時實(shí)踐經(jīng)驗的積累。希望這篇文章不僅 能夠給大家的工作帶來一定的幫助，也希望能讓大家能夠體會到分析問題的方法；最重要的是，希望這篇文章能夠拋磚引玉，掀起大家的學(xué)習(xí)和討論的興趣，以共同 促進(jìn)，共同為公安科技強(qiáng)警事業(yè)和金盾工程做出自己最大的努力。
最后需要說明的是，在試驗中，我發(fā)現(xiàn)用戶在進(jìn)行大數(shù)據(jù)量查詢的時候，對數(shù)據(jù)庫速度影響最大的不是內(nèi)存大小，而是CPU 。 在我的P4 2.4 機(jī)器上試驗的時候，查看“資源管理器”，CPU 經(jīng) 常出現(xiàn)持續(xù)到100% 的現(xiàn)象，而內(nèi)存用量卻并沒有改變或者說沒有大的改變。即使在我們的HP ML 350 G3 服 務(wù)器上試驗時，CPU 峰值也能達(dá)到90% ，一般持續(xù)在70% 左右。
該文章在 2023/3/8 0:24:53 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
