SQL Server數(shù)據(jù)庫(kù)及應(yīng)用（第二版）課件：索引的創(chuàng)建與使用

索引的創(chuàng)建與使用本章學(xué)習(xí)目標(biāo)理解索引的概念了解索引的優(yōu)點(diǎn)掌握索引的分類熟練掌握創(chuàng)建、修改和刪除索引的方法7.1概述7.1.1索引的概念索引是根據(jù)表中一列或若干列，按照一定順序建立的列值與記錄行之間的對(duì)應(yīng)關(guān)系表。在列上創(chuàng)建了索引之后，查找數(shù)據(jù)時(shí)可以直接根據(jù)該列上的索引找到對(duì)應(yīng)行的位置，從而快速地查找到數(shù)據(jù)。SQLServer將索引組織為B樹(shù)。每個(gè)索引行都包含一個(gè)鍵值以及一個(gè)指向較低級(jí)頁(yè)或數(shù)據(jù)行的指針。7.1.2索引的優(yōu)點(diǎn)使用索引可以大大提高系統(tǒng)的性能，其具體表現(xiàn)在：加快數(shù)據(jù)檢索速度。可以保證數(shù)據(jù)記錄的唯一性。在使用ORDERBY和GROUPBY子句進(jìn)行檢索數(shù)據(jù)時(shí)，可以顯著減少查詢中分組和排序的時(shí)間?？梢栽诓樵兊倪^(guò)程中使用優(yōu)化隱藏器提高系統(tǒng)性能?？梢约铀俦砼c表之間的連接。7.1.3索引的分類在SQLServer2008中提供的索引類型主要有以下幾類：聚集索引非聚集索引唯一索引包含性列索引索引視圖全文索引XML索引7.1.3索引的分類1．聚集索引聚集索引是指表中數(shù)據(jù)行的物理存儲(chǔ)順序與索引列順序完全相同。聚集索引由上下兩層結(jié)構(gòu)組成，上層為索引頁(yè)，用于數(shù)據(jù)檢索；下層為數(shù)據(jù)頁(yè)，存放著表中的數(shù)據(jù)。當(dāng)為一個(gè)表的某列創(chuàng)建聚集索引時(shí)，表中的數(shù)據(jù)會(huì)根據(jù)索引列的順序再進(jìn)行重新排序，然后再存儲(chǔ)到磁盤(pán)上。因此，每個(gè)表只能創(chuàng)建一個(gè)聚集索引。聚集索引一般創(chuàng)建于表中經(jīng)常檢索的列或者按順序訪問(wèn)的列上。7.1.3索引的分類2．非聚集索引非聚集索引不改變表中數(shù)據(jù)行的物理存儲(chǔ)位置，數(shù)據(jù)與索引分開(kāi)存儲(chǔ)，通過(guò)索引帶有的指針與表中的數(shù)據(jù)發(fā)生聯(lián)系。一個(gè)表中可有一個(gè)或多個(gè)非聚集索引。一個(gè)表中既要?jiǎng)?chuàng)建聚集索引，又要?jiǎng)?chuàng)建非聚集索引時(shí)，應(yīng)先創(chuàng)建聚集索引，然后再創(chuàng)建非聚集索引。7.1.3索引的分類3．唯一索引如果要求索引中的字段值不能重復(fù)，可以建立唯一索引。索引也可以不是唯一的，即多個(gè)行可以共享同一鍵值。如果索引是根據(jù)多列組合創(chuàng)建的，這樣的索引稱為復(fù)合索引。聚集索引和非聚集索引都可以是唯一索引。7.1.3索引的分類4．幾種特殊類型的索引（1）XML索引XML索引必須創(chuàng)建在表的XML列上，先創(chuàng)建一個(gè)XML主索引，然后才能創(chuàng)建多個(gè)輔助XML索引。（2）篩選索引篩選索引是一種經(jīng)過(guò)優(yōu)化的非聚集索引，適用于從表中選擇少數(shù)行的查詢。（3）空間索引空間索引定義在一個(gè)包含地理數(shù)據(jù)的表列上。7.2創(chuàng)建索引在SQLServer2009中，創(chuàng)建索引的常用方法有以下幾種：由系統(tǒng)自動(dòng)創(chuàng)建使用SSMS創(chuàng)建使用T-SQL語(yǔ)句創(chuàng)建7.2創(chuàng)建索引系統(tǒng)在創(chuàng)建表中的其他對(duì)象時(shí)可以附帶地創(chuàng)建新索引，例如新建表時(shí)，如果創(chuàng)建主鍵或者唯一性約束，系統(tǒng)會(huì)自動(dòng)創(chuàng)建相應(yīng)的索引。例7-1：在AWLT數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)新表ProductCategory1，并將其中的Name字段設(shè)置為聚集的唯一性約束。例7-2：在AWLT數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)新表Address1，并將其中的AddressID字段設(shè)置為主鍵。7.2.1使用SSMS創(chuàng)建索引方法一：（1）在SSMS中，展開(kāi)指定的服務(wù)器和數(shù)據(jù)庫(kù)，展開(kāi)要?jiǎng)?chuàng)建索引的表，并右擊其中的“索引”文件夾，從彈出的快捷菜單中選擇“新建索引”選項(xiàng)。（2）在打開(kāi)的“新建索引”對(duì)話框中，輸入索引名為，在索引類型旁邊的下拉列表框中選擇索引類型，并單擊“添加”按鈕，打開(kāi)“選擇列”對(duì)話框，在其中選中相應(yīng)字段前面的復(fù)選框。如果是復(fù)合索引，可以在“選擇列”對(duì)話框中同時(shí)勾選多個(gè)字段前面的復(fù)選框。7.2.1使用SSMS創(chuàng)建索引方法二：進(jìn)入表結(jié)構(gòu)的設(shè)計(jì)窗口，在窗口中右擊鼠標(biāo)，從快捷菜單中選擇“索引/鍵”，會(huì)打開(kāi)“索引/鍵”對(duì)話框?？梢蕴砑有滤饕騽h除已有的索引。7.2.1使用SSMS創(chuàng)建索引例7-3：在AWLT數(shù)據(jù)庫(kù)中，為表Customer創(chuàng)建一個(gè)唯一、非聚集索引IX_Customer_EmaiAddress，索引字段為EmaiAddress。例7-4：在AWLT數(shù)據(jù)庫(kù)中，為表Customer創(chuàng)建一個(gè)唯一、非聚集索引IX_Customer_Name，索引字段為Name。7.2.2使用T-SQL語(yǔ)句創(chuàng)建索引使用TSQL中的CREATEINDEX語(yǔ)句可以創(chuàng)建索引：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameONtable|view（column[ASC|DESC][,...n]）7.2.2使用T-SQL語(yǔ)句創(chuàng)建索引其中，各參數(shù)的說(shuō)明如下：UNIQUE：指定為表或視圖創(chuàng)建唯一索引。CLUSTERED：指定創(chuàng)建的索引為聚集索引。NONCLUSTERED：指定創(chuàng)建的索引為非聚集索引。index_name：指定所創(chuàng)建的索引名稱。table：指定創(chuàng)建索引的表名稱。view：指定創(chuàng)建索引的視圖名稱。ASC|DESC：指定某個(gè)具體索引列的升序或降序排序方向。默認(rèn)值為升序（ASC）。Column：指定索引所基于的一列或多列。7.2.2使用T-SQL語(yǔ)句創(chuàng)建索引例7-5：在AWLT數(shù)據(jù)庫(kù)中，使用CREATEINDEX語(yǔ)句為表Product創(chuàng)建一個(gè)名為IX_Product_Name的非聚集索引，索引字段為Name。例7-6：在AWLT數(shù)據(jù)庫(kù)中，使用CREATEINDEX語(yǔ)句為表Product創(chuàng)建一個(gè)名為IX_Product_SellStartDate_Color的復(fù)合索引，索引字段為SellStartDate字段和Color字段，按SellStartDate字段降序，Color字段升序。7.3查看索引7.3.1使用SSMS查看索引方法一：（1）在SSMS中，展開(kāi)指定的服務(wù)器、數(shù)據(jù)庫(kù)和創(chuàng)建索引的表，并單擊其中的“索引”，可以顯示出該表上的所有索引。（2）要想查看某個(gè)索引的詳細(xì)信息，在“索引屬性”對(duì)話框中可以查看索引的詳細(xì)信息。（3）在“對(duì)象依賴關(guān)系”對(duì)話框中可以查看該索引依賴的對(duì)象和依賴于該索引的對(duì)象。7.3.1使用SSMS查看索引方法二：進(jìn)入表結(jié)構(gòu)設(shè)計(jì)窗口，在窗口中右擊鼠標(biāo)，從快捷菜單中選擇“索引/鍵”，在“索引/鍵”對(duì)話框中，可以看到已經(jīng)創(chuàng)建的索引，在“選定的主/唯一鍵或索引”中選擇要查看的索引的名稱，在右側(cè)就可以看到選中的索引的詳細(xì)信息。7.3.2使用系統(tǒng)存儲(chǔ)過(guò)程查看索引信息系統(tǒng)存儲(chǔ)過(guò)程sp_helpindex可以返回表的所有索引信息，其語(yǔ)法格式如下：sp_helpindex[@objname=]’name’例7-7：使用系統(tǒng)存儲(chǔ)過(guò)程查看Customer表的索引信息。7.4

修改索引在對(duì)表創(chuàng)建了索引之后，可以根據(jù)實(shí)際需要修改已有的索引，修改索引的常用方法主要有兩種：使用SSMS使用T-SQL語(yǔ)句7.4.1

使用SSMS修改索引在SSMS中，進(jìn)入表結(jié)構(gòu)的設(shè)計(jì)窗口，在窗口中右擊鼠標(biāo)，從快捷菜單中選擇“索引/鍵”，在“索引/鍵”對(duì)話框中，可以看到已經(jīng)創(chuàng)建的索引，在“選定的主/唯一鍵或索引”中選擇要修改的索引的名稱，在右側(cè)就可以修改選中的索引的信息。7.4.2

使用T-SQL語(yǔ)句修改索引使用T-SQL中的ALTERINDEX語(yǔ)句可以修改索引。該語(yǔ)句不能用于修改索引定義，如添加或刪除列，或更改列的順序。如果需要修改索引的組成，要么刪除索引后重新創(chuàng)建索引，要么使用帶DROP_EXISTING=ON選項(xiàng)的索引。7.5刪除索引使用索引可以提高查詢效率，但是如果一個(gè)表上索引過(guò)多，則當(dāng)修改表中的記錄時(shí)會(huì)增加服務(wù)器維護(hù)索引的時(shí)間。刪除索引常用的操作方法有兩種：使用SSMS使用T-SQL語(yǔ)句7.5.1使用SSMS刪除索引方法一：在SSMS中，展開(kāi)指定的服務(wù)器、數(shù)據(jù)庫(kù)、查看索引所在的表和要查看的索引，并右擊需要?jiǎng)h除的索引名稱，從彈出的快捷菜單中選擇“刪除”命令，在“刪除對(duì)象”對(duì)話框中單擊“確定”按鈕，即可刪除選定的索引。7.5.1使用SSMS刪除索引方法二：進(jìn)入表結(jié)構(gòu)的設(shè)計(jì)窗口，在窗口中右擊鼠標(biāo)，從快捷菜單中選擇“索引/鍵”，在“索引/鍵”對(duì)話框中，可以看到已經(jīng)創(chuàng)建的索引，在“選定的主/唯一鍵或索引”中選擇要?jiǎng)h除的索引的名稱，單擊“刪除”按鈕，即可刪除選中的索引。7.5.2使用T-SQL語(yǔ)句刪除索引可以使用DROPINDEX語(yǔ)句刪除一個(gè)或者多個(gè)當(dāng)前數(shù)據(jù)庫(kù)中的索引：DROPINDEXtable_name.index_name|view_name.index_name[,...n]其中，table|view用于指定索引列所在的表或索引視圖；index用于指定要?jiǎng)h除的索引名稱。7.5.2使用T-SQL語(yǔ)句刪除索引注意：DROPINDEX命令不能刪除由CREATETABLE或者ALTERTABLE語(yǔ)句創(chuàng)建的主鍵或者唯一性約束索引。例7-8：刪除表Customer中的名為IX_Customer_Name的索引。本章小結(jié)索引是根據(jù)表中一列或若干列，按照一定順序建立的列值與記錄行之間的對(duì)應(yīng)關(guān)系表。使用所以可以在表中快速地查找到數(shù)據(jù)。在SQLServer2008中提供的索引類型主要有：聚集索引、非聚集索引、唯一索引、包含性列索引、索引視圖、全文索引、篩選索引以及XML索引。其中最常用的是聚集索引、非聚集索引和唯一索引。7.6本章小結(jié)聚集索引是指表中數(shù)據(jù)行的物理存儲(chǔ)順序與索引列順序完全相同。每個(gè)表只能創(chuàng)建一個(gè)聚集索引。非聚集索引不改變表中數(shù)據(jù)行的物理存儲(chǔ)位置，數(shù)據(jù)與索引分開(kāi)存儲(chǔ)，通過(guò)索引帶有的指針與表中的數(shù)據(jù)發(fā)生聯(lián)系。當(dāng)唯一性是數(shù)據(jù)本身應(yīng)考慮的特點(diǎn)時(shí)，可創(chuàng)建唯一索引。7.6本章小結(jié)創(chuàng)建索引的方法系統(tǒng)在創(chuàng)建表中的主鍵或者唯一性約束時(shí)可以附帶地創(chuàng)建新索引使用SSMS使用T-SQL中的CREATEINDEX語(yǔ)句查看索引的方法使用SSMS使用系統(tǒng)存儲(chǔ)過(guò)程sp_helpindex7.6本章小結(jié)刪除索引的方法使用SSMS使用T-SQL中的DROPINDEX語(yǔ)句習(xí)題七一、填空題1．使用索引可以大大加快

速度。2.在SQLServer中，通常不需要用戶建立索引，而是通過(guò)使用________約束和________約束，由系統(tǒng)自動(dòng)建立索引。3．按照存儲(chǔ)結(jié)構(gòu)的不同，索引分為

和

。4．索引可以提高SELECT語(yǔ)句中ORDERBY子句和

子句的執(zhí)行速度。習(xí)題七二、選擇題1．“CREATEUNIQUEINDEXAAAON學(xué)生表（學(xué)號(hào)）”將在學(xué)生表上創(chuàng)建名為AAA的（

）。A．唯一索引B．聚集索引

C．復(fù)合索引D．唯一聚集索引2．下面對(duì)索引的相關(guān)描述正確的是（

）。A．經(jīng)常被查詢的列不適合建索引

B．列值唯一的列適合建索引C．有很多重復(fù)值的列適合建索引

D．是外鍵或主鍵的列不適合建索引習(xí)題七二、選擇題3．下面關(guān)于聚集索引和非聚集索引說(shuō)法正確的是（

）。A．每個(gè)表只能建立一個(gè)聚集索引

B．非聚集索引需要較多的硬盤(pán)空間和內(nèi)存

C．一張表上不能同時(shí)建立聚集索引和非聚集索引