2023年全國碩士研究生考試考研英語一試題真題(含答案詳解+作文范文)_第1頁
已閱讀1頁,還剩49頁未讀, 繼續(xù)免費閱讀

下載本文檔

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

文檔簡介

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

2、引概述,9.1.1 索引的概念,索引的定義 索引是一種為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的分散存儲結構。 SQL Server中的索引與書的目錄很類似 索引是基于表中的數(shù)據(jù)創(chuàng)建的,它是由除存放表的數(shù)據(jù)頁面以外的索引頁面構成。每個索引頁面中的行都含有邏輯指針,以便加速檢索物理數(shù)據(jù)。 對表中的列是否創(chuàng)建索引以及創(chuà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 索引的結構與分類,,,索引的結構,9.1 索引概述,9.1.3 索引的結構與分類,1、索引的結構——B樹,索引的

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

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

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

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

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

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

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

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

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

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

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

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

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

18、索引頁完全填充。 僅當該表為只讀表時才可以這樣做;?設置填充因子可以提高數(shù)據(jù)寫入和更新的效率,但也 會一定程度上影響了讀的效率。,9.2 創(chuàng)建和管理索引,2.PAD_INDEX選項PAD_INDEX選項指定填充非葉級索引頁的百分比。 只有在指定了FILLFACTOR選項時才可以使用?!救蝿?-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存儲過程【任務9-8】查看學生表上的索引信息。,9.2.3 使用T-SQL語句管理索引,語法:sp_helpindex 表名,9.2 創(chuàng)建和管理索引,1.查看索引信息(2)s

20、p_help存儲過程【任務9-9】查看學生表上的索引信息。,9.2.3 使用T-SQL語句管理索引,語法:sp_help 表名,9.2 創(chuàng)建和管理索引,2.刪除索引【任務9-10】刪除學生表姓名列上的索引。,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 索引的分析與維護習題與實驗,9.3 索引的分析與維護,碎片是如何產(chǎn)生的數(shù)據(jù)行往表中添加或從表中刪除索引列的值發(fā)生改變,SQL Server 調整索引頁以維護索引數(shù)據(jù)的存儲——頁拆分管理碎片的方法刪除并重新創(chuàng)建聚集索引并用 FILLFACTOR 選項指定填充因子的值重

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

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

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

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

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

28、能,以達到整理索引碎片的目的。 該語句按邏輯順序重新排序索引的葉級頁。 由于這是聯(lián)機操作,因此在語句運行時仍可使用索引。中斷此操作時不會丟失已經(jīng)完成的任務。 缺點是在重新組織數(shù)據(jù)方面不如重建索引操作的效果好,而且不更新統(tǒng)計信息。,9.3.2 索引的維護,ALTER INDEX { 索引名 | ALL } ON REORGANIZE,【任務9-17】重組學生表班級編號列的非聚集索引以整理索引碎片。,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,【任務9-17】重組學生表班級編號列的非聚集索引以整理索引碎片。,9.3 索引的分析與維護,9.3.2 索引的維護,統(tǒng)計信息是存儲在SQL Server中的列數(shù)據(jù)的樣本。 一般地用于索引列,也可以為非索引列創(chuàng)建統(tǒng)計。 查詢的優(yōu)

31、化依賴于這些統(tǒng)計信息的分布準確度。 查詢優(yōu)化器使用這些數(shù)據(jù)樣本來決定是否使用索引。 1.查看統(tǒng)計信息可以通過執(zhí)行DBCC SHOW_STATISTICS語句查看索引或列的統(tǒng)計信息。語法為:,9.3 索引的分析與維護,9.3.3關于統(tǒng)計信息,DBCC SHOW_STATISTICS ( 表名, 索引名 ),【任務9-19】查看學生表主鍵列上的聚集索引的統(tǒng)計信息。,9.3 索引的分析與維護,9.3.3關于統(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關于統(tǒng)計信息,CREATE STATISTICS 統(tǒng)計信息名 ON { 表名 | 視圖名 } ( 列 [,...n] ),3.更新統(tǒng)計信息(1)自動更新統(tǒng)計信息SQL Server數(shù)據(jù)選項“自動更

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

溫馨提示

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

評論

0/150

提交評論