SQLServer2008第7章索引与视图

第7章 索引与视图

本章的学习目标：

索引的优点和缺点 聚集索引和非聚集索引的特点 索引的类型 视图的概念、特点和类型 使用CREATE VIEW语句创建视图 通过视图修改基表中的数据

7.1 索引概述

通过创建和设计良好的索引进行数据查 询，可以显著提高数据库查询和应用程 序的性能，减少磁盘I/O操作，降低系统 资源的消耗。

7.1.1 索引的基本概念

数据库系统用下列两种方法之一来访问数据：(1)表扫描，就是指系统将指针放在该表的表头数据所在的数 据页上，然后按照数据页的排列顺序，逐页地从前向后扫描 该表数据所占有的全部数据页，直至扫描完表中的全部记录。 在扫描时，如果找到符合查询条件的记录，那么就将这条记 录挑选出来。最后，将全部挑选出来符合查询语句条件的记 录显示出来。 (2)使用索引查找。索引是一种树状结构，其中存储了关键字 和指向包含关键字所在记录的数据页的指针。当使用索引查 找时系统将沿着索引的树状结构，根据索引中关键字和指针 找到符合查询条件的记录。最后将全部查找到的符合查询语 句条件的记录显示出来。

索引是一种与表或视图关联的物理结构， 能提供一种以一列或多列的值为基础迅 速查找表中行的能力，用来加快从表或 视图中检索数据行的速度

7.1.2 索引的分类

在Microsoft SQL Server 2008 R2系统中 有两种基本的索引类型：聚集索引和非 聚集索引。除此之外，还有唯一性索引、 包含性列索引、索引视图、全文索引及 XML索引等。

1.索引文件架构(1)堆文件 堆是不含聚集索引的表，表中的数据没有任何的顺序。 堆结构中数据按照插入的先后次序存放，堆文件的数 据页面不一定在物理上相邻。堆文件执行插入操作很 容易，但是效率不高。 (2) B+树的总体结构 B+树(Balance树，平衡树)是目前广泛采用的动态文 件结构. 在B+树索引中，索引分级组织成一棵树。最上层的索 引节点称为根节点，中间节点按照索引数据量分成不 同的层次。最下层的索引节点称为叶节点。

.聚集索引

聚集索引是一种数据表的物理顺序与索引顺序相同的索引。 建立索引时，系统将对表的物理数据页中的数据按列进行排列， 然后再重新存储到磁盘上，即聚集索引与数据是混为一体的。聚 集索引的叶级和非叶级构成了一个特殊类型的B+树结构. 聚集索引的特点如下： (1)表的数据按照索引的数据顺序排列。 (2)每个数据表只能建立一个聚集索引，并且会在第一个建立， 常常会在主键所在的列或者最常查询的列上建立聚集索引。

(3)索引将占用用户数据库的空间。 (4)适合范围查询。

非聚集索引

非聚集索引是一种数据表的物理顺序与索引顺 序不相同的索引。非聚集索引与聚集索引具有 相同的B树结构，但是在非聚集索引中，基础 表的数据行不是按照非聚集键的顺序排序和存 储，且非聚集索引的叶级是由索引页而不是由 数据页组成。 非聚集索引既可以定义在表或视图的聚集索引 上，也可以定义在表或视图的堆上。

创建一个非聚集索引时，应该注意下列事项：(1)如果没有指定索引类型，那么默认的类型是非聚集索引。 (2)索引页的叶级只包含索引的关键字，不包含实际的数据 (3)每个表最多可以创建249个非聚集索引。 (4)聚集索引应在非聚集索引被创建之前创建。 (5)唯一性是由叶级维护的。 (6)以下情况发生时，SQL Server会自动重建现有的非聚集索 引： 删除现有的聚集索引时 创建聚集索引时 使用DROP_EXISTING选项来改变聚集索引列的定义时

聚集索引上的非聚集索引

当在一个聚集索引上构建非聚集索引时， 索引中的指针值就是数据行的聚集索引 键值。一旦定位到索引值，SQL Server 就使用聚集键导航聚集索引来检索所有 需要的列。

唯一索引

唯一索引是指索引值必须是唯一的，不允许数 据表中具有两行相同的索引值。 聚集索引和非聚集索引是从索引数据存储的角 度来区分的；而唯一索引和非唯一索引是从索 引值来区分的，所以唯一索引和非唯一索引既 可以是聚集索引，也可以是非聚集索引，只要 列中的数据是唯一的，就可以在一张表中创建 一个唯一索引和多个非聚集索引。

7.2 索引的操作7.2.1 创建索引 创建索引的方式可以分为直接方法和间接方法。

直接创建索引的方法就是使用命令和工具直接创建索引。 间接创建索引就是通过创建其他对象而附加创建了索引。

主键约束或唯一性约束创建的索引的优先级高于使用 CREATE INDEX语句创建的索引。当在表上定义主键或 唯一性约束时，如果表中已经有了使用CREATE INDEX 语句创建的标准索引时，那么主键约束或唯一性约束 创建的索引覆盖以前创建的标准索引。

1. 直接使用Microsoft SQL Server Management Studio创建索引。

(1)从“开始”菜单上选择“程序”| Microsoft SQL Server 2008 R2| SQL Server Management Studio命令，使用Windows或 SQL Server身份验证建立连接。 (2)在“对象资源管理器”窗口中，打开要 建立索引的表“业务记录”表，单击前面的 “+”,展开选项，右键单击“索引”项，选择 “新建索引”命令，打开“新建索引”对话框

(3)在“选择页”窗

格中选择“常规”项，输入索引名 称“入住时间_index”,选择非聚集索引类型，单击 “添加”按钮，打开“选择列”对话框，如图7-5所示。 在该窗体中选择“入住时间”字段作为索引列，单击 “确定”按钮，返回“新建索引”对话框。 (4)在“选项”页可设置索引的填充因子等参数，填充 因子指示索引页的填满程度。在“包含性列”页中， 设置索引页还可以包含的其他非键列。在“存储”页， 可以对指定的文件组或分区方案建立索引。在“空间” 页可以设置空间索引的空间属性。在“筛选器”页可 以定义筛选索引的筛选表达式。 (5)设置完成后，单击“确定”按钮，则该索引创建完 成。

2. 使用Transact-SQL语句创建 索引

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_or_view_name ( column [ ASC | DESC ] [ ,...n ] ) [INCLUDE (column_name[, …n])] [ WITH ( PAD_INDEX = {ON | OFF} | FILLFACTOR = fillfactor | SORT_IN_TEMPDB = {ON | OFF} | IGNORE_DUP_KEY = {ON | OFF} | STATISTICS_NORECOMPUTE = {ON | OFF} | DROP_EXISTING = {ON | OFF} | ONLINE = {ON | OFF} | ALLOW_ROW_LOCKS = {ON | OFF} | ALLOW_PAGE_LOCKS = {ON | OFF} | MAXDOP = max_degree_of_parallelism)[, …n]] ) ON {partition_schema_name (column_name) | filegroup_name | default}

UNIOUE选项表示创建唯一性的索引，这时在索引列中不能有相同的两个列值存在。CLUSTERED选项表示创 建聚集索引。NONCLUSTERED选项表示创建非聚集索引，非聚集索引是CREATE INDEX语句的默认值。 第一个ON关键字表示索引所属的表或视图，这里用于指定表或视图的名称和相应的列名称。列名称后面可以 使用ASC或DESC关键字，指定升序排列或降序排列，其默认值是ASC。第二个ON关键字用于指定该索引所在 的分区方案或文件组名称。 INCLUDE子句用于指定将要包含到非聚集索引的页级中的非键列。 PAD_INDEX选项用于指定索引的中间页级，也就是说为非叶级索引页指定填充度。这时的填充度由 FILLFACTOR选项指定。 FILLFACTOR选项用于指定叶级索引页的填充度。 SORT_IN_TEMPDB选项为ON时，用于指定创建索引时产生的中间结果，在tempdb数据库中进行排序。该选 项为OFF时，在当前数据库中排序。 IGNORE_DUP_KEY选项用于指定唯一性索引键冗余数据的系统行为。当为ON时，系统发出警告信息，只有 违反唯一性行的数据插入失败。该选项为OFF时，取消整个INSERT语句并且发出错误信息。 STATISTICS_NORECOMPUTE选项用于指定是否重新计算分发统计信息。为ON时，不自动计算过期的索引统 计信息。为OFF时，启动自动计算功能。 DROP_EXISTING选项用于是否可以删除指定的索

引并且重建该索引。为ON时，可以删除并且重建已有的索 引。为OFF时，不能删除重建。 ONLINE选项用于指定索引操作期间基础表和关联索引是否可用于查询。为ON时，不持有表锁，允许用于查 询。为OFF时，持有表锁，索引操作期间不能执行查询。 ALLOW_ROW_LOCKS选项用于指定是否使用行锁，如果为ON,表示使用行锁。 ALLOW_PAGE_LOCKS选项用于指定是否使用页锁，如果为ON,表示使用页锁。 MAXDOP选项用于指定索引操作期间覆盖最大并行度的配置选项，主要目的是限制执行并行计划过程中使用 的处理器数量。

例7-1 例7-2 例7-3 例7-4 例7-5 例7-6

7.2.2 查看索引1.直接使用Microsoft SQL Server Management Studio查看索引 (1)在“对象资源管理器”窗口中，打开要查 看索引的表“客房信息”表，单击右键，在弹 出菜单中，选择“设计”,进入表设计器。 (2)在“表设计器”中，打开“索引/键”窗口。 如图7-6所示。可以查看表的所有索引，选中 某个索引后还可以查询该索引的名称和列字段 等属性。