SQL Server 索引結構及其使用（四

SQL Server 索引結構及其使用（四）

作者：freedk

一、深入淺出理解索引結構
二、改善SQL語句
三、實現小資料量和海量資料的通用分頁顯示預存程序

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

　　在上一節的標題中，筆者寫的是：實現小資料量和海量資料的通用分頁顯示預存程序。這是因為在將本預存程序應用於“辦公自動化”系統的實踐中時，筆者發現這第三種預存程序在小資料量的情況下，有如下現象：

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

2、在查詢最後一頁時，速度一般為5秒至8秒，哪怕分頁總數只有3頁或30萬頁。

　　雖然在超大容量情況下，這個分頁的實現過程是很快的，但在分前幾頁時，這個1－3秒的速度比起第一種甚至沒有經過最佳化的分頁方法速度還要慢，借使用者的話說就是“還沒有ACCESS資料庫速度快”，這個認識足以導致使用者放棄使用您開發的系統。
　　筆者就此分析了一下，原來產生這種現象的癥結是如此的簡單，但又如此的重要：排序的欄位不是叢集索引！
　　本篇文章的題目是：“查詢最佳化及分頁演算法方案”。筆者只所以把“查詢最佳化”和“分頁演算法”這兩個聯絡不是很大的論題放在一起，就是因為二者都需要一個非常重要的東西――叢集索引。
在前面的討論中我們已經提到了，叢集索引有兩個最大的優勢：

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

2、以最快的速度進列欄位排序。

　　第1條多用在查詢最佳化時，而第2條多用在進行分頁時的資料排序。

　　而叢集索引在每個表內又只能建立一個，這使得叢集索引顯得更加的重要。叢集索引的挑選可以說是實現“查詢最佳化”和“高效分頁”的最關鍵因素。
　　但要既使叢集索引列既符合查詢列的需要，又符合排序列的需要，這通常是一個矛盾。筆者前面“索引”的討論中，將fariqi，即使用者發文日期作為了叢集索引的起始列，日期的精確度為“日”。這種作法的優點，前面已經提到了，在進行劃時間段的快速查詢中，比用ID主鍵列有很大的優勢。
　　但在分頁時，由於這個叢集索引列存在著重複記錄，所以無法使用max或min來最為分頁的參照物，進而無法實現更為高效的排序。而如果將ID主鍵列作為叢集索引，那麼叢集索引除了用以排序之外，沒有任何用處，實際上是浪費了叢集索引這個寶貴的資源。
　　為解決這個矛盾，筆者後來又添加了一個日期列，其預設值為getdate()。使用者在寫入記錄時，這個列自動寫入當時的時間，時間精確到毫秒。即使這樣，為了避免可能性很小的重合，還要在此列上建立UNIQUE約束。將此日期列作為叢集索引列。
　　有了這個時間型叢集索引列之後，使用者就既可以用這個列尋找使用者在插入資料時的某個時間段的查詢，又可以作為唯一列來實現max或min，成為分頁演算法的參照物。
　　經過這樣的最佳化，筆者發現，無論是大資料量的情況下還是小資料量的情況下，分頁速度一般都是幾十毫秒，甚至0毫秒。而用日期段縮小範圍的查詢速度比原來也沒有任何遲鈍。叢集索引是如此的重要和珍貴，所以筆者總結了一下，一定要將叢集索引建立在：

1、您最頻繁使用的、用以縮小查詢範圍的欄位上；

2、您最頻繁使用的、需要排序的欄位上。

結束語

　　本篇文章彙集了筆者近段在使用資料庫方面的心得，是在做“辦公自動化”系統時實踐經驗的積累。希望這篇文章不僅能夠給大家的工作帶來一定的協助，也希望能讓大家能夠體會到分析問題的方法；最重要的是，希望這篇文章能夠拋磚引玉，掀起大家的學習和討論的興趣，以共同促進，共同為公安科技強警事業和金盾工程做出自己最大的努力。
　　最後需要說明的是，在實驗中，我發現使用者在進行大資料量查詢的時候，對資料庫速度影響最大的不是記憶體大小，而是CPU。在我的P4 2.4機器上實驗的時候，查看“資源管理員”，CPU經常出現持續到100%的現象，而記憶體用量卻並沒有改變或者說沒有大的改變。即使在我們的HP ML 350 G3伺服器上實驗時，CPU峰值也能達到90%，一般持續在70%左右。
　　本文的實驗資料都是來自我們的HP ML 350伺服器。伺服器配置：雙Inter Xeon 超執行緒 CPU 2.4G，記憶體1G，作業系統Windows Server 2003 Enterprise Edition，資料庫SQL Server 2000 SP3