第7章索引7.1索引概述7.2創建索引7.3管理索引7.4索引的應用

【技能目標】

理解索引的優缺點；學會根據實際需求設計數據庫中表的索引，提高數據檢索的速度。

【知識目標】

掌握索引的基本概念；理解索引的優點和缺點；了解索引的分類；掌握索引的創建方法和步驟；掌握管理索引的方法；掌握索引的應用。

7.1.1索引的概念

用戶對數據庫最頻繁的操作是進行數據查詢。一般情況下，數據庫在進行查詢操作時需要對整個表進行數據搜索。當表中的數據很多時，搜索數據就需要很長的時間，造成了服務器的資源浪費。為了提高檢索數據的能力，數據庫引入了索引機制。

7.1索引概述索引是一種特殊類型的數據庫對象。它保存著數據表中一列或幾列組合的排序結構。為數據表添加索引，可以大大提高數據的檢索效率。

索引是針對一個表，以表列為基礎建立的數據庫對象，是一個單獨的、物理的數據結構。索引是依賴于表建立的，表的存儲由兩部分組成，一部分用來存放表的數據頁面，另一部分存放索引頁面。通常，索引頁面相對于數據頁面來說小得多。在進行數據檢索時，系統先搜索索引頁面，從中找到所需數據的指針，再直接通過指針在數據頁面中讀取數據。7.1.2索引的作用

數據庫中的索引類似于書籍中的目錄。在圖書中，目錄是內容和相應頁碼的清單，利用目錄給出的章節頁碼可以快速查找到對應的內容，而不必一頁一頁地查找；在數據庫中，索引就是表中數據和相應存儲位置的列表，數據庫應用程序利用索引可以迅速找到表中特定的數據，而不必掃描整個數據庫。在數據表中，數據頁中保存表中的數據信息，若要查找某一數據記錄，必須在數據頁中逐個記錄查找，直到找到為止。若在數據表中創建了索引，則數據庫中就建立了索引表，索引表中包含了索引列和指針信息。利用索引表，若要查找某一數據記錄，則先由索引列找到指針值，有了指針值，再到數據表中根據指針值找到相應的數據記錄就可以了。由此可知，創建了索引之后，可以大大提高數據的檢索效率。7.1.3索引的分類

SQLServer中的索引，從屬性列的重復值、索引表的物理存儲順序以及索引列中所包含的屬性列數等不同的角度，可以劃分為以下幾類。

1.唯一索引和非唯一索引

唯一索引要求索引表中所有數據行中的任意兩行的被索引列不能有重復的值，包括NULL。如果是復合唯一索引，則可以確保索引列中的每個組合值都是唯一的。非唯一索引就沒有這個限制。

2.聚集索引和非聚集索引

1)聚集索引

聚集索引要求數據行的物理存儲順序與索引順序完全相同。只有當表中包含聚集索引時，表內的數據行才按一定順序存儲。如果表中沒有聚集索引，則表內的數據行按堆集方式存儲。每個表只能包括一個聚集索引。

2)非聚集索引

非聚集索引具有完全獨立于數據行的結構，索引不改變表中數據行的物理存儲順序。非聚集索引包含非聚集索引的鍵值和一個指針，指針指向包含該鍵值的數據行。非聚集索引不會改變數據行的物理存儲順序，因而一個表可以有多個(最多可建立249個)非聚集索引。

3.單列索引和復合索引

單列索引是指依據表中單個列建立的索引；復合索引是指依據表中兩個或兩個以上的列的組合建立的索引。其中最大列數為16，這些列的最大長度之和不能超過900字節，且必須位于同一個表中。復合索引中的列順序可以和表中的列順序不同，在復合索引中應首先定義最有可能是唯一值的列。7.1.4索引與約束的關系

對列定義PRIMARYKEY約束和UNIQUE約束時，會自動創建索引。

1.?PRIMARYKEY約束和索引

如果創建表時將一個特定的列標識為主鍵，則SQLServer2005數據庫引擎會自動為該列創建PRIMARYKEY約束和唯一聚集索引。

2.?UNIQUE約束和索引

默認情況下創建UNIQUE約束時，SQLServer2005數據庫引擎會自動為該列創建唯一非聚集索引。

當用戶從表中刪除主鍵約束或唯一約束時，創建在這些約束列上的索引也會被自動刪除。

在SQLServer2005中創建索引的方法有兩種：一種是使用SQLServerManagementStudio創建索引；另一種是使用CREATEINDEX語句創建索引。只有表或視圖的所有者才能為表創建索引。創建唯一索引時，要保證創建索引的列不包括重復的數據，并且沒有兩個或更多的空值，否則索引將不能創建成功。7.2創建索引7.2.1使用SQLServerManagementStudio創建索引

【例7-1】為SCMS數據庫的學生表student基于學號列sno創建唯一聚集索引。

具體操作步驟如下：

(1)啟動SQLServerManagementStudio。

(2)在“對象資源管理器”窗口中展開“數據庫|SCMS|表|dbo.student|索引”節點。

可以發現系統已默認依據設置的主鍵自動產生了一個聚集索引PK_student，如圖7-1所示。

說明當用戶在student表中創建主鍵時，SQLServer2005自動為該列創建PRIMARYKEY約束和唯一聚集索引。

(3)雙擊PK_student索引，

打開“索引屬性-PK_student”對話框，如圖7-2所示。可以看到該索引正是基于student表的sno列創建的唯一聚集索引。圖7-1系統默認創建的聚集索引圖7-2“索引屬性-PK_student”對話框

【例7-2】為SCMS數據庫的系表department基于系名列dname創建唯一非聚集索引，索引名稱為IX_dname_department。

具體操作步驟如下：

(1)啟動SQLServerManagementStudio。

(2)在“對象資源管理器”窗口中展開“數據庫|SCMS|表|dbo.department|索引”節點。

(3)右擊“索引”節點，在彈出的快捷菜單中選擇“新建索引”選項。打開“新建索引”對話框，在“索引名稱”文本框中輸入IX_dname_department，在“索引類型”下拉列表框中選擇“非聚集”選項，選定“唯一”復選框，如圖7-3所示。圖7-3“新建索引”對話框

(4)單擊“添加”按鈕，打開“選擇列”對話框，如圖7-4所示。在“表列”中選擇要添加到索引鍵的表列dname，單擊“確定”按鈕。

(5)在“新建索引”對話框中單擊“確定”按鈕，則一個名稱為IX_dname_department的唯一、非聚集索引創建完成，結果如圖7-5所示。圖7-4“選擇列”對話框

圖7-5創建的系名列唯一非聚集索引7.2.2使用CREATEINDEX語句創建索引

使用CREATEINDEX語句創建索引的語法格式如下：

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]

INDEXindex_name

ON{table|view}(column[ASC|DESC][,...n])

參數說明如下：

●

UNIQUE：表示創建唯一索引。視圖上的聚集索引必須是UNIQUE索引。

●

CLUSTERED：用于指定創建的索引為聚集索引。

●

NONCLUSTERED：用于指定創建的索引為非聚集索引。●

index_name：索引名。索引名必須符合標識符命名規則。

●

table：要建立索引的表的名稱。

●

view：要建立索引的視圖的名稱。

●

column：索引所基于的列。

●

[ASC|DESC]：指定索引列的排序方式。ASC為升序，DESC為降序。默認為ASC。

【例7-3】假設SCMS數據庫中創建表時沒有為授課表tc設置主鍵約束，現為其創建唯一聚集索引，索引列為教師號列tno和課程號列cno，索引名稱為IX_tnocno_tc。

USESCMS

GO

CREATEUNIQUECLUSTEREDINDEXIX_tnocno_tc

ONtc(tno,cno)

GO

【例7-4】為SCMS數據庫的學生表student創建基于姓名列sname的非聚集索引，索引名稱為IX_sname_student。

USESCMS

GO

CREATENONCLUSTEREDINDEXIX_sname_student

ONstudent(sname)

GO

7.3.1查看索引

在SQLServer2005中可以使用SQLServerManagementStudio查看索引，也可調用系統存儲過程sp_helpindex查看索引。

7.3管理索引

1.使用SQLServerManagementStudio查看索引

【例7-5】查看例7-2中為SCMS數據庫的department表創建的唯一非聚集索引IX_dname_department。

具體操作步驟如下：

(1)打開SQLServerManagementStudio。

(2)在“對象資源管理器”窗口中展開“數據庫|SCMS|表|dbo.department|索引”節點。

(3)右擊IX_dname_department節點，在彈出的快捷菜單中選擇“屬性”選項，打開“索引屬性-IX_dname_department”對話框，如圖7-6所示。

圖7-6“索引屬性-IX_dname_department”對話框

(4)單擊左側“選擇頁”的“碎片”選項，可以詳細查看IX_dname_department索引的碎片信息。通過對話框左側的“選擇頁”還可以查看索引的其他信息。

說明索引創建之后，隨著時間的推移，對數據執行插入、修改和刪除操作可能導致索引中的信息分散在數據庫中，產生碎片。碎片越多索引性能越低，導致應用程序響應越緩慢。

2.調用sp_helpindex查看索引

調用系統存儲過程sp_helpindex可以返回表的所用索引信息。語法格式如下：

sp_helpindex[@objname=]'name'

其中，[@objname=]'name'用于指定當前數據庫中的表的名稱。

【例7-6】調用sp_helpindex查看SCMS數據庫的系表department的索引。

USESCMS

GO

EXECsp_helpindexdepartment

GO

執行結果如圖7-7所示。圖7-7department表的索引信息7.3.2刪除索引

當一個索引不再需要時，可以將其從數據庫中刪除，以收回它當前使用的磁盤空間。根據索引的創建方式，要刪除的索引分為兩類：一類為創建表約束時自動創建的索引，必須先刪除PRIMARYKEY或UNIQUE約束，才能刪除約束使用的索引；另一類為通過創建索引的方式創建的獨立于約束的索引，可以利用SQLServerManagementStudio刪除，也可以使用DROPINDEX語句刪除。以下主要說明后一類索引刪除的操作過程。

1.使用SQLServerManagementStudio刪除索引

【例7-7】

刪除例7-2創建的SCMS數據庫department表的IX_dname_department

索引。

具體操作步驟如下：

(1)在“對象資源管理器”窗口中展開“數據庫|SCMS|表|dbo.department|索引”節點。

(2)右擊IX_dname_department節點，在彈出的快捷菜單中選擇“刪除”選項，打開“刪除對象”對話框。

(3)在“刪除對象”對話框中單擊“確定”按鈕，即可刪除IX_dname_department索引。

2.使用DROPINDEX語句刪除索引

使用DROPINDEX語句刪除索引的語法格式如下：

DROPINDEX<table_name>.<index_name>

參數說明如下：

●

table_name：指定要刪除索引的表的名稱。

●

index_name：指定要刪除的索引的名稱。

【例7-8】使用DROPINDEX語句刪除例7-2創建的SCMS數據庫department表的IX_dname_department索引。

USESCMS

GO

DROPINDEXdepartment.IX_dname_department

執行結果與例7-5完全相同。

【例7-9】使用DROPINDEX語句刪除SCMS數據庫的系表department的PK_department索引。

USESCMS

GO

DROPINDEXdepartment.PK_department

GO

執行結果如圖7-8所示，無法刪除創建主鍵約束時創建的索引。

說明由于PK_department索引是創建主鍵約束時自動創建的，所以無法利用DROPINDEX語句刪除。圖7-8刪除由主鍵約束創建的索引

索引的建立需