資料庫索引

資料庫索引

索引是對資料庫表中一列或多列的值進行排序的一種結構,使用索引可快速訪問資料庫表中的特定信息。如果想按特定職員的姓來查找他或她,則與在表中搜尋所有的行相比,索引有助於更快地獲取信息。

索引的一個主要目的就是加快檢索表中數據,亦即能協助信息搜尋者儘快的找到符合限制條件的記錄ID的輔助數據結構。

基本介紹

  • 中文名資料庫索引
  • 外文名:index
  • 分類:聚簇索引 非聚簇索引
  • 詳述:提高系統的性能
  • 目的:加快對表中記錄的查找或排序
  • 優點:迅速
簡介,基本概念,主要種類,基本特點,優點,缺點,注意事項,操作案例,

簡介

索引是對資料庫表中一個或多個列(例如,employee 表的姓名 (name) 列)的值進行排序的結構。
例如這樣一個查詢:select * from table1 where id=10000。如果沒有索引,必須遍歷整個表,直到ID等於10000的這一行被找到為止;有了索引之後(必須是在ID這一列上建立的索引),即可在索引中查找。由於索引是經過某種算法最佳化過的,因而查找次數要少的多。可見,索引是用來定位的。
從數據搜尋實現的角度來看,索引也是另外一類檔案/記錄,它包含著可以指示出相關數據記錄的各種記錄。其中,每一索引都有一個相對應的搜尋碼,字元段的任意一個子集都能夠形成一個搜尋碼。這樣,索引就相當於所有數據目錄項的一個集合,它能為既定的搜尋碼值的所有數據目錄項提供定位所需的各種有效支持。

基本概念

  • 搜尋碼。它表示的是記錄各種字元段的一個集合,它可以是一個或者是多個字元段的任意序列組合,並不是惟一的一個標識記錄。
  • 數據目錄項。即為索引的相關元素,在建立索引的過程中,數據目錄項一般具有各種不同的選擇方式。
  • 記錄ID。每一個/段索引在存儲內容中惟一的一個標識符。

主要種類

資料庫索引好比是一本書前面的目錄,能加快資料庫的查詢速度。索引分為聚簇索引非聚簇索引兩種,聚簇索引 是按照數據存放的物理位置為順序的,而非聚簇索引就不一樣了;聚簇索引能提高多行檢索的速度,而非聚簇索引對於單行的檢索很快。
SQL Server的B樹結構SQL Server的B樹結構
根據資料庫的功能,可以在資料庫設計器中創建三種索引唯一索引、主鍵索引和聚集索引。有關資料庫所支持的索引功能的詳細信息,請參見資料庫文檔。
提示:儘管唯一索引有助於定位信息,但為獲得最佳性能結果,建議改用主鍵唯一約束
唯一索引
唯一索引是不允許其中任何兩行具有相同索引值的索引。當現有數據中存在重複的鍵值時,大多數資料庫不允許將新創建的唯一索引與表一起保存。資料庫還可能防止添加將在表中創建重複鍵值的新數據。例如,如果在employee表中職員的姓(lname)上創建了唯一索引,則任何兩個員工都不能同姓。
主鍵索引
資料庫表經常有一列或多列組合,其值唯一標識表中的每一行。該列稱為表的主鍵。在資料庫關係圖中為表定義主鍵將自動創建主鍵索引,主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。當在查詢中使用主鍵索引時,它還允許對數據的快速訪問。
聚集索引
在聚集索引中,表中行的物理順序與鍵值的邏輯(索引)順序相同。一個表只能包含一個聚集索引。如果某索引不是聚集索引,則表中行的物理順序與鍵值的邏輯順序不匹配。與非聚集索引相比,聚集索引通常提供更快的數據訪問速度。聚集索引和非聚集索引的區別,如字典默認按字母順序排序,讀者如知道某個字的讀音可根據字母順序快速定位。因此聚集索引和表的內容是在一起的。如讀者需查詢某個生僻字,則需按字典前面的索引,舉例按偏旁進行定位,找到該字對應的頁數,再打開對應頁數找到該字。這種通過兩個地方而查詢到某個字的方式就如非聚集索引。
索引列
可以基於資料庫表中的單列或多列創建索引。多列索引可以區分其中一列可能有相同值的行。如果經常同時搜尋兩列或多列或按兩列或多列排序時,索引也很有幫助。例如,如果經常在同一查詢中為姓和名兩列設定判據,那么在這兩列上創建多列索引將很有意義。
檢查查詢的WHERE和JOIN子句。在任一子句中包括的每一列都是索引可以選擇的對象。對新索引進行試驗以檢查它對運行查詢性能的影響。考慮已在表上創建的索引數量。最好避免在單個表上有很多索引。檢查已在表上創建的索引的定義。最好避免包含共享列的重疊索引
檢查某列中唯一數據值的數量,並將該數量與表中的行數進行比較。比較的結果就是該列的可選擇性,這有助於確定該列是否適合建立索引,如果適合,確定索引的類型。

基本特點

建立索引的目的是加快對表中記錄的查找排序。為表設定索引要付出代價的:一是增加了資料庫的存儲空間,二是在插入和修改數據時要花費較多的時間(因為索引也要隨之變動)。資料庫索引就是為了提高表的搜尋效率而對某些欄位中的值建立的目錄 。
創建索引可以大大提高系統的性能。第一,通過創建唯一性索引,可以保證資料庫表中每一行數據的唯一性。第二,可以大大加快數據的檢索速度,這也是創建索引的最主要的原因。第三,可以加速表和表之間的連線,特別是在實現數據的參考完整性方面特別有意義。第四,在使用分組和排序子句進行數據檢索時,同樣可以顯著減少查詢中分組和排序的時間。第五,通過使用索引,可以在查詢的過程中,使用最佳化隱藏器,提高系統的性能。
因為,增加索引也有許多不利的方面。第一,創建索引和維護索引要耗費時間,這種時間隨著數據量的增加而增加。第二,索引需要占物理空間,除了數據表占數據空間之外,每一個索引還要占一定的物理空間,如果要建立聚簇索引,那么需要的空間就會更大。第三,當對表中的數據進行增加、刪除和修改的時候,索引也要動態的維護,這樣就降低了數據的維護速度。

優點

通過建立索引可以極大地提高在資料庫中獲取所需信息的速度,同時還能提高伺服器處理相關搜尋請求的效率,從這個方面來看它具有以下優點:
  • 在設計資料庫時,通過創建一個惟一的索引,能夠在索引和信息之間形成一對一的映射式的對應關係,增加數據的惟一性特點。
  • 能提高數據的搜尋及檢索速度,符合資料庫建立的初衷。
  • 能夠加快表與表之間的連線速度,這對於提高數據的參考完整性方面具有重要作用。
  • 在信息檢索過程中,若使用分組及排序子句進行時,通過建立索引能有效的減少檢索過程中所需的分組及排序時間,提高檢索效率。
  • 建立索引之後,在信息查詢過程中可以使用最佳化隱藏器,這對於提高整個信息檢索系統的性能具有重要意義。

缺點

雖然索引的建立在提高檢索效率方面具有諸多積極的作用,但還是存在下列缺點:
  • 在資料庫建立過程中,需花費較多的時間去建立並維護索引,特別是隨著數據總量的增加,所花費的時間將不斷遞增。
  • 在資料庫中創建的索引需要占用一定的物理存儲空間,這其中就包括數據表所占的數據空間以及所創建的每一個索引所占用的物理空間,如果有必要建立起聚簇索引,所占用的空間還將進一步的增加
  • 在對表中的數據進行修改時,例如對其進行增加、刪除或者是修改操作時,索引還需要進行動態的維護,這給資料庫的維護速度帶來了一定的麻煩。

注意事項

索引是建立在資料庫表中的某些列的上面。在創建索引的時候,應該考慮在哪些列上可以創建索引,在哪些列上不能創建索引。一般來說,應該在這些列上創建索引:
在經常需要搜尋的列上,可以加快搜尋的速度;
在作為主鍵的列上,強制該列的唯一性和組織表中數據的排列結構;
在經常用在連線的列上,這些列主要是一些外鍵,可以加快連線的速度;在經常需要根據範圍進行搜尋的列上創建索引,因為索引已經排序,其指定的範圍是連續的;
在經常需要排序的列上創建索引,因為索引已經排序,這樣查詢可以利用索引的排序,加快排序查詢時間;
在經常使用在WHERE子句中的列上面創建索引,加快條件的判斷速度。
同樣,對於有些列不應該創建索引。一般來說,不應該創建索引的這些列具有下列特點:
第一,對於那些在查詢中很少使用或者參考的列不應該創建索引。這是因為,既然這些列很少使用到,因此有索引或者無索引,並不能提高查詢速度。相反,由於增加了索引,反而降低了系統的維護速度和增大了空間需求。
第二,對於那些只有很少數據值的列也不應該增加索引。這是因為,由於這些列的取值很少,例如人事表的性別列,在查詢的結果中,結果集的數據行占了表中數據行的很大比例,即需要在表中搜尋的數據行的比例很大。增加索引,並不能明顯加快檢索速度。
第三,對於那些定義為text, image和bit數據類型的列不應該增加索引。這是因為,這些列的數據量要么相當大,要么取值很少,不利於使用索引。
第四,當修改性能遠遠大於檢索性能時,不應該創建索引。這是因為,修改性能和檢索性能是互相矛盾的。當增加索引時,會提高檢索性能,但是會降低修改性能。當減少索引時,會提高修改性能,降低檢索性能。因此,當修改操作遠遠多於檢索操作時,不應該創建索引。

操作案例

最普通的情況,是為出現在where子句的欄位建一個索引
CREATE TABLE mytable(idserial int primary key,category_id int default 0not null ,user_id int default 0not null ,adddate int default 0not null);
如果在查詢時常用類似以下的語句:
SELECT * FROM mytable WHERE category_id=1;
最直接的應對之道,是為category_id建立一個簡單的索引
CREATE INDEX mytable_categoryid ON mytable (category_id);
OK.如果有不止一個選擇條件呢?例如:
SELECT * FROM mytable WHERE category_id=1 AND user_id=2;
第一反應可能是,再給user_id建立一個索引。不好,這不是一個最佳的方法。可以建立多重的索引
CREATE INDEX mytable_categoryid_userid ON mytable(category_id,user_id);
注意到在命名時的習慣了嗎?使用"表名_欄位1名_欄位2名"的方式。很快就會知道為什麼這樣做了。
現在已經為適當的欄位建立了索引,不過,還是有點不放心吧,可能會問,資料庫會真正用到這些索引嗎?測試一下就OK,對於大多數的資料庫來說,這是很容易的,只要使用EXPLAIN命令:
EXPLAINSELECT * FROM mytableWHERE category_id=1 AND user_id=2;This is what Postgres 7.1 returns (exactlyasI expected)NOTICE:QUERY PLAN:Index Scan using mytable_categoryid_userid onmytable(cost=0.00..2.02 rows=1 width=16)EXPLAIN
以上是postgres的數據,可以看到該資料庫在查詢的時候使用了一個索引(一個好開始),而且它使用的是創建的第二個索引。看到上面命名的好處了吧,馬上知道它使用適當的索引了。
接著,來個稍微複雜一點的,如果有個ORDERBY 子句呢?不管你信不信,大多數的資料庫在使用orderby的時候,都將會從索引中受益。
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;
很簡單,就像為where子句中的欄位建立一個索引一樣,也為ORDER BY的子句中的欄位建立一個索引:
CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate);
注意:"mytable_categoryid_userid_adddate"將會被截短為"mytable_categoryid_userid_addda"
CREATEEXPLAIN SELECT * FROM mytableWHERE category_id=1 AND user_id=2ORDER BY adddate DESC;NOTICE:QUERY PLAN:Sort(cost=2.03..2.03 rows=1 width=16)->Index Scanusing mytable_categoryid_userid_adddaon mytable(cost=0.00..2.02 rows=1 width=16)EXPLAIN
看看EXPLAIN的輸出,資料庫多做了一個沒有要求的排序,這下知道性能如何受損了吧,看來對於資料庫的自身運作是有點過於樂觀了,那么,給資料庫多一點提示吧。
為了跳過排序這一步,並不需要其它另外的索引,只要將查詢語句稍微改一下。這裡用的是postgres,將給該資料庫一個額外的提示--在ORDER BY語句中,加入where語句中的欄位。這只是一個技術上的處理,並不是必須的,因為實際上在另外兩個欄位上,並不會有任何的排序操作,不過如果加入,postgres將會知道哪些是它應該做的。
EXPLAIN SELECT * FROM mytableWHERE category_id=1 AND user_id=2ORDER BY category_id DESC,user_id DESC,adddate DESC;NOTICE:QUERY PLAN:Index Scan Backward usingmytable_categoryid_userid_addda on mytable(cost=0.00..2.02 rows=1 width=16)EXPLAIN
現在使用料想的索引了,而且它還挺聰明,知道可以從索引後面開始讀,從而避免了任何的排序。
以上說得細了一點,不過如果資料庫非常巨大,並且每日的頁面請求達上百萬算,想會獲益良多的。不過,如果要做更為複雜的查詢呢,例如將多張表結合起來查詢,特別是where限制字句中的欄位是來自不止一個表格時,應該怎樣處理呢?通常都儘量避免這種做法,因為這樣資料庫要將各個表中的東西都結合起來,然後再排除那些不合適的行,搞不好開銷會很大。
如果不能避免,應該查看每張要結合起來的表,並且使用以上的策略來建立索引,然後再用EXPLAIN命令驗證一下是否使用了料想中的索引。如果是的話,就OK。不是的話,可能要建立臨時的表來將他們結合在一起,並且使用適當的索引。
要注意的是,建立太多的索引將會影響更新和插入的速度,因為它需要同樣更新每個索引檔案。對於一個經常需要更新和插入的表格,就沒有必要為一個很少使用的where字句單獨建立索引了,對於比較小的表,排序的開銷不會很大,也沒有必要建立另外的索引。
以上介紹的只是一些十分基本的東西,其實裡面的學問也不少,單憑EXPLAIN是不能判定該方法是否就是最最佳化的,每個資料庫都有自己的一些最佳化器,雖然可能還不太完善,但是它們都會在查詢時對比過哪種方式較快,在某些情況下,建立索引的話也未必會快,例如索引放在一個不連續的存儲空間時,這會增加讀磁碟的負擔,因此,哪個是最優,應該通過實際的使用環境來檢驗。
在剛開始的時候,如果表不大,沒有必要作索引,意見是在需要的時候才作索引,也可用一些命令來最佳化表,例如MySQL可用"OPTIMIZETABLE"。

相關詞條

熱門詞條

聯絡我們