摘 要：索引是影響關(guān)系數(shù)據(jù)庫（RDBMS）性能的重要因素之一。當(dāng)今常用的關(guān)系數(shù)據(jù)庫，如SQL Server、Sybase、Oracle、DB2等，為了提高性能，都提供相應(yīng)的索引機制。本文以微軟的SQL Server數(shù)據(jù)庫為例，介紹索引的概念、使用方法以及日常維護。
關(guān)鍵詞：數(shù)據(jù)庫、索引、檢索
1 索引的概念
索引是一個單獨的、物理的數(shù)據(jù)庫結(jié)構(gòu)，它是某個表中一列或若干列值的集合和相應(yīng)的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單。表的存儲由兩部分組成，一部分用來存放數(shù)據(jù)頁面，另一部分存放索引頁面。通常，索引頁面相對于數(shù)據(jù)頁面來說小得多。數(shù)據(jù)檢索花費的大部分開銷是磁盤讀寫，沒有索引就需要從磁盤上讀表的每一個數(shù)據(jù)頁，如果有索引，則只需查找索引頁面就可以了。所以建立合理的索引，就能加速數(shù)據(jù)的檢索過程。
SQL Server采用B-樹結(jié)構(gòu)的索引，根據(jù)索引的順序與數(shù)據(jù)表的物理順序是否相同可以分為：聚簇索引（clustered index）和非聚簇索引（nonclustered index）。
（1）聚簇索引重新組織表中的數(shù)據(jù)以按指定的一個或多個列的值排序。聚簇索引的葉節(jié)點包含實際的數(shù)據(jù)，因此用它查找數(shù)據(jù)很快，但每個表只能建一個聚簇索引。
（2）非聚簇索引不重新組織表中的數(shù)據(jù)，它的葉節(jié)點中存儲了組成非聚簇索引的列的值和行定位指針。一個表可以建249 個非聚簇索引。
通俗的說，漢語字典的正文就是一個建立在拼音基礎(chǔ)上的聚簇索引，以英文字母“a”開頭并以“z”結(jié)尾。比如，我們要查“阿”字，就會翻開字典的第一頁，因為“阿”的拼音是“a”，所以排在字典的前面。如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那么就說明字典中沒有這個字。同樣的，如果查“做”字，就會把字典翻到最后。
字典的“偏旁部首”是非聚簇索引。比如我們要查“阿”字，在查部首之后，看到部首檢字表中“阿”的頁碼是1頁，“阿”的上面是“際”字，但頁碼卻是277頁，“阿”的下面是“隴”字，頁碼是416頁。很顯然，這些字并不是真正的分別位于“阿”字的上下方，現(xiàn)在看到的連續(xù)的“際、阿、隴”三字實際上就是他們在非聚簇索引中的排序，是字典正文中的字在非聚簇索引中的映射。
2 索引的使用
1）聚簇索引的使用
在聚簇索引下，數(shù)據(jù)在物理上按順序排在數(shù)據(jù)頁上，重復(fù)值也排在一起，因而在那些包含范圍檢查(between、<、<=、>、>=)或使用group by、order by的查詢時，一旦找到具有范圍中第一個鍵值的行，具有后續(xù)索引值的行必然連在一起，不必進一步搜索，避免了大范圍掃描，可以大大提高查詢速度。
聚簇索引的侯選列是：
u 經(jīng)常按范圍存取的列，如date>”20050101” and date< “20050131”；
u 經(jīng)常在where子句中使用并且插入是隨機的主鍵列；
u 在group by或order by中使用的列；
u 在連接操作中使用的列。
2）非聚簇索引的使用
由于非聚簇索引的葉級點不包含實際的數(shù)據(jù)，因此它檢索效率較低，但一個表只能建一個聚簇索引，當(dāng)用戶需要建立多個索引時就需要使用非聚簇索引了。在建立非聚簇索引時，要權(quán)衡索引對查詢速度的加快與降低修改速度之間的利弊。
在下面情況中使用非聚簇索引：
u 常用于集合函數(shù)(如Sum,....)的列；
u 常用于join, order by, group by的列；
u 查尋出的數(shù)據(jù)不超過表中數(shù)據(jù)量的20%。
表1 索引使用情況分析表
| 情況描述 | 使用聚簇索引 | 使用非聚簇索引 |
| 用于返回某范圍內(nèi)數(shù)據(jù)的列 | 應(yīng) | 不應(yīng) |
| 經(jīng)常被用作分組排序的列 | 應(yīng) | 應(yīng) |
| 小數(shù)目不同值的列 | 應(yīng) | 不應(yīng) |
| 連接操作使用的列 | 應(yīng) | 應(yīng) |
| 頻繁更新、修改的列 | 不應(yīng) | 應(yīng) |
| 一個或極少不同值的列 | 不應(yīng) | 不應(yīng) |
| 大數(shù)目不同值的列 | 不應(yīng) | 應(yīng) |
3）創(chuàng)建索引需要注意的要點
1) 慎重選擇作為聚簇索引的列
默認情況下，SQL Server用主鍵創(chuàng)建聚簇索引。這種做法常常造成聚簇索引的浪費。通常，我們會為每個表建立一個ID列，以區(qū)分每條數(shù)據(jù)，并且該列是自動增大的，步長一般為1。如果我們把這個列設(shè)為主鍵，SQL Server會將此列默認為聚簇索引。這樣做可以使數(shù)據(jù)在數(shù)據(jù)庫中按ID進行物理排序，但這種做法在實際應(yīng)用中意義并不大。根據(jù)前面談到的聚簇索引的定義和使用情況可以看出，使用聚簇索引的最大好處就是能夠根據(jù)查詢要求，迅速返回某個范圍內(nèi)的數(shù)據(jù)，避免全表掃描。在實際應(yīng)用中，因為ID號是自動生成的，我們并不知道每條記錄的ID號，所以我們不太可能用ID號來進行查詢。這就使聚簇索引成為擺設(shè)，造成資源浪費。其次，讓每個值都不同的ID列作為聚簇索引也不符合“大數(shù)目的不同值情況下不應(yīng)建立聚簇索引”規(guī)則。
一般情況下，數(shù)據(jù)庫應(yīng)用系統(tǒng)進行數(shù)據(jù)檢索都離不開“ 用戶名（代碼）”、“日期”字段。以筆者所用的HIS系統(tǒng)（醫(yī)院管理信息系統(tǒng)）為例，我們進行費用、處方、檢查單等信息檢索時需要根據(jù)“住院號”和“日期”這兩個字段來返回特定范圍內(nèi)的數(shù)據(jù)。下面我們分幾種情況觀察在不同索引條件下查詢相同內(nèi)容所用的時間。
假設(shè)病人費用表名為“brfy”，其中住院號字段名為“zyh”，日期字段名為“riqi”，要求是從表brfy中檢索zyh為“028246”的病人2005年3月1日到20日的費用，對應(yīng)的SQL語句如下：
Select * from brfy where zyh=’028246’ and riqi>=’20050301’ and riqi<=’20050320’;
第一種情況，用ID列建立聚簇索引，不為zyh和riqi建立索引，查詢時間為87秒。
第二種情況，用ID列建立聚簇索引，為zyh和riqi兩列建立非聚簇索引（zyh在前），查詢時間為33秒。
第三種情況，用zyh和riqi兩列建立聚簇索引（zyh在前），查詢時間為2秒。
由以上分析可以看出聚簇索引是非常寶貴的，應(yīng)該為經(jīng)常用于檢索某個范圍內(nèi)數(shù)據(jù)的列或group by、order by等子句的列建立聚簇索引，這樣能夠極大的提高系統(tǒng)性能。
2) 重視以多個列創(chuàng)建的索引中列的順序問題
一些用戶認為只要合理的選擇列建立索引，不必關(guān)心列的順序就可以提高檢索速度，這種觀點是錯誤的。多列索引中列的先后順序應(yīng)該和實際應(yīng)用中where、group by或order by等子句里列的放置位置相同。參考上面舉的例子，在第二、第三種情況下，如果把riqi放在zyh前面，執(zhí)行上述SQL語句就不會用到這兩個索引，檢索的時間也會變得很長。
3 索引的維護
數(shù)據(jù)庫系統(tǒng)運行一段時間后，隨著數(shù)據(jù)行的插入、刪除和數(shù)據(jù)頁的分裂，索引對系統(tǒng)的優(yōu)化性能就會大大降低。這時候，我們需要對索引進行分析和重建。
SQL Server使用DBCC SHOWCONTIG確定是否需要重建表的索引。在 SQL Server的查詢分析器中輸入命令：
Use database_name
Declare @table_id int
Set @table_id=object_id ('Employee')
Dbcc showcontig (@table_id)
在命令返回的參數(shù)中Scan Density 是索引性能的關(guān)鍵指示器，這個值越接近100％越好，一般在低于90％的情況下，就需要重建索引。重建索引可以使用DBCC DBREINDEX，使用方式如下：
dbcc dbreindex('表名', 索引名, 填充因子) /*填充因子一般為90或100*/
如果重建后，Scan Density還沒有達到100%，可以重建該表的所有索引：
dbcc dbreindex('表名', '', 填充因子)
在良好的數(shù)據(jù)庫設(shè)計基礎(chǔ)上，有效地使用索引是數(shù)據(jù)庫應(yīng)用系統(tǒng)取得高性能的基礎(chǔ)。然而，任何事物都具有兩面性，索引也不例外。索引的建立需要占用額外的存儲空間，并且在增、刪、改操作中也會增加一定的工作量，因此，在適當(dāng)?shù)牡胤皆黾舆m當(dāng)?shù)乃饕牟缓侠淼牡胤絼h除次要的索引，將有助于優(yōu)化那些性能較差的數(shù)據(jù)庫應(yīng)用系統(tǒng)。實踐表明，合理的索引設(shè)計是建立在對各種查詢的分析和預(yù)測上的，只有正確地使索引與程序結(jié)合起來,才能產(chǎn)生最佳的優(yōu)化方案
轉(zhuǎn)自：
http://www.xinxijishu.org/Article/pc/wangluo/200607/1793.html 
