第9章 索引

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

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

3、1）索引可以加速查詢（2）加快表與表之間的連接 （3）在包含分組和排序的查詢中，減少分組排序時間（4）有利于SQL Server進行查詢優(yōu)化（5）可以強制實施唯一性約束創(chuàng)建索引的缺點（1）創(chuàng)建索引和維護索引要耗費時間（2）索引需要占一定的物理空間（3）索引會降低數(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ù)的物理順序;每個表只能有一個聚集索引;數(shù)據(jù)頁就是聚集索引樹的葉級頁;行的物理存儲順序和索引的邏輯順序完全相同;對于那些經(jīng)常要搜索范圍值的列特別有效。 表上如果存在主鍵，主鍵列即自動會創(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)會創(chuàng)建聚集索引。特殊情況下我們需要考慮以下情況建立聚集索引：經(jīng)常按范圍查詢的列；經(jīng)常用于分組和排序的列；在連接中常用的列。在創(chuàng)建聚集索引時應(yīng)注意以下事項：每張表只能包含一個聚集索引，但可以是多列的組合；由于聚集索引會改變表中數(shù)據(jù)的物理順序，所以應(yīng)該先創(chuàng)建聚集索引，后建立非聚集索引；不能在頻繁修改的列上創(chuàng)建

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

7、點包含了具有指針的行標(biāo)識符，由文件ID、頁碼行頁上的行數(shù)構(gòu)成。當(dāng)在已經(jīng)有聚集索引的表上建立非聚集索引的時候，每個非聚集索引的行指示器包含了行的聚集索引鍵值。,,,非聚集索引的結(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)建非聚集索引需要考慮以下一些事項：創(chuàng)建索引時，默認(rèn)為非聚集索引；對每個表最多可以建立249個非聚集索引；非聚集索引需要占用一定的磁盤空間；會一定程度上降低向表中插入和更新數(shù)據(jù)的速度；若表上的聚集索引發(fā)生改變（新建或刪除），將重建表上現(xiàn)有的非聚集索引。,9.1 索引概述,9.1.4 設(shè)計數(shù)據(jù)表的索引,1．考慮創(chuàng)建索引的列（1）主鍵列上（默認(rèn)創(chuàng)建一個聚集索引）；（2）經(jīng)常用在連接的列上；（3） 經(jīng)常需要進行范

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

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

11、專業(yè)編號和班長編號是外鍵列，經(jīng)常用于連接。,聚集索引：班級編號列上創(chuàng)建主鍵，默認(rèn)創(chuàng)建聚集索引； 聚集索引：班級名稱、專業(yè)編號、班長編號分別創(chuàng)建非聚集索引。,第9章 索引,,9.1 索引概述9.2 創(chuàng)建和管理索引9.3 索引的分析與維護習(xí)題與實驗,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選項表示索引的列必須唯一NonClustered表示索引為非聚集索引，默認(rèn)Clustered表示索引為聚集索引FillFactor 用于指定索引葉級頁的填滿程度Pad_Index用于指定索引非葉級頁的填滿程度

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

14、在主鍵列外的列上，應(yīng)在創(chuàng)建主鍵時指定主鍵列為非聚集索引。,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)選項，所以NONCLUSTERED可以省略。帶有

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

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

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

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

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

20、p_help存儲過程【任務(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刪除索引時必須指定其對象名，即 該索引所在的表名或視圖名；?不能用DROP INDEX語句刪除主鍵約束或UNIQUE約 束創(chuà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 索引的分析與維護習(xí)題與實驗,9.3 索引的分析與維護,碎片是如何產(chǎn)生的數(shù)據(jù)行往表中添加或從表中刪除索引列的值發(fā)生改變，SQL Server 調(diào)整索引頁以維護索引數(shù)據(jù)的存儲——頁拆分管理碎片的方法刪除并重新創(chuàng)建聚集索引并用 FILLFACTOR 選項指定填充因子的值重

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

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

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

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

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

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

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

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

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

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

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