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

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

CREATENONCLUSTEREDINDEXIX_zyxmON

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

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

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

EXECsp_helpindex學(xué)生信息

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

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

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

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

DROPINDEXtable_name.index_name[,…]

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

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

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

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

全文索引技術(shù)是目前搜索引擎的關(guān)鍵技術(shù)。全文索引包含在全文目錄中。每個數(shù)據(jù)庫可以包含一個或多個全文目錄。一個目錄不能屬于多個數(shù)據(jù)庫，而每個目錄可以包含一個或多個表的全文索引。一個表只能有一個全文索引，因此每個有全文索引的表只屬于一個全文目錄。全文索引必須在基本表上定義，而不能在視圖、系統(tǒng)表或臨時表上定義。普通SQL索引全文索引存儲時受定義它們所在的數(shù)據(jù)庫的控制存儲在文件系統(tǒng)中，但通過數(shù)據(jù)庫管理每個表允許有若干個普通索引每個表只允許有一個全文索引當對作為其基礎(chǔ)的數(shù)據(jù)進行插入、更新或刪除時，它們會自動更新將數(shù)據(jù)添加到全文索引稱為填充，全文索引可通過調(diào)度或特定請求來請求，也可以在添加新數(shù)據(jù)時自動發(fā)生不分組在同一個數(shù)據(jù)庫內(nèi)分為一個或多個全文目錄使用SQLServer對象資源管理器、向?qū)Щ騎-SQL語句創(chuàng)建和刪除使用SQLServer對象資源管理器、向?qū)Щ虼鎯^程創(chuàng)建、管理和刪除全文索引和普通索引的區(qū)別在SQLServer數(shù)據(jù)庫中使用全文索引需要以下步驟：(1)啟動數(shù)據(jù)庫的全文處理功能（sp_fulltext_database）。(2)建立全文目錄（sp_fulltext_catalog）。(3)在全文目錄中注冊需要全文索引的表（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ù)庫使用全文索引2.創(chuàng)建全文目錄3.查看和修改全文目錄4.創(chuàng)建全文索引5.使用全文搜索查詢使用全文搜索查詢命令格式：SELECTcolumn_listFROMtable_nameWHERECONTAINS(column_name|*,'search_condition')【例6.12】在教師信息表中搜索teacher_research列中包含“數(shù)據(jù)庫”的記錄。SELECT*FROM教師信息

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

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