索引性能好不好讓二進制高度來說話

作為資料庫管理者來說，要在表上建立索引很簡單。但是要知道這個索引是否合适、如何優化索引則就具有一定的難度。這項工作也是用來評價一個資料庫管理者是否算得上專家的一個重要名額。那麼為什麼索引優化有這麼大的難度呢？

這主要是因為索引優化是考察一個資料庫管理者經驗的時候。筆者今天就跟大家分享一下這方面的管理經驗。

　　其實在資料庫中本身就提供了一些比較有參考價值的資訊，可以幫助管理者來做好索引的設計與優化。二進制高度就是Oracle資料庫中幫助資料庫管理者來做好這個工作的工具。什麼叫做二進制高度呢?說實話筆者也不了解這個名詞具體代表的含義。隻知道索引二進制高高度對把Rowid傳回給使用者程序時所要求的I/0數量起到非常關鍵的作用。資料庫管理者隻要了解這個即可，而不需要花費很長的時間去搞明白什麼叫做二進制高度。Oracle資料庫中，系統視圖sys.dba_indexes就儲存在索引的二進制高度資訊。如下圖所示的SQL語句，可以查詢處索引的二進制高度的值。

　　如上圖所示，字段Blevel表示二進制高度;Index_name則表示索引的名稱。一般來說，二進制高度越低越好(最低為0)。作為資料庫管理者，就是需要相方設法讓這個二進制高度的值變為0。雖然這個目标看起來比較簡單，但是實作起來卻有相當大的困難。

一、二進制高度對索引的影響。

　　為了說明二進制高度對索引的影響，筆者舉一個例子為大家說明。假設現在有一個索引，其二進制高度為3。此時如果使用者需要利用這個索引來查詢資料的話，則此時同時将會有4個塊被讀取，其中3個來自索引，一個來自表。随着索引二進制高度的值的增加，整個檢索資料所要求的I/O數量也會随之增加。因為二進制高度增加一個級别都會增加一個額外的讀取快。而且通常情況下由于這些塊不能夠按照順序讀取，他們都會要求一個獨立的I/O操作。是以在這個二進制高度的值越大，其I/O争用的現象也可能會越頻繁。此時其資料庫索引的性能也就會越低。

二、如何來查找二進制高度的值?

　　通常情況下，資料庫管理者可以對索引或者表進行分析進而得到索引的二進制高度。不過這個分析比較複雜。筆者是建議各位資料庫管理者直接查詢資料視圖sys.dba_indexes，來獲得系統目前索引的二進制高度值。既然資料庫中已經有類似的值，那麼就沒有必要浪費時間在去手工分析這些值了。以前筆者在給跟一些資料庫管理者溝通的時候，他們也詢問過筆者，這個二進制高度的值是如何計算出來的。筆者給他們解釋了好半天，他們仍然搞不明白。其實這也怪不得他們，因為筆者對這個計算分析的過程也是一知半解。是以不知道這個二進制高度的值怎麼計算出來的，沒有關系。隻要知道該從哪個地方去查找這個值即可。

三、哪些因素會影響到這個值?

　　在對索引進行優化的時候，就是要想盡一切辦法，把這個值降低到最低。而要實作降低這個二進制高度的值的目标，則資料庫管理者就必須先了解哪些因素跟這個值有關。如此的話，資料庫管理者才能夠對症下藥，降低二進制高度的值，提高索引的性能。

1、删除操作。有時候資料庫管理者可能會發現他們剛完成資料的導入工作，就會發現資料庫的性能有所下降。 而此時查詢這個二進制高度的值， 則會發現這個值非常的大。 照理來說， 才剛完成資料的導入功能，

還沒有進行其它的一些業務操作， 這個值不應該很大呀。 那這是怎麼一回事情呢?

其實，這主要是因為大量的删除操作所造成的。原來在導入資料的時候，可能會發現某些資料導入有問題。故有些資料庫管理者會利用delete語句清除倒入到資料庫中的記錄。此時資料庫管理者就需要注意，索引上如果有大量被删除的行，則它對應的二進制高度的值也就會逐漸增加。遇到這種情況時，資料庫管理者可以嘗試着重建索引。通常情況下，如果二進制高度的值比較大确實是因為删除操作所引起的，那麼通過這個重建索引的工作後，基本上可以把這個二進制高度的值降下去。筆者建議，如果一個索引中被删除掉記錄接近于全部記錄的30%左右，此時資料庫管理者就需要采取重建索引的作業，用于降低二進制高度以及在一次I/O過程中所讀取的空閑時間。

2、資料塊尺寸。通常情況下，資料庫塊尺寸與二進制高度的值是成反比的。資料塊尺寸越大，則索引的二進制高度就越低。資料塊大小是表空間管理中的一個屬性。通常情況下，資料庫管理者在建立表空間的時候，往往采用其預設的塊大小，而不會改變其大小。System與Sysaux是系統表空間，他們的資料塊大小往往是在表空間建立時的初始化參數定義的。一般情況下不同的表空間可以由不同的塊大小，以對應不同的表空間對于性能的不同要求。不過總的來說，這個資料庫塊也不是說分的越細越好。這會增加資料庫維護的難度。在Oracle資料庫中，對此也做了一定的限制。如正常情況下，這個資料庫最多可以設定五個不同的資料塊大小。`資料庫`是以資料塊為機關配置設定和使用磁盤空間，一個資料塊是磁盤空間中固定個數的位元組，位于磁盤空間管理的最底層。通常情況下，一個資料塊的大小從8KB到32KB之間。并不是說，其可以取到8KB到32KB之間的任何值，其還必須滿足必須是存儲器裝置的實體塊大小的一個倍數。如資料庫存儲器裝置的存儲快大小為8KB的話，則這個資料塊的大小就可以為8、16、24、32KB中的任何一個值。如果不考慮其他因素，在建立資料表時設定比較大的資料塊，可以降低二進制高度的值，可以産生比較淺的B樹，可以産生比較佳的性能。一般來說，如果資料庫管理者認為資料庫比較複雜，所涉及的應用比較大的話，在可以把資料塊設定的比較大一點，如24KB，甚至32KB。

這裡需要注意的是，如果資料庫系統用來做資料倉庫，則最好把這個數塊設定為32KB。而對于其他用途的資料庫，則需要根據資料庫應用的負責程度來進行合适的設定。如果要說把資料庫塊大小設定為多少合适，要定一個具體的值，恐怕沒有人可以輕易下這個結論。資料庫管理者需要根據企業資料庫的實際應用，來确定一個合适的值。而這就跟資料庫管理者的經驗挂鈎了。豐富的資料庫管理經驗，可以幫助管理者來設定一個合适的資料塊。如果隻資料庫隻用來學習用的話，那麼在建立表空間時可以忽略這個參數，直接采用預設的資料塊大小即可。而如果資料庫用來當作生産用資料庫時，則對此資料庫管理者就需要慎重考慮。可以結合這個索引相關的二進制高度參數來判斷目前的資料庫塊大小是否合适。

3、二進制高度的值還會随着索引列中的非NULL數量以及索引列中值的範圍狹窄程度而變化。一般來說，索引列中非NULL的數量越多(即空字段餘越少)則其二進制高度的值越低。或者說，索引列中的值越靠近，即範圍比較小，則其二進制高度的值也就越低。為此，這就提醒資料庫管理者在設計索引的時候，最好能夠把索引字段設定為非空。如此的話，就可以降低索引的二進制高度，以提高索引的性能。如筆者在索引設計時，往往把索引字段設定為非空。而使用者在實際應用的過程中，如果這個索引字段暫時不需要輸入内容(如可能暫時無法取得這個值，需要在後續補入)，此時系統就會采用一個預設值(如NULL)，來表示這個字段的内容暫時為空。不過在資料庫存儲的時候，已經有具體的内容了。如此即可以滿足索引列非空的需要，使用者在日後也可以修改這個資訊;同時又降低了二進制高度的值，提高了資料庫索引的性能。一舉多得，資料庫管理者可以嘗試。另外，把索引列的值在滿足應用的前提下，限制在一個比較小的範圍之内，也可以降低這個二進制高度的值。