sql 數(shù)據(jù)庫 第九章 SQL Server 2008索引.ppt

1、SQL Server 數(shù)據(jù)庫程序設(shè)計(jì),授課教師：姜 姍,本章學(xué)習(xí)目標(biāo)理解索引的作用和分類；熟練掌握索引的創(chuàng)建、編輯和刪除；熟練掌握索引的管理方法。,第9章 索引,在應(yīng)用系統(tǒng)中，尤其在聯(lián)機(jī)事務(wù)處理系統(tǒng)中，對(duì)數(shù)據(jù)查詢及處理速度已成為衡量應(yīng)用系統(tǒng)成敗的標(biāo)準(zhǔn)。而采用索引來加快數(shù)據(jù)處理速度通常是最普遍采用的優(yōu)化方法。,索引的概念,數(shù)據(jù)庫中的索引與書籍中的目錄類似。 在一本書中，利用索引可以快速查找所需信息，無須閱讀整本書。書中的目錄是一個(gè)詞語列表，其中注明了包含各個(gè)詞的頁碼。 在數(shù)據(jù)庫中，索引使數(shù)據(jù)庫程序無須對(duì)整個(gè)表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)。而數(shù)據(jù)庫中的索引是一個(gè)表中所包含的值的列表，其中注明

2、了表中包含各個(gè)值的行所在的存儲(chǔ)位置。,SQL Server中數(shù)據(jù)的訪問方法： 表掃描法：當(dāng)訪問未建索引的表內(nèi)數(shù)據(jù)時(shí)，從表的起始處逐行查找，直到符合查詢條件為止。 使用索引：當(dāng)使用索引訪問建有索引的表內(nèi)數(shù)據(jù)時(shí),系統(tǒng)會(huì)通過遍歷索引樹結(jié)構(gòu)來查找行的存儲(chǔ)位置，效率非常高。,通過創(chuàng)建唯一索引，可以增強(qiáng)數(shù)據(jù)記錄的唯一性。 可以大大加快數(shù)據(jù)檢索速度。 可以加速表與表之間的連接，這一點(diǎn)在實(shí)現(xiàn)數(shù)據(jù)的參照完整性方面有特別的意義。 在使用ORDER BY和GROUP BY子句中進(jìn)行檢索數(shù)據(jù)時(shí)，可以顯著減少查詢中分組和排序的時(shí)間。 使用索引可以在檢索數(shù)據(jù)的過程中使用優(yōu)化隱藏器，提高系統(tǒng)性能。,索引的作用,不過，索引為

3、性能所帶來的好處卻是有代價(jià)的。帶索引的表在數(shù)據(jù)庫中會(huì)占據(jù)更多的空間。另外，為了維護(hù)索引，對(duì)數(shù)據(jù)進(jìn)行插入、更新、刪除操作的命令所花費(fèi)的時(shí)間會(huì)更長(zhǎng)。 創(chuàng)建索引所需的工作空間約為數(shù)據(jù)庫表的1.2倍，在建立索引時(shí)，數(shù)據(jù)被復(fù)制以便建立索引。索引建立后，舊的未加索引的表被刪除，創(chuàng)建索引時(shí)使用的硬盤空間由系統(tǒng)自動(dòng)收回。 在設(shè)計(jì)和創(chuàng)建索引時(shí)，應(yīng)確保對(duì)性能的提高程度大于在存儲(chǔ)空間和處理資源方面的代價(jià)。,索引的注意事項(xiàng),因創(chuàng)建索引要耗一定的系統(tǒng)性能，因此要考察對(duì)某列創(chuàng)建索引的必要性。,如果以存儲(chǔ)結(jié)構(gòu)來區(qū)分，則有“聚集索引”（Clustered Index，也稱聚類索引、簇集索引）和“非聚集索引”（Nonclust

4、ered Index，也稱非聚類索引、非簇集索引）的區(qū)別； 如果以數(shù)據(jù)的唯一性來區(qū)別，則有“唯一索引”（Unique Index）和“非唯一索引”（Nonunique Index）的不同； 若以鍵列的個(gè)數(shù)來區(qū)分，則有“單列索引”與“多列索引”的分別。,索引的分類,1聚集索引聚集索引將數(shù)據(jù)行的鍵值在表內(nèi)排序并存儲(chǔ)對(duì)應(yīng)的數(shù)據(jù)記錄，使得數(shù)據(jù)表物理順序與索引順序一致。當(dāng)以某字段作為關(guān)鍵字建立聚集索引時(shí)，表中數(shù)據(jù)以該字段作為排序根據(jù)。因此，一個(gè)表只能建立一個(gè)聚集索引，但該索引可以包含多個(gè)列（組合索引）,2非聚集索引非聚集索引完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。數(shù)據(jù)存儲(chǔ)在一個(gè)地方，索引存儲(chǔ)在另一個(gè)地方。非聚集索引中的

5、數(shù)據(jù)排列順序并不是表格中數(shù)據(jù)的排列順序。 SQL Server默認(rèn)情況下建立的索引是非聚集索引。一個(gè)表可以擁有多個(gè)非聚集索引，每個(gè)非聚集索引提供訪問數(shù)據(jù)的不同排序順序。,關(guān)于非聚集索引 非聚簇索引提高的存取速度，但降低了表的更新的速度 如果硬盤和內(nèi)存空間有限，應(yīng)限制非聚簇索引的使用 修改一個(gè)表的數(shù)據(jù)時(shí)，同時(shí)要維護(hù)索引,聚集型索引和非聚集型索引的比較,3唯一索引唯一索引是指索引值必須是唯一的。聚集索引和非聚集索引均可用于強(qiáng)制表內(nèi)的唯一性，方法是在現(xiàn)有表上創(chuàng)建索引時(shí)指定UNIQUE關(guān)鍵字。確保表內(nèi)唯一性的另一種方法是使用UNIQUE約束。,4索引視圖對(duì)視圖創(chuàng)建唯一聚集索引后，結(jié)果集將存儲(chǔ)在數(shù)據(jù)庫中

6、，就像帶有聚集索引的表一樣，這樣的視圖稱為索引視圖，即是為了實(shí)現(xiàn)快速訪問而將其結(jié)果持續(xù)存放于數(shù)據(jù)庫內(nèi)并創(chuàng)建索引的視圖。 索引視圖在基礎(chǔ)數(shù)據(jù)不經(jīng)常更新的情況下效果最佳。維護(hù)索引視圖的成本可能高于維護(hù)表索引的成本。如果基礎(chǔ)數(shù)據(jù)更新頻繁，索引視圖數(shù)據(jù)的維護(hù)成本就可能超過使用索引視圖帶來的性能收益。,5全文索引全文索引可以對(duì)存儲(chǔ)在數(shù)據(jù)庫中的文本數(shù)據(jù)進(jìn)行快速檢索。全文索引是一種特殊類型的基于標(biāo)記的功能性索引，它是由 SQL Server 全文引擎生成和維護(hù)的。 每個(gè)表只允許有一個(gè)全文索引。,1系統(tǒng)自動(dòng)創(chuàng)建索引系統(tǒng)在創(chuàng)建表中的其他對(duì)象時(shí)可以附帶地創(chuàng)建新索引。通常情況下，在創(chuàng)建UNIQUE約束或PRIMAR

7、Y KEY約束時(shí)，SQL Server會(huì)自動(dòng)為這些約束列創(chuàng)建聚集索引。 2用戶創(chuàng)建索引除了系統(tǒng)自動(dòng)生成的索引外，也可以根據(jù)實(shí)際需要，使用對(duì)象資源管理器或利用SQL語句中的CREATE INDEX命令直接創(chuàng)建索引。,創(chuàng)建索引的方法,利用資源管理器創(chuàng)建索引,語法形式 CREATE UNIQUE /*是否為唯一索引*/ CLUSTERED | NONCLUSTERED /*索引的組織方式*/ INDEX index_name /*索引名稱*/ ON table | view ( column ASC | DESC ,.n ) /*指定索引定義依據(jù)的對(duì)象*/ WITH /*索引選項(xiàng)*/ , FILLF

8、ACTOR = fillfactor , IGNORE_DUP_KEY , DROP_EXISTING , STATISTICS_NORECOMPUTE , SORT_IN_TEMPDB ON filegroup /*指定索引文件所在的文件組*/,默認(rèn)值,命令方式創(chuàng)建索引,參數(shù)說明如下：CLUSTERED：用于指定創(chuàng)建的索引為聚集索引。NONCLUSTERED：用于指定創(chuàng)建的索引為非聚集索引。ASC|DESC：用于指定某個(gè)具體索引列的升序或降序排序方式。FILLFACTOR：填充因子，或填充率。 IGNORE_DUP_KEY:當(dāng)向包含于一個(gè)唯一聚集索引的列中插入重復(fù)數(shù)據(jù)時(shí)，將忽略該insert

9、或update語句。DROP_EXISTING：用于指定應(yīng)刪除并重新創(chuàng)建同名的先前存在的聚集索引或非聚集索引。STATISTICS_NORECOMPUTE：用于指定過期的索引統(tǒng)計(jì)不自動(dòng)重新計(jì)算。SORT_IN_TEMPDB：用于指定創(chuàng)建索引時(shí)的中間排序結(jié)果將存儲(chǔ)在tempdb數(shù)據(jù)庫中。,【例9-1】使用CREATE INDEX語句為表stu_info創(chuàng)建一個(gè)非聚集索引，索引字段為name，索引名為idx_name。 CREATE INDEX idx_name ON stu_info ( name ),例1：根據(jù)student表的學(xué)號(hào)和姓名列創(chuàng)建索引idx_xhxm。 Use xskc Crea

10、te Index idx_xhxm on student(sno,sname),例2：根據(jù)sc表的學(xué)號(hào)列創(chuàng)建唯一聚集索引。如果輸入重復(fù)鍵值，將忽略該insert或update語句。 Create unique clustered Index idx_sno_unique on sc (sno) with ignore_dup_key,例3：根據(jù)sc表的學(xué)號(hào)創(chuàng)建索引，使用降序排列，填滿率為60。 Create Index idx_sno on sc(sno desc) with filefacter = 60,【例9-2】使用CREATE INDEX語句為表course_info創(chuàng)建一個(gè)唯一聚集

11、索引，索引字段為course_id，索引名為idx_course_id，要求成批插入數(shù)據(jù)時(shí)忽略重復(fù)值，不重新計(jì)算統(tǒng)計(jì)信息，填充因子取40。 CREATE UNIQUE CLUSTERED INDEX idx_course_id ON course_info ( course_id ) WITH PAD_INDEX,FILLFACTOR = 40,IGNORE_DUP_KEY ,STATISTICS_NORECOMPUTE,1利用對(duì)象資源管理器查看索引定義,管理索引,2利用系統(tǒng)存儲(chǔ)過程查看索引定義利用系統(tǒng)提供的存儲(chǔ)過程sp_helpindex可以查看索引信息，其語法格式如下：sp_helpind

12、ex objname = object_name,其中， objname = object_name 表示所要查看的當(dāng)前數(shù)據(jù)庫中表的名稱。,例：查看xskc數(shù)據(jù)庫中student表的索引信息。Exec sp_helpindex student,1利用對(duì)象資源管理器更名索引（1）啟動(dòng)SQL Server Management Studio。（2）在對(duì)象資源管理器窗口里，展開SQL Server實(shí)例，選擇“數(shù)據(jù)庫”| student |“表”| dbo.stu_info |“索引”| idx_name，單擊鼠標(biāo)右鍵，然后從彈出的快捷菜單中選擇“重命名”命令。3）所要更名索引的索引名處于編輯狀態(tài)，輸

13、入新的索引名稱。,更名索引,2利用系統(tǒng)存儲(chǔ)過程更名索引利用系統(tǒng)提供的存儲(chǔ)過程sp_rename可以對(duì)索引進(jìn)行重命名 例：將student表中的索引idx_name更名為idx_stu_name。 Exec sp_rename student.idx_name ,idx_stu_name,1利用對(duì)象管理器刪除索引 選擇“數(shù)據(jù)庫”| student |“表”| dbo.stu_info |“索引”| idx_name，單擊鼠標(biāo)右鍵，然后從彈出的快捷菜單中選擇“刪除”命令，打開“刪除對(duì)象”對(duì)話框。,刪除索引,2利用T-SQL語句刪除索引刪除索引的語法格式如下：DROP INDEX table_nam

14、e.index_name ，n 其中，index_name為所要?jiǎng)h除的索引的名稱。刪除索引時(shí)，不僅要指定索引，而且必須要指定索引所屬的表。,【例9-5】刪除stu_info表中的idx_name索引。DROP INDEX stu_info.idx_nameDROP INDEX不能刪除系統(tǒng)自動(dòng)創(chuàng)建的索引，如主鍵或唯一性約束索引，也不能刪除系統(tǒng)表中的索引。,某些不合適的索引影響到SQL Server的性能，隨著應(yīng)用系統(tǒng)的運(yùn)行，數(shù)據(jù)不斷地發(fā)生變化，當(dāng)數(shù)據(jù)變化達(dá)到某一個(gè)程度時(shí)將會(huì)影響到索引的使用。這時(shí)需要對(duì)索引進(jìn)行維護(hù)。索引的維護(hù)包括重建索引和更新索引統(tǒng)計(jì)信息。,維護(hù)索引,隨著另外應(yīng)用在執(zhí)行大塊I/O

15、的時(shí)候，重建非聚集索引可以降低分片，重建索引實(shí)際上是重新組織B-樹空間。無論何時(shí)對(duì)基礎(chǔ)數(shù)據(jù)執(zhí)行插入、更新或刪除操作，SQL Server 2008數(shù)據(jù)庫引擎都會(huì)自動(dòng)維護(hù)索引。在SQL Server 2008中，可以通過重新組織索引或重新生成索引來修復(fù)索引碎片，維護(hù)大塊I/O的效率。SQL Server提供了多種維護(hù)索引的方法。,重建索引,1檢查整理索引碎片使用DBCC SHOWCONTIG檢查有無索引碎片，或使用DBCC INDEXDEFRAG整理索引碎片。DBCC SHOWCONTIG語句用來顯示指定表的數(shù)據(jù)和索引的碎片信息。當(dāng)對(duì)表進(jìn)行大量的修改或添加數(shù)據(jù)之后，應(yīng)該執(zhí)行此語句來查看有無碎片。

16、,檢查碎片,其語法格式如下：DBCC SHOWCONTIG ( table_name | table_id | view_name | view_id , index_name | index_id )例：檢查student表的索引idx_stu_name的碎片信息。DBCC SHOWCONTIG ( student, idx_stu_name ),整理碎片,DBCC INDEXDEFRAG( database_name | database_id, table_name | table_id | view_name | view_id , index_name | index_id ) 【例

17、9-7】整理student數(shù)據(jù)庫中stu_info表的索引idx_name上的碎片。DBCC INDEXDEFRAG ( student, stu_info, idx_name ),2重新組織索引重新組織索引是通過對(duì)葉級(jí)頁進(jìn)行物理重新排序，使其與葉節(jié)點(diǎn)的邏輯順序（從左到右）相匹配，從而對(duì)表或視圖的聚集索引和非聚集索引的葉級(jí)別進(jìn)行碎片整理，使頁有序可以提高索引掃描的性能。,使用ALTER INDEX REORGANIZE按邏輯順序重新排序索引的葉級(jí)頁。由于這是聯(lián)機(jī)操作，因此在語句運(yùn)行時(shí)仍可使用索引。此方法的缺點(diǎn)是在重新組織數(shù)據(jù)方面不如索引重新生成操作的效果好，而且不更新統(tǒng)計(jì)信息。,3重新生成索引

18、重新生成索引將刪除原索引并創(chuàng)建一個(gè)新索引。此過程中將刪除碎片，通過使用指定的或現(xiàn)有的填充因子設(shè)置壓縮頁來回收磁盤空間，并在連續(xù)頁中對(duì)索引行重新排序（根據(jù)需要分配新頁）?？梢允褂脙煞N方法重新生成聚集索引和非聚集索引：帶 REBUILD 子句的 ALTER INDEX。帶 DROP_EXISTING 子句的 CREATE INDEX。這種方法的缺點(diǎn)是索引在刪除和重新創(chuàng)建周期內(nèi)為脫機(jī)狀態(tài)，并且操作屬原子級(jí)。如果中斷索引創(chuàng)建，則不會(huì)重新創(chuàng)建該索引。,當(dāng)在一個(gè)包含數(shù)據(jù)的表上創(chuàng)建索引的時(shí)候，SQL Server會(huì)創(chuàng)建分布數(shù)據(jù)頁來存放有關(guān)索引的兩種統(tǒng)計(jì)信息：分布表和密度表。優(yōu)化器利用這個(gè)頁來判斷該索引對(duì)某個(gè)特定查詢是否有用。當(dāng)表的數(shù)據(jù)改變之后，統(tǒng)計(jì)信息有可能是過時(shí)的，從而影響優(yōu)化器追求最有工作的目標(biāo)。因此，需要對(duì)索引統(tǒng)計(jì)信息進(jìn)行更新。,索引統(tǒng)計(jì)信息的更新,其語