SQL Server 網(wǎng)絡(luò)數(shù)據(jù)庫管理項(xiàng)目教程課件8項(xiàng)目八 索引及其應(yīng)用

項(xiàng)目八:索引及其應(yīng)用索引的概念創(chuàng)建索引索引的刪除任務(wù)一任務(wù)二任務(wù)三索引及其應(yīng)用任務(wù)四索引的優(yōu)化8.1任務(wù)1:索引的概念索引及其應(yīng)用索引是與表關(guān)聯(lián)的頁的集合，用于提高查詢的性能或增強(qiáng)唯一性。如果把數(shù)據(jù)庫表看做一本書，則表的索引就如同書的目錄一樣，通過索引可以大大提高查詢的速度。常用的關(guān)系數(shù)據(jù)庫如SQLServer、Sybase、Oracle、DB2等，為了提高性能，都提供了相應(yīng)的索引機(jī)制。8.1.1相關(guān)知識索引的概念使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。索引是對數(shù)據(jù)庫表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。在關(guān)系數(shù)據(jù)庫中，索引是一種與表有關(guān)的數(shù)據(jù)庫結(jié)構(gòu)，它可以使對應(yīng)于表的SQL語句執(zhí)行得更快。索引的作用相當(dāng)于圖書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。當(dāng)表中有大量記錄時(shí)，若要對表進(jìn)行查詢，第一種搜索信息方式是全表搜索，是將所有記錄一一取出，和查詢條件進(jìn)行一一對比，然后返回滿足條件的記錄，這樣做會消耗大量數(shù)據(jù)庫系統(tǒng)時(shí)間，并造成大量磁盤I/O操作；第二種就是在表中建立索引，然后在索引中找到符合查詢條件的索引值，最后通過保存在索引中的ROWID（相當(dāng)于頁碼）快速找到表中對應(yīng)的記錄。如圖8?1所示：（1）索引的概念相關(guān)知識索引的概念（1）索引的概念圖8-1SQL-Server訪問數(shù)據(jù)方式相關(guān)知識索引的概念在SQLServer2008中，從物理結(jié)構(gòu)上主要有兩種常用索引，包括聚集索引、非聚集索引。其中：聚集索引：聚集索引將數(shù)據(jù)行的鍵值在表內(nèi)排序并存儲對應(yīng)的數(shù)據(jù)記錄，使得數(shù)據(jù)表物理順序與索引順序一致，當(dāng)以某字段作為關(guān)鍵字建立聚集索引時(shí)，表中數(shù)據(jù)以該字段作為排序根據(jù)。因此一張表只能建立一個(gè)聚集索引。如果表中沒有創(chuàng)建其他的聚集索引，則會在表的主鍵上自動創(chuàng)建聚集索引。（2）索引的分類圖8-2聚集索引相關(guān)知識索引的概念非聚集索引：非聚集索引并不物理上改變表中數(shù)據(jù)行的排列，即索引中的邏輯順序并不等同于表中數(shù)據(jù)行的物理順序，索引僅記錄指向表中數(shù)據(jù)行位置的指針。因此，在非聚集索引中，數(shù)據(jù)和索引是分開存儲的，索引的葉級由索引頁，而不是數(shù)據(jù)頁組成。一個(gè)表最多可以有249個(gè)非聚集索引，無論表是否使用聚集索引，都可以對表定義非聚集索引。（2）索引的分類圖8-3非聚集索引相關(guān)知識索引的概念唯一性索引：一個(gè)唯一索引，不允許具有索引值相同的行，從而禁止重復(fù)的索引或鍵值。系統(tǒng)在創(chuàng)建該索引時(shí)檢查是否有重復(fù)的鍵值，并在每次使用INSERT或UPDATE語句添加數(shù)據(jù)時(shí)進(jìn)行檢查，聚集索引和非聚集索引都可以是唯一性索引。非唯一性索引：表中索引列的值不唯一。（2）索引的分類相關(guān)知識索引的概念索引的優(yōu)點(diǎn)通過創(chuàng)建唯一索引，可以增強(qiáng)數(shù)據(jù)記錄的唯一性可以大大加快數(shù)據(jù)檢索的速度?？梢约铀俦砼c表之間的連接。特別是在實(shí)現(xiàn)數(shù)據(jù)的參照完整性方面。使用索引可以再檢索數(shù)據(jù)的過程中使用優(yōu)化隱藏器，提高系統(tǒng)性能。在使用ORDERBY和GROUPBY子句中進(jìn)行檢索數(shù)據(jù)時(shí)，可以顯著減少查詢中分組和排序的時(shí)間。創(chuàng)建索引的注意事項(xiàng)帶索引的表在數(shù)據(jù)庫中會占據(jù)更多的空間，對數(shù)據(jù)進(jìn)行插入、更新、刪除操作的命令所花費(fèi)的時(shí)間會更長。創(chuàng)建索引所需的工作空間約為數(shù)據(jù)庫表的1.2倍。在設(shè)計(jì)和創(chuàng)建索引時(shí)，應(yīng)確保對性能的提高程度大于在存儲空間和處理資源方面的代價(jià)。（3）索引的優(yōu)點(diǎn)與注意事項(xiàng)8.2任務(wù)2:創(chuàng)建索引8.2.1相關(guān)知識創(chuàng)建索引索引的創(chuàng)建方式有兩種，使用圖形工具和使用T-SQL語句。1.什么情況需要創(chuàng)建索引：因?yàn)閯?chuàng)建索引需要耗費(fèi)一定的系統(tǒng)性能，所以當(dāng)出現(xiàn)以下情況時(shí)間可以考慮創(chuàng)建索引。定義有主關(guān)鍵字和外部關(guān)鍵字的列需在指定范圍中快速或頻繁查詢的列需要按排序順序快速或頻繁檢索的列在集合過程中需要快速或頻繁組合到一起的列2.什么情況不需要創(chuàng)建索引：在下列情況時(shí)，可以考慮不創(chuàng)建索引。在查詢中幾乎不涉及的列很少有唯一值的列由text，ntext或image數(shù)據(jù)類型定義的列只有較少行數(shù)的表（1）創(chuàng)建索引的方法相關(guān)知識創(chuàng)建索引使用Transact-SQL的CREATINDEX語句創(chuàng)建索引的語法結(jié)構(gòu)和參數(shù)說明如下：基本語法：CREATE[UNIQUE][CLUSTERED][NONCLUSTERED]INDEXindex_nameONtable_or_view_name(colum[ASC|DESC][,…n])[INCLUDE(colum_name[,…n])][WITH(PAD_INDEX={ON|OFF}FILLFACTOR=fillfactorSORT_IN_TEMPDB={ON|OFF}IGNORE_DUP_KEY={ON|OFF}STATISTICS_NORECOMPUTE={ON|OFF}DROP_EXISTING={ON|OFF}ONLINE={ON|OFF}ALLOW_ROW_LOCKS={ON|OFF}ALLOW_PAGE_LOCKS={ON|OFF}MAXDOP=max_degree_of_parallelism)[,…n]ON{_schema_name(column_name)|filegroup_name|default}（2）創(chuàng)建索引的語法其中：參數(shù)代表的意義如下所示UNIQUE 該選項(xiàng)表示創(chuàng)建唯一性的索引，在索引列中不能有相同的列值存在。CLUSTERED 該選項(xiàng)表示創(chuàng)建聚集索引NONCLUSTERED 該選項(xiàng)表示創(chuàng)建非聚集索引，這是CREAEINDEX的默認(rèn)值。INCLUDE 該選項(xiàng)用于指導(dǎo)將要包含到非聚集索引的頁級中的非鍵列。PAD_INDEX 該選項(xiàng)用于指定索引的中間頁級，也就是說為非葉級索引指定填充度。這時(shí)的填充度由FILLFACT選項(xiàng)指定。FIllFACTOR 該選項(xiàng)用于指定非頁級索引頁的填充度。SORT_INT_TEMPDB 該選項(xiàng)為ON時(shí)，用于指定創(chuàng)建索引時(shí)產(chǎn)生的中間結(jié)果，在tempdb數(shù)據(jù)庫中進(jìn)行排序。為OFF時(shí)，在當(dāng)前數(shù)據(jù)庫中排序。IGNORE_DUP_KEY 該選項(xiàng)用于指定唯一性索引鍵冗余數(shù)據(jù)的系統(tǒng)行為。當(dāng)為ON時(shí)，系統(tǒng)發(fā)出警告信息，違反唯一性行的數(shù)據(jù)插入失敗。為OFF時(shí)，取消整個(gè)INSERT語句，并且發(fā)出錯誤信息。STATISTICS_NORECOMPUTE 該選項(xiàng)用于是否重新計(jì)算索引統(tǒng)計(jì)信息。為ON時(shí)，不自動計(jì)算過期的索引統(tǒng)計(jì)信息。為OFF時(shí)，啟動自動計(jì)算能力。DROP_EXIXTING 該選項(xiàng)用于是否可以刪除指定的索引，并且重建已有的索引。為OFF時(shí)，不能刪除重建。ONLINE 該選項(xiàng)用于指定索引操作期間基礎(chǔ)表和關(guān)聯(lián)索引是否可用于查詢。為ON為，不持有表鎖，允許用于查詢。為OFF時(shí)，持有表鎖，索引操作期間不能指定查詢。ALLOW_ROW_LOCKS: 該選項(xiàng)用于指定是否使用行鎖，為ON，表示使用行鎖。ALLOW_PAGE_LOCKS 該選項(xiàng)用于指定是否使用頁鎖，為ON，表示使用頁鎖。MAXDOP 該選項(xiàng)用于指定索引操作期間覆蓋最大并行度的配置選項(xiàng)。主要目的是限制執(zhí)行并行計(jì)劃過程中使用的處理器數(shù)量8.2.2任務(wù)實(shí)施創(chuàng)建索引步驟1：在“SQLServerManagermentStudio”管理工具中展開數(shù)據(jù)庫對象文件夾，選擇并展要創(chuàng)建索引的數(shù)據(jù)庫如“student”。展開要創(chuàng)建索引的數(shù)據(jù)表，右鍵單擊“索引”節(jié)點(diǎn)，在彈出的快捷菜單中，選擇“新建索引”命令。方法1：使用“SSMS”管理工具創(chuàng)建索引操作圖示步驟2：在彈出的“新建索引”對話框中，可以輸入索引的名稱、選擇索引的類型、選擇是否是唯一索引等如圖8?5所示。在此例中，我們在“班級”表中建立“班級名稱”的非聚集索引。操作圖示8.2.2任務(wù)實(shí)施創(chuàng)建索引步驟3：單擊“添加”按鈕，從“班級”表中選擇“班級名稱”復(fù)選框。方法1：使用“SSMS”管理工具創(chuàng)建索引操作圖示步驟4：單擊“確定”按鈕，返回“新建索引”窗口，然后單擊“確定”就生成了“班級名稱”索引。操作圖示8.2.2任務(wù)實(shí)施創(chuàng)建索引操作1：在數(shù)據(jù)庫（Student）中對“學(xué)生”表中的“學(xué)生姓名”列，創(chuàng)建的索引“學(xué)生姓名”，該索引要求為非聚集索引，創(chuàng)建語句如下：CREATENONCLUSTEREDINDEX學(xué)生姓名ON學(xué)生表(姓名)打開表“學(xué)生”表在索引節(jié)點(diǎn)可以看到新建的（非唯一、非聚集索引“學(xué)生姓名”）。方法2：使用Transaction-SQL語句創(chuàng)建索引操作圖示8.2.2任務(wù)實(shí)施創(chuàng)建索引操作2：在數(shù)據(jù)庫（Student）中對“課程”表中的課程編號,建立唯一性索引“編號”，創(chuàng)建語句如下：CREATEUNIQUEINDEX編號ON課程表(課程編號)打開表“課程”在索引節(jié)點(diǎn)可以看到新建的（唯一性、非聚集索引“編號”）如圖8-9所示。方法2：使用Transaction-SQL語句創(chuàng)建索引操作圖示8.3任務(wù)3:索引的刪除8.3.1相關(guān)知識索引的刪除刪除索引也有兩種方式：使用SQLServerManagementStudio管理工具和使用T-SQL語句。（2）.刪除索引的語法使用Transact-SQL的DROP語句修改索引的基本語法如下：DROPINDEX<indexname>on<tableorviewname>（1）.刪除索引的方法8.3.2任務(wù)實(shí)施索引的刪除步驟1：打開“SQLServerManagementStudio”管理工具，選擇要刪除的索引。方法1.使用“SSMS”管理工具刪除索引步驟2：單擊右鍵，選擇“刪除”，如圖8-10所示。操作圖示8.2.2任務(wù)實(shí)施索引的刪除操作1：在數(shù)據(jù)庫“Student”中的“學(xué)生”表中刪除索引“學(xué)生姓名”，執(zhí)行語句如下：DROPINDEX學(xué)生姓名ON學(xué)生表方法2：使用T-SQL語言刪除索引8.4任務(wù)4:索引的優(yōu)化8.4.1相關(guān)知識索引的優(yōu)化數(shù)據(jù)庫的總體性能很大程度上取決于索引的效率，因此，有必要確保以最有利于應(yīng)用程序的方式設(shè)計(jì)并實(shí)現(xiàn)索引。實(shí)現(xiàn)索引后，必須維護(hù)索引，以確保其一致保持最佳性能。隨著數(shù)據(jù)庫中的數(shù)據(jù)增加、更改和刪除，索引將變得分碎。根據(jù)業(yè)務(wù)環(huán)境及數(shù)據(jù)庫應(yīng)用程序的用途，碎片可能有利于性能，也可能有礙于性能，但是無論如何都需要對索引進(jìn)行優(yōu)化，并將索引碎片控制在適當(dāng)?shù)某潭取Ｋ饕膬?yōu)化主要手段就是索引碎片整理。8.4.1相關(guān)知識索引的優(yōu)化1.索引碎片的優(yōu)化整理方式索引碎片的優(yōu)化整理主要有兩種方式：重新組織和重新生成，通常情況下，如果碎片度低于30%，那么應(yīng)該重新組織索引，如果碎片度高于30%應(yīng)該重新生成索引。重新組織和重新生成的區(qū)別如表8-2所示。操作圖示重新組織重新生成重新組織索引是在物理上對葉級頁重新排序，使其與葉節(jié)點(diǎn)的邏輯順序（從左到右）相符，從而對表的聚集索引和非聚集索引的葉級進(jìn)行碎片整理重新生成索引將刪除原索引，并重新生成一個(gè)新索引，重新生成將刪除碎片，回收并重新分配磁盤空間，從而提高磁盤性能。表8?2重新組織和重新生成區(qū)別表索引的優(yōu)化1.索引優(yōu)化的主要方法：索引優(yōu)化的主要方法也分為兩種：使用SQLServerManagementStudio管理工具和使用T-SQL語句。2.索引優(yōu)化的主要語法：使用Transact-SQL的ALTER語句修改索引的語法結(jié)構(gòu)和參數(shù)說明如下：重新組織基本語法：ALTERINDEX<indexname>on<tableorviewname>REORGANIZE重新生成基本語法：ALTERINDEX<indexname>on<tableorviewname>REBUILD2．查看索引碎片信息索引的優(yōu)化步驟1：在SQLServerManagementStudio中，選擇要查看的索引單擊右鍵，彈出索引屬性菜單。如圖8-11所示。操作圖示方法1．使用“SSMS”管理工具查看索引碎片信息步驟2：在索引屬性窗口中，單擊“碎片”選項(xiàng)，可以看到當(dāng)前索引的碎片狀態(tài)如圖8-12所示。操作圖示3.索引優(yōu)化索引的優(yōu)化步驟1：打開“SQLServerManagementStudio”管理工具，選擇要優(yōu)化的索引。方法1:：使用“SQLServerManagementStudio”管理工具優(yōu)化索引步驟2：單擊右鍵，選擇“重新生成”或“重新組織”，如圖8-13所示。操作圖示3.索引優(yōu)化索引的優(yōu)化如重新組織“班級”表中的索引“班級名稱”執(zhí)行語法如下：ALTERINDEX班級名稱ON班級表REORGANIZE方法2：使用T-SQL語句優(yōu)化索引項(xiàng)目小結(jié)本項(xiàng)目主要講解了索引的作用及分類，索引根據(jù)物理結(jié)構(gòu)可以分為聚集索引和非聚集索引，根據(jù)唯一性又可以分為唯一索引和非唯一索引，接著介紹了索引的優(yōu)點(diǎn)及創(chuàng)建索引時(shí)需要注意的情況，然后介紹了如何使用管理工具或T-SQL