数据库技术及应用第4章 索引与视图

4.1.1索引的概念模式、外模式和内模式中的基本对象有数据库、表、视图和索引。其中表是面向整个系统的，它描述了关系的逻辑结构，属于模式范畴；视图是面向用户的，它描述了关系的部分逻辑结构，属于子模式范畴；而索引是关系数据库的内部实现技术，属于内模式范畴。用户可以根据应用的需要，在基表上创建一个或多个索引，以提供多种存取路径，加快查询速度。1、什么是索引表扫描查找方式就好像在图书馆里找一本书时，将图书馆中所有的书都找一遍，这样做的效率毫无疑问是非常低的。索引结构实现直接定位的访问方式。索引的工作方式非常类似于书的目录。如果希望了解某一特定章节的内容，可以在书的目录中查找该章节内容的起始页，然后读这些页，查找需要的信息。

4.1.1索引的概念2、稠密索引和稀疏索引用于在表中查找特定元组的属性或属性集称为搜索码。注意这里的码的定义与主码、候选码以及超码中的定义不同。索引是由一个个索引项组成的，每个索引项由一个搜索码值和指向具有该搜索码值的一个或多个元组的指针构成，整个索引按索引项中的搜索码值有序排列。表中每个搜索码值都有一个索引项，这种索引称为稠密索引。Sno Sno Sname Ssex Sage Major Address1600116001 张文杰 男 21 计算机 …16003 16003 沈婷 女 20 通信 …16004 16004 刘鹏飞 男 20 计算机 …16005 16005 王翔 男 21 通信 …16007 16007 陆文婷 女 19 计算机 …16008 16008 陈亮 男 20 计算机…16009 16009 李春红 女 21 通信 …16011 16011 孙莉 女 19 电子 …16012 16012 徐泽南 男 20 电子 …

4.1.1索引的概念2、稠密索引和稀疏索引当表按照搜索码值有序存储时，可以只为搜索码的某些值建立索引项，这种索引称为稀疏索引

。

假定要查找学号Sno为16004的学生记录，在稠密索引中，可以利用索引直接找到所需记录的指针。在稀疏索引中，由于没有Sno为16004索引项，只能找到比16004小的最后一个索引项16001，于是可以按着该指针查找，然后顺序读取S表，直到查找到所需的记录。Sno Sno Sname Ssex Sage Major Address1600116001 张文杰 男 21 计算机 …16005 16003 沈婷 女 20 通信 …16009 16004 刘鹏飞 男 20 计算机 … 16005 王翔 男 21 通信 … 16007 陆文婷 女 19 计算机 … 16008 陈亮 男 20 计算机… 16009 李春红 女 21 通信 … 16011 孙莉 女 19 电子 … 16012 徐泽南 男 20 电子 …稠密索引比稀疏索引更快地定位一条记录，稀疏索引也有比稠密索引优越的地方。

4.1.1索引的概念3、多级索引

通常为每一个数据块建一个索引项的稀疏索引是一个较好的折中，因为查找的开销主要由把数据块从磁盘读到内存的时间决定。一旦把块读入内存，在块中查找的时间可以忽略。如索引小到可以放在内存中，那么搜索一个索引项的时间就可以忽略。

由于索引项总是有序的，可在内层索引上构造一个稀疏的外层索引。

……………………数据块0数据块1索引块0索引块1内层索引外层索引107个元组，一个4KB的磁盘块中可以容纳10个元组和100个索引项。内层索引中有106个索引项，需要占用104个块，而外层索引只有104个索引项，仅占用100块。外层索引完全可以常驻内存

109个元组，内层索引中有108个索引项，占用106个块，外层索引有106个索引项，占用104个块。这时，可再创建另一级索引。具有两级或两级以上的索引称为多级索引。

4.1.1索引的概念4、聚集索引和辅助索引索引有多种分类方法。根据索引的存储结构来分，可以将索引分为聚集索引（ClusteringIndex）和辅助索引（SecondaryIndex，即非聚集索引NonclusteringIndex）两大类；根据搜索码值是否允许重复来分，可以将索引分为唯一索引和非唯一索引两大类；根据索引搜索码中的属性数来分，可以将索引分为单索引和复合索引两大类。SnameSno Sname Ssex…陈亮 16001 张文杰 男…李春红 16003 沈婷 女…刘鹏飞 16004 刘鹏飞 男…陆文婷 16005 王翔 男…沈婷 16007 陆文婷 女…孙莉 16008 陈亮 男…王翔 16009 李春红 女…徐泽南 16011 孙莉 女…张文杰 16012 徐泽南 男…所谓聚集索引就是指表中的元组按照索引中搜索码指定的顺序排序，使得具有相同搜索码值的元组在物理上聚集在一起。显然，一张表最多只能有一个聚集索引。聚集索引往往是稀疏索引。见图4.2。辅助索引必须是稠密索引，因为表中的元组是按聚集索引的搜索码有序存放的。显然，一张表可以创建多个辅助索引。不管是聚集索引，还是辅助索引，当索引项很多时，它们都可以是多级索引。

4.1.2创建索引的基本原则设计高效的索引对于获得良好的数据库和应用程序性能极为重要。总体索引设计策略应为查询优化器提供可供选择的多个索引，并由查询优化器做出正确的决定。索引一方面可以加快查询速度，提高对数据表的访问效率。另一方面，索引需要占用磁盘空间，系统对索引的维护使得当对数据进行增删改操作时所需花费的时间会更长。设计一个合理的索引配置方案是一项需要在查询速度与更新所需开销之间取得平衡的复杂任务，可能需要进行多次设计方案的试验，才能找到最合理有效的索引方案。设计索引时，应考虑以下原则：(1)应避免对经常更新的表进行过多的索引。(2)对小表进行索引可能不会产生优化效果，应避免对小表创建索引。

4.1.2创建索引的基本原则(3)使用多个索引可以提高更新少而数据量大的查询的性能。(4)应检查属性列中的数据分布，对于包含很多重复值的属性列避免创建索引。

(5)考虑对下列查询中涉及的属性列创建聚集索引：①WHERE子句条件中经常使用BETWEEN、>、>=、<和<=运算符进行比较的属性列；②出现在JOIN子句连接条件中的属性列；③ORDERBY或GROUPBY子句中的属性列。但经常更新的列不宜建聚簇索引，因为更新该索引列上的数据时，往往会导致表中元组物理顺序的改变，代价太大。(6)非聚集索引包含搜索码值和指向表数据存储位置的指针。非聚集索引可以提高涉及经常使用的、但没有建立聚集索引的属性列的查询的性能。非聚集索引成为完全匹配查询（即“=”比较）的最佳选择。另外，考虑对JOIN子句连接条件中的属性列和GROUPBY子句中的属性列创建非聚集索引。

4.1.2创建索引的基本原则(7)如果索引是复合索引，则应考虑属性列的顺序。WHERE子句条件中使用BETWEEN、>、<和=运算符进行比较的属性列或者JOIN子句连接条件中的属性列应该放在最前面。其他属性列应该基于其非重复级别进行排序，就是说，从最不重复的列到最重复的列。例如，如果将索引定义在LastName和FirstName属性列上，则该索引在搜索条件为WHERELastName='Smith'或WHERELastName='Smith'ANDFirstNameLIKE'J%'时将很有用。但是，查询优化器不会将此索引用于基于FirstName（如WHEREFirstName='Jane'）搜索的查询。(8)唯一索引能够保证搜索码中不包含重复的值，从而使表中的每一行从某种方式上具有唯一性。只有当唯一性是数据本身的特征时，指定唯一索引才有意义。只要列中的数据是唯一的，就可以为同一个表创建一个唯一聚集索引和多个唯一非聚集索引。唯一索引的优点有：①能够确保定义的属性列的数据完整性；②提供了对查询优化器有用的附加信息。

4.1.3索引的创建与删除1、创建索引其一般格式如下：

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX<索引名>ON<表名>(<列名>[ASC|DESC][,…n])NONCLUSTERED是默认值，ASC是默认值。如果既要创建聚集索引，又要创建非聚集索引，则应该先创建聚集索引，后创建非聚集索引。SQLServer2014规定一张表最多可以有249个非聚集索引，一个复合索引最多可组合16列，列的长度累计不能超过900字节。另外，T-SQL语言的CREATETABLE语句中的PRIMARYKEY约束默认会创建唯一聚集索引，而UNIQUE约束默认会创建唯一非聚集索引。例4.1

为C表的课程名按升序创建一个唯一非聚集索引。

CREATEUNIQUEINDEXIndex_C_CnameONC(Cname)例4.2

对SC表按课程号升序和选修日期降序创建一个复合非聚集索引。

CREATEINDEXIndex_SC_CnoSdateONSC(Cno,SdateDESC)

4.1.3索引的创建与删除2、删除索引索引一经建立，就由系统使用和维护它，不需用户干预。可删除一些不必要的索引，其一般格式如下：

DROPINDEX<索引名>例4.3

删除SC表上的Index_SC_CnoSdate索引。

DROPINDEXSC.Index_SC_CnoSdate

说明：在SQLServer2014中，在索引名前必须加表名。另外，由CREATETABLE语句中的约束自动创建的索引不能用DROPINDEX语句删除。

4.1.4SQLServer2014中的索引1、索引的结构在SQLServer中，如果一个表没有创建索引，则数据行不按任何特定的顺序存储，这种结构称为堆集。不管是聚集索引还是非聚集索引都按B树结构进行组织的，B树中的每一页称为一个索引节点。每级索引中的页均被链接在双向链表中。多级索引构成了一棵树，树中有的节点是满的，有的节点有很多闲置空间，还有一种情况就是树的左右各分支可能很不平衡。所谓B树就是一种平衡树，它能有效解决储存空间浪费与查找效率下降

。关于B树的详细讨论请“数据结构”。

4.1.4SQLServer2014中的索引SQLServer2014中的聚集索引和非聚集索引结构。非聚集索引中的行定位器有两种形式：如果表是堆集，行定位器就是指向行的指针（行ID）；如果表有唯一聚集索引，则行定位器就是行的聚集索引键，否则是一个内部添加值（称为唯一值）。

4.1.4SQLServer2014中的索引2、观察索引在查询中的作用T-SQL语言中的SETSTATISTICSIO{ON|OFF}语句，该语句可以让系统显示或不显示DML语句的磁盘I/O统计信息。

选择SSMS主菜单“查询”中的“包括实际的执行计划”命令，可以让系统显示或不显示DML语句的图形化执行计划并估计语句对资源的需求。执行语句SELECT*FROMtempWHEREid=23456。

没有索引：对表temp扫描1次，逻辑读640次，物理读0次；采用TableScan执行计划，并显示估计I/O开销为0.476537，估计CPU开销为0.0880785，总开销为0.564615。创建索引后：对表temp扫描0次，逻辑读3次，物理读0次；采用IndexSeek和RIDLookup执行计划，并显示估计I/O开销两者均为0.003125，估计CPU开销两者均为0.0001581，总开销为0.0065662。

4.2视图视图（View）从逻辑上看，它属于外模式，它是从一张或几张基本表（或视图）导出的表。与基本表不同，视图是一张虚表，在数据库中只存放视图的定义（即SELECT语句），不存放视图对应的数据（即SELECT语句的查询结果）。所以基本表中的数据一旦发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，通过它用户可以看到数据库中自己感兴趣的数据及其变化。视图一经定义，就可以和基本表一样被查询和删除，也可以在一个视图之上再定义新的视图，但对视图的更新（增加、删除、修改）操作则有一定的限制。

4.2.1视图的定义与删除1、定义视图其一般格式如下：

CREATEVIEW<视图名>[(<列名>[,…])]AS<子查询>[WITHCHECKOPTION]

其中的子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDERBY子句。WITHCHECKOPTION表示用户必须保证每当向该视图中插入或修改数据时，所插入或修改的数据能够从该视图查询出来。下列三种情况下必须明确指定组成视图的属性列名：(1)某个目标列是聚集函数或列表达式；(2)多表连接时有同名的列（即<表名>.<列名>形式的列）作为视图的属性列；(3)需要在视图中为某个列启用新的更合适的名字。

4.2.1视图的定义与删除1、定义视图例4.4

创建由计算机专业学生的学号、姓名、性别和联系电话组成的视图。

CREATEVIEWCS_S--行列子集视图

ASSELECTSno,Sname,Ssex,MphoneFROMSWHEREMajor='计算机'例4.5

创建在2017秋选修了2002号课程的学生视图，该视图包括学号、姓名、性别、专业和成绩。

CREATEVIEWS_2002(Sno,Sname,Ssex,Major,Score)ASSELECTS.Sno,Sname,Ssex,Major,ScoreFROMSJOINSCONS.Sno=SC.SnoWHERESdate='2017秋'ANDCno='2002'

4.2.1视图的定义与删除1、定义视图例4.6

创建在2017秋选修了2002号课程的计算机专业学生视图，该视图包括学号、姓名、性别和成绩。

CREATEVIEWS_CS2002ASSELECTSno,Sname,Ssex,ScoreFROMS_2002--建立在视图S_2002之上

WHEREMajor='计算机'例4.7

创建一个由每个学生的学号及所有已考课程的平均成绩组成的试图。

CREATEVIEWS_AVG(Sno,AVG_S)--分组视图

ASSELECTSno,AVG(Score)FROMSCWHEREScoreISNOTNULLGROUPBYSno

4.2.1视图的定义与删除1、定义视图例4.8

创建一个由全体女生组成的视图。

CREATEVIEWF_SASSELECT*FROMSWHERESsex='女'

说明：由于SELECT子句中出现了“*”，所以视图F_S的属性列与表S的属性列一一对应。如果以后修改了表S的结构，就会破坏原来的对应关系，使得视图F_S不能正确工作。2、删除视图其一般格式如下：

DROPVIEW<视图名>例4.9

删除视图S_CS2002。

DROPVIEWS_CS2002需要说明的是：如果删除了基本表S（或视图S_2002），但在它基础上创建的视图CS_S（或视图S_CS2002）仍然存在，但已不能正常使用，必须显式删除。

4.2.2查询视图一般情况下，视图可以像基本表那样使用，视图名可以出现在基表名可以出现的地方。当然，视图定义后，可以像基本表一样对其进行查询。例4.10

在视图CS_S中找出计算机专业女生的学号、姓名和联系电话。

SELECTSno,Sname,MphoneFROMCS_SWHERESsex='女'视图消解后

SELECTSno,Sname,MphoneFROMSWHEREMajor='计算机'ANDSsex='女'例4.11找出在2017春选修了1002号课程的计算机专业学生的学号、姓名、性别和成绩。

SELECTSC.Sno,Sname,Ssex,ScoreFROMCS_SJOINSCONCS_S.Sno=SC.SnoWHERESdate='2017春'ANDCno='1002'CREATEVIEWCS_SASSELECTSno,Sname,Ssex,MphoneFROMSWHEREMajor='计算机'

4.2.2查询视图例4.12

找出所有已考课程的平均成绩在80分以上的学生学号。

SELECTSnoFROMS_AVGWHEREAVG_S>80本例转换后的查询语句为：

SELECTSnoFROMSCWHEREScoreISNOTNULLGROUPBYSno

HAVINGAVG(Score)>80

说明：本例中S_AVG是一个分组视图，WHERE子句中的查询条件AVG_S>80，转换后变为AVG(Score)>80出现在了HAVING子句中，而不能机械地仍然在WHERE子句中，否则将会出现语法错误。目前大多数的RDBMS都能对此做正确的转换。CREATEVIEWS_AVG(Sno,AVG_S)ASSELECTSno,AVG(Score)FROMSCWHEREScoreISNOTNULLGROUPBYSno

4.2.3更新视图更新视图是指通过视图进行数据的插入、删除和修改。由于视图是不存储数据的虚表，因此对视图的更新最终也是转换为对其基表的更新。

CREATEVIEWCS1_SASSELECTSno,Sname,Ssex,Sage,Major,MphoneFROMSWHEREMajor='计算机'

CREATEVIEWCS2_SASSELECTSno,Sname,Ssex,Sage,Major,MphoneFROMSWHEREMajor='计算机'

WITHCHECKOPTION

4.2.3更新视图例4.13

向计算机专业学生视图CS1_S中插入一个新的学生记录，其中学号为16008，姓名为陈亮，性别为男性，联系电话

INSERTINTOCS1_S(Sno,Sname,Ssex,Mphone)VALUES('16008','陈亮','男',)转换为对基本表的更新：

INSERTINTOS(Sno,Sname,Ssex,Major,Mphone)VALUES('16008','陈亮','男',null,)

说明：在SQLServer2014中插入操作成功，但由于Major为null，导致在CS1_S视图中看不到刚才插入的学生。

4.2.3更新视图例4.14

将计算机专业学生视图CS1_S中学号为16004的学生年龄由20改为22，专业由计算机改为通信。

UPDATECS1_SSETSage=22,Major='通信'WHERESno='16004'转换为对基本表的更新：

UPDATESSETSage=22,Major='通信'WHEREMajor='计算机'ANDSno='16004'

说明：在SQLServer2014中修改操作成功，但由于Major已改为通信，导致在CS1_S视图中看不到刚才修改过的学生。

4.2.3更新视图虽然例4.13和4.14中的操作都成功，但在操作的对象CS1_S中都看不到操作结果，有点匪夷所思。为了防止这种情况的出现，SQL语言中可以通过在定义视图时加上WITHCHECKOPTION子句来解决。如果将例4.13和4.14中的操作对象改为CS2_S，那么操作都会被拒绝执行，因为这些操作都会导致操作结果不满足视图定义中的条件。当然，如果在例4.13中明确16008号学生的专业是计算机，例4.14中仅仅修改16004号学生的年龄，那么通过视图CS1_S或CS2_S都可以完成插入和修改操作，而且也可以通过视图CS1_S或CS2_S查看操作结果。

4.2.3更新视图例4.15

删除计算机专业学生视图CS2_S中学号为16008的学生。

DELETEFROMCS2_SWHERESno='16008'

要特别说明的是：更新视图有许多限制，对视图的许多更新操作是不能唯一地有意义地转换成对相应基表的更新。例如，对视图S_AVG要修改它的AVG_S列是无法转换成对基表SC的修改的。哪些视图是可以更新的，目前各个RDBMS有不同的规定，一般来说，如果定义视图的子查询对下列条件都能满足，那么该视图是可以更新的：(1)FROM子句中只有一个数据库关系；(2)SELECT子句中只包含关系的属性名，不包含任何表达式、聚集函数或DISTINCT短语；(3)没有出现在SELECT子句中的属性可以取空值（即这些属性上没有NOTNULL约束），也不是主码中的属性；(4)子查询中没有GROUPBY或HAVING子句。

4.2.4视图的作用

(1)由于视图在逻辑上属于外模式，因此，视图对重构数据