索引是以表列為基礎的數據庫對象索引中保存著表中排序的索引列並且紀錄了索引列在數據庫表中的物理存儲位置實現了表中數據的邏輯排序通過索引可以加快數據的查詢速度和減少系統的響應時間;可以使表和表之間的連接速度加快
但是不是在任何時候使用索引都能夠達到這種效果若在不恰當的場合下使用索引反而會事與願違所以在SQL Server數據庫中使用索引的話還是需要遵守一定的規則筆者覺得主要是需要遵守六大鐵律
鐵律一天下沒有免費的午餐使用索引是需要付出代價的
索引的優點有目共睹但是卻很少有人關心過采用索引所需要付出的成本若數據庫管理員能夠對索引所需要付出的代價有一個充分的認識也就不會那麼隨意到處建立索引了
仔細數數其實建立索引的代價還是蠻大的如創建索引和維護索引都需要花費時間與精力特別是在數據庫設計的時候數據庫管理員為表中的哪些字段需要建立索引要調研要協調如當建有索引的表中的紀錄又增加刪除修改操作時數據庫要對索引進行重新調整雖然這個工作數據庫自動會完成但是需要消耗服務器的資源當表中的數據越多這個消耗的資源也就越多如索引是數據庫中實際存在的對象所以每個索引都會占用一定的物理空間若索引多了不但會占用大量的物理空間而且也會影響到整個數據庫的運行性能
可見數據庫管理員若要采用索引來提高系統的性能自身仍然需要付出不少的代價數據庫管理員現在要考慮的就是如何在這兩個之間取得一個均衡或者說找到一個回報與投入的臨界點
鐵律二對於查詢中很少涉及的列或者重復值比較多的列不要建立索引
在查詢的時候如果我們不按某個字段去查詢則在這個字段上建立索引也是浪費如現在有一張員工信息表我們可能按員工編號員工姓名或者出身地去查詢員工信息但是我們往往不會按照身份證號碼去查詢雖然這個身份證號碼是唯一的此時即使在這個字段上建立索引也不能夠提高查詢的速度相反增加了系統維護時間和占用了系統空間這簡直就是搬起石頭砸自己的腳呀
另外如上面的員工信息表有些字段重復值比較多如性別字段主要就是“男”“女”;職位字段中也是有限的幾個內容此時在這些字段上添加索引也不會顯著的增加查詢速度減少用戶響應時間相反因為需要占用空間反而會降低數據庫的整體性能
數據庫索引管理中的第二條鐵律就是對於查詢中很少涉及的列或者重復值比較多的列不要建立索引
鐵律三對於按范圍查詢的列最好建立索引
在信息化管理系統中很多時候需要按范圍來查詢某些交易記錄如在ERP系統中經常需要查詢當月的銷售訂單與銷售出貨情況這就需要按日期范圍來查詢交易記錄如有時候發現庫存不對時也需要某段時期的庫存進出情況如月日到月日的庫存交易情況等等此時也是根據日期來進行查詢
對於這些需要在指定范圍內快速或者頻繁查詢的數據列需要為其建立索引因為索引已經排序其保存的時候指定的范圍是連續的查詢可以利用索引的排序加快查詢時間減少用戶等待時間
不過若雖然可能需要按范圍來進行查詢但是若這個范圍查詢條件利用的不多的情況下最好不好采用索引如在員工信息表中可能需要查詢 年月份以前入職的員工明細要為他們增加福利但是由於表中記錄不多而且也很少進行類似的查詢若維這個字段建立索引雖然無傷大雅但是很明顯索引所獲得的收益要低於其成本支出對數據庫管理員來說是得不償失的
再者若采用范圍查詢的話最好能利用TOP關鍵字來限制一次查詢的結果如第一次按順序只顯示前面的條記錄等等把TOP關鍵字跟范圍一起使用可以大大的提高查詢的效率
鐵律四表中若有主鍵或者外鍵一定要為其建立索引
定義有主鍵的索引列一定要為其建立索引因為主鍵可以加速定位到表中的某一行結合索引的作用可以使得查詢的速度加倍如在員工信息表中我們往往把員工編號設置為主鍵因為這不但可以提高查詢的速度而且因為主鍵要求記錄的唯一還可以保證員工編號的唯一性此時若再把這個員工編號字段設置為索引則通過員工編號來查詢員工信息其效率要比沒有建立索引高出許多
另外若要使得某個字段的值唯一可以通過兩種索引方式實現一種就是上面所講的主鍵索引還有一種就是唯一索引利用UNIQUE關鍵字指定字段內容的唯一性這兩種方式都會在表中的指定列上自動創建唯一索引這兩種方式的結果沒有明顯的區別查詢優化器不會區分到底是哪種方式建立的唯一性索引而且他們進行數據查詢的方式也是相同的
若某張表中的數據列定義有外鍵則最好也要為這個字段建立索引因為外鍵的主要作用就在於表與表之間的連接查詢若在外鍵上建立索引可以加速表與表之間的連接查詢如在員工基本信息表中有一個字段為員工職位由於員工職位經常在變化在這裡存儲的其實只是一個員工職位的代碼在另外一張職位信息表中詳細記錄著該職位的相關信息此時這個員工職位字段就是外鍵若在這個字段上建立外鍵則可以顯著提高兩張表的連接速度而且記錄越多其效果越加明顯
所以當表中有外鍵或者主鍵的時候就最好為其建立索引通過索引可以強化主鍵與外鍵的作用提高數據庫的性能
鐵律五對於一些特殊的數據類型不要建立索引
在表中有些字段比較特殊如文本字段(TXT)圖像類型字段(IMAGE)等等如果表中的字段屬於這些數據類型則最好不要為其建立索引因為這些字段有一些共同的特點如長度不確定要麼很長幾個字符;要麼就是空字符串如文本數據類型常在應用系統的數據庫表中用來做備注的數據類型有時候備注很長但有時候又沒有數據若這種類型的字段上建立索引那根本起不了作用相反還增加了系統的負擔
所以在一些比較特殊的數據類型上建立索引要謹慎在通常情況下沒有必要為其建立索引但是也有特殊的情況如有時候在ERP系統中有產品信息這個表其中有個產品規格這個字段有時候其長度可能長達個字符此時只有文本型的數據類型可以容納這麼大的數據量而且在查詢的時候用戶又喜歡通過規格這個參數來查詢產品信息此時若不為這個字段建立索引的話則查詢的速度會很慢遇到這種情況時數據庫管理員只有犧牲一點系統資源為其建立索引
從這裡也可以看出雖然以上幾條說的時鐵律但是是否需要遵循還是需要數據庫管理員根據企業的實際情況做出合理的選擇
鐵律六索引可以跟Where語句的集合融為一體
用戶在查詢信息的時候有時會經常會用到一些限制語句如在查詢銷售訂單的時候經常會用到客戶以及下單日期的條件集合;如在查詢某個產品的庫存交易情況時就會利用產品編號與交易日期起止日期的條件集合
對於這些經常用在Where子句中的數據列將索引建立在 Where子句的集合過程中對於需要加速或者頻繁檢索的數據列可以讓這些經常參與查詢的數據列按照索引的排序進行查詢以加快查詢的時間
總之索引就好像一把雙刃劍即可以提高數據庫的性能也可能對數據庫的性能起到反面作用作為數據庫管理員要有這個能力判斷在合適的時間合適的業務合適的字段上建立合適的索引以上六個鐵律只是對建立索引的一些基本要求
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22311.html
