第6章索引與視圖

1、數(shù)據(jù)庫原理與應用（第數(shù)據(jù)庫原理與應用（第2 2版）版）高等院校計算機教材系列第第6 6章章 索引和視圖索引和視圖6.1 6.1 索引索引6.2 6.2 視圖視圖6.1 索引 6.1.1 索引基本概念 6.1.2 索引的存儲結(jié)構(gòu)及分類 6.1.3 創(chuàng)建和刪除索引*36.1 索引基本概念 索引與書籍中的目錄類似。 索引使對數(shù)據(jù)的查找不需要對整個表進行掃描，就可以在其中找到所需數(shù)據(jù)。 可以為表中的單個列建立索引，也可以為一組列（索引項）建立索引。 索引一般采用B樹結(jié)構(gòu)。*4索引及數(shù)據(jù)間的對應關系示意圖 索引的組織方式 索引項按數(shù)據(jù)頁(一塊固定大小的連續(xù)存儲空間)存儲。 表中的全部索引連在一起。6.1

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

3、節(jié)點的索引頁。 10建有聚集索引的表的存儲結(jié)構(gòu)示意圖數(shù)據(jù)示例數(shù)據(jù)示例enoenamedeptE01ABCSE02AACSE03BBISE04BCCSE05CBISE06ASISE07BBISE08ADCSE09BDISE10BAISE11CCCSE12CACS聚集索引聚集索引非聚集索引非聚集索引聚集索引示例數(shù)據(jù)示例數(shù)據(jù)示例13查找過程查找過程 當在建有聚集索引的列上查找數(shù)據(jù)時 首先從聚集索引樹的入口（根節(jié)點）開始逐層向下查找， 直到達到B-樹索引的葉級，也就是達到了要找的數(shù)據(jù)所在的數(shù)據(jù)頁， 最后只在這個數(shù)據(jù)頁中查找所需數(shù)據(jù)查找示例查找示例SELECT * FROM employee WHERE

4、 eno=E08說明說明 在聚集索引的葉節(jié)點中，數(shù)據(jù)按聚集索引項的值進行物理排序。 因此，聚集索引很類似于電話號碼簿。 一個表只能包含一個聚集索引。 但一個索引可以由多個列（組合索引）組成。下列情況可考慮創(chuàng)建聚集索 包含大量非重復值的列。 使用下列運算符返回一個范圍值的查詢：BETWEEN AND、=、 和 =。 被連續(xù)訪問的列。 不返回大型結(jié)果集的查詢。 經(jīng)常被用作連接的列。 ORDER BY或GROUP BY子句中指定的列。下列情況不適于建立聚集索引 頻繁更改的列。 字節(jié)長的列。因為聚集索引的索引項的值將被所有非聚集索引作為查找關鍵字使用，并被存儲在每個非聚集索引的B樹的葉級索引項中。非聚

5、集索引 非聚集索引與圖書后邊的術(shù)語表類似。數(shù)據(jù)存儲在一個地方，術(shù)語表存儲在另一個地方。而且數(shù)據(jù)并不按術(shù)語表的順序存放，但術(shù)語表中的每個詞在書中都有確切的位置。 非聚集索引就類似于術(shù)語表，而數(shù)據(jù)就類似于一本書的內(nèi)容。非聚集索引的存儲示意圖 非聚集索引與聚集索引的差別 數(shù)據(jù)不按非聚集索引關鍵字值的順序排序和存儲。 葉級節(jié)點不是存放數(shù)據(jù)的數(shù)據(jù)頁。 非聚集索引B樹的葉級節(jié)點是索引行。每個索引行包含非聚集索引關鍵字值以及一個或多個行定位器，這些行定位器指向該關鍵字值對應的數(shù)據(jù)行（如果索引不唯一，則可能是多行）在在enoeno列上建有非聚集索引的情形列上建有非聚集索引的情形 數(shù)據(jù)示例數(shù)據(jù)示例下述情況可考慮

6、建立非聚集索引 包含大量非重復值的列。 不返回大型結(jié)果集的查詢。 經(jīng)常作為查詢條件使用的列。 經(jīng)常作為連接和分組條件的列。唯一索引 確保索引列不包含重復值。 在組合唯一索引的情況下，可以確保索引列中每個值的組合都是唯一的。 例如，如果在last_name、first_name和middle_initial列的組合上創(chuàng)建了唯一索引full_name，則該表中任何兩個人都不可以具有完全相同的名字。 聚集索引和非聚集索引都可以是唯一的。 說明 如果必須要實施唯一性來確保數(shù)據(jù)的完整性，則應在列上創(chuàng)建UNIQUE約束或PRIMARY KEY約束，而不要創(chuàng)建唯一索引。 例如，如果限制身份證號碼（sid）列

7、的取值不重復，則可在sid列上創(chuàng)建UNIQUE約束。 實際上，當在表上創(chuàng)建PRIMARY KEY約束或UNIQUE約束時，系統(tǒng)會自動在這些列上創(chuàng)建唯一索引。 創(chuàng)建索引 CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX 索引名 ON 表名(列名 ,.n) UNIQUE：創(chuàng)建唯一索引。 CLUSTERED：創(chuàng)建聚集索引。 NONCLUSTERED：創(chuàng)建非聚集索引。 如果沒有指定索引類型，則默認是創(chuàng)建非聚集索引。示例示例 例1 為Student表的Sname列創(chuàng)建非聚集索引。CREATE INDEX Sname_ind ON Stuent (Sname) 例2 為S

8、tudent表的Sid列創(chuàng)建唯一聚集索引。CREATE UNIQUE CLUSTERED INDEX Sid_ind ON Stuent (Sid )示例 例3 為Employee表的FirstName和LastName列創(chuàng)建一個聚集索引。CREATE CLUSTERED INDEX EName_ind ON Employee(FirstName,LastName)刪除索引 刪除索引的基本語法格式為： DROP INDEX 例4 刪除Student表的Sname_ind索引DROP INDEX Sname_ind 6.2 6.2 視圖視圖 6.2.1 6.2.1 概念概念概念概念 6.2.2

9、6.2.2 定義視圖定義視圖 6.2.3 6.2.3 通過視圖查詢數(shù)據(jù)通過視圖查詢數(shù)據(jù) 6.2.4 6.2.4 修改和刪除視圖修改和刪除視圖 6.2.5 6.2.5 視圖的作用視圖的作用6.2.1 基本概念 視圖是由從數(shù)據(jù)庫的基本表中選取出來的數(shù)據(jù)組成的邏輯窗口， 是基本表的部分行和列數(shù)據(jù)的組合。 視圖是一個虛表。 數(shù)據(jù)庫中只存儲視圖的定義，而不存儲視圖所包含的數(shù)據(jù)。視圖與基本表關系6.2.2 6.2.2 定義視圖定義視圖 CREATE VIEW (視圖列名表)AS 查詢語句 查詢語句中通常不包含ORDER BY和DISTINCT子句。 在定義視圖時要么指定視圖的全部列名，要么全部省略不寫。說

10、明說明 如果省略了視圖的“列名表”部分，則視圖的列名與查詢語句結(jié)果顯示的列名相同。 如下三種情況下必須明確指定視圖列名： 某個目標列是函數(shù)或表達式，并且沒有為這樣的列起別名。 多表連接時選出了幾個同名列作為視圖的字段。 需要在視圖中為列選用新的更合適的列名。定義單源表視圖定義單源表視圖 視圖取自一個基本表的部分行、列，視圖行列與基本表行列對應， 一般可看可改。示例示例例例1 1建立信息系學生的的學號、姓名、建立信息系學生的的學號、姓名、性別和年齡視圖。性別和年齡視圖。 CREATE VIEW IS_StudentASSELECT Sno, Sname, Ssex, Sage FROM Stud

11、ent WHERE Sdept = 信息系 定義多源表視圖定義多源表視圖 子查詢源表多于一個， 一般可看不可改。37示例例例2 2建立信息系選修了建立信息系選修了c01c01課程的學課程的學生的學號、姓名和成績的視圖。生的學號、姓名和成績的視圖。 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 在已有視圖上定義新視圖在已有視圖上定義新視圖 視圖的

12、數(shù)據(jù)源可以來自其它的視圖。示例示例例例3 3利用例利用例1 1建立的視圖，建立查詢信建立的視圖，建立查詢信息管理系年齡小于息管理系年齡小于2020的學生的學號、姓的學生的學號、姓名和年齡的視圖名和年齡的視圖 。 CREATE VIEW IS_Student_SageAS SELECT Sno, Sname, Sage FROM IS_Student WHERE Sage = 60 此查詢轉(zhuǎn)換成的對最終基本表的查詢：SELECT S.Sno, Sname, Grade FROM SC JOIN Student S ON S.Sno = SC.Sno WHERE Sdept = 信息系 AND S

13、C.Cno = c01 AND Grade = 60示例示例 例9.查詢信息系學生的學號、姓名、所選課程名 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 Sdept = 信息系示例示例 例10

14、利用例6建立的視圖，查詢平均成績大于等于80分的學生的學號和平均成績。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 視圖到基本表的轉(zhuǎn)換有些并不是直接進行視圖到基本表的轉(zhuǎn)換有些并不是直接進行的。的。 目前大多數(shù)目前大多數(shù)DBMSDBMS對這種含有統(tǒng)計函數(shù)的視對這種含有統(tǒng)計函數(shù)的視圖的查詢均能進行正確的轉(zhuǎn)換。圖的查詢均能進行正確的轉(zhuǎn)換。通過視圖修改數(shù)據(jù) 也可以通過視圖修改基本表中的數(shù)據(jù)， 但并不是所有的視圖都可以用于修改數(shù)據(jù)。如經(jīng)過統(tǒng)計或表達式計算得到的視圖。 能否通過視圖修改數(shù)據(jù)的基本原則： 如果這個操作能夠最終落實到基本表上，并成為對基本表的正確操作，則可以 否則不行。6.2.4 修改和刪除視圖 1.修改視圖ALTER VIEW 視圖名( 列名 ,.n ) AS 查詢語句示例示例 例11.修改S_G視圖，使其統(tǒng)計