《數(shù)據(jù)庫(kù)原理及應(yīng)用》課件第7章

第7章索引7.1索引概述7.2創(chuàng)建索引7.3管理索引7.4索引的應(yīng)用

【技能目標(biāo)】

理解索引的優(yōu)缺點(diǎn)；學(xué)會(huì)根據(jù)實(shí)際需求設(shè)計(jì)數(shù)據(jù)庫(kù)中表的索引，提高數(shù)據(jù)檢索的速度。

【知識(shí)目標(biāo)】

掌握索引的基本概念；理解索引的優(yōu)點(diǎn)和缺點(diǎn)；了解索引的分類；掌握索引的創(chuàng)建方法和步驟；掌握管理索引的方法；掌握索引的應(yīng)用。

7.1.1索引的概念

用戶對(duì)數(shù)據(jù)庫(kù)最頻繁的操作是進(jìn)行數(shù)據(jù)查詢。一般情況下，數(shù)據(jù)庫(kù)在進(jìn)行查詢操作時(shí)需要對(duì)整個(gè)表進(jìn)行數(shù)據(jù)搜索。當(dāng)表中的數(shù)據(jù)很多時(shí)，搜索數(shù)據(jù)就需要很長(zhǎng)的時(shí)間，造成了服務(wù)器的資源浪費(fèi)。為了提高檢索數(shù)據(jù)的能力，數(shù)據(jù)庫(kù)引入了索引機(jī)制。

7.1索引概述索引是一種特殊類型的數(shù)據(jù)庫(kù)對(duì)象。它保存著數(shù)據(jù)表中一列或幾列組合的排序結(jié)構(gòu)。為數(shù)據(jù)表添加索引，可以大大提高數(shù)據(jù)的檢索效率。

索引是針對(duì)一個(gè)表，以表列為基礎(chǔ)建立的數(shù)據(jù)庫(kù)對(duì)象，是一個(gè)單獨(dú)的、物理的數(shù)據(jù)結(jié)構(gòu)。索引是依賴于表建立的，表的存儲(chǔ)由兩部分組成，一部分用來存放表的數(shù)據(jù)頁(yè)面，另一部分存放索引頁(yè)面。通常，索引頁(yè)面相對(duì)于數(shù)據(jù)頁(yè)面來說小得多。在進(jìn)行數(shù)據(jù)檢索時(shí)，系統(tǒng)先搜索索引頁(yè)面，從中找到所需數(shù)據(jù)的指針，再直接通過指針在數(shù)據(jù)頁(yè)面中讀取數(shù)據(jù)。7.1.2索引的作用

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

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

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

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

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

1)聚集索引

聚集索引要求數(shù)據(jù)行的物理存儲(chǔ)順序與索引順序完全相同。只有當(dāng)表中包含聚集索引時(shí)，表內(nèi)的數(shù)據(jù)行才按一定順序存儲(chǔ)。如果表中沒有聚集索引，則表內(nèi)的數(shù)據(jù)行按堆集方式存儲(chǔ)。每個(gè)表只能包括一個(gè)聚集索引。

2)非聚集索引

非聚集索引具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)，索引不改變表中數(shù)據(jù)行的物理存儲(chǔ)順序。非聚集索引包含非聚集索引的鍵值和一個(gè)指針，指針指向包含該鍵值的數(shù)據(jù)行。非聚集索引不會(huì)改變數(shù)據(jù)行的物理存儲(chǔ)順序，因而一個(gè)表可以有多個(gè)(最多可建立249個(gè))非聚集索引。

3.單列索引和復(fù)合索引

單列索引是指依據(jù)表中單個(gè)列建立的索引；復(fù)合索引是指依據(jù)表中兩個(gè)或兩個(gè)以上的列的組合建立的索引。其中最大列數(shù)為16，這些列的最大長(zhǎng)度之和不能超過900字節(jié)，且必須位于同一個(gè)表中。復(fù)合索引中的列順序可以和表中的列順序不同，在復(fù)合索引中應(yīng)首先定義最有可能是唯一值的列。7.1.4索引與約束的關(guān)系

對(duì)列定義PRIMARYKEY約束和UNIQUE約束時(shí)，會(huì)自動(dòng)創(chuàng)建索引。

1.?PRIMARYKEY約束和索引

如果創(chuàng)建表時(shí)將一個(gè)特定的列標(biāo)識(shí)為主鍵，則SQLServer2005數(shù)據(jù)庫(kù)引擎會(huì)自動(dòng)為該列創(chuàng)建PRIMARYKEY約束和唯一聚集索引。

2.?UNIQUE約束和索引

默認(rèn)情況下創(chuàng)建UNIQUE約束時(shí)，SQLServer2005數(shù)據(jù)庫(kù)引擎會(huì)自動(dòng)為該列創(chuàng)建唯一非聚集索引。

當(dāng)用戶從表中刪除主鍵約束或唯一約束時(shí)，創(chuàng)建在這些約束列上的索引也會(huì)被自動(dòng)刪除。

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

【例7-1】為SCMS數(shù)據(jù)庫(kù)的學(xué)生表student基于學(xué)號(hào)列sno創(chuàng)建唯一聚集索引。

具體操作步驟如下：

(1)啟動(dòng)SQLServerManagementStudio。

(2)在“對(duì)象資源管理器”窗口中展開“數(shù)據(jù)庫(kù)|SCMS|表|dbo.student|索引”節(jié)點(diǎn)。

可以發(fā)現(xiàn)系統(tǒng)已默認(rèn)依據(jù)設(shè)置的主鍵自動(dòng)產(chǎn)生了一個(gè)聚集索引PK_student，如圖7-1所示。

說明當(dāng)用戶在student表中創(chuàng)建主鍵時(shí)，SQLServer2005自動(dòng)為該列創(chuàng)建PRIMARYKEY約束和唯一聚集索引。

(3)雙擊PK_student索引，

打開“索引屬性-PK_student”對(duì)話框，如圖7-2所示?？梢钥吹皆撍饕腔趕tudent表的sno列創(chuàng)建的唯一聚集索引。圖7-1系統(tǒng)默認(rèn)創(chuàng)建的聚集索引圖7-2“索引屬性-PK_student”對(duì)話框

【例7-2】為SCMS數(shù)據(jù)庫(kù)的系表department基于系名列dname創(chuàng)建唯一非聚集索引，索引名稱為IX_dname_department。

具體操作步驟如下：

(1)啟動(dòng)SQLServerManagementStudio。

(2)在“對(duì)象資源管理器”窗口中展開“數(shù)據(jù)庫(kù)|SCMS|表|dbo.department|索引”節(jié)點(diǎn)。

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

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

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

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

使用CREATEINDEX語句創(chuàng)建索引的語法格式如下：

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]

INDEXindex_name

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

參數(shù)說明如下：

●

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

●

CLUSTERED：用于指定創(chuàng)建的索引為聚集索引。

●

NONCLUSTERED：用于指定創(chuàng)建的索引為非聚集索引?！?/p>

index_name：索引名。索引名必須符合標(biāo)識(shí)符命名規(guī)則。

●

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

●

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

●

column：索引所基于的列。

●

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

【例7-3】假設(shè)SCMS數(shù)據(jù)庫(kù)中創(chuàng)建表時(shí)沒有為授課表tc設(shè)置主鍵約束，現(xiàn)為其創(chuàng)建唯一聚集索引，索引列為教師號(hào)列tno和課程號(hào)列cno，索引名稱為IX_tnocno_tc。

USESCMS

GO

CREATEUNIQUECLUSTEREDINDEXIX_tnocno_tc

ONtc(tno,cno)

GO

【例7-4】為SCMS數(shù)據(jù)庫(kù)的學(xué)生表student創(chuàng)建基于姓名列sname的非聚集索引，索引名稱為IX_sname_student。

USESCMS

GO

CREATENONCLUSTEREDINDEXIX_sname_student

ONstudent(sname)

GO

7.3.1查看索引

在SQLServer2005中可以使用SQLServerManagementStudio查看索引，也可調(diào)用系統(tǒng)存儲(chǔ)過程sp_helpindex查看索引。

7.3管理索引

1.使用SQLServerManagementStudio查看索引

【例7-5】查看例7-2中為SCMS數(shù)據(jù)庫(kù)的department表創(chuàng)建的唯一非聚集索引IX_dname_department。

具體操作步驟如下：

(1)打開SQLServerManagementStudio。

(2)在“對(duì)象資源管理器”窗口中展開“數(shù)據(jù)庫(kù)|SCMS|表|dbo.department|索引”節(jié)點(diǎn)。

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

圖7-6“索引屬性-IX_dname_department”對(duì)話框

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

說明索引創(chuàng)建之后，隨著時(shí)間的推移，對(duì)數(shù)據(jù)執(zhí)行插入、修改和刪除操作可能導(dǎo)致索引中的信息分散在數(shù)據(jù)庫(kù)中，產(chǎn)生碎片。碎片越多索引性能越低，導(dǎo)致應(yīng)用程序響應(yīng)越緩慢。

2.調(diào)用sp_helpindex查看索引

調(diào)用系統(tǒng)存儲(chǔ)過程sp_helpindex可以返回表的所用索引信息。語法格式如下：

sp_helpindex[@objname=]'name'

其中，[@objname=]'name'用于指定當(dāng)前數(shù)據(jù)庫(kù)中的表的名稱。

【例7-6】調(diào)用sp_helpindex查看SCMS數(shù)據(jù)庫(kù)的系表department的索引。

USESCMS

GO

EXECsp_helpindexdepartment

GO

執(zhí)行結(jié)果如圖7-7所示。圖7-7department表的索引信息7.3.2刪除索引

當(dāng)一個(gè)索引不再需要時(shí)，可以將其從數(shù)據(jù)庫(kù)中刪除，以收回它當(dāng)前使用的磁盤空間。根據(jù)索引的創(chuàng)建方式，要?jiǎng)h除的索引分為兩類：一類為創(chuàng)建表約束時(shí)自動(dòng)創(chuàng)建的索引，必須先刪除PRIMARYKEY或UNIQUE約束，才能刪除約束使用的索引；另一類為通過創(chuàng)建索引的方式創(chuàng)建的獨(dú)立于約束的索引，可以利用SQLServerManagementStudio刪除，也可以使用DROPINDEX語句刪除。以下主要說明后一類索引刪除的操作過程。

1.使用SQLServerManagementStudio刪除索引

【例7-7】

刪除例7-2創(chuàng)建的SCMS數(shù)據(jù)庫(kù)department表的IX_dname_department

索引。

具體操作步驟如下：

(1)在“對(duì)象資源管理器”窗口中展開“數(shù)據(jù)庫(kù)|SCMS|表|dbo.department|索引”節(jié)點(diǎn)。

(2)右擊IX_dname_department節(jié)點(diǎn)，在彈出的快捷菜單中選擇“刪除”選項(xiàng)，打開“刪除對(duì)象”對(duì)話框。

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

2.使用DROPINDEX語句刪除索引

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

DROPINDEX<table_name>.<index_name>

參數(shù)說明如下：

●

table_name：指定要?jiǎng)h除索引的表的名稱。

●

index_name：指定要?jiǎng)h除的索引的名稱。

【例7-8】使用DROPINDEX語句刪除例7-2創(chuàng)建的SCMS數(shù)據(jù)庫(kù)department表的IX_dname_department索引。

USESCMS

GO

DROPINDEXdepartment.IX_dname_department

執(zhí)行結(jié)果與例7-5完全相同。

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

USESCMS

GO

DROPINDEXdepartment.PK_department

GO

執(zhí)行結(jié)果如圖7-8所示，無法刪除創(chuàng)建主鍵約束時(shí)創(chuàng)建的索引。

說明由于PK_department索引是創(chuàng)建主鍵約束時(shí)自動(dòng)創(chuàng)建的，所以無法利用DROPINDEX語句刪除。圖7-8刪除由主鍵約束創(chuàng)建的索引

索引的建立需