数据库应用技术：第08章_索引与视图

1、第8章 索引与视图1本章内容8.1 使用索引8.2 使用视图28.1 使用索引索引是与表或视图关联的磁盘上结构，索引中的键存储在一个结构中，使SQL Server可以快速有效地查找与键值关联的行。 建立索引的优点：索引可以减少为返回查询结果集而必须读取的数据量；索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性；使用索引可以快速找到表或索引视图中的特定信息 。38.1 使用索引索引的分类表或视图的索引可以包括以下几类：(1) 惟一索引在表中建立惟一性索引时，组成该索引的字段或字段组合在表中具有惟一值，也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。 48.1 使用索引(

2、2) 聚集索引聚集索引根据数据行的键值在表或视图中排序和存储这些数据行，即聚集索引决定了数据的物理顺序。只有在表中建立了一个聚集索引后，数据才会按照索引键值指定的顺序存储到表中。由于一个表中的数据只能按照一种顺序来存储，所以在一个表中只能建立一个聚集索引。 表具有聚集索引，则该表称为聚集表。否则，其数据行存储在一个称为堆的无序结构中 。58.1 使用索引(3) 非聚集索引非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。具有独立于数据行的结构 。从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是

3、指向行的指针。对于聚集表，行定位器是聚集索引键。注意：聚集索引和非聚集索引都可以是唯一的，即任何两行都不能有相同的索引键值 ；索引也可以不是唯一的，即多行可以共享同一键值 ；每当修改了表数据后，都会自动维护表或视图的索引 。 68.1 使用索引(4) 包含性列索引 一种非聚集索引，它扩展后不仅包含键列，还包含非键列 。(5) 索引视图 视图的索引将具体化(执行)视图，并将结果集永久存储在唯一的聚集索引中，而且其存储方法与带聚集索引的表的存储方法相同。创建聚集索引后，可以为视图添加非聚集索引。78.1 使用索引(6) 全文索引 一种特殊类型的基于标记的功能性索引，由Microsoft SQL S

4、erver全文引擎(MSFTESQL)服务创建和维护，用于帮助在字符串数据中搜索复杂的词。(7) XML索引 xml数据类型列中XML二进制大型对象的已拆分持久表示形式 。注意：对表中的列定义了PRIMARY KEY约束和UNIQUE约束时，会自动创建索引。 88.1 使用索引索引设计准则 要设计出好的索引集，需要了解数据库、查询和数据列的特征 。(1) 数据库准则 避免对经常更新的表创建过多的索引，并且列要尽可能少；使用多个索引可以提高更新少而数据量大的查询的性能。对小表进行索引可能不会产生优化效果 。当视图包含聚合、表联接或聚合和联接的组合时，视图的索引可以显著地提升性能 。 98.1 使

5、用索引(2) 查询准则为经常用于查询中的谓词和联接条件的所有列创建非聚集索引。涵盖索引可以提高查询性能，因为符合查询要求的全部数据都存在于索引本身中 。将插入或修改尽可能多的行的查询写入单个语句内，而不要使用多个查询更新相同的行。 评估查询类型以及如何在查询中使用列 。例如，在完全匹配查询类型中使用的列就适合用于非聚集索引或聚集索引。108.1 使用索引(3) 列准则对于聚集索引，应保持较短的索引键长度。另外，对唯一列或非空列创建聚集索引可以使聚集索引效率高 .不能将ntext、text、image、varchar(max)、nvarchar(max)和varbinary(max)数据类型的列

6、指定为索引键列 ，不过，varchar(max)、nvarchar(max)、varbinary(max)和xml数据类型的列可以作为非键索引列参与非聚集索引。xml数据类型的列只能在XML索引中用作键列 。 118.1 使用索引如果索引包含多个列，则应考虑列的顺序 。用于等于 (=)、大于 ()、小于 () 或BETWEEN搜索条件的WHERE子句或者参与联接的列应该放在最前面。其他列应该基于其非重复级别进行排序，就是说，从最不重复的列到最重复的列。 考虑对计算列进行索引 。128.1 使用索引(4) 索引特征在确定某一索引适合某一查询之后，可以选择最适合具体情况的索引类型。创建索引时需确定

7、以下选项。聚集还是非聚集唯一还是非唯一单列还是多列索引中的列是升序排序还是降序排序 138.1 使用索引 创建索引1. 创建索引步骤如下：(1) 设计索引 索引设计包括确定要使用的列，选择索引类型(例如聚集或非聚集)，选择适当的索引选项，以及确定文件组或分区方案布置 。(2) 确定最佳的创建方法 使用CREATE TABLE或ALTER TABLE对列定义PRIMARY KEY或UNIQUE约束。148.1 使用索引默认情况下，创建的唯一聚集索引可以强制PRIMARY KEY约束，除非表中已存在聚集索引或指定了唯一的非聚集索引。默认情况下，创建的唯一非聚集索引可以强制UNIQUE约束，除非已明

8、确指定唯一的聚集索引且表中不存在聚集索引。 使用CREATE INDEX语句或SQL Server Management Studio对象资源管理器中的“新建索引”对话框创建独立于约束的索引 。必须指定索引的名称、表以及应用该索引的列。还可以指定索引选项和索引位置、文件组或分区方案。默认情况下，如果未指定聚集或唯一选项，将创建非聚集的非唯一索引 158.1 使用索引(3) 创建索引一个重要因素需要考虑：是对空表还是对包含数据的表创建索引。对空表创建索引在创建索引时不会对性能产生任何影响，而向表中添加数据时，会对性能产生影响。创建索引后，索引将自动启用并可以使用。可以通过禁用索引来删除对该索引的

9、访问。168.1 使用索引2. 创建聚集索引用途：聚集索引可以提高查询性能，还可以按需重新生成或重新组织来控制表碎片，所以除了个别表之外，每个表都应该有聚集索引。也可以对视图创建聚集索引 。聚集索引按下列方式实现 ： PRIMARY KEY和UNIQUE约束在创建PRIMARY KEY约束时，如果不存在该表的聚集索引且未指定唯一非聚集索引，则将自动对一列或多列创建唯一聚集索引。主键列不允许空值。在创建UNIQUE约束时，默认情况下将创建唯一非聚集索引，以便强制UNIQUE约束。如果不存在该表的聚集索引，则可以指定唯一聚集索引。178.1 使用索引将索引创建为约束的一部分后，会自动将索引命名为与

10、约束名称相同的名称。 独立于约束的索引 指定非聚集主键约束后，可以对非主键列的列创建聚集索引 索引视图若要创建索引视图，需要对一个或多个视图列定义唯一聚集索引。视图将具体化，并且结果集存储在该索引的页级别中，其存储方式与表数据存储在聚集索引中的方式相同 。188.1 使用索引3. 创建非聚集索引通常，创建非聚集索引是为了提高聚集索引未包含的常用查询的性能。可以通过下列方法实现非聚集索引 ：PRIMARY KEY和UNIQUE约束独立于约束的索引：默认情况下，如果未指定聚集，将创建非聚集索引 。索引视图的非聚集索引：对视图创建唯一的聚集索引后，便可以创建非聚集索引 。198.1 使用索引4. 创

11、建唯一索引创建唯一索引可以确保任何生成重复键值的尝试都会失败。创建UNIQUE约束和创建与约束无关的唯一索引并没有明显的区别 。唯一索引可通过以下方式实现 ： PRIMARY KEY或UNIQUE约束 独立于约束的索引：可以为一个表定义多个唯一非聚集索引 索引视图如果在键列中存在重复值，将无法创建唯一索引或约束 208.1 使用索引5. 使用Transact-SQL语句创建索引 使用CREATE INDEX语句可以创建索引，语法格式如下 CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX index_name ON ( column ASC | DESC

12、,.n ) ; := database_name. schema_name . | schema_name. table_or_view_name218.1 使用索引 UNIQUE：为表或视图创建唯一索引。唯一索引不允许两行具有相同的索引键值。视图的聚集索引必须唯一。 CLUSTERED：为表或视图创建聚集索引 。一个表或视图只允许同时有一个聚集索引。为一个视图创建唯一聚集索引会在物理上具体化该视图。必须先为视图创建唯一聚集索引，然后才能为该视图定义其他索引 。 NONCLUSTERED：为表或视图创建非聚集索引 。对于非聚集索引，数据行的物理排序独立于索引排序 。每个表都最多可包含249个非

13、聚集索引 。默认值为NONCLUSTERED 228.1 使用索引 index_name：索引的名称 。在表或视图中必须唯一，但在数据库中不必唯一。索引名称必须符合标识符的规则 。主XML索引名不得以#、#、 或字符开头。 column：索引所基于的一列或多列 。指定两个或多个列名，可为指定列的组合值创建组合索引。在table_or_view_name后的括号中，按排序优先级列出组合索引中要包括的列 。一个组合索引键中最多可组合16列。组合索引键中的所有列必须在同一个表或视图中 。238.1 使用索引 ASC | DESC ：确定特定索引列的升序或降序排序方式。默认值为ASC 。 ：要为其建立

14、索引的完全限定对象或非完全限定对象。其中选项含义同其他命令。例如：创建简单非聚集索引。以下示例为teaching数据库中student表的AGE列创建非聚集索引。USE teachingGOCREATE INDEX IX_age ON student (AGE)GO执行结果如图8.1所示。248.1 使用索引6. 使用图形工具创建索引 具体步骤详见教材258.1 使用索引 修改索引1. 禁止索引索引定义保留在元数据中，非聚集索引的索引统计信息仍保留。对视图禁用非聚集索引或聚集索引会以物理方式删除索引数据。禁用表的聚集索引可以防止对数据的访问，数据仍保留在表中，但在删除或重新生成索引之前，无法对

15、这些数据执行DML操作。 在以下情况中可能禁用一个或多个索引 ：(1) SQL Server 2005 Database Engine在SQL Server升级期间自动禁用索引 。(2) 使用ALTER INDEX手动禁用索引 。268.1 使用索引ALTER INDEX命令语句的语法格式如下： ALTER INDEX index_name | ALL ON DISABLE各选项含义如下：ALL：指定与表或视图相关联的所有索引，而不考虑索引类型。 DISABLE：将索引标记为禁用，从而不能由SQL Server 2005 Database Engine使用 。已禁用索引的索引定义保留在没有基础

16、索引数据的系统目录中 。禁用聚集索引将阻止用户访问基础表数据。278.1 使用索引例如：禁用索引。下面的示例禁用了对course表的IX_Cname索引。USE teachingGOALTER INDEX IX_Cname ON course DISABLEGO288.1 使用索引(3) 使用图形工具禁用索引 具体步骤详见教材298.1 使用索引2. 启用索引索引被禁用后一直保持禁用状态，直到它重新生成或删除 。可以使用下列方法之一，重新生成禁用的索引来启用它。(1) 带REBUILD子句的ALTER INDEX语句 ALTER INDEX命令语句的语法格式如下。 ALTER INDEX in

17、dex_name | ALL ON REBUILD308.1 使用索引REBUILD：指定将使用相同的列、索引类型、唯一性属性和排序顺序重新生成索引。REBUILD启用已禁用的索引。 例如：重新生成索引。以下示例在Course表中重新生成索引IX_Cname。USE teachingGOALTER INDEX IX_Cname ON course REBUILDGO318.1 使用索引(2) 使用图形工具启用索引 具体步骤详见教材328.1 使用索引3. 重命名索引重命名索引将用提供的新名称替换当前的索引名称 。(1) 使用sp_rename系统过程重命名索引 sp_rename系统过程的语法

18、格式如下。 sp_rename objname = object_name , newname = new_name , objtype = object_type 338.1 使用索引 objname = object_name：用户对象或数据类型的当前限定或非限定名称 。如果要重命名的对象是表中的列，则object_name的格式必须是table.column。如果要重命名的对象是索引，则object_name的格式必须是table.index。 newname = new_name：指定对象的新名称 。new_name的数据类型为sysname，无默认值 。 objtype = obje

19、ct_type：要重命名的对象的类型 。object_type的数据类型为 varchar(13)，默认值为NULL，可取COLUMN、DATABASE、INDEX、OBJECT、USERDATATYPE，具体见表8.1。348.1 使用索引例如：重命名索引。以下示例将course表中的IX_Cname索引重命名为IX_Course_Cname。USE teachingGOEXEC sp_rename N IX_Cname , N IX_Course_Cname , N INDEX (2) 使用图形工具重命名索引 具体步骤详见教材358.1 使用索引 删除索引当一个索引不再需要时，可以将其从数

20、据库中删除，以回收它当前使用的磁盘空间。以便数据库中的任何对象都可以使用此回收的空间。必须先删除PRIMARY KEY或UNIQUE约束，才能删除约束使用的索引 。删除视图或表时，将自动删除为永久性和临时性视图或表创建的索引 。删除聚集索引后，存储在聚集索引叶级中的数据行将存储在未排序的表(堆)中 。删除索引视图的聚集索引时，将自动删除同一视图的所有非聚集索引和自动创建的统计信息。手动创建的统计信息不会删除。368.1 使用索引1. 使用Transact-SQL语句删除索引 可以使用DROP INDEX命令删除索引，语句的语法格式如下。DROP INDEX index_name ON DROP

21、 INDEX语句不适用于通过定义PRIMARY KEY或UNIQUE约束创建的索引。这些约束是分别使用CREATE TABLE或ALTER TABLE语句的PRIMARY KEY或UNIQUE选项创建的。378.1 使用索引例如：删除索引。下列示例删除了student表中的IX_age索引。 USE teachingGODROP INDEX IX_age ON studentGO2. 使用图形工具删除索引 具体步骤详见教材388.2 使用视图视图可以被看成是虚拟表或存储查询。除非是索引视图，否则视图的数据不会作为非重复对象存储在数据库中。SELECT语句的结果集构成视图所返回的虚拟表。用户可以

22、采用引用表时所使用的方法，在Transact-SQL语句中引用视图名称来使用此虚拟表。398.2 使用视图 视图的作用视图是一个虚拟表，其内容由查询定义 。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少 。(1) 视图的类型标准视图 标准视图组合了一个或多个表中的数据，可以获得使用视图的大多数好处 索引视图 分区视图 分区视图在一台或多台服务器间水平连接一组成员表中的分区数据 408.2 使用视图(2) 视图的使用视图可用作安全机制，它可以允许用户通过视图访问数据，而不授予用户直接访问视图基础表的权限 使用视图有以下作用 ：着重于特定数据 视图使用户能够着重于所感兴趣的特定数

23、据和所负责的特定任务。不必要的数据或敏感数据可以不出现在视图中 。简化数据操作 可以将常用联接、投影、UNION查询和SELECT查询定义为视图，以便使用户不必在每次对该数据执行附加操作时指定所有条件和条件限定。 418.2 使用视图 提供向后兼容性视图能够在表的架构更改时为表创建向后兼容接口 。自定义数据 视图允许用户以不同方式查看数据，即使在他们同时使用相同的数据时也是如此。这在具有许多不同目的和技术水平的用户共用同一数据库时尤其有用。 例如，可创建一个视图以仅检索由客户经理处理的客户数据。该视图可以根据使用它的客户经理的登录ID决定检索哪些数据。428.2 使用视图导出和导入数据 可使用

24、视图将数据导出到其他应用程序。例如，可将teaching数据库中的student表、course表及s_c表导出至Microsoft Excel中进行数据分析 。跨服务器组合分区数据 Transact-SQL UNION集合运算符可在视图内使用，将单独表的两个或多个查询的结果组合到单一的结果集中。这在用户看来是一个单独的表，称为分区视图 。例如，如果一个表包含天津的销售数据，另一个表包含北京的销售数据，则可以对这两个表使用UNION创建一个视图。该视图代表这两个地区的销售数据。438.2 使用视图创建视图若要创建视图，必须获得数据库所有者授予创建视图的权限 。在创建视图前需考虑如下规则 ：只能

25、在当前数据库中创建视图 。视图名称必须遵循标识符的规则，且对每个架构都必须唯一 ，该名称不得与该架构包含的任何表的名称相同。可以对其他视图创建视图 。不能将规则或DEFAULT定义与视图相关联 。不能将AFTER触发器与视图相关联，只有INSTEAD OF触发器可以与之相关联 。448.2 使用视图定义视图的查询不能包含COMPUTE子句、COMPUTE BY子句或INTO关键字 。定义视图的查询不能包含ORDER BY子句，除非在SELECT语句的选择列表中还有一个TOP子句。 定义视图的查询不能包含指定查询提示的OPTION子句 。不能创建临时视图，也不能对临时表创建视图 。下列情况下必须

26、指定视图中每列的名称 视图中的任何列都是从算术表达式、内置函数或常量派生而来 ；视图中有两列或多列应具有相同名称 ；希望为视图中的列指定一个与其源列不同的名称(也可以在视图中重命名列) 。无论重命名与否，视图列都会继承其源列的数据类型 。458.2 使用视图1. 使用Transact-SQL命令创建视图 可以使用CREATE VIEW命令创建视图，语句语法格式如下 ：CREATE VIEW schema_name . view_name (column ,.n ) AS select_statement WITH CHECK OPTION ; 468.2 使用视图 view_name：视图的名

27、称 。 column ,.n ：视图中的列使用的名称 。 AS：指定视图要执行的操作 。 select_statement：定义视图的SELECT语句 。该语句可以使用多个表和其他视图 。 WITH CHECK OPTION：强制针对视图执行的所有数据修改语句都必须符合在select_statement中设置的条件 。通过视图修改行时，WITH CHECK OPTION可确保提交修改后，仍可通过视图看到数据。如果在select_statement中的任何位置使用TOP，则不能指定CHECK OPTION。478.2 使用视图例如：使用CREATE VIEW命令。以下示例使用简单SELECT语句

28、创建视图 ，此视图的数据来自teaching数据库的student和s_c表。这些数据提供有关学生的学号、姓名以及所选课程的课号和成绩 。USE teachingGOCREATE VIEW student_viewAS SELECT s.SNO, s.SNAME, sc.CNO, sc.GRADEFROM student s JOIN s_c sc on s.SNO=sc.SNOGO488.2 使用视图2. 使用图形工具创建视图具体步骤详见教材498.2 使用视图 修改视图视图定义后，可以更改视图的名称或视图的定义而无需删除并重新创建视图。删除并重新创建视图会造成与该视图关联的权限丢失。1.

29、重命名视图在重命名视图时，需考虑以下原则。要重命名的视图必须位于当前数据库中。新名称必须遵守标识符规则。仅可以重命名具有更改权限的视图。数据库所有者可以更改任何用户视图的名称 。508.2 使用视图(1) 使用系统存储过程重命名视图可以使用系统存储过程sp_rename来重命名视图名称，与重命名索引方法相同 。(2)使用图形工具重命名视图右击要重命名的视图，选择“重命名”，然后输入新视图名称，回车即可 。注意：重命名视图并不更改它在视图定义文本中的名称。要在定义中更改视图名称，应直接修改视图。518.2 使用视图2. 修改视图定义修改先前创建的视图，其中包括索引视图 。(1) 使用Transa

30、ct-SQL命令修改视图 可以使用ALTER VIEW命令修改视图定义，该命令不影响相关的存储过程或触发器，并且不会更改权限 。语法格式如下 ：ALTER VIEW schema_name . view_name ( column ,.n ) AS select_statement WITH CHECK OPTION ; 528.2 使用视图注意：如果原来的视图定义是使用WITH CHECK OPTION创建的，则只有在ALTER VIEW中也包含这些选项时，才会启用这些选项 。例如：修改teaching数据库中的视图student_view，使其只包含女学生的选课信息。 USE teachi

31、ngGOALTER VIEW student_viewAS SELECT s.SNO, s.SNAME, sc.CNO, sc.GRADEFROM student s JOIN s_c sc on s.SNO=sc.SNOWHERE s.SEX=FGO(2) 使用图形工具修改视图具体步骤详见教材538.2 使用视图3. 通过视图修改数据可以通过视图修改基础表的数据，修改方式与通过UPDATE、INSERT和DELETE命令修改表中数据的方式一样 。但是，以下限制应用于更新视图，但不应用于更新表 。任何修改(包括UPDATE、INSERT和DELETE语句)都只能引用一个基础表的列 。视图中被修

32、改的列必须直接引用表列中的基础数据，不能通过其他方式派生 。正在修改的列不受GROUP BY、HAVING或DISTINCT子句的影响。548.2 使用视图如果在视图定义中使用了WITH CHECK OPTION子句，则所有在视图上执行的数据修改语句都必须符合定义视图的SELECT语句中所设置的条件。如果使用了WITH CHECK OPTION子句，修改行时需注意不让它们在修改完成后从视图中消失 。INSERT语句必须为不允许空值并且没有DEFAULT定义的基础表中的所有列指定值。在基础表的列中修改的数据必须符合对这些列的约束，例如为空性、约束及DEFAULT定义等。 558.2 使用视图(1

33、) 通过视图添加数据 例如：通过视图将数据加载到基础表。以下示例在INSERT命令中指定一个视图名，但执行后系统将新行插入到该视图的基础表中。INSERT语句中VALUES列表的顺序必须与视图的列顺序相匹配 。568.2 使用视图USE teachingGOCREATE VIEW stu_male_view AS SELECT SNO, SNAME, AGEFROM studentWHERE SEX=MGOINSERT INTO stu_male_view VALUES (S6, HUANG, 21)GOSELECT * FROM studentGOSELECT * FROM stu_male

34、_viewGO对视图stu_male_view增加的数据反映到基础表student中，即：INSERT INTO stu_male_view VALUES (S6, HUANG, 21)语句转变为：INSERT INTO student VALUES (S6, HUANG, M, 21)578.2 使用视图(2) 通过视图更改数据例如：通过视图将数据修改到基础表。以下示例在UPDATE语句中指定一个视图名，但将修改的数据反映到该视图的基础表中 。USE teachingGOCREATE VIEW stu_male_viewAS SELECT SNO, SNAME, AGEFROM student588.2 使用视图WHERE SEX=MGOUPDATE stu_male_view SET AGE=22 WHERE SNO=S2GOSELECT * FROM studentGOSELECT * FROM stu_male_viewGO对视图stu_male_view增加的数据反映到基础表student中，即：UPDATE stu_male_view SET