1 索引的概念

索引是一個單獨的、物理的數據庫結構，它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。表的存儲由兩部分組成，一部分用來存放數據頁面，另一部分存放索引頁面。通常，索引頁面相對于數據頁面來說小得多。數據檢索花費的大部分開銷是磁盤讀寫，沒有索引就需要從磁盤上讀表的每一個數據頁，如果有索引，則只需查找索引頁面就可以了。所以建立合理的索引，就能加速數據的檢索過程。

SQL Server采用B-樹結構的索引，根據索引的順序與數據表的物理順序是否相同可以分為：聚簇索引（clustered index）和非聚簇索引（nonclustered index）。

（1）聚簇索引重新組織表中的數據以按指定的一個或多個列的值排序。聚簇索引的葉節點包含實際的數據，因此用它查找數據很快，但每個表只能建一個聚簇索引。

（2）非聚簇索引不重新組織表中的數據，它的葉節點中存儲了組成非聚簇索引的列的值和行定位指針。一個表可以建249 個非聚簇索引。

通俗的說，漢語字典的正文就是一個建立在拼音基礎上的聚簇索引，以英文字母“a”開頭并以“z”結尾。比如，我們要查“阿”字，就會翻開字典的第一頁，因為“阿”的拼音是“a”，所以排在字典的前面。如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那么就說明字典中沒有這個字。同樣的，如果查“做”字，就會把字典翻到最后。

字典的“偏旁部首”是非聚簇索引。比如我們要查“阿”字，在查部首之后，看到部首檢字表中“阿”的頁碼是1頁，“阿”的上面是“際”字，但頁碼卻是277頁，“阿”的下面是“隴”字，頁碼是416頁。很顯然，這些字并不是真正的分別位于“阿”字的上下方，現在看到的連續的“際、阿、隴”三字實際上就是他們在非聚簇索引中的排序，是字典正文中的字在非聚簇索引中的映射。

2 索引的使用

1）聚簇索引的使用

在聚簇索引下，數據在物理上按順序排在數據頁上，重復值也排在一起，因而在那些包含范圍檢查(between、<、<=、>、>=)或使用group by、order by的查詢時，一旦找到具有范圍中第一個鍵值的行，具有后續索引值的行必然連在一起，不必進一步搜索，避免了大范圍掃描，可以大大提高查詢速度。

聚簇索引的侯選列是：

u??????? 經常按范圍存取的列，如date>”20050101” and date< “20050131”；

u??????? 經常在where子句中使用并且插入是隨機的主鍵列；

u??????? 在group by或order by中使用的列；

u??????? 在連接操作中使用的列。

2）非聚簇索引的使用

由于非聚簇索引的葉級點不包含實際的數據，因此它檢索效率較低，但一個表只能建一個聚簇索引，當用戶需要建立多個索引時就需要使用非聚簇索引了。在建立非聚簇索引時，要權衡索引對查詢速度的加快與降低修改速度之間的利弊。

在下面情況中使用非聚簇索引：

u??????? 常用于集合函數(如Sum,....)的列；

u??????? 常用于join, order by, group by的列；

u??????? 查尋出的數據不超過表中數據量的20%。

3）創建索引需要注意的要點

1)??? 慎重選擇作為聚簇索引的列

默認情況下，SQL Server用主鍵創建聚簇索引。這種做法常常造成聚簇索引的浪費。通常，我們會為每個表建立一個ID列，以區分每條數據，并且該列是自動增大的，步長一般為1。如果我們把這個列設為主鍵，SQL Server會將此列默認為聚簇索引。這樣做可以使數據在數據庫中按ID進行物理排序，但這種做法在實際應用中意義并不大。根據前面談到的聚簇索引的定義和使用情況可以看出，使用聚簇索引的最大好處就是能夠根據查詢要求，迅速返回某個范圍內的數據，避免全表掃描。在實際應用中，因為ID號是自動生成的，我們并不知道每條記錄的ID號，所以我們不太可能用ID號來進行查詢。這就使聚簇索引成為擺設，造成資源浪費。其次，讓每個值都不同的ID列作為聚簇索引也不符合“大數目的不同值情況下不應建立聚簇索引”規則。

一般情況下，數據庫應用系統進行數據檢索都離不開“ 用戶名（代碼）”、“日期”字段。以筆者所用的HIS系統（醫院管理信息系統）為例，我們進行費用、處方、檢查單等信息檢索時需要根據“住院號”和“日期”這兩個字段來返回特定范圍內的數據。下面我們分幾種情況觀察在不同索引條件下查詢相同內容所用的時間。

假設病人費用表名為“brfy”，其中住院號字段名為“zyh”，日期字段名為“riqi”，要求是從表brfy中檢索zyh為“028246”的病人2005年3月1日到20日的費用，對應的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秒。

由以上分析可以看出聚簇索引是非常寶貴的，應該為經常用于檢索某個范圍內數據的列或group by、order by等子句的列建立聚簇索引，這樣能夠極大的提高系統性能。

2)??? 重視以多個列創建的索引中列的順序問題

一些用戶認為只要合理的選擇列建立索引，不必關心列的順序就可以提高檢索速度，這種觀點是錯誤的。多列索引中列的先后順序應該和實際應用中where、group by或order by等子句里列的放置位置相同。參考上面舉的例子，在第二、第三種情況下，如果把riqi放在zyh前面，執行上述SQL語句就不會用到這兩個索引，檢索的時間也會變得很長。

3 索引的維護

數據庫系統運行一段時間后，隨著數據行的插入、刪除和數據頁的分裂，索引對系統的優化性能就會大大降低。這時候，我們需要對索引進行分析和重建。

SQL Server使用DBCC SHOWCONTIG確定是否需要重建表的索引。在 SQL Server的查詢分析器中輸入命令：

Use database_name

Declare @table_id int

Set @table_id=object_id ('Employee')

Dbcc showcontig (@table_id)

在命令返回的參數中Scan Density 是索引性能的關鍵指示器，這個值越接近100％越好，一般在低于90％的情況下，就需要重建索引。重建索引可以使用DBCC DBREINDEX，使用方式如下：

dbcc dbreindex('表名', 索引名, 填充因子)?????? /*填充因子一般為90或100*/

如果重建后，Scan Density還沒有達到100%，可以重建該表的所有索引：

dbcc dbreindex('表名', '', 填充因子)

在良好的數據庫設計基礎上，有效地使用索引是數據庫應用系統取得高性能的基礎。然而，任何事物都具有兩面性，索引也不例外。索引的建立需要占用額外的存儲空間，并且在增、刪、改操作中也會增加一定的工作量，因此，在適當的地方增加適當的索引并從不合理的地方刪除次要的索引，將有助于優化那些性能較差的數據庫應用系統。實踐表明，合理的索引設計是建立在對各種查詢的分析和預測上的，只有正確地使索引與程序結合起來,才能產生最佳的優化方案。

1.創建表并插入數據

在Sql Server2008中創建測試數據庫Test，接著創建數據庫表并插入數據，sql代碼如下：

USE Test IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'emp_pay')DROP TABLE emp_pay GO USE Test IF EXISTS (SELECT name FROM sys.indexes WHERE name = 'employeeID_ind')DROP INDEX emp_pay.employeeID_ind GO USE Test GO CREATE TABLE emp_pay (employeeID int NOT NULL,base_pay money NOT NULL,commission decimal(2, 2) NOT NULL ) INSERT emp_payVALUES (1, 500, .10) INSERT emp_pay VALUES (2, 1000, .05) INSERT emp_pay VALUES (6, 800, .07) INSERT emp_payVALUES (5, 1500, .03) INSERT emp_payVALUES (9, 750, .06)

執行完上述sql代碼以后我們會發現在Test數據庫中多出了一張emp_pay表，數據庫表的內容如下圖所示：

2.無索引查找

從上圖我們可以看出數據庫中存儲的數據排列順序與我們插入的先后順序一致。接下來我們查詢employeeID=5的字段，執行如下sql代碼：

USE Test SELECT * FROM emp_pay where employeeID=5

在SQL SERVER MANAGEMENT STUDIO中我們點擊“顯示估計的查詢計劃”，會出現如下圖所示的查詢計劃圖：

其中表掃描的內容為：

3.創建索引

接下來我們為上述表添加聚集唯一索引，代碼如下：

SET NOCOUNT OFF CREATE UNIQUE CLUSTERED INDEX employeeID_indON emp_pay (employeeID) GO

在執行完上述創建索引的代碼以后，我們再次查詢emp_pay的數據內容，如下圖所示：

從上圖我們可以發現數據內容已經按照employeeID進行了排序。

我們繼續執行前面關于employeeID=5的查詢，點擊“顯示估計的執行計劃”，出現如下圖所示內容：

聚集索引查找的內容為：

總結：

當我們為數據庫表中的某一個字段創建索引，并且在查詢語句中where子句中用到這樣一個字段，那么查詢效率會有所提高，我們上述實驗因為數據量的關系查詢效率提高不明顯。

補充

我們上面添加的索引是唯一聚集索引，因此當插入的數據在employeeID字段出現重復時會報錯。假如我們在創建索引之前數據字段出現重復，那么就不能創建唯一索引。

創建索引以后的排序（PS:2012-5-28）

執行如下sql語句

update emp_pay set employeeID=7 where employeeID=1;

然后再次執行全表查詢，我們發現查詢結果如下所示：

只要我們更新了employeeID，那么最后的更新結果都會按照employeeID的值進行升序排序。這是因為我們在employeeID上創建了索引的緣故。

刪除索引（PS:2012-6-4）

我們可以通過sql server management studio這個工具刪除索引，也可以通過sql語句進行索引的刪除，假設我們要求刪除在前面創建的索引employeeID_ind，那么sql語句如下代碼所示：

DROP INDEX employeeID_ind ON emp_pay;

總結

以上是生活随笔為你收集整理的索引的概念和创建索引例子的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。