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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

15、的時候，重建非聚集索引可以降低分片，重建索引實際上是重新組織B-樹空間。無論何時對基礎數(shù)據執(zhí)行插入、更新或刪除操作，SQL Server 2008數(shù)據庫引擎都會自動維護索引。在SQL Server 2008中，可以通過重新組織索引或重新生成索引來修復索引碎片，維護大塊I/O的效率。SQL Server提供了多種維護索引的方法。,重建索引,1檢查整理索引碎片使用DBCC SHOWCONTIG檢查有無索引碎片，或使用DBCC INDEXDEFRAG整理索引碎片。DBCC SHOWCONTIG語句用來顯示指定表的數(shù)據和索引的碎片信息。當對表進行大量的修改或添加數(shù)據之后，應該執(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ù)據庫中stu_info表的索引idx_name上的碎片。DBCC INDEXDEFRAG ( student, stu_info, idx_name ),2重新組織索引重新組織索引是通過對葉級頁進行物理重新排序，使其與葉節(jié)點的邏輯順序（從左到右）相匹配，從而對表或視圖的聚集索引和非聚集索引的葉級別進行碎片整理，使頁有序可以提高索引掃描的性能。,使用ALTER INDEX REORGANIZE按邏輯順序重新排序索引的葉級頁。由于這是聯(lián)機操作，因此在語句運行時仍可使用索引。此方法的缺點是在重新組織數(shù)據方面不如索引重新生成操作的效果好，而且不更新統(tǒng)計信息。,3重新生成索引

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