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

CREATENONCLUSTEREDINDEXIX_zyxmON

學生信息(dept_id,stu_name)GO

使用系統存儲過程sp_helpindex查看學生信息表的索引情況。

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

EXECsp_helpindex學生信息

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

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

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

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

DROPINDEXtable_name.index_name[,…]

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

1.顯示碎片信息當往表中添加或從表中刪除數據行以及索引的值發生改變時，SQLServer將調整索引頁維護索引數據的存儲。頁拆分時會產生碎片，使用DBCCSHOWCONTIG命令，可以顯示指定的表或視圖的數據和索引的碎片信息。【例6.7】顯示“學生信息”表索引標識為IX_xm索引的碎片統計信息。語法如下：USEjxglGODBCCSHOWCONTIG(學生信息,IX_xm)GO6.3.4索引的分析與維護2.索引的分析

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

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

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

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

WHERECONTAINS(teacher_research,'*數據庫*')6.4.2使用T-SQL創建使用全文索引

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