1、第五章第五章索引与视图索引与视图索引的分类索引的分类创建合适的索引创建合适的索引创建视图创建视图 用户对数据库最常用的操作就是查询数据。在数据量比用户对数据库最常用的操作就是查询数据。在数据量比较大时，搜索满足条件的数据可能会花费很长时间，从而占较大时，搜索满足条件的数据可能会花费很长时间，从而占用较多的服务器资源。为了提高数据检索的能力，在数据库用较多的服务器资源。为了提高数据检索的能力，在数据库中引入了索引的概念。中引入了索引的概念。数据库中的索引是一个表中所包含的值的列表，数据库中的索引是一个表中所包含的值的列表，其中注明了表中其中注明了表中包含的某列（或某些列）的值以包含的某列（或某些
2、列）的值以及各个值所对应的记录存储位置及各个值所对应的记录存储位置。可以为表中的。可以为表中的单个列单个列建立索引，也可以为建立索引，也可以为一组列一组列建立索引。建立索引。1.可以大大可以大大加快数据检索速度加快数据检索速度。在检索数据过程。在检索数据过程中自动进行优化，提高系统性能。中自动进行优化，提高系统性能。2.可以可以加速表与表之间的连接加速表与表之间的连接。3.可以显著可以显著减少查询中分组和排序的时间减少查询中分组和排序的时间。1.创建索引需花费时间。创建索引需花费时间。2.每建一个索引都需要花费一定的存储空间。每建一个索引都需要花费一定的存储空间。3.当数据变化时，需要重新维护
3、索引。当数据变化时，需要重新维护索引。1.聚集索引（聚集索引（Clustered index）2.非聚集索引（非聚集索引（Nonclustered index）聚集索引：在聚集索引中，表中各行的物理顺序与索引键值聚集索引：在聚集索引中，表中各行的物理顺序与索引键值的逻辑（索引）顺序相同。表只能包含一个聚集索引，聚集的逻辑（索引）顺序相同。表只能包含一个聚集索引，聚集索引通常建在有大量唯一值及不常更新的列上，聚集索引常索引通常建在有大量唯一值及不常更新的列上，聚集索引常用于搜索范围值特别有效。用于搜索范围值特别有效。非聚集索引：非聚集索引具有完全独立于数据行的结构，非非聚集索引：非聚集索引具有完
4、全独立于数据行的结构，非聚集索引每张表可以创建聚集索引每张表可以创建249个。个。非聚集索常用于：非聚集索常用于：1.在在join，where及及group by子句上出现的列。子句上出现的列。2.列的值常常更新。列的值常常更新。3.外键对应的列。外键对应的列。（非）聚集索引是按（非）聚集索引是按B+树结构存放的，工作步骤如下：树结构存放的，工作步骤如下：1.SQL SERVER从从sysindexes表中获得根页地址。表中获得根页地址。2.将要查找的值与根页上的键值相比较。将要查找的值与根页上的键值相比较。3.找到页上小于或等于要查找的值的最大键值。找到页上小于或等于要查找的值的最大键值。4
5、.页指针指向下一页。页指针指向下一页。5.重复步骤重复步骤3和和4，直到叶子页。，直到叶子页。6.找到要查找的值，如果没有该值，则不返回结果。找到要查找的值，如果没有该值，则不返回结果。602E005601E001Page PtrEid Page 603204E007203E005Page PtrEid Page 602202E003201E001Page PtrEid Page 601CalMillerE006LADavE005CityNameEid Page 203CalWilsE008WACarlE007CityNameEid Page 204BostonDunE002LAAllenE0
6、01CityNameEid Page 201LAKellyE004CalColsE003CityNameEid Page 202Root pageIntermediate pageData(Leaf)pages(table)1.SQL SERVER从从sysindexes表中表中获得根页地址。获得根页地址。2.将要查找的值将要查找的值与根页上的键值与根页上的键值相比较。相比较。3.找到页上小于找到页上小于或等于要查找的或等于要查找的值的最大键值。值的最大键值。4.页指针指向下页指针指向下一页一页3.找到页上小于找到页上小于或等于要查找的或等于要查找的值的最大键值。值的最大键值。4.页指针指向下
7、页指针指向下一页一页602Dun601AllenPage Ptr NamePage 603CalColsE006LAAllenE005CityNameEid Page 303BostonDunE008LAKellyE007CityNameEid Page 304CalWilsE002LADavE001CityNameEid Page 301WACarlE004CalMillerE003CityNameEid Page 302Root pageIntermediate pageData pages(table)Leaf page202Cols201AllenPage PtrNamePage 60
8、1204Miller203DunPage PtrNamePage 602302Carl303AllenPage PtrNamePage 201301Dav303ColsPage PtrName Page 202304Kelly304DunPage PtrNamePage 203301Wils302MillerPage PtrNamePage 2041.SQL SERVER从从sysindexes表中表中获得根页地址。获得根页地址。2.将要查找的值将要查找的值与根页上的键值与根页上的键值相比较。相比较。3.找到页上小于找到页上小于或等于要查找的或等于要查找的值的最大键值。值的最大键值。4.页指针
9、指向下页指针指向下一页一页3.找到页上小于找到页上小于或等于要查找的或等于要查找的值的最大键值。值的最大键值。4.页指针指向下页指针指向下一页一页1.索引增强了连接表，排序，分组的查询。索引增强了连接表，排序，分组的查询。2.当修改了创建索引的列，相应的索引也会自动更新。当修改了创建索引的列，相应的索引也会自动更新。3.维护索引需要花费时间，不常用的索引不应创建。维护索引需要花费时间，不常用的索引不应创建。4.聚集索引通常创建在非聚集索引之前。聚集索引通常创建在非聚集索引之前。5.通常外键上创建非聚集索引，主键创建聚集索引。通常外键上创建非聚集索引，主键创建聚集索引。语法：语法：CREATE
10、CLUSTERED|NONCLUSTERED INDEX 索引名索引名ON 表名表名|视图名视图名 (列名列名 ASC|DESC ,.n )默认为非聚集索引默认为非聚集索引示例示例1:为为“Student”表创建基于表创建基于“StuName”列的聚集索列的聚集索引引“idxStuName”解决方案：解决方案：CREATE CLUSTERED INDEX idxStuName ON Student(StuName)示例示例2:对对“Student”表的表的“DepID”列创建非聚集索引列创建非聚集索引解决方案：解决方案：CREATE NONCLUSTERED INDEX idxDepID ON
11、 Student(DepID)语法：语法：DROP INDEX 表名表名.索引名索引名示例示例:删除删除“Student”表的索引表的索引“idxStuName”解决方案：解决方案：DROP INDEX Student.idxStuName有下列查询语句，由于数据量较大，希望查询时能提升查询有下列查询语句，由于数据量较大，希望查询时能提升查询速度，该如何建立适当的索引？速度，该如何建立适当的索引？SELECT StuNo,StuName,DepNameFROM Student JOIN DepartmentON Student.DeptID=Department.DeptID解决方案：解决方案
12、：CREATE CLUSTERED INDEX idxDepartmentON Department(DeptID)CREATE CLUSTERED INDEX idxStudentON Student(StuID)CREATE NONCLUSTERED INDEX nidxStudentON Student(DeptID)查询数据库原理课程最高分对应的学生信息。查询数据库原理课程最高分对应的学生信息。SELECT SC.StuID,StuName,StuSex,DepID FROM Student JOIN SC ON Student.StuID=SC.StuIDJOIN Course on
13、 Course.CourseID=Sc.CourseIDWHERE CourseName=database AND Grade=(SELECT max(Grade)FROM SC WHERE CourseID=(SELECT CourseID FROM Course WHERE CourseName=database)视图是保存在数据库中的视图是保存在数据库中的SELECT查询，其内容由查询，其内容由查询定义，因此，视图不是真实存在的基础表，而是从查询定义，因此，视图不是真实存在的基础表，而是从一个或者多个表中导出的虚拟的表。同真实的表一样，一个或者多个表中导出的虚拟的表。同真实的表一样，视图
14、包含一系列带有名称的列和行数据，但视图中的行视图包含一系列带有名称的列和行数据，但视图中的行和列数据来自由定义视图的查询所引用的表，并且在引和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。因此，用视图时动态生成。因此，视图所对应的数据并不实际视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用地以视图结构存储在数据库中，而是存储在视图所引用的表中的表中。简单性。视图不仅可以简化用户对数据的理解，也简单性。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使用户不
15、必为以后的操作每次都指定全义为视图，从而使用户不必为以后的操作每次都指定全部的条件。部的条件。安全性。通过视图用户只能查询和修改他们所能见安全性。通过视图用户只能查询和修改他们所能见到的数据。数据库中的其他数据则既看不见也取不到。到的数据。数据库中的其他数据则既看不见也取不到。数据库授权命令可以使每个用户对数据库的检索限制到数据库授权命令可以使每个用户对数据库的检索限制到特定的数据库对象上，但不能授权到数据库特定行和特特定的数据库对象上，但不能授权到数据库特定行和特定的列上。通过视图，用户可以被限制在数据的不同子定的列上。通过视图，用户可以被限制在数据的不同子集上。集上。示例：创建视图显示示例
16、：创建视图显示选修了选修了“数据库数据库”课程的学生姓名课程的学生姓名,所所在系在系,年龄及成绩。年龄及成绩。语法语法：CREATE VIEW 视图名视图名 AS SELECT语句语句解决方案解决方案：CREATE VIEW vwStudentInfoWithDBASSELECT StuName,DepName,StuAge,ScoreFROM Student s JOIN SC ON s.StuID=SC.StuID JOIN Course ON SC.CourseID=Course.CourseIDJOIN Department d ON d.DepID=s.DepIDWHERE Cour
17、seName=数据库数据库示例：创建视图查询其成绩比该课程平均成绩高的学生的学示例：创建视图查询其成绩比该课程平均成绩高的学生的学号、课程号及成绩号、课程号及成绩相关子查询：相关子查询：SELECT EmployeeID,Title,VacationHoursFROM Employee e1 WHERE e1.VacationHours (SELECT AVG(e2.VacationHours)FROM Employee e2 WHERE e2.Title=e1.Title)解决方案：解决方案：CREATE VIEW vwGreaterThanAvgScoreASSELECT StuID,Co
18、urseID,GradeFROM SC xWHERE x.Grade=(SELECT AVG(Grade)FROM SC y WHERE y.CourseID=x.CourseID)CREATE VIEW vwStudentInfoWithDBASSELECT StuName,DepName,StuAge,ScoreFROM Student s JOIN SC ON s.StuID=SC.StuID JOIN Course ON SC.CourseID=Course.CourseIDJOIN Department d ON d.DepID=s.DepIDWHERE CourseName=数据库
19、数据库如要更新如要更新1号学生的年龄和成绩：号学生的年龄和成绩：UPDATE vwStudentInfoWithDB SET StuAge=StuAge+1,Score=Score+10WHERE StuID=1编译器将报错编译器将报错视图不能同时修改来自于视图不能同时修改来自于2张或张或2张以上基表的数据张以上基表的数据正确的更新方法：正确的更新方法：UPDATE vwStudentInfoWithDBSET StuAge=StuAge+1WHERE StuID=1UPDATE vwStudentInfoWithDBSET Score=Score+10WHERE StuID=1查询选修了查询选修了“数据库数据库”课程的学生姓名，所在系，年龄及课程的学生姓名，所在系，年龄及成绩。成绩。SELECT*FROM vwStudentInfoWithDB语法：语法：ALTER VIEW 视图名视图名AS SELECT语句语句语法：语法：DROP VIEW 视图名视图名Sp_helptext 视图名视图名索引索引视图视图索引，视图索引，视图查询语句（高级）查询语句（高级）
