萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> SQL Server索引管理的六大鐵律

SQL Server索引管理的六大鐵律

  索引是以表列為基礎的數據庫對象。索引中保存著表中排序的索引列,並且紀錄了索引列在數據庫表中的物理存儲位置,實現了表中數據的邏輯排序。通過索引,可以加快數據的查詢速度和減少系統的響應時間;可以使表和表之間的連接速度加快。

  但是,不是在任何時候使用索引都能夠達到這種效果。若在不恰當的場合下,使用索引反而會事與願違。所以,在SQL Server數據庫中使用索引的話,還是需要遵守一定的規則。筆者覺得,主要是需要遵守六大鐵律。

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

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

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

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

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

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

  另外,如上面的員工信息表,有些字段重復值比較多。如性別字段主要就是“男”、“女”;職位字段中也是有限的幾個內容。此時,在這些字段上添加索引也不會顯著的增加查詢速度,減少用戶響應時間。相反,因為需要占用空間,反而會降低數據庫的整體性能。

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

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

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

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

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

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

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

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

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

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

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

  鐵律五:對於一些特殊的數據類型,不要建立索引

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

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

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

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

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

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

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

copyright © 萬盛學電腦網 all rights reserved