索引及其應(yīng)用解析

1、第6章索引及其應(yīng)用15n教學(xué)目標(biāo)通過本章學(xué)習(xí)，使學(xué)生掌握索引的基本概念、分類和作用，掌握索引 的建立和操作方法，掌握索引的維護(hù)方法，會根據(jù)實(shí)際問題的需要，能夠 熟練地建立表和視圖的相關(guān)索引。7教學(xué)要求知識要點(diǎn)能力要求關(guān)聯(lián)知識索引概念、分類和作用(1)掌握索引的基本概念、分類和作用索引概念、分類和作用索引的建立和操作(1)掌握索引的建立方法掌握索引的操作方法SQL Server Man ageme ntStudio建立和操作索引，CREATE INDEX 等 SQL 命令索引的維護(hù)(1)掌握維護(hù)索引的常用方法DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 命令索引視圖(1)

2、掌握索引視圖的建立和應(yīng)用方法CREATE VIEW，CREATEINDEX等SQL命令N重點(diǎn)難點(diǎn)索引的概念、分類和作用索引的建立和操作方法索引視圖的建立與應(yīng)用6.1 任務(wù)描述本章完成項(xiàng)目的第 6 個(gè)任務(wù)：在大學(xué)生選課管理數(shù)據(jù)庫 Student 中，完成如下 操作：1為課程信息表創(chuàng)建一個(gè)非聚集復(fù)合索引。2為教師教課信息表創(chuàng)建一個(gè)聚集復(fù)合索引。 3為學(xué)生選課信息表創(chuàng)建一個(gè)唯一、聚集復(fù)合索引。6.2 索引綜述數(shù)據(jù)庫中的索引可以快速找到表或索引視圖中的特定信息。索引包含從表或視 圖中一個(gè)或多個(gè)列生成的鍵，以及映射到指定數(shù)據(jù)的存儲位置的指針。通過創(chuàng)建、 設(shè)計(jì)良好的索引以支持查詢，可以顯著提高數(shù)據(jù)庫查詢和

3、用應(yīng)程序的性能。索引可 以減少為返回查詢結(jié)果集而必須讀取的數(shù)據(jù)量。索引還可以強(qiáng)制表中的行具有唯一 性，從而確保表數(shù)據(jù)的數(shù)據(jù)完整性。1索引的概念數(shù)據(jù)庫中的索引與書籍中的索引（目錄）類似，在一本書中，利用索引可以快 速查找所需信息，無須閱讀整本書。在數(shù)據(jù)庫中，索引使數(shù)據(jù)庫程序無須對整個(gè)表 進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)。書中的索引是一個(gè)詞語列表，其中注明了 包含各個(gè)詞的頁碼。而數(shù)據(jù)庫中的索引是某個(gè)表中一列或者若干列值的集合和相應(yīng) 的指向表中物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單。 也可以這么說， 數(shù)據(jù)庫中某 個(gè)表的索引是指， 將這個(gè)表中數(shù)據(jù)行按照某一列或者若干列值的組合 （稱為索引鍵） 的大小，

4、只排列各個(gè)數(shù)據(jù)行的順序，而不改變數(shù)據(jù)行的存儲位置，得到的一個(gè)非結(jié) 構(gòu)數(shù)據(jù)文件。2 索引的作用通過創(chuàng)建唯一索引，可以保證數(shù)據(jù)記錄的唯一性。 通過創(chuàng)建和使用索引可以大大加快數(shù)據(jù)檢索的速度。 通過創(chuàng)建和使用索引可以加速表與表之間的連接，這一點(diǎn)在實(shí)現(xiàn)數(shù)據(jù)的參 照完整性方面有特別的意義。通過創(chuàng)建和使用索引使得在使用 ORDER B湘GROUP BY子句中進(jìn)行檢索數(shù) 據(jù)時(shí)，可以顯著減少查詢中分組和排序的時(shí)間。 通過索引可以在檢索數(shù)據(jù)的過程中使用優(yōu)化隱藏器，提高系統(tǒng)性能。3索引類型 表或視圖可以包含以下類型的索引。（1）聚集索引 聚集索引是指表中數(shù)據(jù)行的物理存儲順序與索引列順序完全相同。聚集索引是 根據(jù)數(shù)據(jù)

5、行的鍵值在表或視圖中排序而存儲這些數(shù)據(jù)行。索引定義中包含聚集索引 列。每個(gè)表只能有一個(gè)聚集索引，因?yàn)閿?shù)據(jù)行本身只能按一個(gè)順序方式排序。只有當(dāng)表包含聚集索引時(shí)，表中的數(shù)據(jù)行才按排序順序存儲。如果表沒有聚集 索引，則其數(shù)據(jù)行存儲在一個(gè)稱為堆的無序結(jié)構(gòu)中。（2）非聚集索引 非聚集索引不改變表中數(shù)據(jù)行的物理存儲位置，數(shù)據(jù)與索引分開存儲，通過索 引帶有的指針與表中的數(shù)據(jù)發(fā)生聯(lián)系。非聚集索引具有獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚 集索引包含非聚集索引鍵值， 并且每個(gè)鍵值項(xiàng)都是指向包含該鍵值的數(shù)據(jù)行的指針。一個(gè)表或視圖可含有多個(gè)非聚集索引。聚集索引和非聚集索引都可以是唯一的。這意味著任何兩行都不能有相同的索 引鍵值。

6、另外，索引也可以不是唯一的，即多行可以共享同一個(gè)索引鍵值。每當(dāng)修 改了數(shù)據(jù)表內(nèi)容后，都會自動維護(hù)表或視圖的索引。（ 3）唯一索引 唯一索引確保索引鍵不包含重復(fù)的值，因此，表或視圖中的每一行在某種程度 上是唯一的。（ 4）包含性列索引 是一種非聚集索引，它擴(kuò)展后不僅包含鍵列，還包含非鍵列。（ 5）索引視圖 視圖的索引將具體化（執(zhí)行）視圖，并將結(jié)果集永久存儲在唯一的聚集索引中， 而且存儲方法與帶聚集索引的表的存儲方法相同。創(chuàng)建聚集索引后，可以為視圖添 加非聚集索引。6.3 創(chuàng)建索引使用索引要付出一定的空間和時(shí)間的代價(jià)，因此為表建立索引時(shí)，要根據(jù)實(shí)際 情況，認(rèn)真考慮哪些列應(yīng)該索引，哪些列不應(yīng)該索引。

7、建立索引一般要遵循以下幾條原則：主鍵列上一定要建立索引 ;外鍵列上可以建立索引 ;在經(jīng)常查詢的字段上最好建立索引 ; 對于查詢中很少涉及的列、重復(fù)值比較多的列不要建立索引 ; 對于定義為 text 、 image 和 bit 數(shù)據(jù)類型的列上不要建立索引 ;SQL Server 2008在創(chuàng)建主鍵約束或唯一約束時(shí)，自動創(chuàng)建唯一索引，以強(qiáng)制實(shí) 施PRIMARY KEY和UNIQUE約束的唯一性要求。如果需要?jiǎng)?chuàng)建不依賴于約束的索引, 可以使用 SQL Server Management Studio或者使用 SQL命令創(chuàng)建索引。建立索引時(shí)要注意以下幾點(diǎn)：只有表或視圖的所有者才有權(quán)建立索引。在建立聚集

8、索引時(shí)，將會對表進(jìn)行復(fù)制，對表中的數(shù)據(jù)進(jìn)行排序，然后刪除原 始的表。因此，數(shù)據(jù)庫上必須有足夠的空間，以容納數(shù)據(jù)復(fù)本。在使用CREATENDEX命令建立索引時(shí)，必須指定索引名稱、表名稱及索引所應(yīng) 用的各列名稱（即索引鍵）。在一個(gè)表中最多可建立 249個(gè)非聚集索引。默認(rèn)情況下，建立的索引是非聚集 索引。復(fù)合索引的列的最大數(shù)目為16,各列組合的最大長度為900字節(jié)。6.3.1 使用 SQL Server Management Studio 創(chuàng)建索引1.啟動 SQL Server Management Studio,并連接到 SQL Server 2008中的數(shù)據(jù)庫，在“對象資源管理器”窗口中展開“數(shù)

9、據(jù)庫”節(jié)點(diǎn)，再展開建立索引的表所屬的數(shù) 據(jù)庫名（比如Student）,再展開其“表”節(jié)點(diǎn)，展開要建立索引的表名（比如Stab）,右擊其“索引”節(jié)點(diǎn)，出現(xiàn)彈出菜單，如圖 6-1所示。IB Q H'*pcrtSi*r!terTRrpnBQ (J S-xdent匡軸年龍禾圖-1如沁* 和匡峨e itaF 口+國J Jbcf T-a?l dbo.TCtal3L 口視匡 EE I瞰說+ LJ町堀程性* _j w- ited LJ存悔 E lj妥空住E Teacitr e性圖6-1新建索引2 .執(zhí)行彈出菜單中的【新建索引】命令，系統(tǒng)則出現(xiàn)“新建索引”對話框，如 圖6-2所示。O.二Jr. * 二二

10、.r$L：樺劃r陰.阿-衲 IS .(siijTikdr Otr 印EZFrrrfilEFi-STTirSDI5S氏P .VnCfc- 5 7Til: LI'istlIlI=Ef £ SfFUftfill宇：|&列逞卄亠£ 土1帕a|軸1毎識1樸F 1JUL. -It圖6-2 “新建索引”對話框3 在新建索引對話框中，于“索引名稱”文本框中輸入新建索引的名稱，可于 “索引類型”下拉框中選擇新建索引的類型，可單擊“索引鍵列”列表框后的“添 加”按鈕，系統(tǒng)出現(xiàn)“選擇索引鍵列”對話框，如圖6-3所示。tX-UF+T*.hil 1left.1亦期1AN .IE Ir

11、止strinarKbgr如*3舌舌rrajUu幸W¥申網(wǎng)r筋r (20)舌苦bprcLui QQ>20舌dllckriij11舌圖6-3選擇索引鍵列4在選擇索引鍵列對話框中，列出了建立索引的表的所有字段，從中選擇新建索引所應(yīng)用的各個(gè)列名(即選擇作為索引鍵的各個(gè)列)，選擇完畢后，單擊“確定“按鈕，系統(tǒng)返回“新建索引”對話框，如圖 6-4所示。#*¥3豆msl*iH _ii> 刨孱說 *i：f HUI ittaKin.血?jiǎng)?wù)圭num. rTTir圖6-4 “新建索引”對話框5 在該新建索引對話框中，可通過“索引鍵列”列表框中的“排序順序”下 拉框，設(shè)置相應(yīng)的索引鍵列的

12、排序順序。(1) 可選擇“選項(xiàng)”選擇頁，進(jìn)入“選項(xiàng)”設(shè)置界面，在此，可根據(jù)實(shí)際需要設(shè)置應(yīng)用索引時(shí)的相關(guān)選項(xiàng)，如圖6-5所示。(2) 可選擇“包含性列”選擇頁，進(jìn)入“包含性列”設(shè)置界面，在此，可設(shè)置 另一個(gè)表中的列，只有非聚集索引，該選擇頁才可用。(3) 可選擇“存儲“選擇頁，進(jìn)入“存儲”設(shè)置界面，在此，可設(shè)置對指定的 文件組或方案創(chuàng)建索引。圖6-5 “新建索引”對話框632使用SQL命令創(chuàng)建索引語法形式：CREATE UNIQUE CLUSTERED| NONCLUSTERED INDEX in dex_ nameON table | view ( colu mn ASC | DESC ，n

13、) WITHPAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP_EXISTING,STATISTICS_NORECOMPUTE ,SORT_IN_TEMPDBON filegroup 其中：UNIQUE ：用于指定為表或視圖創(chuàng)建唯一索引；CLUSTERED ：用于指定創(chuàng)建的索引為聚集索引；NONCLUSTERED ：用于指定創(chuàng)建的索引為非聚集索引，默認(rèn)為非聚集索 引；index_name ：用于指定所創(chuàng)建的索引名稱；table ：用于指定創(chuàng)建索引的表的名稱；view ：用于指定創(chuàng)建索引的視圖的名稱。column ：用于指定被索引的列，即索引所

14、應(yīng)用的列(索引鍵中的列) ；ASC|DESC ：用于指定具體某個(gè)索引列的升序或降序排序方向；PAD_INDEX ：用于指定索引中間級中每個(gè)頁(節(jié)點(diǎn))上保持開放的空間 ； FILLFACTOR = fillfactor ：用于指定在創(chuàng)建索引時(shí)，每個(gè)索引頁的數(shù)據(jù)占 索引頁大小的百分比， fillfactor 的值為 1 到 100。IGNORE_DUP_KEY ：用于控制當(dāng)往包含于一個(gè)唯一聚集索引中的列中插入 重復(fù)數(shù)據(jù)時(shí) SQL Server 所作的反應(yīng)。DROP_EXISTING ：用于指定應(yīng)刪除并重新創(chuàng)建已命名的先前存在的聚集索 引或者非聚集索引。STATISTICS_NORECOMPUTE

15、：用于指定過期的索引統(tǒng)計(jì)不會自動重新計(jì) 算。SORT_IN_TEMPDB ：用于指定創(chuàng)建 索引時(shí)的中間排 序結(jié)果將存儲 在 tempdb 數(shù)據(jù)庫中。ON filegroup ：用于指定存放索引的文件組?！纠?6-1 】在數(shù)據(jù)庫 Teaching 中，為學(xué)生成績表 sgrade 建立一個(gè)基于 “學(xué)號， 姓名” 組合列的唯一、非聚集復(fù)合索引 s_index1 。Use TeachingGoCreate UNIQUE Index s_index1 ON sgrade(xh , xm)Go【例 6-2 】在數(shù)據(jù)庫 Teaching 中，為學(xué)生成績表 sgrade 建立一個(gè)基于“所在 系，班級，姓名”組

16、合列的聚集復(fù)合索引 s_index2 。Use TeachingGoCreate CLUSTERED Index s_index2ON sgrade(szx , bj , xm)Go【例 6-3 】在數(shù)據(jù)庫 Teaching 中，為學(xué)生成績表 sgrade 建立一個(gè)基于“姓名”列的非聚集索引 s_index3 。Use Teach ingGoCreate In dex s_in dex3 ON sgrade（xm DESC）Go6.4操作索引641 使用 SQL Server Management Studio 操作索引啟動 SQL Server Management Studio，并連接至U

17、 SQL Server 2008中的數(shù)據(jù)庫，在“對象資源管理器”窗口中展開“數(shù)據(jù)庫”節(jié)點(diǎn)，再展開操作索引的表所屬的數(shù)據(jù)庫名（比如Student），再展開其“表”節(jié)點(diǎn)，展開索引所屬的表名（比如Stab）,展開其“索弓I”節(jié)點(diǎn)，右擊要操作的索引名，出現(xiàn)彈出菜單，如圖6-6所示。B ,J StLdCT-til _j額毎庫珈創(chuàng) 凹丄去0Mitt* Zi dt»,£ub國tildb.Ttab- dbo.ctabH = dkn.TCtoba dtK.SCt>l±l圖ffl 口同空同 田J可瑞程性L+l Service BrEka-m _j存愴Ld旻全性H . | 1 e

18、ach erFl丄査全世5CL 5cr/r 比建r口 二斬瞎幸引g. 扁寫蟲切掃:対&車來誹朝 緊用也誓B5Q方血暢官和比怕汁初也SHVE圖6-6操作索引1. 查看和修改索引屬性執(zhí)行圖6-6彈出菜單中的【屬性】命令，進(jìn)入“索引屬性”對話框，在此，可 查看和修改當(dāng)前索引的有關(guān)屬性，如圖6-7所示。女引 tttt - Siabjn*!' il l rsii'i?jICT i；i二丈申后說解門川1巾"|：5F0:汕|訓(xùn)131否HI昆bvucbv EDI印晉7.HIMI語醫(yī)Pl 血 EMTTMJtlr_ JLE1-Z U-jJ' =圖6-7 “索引屬性”對話框

19、2 .重命名索引執(zhí)行圖6-6彈出菜單中的【重命名】命令，可以重命名當(dāng)前索引。3 刪除索引執(zhí)行圖6-6彈出菜單中的【刪除】命令，可以刪除當(dāng)前索引。642使用SQL命令操作索引1查看表的索引信息語法格式：EXEC sp_helpindex 表名2重命名索引語法格式：EXEC sp_rename 表名.原索引名 ' , 表名 .新索引名 '3刪除表索引語法格式：DROP INDEX 表名 .索引名或者DROP INDEX 索引名 ON 表名或視圖名【例 6-4】 刪除數(shù)據(jù)庫 Teaching 中，表 sgrade 的索引 s_index2 。Use TeachingGoDrop In

20、dex sgrade . s_index2Go6.5 索引的維護(hù)索引創(chuàng)建之后，由于數(shù)據(jù)的增加、刪除和修改等操作會使索引頁發(fā)生碎塊，因 此必須對索引進(jìn)行維護(hù)。SQL Server提供了多種維護(hù)索引的方法， 常用的有DBCC SHOWCONTIG和DBCC INDEXDEFRAG命令。1查看表的數(shù)據(jù)和索引的碎片信息語法格式：（1）DBCC SHOWCONTIG 表名或視圖名（2）DBCC SHOWCONTIG （表名或視圖名 , 索引名）2整理表中索引碎片語法格式：DBCC INDEXDEFRAG數(shù)據(jù)庫名,表名或視圖名，索引名)【例6-5】清除數(shù)據(jù)庫Teaching中的表sgrade的所有索引碎片

21、。Use TeachingGoDBCC INDEXDEFRAG(Teaching， sgrade)Go附注： 可在“索引屬性”對話框中(如上圖 6-7 所示)通過“碎片”選擇頁進(jìn)入“碎 片”界面，在此可查看和整理當(dāng)前索引的碎片情況。說明： 當(dāng)數(shù)據(jù)進(jìn)行大量的修改后，這時(shí)可將原索引刪掉，再重新建立索引。6.6 索引視圖對于視圖而言，系統(tǒng)為它們動態(tài)生成結(jié)果集的開銷很大，尤其是對于那些涉及 大量行進(jìn)行復(fù)雜處理(如聚集大量數(shù)據(jù)或連接許多行)的視圖。如果在查詢中頻繁 地使用這類視圖，應(yīng)該對視圖創(chuàng)建唯一聚集索引，形成索引視圖。索引視圖中存放 著查詢得到的結(jié)果集，它在數(shù)據(jù)庫中的存儲方式與具有聚集索引的表的存儲

22、方式相 同，從而提高查詢性能。創(chuàng)建索引視圖除要遵照創(chuàng)建標(biāo)準(zhǔn)視圖的要求外，還應(yīng)注意如下幾點(diǎn)：索引視圖只能引用基表，不能引用其他視圖。索引視圖引用的所有基表必須與視圖位于同一數(shù)據(jù)庫中， 且所有者也與視圖 相同。索引視圖引用的基表名稱必須由兩部分組成，即 架構(gòu)名 .表名創(chuàng)建索引視圖時(shí)必須使用WITH SCHEMABINDING選項(xiàng)若索引視圖定義中使用聚集函數(shù)， SELEC例表中必須包括 COUNT_BIG(*)。 索引視圖中的表達(dá)式引用的所有函數(shù)必須是確定的?！纠?6-6】在大學(xué)生選課管理數(shù)據(jù)庫 Student 中，創(chuàng)建一個(gè) xk_view 的索引視圖， 該視圖可查詢每個(gè)學(xué)生的學(xué)號、姓名、所選課門數(shù)

23、和所選課程的平均成績。Use StudentGo/* 使用 WITH SCHEMABINDING選項(xiàng)創(chuàng)建視圖 xk_view*/Create View xk_viewWITH SCHEMABINDINGASSelect dbo .stab.xh 學(xué)號 , dbo .stab.xm 姓名 ,Count_Big(*) 選課門數(shù) , Avg(dbo.sctab. cj) 平均成績From dbo .stab , dbo .sctabWhere dbo.stab.xh=dbo.sctab.xhGroup By dbo .stab.xh , dbo .stab.xmGo/*為視圖建立一個(gè)基于"學(xué)號 ,姓名”組合列的唯一聚集索引xk_index，形成索引視圖 */Create UNIQUE CLUSTERED Index xk_indexON xk_view ( 學(xué)號 , 姓名 )Go/*使用該索引視圖xk_view，查看學(xué)生選課情況*/Select * From xk_viewGo6.7 任務(wù)實(shí)現(xiàn)1為數(shù)據(jù)庫 Student 中的課程信息表創(chuàng)建一個(gè)復(fù)合索引在數(shù)據(jù)庫 Student 中，為課程信息表創(chuàng)建一個(gè)基