摘自:http://crm.xingfa.com/HelpDesk/Archiver.asp?ThreadID=8587 感謝作者
2010-09-29 8:00:53
我們來簡單地看看SQL SERVER索引是如何工作的,關於索引的一些概念就不說了。
聚簇索引:
(圖A)
我們來看圖A,聚簇索引的結構圖。
資料頁就是資料庫裡實際儲存資料的地方,可以看到是按頁1頁1頁存的。
假設那個列是”LastName”。
因為是叢集索引,所以它是按照順序排下來的。可以看到,索引是一棵樹,首先先看一下這棵樹是怎麼形成的。
先看Page100和Page110的最上面,由它們形成了Page141,Page141的第一條資料是Page100的第一條資料,Page141的最後一條資料是Page110的第一條資料。同理由Page120和Page130形成Page145,Page141和Page145形成根Page140.
好了,然後來看看它是如何尋找資料的。
我們來找”Rudd”這個姓。
首先它會從根即Page140開始找,因為”Rudd”的值比”Martin”大(只要比較一下他們首字母就知道了,按26個字母順序R排在M的後面),所以會往”Martin”的後面找,即找到Page145,然後在比較一下”Rudd”和”Smith”,”Rudd”比”Smith”小,所以會往左邊找即Page120,然後在Page120漸進式掃描下來直到找到”Rudd”。
如果不建索引的話,SQL SERVER會從第一頁開始按順序每頁漸進式掃描過去,直到找到”Rudd”。顯然如果對於一個百萬行的表來說,效率是極其低下的,如果建了索引,非常快就能找到。
非聚簇索引:
(圖B)
看圖B,非聚簇索引的結構圖。
聚簇索引和非聚簇索引的區別就是:聚簇索引的資料實體儲存體順序和索引順序一致的,也就是它的資料就是按順序排下來的。非聚簇索引的資料存放區是無序的,不按索引順序排列。
從圖B可以看到資料頁裡是無序的。那麼它的索引是如何建立的呢?
再看圖B,它是把這個索引列的資料複製了一份然後按順序排下來,再建立索引。每行資料都有一個指標。
我們再來找”Rudd”.首先從索引頁的根開始找,尋找原理跟叢集索引是一樣的。在索引頁的Page61找到”Rudd”,它的指標是470501,然後在資料頁的Page5找到470501,這個位置就是”Rudd”在資料庫中的實際位置,這樣就找到了”Rudd”。
好了,索引的基本工作原理就是這樣,可能實際上要複雜些。
rokingphone2010-09-29 8:01:16<div class=postTitle>SQL SERVER索引最佳化系列之二:索引效能考慮 </div>
在前面說過了索引能極大的提高資料的檢索速度,那為什麼不在每一個列上建索引呢?初學者可能會困惑這個問題,而且通常不知道哪些列該建索引,哪些不該建, 甚至於會把like模糊查詢的列也作為索引列,其實like是不使用索引的,只有等於,大於,IN等操作符會使用索引。SQLSERVER對於資料的插入,更新和刪除,都要更新相應的索引。這無疑會大大增加更新時間。另外,如果某個資料頁已滿,這時如果要在該頁插入資料時,就會造成頁分裂產生片段(後面還會說到),而影響效能。所以僅當查詢的效能比更新的效能更重要時才建索引。
考慮建索引的列
1. 主鍵
2. 外鍵
3. 頻繁檢索的列和按排序次序頻繁檢索的列
通常where 後面的條件引用的列都是考慮建索引的列,模糊查詢除外(如like查詢)
不考慮建索引的列
1.很少或從來不在查詢中引用的列
2.只有兩個或若干個值的列(比如只有男和女兩個值的列)
3.小表(行數很少的表,這時候SQL SERVER花費在索引上的時間比直接掃描表的時間還更長)
SQL SERVER對於建立索引的列,都要付出一定的代價來維護這個索引。另外SQLSERVER會自動分析是否使用該列的索引,比如對於只有男和女兩個值的列,如果給它建立索引,SQLSERVER自行分析後,會認為改列使用索引尋找的效率不大,因為返回結果集的百分比比較大,於是SQLSERVER會將統計資料記錄下來,當下次尋找該列時,就會根據該統計資料來決定是否要使用改列的索引。
對於返回結果集百分比比較大的列(比如有100萬的資料,而尋找的結果將返回50萬),SQLSERVER就可能不會使用該列上的索引,而採用全表掃描的方法。可自行測試,插入2000條資料,有1999條資料是一樣的,比如ForumID為2的有1999條,ForumID為3的只有一條,這時使用
SET SHOWPLAN_TEXT ON –顯示執行計畫,可查看查詢語句使用了哪些索引
GO
SELECT * FROM Posts WHERE ForumID=2
會發現沒有使用ForumID列的索引。
SELECT * FROM Posts WHERE ForumID=3
則使用了ForumID列的索引
進行大批量插入或更新應先刪除索引最後再重建索引,避免每插入或更新一條資料時都要更新相應的索引,而影響更新速度。
複合索引(指兩列或多列組成的索引,通常where後面由多個列組成的條件時,可以把這些列建成一個複合索引)
1) 只有當WHERE子句中指定索引鍵的第一列時才使用該索引。
例子:
CREATE INDEX Posts_INDEX
ON Posts(ThreadID,ForumID)
如果SELECT * FROM Posts WHERE ForumID=2 則查詢不會使用Posts_INDEX索引
而 SELECT * FROM Posts WHERE ThreadID=10 則會使用Posts_INDEX索引
2) 索引不應過大(<= 8個位元組為最好,int型相當於4個位元組,SmallInt相當於2個位元組)。
3) 首先定義最具唯一性的列(順序不一樣,索引是不一樣的)
比如:A列有30%的資料是重複的,B列有10%的列是重複的,C列有25%的資料是重複的,這時候建立索引的列的順序應當是 B C A
建立索引還有一個比較重要的選項:填滿因數。下一篇繼續。
rokingphone2010-09-29 8:01:32<div class=postTitle>SQL SERVER索引最佳化系列之三:填滿因數 </div>
建SQL SERVER索引的時候有一個選項,即Fillfactor(填滿因數)。
這個可能很少人會去注意它,但它也是比較重要的。大家可能也都知道有這個東西,但是如何去使用它,可能會比較迷糊。另外,即使你理解了它的原理,也不一定能使用好它,這個還要具體分析索引欄位的更新頻率等等。
記得看書知道有這麼個東西,但是都是看的迷迷糊糊的,不知道幹嗎的,好象設定不設定都一樣的。其實,像索引這些東西,當資料達到幾十萬上百萬的時候,它的效果就表現的很明顯。
填滿因數定義:索引中葉級頁的資料充滿度。它的作用:當系統建立或重建索引時,在每一個索引頁上預先留出一部分空間。使得系統在新增索引資訊時能夠保持索引頁不分裂。它的目的是使索引的頁分裂最小並對效能微調。
(圖A)
看圖A,通過前面說的我們知道資料是按頁存的,根據定義,填滿因數是指圖A中的Page100(Page110,Page120,Page130)的資料的充滿度。如果按預設,填滿因數是為0(0和100%是一樣的),即完全充滿。如果設定60%,則Page100的資料只充滿空間的60%,會有40%的剩餘空間。
填滿因數只在建立索引時執行;索引建立後,當表中進行資料的添加、刪除或更新時,不會保持填滿因數。即建立索引完後添加資料,比如添加”COM”,則會添加在Page100的”CON”的前面,此時Page100的剩餘空間將小於40%。因為充滿度只有60%,所以”Barr”和”CON”之間會有空隙,所以”COM”將直接插入他們之間,不會照成資料移動和頁分裂。如果充滿度是100%即整頁充滿資料,則會照成”CON”及它後面的資料都向後移動1位,然後在”CON”前面插入”COM”,而Page100會大約一半的行(後半部)移到新頁中以便為新行騰出空間(這種重組稱為頁面分割。頁面分割會降低效能並使表中的資料存放區產生片段.),在這些移動的過程中一方面速度明顯下降,另一方面會產生片段。添加完資料後要使填滿因數繼續保持60%則需要重建索引。
有人將填滿因數比喻成下面這個例子:
為了給一個班的10個同學排一下順序,我們可以給每一位同學一個編號,如:
a. 從1,2,3,4,5,6,7,9,10。這時,我們說填滿因數是100.
此時,如果又來了新同學,而其排名要在中間某位置的話,我們就要改變許多個同學的號碼,如新同學排第5位,就需要4號以後的同學號碼都加1,再將新同學編為5號才行.
b.我們又可以給同學這樣編號:1,3,5,7,9,11,13,15,17,19
也同樣完成了順序的排列.我們說這時填滿因數是50%,此時如果來了新同學,又是排在第5位的話,那麼我們只需將其號碼編為8就行了.其它同學都不用變.
可見,填滿因數大的時候,點用的號碼空間小,耗費資源少,小的時候,佔用資源加大,但操作方便,迅速.
填滿因數大的時候,插入或修改記錄後重新索引的工作會很大,磁碟IO操作增加,效能必然降低,但其佔用空間小.填滿因數小的時候,索引檔案佔用磁碟及記憶體空間相對要大,但是,系統本身重新索引所需IO操作減少,效能提高,只是多佔用一些儲存空間. 孰輕孰重要自已決定。
通常只有當不會對資料變更時(例如,在唯讀表中)才會使設定100%。另外,只有當在表中根據現有資料建立新索引,並且可以精確預見將來會對這些資料進行哪些更改時,將填滿因數選項設定為另一個值才有用。所以填滿因數不是很容易設定的。