《索引和視圖》PPT課件.ppt

《《索引和視圖》PPT課件.ppt》由會員分享，可在線閱讀，更多相關(guān)《《索引和視圖》PPT課件.ppt（55頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、第6章 索引和視圖,6.1 索引 6.2 視圖,6.1 索引,6.1.1 索引基本概念 6.1.2 索引的存儲結(jié)構(gòu)及分類 6.1.3 創(chuàng)建和刪除索引,2020年7月29日3時(shí)6分,2,6.1 索引基本概念,索引與書籍中的目錄類似。 索引使對數(shù)據(jù)的查找不需要對整個表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)。 可以為表中的單個列建立索引，也可以為一組列（索引項(xiàng)）建立索引。 索引一般采用B樹結(jié)構(gòu)。,2020年7月29日3時(shí)6分,3,索引及數(shù)據(jù)間的對應(yīng)關(guān)系示意圖,,索引的組織方式,索引項(xiàng)按數(shù)據(jù)頁(一塊固定大小的連續(xù)存儲空間)存儲。 表中的全部索引連在一起。,6.1.2 索引的存儲結(jié)構(gòu)及分類,聚集索引（Clu

2、stered Index，也稱為聚簇索引） 將數(shù)據(jù)按照索引項(xiàng)的順序進(jìn)行物理排序。 非聚集索引（Non-clustered Index，也稱為非聚簇索引）。 不對數(shù)據(jù)進(jìn)行物理排序。,兩類索引相同點(diǎn),聚集索引和非聚集索引一般都使用B-樹結(jié)構(gòu)來存儲索引項(xiàng)， 都包含數(shù)據(jù)頁和索引頁， 索引頁用來存放索引項(xiàng)和指向下一層的指針， 數(shù)據(jù)頁用來存放數(shù)據(jù)。,B-樹結(jié)構(gòu),,聚集索引,聚集索引的B-樹按自下而上建立，最下層的葉級節(jié)點(diǎn)存放數(shù)據(jù)，它同時(shí)也是數(shù)據(jù)頁。 多個數(shù)據(jù)頁生成一個中間層節(jié)點(diǎn)的索引頁，然后再由數(shù)個中間層的節(jié)點(diǎn)的索引頁合成更上層的索引頁， 如此上推，直到生成頂層的根節(jié)點(diǎn)的索引頁。,9,建有聚集索引的表的存

3、儲結(jié)構(gòu)示意圖,數(shù)據(jù)示例,聚集索引,非聚集索引,聚集索引示例,12,,數(shù)據(jù)示例,查找過程,當(dāng)在建有聚集索引的列上查找數(shù)據(jù)時(shí) 首先從聚集索引樹的入口（根節(jié)點(diǎn)）開始逐層向下查找， 直到達(dá)到B-樹索引的葉級，也就是達(dá)到了要找的數(shù)據(jù)所在的數(shù)據(jù)頁， 最后只在這個數(shù)據(jù)頁中查找所需數(shù)據(jù),查找示例,SELECT * FROM employee WHERE eno=E08,,,,,,,說明,在聚集索引的葉節(jié)點(diǎn)中，數(shù)據(jù)按聚集索引項(xiàng)的值進(jìn)行物理排序。 因此，聚集索引很類似于電話號碼簿。 一個表只能包含一個聚集索引。 但一個索引可以由多個列（組合索引）組成。,下列情況可考慮創(chuàng)建聚集索,包含大量非重復(fù)值的列。 使用下列運(yùn)

4、算符返回一個范圍值的查詢：BETWEEN AND、、=、< 和 <=。 被連續(xù)訪問的列。 不返回大型結(jié)果集的查詢。 經(jīng)常被用作連接的列。 ORDER BY或GROUP BY子句中指定的列。,下列情況不適于建立聚集索引,頻繁更改的列。 字節(jié)長的列。因?yàn)榫奂饕乃饕?xiàng)的值將被所有非聚集索引作為查找關(guān)鍵字使用，并被存儲在每個非聚集索引的B樹的葉級索引項(xiàng)中。,非聚集索引,非聚集索引與圖書后邊的術(shù)語表類似。數(shù)據(jù)存儲在一個地方，術(shù)語表存儲在另一個地方。而且數(shù)據(jù)并不按術(shù)語表的順序存放，但術(shù)語表中的每個詞在書中都有確切的位置。 非聚集索引就類似于術(shù)語表，而數(shù)據(jù)就類似于一本書的內(nèi)容。,非聚集索引的存儲示意圖,

5、非聚集索引與聚集索引的差別,數(shù)據(jù)不按非聚集索引關(guān)鍵字值的順序排序和存儲。 葉級節(jié)點(diǎn)不是存放數(shù)據(jù)的數(shù)據(jù)頁。 非聚集索引B樹的葉級節(jié)點(diǎn)是索引行。每個索引行包含非聚集索引關(guān)鍵字值以及一個或多個行定位器，這些行定位器指向該關(guān)鍵字值對應(yīng)的數(shù)據(jù)行（如果索引不唯一，則可能是多行）,在eno列上建有非聚集索引的情形,,數(shù)據(jù)示例,下述情況可考慮建立非聚集索引,包含大量非重復(fù)值的列。 不返回大型結(jié)果集的查詢。 經(jīng)常作為查詢條件使用的列。 經(jīng)常作為連接和分組條件的列。,唯一索引,確保索引列不包含重復(fù)值。 在組合唯一索引的情況下，可以確保索引列中每個值的組合都是唯一的。 例如，如果在last_name、first_n

6、ame和middle_initial列的組合上創(chuàng)建了唯一索引full_name，則該表中任何兩個人都不可以具有完全相同的名字。 聚集索引和非聚集索引都可以是唯一的。,說明,如果必須要實(shí)施唯一性來確保數(shù)據(jù)的完整性，則應(yīng)在列上創(chuàng)建UNIQUE約束或PRIMARY KEY約束，而不要創(chuàng)建唯一索引。 例如，如果限制身份證號碼（sid）列的取值不重復(fù)，則可在sid列上創(chuàng)建UNIQUE約束。 實(shí)際上，當(dāng)在表上創(chuàng)建PRIMARY KEY約束或UNIQUE約束時(shí)，系統(tǒng)會自動在這些列上創(chuàng)建唯一索引。,創(chuàng)建索引,CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名

7、(列名 ,...n) UNIQUE：創(chuàng)建唯一索引。 CLUSTERED：創(chuàng)建聚集索引。 NONCLUSTERED：創(chuàng)建非聚集索引。 如果沒有指定索引類型，則默認(rèn)是創(chuàng)建非聚集索引。,示例,例1 為Student表的Sname列創(chuàng)建非聚集索引。 CREATE INDEX Sname_ind ON Stuent (Sname) 例2 為Student表的Sid列創(chuàng)建唯一聚集索引。 CREATE UNIQUE CLUSTERED INDEX Sid_ind ON Stuent (Sid ),示例,例3 為Employee表的FirstName和LastName列創(chuàng)建一個聚集索引。 CREATE CLU

8、STERED INDEX EName_ind ON Employee(FirstName,LastName),刪除索引,刪除索引的基本語法格式為： DROP INDEX 例4 刪除Student表的Sname_ind索引 DROP INDEX Sname_ind,6.2 視圖,6.2.1 概念概念 6.2.2 定義視圖 6.2.3 通過視圖查詢數(shù)據(jù) 6.2.4 修改和刪除視圖 6.2.5 視圖的作用,6.2.1 基本概念,視圖是由從數(shù)據(jù)庫的基本表中選取出來的數(shù)據(jù)組成的邏輯窗口， 是基本表的部分行和列數(shù)據(jù)的組合。 視圖是一個虛表。 數(shù)據(jù)庫中只存儲視圖的定義，而不存儲視圖所包含的數(shù)據(jù)。,視圖與基本

9、表關(guān)系,6.2.2 定義視圖,CREATE VIEW (視圖列名表) AS 查詢語句 查詢語句中通常不包含ORDER BY和DISTINCT子句。 在定義視圖時(shí)要么指定視圖的全部列名，要么全部省略不寫。,說明,如果省略了視圖的“列名表”部分，則視圖的列名與查詢語句結(jié)果顯示的列名相同。 如下三種情況下必須明確指定視圖列名： 某個目標(biāo)列是函數(shù)或表達(dá)式，并且沒有為這樣的列起別名。 多表連接時(shí)選出了幾個同名列作為視圖的字段。 需要在視圖中為列選用新的更合適的列名。,定義單源表視圖,視圖取自一個基本表的部分行、列，視圖行列與基本表行列對應(yīng)， 一般可看可改。,示例,CREATE VIEW IS_Stud

10、ent AS SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept = 信息系,例1建立信息系學(xué)生的的學(xué)號、姓名、性別和年齡視圖。,定義多源表視圖,子查詢源表多于一個， 一般可看不可改。,36,示例,CREATE VIEW V_IS_S1（Sno, Sname, Grade） AS SELECT Student.Sno, Sname, Sage FROM Student, SC WHERE Sdept = 信息系 AND Student.Sno = SC.Sno AND SC.Cno = c01,例2建立信息系選修了c01課

11、程的學(xué)生的學(xué)號、姓名和成績的視圖。,在已有視圖上定義新視圖,視圖的數(shù)據(jù)源可以來自其它的視圖。,示例,CREATE VIEW IS_Student_Sage AS SELECT Sno, Sname, Sage FROM IS_Student WHERE Sage < 20,例3利用例1建立的視圖，建立查詢信息管理系年齡小于20的學(xué)生的學(xué)號、姓名和年齡的視圖 。,示例,例4 在例1所建的視圖基礎(chǔ)上，例2的視圖定義可改為： CREATE VIEW V_IS_S2(Sno,Sname,Grade) AS SELECT SC.Sno, Sname, Grade FROM IS_Student J

12、OIN SC ON IS_Student.Sno = SC.Sno WHERE Cno = c01,2020年7月29日3時(shí)6分,40,定義帶表達(dá)式的視圖,定義基本表時(shí)，為減少數(shù)據(jù)冗余，表中只存放基本數(shù)據(jù)。 由基本數(shù)據(jù)經(jīng)過各種計(jì)算派生出的數(shù)據(jù)一般不存儲。 由于視圖中的數(shù)據(jù)并不實(shí)際存儲，因此，可以在視圖中設(shè)置一些附加列來保存這些派生的數(shù)據(jù)。 由于這些附加列在基本表中并不實(shí)際存在，因此稱這些列為虛擬列。 稱包含虛擬列的視圖為帶表達(dá)式的視圖。,示例,例5.定義一個查詢學(xué)生出生年份的視圖，內(nèi)容包括學(xué)號，姓名和出生年份。 CREATE VIEW BT_S(Sno,Sname,Sbirth) AS S

13、ELECT Sno, Sname, 2010 - Sage FROM Student,含分組統(tǒng)計(jì)信息的視圖,子查詢中含GROUP BY子句，視圖行列由基本表行列得到， 數(shù)據(jù)只可看不可改。,示例,CREATE VIEW S_G AS SELECT Sno,AVG(Grade) AverageGrade FROM SC GROUP BY Sno,例6定義一個查詢每個學(xué)生的學(xué)號及平均成績的視圖。,6.2.3 通過視圖查詢數(shù)據(jù),視圖定義好后，可以對其進(jìn)行查詢， 通過視圖查詢數(shù)據(jù)同基本表一樣。,示例,例7：利用例1建立的視圖，查詢信息系男生的信息。 SELECT * FROM IS_Studen

14、t WHERE Ssex = 男,轉(zhuǎn)換成相關(guān)基本表的等價(jià)查詢,SELECT Sno, Sname, Ssex, Sage FROM Student WHERE Sdept = 信息系 AND Ssex = 男,示例,例8 查詢信息系選修了“cC01”號課程且成績大于等于60的學(xué)生的學(xué)號、姓名和成績。 這個查詢可以利用例2的視圖實(shí)現(xiàn)。 SELECT * FROM V_IS_S1 WHERE Grade = 60 此查詢轉(zhuǎn)換成的對最終基本表的查詢： SELECT S.Sno, Sname, Grade FROM SC JOIN Student S ON S.Sno = SC.Sno WHER

15、E Sdept = 信息系 AND SC.Cno = c01 AND Grade = 60,示例,例9.查詢信息系學(xué)生的學(xué)號、姓名、所選課程名 SELECT v.Sno, Sname, Cname FROM IS_Student v JOIN SC ON v.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno 此查詢轉(zhuǎn)換成的對最終基本表的查詢： SELECT S.Sno, Sname, Cname FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE

16、Sdept = 信息系,示例,例10 利用例6建立的視圖，查詢平均成績大于等于80分的學(xué)生的學(xué)號和平均成績。 SELECT * FROM S_G WHERE AverageGrade = 80 此查詢轉(zhuǎn)換成的對最終基本表的查詢： SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) = 80,注意,若將例10的查詢轉(zhuǎn)換為如下形式： SELECT Sno, AVG(Grade) FROM SC WHERE AVG(Grade) 80 GROUP BY Sno,x,視圖到基本表的轉(zhuǎn)換有些并不是直接進(jìn)行的。 目前大多數(shù)DBMS

17、對這種含有統(tǒng)計(jì)函數(shù)的視圖的查詢均能進(jìn)行正確的轉(zhuǎn)換。,通過視圖修改數(shù)據(jù),也可以通過視圖修改基本表中的數(shù)據(jù)， 但并不是所有的視圖都可以用于修改數(shù)據(jù)。如經(jīng)過統(tǒng)計(jì)或表達(dá)式計(jì)算得到的視圖。 能否通過視圖修改數(shù)據(jù)的基本原則： 如果這個操作能夠最終落實(shí)到基本表上，并成為對基本表的正確操作，則可以 否則不行。,6.2.4 修改和刪除視圖,1.修改視圖 ALTER VIEW 視圖名( 列名 ,...n ) AS 查詢語句,示例,例11.修改S_G視圖，使其統(tǒng)計(jì)每個學(xué)生的考試平均成績和修課總門數(shù)。 ALTER VIEW S_G(Sno, AverageGrade,Count_Cno) AS SELECT Sno, AVG(Grade), Count(*) FROM SC GROUP BY Sno,2. 刪除視圖,格式： DROP VIEW 例刪除IS_Student視圖。 DROP VIEW IS_Student,6.2.5 視圖的作用,簡化數(shù)據(jù)查詢語句 使用戶能從多角度看待同一數(shù)據(jù) 提高了數(shù)據(jù)的安全性 提供了一定程度的邏輯獨(dú)立性,