SQL-Server實(shí)用數(shù)據(jù)庫技術(shù)課件第7章

本章內(nèi)容索引的含義和優(yōu)、缺點(diǎn)、設(shè)計(jì)原則索引的類型創(chuàng)建索引刪除索引技能展示：了解索引的含義、優(yōu)缺點(diǎn)及設(shè)計(jì)原則掌握索引的分類掌握索引的創(chuàng)建與刪除的方法掌握查看與修改索引

開封大學(xué)信息工程學(xué)院7.1SQL索引的含義和優(yōu)、缺點(diǎn)、設(shè)計(jì)原則索引概述索引的優(yōu)缺點(diǎn)索引的設(shè)計(jì)原則7.1.1.索引的含義索引是一個(gè)在表或視圖上創(chuàng)建的對(duì)象，是對(duì)表或視圖的一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)

，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。表與索引示例DepartmentNO（部門號(hào)）Department（部門）Phone（電話）1105人事處0378-32001111104科研處0378-32001153106學(xué)報(bào)0378-32021162107財(cái)務(wù)處0378-32031562108招生就業(yè)處0378-32031574109保衛(wèi)處0378-32061514110基建處0378-32051612111北校區(qū)綜合辦0378-32052663101財(cái)政經(jīng)濟(jì)學(xué)引編號(hào)指針地址110421105121074210852111831019310634109641107使用索引優(yōu)點(diǎn)索引在數(shù)據(jù)庫中的作用類似于目錄在書籍中的作用，用來提高查找信息的速度。使用索引查找數(shù)據(jù)，無需對(duì)整表進(jìn)行掃描，可以快速找到所需數(shù)據(jù)。使用索引的缺點(diǎn)索引需要占用數(shù)據(jù)表以外的物理存儲(chǔ)空間。創(chuàng)建索引和維護(hù)索引要花費(fèi)一定的時(shí)間。當(dāng)對(duì)表進(jìn)行更新操作時(shí)，索引需要被重建，這樣降低了數(shù)據(jù)的維護(hù)速度。7.1.2.索引的優(yōu)缺點(diǎn)可以在這些列上創(chuàng)建索引

在經(jīng)常需要搜索的列上；在作為主鍵的列上；在經(jīng)常用在連接的列上，這些列主要是一些外鍵；在經(jīng)常需要排序的列上創(chuàng)建索引；在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引；7.1.3.索引設(shè)計(jì)的原則

一般不應(yīng)該在具有下列特點(diǎn)的列上創(chuàng)建索引在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引；只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引；定義為text,image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引；7.1.3.索引設(shè)計(jì)的原則

開封大學(xué)信息工程學(xué)院7.2索引的類型聚集索引和非聚集索引唯一索引和非唯一索引簡(jiǎn)單索引和復(fù)合索引索引是在數(shù)據(jù)庫表或者視圖上創(chuàng)建的對(duì)象，目的是為了加快對(duì)表或視圖的查詢的速度

按存儲(chǔ)方式分聚集索引（clustered）非聚集索引（nonclustered）按維護(hù)和管理角度分唯一索引和非唯一索引簡(jiǎn)單索引和復(fù)合索引7.2索引的分類聚集索引（ClusteredIndex）對(duì)表的數(shù)據(jù)按列進(jìn)行排序，然后再重新存儲(chǔ)到磁盤上，即聚集索引與數(shù)據(jù)是混為一體的。

7.2.1聚集索引和非聚集索引

聚集索引（續(xù)）聚集索引的一些事實(shí)行的物理位置和行在索引中的位置是相同的每個(gè)表只能有一個(gè)聚集索引建聚集索引需要至少相當(dāng)該表120%的附加空間

系部代碼系部名稱系主任05社會(huì)科學(xué)部劉克忠02經(jīng)濟(jì)管理系劉國峰08建筑系王未起04基礎(chǔ)科學(xué)部王彬03傳播技術(shù)系田建國07農(nóng)林系陳瑞修06機(jī)電工程系王偉東01計(jì)算機(jī)系劉海軍非聚集索引（NonclusteredIndex）具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)，數(shù)據(jù)行不按非聚集索引鍵的順序排序和存儲(chǔ)。每個(gè)索引行包含聚集索引鍵和行定位器。7.2.1聚集索引和非聚集索引

非聚集索引系部代碼指針地址018022035044051067076083系部代碼系部名稱系主任05社會(huì)科學(xué)部劉克忠02經(jīng)濟(jì)管理系劉國峰08建筑系王未起04基礎(chǔ)科學(xué)部王彬03傳播技術(shù)系田建國07農(nóng)林系陳瑞修06機(jī)電工程系王偉東01計(jì)算機(jī)系劉海軍“系部代碼”索引“系部”表(a)(b)非聚集索引的一些事實(shí)和指導(dǎo)原則若未指定索引類型，則默認(rèn)為非聚集索引索引的邏輯順序與表的數(shù)據(jù)行的物理存儲(chǔ)順序次序不同每個(gè)表最多可以有249個(gè)非聚集索引在非聚集索引創(chuàng)建之前創(chuàng)建聚集索引非聚集索引（續(xù)）唯一索引：唯一索引是索引列值不能重復(fù)的索引，唯一索引不允許兩行具有相同的索引值非唯一索引：非惟一索引是索引列值可以重復(fù)的索引

無論是唯一索引還是非唯一，索引列都不允許NULL。

7.2.2唯一索引和非唯一索引簡(jiǎn)單索引：?jiǎn)我凰饕侵杆饕袨橐涣械那闆r,即新建索引的語句只實(shí)施在一列上。通常應(yīng)對(duì)每個(gè)基本表的主關(guān)鍵字建立簡(jiǎn)單索引。

復(fù)合索引：對(duì)基本表中兩個(gè)或兩個(gè)以上列建立索引，這種索引叫做復(fù)合索引(組合索引)。7.2.3簡(jiǎn)單索引和復(fù)合索引

7.3創(chuàng)建索引開封大學(xué)信息工程學(xué)院使用SSMS創(chuàng)建索引使用SQL語句創(chuàng)建索引7.3.1使用SQLServerManagementStudio創(chuàng)建索引

（1）在SQLServerManagementStudio中，連接到包含默認(rèn)的數(shù)據(jù)庫的服務(wù)器實(shí)例。（2）在【對(duì)象資源管理器】中，展開【服務(wù)器】|【數(shù)據(jù)庫】|【build】|【表】|【T_Facility】節(jié)點(diǎn)，右擊【索引】節(jié)點(diǎn)，在彈出的菜單中選擇【新建索引】命令。（3）在【新建索引】窗口的【常規(guī)】頁面，可以配置索引的名稱、選擇索引的類型、是否是唯一索引等，如圖7-1所示。7.3.1使用SQLServerManagementStudio創(chuàng)建索引

（4）單擊【添加】按鈕，打開【從“dboT_Facility”中選擇列】窗口，在窗口中的【表列】列表中t選中【FacilityName】復(fù)選框，如圖7-2所示。（5）單擊【確定】按鈕，返回【新建索引】窗口，然后再單擊【新建索引】窗口的【確定】按鈕，【索引】節(jié)點(diǎn)下便生成了一個(gè)名“cls_facility”的索引，說明該索引創(chuàng)建成功。CREATE

[UNIQUE][CLUSTERED|NONCLUSTERED]

INDEX

index_nameON{table|view}(column[ASC|DESC][,...n])[WITH[PAD_INDEX][[,]FILLFACTOR=fillfactor][[,]IGNORE_DUP_KEY][[,]DROP_EXISTING][[,]STATISTICS_NORECOMPUTE][[,]SORT_IN_TEMPDB]][ONfilegroup]7.3.2使用使用SQL語句創(chuàng)建索引創(chuàng)建索引

語法格式：說明(1)：UNIQUE：創(chuàng)建一個(gè)惟一索引，即索引的鍵值不重復(fù)。在列包含重復(fù)值時(shí)，不能建惟一索引。如要使用此選項(xiàng)，則應(yīng)確定索引所包含的列均不允許NULL值，否則在使用時(shí)會(huì)經(jīng)常出錯(cuò)。CLUSTERED：指明創(chuàng)建的索引為聚集索引。如果此選項(xiàng)缺省，則創(chuàng)建的索引為非聚集索引。NONCLUSTERED：指明創(chuàng)建的索引為非聚集索引。index_name：指定所創(chuàng)建的索引的名稱索引名稱。在一個(gè)表中應(yīng)是惟一的，但在同一數(shù)據(jù)庫或不同數(shù)據(jù)庫中可以重復(fù)。說明(2)：table|view：指定創(chuàng)建索引的表名稱或視圖名稱。必要時(shí)還應(yīng)指明數(shù)據(jù)庫名稱和所有者名稱。索引如果建在視圖上，視圖必須是使用SCHEMABINDING選項(xiàng)定義過的。ASC|DESC：指定特定的索引列的排序方式。默認(rèn)值是升序ASC。Column：指定被索引的列。如果使用兩個(gè)或兩個(gè)以上的列，組成一個(gè)索引則稱為復(fù)合索引。一個(gè)索引中最多可以指定16個(gè)列，但列的數(shù)據(jù)類型的長度和不能超過900個(gè)字節(jié)。說明(2)：PAD_INDEX：指定填充索引的內(nèi)部節(jié)點(diǎn)的行數(shù)至少應(yīng)大于等于兩行。PAD_INDEX選項(xiàng)只有在FILLFACTOR選項(xiàng)指定后才起作用，因?yàn)镻AD_INDEX使用與FILLFACTOR相同的百分比。FILLFACTOR=fillfactor：FILLFACTOR稱為填充因子，它指定創(chuàng)建索引時(shí)每個(gè)索引頁的數(shù)據(jù)占索引頁大小的百分比。FILLFACTOR的值為1到100。對(duì)于那些頻繁進(jìn)行大量數(shù)據(jù)插入或刪除的表，在建索引時(shí)應(yīng)該為將來生成的索引數(shù)據(jù)預(yù)留較大的空間，即將FILLFACTOR設(shè)得較小，否則索引頁會(huì)因數(shù)據(jù)的插入而很快填滿并產(chǎn)生分頁，而分頁會(huì)大大增加系統(tǒng)的開銷。但如果設(shè)得過小又會(huì)浪費(fèi)大量的磁盤空間，降低查詢性能。因此對(duì)于此類表通常設(shè)一個(gè)大約為10的FILLFACTOR。說明(3)：IGNORE_DUP_KEY：此選項(xiàng)控制了當(dāng)往包含于一個(gè)惟一約束中的列中插入重復(fù)數(shù)據(jù)時(shí)，SQLServer所作的反應(yīng)。當(dāng)選擇此選項(xiàng)時(shí)，SQLServer返回一個(gè)錯(cuò)誤信息，跳過此行數(shù)據(jù)的插入，繼續(xù)執(zhí)行下面的插入數(shù)據(jù)的操作。當(dāng)沒選擇此選項(xiàng)時(shí)，SQLServer不僅會(huì)返回一個(gè)錯(cuò)誤信息，而且還不會(huì)完成數(shù)據(jù)的插入DROP_EXISTING：用于指定應(yīng)刪除并重新創(chuàng)建已命名的先前存在的聚集索引或者非聚集索引。。

SORT_IN_TEMPDB：指定用于創(chuàng)建索引的分類排序結(jié)果，將被存儲(chǔ)到Tempdb數(shù)據(jù)庫中。如果Tempdb數(shù)據(jù)庫和用戶數(shù)據(jù)庫位于不同的磁盤設(shè)備上，那么使用這一選項(xiàng)可以減少創(chuàng)建索引的時(shí)間，但它會(huì)增加創(chuàng)建索引所需的磁盤空間。ONfilegroup：指定存放索引的文件組。USEbuildGOCREATEUNIQUECLUSTEREDINDEXDEX_NO_DEPOND_Department(DepartmentNO)WITHPAD_INDEX,FILLFACTOR=10【例7-1】為表“D_Department”基于“部門號(hào)”字段創(chuàng)建一個(gè)唯一的聚集索,FILLFACTOR設(shè)置為10。

USEbuildGOCREATEUNIQUECLUSTEREDINDEXDEX_NO_NAME_DEPOND_Department(DepartmentNO,Department)WITHPAD_INDEX,FILLFACTOR=20【例7-2】為表“D_Department”基于“部門號(hào)”和“部門名稱”字段創(chuàng)建一個(gè)非聚集索引,FILLFACTOR設(shè)置為20

7.4刪除索引開封大學(xué)信息工程學(xué)院使用SSMS刪除索引

使用DROPINDEX刪除索引

7.4.1使用SQLServerManagementStudio刪除索引

在SQLServerManagementStudio的【對(duì)象資源管理器】中，展開【服務(wù)器】|【數(shù)據(jù)庫】|【build】|【表】|【T_Facility】節(jié)點(diǎn)，單擊【索引】節(jié)點(diǎn)，右擊所要?jiǎng)h除的索引，從彈出的快捷菜單中選擇“刪除”命令，即可刪除索引。7.4.2使用SQL語句刪除索引刪除索引的語法非常類似于刪除表的語法，如下所示DROPINDEX<tableorviewname>.<indexname>或DROPINDEX<indexname>ON<tableorviewname>DROPINDEXD_Department.DEX_NO_DEPDROPINDEXDEX_NO_DEPOND_Department【例】將D_Department表中的DEX_NO_DEP索引刪除。

使用DROPINDEX將索引刪除，可以使用如下兩句：當(dāng)執(zhí)行DROPINDEX語句時(shí)，SQLServer釋放被該索引所占的磁盤空間。不能使用DROPINDEX語句刪除由主鍵約束或唯一性約束創(chuàng)建的索引。要想刪除這些索引，必須先刪除這些約束。當(dāng)刪除表時(shí)，該表全部索引也將被刪除。當(dāng)刪除一個(gè)聚集索引時(shí)，該表的全部非聚集索引重新自動(dòng)創(chuàng)建。不能在系統(tǒng)表上使用DROPINDEX語句。在刪除索引時(shí)，要注意下面的一些情況7.5.1使用SSMS查看、修改索引在SQLServerManagementStudio的【對(duì)象資源管理器】中，展開【服務(wù)器】|【數(shù)據(jù)庫】|【build】|【表】，在所要查看索引的表上打開索引，在建好的索引上右擊，從彈出的快捷菜單中選擇“屬性”命令，如圖所示。7.5查看與修改索引7.5.2用存儲(chǔ)過程sp_helpindex查看索引

利用系統(tǒng)提供的存儲(chǔ)過程sp_helpindex可以查看索引信息，其語法格式如下：sp_helpindex[@objname=]‘object_name’,

其中，[@objname=]‘object_name’表示所要查看的當(dāng)前數(shù)據(jù)庫中表的名稱。E