資料庫建立索引的原則

sz_bdqn發表於2011-06-18

鐵律一:天下沒有免費的午餐,使用索引是需要付出代價的。

索引的優點有目共睹,但是,卻很少有人關心過採用索引所需要付出的成本。若資料庫管理員能夠對索引所需要付出的代價有一個充分的認識,也就不會那麼隨意到處建立索引了。

仔細數數,其實建立索引的代價還是蠻大的。如建立索引和維護索引都需要花費時間與精力。特別是在資料庫設計的時候,資料庫管理員為表中的哪些欄位需要建立索引,要調研、要協調。如當建有索引的表中的紀錄又增加、刪除、修改操作時,資料庫要對索引進行重新調整。雖然這個工作資料庫自動會完成,但是,需要消耗伺服器的資源。當表中的資料越多,這個消耗的資源也就越多。如索引是資料庫中實際存在的物件,所以,每個索引都會佔用一定的物理空間。若索引多了,不但會佔用大量的物理空間,而且,也會影響到整個資料庫的執行效能。

可見,資料庫管理員若要採用索引來提高系統的效能,自身仍然需要付出不少的代價。資料庫管理員現在要考慮的就是如何在這兩個之間取得一個均衡。或者說,找到一個回報與投入的臨界點。

 

鐵律二:對於查詢中很少涉及的列或者重複值比較多的列,不要建立索引。

在查詢的時候,如果我們不按某個欄位去查詢,則在這個欄位上建立索引也是浪費。如現在有一張員工資訊表,我們可能按員工編號、員工姓名、或者出身地去查詢員工資訊。但是,我們往往不會按照身份證號碼去查詢。雖然這個身份證號碼是唯一的。此時,即使在這個欄位上建立索引,也不能夠提高查詢的速度。相反,增加了系統維護時間和佔用了系統空間。這簡直就是搬起石頭砸自己的腳呀。

另外,如上面的員工資訊表,有些欄位重複值比較多。如性別欄位主要就是“男”、“女”;職位欄位中也是有限的幾個內容。此時,在這些欄位上新增索引也不會顯著的增加查詢速度,減少使用者響應時間。相反,因為需要佔用空間,反而會降低資料庫的整體效能。

資料庫索引管理中的第二條鐵律就是,對於查詢中很少涉及的列或者重複值比較多的列,不要建立索引。

 

鐵律三:對於按範圍查詢的列,最好建立索引。

在資訊化管理系統中,很多時候需要按範圍來查詢某些交易記錄。如在ERP系統中,經常需要查詢當月的銷售訂單與銷售出貨情況,這就需要按日期範圍來查詢交易記錄。如有時候發現庫存不對時,也需要某段時期的庫存進出情況,如5月1日到12月3日的庫存交易情況等等。此時,也是根據日期來進行查詢。

對於這些需要在指定範圍內快速或者頻繁查詢的資料列,需要為其建立索引。因為索引已經排序,其儲存的時候指定的範圍是連續的,查詢可以利用索引的排序,加快查詢時間,減少使用者等待時間。

不過,若雖然可能需要按範圍來進行查詢,但是,若這個範圍查詢條件利用的不多的情況下,最好不好採用索引。如在員工資訊表中,可能需要查詢2008年3月份以前入職的員工明細,要為他們增加福利。但是,由於表中記錄不多,而且,也很少進行類似的查詢。若維這個欄位建立索引,雖然無傷大雅,但是很明顯,索引所獲得的收益要低於其成本支出。對資料庫管理員來說,是得不償失的。

再者,若採用範圍查詢的話,最好能利用TOP關鍵字來限制一次查詢的結果。如第一次按順序只顯示前面的500條記錄等等。把TOP關鍵字跟範圍一起使用,可以大大的提高查詢的效率。

 

鐵律四:表中若有主鍵或者外來鍵,一定要為其建立索引。

定義有主鍵的索引列,一定要為其建立索引。因為主鍵可以加速定位到表中的某一行。結合索引的作用,可以使得查詢的速度加倍。如在員工資訊表中,我們往往把員工編號設定為主鍵。因為這不但可以提高查詢的速度,而且因為主鍵要求記錄的唯一,還可以保證員工編號的唯一性。此時,若再把這個員工編號欄位設定為索引,則通過員工編號來查詢員工資訊,其效率要比沒有建立索引高出許多。

另外,若要使得某個欄位的值唯一,可以通過兩種索引方式實現。一種就是上面所講的主鍵索引。還有一種就是唯一索引,利用UNIQUE關鍵字指定欄位內容的唯一性。這兩種方式都會在表中的指定列上自動建立唯一索引。這兩種方式的結果沒有明顯的區別。查詢優化器不會區分到底是哪種方式建立的唯一性索引,而且他們進行資料查詢的方式也是相同的。

若某張表中的資料列定義有外來鍵,則最好也要為這個欄位建立索引。因為外來鍵的主要作用就在於表與表之間的連線查詢。若在外來鍵上建立索引,可以加速表與表之間的連線查詢。如在員工基本資訊表中,有一個欄位為員工職位。由於員工職位經常在變化,在這裡,儲存的其實只是一個員工職位的程式碼。在另外一張職位資訊表中詳細記錄著該職位的相關資訊。此時,這個員工職位欄位就是外來鍵。若在這個欄位上建立外來鍵,則可以顯著提高兩張表的連線速度。而且,記錄越多,其效果越加明顯。

所以,當表中有外來鍵或者主鍵的時候,就最好為其建立索引。通過索引,可以強化主鍵與外來鍵的作用,提高資料庫的效能。

 

鐵律五:對於一些特殊的資料型別,不要建立索引。

在表中,有些欄位比較特殊。如文字欄位(TXT)、影像型別欄位(IMAGE)等等。如果表中的欄位屬於這些資料型別,則最好不要為其建立索引。因為這些欄位有一些共同的特點。如長度不確定,要麼很長,幾個字元;要麼就是空字串。如文字資料型別常在應用系統的資料庫表中用來做備註的資料型別。有時候備註很長,但有時候又沒有資料。若這種型別的欄位上建立索引,那根本起不了作用。相反,還增加了系統的負擔。

所以,在一些比較特殊的資料型別上,建立索引要謹慎。在通常情況下,沒有必要為其建立索引。但是,也有特殊的情況。如有時候,在ERP系統中,有產品資訊這個表,其中有個產品規格這個欄位。有時候,其長度可能長達5000個字元。此時,只有文字型的資料型別可以容納這麼大的資料量。而且,在查詢的時候,使用者又喜歡通過規格這個引數來查詢產品資訊。此時,若不為這個欄位建立索引的話,則查詢的速度會很慢。遇到這種情況時,資料庫管理員只有犧牲一點系統資源,為其建立索引。

從這裡也可以看出,雖然以上幾條說的時鐵律,但是,是否需要遵循,還是需要資料庫管理員根據企業的實際情況,做出合理的選擇。

 

鐵律六:索引可以跟Where語句的集合融為一體。

使用者在查詢資訊的時候,有時會經常會用到一些限制語句。如在查詢銷售訂單的時候,經常會用到客戶以及下單日期的條件集合;如在查詢某個產品的庫存交易情況時,就會利用產品編號與交易日期起止日期的條件集合。

對於這些經常用在Where子句中的資料列,將索引建立在Where子句的集合過程中,對於需要加速或者頻繁檢索的資料列,可以讓這些經常參與查詢的資料列按照索引的排序進行查詢,以加快查詢的時間。

總之,索引就好像一把雙刃劍,即可以提高資料庫的效能,也可能對資料庫的效能起到反面作用。作為資料庫管理員,要有這個能力判斷在合適的時間、合適的業務、合適的欄位上建立合適的索引。以上六個鐵律,只是對建立索引的一些基本要求

 

本文來自CSDN部落格,轉載請標明出處:http://blog.csdn.net/yanshouke/archive/2008/12/18/3547518.aspx

相關文章