數(shù)據(jù)庫(kù)基礎(chǔ)PPT第6章 索引及其應(yīng)用

第6章索引及其應(yīng)用6.1索引概述6.2創(chuàng)建索引6.3管理和維護(hù)索引6.4全文索引Page129十月2023教學(xué)要求：通過(guò)本章學(xué)習(xí)，讀者應(yīng)掌握以下內(nèi)容：索引的概念和功能；使用對(duì)象資源管理器和T-SQL命令兩種方式創(chuàng)建、修改、刪除索引的方法；全文索引的定義與使用。Page229十月20236.1索引概述索引是一個(gè)列表，這個(gè)列表中包含了某個(gè)表中一列或者若干列的集合，以及這些值的記錄在數(shù)據(jù)表中存儲(chǔ)位置的物理地址。6.1.1索引的功能使用索引可以大大提高系統(tǒng)的性能，其具體表現(xiàn)在：(1)加快數(shù)據(jù)查詢(2)加快表的連接、排序和分組工作(3)索引能提高WHERE語(yǔ)句提取數(shù)據(jù)的速度，也能提高更新和刪除數(shù)據(jù)記錄的速度。(4)可以確保數(shù)據(jù)的唯一性。Page329十月20236.1.2創(chuàng)建索引的原則建立索引的一般原則是：（1）對(duì)經(jīng)常用來(lái)搜索數(shù)據(jù)記錄的字段建立索引。（2）對(duì)表中的主鍵字段建立索引。（3）對(duì)表中的外鍵字段建立索引。（4）對(duì)在查詢中用來(lái)連接表的字段建立索引。（5）對(duì)經(jīng)常用來(lái)作為排序基準(zhǔn)的字段建立索引。Page429十月20236.1.3索引的分類從不同的角度，對(duì)索引的類型有不同的劃分方法。按存儲(chǔ)結(jié)構(gòu)區(qū)分，有聚集索引和非聚集索引；按數(shù)據(jù)的惟一性來(lái)區(qū)分，有惟一索引和非惟一索引；按鍵列的個(gè)數(shù)區(qū)分，有單列索引和多列索引。1.聚集索引和非聚集索引聚集索引（ClusteredIndex）對(duì)表在物理數(shù)據(jù)頁(yè)中的數(shù)據(jù)按列進(jìn)行排序，然后再重新存儲(chǔ)到磁盤上。由于表中的數(shù)據(jù)行只能以一種排序方式存儲(chǔ)在磁盤上，所以一個(gè)表只能有一個(gè)聚集索引。當(dāng)建立主鍵約束時(shí)，如果表中沒有聚集索引，SQL會(huì)用主鍵作為聚集索引。Page529十月2023與聚集索引不同的是，非聚集索引（NonClusteredIndex）盡管包含按升序排列的鍵值，但它絲毫不影響表中數(shù)據(jù)記錄實(shí)際排列的順序。當(dāng)針對(duì)表執(zhí)行以下操作時(shí)，SQL會(huì)自動(dòng)重建此表所有現(xiàn)存的非聚集索引：（1）將表的聚集索引刪除。（2）為表創(chuàng)建一個(gè)聚集索引。（3）更改聚集索引的鍵列。所以在創(chuàng)建非聚集索引之前，應(yīng)先創(chuàng)建聚集索引。創(chuàng)建了聚集索引的表上執(zhí)行查詢操作比只創(chuàng)建了非聚集索引的表上執(zhí)行查詢速度快，但是，執(zhí)行修改操作則比只創(chuàng)建了非聚集索引的表上執(zhí)行的速度慢，這是因?yàn)楸頂?shù)據(jù)的改變需要更多的時(shí)間來(lái)維護(hù)聚集索引。一個(gè)表最多能夠擁有249個(gè)非聚集索引。Page629十月20232．惟一索引和非惟一索引惟一索引要求所有數(shù)據(jù)行中任意兩行中的被索引列或索引列組合不能存在重復(fù)值，包括不能有兩個(gè)空值NULL，而非惟一索引(NonUniqueIndex)則不存在這樣的限制。聚集索引和非聚集索引都可以是一個(gè)惟一索引或非惟一索引。3．單列索引和多列索引單列索引是指為某單一字段創(chuàng)建索引；多列索引則是為多個(gè)字段的組合創(chuàng)建索引。多列索引也叫復(fù)合索引，適用以下幾種情況：（1）當(dāng)兩個(gè)或兩個(gè)以上的字段組合在一起為最佳的搜索鍵值時(shí)，就非常適合為這些字段的組合創(chuàng)建一個(gè)多列索引。（2）當(dāng)查詢所引用的字段均是索引的鍵列時(shí)，應(yīng)該為這些字段的組合創(chuàng)建一個(gè)多列索引。覆蓋查詢是一個(gè)最典型的例子。一個(gè)多列索引中最多可以有16個(gè)字段組合，并且多列索引中的所有字段必須在同一個(gè)表中。Page729十月20234．全文索引全文索引是Microsoft全文引擎(Full-textIndex)創(chuàng)建并管理的一種特殊類型的基于標(biāo)記的功能性索引。由MicrosoftSQLServer全文引擎(MSFTESQL)服務(wù)創(chuàng)建和維護(hù)，可以大大提高從字符串中搜索數(shù)據(jù)的速度，用于幫助用戶在字符串?dāng)?shù)據(jù)中搜索復(fù)雜的詞。Page829十月20236.2創(chuàng)建索引6.2.1系統(tǒng)自動(dòng)創(chuàng)建索引在創(chuàng)建或修改表時(shí)，如果添加了一個(gè)主鍵或惟一鍵約束，則系統(tǒng)將自動(dòng)在該表上，以該鍵值作為索引列，創(chuàng)建一個(gè)惟一索引。該索引是聚集索引還是非聚集索引，要根據(jù)當(dāng)前表中的索引狀況和約束語(yǔ)句或命令而定。Page929十月20236.2.2在SQLServerManagementStudio下創(chuàng)建索引6.2.3使用CREATEINDEX語(yǔ)句創(chuàng)建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON表名(字段名[,...n])[WITH[索引選項(xiàng)[,...n]]][ON文件組]其中各參數(shù)的含義如下。(1)UNIQUE：建立惟一索引。CLUSTERED：建立聚集索引。NONCLUSTERED：建立非聚集索引。(2)table_name/view_name。用于指定創(chuàng)建的索引的表或視圖名稱。(3)[PAD_INDEX]。用于指定索引中間級(jí)中每個(gè)頁(yè)（節(jié)占）上保持開放的空間。(4)ONfilegroup。用于指定存放索引的文件組，使用創(chuàng)建索引向志給表創(chuàng)建索引。Page1029十月2023【例6.3】使用CREATEINDEX語(yǔ)句，在“學(xué)生信息”表的“dept_id”列和“stu_name”列上創(chuàng)建名為“IX_zyxm”的非聚集、復(fù)合索引。運(yùn)行如下命令。

CREATENONCLUSTEREDINDEXIX_zyxmON

學(xué)生信息(dept_id,stu_name)GO

使用系統(tǒng)存儲(chǔ)過(guò)程sp_helpindex查看學(xué)生信息表的索引情況。

EXECsp_helpindex學(xué)生信息Page1129十月2023用戶在創(chuàng)建和使用惟一索引時(shí)應(yīng)注意如下事項(xiàng)。（1）在建有聚集惟一索引的表上，執(zhí)行INSERT語(yǔ)句或UPDATE語(yǔ)句時(shí)，SQLServer將自動(dòng)檢驗(yàn)新的數(shù)據(jù)中是否存在重復(fù)值。如果存在的話，當(dāng)創(chuàng)建索引的語(yǔ)句指定了IGNORE_DUP_KEY選項(xiàng)時(shí)，SQLServer將發(fā)出警告消息并忽略重復(fù)的行。如果沒有為索引指定IGNORE_DUP_KEY，SQLServer會(huì)發(fā)出一條警告消息，并回滾整個(gè)INSERT語(yǔ)句。（2）具有相同組合列、不同組合順序的復(fù)合索引彼此是不同的。（3）如果表中已有數(shù)據(jù)，那么在創(chuàng)建惟一索引時(shí)，SQLServer將自動(dòng)檢驗(yàn)是否存在重復(fù)的值，若有重復(fù)值，則不能創(chuàng)建惟一索引。Page1229十月20236.3管理和維護(hù)索引6.3.1查看和維護(hù)索引信息查看表的索引信息可以使用sp_helpindex系統(tǒng)存儲(chǔ)過(guò)程，例如查看“學(xué)生信息”表的索引信息使用以下的語(yǔ)句。

EXECsp_helpindex學(xué)生信息

在SQLServerManagementStudio的“對(duì)象資源管理器”中，依次展開到表的“索引”項(xiàng)，可以查看或修改已建索引。注意：創(chuàng)建和修改聚集索引時(shí)，SQLServer要在磁盤上對(duì)表進(jìn)行重組，當(dāng)表中存儲(chǔ)了大量記錄時(shí)，會(huì)產(chǎn)生很大的系統(tǒng)開銷，花費(fèi)的時(shí)間可能會(huì)較長(zhǎng)。Page1329十月20236.3.2更改索引標(biāo)識(shí)

可以使用系統(tǒng)存儲(chǔ)過(guò)程sp_rename更改索引標(biāo)識(shí)名稱，語(yǔ)法格式如下：sp_renametable_name.OldName,NewName[,object_type]

其中，table_name是索引所在的表的名字，OldName是要重命名的索引名稱，NewName是新的索引名稱?！纠?.5】更改“學(xué)生信息”表中的索引標(biāo)識(shí)IX_zyxm為IDX_dept_name。命令如下USEjxglGOEXECsp_rename'學(xué)生信息.IX_zyxm','IDX_dept_name'GO6.3.3刪除索引

刪除索引可以在SQLServerManagementStudio的“對(duì)象資源管理器”中完成或用DROPINDEX命令完成。用DROPINDEX命令刪除索引的格式如下

DROPINDEXtable_name.index_name[,…]

【例6.6】用DROPINDEX命令刪除“學(xué)生信息”表中的“IDX_dept_name”索引。運(yùn)行如下命令。USEjxglGODROPINDEX學(xué)生信息.IDX_dept_nameGO用DROPINDEX命令刪除索引時(shí)，需要注意如下事項(xiàng)。（1）不能用DROPINDEX命令刪除由PRIMARYKEY約束或UNIQUE約束創(chuàng)建的索引。這些索引必須通過(guò)刪除PRIMARYKEY約束或UNIQUE約束，由系統(tǒng)自動(dòng)刪除。（2）在刪除聚集索引時(shí)，表中的所有非聚集索引都將被重建。Page1629十月20236.3.4索引的分析與維護(hù)

1.顯示碎片信息當(dāng)往表中添加或從表中刪除數(shù)據(jù)行以及索引的值發(fā)生改變時(shí)，SQLServer將調(diào)整索引頁(yè)維護(hù)索引數(shù)據(jù)的存儲(chǔ)。頁(yè)拆分時(shí)會(huì)產(chǎn)生碎片，使用DBCCSHOWCONTIG命令，可以顯示指定的表或視圖的數(shù)據(jù)和索引的碎片信息。【例6.7】顯示“學(xué)生信息”表索引標(biāo)識(shí)為IX_xm索引的碎片統(tǒng)計(jì)信息。語(yǔ)法如下：USEjxglGODBCCSHOWCONTIG(學(xué)生信息,IX_xm)GO6.3.4索引的分析與維護(hù)2.索引的分析

SQLServer內(nèi)部存在一個(gè)查詢優(yōu)化器，如何進(jìn)行數(shù)據(jù)查詢,查詢是否使用索引等都是由查詢優(yōu)化器決定的。（1）顯示查詢計(jì)劃【例6.8】執(zhí)行學(xué)生成績(jī)的查詢，顯示執(zhí)行計(jì)劃執(zhí)行下面的查詢語(yǔ)句。SELECT*FROM學(xué)生信息AINNERJOIN成績(jī)BONA.stu_id=B.stu_idGO然后，單擊“查詢”|“顯示估計(jì)執(zhí)行計(jì)劃”命令，完成顯示執(zhí)行計(jì)劃的設(shè)置。Page1829十月2023以表格方式顯示計(jì)劃設(shè)置是否顯示查詢計(jì)劃的命令如下:SETSHOWPLAN_ALLON|OFF或SETSHOWPLAN_TEXTON|OFF【例6.9】執(zhí)行學(xué)生成績(jī)的查詢，以表格方式顯示查詢計(jì)劃。SETSHOWPLAN_TEXTONSELECT*FROM學(xué)生信息AINNERJOIN成績(jī)BONA.stu_id=B.stu_id（2）數(shù)據(jù)I/O統(tǒng)計(jì)設(shè)置是否顯示磁盤I/O統(tǒng)計(jì)的命令為：SETSTATISTICSIOON|OFF【例6.10】執(zhí)行學(xué)生成績(jī)的查詢，以表格的方式顯示執(zhí)行計(jì)劃。

SETSHOWPLAN_TEXTON--打開計(jì)劃顯示GOSELECT*FROM學(xué)生信息AINNERJOIN成績(jī)BONA.stu_id=B.stu_idGOSETSTATISTICSIOOFF--關(guān)閉I/O統(tǒng)計(jì)Page2029十月20233.重新組織索引重新組織索引是重新進(jìn)行物理排序，從而對(duì)表或視圖的聚集索引和非聚集索引進(jìn)行碎片整理，提高索引掃描的性能。【例6.11】重新組織“學(xué)生信息”表上的索引PK_學(xué)生信息，語(yǔ)句如下。USEjxglGOALTERINDEXPK_學(xué)生信息ON學(xué)生信息REORGANIZEGOPage2129十月20236.4全文索引

全文索引技術(shù)是目前搜索引擎的關(guān)鍵技術(shù)。全文索引包含在全文目錄中。每個(gè)數(shù)據(jù)庫(kù)可以包含一個(gè)或多個(gè)全文目錄。一個(gè)目錄不能屬于多個(gè)數(shù)據(jù)庫(kù)，而每個(gè)目錄可以包含一個(gè)或多個(gè)表的全文索引。一個(gè)表只能有一個(gè)全文索引，因此每個(gè)有全文索引的表只屬于一個(gè)全文目錄。全文索引必須在基本表上定義，而不能在視圖、系統(tǒng)表或臨時(shí)表上定義。普通SQL索引全文索引存儲(chǔ)時(shí)受定義它們所在的數(shù)據(jù)庫(kù)的控制存儲(chǔ)在文件系統(tǒng)中，但通過(guò)數(shù)據(jù)庫(kù)管理每個(gè)表允許有若干個(gè)普通索引每個(gè)表只允許有一個(gè)全文索引當(dāng)對(duì)作為其基礎(chǔ)的數(shù)據(jù)進(jìn)行插入、更新或刪除時(shí)，它們會(huì)自動(dòng)更新將數(shù)據(jù)添加到全文索引稱為填充，全文索引可通過(guò)調(diào)度或特定請(qǐng)求來(lái)請(qǐng)求，也可以在添加新數(shù)據(jù)時(shí)自動(dòng)發(fā)生不分組在同一個(gè)數(shù)據(jù)庫(kù)內(nèi)分為一個(gè)或多個(gè)全文目錄使用SQLServer對(duì)象資源管理器、向?qū)Щ騎-SQL語(yǔ)句創(chuàng)建和刪除使用SQLServer對(duì)象資源管理器、向?qū)Щ虼鎯?chǔ)過(guò)程創(chuàng)建、管理和刪除全文索引和普通索引的區(qū)別在SQLServer數(shù)據(jù)庫(kù)中使用全文索引需要以下步驟：(1)啟動(dòng)數(shù)據(jù)庫(kù)的全文處理功能（sp_fulltext_database）。(2)建立全文目錄（sp_fulltext_catalog）。(3)在全文目錄中注冊(cè)需要全文索引的表（sp_fulltext_table）。(4)指出表中需要全文索引的列名（sp_fulltext_column）。(5)為表創(chuàng)建全文索引（sp_fulltext_table）。(6)填充全文目錄（sp_fulltext_catalog）。6.4.1使用SSMS創(chuàng)建全文索引

步驟如下：1.允許數(shù)據(jù)庫(kù)使用全文索引2.創(chuàng)建全文目錄3.查看和修改全文目錄4.創(chuàng)建全文索引5.使用全文搜索查詢使用全文搜索查詢命令格式：SELECTcolumn_listFROMtable_nameWHERECONTAINS(column_name|*,'search_condition')【例6.12】在教師信息表中搜索teacher_research列中包含“數(shù)據(jù)庫(kù)”的記錄。SELECT*FROM教師信息

WHERECONTAINS(teacher_research,'*數(shù)據(jù)庫(kù)*')6.4.2使用T-SQL創(chuàng)建使用全文索引

1.啟用數(shù)據(jù)庫(kù)的全文索引Sp_fulltext_databaseenable--啟用數(shù)據(jù)庫(kù)的全文索引GO2.建立全文目錄（創(chuàng)建full_extcatalog）命令格式如下：CREATEFULLTEXTCATALOGcatalog_name[ONFILEGROUPfilegroup][INPATH‘rootpath’][WITH<catalog_option>][ASDEFAULT][AUTHORIZATIONowner_name]其中：<catalog_option>::=ACCENT_SENSITIVITY={ON|OFF}其中參數(shù)說(shuō)明如下：(1)catalog_nameo為全文目錄名稱。(2)ONFILEGROUPfilegroup為包含全文目錄的文件組名。(3)INPATH‘rootpath’為全文目錄的路徑。ASDEFAULT為指定該全文目錄為默認(rèn)目錄。【例6.13】在jxgl數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名為teachers_FT的全文目錄，其代碼如下：CREATEFULLTEXTCATALOGteachers_FTONFILEGROUP[PRIMARY]INPATH'D:\data\'ASDEFAULT3.建立全文索引有了全文目錄后，可以在全文目錄里創(chuàng)建全文索引。創(chuàng)建全文索引的T-SQL語(yǔ)句格式如下：CREATEFULLTEXTINDEXONtable_name[(column_name[TYPECOLUMNtype_column_name][LANGUAGElanguags_term][,…n])]KEYINDEXindex_name[ONfulltext_catalog_name][WITH{CHANGE_TRACKING{MANUAL|AUTO|OFF[,ONPOPULATION]}}]其中參數(shù)說(shuō)明如下：(1)