sql 數(shù)據庫 第九章 SQL Server 2008索引.ppt_第1頁
sql 數(shù)據庫 第九章 SQL Server 2008索引.ppt_第2頁
sql 數(shù)據庫 第九章 SQL Server 2008索引.ppt_第3頁
sql 數(shù)據庫 第九章 SQL Server 2008索引.ppt_第4頁
sql 數(shù)據庫 第九章 SQL Server 2008索引.ppt_第5頁
已閱讀5頁,還剩43頁未讀, 繼續(xù)免費閱讀

下載本文檔

版權說明:本文檔由用戶提供并上傳,收益歸屬內容提供方,若內容存在侵權,請進行舉報或認領

文檔簡介

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)計信息的更新,其語

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網頁內容里面會有圖紙預覽,若沒有圖紙預覽就沒有圖紙。
  • 4. 未經權益所有人同意不得將文件中的內容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網僅提供信息存儲空間,僅對用戶上傳內容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內容本身不做任何修改或編輯,并不能對任何下載內容負責。
  • 6. 下載文件中如有侵權或不適當內容,請與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準確性、安全性和完整性, 同時也不承擔用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

最新文檔

評論

0/150

提交評論