淺談數據庫優化之-索引

數據庫索引

1．什么是索引

在數據庫中，索引的含義與日常意義上的“索引”一詞并無多大區別（想想小時候查字典），它是用于提高數據庫表數據訪問速度的數據庫對象。A）索引可以避免全表掃描。多數查詢可以僅掃描少量索引頁及數據頁，而不是遍歷所有數據頁。B）對于非聚集索引，有些查詢甚至可以不訪問數據頁。C）聚集索引可以避免數據插入操作集中于表的最后一個數據頁。D）一些情況下，索引還可用于避免排序操作。

當然，眾所周知，雖然索引可以提高查詢速度，但是它們也會導致數據庫系統更新數據的性能下降，因為大部分數據更新需要同時更新索引。

例如這樣一個查詢：select * from table1 where id=44。如果沒有索引，必須遍歷整個表，直到ID等于44的這一行被找到為止；有了索引之后(必須是在ID這一列上建立的索引)，直接在索引里面找44（也就是在ID這一列找），就可以得知這一行的位置，也就是找到了這一行?？梢?，索引是用來定位的。

2.索引的存儲

一條索引記錄中包含的基本信息包括：鍵值（即你定義索引時指定的所有字段的值）+邏輯指針（指向數據頁或者另一索引頁）。

當你為一張空表創建索引時，數據庫系統將為你分配一個索引頁，該索引頁在你插入數據前一直是空的。此頁此時既是根結點，也是葉結點。每當你往表中插入一行數據，數據庫系統即向此根結點中插入一行索引記錄。當根結點滿時，數據庫系統大抵按以下步驟進行分裂：A）創建兩個兒子結點B）將原根結點中的數據近似地拆成兩半，分別寫入新的兩個兒子結點C）根結點中加上指向兩個兒子結點的指針

通常狀況下，由于索引記錄僅包含索引字段值（以及4-9字節的指針），索引實體比真實的數據行要小許多，索引頁相較數據頁來說要密集許多。一個索引頁可以存儲數量更多的索引記錄，這意味著在索引中查找時在I/O上占很大的優勢，理解這一點有助于從本質上了解使用索引的優勢。

3．索引的類型

A) 唯一索引:唯一索引不允許兩行具有相同索引值

B) 主鍵索引:定義一個主鍵將自動創建主鍵索引,主鍵索引是唯一索引的特殊類型.要求每個值唯一的,并且不能為空。

C）聚集索引，表數據按照索引的順序來存儲的。對于聚集索引，葉子結點即存儲了真實的數據行，不再有另外單獨的數據頁，每個表只能一個

D）非聚集索引，表數據存儲順序與索引順序無關。對于非聚集索引，數據存儲在一個位置,索引存儲在另一個位置,索引中包含指向數據存儲位置的指針。

4. 索引創建語法

crate [unique](唯一) [clustered](聚集) [nonclustered] index  索引名稱 on 表名(列名)

注:以上[]中的值根據需要選擇一個

5.索引刪除語法

drop index 表名.索引名

sysindexes:系統索引表

6.索引優缺點

優點：創建索引可以大大提高系統的性能。　　　　1):通過創建唯一性索引，可以保證數據庫表中每一行數據的唯一性?！　　　?):可以大大加快數據的檢索速度，這也是創建索引的最主要的原因?！　　　?):可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義?！　　　?):在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間?！　　　?):通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

缺點： 1):創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加?！　　　?):索引需要占物理空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大?！　　　?):當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。

7.在哪建索引

　　索引是建立在數據庫表中的某些列的上面。在創建索引的時候，應該考慮在哪些列上可以創建索引，在哪些列上不能創建索引。

　　一般來說，應該在這些列上創建索引：　　1):在經常需要搜索的列上，可以加快搜索的速度；　　2):在作為主鍵的列上，強制該列的唯一性和組織表中數據的排列結構；　　3):在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍是連續的；　　4):在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；　　5):在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。

　　同樣，對于有些列不應該創建索引。一般來說，不應該創建索引的的這些列具有下列特點：　　1):對于那些在查詢中很少使用或者參考的列不應該創建索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度。

　　　相反，由于增加了索引，反而降低了系統的維護速度和增大了空間需求?！　?):對于那些只有很少數據值的列也不應該增加索引。這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的數據行占了表中數據行的很大比例，

　　　即需要在表中搜索的數據行的比例很大。增加索引，并不能明顯加快檢索速度?！　?):對于那些定義為text, image和bit數據類型的列不應該增加索引。這是因為，這些列的數據量要么相當大，要么取值很少,不利于使用索引。　　4):當修改性能遠遠大于檢索性能時，不應該創建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。

　　　當減少索引時，會提高修改性能，降低檢索性能。因此，當修改操作遠遠多于檢索操作時，不應該創建索引。