第9章 索引

1、數(shù) 據(jù) 庫 原 理 與 應(yīng) 用（SQL Server 2005）,第9章 索引,第9章 索引,,任務(wù)目標(biāo)：?理解索引的概念、結(jié)構(gòu)和分類、優(yōu)缺點(diǎn)；?掌握創(chuàng)建索引的方法及創(chuàng)建索引時(shí)的選項(xiàng)；?了解使用SSMS創(chuàng)建和管理索引的方法；?了解索引的分析方法和整理碎片的方法；?理解統(tǒng)計(jì)信息的概念；?會(huì)配置統(tǒng)計(jì)信息的選項(xiàng)。,第9章 索引,,9.1 索引概述9.2 創(chuàng)建和管理索引9.3 索引的分析與維護(hù)習(xí)題與實(shí)驗(yàn),9.1 索

2、引概述,9.1.1 索引的概念,索引的定義 索引是一種為了加速對(duì)表中數(shù)據(jù)行的檢索而創(chuàng)建的分散存儲(chǔ)結(jié)構(gòu)。 SQL Server中的索引與書的目錄很類似 索引是基于表中的數(shù)據(jù)創(chuàng)建的，它是由除存放表的數(shù)據(jù)頁面以外的索引頁面構(gòu)成。每個(gè)索引頁面中的行都含有邏輯指針，以便加速檢索物理數(shù)據(jù)。 對(duì)表中的列是否創(chuàng)建索引以及創(chuàng)建什么樣的索引，對(duì)于查詢的響應(yīng)速度都會(huì)有很大的影響。,9.1 索引概述,9.1.2 索引的優(yōu)點(diǎn)與缺點(diǎn),創(chuàng)建索引的優(yōu)點(diǎn)（

3、1）索引可以加速查詢（2）加快表與表之間的連接 （3）在包含分組和排序的查詢中，減少分組排序時(shí)間（4）有利于SQL Server進(jìn)行查詢優(yōu)化（5）可以強(qiáng)制實(shí)施唯一性約束創(chuàng)建索引的缺點(diǎn)（1）創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間（2）索引需要占一定的物理空間（3）索引會(huì)降低數(shù)據(jù)修改的性能,9.1 索引概述,9.1.3 索引的結(jié)構(gòu)與分類,,,索引的結(jié)構(gòu),9.1 索引概述,9.1.3 索引的結(jié)構(gòu)與分類,1、索引的結(jié)構(gòu)——B樹,索引的

4、結(jié)構(gòu),9.1 索引概述,9.1.3 索引的結(jié)構(gòu)與分類,2、索引分類—聚集索引（CLUSTERED INDEX）聚集索引確定表中數(shù)據(jù)的物理順序;每個(gè)表只能有一個(gè)聚集索引;數(shù)據(jù)頁就是聚集索引樹的葉級(jí)頁;行的物理存儲(chǔ)順序和索引的邏輯順序完全相同;對(duì)于那些經(jīng)常要搜索范圍值的列特別有效。 表上如果存在主鍵，主鍵列即自動(dòng)會(huì)創(chuàng)建聚集索引。,9.1 索引概述,9.1.3 索引的結(jié)構(gòu)與分類,聚集索引的結(jié)構(gòu),9.1 索引概述,9.1.3

5、索引的結(jié)構(gòu)與分類,2、索引分類—聚集索引（CLUSTERED INDEX）一般情況下，表上如果存在主鍵，主鍵列即默認(rèn)會(huì)創(chuàng)建聚集索引。特殊情況下我們需要考慮以下情況建立聚集索引：經(jīng)常按范圍查詢的列；經(jīng)常用于分組和排序的列；在連接中常用的列。在創(chuàng)建聚集索引時(shí)應(yīng)注意以下事項(xiàng)：每張表只能包含一個(gè)聚集索引，但可以是多列的組合；由于聚集索引會(huì)改變表中數(shù)據(jù)的物理順序，所以應(yīng)該先創(chuàng)建聚集索引，后建立非聚集索引；不能在頻繁修改的列上創(chuàng)建

6、聚集索引，這樣會(huì)浪費(fèi)大量的成本來維護(hù)索引。,9.1 索引概述,9.1.3 索引的結(jié)構(gòu)與分類,2、索引分類—非聚集索引（NONCLUSTERED INDEX）非聚集索引中記錄的物理順序與邏輯順序沒有必然的聯(lián)系。非聚集索引的葉級(jí)頁中記錄了指向物理數(shù)據(jù)位置的信息，可以快速定位到指定數(shù)據(jù)。如果表中沒有聚集索引，則非聚集索引建立在原始無序的數(shù)據(jù)上；而當(dāng)表上已經(jīng)有了聚集索引時(shí)，非聚集索引將建立在聚集索引上。在只包含非聚集索引的表中，葉節(jié)

7、點(diǎn)包含了具有指針的行標(biāo)識(shí)符，由文件ID、頁碼行頁上的行數(shù)構(gòu)成。當(dāng)在已經(jīng)有聚集索引的表上建立非聚集索引的時(shí)候，每個(gè)非聚集索引的行指示器包含了行的聚集索引鍵值。,,,非聚集索引的結(jié)構(gòu),9.1 索引概述,9.1.3 索引的結(jié)構(gòu)與分類,2、索引分類—非聚集索引（NONCLUSTERED INDEX）非聚集索引常被用在以下情況： 經(jīng)常用于分組、匯總的列上；經(jīng)常用于排序的列上；經(jīng)常用于連接的列上；經(jīng)常返回總數(shù)據(jù)量中很少一部分的列上。

8、創(chuàng)建非聚集索引需要考慮以下一些事項(xiàng)：創(chuàng)建索引時(shí)，默認(rèn)為非聚集索引；對(duì)每個(gè)表最多可以建立249個(gè)非聚集索引；非聚集索引需要占用一定的磁盤空間；會(huì)一定程度上降低向表中插入和更新數(shù)據(jù)的速度；若表上的聚集索引發(fā)生改變（新建或刪除），將重建表上現(xiàn)有的非聚集索引。,9.1 索引概述,9.1.4 設(shè)計(jì)數(shù)據(jù)表的索引,1．考慮創(chuàng)建索引的列（1）主鍵列上（默認(rèn)創(chuàng)建一個(gè)聚集索引）；（2）經(jīng)常用在連接的列上；（3） 經(jīng)常需要進(jìn)行范

9、圍查詢的列上；（4）經(jīng)常需要排序的列上。 2．不考慮創(chuàng)建索引的列（1）很少或從來不在查詢中引用的列。（2）選擇性低（重復(fù)值多）的列；（3）小表（記錄數(shù)很少的表）一般也不必創(chuàng)建索引；（4）更新操作比較頻繁的列上不適合創(chuàng)建索引。,9.1 索引概述,9.1.4 設(shè)計(jì)數(shù)據(jù)表的索引,【任務(wù)9-1】設(shè)計(jì)學(xué)生表Student的索引。,分析： 在學(xué)生表中經(jīng)常查詢的字段包括：學(xué)生編號(hào)、學(xué)生姓名、性別、班級(jí)編號(hào)、地址等。

10、其中學(xué)生編號(hào)是主鍵列； 班級(jí)編號(hào)是外鍵列，經(jīng)常用于連接； 性別列唯一性差、地址列字段較長(zhǎng)，均不適合創(chuàng)建索引。,聚集索引：學(xué)生編號(hào)列上創(chuàng)建主鍵，默認(rèn)創(chuàng)建聚集索引； 聚集索引：學(xué)生姓名、班級(jí)編號(hào)兩列上分別建立非聚集索引。,9.1 索引概述,9.1.4 設(shè)計(jì)數(shù)據(jù)表的索引,【任務(wù)9-2】設(shè)計(jì)班級(jí)表Class的索引。,分析： 班級(jí)表中經(jīng)常查詢的字段包括：班級(jí)編號(hào)、班級(jí)名稱、專業(yè)編號(hào)、班長(zhǎng)編號(hào)等； 班級(jí)編號(hào)是主鍵列；

11、專業(yè)編號(hào)和班長(zhǎng)編號(hào)是外鍵列，經(jīng)常用于連接。,聚集索引：班級(jí)編號(hào)列上創(chuàng)建主鍵，默認(rèn)創(chuàng)建聚集索引； 聚集索引：班級(jí)名稱、專業(yè)編號(hào)、班長(zhǎng)編號(hào)分別創(chuàng)建非聚集索引。,第9章 索引,,9.1 索引概述9.2 創(chuàng)建和管理索引9.3 索引的分析與維護(hù)習(xí)題與實(shí)驗(yàn),9.2 創(chuàng)建和管理索引,使用T-SQL語句創(chuàng)建索引的語法,9.2.1 使用CREATE INDEX語句創(chuàng)建索引,CREATE [UNIQUE][CLUSTERED|NONCL

12、USTERED] INDEX 索引名 ON {表名|視圖名}(列[ASC|DESC][ ,...n ])[WITH [PAD_INDEX] [[,] FILLFACTOR = 填充因子] [ON 文件組],Unique選項(xiàng)表示索引的列必須唯一NonClustered表示索引為非聚集索引，默認(rèn)Clustered表示索引為聚集索引FillFactor 用于指定索引葉級(jí)頁的填滿程度Pad_Index用于指定索引非葉級(jí)頁的填滿程度

13、DROP_EXISTING選項(xiàng)用來刪除已存在的同名索引,9.2 創(chuàng)建和管理索引,1．創(chuàng)建聚集索引【任務(wù)9-3】在學(xué)生表的學(xué)號(hào)列上創(chuàng)建的聚集索引。,9.2.1 使用CREATE INDEX語句創(chuàng)建索引,CREATE UNIQUE CLUSTERED INDEX IX_Student_IDON Student ( studentID ),【注意】索引的名稱應(yīng)該遵循標(biāo)識(shí)符命名規(guī)則。一般情況下，聚集索引都創(chuàng)建在主鍵列上。如果需要?jiǎng)?chuàng)建

14、在主鍵列外的列上，應(yīng)在創(chuàng)建主鍵時(shí)指定主鍵列為非聚集索引。,9.2 創(chuàng)建和管理索引,2．創(chuàng)建非聚集索引【任務(wù)9-4】在學(xué)生表的姓名列上創(chuàng)建唯一的非聚集索引。,9.2.1 使用CREATE INDEX語句創(chuàng)建索引,CREATE UNIQUE NONCLUSTERED INDEX IX_Student_NameON Student ( studentName),【注意】非聚集索引是默認(rèn)選項(xiàng)，所以NONCLUSTERED可以省略。帶有

15、唯一選項(xiàng)的索引與唯一約束的功能是一樣的。,9.2 創(chuàng)建和管理索引,3．創(chuàng)建組合索引【任務(wù)9-5】在學(xué)生成績(jī)表的學(xué)生編號(hào)列和課程編號(hào)列上創(chuàng)建組合的聚集索引。,9.2.1 使用CREATE INDEX語句創(chuàng)建索引,CREATE CLUSTERED INDEX IX_Grade_StuID_CouIDON Grade ( studentID, courseID),【注意】組合索引的列必須來自一個(gè)表，且最多只能組合16個(gè)列。在創(chuàng)建索引

16、時(shí)多列的先后順序不一樣將直接影響索引的性能。應(yīng)該將唯一性高的列放在前面，稱之為最高順序。,9.2 創(chuàng)建和管理索引,1．FILLFACTOR選項(xiàng)FILLFACTOR選項(xiàng)用于設(shè)置填充因子的值。所謂填充因子是指葉級(jí)索引頁的填滿程度。 在葉級(jí)索引頁適當(dāng)留出空間，可以減少頁拆分的頻率。填充因子可以用來優(yōu)化包含索引的表中的INSERT和UPDATE語句的性能。,9.2.2 創(chuàng)建索引時(shí)的選項(xiàng),表9-1 FILLFACTOR選項(xiàng)的說明,9.

17、2 創(chuàng)建和管理索引,【任務(wù)9-6】在學(xué)生表的班級(jí)編號(hào)列上創(chuàng)建索引，指定其填充程度為80%。,9.2.2 創(chuàng)建索引時(shí)的選項(xiàng),CREATE CLUSTERED INDEX IX_Student_classIDON Student (classID)WITH FILLFACTOR=80,【注意】?FILLFACTOR 選項(xiàng)僅在索引創(chuàng)建和重建時(shí)才應(yīng)用；?默認(rèn)填充因子為0，但不能顯式指定填充因子0；?可以將填充因子為100，此時(shí)葉級(jí)

18、索引頁完全填充。 僅當(dāng)該表為只讀表時(shí)才可以這樣做；?設(shè)置填充因子可以提高數(shù)據(jù)寫入和更新的效率，但也 會(huì)一定程度上影響了讀的效率。,9.2 創(chuàng)建和管理索引,2．PAD_INDEX選項(xiàng)PAD_INDEX選項(xiàng)指定填充非葉級(jí)索引頁的百分比。 只有在指定了FILLFACTOR選項(xiàng)時(shí)才可以使用。【任務(wù)9-7】在課程表的專業(yè)編號(hào)列上創(chuàng)建索引，指定其葉級(jí)頁和非葉級(jí)頁的填充程度為80%。,9.2.2 創(chuàng)建索引時(shí)的選項(xiàng),CR

19、EATE CLUSTERED INDEX IX_Class_specIDON Class (specialityID)WITH PAD_INDEX, FILLFACTOR=80,9.2 創(chuàng)建和管理索引,1．查看索引信息（1）sp_helpindex存儲(chǔ)過程【任務(wù)9-8】查看學(xué)生表上的索引信息。,9.2.3 使用T-SQL語句管理索引,語法：sp_helpindex 表名,9.2 創(chuàng)建和管理索引,1．查看索引信息（2）s

20、p_help存儲(chǔ)過程【任務(wù)9-9】查看學(xué)生表上的索引信息。,9.2.3 使用T-SQL語句管理索引,語法：sp_help 表名,9.2 創(chuàng)建和管理索引,2．刪除索引【任務(wù)9-10】刪除學(xué)生表姓名列上的索引。,9.2.3 使用T-SQL語句管理索引,DROP INDEX { 表名.索引名 | 視圖名.索引名 } [, ...n],DROP INDEX Student.Ix_Student_NameGO,【注意】?使用DR

21、OP INDEX刪除索引時(shí)必須指定其對(duì)象名，即 該索引所在的表名或視圖名；?不能用DROP INDEX語句刪除主鍵約束或UNIQUE約 束創(chuàng)建的索引，這些索引會(huì)在刪除約束時(shí)自動(dòng)刪除；?刪除聚集索引時(shí),所有表上的非聚集索引會(huì)自動(dòng)被重建；?不能刪除系統(tǒng)表中的索引。,9.2 創(chuàng)建和管理索引,1．創(chuàng)建索引,9.2.4使用SSMS創(chuàng)建和管理索引,9.2 創(chuàng)建和管理索引,2．查看索引信息,9.2.4使用SSMS創(chuàng)建和管

22、理索引,9.2 創(chuàng)建和管理索引,3．刪除索引,9.2.4使用SSMS創(chuàng)建和管理索引,,第9章 索引,,9.1 索引概述9.2 創(chuàng)建和管理索引9.3 索引的分析與維護(hù)習(xí)題與實(shí)驗(yàn),9.3 索引的分析與維護(hù),碎片是如何產(chǎn)生的數(shù)據(jù)行往表中添加或從表中刪除索引列的值發(fā)生改變，SQL Server 調(diào)整索引頁以維護(hù)索引數(shù)據(jù)的存儲(chǔ)——頁拆分管理碎片的方法刪除并重新創(chuàng)建聚集索引并用 FILLFACTOR 選項(xiàng)指定填充因子的值重

23、建索引并指定填充因子的值,9.3 索引的分析與維護(hù),1．SHOWPLAN選項(xiàng)包括SHOWPLAN_ALL和SHOWPLAN_TEXT兩個(gè)選項(xiàng)。打開該選項(xiàng)將不執(zhí)行其后的T-SQL語句，SQL Server將返回有關(guān)語句執(zhí)行方式和預(yù)計(jì)所需資源的詳細(xì)信息。其語法格式為：,9.3.1索引的分析,SET SHOWPLAN_ALL { ON | OFF } SET SHOWPLAN_TEXT { ON | OFF },9.3 索引的分析

24、與維護(hù),【任務(wù)9-13】用戶需要查詢“08計(jì)算機(jī)1”班學(xué)生的學(xué)號(hào)、姓名和出生日期，請(qǐng)首先顯示查詢處理過程，查看索引的使用情況。,9.3.1索引的分析,?SHOWPLAN語句只返回查詢處理情況,不返回查詢結(jié)果；?SHOWPLAN_ALL與SHOWPLAN_TEXT的功能基本一樣。?SHOWPLAN選項(xiàng)打開后必須關(guān)閉，否則后面所有的語句 都會(huì)只顯示查詢處理的情況，不返回查詢結(jié)果。,9.3 索引的分析與維護(hù),2．STATIS

25、TICS IO選項(xiàng)用來顯示執(zhí)行查詢語句所花費(fèi)的磁盤活動(dòng)量統(tǒng)計(jì)，即查詢過程中所產(chǎn)生的磁盤讀寫次數(shù)。可以利用這些信息來確定是否重新設(shè)計(jì)索引。其語法格式為：,9.3.1索引的分析,SET STATISTICS IO { ON | OFF },9.3 索引的分析與維護(hù),【任務(wù)9-14】用戶需要查詢“08計(jì)算機(jī)1”班學(xué)生的學(xué)號(hào)、姓名和出生日期，并查看磁盤活動(dòng)情況。,9.3.1索引的分析,9.3 索引的分析與維護(hù),3．DBCC SHOWC

26、ONTIG語句用來顯示指定表的數(shù)據(jù)和索引的碎片情況當(dāng)向表中添加或刪除了大量數(shù)據(jù)的時(shí)候使用。其語法格式為：其中，索引名、索引ID可以省略，此時(shí)將返回該表或視圖上的聚集索引的碎片情況；如果將表名和索引名都省略，則返回當(dāng)前數(shù)據(jù)庫中所有表上聚集索引的碎片情況。,9.3.1索引的分析,DBCC SHOWCONTIG [({表名|表ID|視圖名|視圖ID} [,索引名 | 索引 ID])],9.3 索引的分析與維

27、護(hù),【任務(wù)9-15】查看學(xué)生表的聚集索引的碎片情況。,9.3.1索引的分析,表9-2 DBCC SHOWCONTIG語句返回的統(tǒng)計(jì)信息,9.3 索引的分析與維護(hù),1．使用WITH DROP_EXISTING選項(xiàng)重建索引【任務(wù)9-16】重建學(xué)生表姓名列的非聚集索引來重建索引，并修改其填充因子為70。,9.3.2 索引的維護(hù),9.3 索引的分析與維護(hù),2．使用ALTER INDEX REORGANIZE語句重組索引實(shí)現(xiàn)重組索引的功

28、能，以達(dá)到整理索引碎片的目的。 該語句按邏輯順序重新排序索引的葉級(jí)頁。 由于這是聯(lián)機(jī)操作，因此在語句運(yùn)行時(shí)仍可使用索引。中斷此操作時(shí)不會(huì)丟失已經(jīng)完成的任務(wù)。 缺點(diǎn)是在重新組織數(shù)據(jù)方面不如重建索引操作的效果好，而且不更新統(tǒng)計(jì)信息。,9.3.2 索引的維護(hù),ALTER INDEX { 索引名 | ALL } ON REORGANIZE,【任務(wù)9-17】重組學(xué)生表班級(jí)編號(hào)列的非聚集索引以整理索引碎片。,9.3 索引的分析與維護(hù)

29、,9.3.2 索引的維護(hù),?在SQL Server 2005中，索引包含行鎖和頁鎖這兩個(gè)選項(xiàng)。 如果未打開這兩個(gè)選項(xiàng)，該語句將執(zhí)行失?。?該語句是聯(lián)機(jī)操作，不影響用戶使用；?該語句具備收縮大對(duì)象的作用，該功能是該語句不具備的。,3．使用ALTER INDEX REBUILD語句重建索引它將首先不刪除現(xiàn)有索引，重新申請(qǐng)空間建立一個(gè)新的索引，完成后刪除舊索引。 ALTER INDEX REBUILD語句是一個(gè)聯(lián)機(jī)操作，將

30、不影響用戶的使用。 缺點(diǎn)：將以占用較多的系統(tǒng)資源，并且需要更多時(shí)間。,9.3 索引的分析與維護(hù),9.3.2 索引的維護(hù),ALTER INDEX { 索引名 | ALL } ON REBUILD,【任務(wù)9-17】重組學(xué)生表班級(jí)編號(hào)列的非聚集索引以整理索引碎片。,9.3 索引的分析與維護(hù),9.3.2 索引的維護(hù),統(tǒng)計(jì)信息是存儲(chǔ)在SQL Server中的列數(shù)據(jù)的樣本。 一般地用于索引列，也可以為非索引列創(chuàng)建統(tǒng)計(jì)。 查詢的優(yōu)

31、化依賴于這些統(tǒng)計(jì)信息的分布準(zhǔn)確度。 查詢優(yōu)化器使用這些數(shù)據(jù)樣本來決定是否使用索引。 1．查看統(tǒng)計(jì)信息可以通過執(zhí)行DBCC SHOW_STATISTICS語句查看索引或列的統(tǒng)計(jì)信息。語法為：,9.3 索引的分析與維護(hù),9.3.3關(guān)于統(tǒng)計(jì)信息,DBCC SHOW_STATISTICS ( 表名, 索引名 ),【任務(wù)9-19】查看學(xué)生表主鍵列上的聚集索引的統(tǒng)計(jì)信息。,9.3 索引的分析與維護(hù),9.3.3關(guān)于統(tǒng)計(jì)信息,表9-3 DBC

32、C SHOW_STATISTICS語句的返回信息,,,2．創(chuàng)建統(tǒng)計(jì)信息的方法（1）自動(dòng)創(chuàng)建統(tǒng)計(jì)信息建立索引后，就會(huì)出現(xiàn)一個(gè)同名的統(tǒng)計(jì)信息。（2）手動(dòng)創(chuàng)建統(tǒng)計(jì)信息手動(dòng)創(chuàng)建統(tǒng)計(jì)信息的語法如下：,9.3 索引的分析與維護(hù),9.3.3關(guān)于統(tǒng)計(jì)信息,CREATE STATISTICS 統(tǒng)計(jì)信息名 ON { 表名 | 視圖名 } ( 列 [,...n] ),3．更新統(tǒng)計(jì)信息（1）自動(dòng)更新統(tǒng)計(jì)信息SQL Server數(shù)據(jù)選項(xiàng)“自動(dòng)更

33、新統(tǒng)計(jì)信息”設(shè)置為打開（2）手動(dòng)更新統(tǒng)計(jì)信息（3）更新指定數(shù)據(jù)庫所有對(duì)象上的統(tǒng)計(jì)信息時(shí),9.3 索引的分析與維護(hù),9.3.3關(guān)于統(tǒng)計(jì)信息,UPDATE STATISTICS 表名 | 視圖名 [ 索引名| (統(tǒng)計(jì)信息名 [,...n])],EXEC sp_updatestats [[@resample =] 'resample'],第9章 索引,,本章小結(jié)：索引的概念、優(yōu)缺點(diǎn)索引的分類及結(jié)構(gòu)索引的創(chuàng)建