索引与视图
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
23 23
8.1 使用索引
[ ASC | DESC ]：确定特定索引列的升序或降序排序方 式。默认值为ASC 。 <object>：要为其建立索引的完全限定对象或非完全限 定对象。其中选项含义同其他命令。 例如：创建简单非聚集索引。以下示例为teaching数据库 中student表的AGE列创建非聚集索引。 USE teaching GO CREATE INDEX IX_age ON student (AGE) GO 执行结果如图8.1所示。
20 20
8.1 使用索引
5. 使用Transact-SQL语句创建索引
使用CREATE INDEX语句可以创建索引，语法格式如下 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] ) [;] <object> ::= { [ database_name. [ schema_name ] . | schema_name. ] table_or_view_name }
第8章 索引与视图
1 1
本章内容
8.1 使用索引
8.2 使用视图
2 2
8.1 使用索引
索引是与表或视图关联的磁盘上结构，索引中的键存储 在一个结构中，使SQL Server可以快速有效地查找与键值关 联的行。 建立索引的优点： 索引可以减少为返回查询结果集而必须读取的数据量； 索引还可以强制表中的行具有唯一性，从而确保表数据的数 据完整性；使用索引可以快速找到表或索引视图中的特定信 息。
21 21
8.1 使用索引
UNIQUE：为表或视图创建唯一索引。
唯一索引不允许两行具有相同的索引键值。视图的聚集 索引必须唯一。
CLUSTERED：为表或视图创建聚集索引 。
一个表或视图只允许同时有一个聚集索引。为一个视图 创建唯一聚集索引会在物理上具体化该视图。必须先为视图 创建唯一聚集索引，然后才能为该视图定义其他索引 。
3 3
8.1 使用索引
索引的分类
表或视图的索引可以包括以下几类：
(1) 惟一索引 在表中建立惟一性索引时，组成该索引的字段或字段组 合在表中具有惟一值，也就是说，对于表中的任何两行记录 来说，索引键的值都是各不相同。
4 4
8.1 使用索引
(2) 聚集索引
聚集索引根据数据行的键值在表或视图中排序和存储这 些数据行，即聚集索引决定了数据的物理顺序。只有在表中 建立了一个聚集索引后，数据才会按照索引键值指定的顺序 存储到表中。由于一个表中的数据只能按照一种顺序来存储， 所以在一个表中只能建立一个聚集索引。
26 26
8.1 使用索引
ALTER INDEX命令语句的语法格式如下：
ALTER INDEX { index_name | ALL }
ON <object> DISABLE 各选项含义如下：
ALL：指定与表或视图相关联的所有索引，而不考虑索 引类型。
DISABLE：将索引标记为禁用，从而不能由SQL Server 2005 Database Engine使用 。 已禁用索引的索引定义保留在没有基础索引数据的系统 目录中 。禁用聚集索引将阻止用户访问基础表数据。
19 19
8.1 使用索引
4. 创建唯一索引
创建唯一索引可以确保任何生成重复键值的尝试都会失 败。创建UNIQUE约束和创建与约束无关的唯一索引并没有 明显的区别 。 唯一索引可通过以下方式实现 ：
PRIMARY KEY或UNIQUE约束 独立于约束的索引：可以为一个表定义多个唯一非聚集 索引 索引视图 如果在键列中存在重复值，将无法创建唯一索引或约束
(6) 全文索引
一种特殊类型的基于标记的功能性索引，由Microsoft SQL Server全文引擎(MSFTESQL)服务创建和维护，用于帮 助在字符串数据中搜索复杂的词。
(7) XML索引 xml数据类型列中XML二进制大型对象的已拆分持久表 示形式 。 注意： 对表中的列定义了PRIMARY KEY约束和UNIQUE约束 时，会自动创建索引。
NONCLUSTERED：为表或视图创建非聚集索引 。对 于非聚集索引，数据行的物理排序独立于索引排序 。每个表 都最多可包含249个非聚集索引 。 默认值为NONCLUSTERED
22 22
8.1 使用索引
index_name：索引的名称 。
在表或视图中必须唯一，但在数据库中不必唯一。索引 名称必须符合标识符的规则 。
2. 创建聚集索引
用途：聚集索引可以提高查询性能，还可以按需重新生 成或重新组织来控制表碎片，所以除了个别表之外，每个表 都应该有聚集索引。也可以对视图创建聚集索引 。 聚集索引按下列方式实现 ： PRIMARY KEY和UNIQUE约束 在创建PRIMARY KEY约束时，如果不存在该表的聚集 索引且未指定唯一非聚集索引，则将自动对一列或多列创建 唯一聚集索引。主键列不允许空值。 在创建UNIQUE约束时，默认情况下将创建唯一非聚集 索引，以便强制UNIQUE约束。如果不存在该表的聚集索引， 则可以指定唯一聚集索引。
14 14
8.1 使用索引
默认情况下，创建的唯一聚集索引可以强制PRIMARY KEY约束，除非表中已存在聚集索引或指定了唯一的非聚集 索引。默认情况下，创建的唯一非聚集索引可以强制 UNIQUE约束，除非已明确指定唯一的聚集索引且表中不存 在聚集索引。 使用CREATE INDEX语句或SQL Server Management Studio对象资源管理器中的“新建索引”对话框创建独立于 约束的索引 。 必须指定索引的名称、表以及应用该索引的列。还可以 指定索引选项和索引位置、文件组或分区方案。默认情况下， 如果未指定聚集或唯一选项，将创建非聚集的非唯一索引
24 24
8.1 使用索引
6. 使用图形工具创建索引
具体步骤详见教材
Fra Baidu bibliotek25 25
8.1 使用索引
修改索引 1. 禁止索引
索引定义保留在元数据中，非聚集索引的索引统计信息 仍保留。对视图禁用非聚集索引或聚集索引会以物理方式删 除索引数据。禁用表的聚集索引可以防止对数据的访问，数 据仍保留在表中，但在删除或重新生成索引之前，无法对这 些数据执行DML操作。 在以下情况中可能禁用一个或多个索引 ： (1) SQL Server 2005 Database Engine在SQL Server升级 期间自动禁用索引 。 (2) 使用ALTER INDEX手动禁用索引 。
6 6
8.1 使用索引
(4) 包含性列索引
一种非聚集索引，它扩展后不仅包含键列，还包含非键 列。
(5) 索引视图
视图的索引将具体化(执行)视图，并将结果集永久存储在 唯一的聚集索引中，而且其存储方法与带聚集索引的表的存 储方法相同。创建聚集索引后，可以为视图添加非聚集索引。
7 7
8.1 使用索引
9 9
8.1 使用索引
(2) 查询准则
为经常用于查询中的谓词和联接条件的所有列创建非聚 集索引。
涵盖索引可以提高查询性能，因为符合查询要求的全部 数据都存在于索引本身中 。 将插入或修改尽可能多的行的查询写入单个语句内，而 不要使用多个查询更新相同的行。 评估查询类型以及如何在查询中使用列 。例如，在完全 匹配查询类型中使用的列就适合用于非聚集索引或聚集索引。
主XML索引名不得以#、##、@ 或@@字符开头。
column：索引所基于的一列或多列 。
指定两个或多个列名，可为指定列的组合值创建组合索 引。在table_or_view_name后的括号中，按排序优先级列出 组合索引中要包括的列 。 一个组合索引键中最多可组合16列。组合索引键中的所 有列必须在同一个表或视图中 。
17 17
8.1 使用索引
将索引创建为约束的一部分后，会自动将索引命名为与 约束名称相同的名称。 独立于约束的索引 指定非聚集主键约束后，可以对非主键列的列创建聚集 索引 索引视图 若要创建索引视图，需要对一个或多个视图列定义唯一 聚集索引。视图将具体化，并且结果集存储在该索引的页级 别中，其存储方式与表数据存储在聚集索引中的方式相同 。
27 27
8.1 使用索引
例如：禁用索引。下面的示例禁用了对course表的 IX_Cname索引。 USE teaching GO
ALTER INDEX IX_Cname ON course DISABLE
GO
28 28
8.1 使用索引
(3) 使用图形工具禁用索引
8.1 使用索引
(4) 索引特征
在确定某一索引适合某一查询之后，可以选择最适合具 体情况的索引类型。创建索引时需确定以下选项。
聚集还是非聚集
唯一还是非唯一
单列还是多列 索引中的列是升序排序还是降序排序
13 13
8.1 使用索引
创建索引
1. 创建索引步骤如下：
(1) 设计索引 索引设计包括确定要使用的列，选择索引类型(例如聚集 或非聚集)，选择适当的索引选项，以及确定文件组或分区 方案布置 。 (2) 确定最佳的创建方法 使用CREATE TABLE或ALTER TABLE对列定义 PRIMARY KEY或UNIQUE约束。
10 10
8.1 使用索引
(3) 列准则
对于聚集索引，应保持较短的索引键长度。另外，对唯 一列或非空列创建聚集索引可以使聚集索引效率高 .
不能将ntext、text、image、varchar(max)、 nvarchar(max)和varbinary(max)数据类型的列指定为索引键 列 ，不过，varchar(max)、nvarchar(max)、varbinary(max) 和xml数据类型的列可以作为非键索引列参与非聚集索引。 xml数据类型的列只能在XML索引中用作键列 。
表具有聚集索引，则该表称为聚集表。否则，其数据行 存储在一个称为堆的无序结构中 。
5 5
8.1 使用索引
(3) 非聚集索引 非聚集索引包含非聚集索引键值，并且每个键值项都有 指向包含该键值的数据行的指针。具有独立于数据行的结构 。 从非聚集索引中的索引行指向数据行的指针称为行定位 器。行定位器的结构取决于数据页是存储在堆中还是聚集表 中。对于堆，行定位器是指向行的指针。对于聚集表，行定 位器是聚集索引键。 注意： 聚集索引和非聚集索引都可以是唯一的，即任何两行都 不能有相同的索引键值 ；索引也可以不是唯一的，即多行可 以共享同一键值 ；每当修改了表数据后，都会自动维护表或 视图的索引 。
8 8
8.1 使用索引
索引设计准则
要设计出好的索引集，需要了解数据库、查询和数据列 的特征 。
(1) 数据库准则
避免对经常更新的表创建过多的索引，并且列要尽可能 少；使用多个索引可以提高更新少而数据量大的查询的性能。
对小表进行索引可能不会产生优化效果 。
当视图包含聚合、表联接或聚合和联接的组合时，视图 的索引可以显著地提升性能 。
15 15
8.1 使用索引
(3) 创建索引
一个重要因素需要考虑：是对空表还是对包含数据的表 创建索引。对空表创建索引在创建索引时不会对性能产生任 何影响，而向表中添加数据时，会对性能产生影响。
创建索引后，索引将自动启用并可以使用。可以通过禁 用索引来删除对该索引的访问。
16 16
8.1 使用索引
11 11
8.1 使用索引
如果索引包含多个列，则应考虑列的顺序 。
用于等于 (=)、大于 (>)、小于 (<) 或BETWEEN搜索条件 的WHERE子句或者参与联接的列应该放在最前面。其他列 应该基于其非重复级别进行排序，就是说，从最不重复的列 到最重复的列。
考虑对计算列进行索引 。
12 12
18 18
8.1 使用索引
3. 创建非聚集索引
通常，创建非聚集索引是为了提高聚集索引未包含的常 用查询的性能。
可以通过下列方法实现非聚集索引 ：
PRIMARY KEY和UNIQUE约束
独立于约束的索引：默认情况下，如果未指定聚集，将 创建非聚集索引 。
索引视图的非聚集索引：对视图创建唯一的聚集索引后， 便可以创建非聚集索引 。
8.1 使用索引
[ ASC | DESC ]：确定特定索引列的升序或降序排序方 式。默认值为ASC 。 <object>：要为其建立索引的完全限定对象或非完全限 定对象。其中选项含义同其他命令。 例如：创建简单非聚集索引。以下示例为teaching数据库 中student表的AGE列创建非聚集索引。 USE teaching GO CREATE INDEX IX_age ON student (AGE) GO 执行结果如图8.1所示。
20 20
8.1 使用索引
5. 使用Transact-SQL语句创建索引
使用CREATE INDEX语句可以创建索引，语法格式如下 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON <object> ( column [ ASC | DESC ] [ ,...n ] ) [;] <object> ::= { [ database_name. [ schema_name ] . | schema_name. ] table_or_view_name }
第8章 索引与视图
1 1
本章内容
8.1 使用索引
8.2 使用视图
2 2
8.1 使用索引
索引是与表或视图关联的磁盘上结构，索引中的键存储 在一个结构中，使SQL Server可以快速有效地查找与键值关 联的行。 建立索引的优点： 索引可以减少为返回查询结果集而必须读取的数据量； 索引还可以强制表中的行具有唯一性，从而确保表数据的数 据完整性；使用索引可以快速找到表或索引视图中的特定信 息。
21 21
8.1 使用索引
UNIQUE：为表或视图创建唯一索引。
唯一索引不允许两行具有相同的索引键值。视图的聚集 索引必须唯一。
CLUSTERED：为表或视图创建聚集索引 。
一个表或视图只允许同时有一个聚集索引。为一个视图 创建唯一聚集索引会在物理上具体化该视图。必须先为视图 创建唯一聚集索引，然后才能为该视图定义其他索引 。
3 3
8.1 使用索引
索引的分类
表或视图的索引可以包括以下几类：
(1) 惟一索引 在表中建立惟一性索引时，组成该索引的字段或字段组 合在表中具有惟一值，也就是说，对于表中的任何两行记录 来说，索引键的值都是各不相同。
4 4
8.1 使用索引
(2) 聚集索引
聚集索引根据数据行的键值在表或视图中排序和存储这 些数据行，即聚集索引决定了数据的物理顺序。只有在表中 建立了一个聚集索引后，数据才会按照索引键值指定的顺序 存储到表中。由于一个表中的数据只能按照一种顺序来存储， 所以在一个表中只能建立一个聚集索引。
26 26
8.1 使用索引
ALTER INDEX命令语句的语法格式如下：
ALTER INDEX { index_name | ALL }
ON <object> DISABLE 各选项含义如下：
ALL：指定与表或视图相关联的所有索引，而不考虑索 引类型。
DISABLE：将索引标记为禁用，从而不能由SQL Server 2005 Database Engine使用 。 已禁用索引的索引定义保留在没有基础索引数据的系统 目录中 。禁用聚集索引将阻止用户访问基础表数据。
19 19
8.1 使用索引
4. 创建唯一索引
创建唯一索引可以确保任何生成重复键值的尝试都会失 败。创建UNIQUE约束和创建与约束无关的唯一索引并没有 明显的区别 。 唯一索引可通过以下方式实现 ：
PRIMARY KEY或UNIQUE约束 独立于约束的索引：可以为一个表定义多个唯一非聚集 索引 索引视图 如果在键列中存在重复值，将无法创建唯一索引或约束
(6) 全文索引
一种特殊类型的基于标记的功能性索引，由Microsoft SQL Server全文引擎(MSFTESQL)服务创建和维护，用于帮 助在字符串数据中搜索复杂的词。
(7) XML索引 xml数据类型列中XML二进制大型对象的已拆分持久表 示形式 。 注意： 对表中的列定义了PRIMARY KEY约束和UNIQUE约束 时，会自动创建索引。
NONCLUSTERED：为表或视图创建非聚集索引 。对 于非聚集索引，数据行的物理排序独立于索引排序 。每个表 都最多可包含249个非聚集索引 。 默认值为NONCLUSTERED
22 22
8.1 使用索引
index_name：索引的名称 。
在表或视图中必须唯一，但在数据库中不必唯一。索引 名称必须符合标识符的规则 。
2. 创建聚集索引
用途：聚集索引可以提高查询性能，还可以按需重新生 成或重新组织来控制表碎片，所以除了个别表之外，每个表 都应该有聚集索引。也可以对视图创建聚集索引 。 聚集索引按下列方式实现 ： PRIMARY KEY和UNIQUE约束 在创建PRIMARY KEY约束时，如果不存在该表的聚集 索引且未指定唯一非聚集索引，则将自动对一列或多列创建 唯一聚集索引。主键列不允许空值。 在创建UNIQUE约束时，默认情况下将创建唯一非聚集 索引，以便强制UNIQUE约束。如果不存在该表的聚集索引， 则可以指定唯一聚集索引。
14 14
8.1 使用索引
默认情况下，创建的唯一聚集索引可以强制PRIMARY KEY约束，除非表中已存在聚集索引或指定了唯一的非聚集 索引。默认情况下，创建的唯一非聚集索引可以强制 UNIQUE约束，除非已明确指定唯一的聚集索引且表中不存 在聚集索引。 使用CREATE INDEX语句或SQL Server Management Studio对象资源管理器中的“新建索引”对话框创建独立于 约束的索引 。 必须指定索引的名称、表以及应用该索引的列。还可以 指定索引选项和索引位置、文件组或分区方案。默认情况下， 如果未指定聚集或唯一选项，将创建非聚集的非唯一索引
24 24
8.1 使用索引
6. 使用图形工具创建索引
具体步骤详见教材
Fra Baidu bibliotek25 25
8.1 使用索引
修改索引 1. 禁止索引
索引定义保留在元数据中，非聚集索引的索引统计信息 仍保留。对视图禁用非聚集索引或聚集索引会以物理方式删 除索引数据。禁用表的聚集索引可以防止对数据的访问，数 据仍保留在表中，但在删除或重新生成索引之前，无法对这 些数据执行DML操作。 在以下情况中可能禁用一个或多个索引 ： (1) SQL Server 2005 Database Engine在SQL Server升级 期间自动禁用索引 。 (2) 使用ALTER INDEX手动禁用索引 。
6 6
8.1 使用索引
(4) 包含性列索引
一种非聚集索引，它扩展后不仅包含键列，还包含非键 列。
(5) 索引视图
视图的索引将具体化(执行)视图，并将结果集永久存储在 唯一的聚集索引中，而且其存储方法与带聚集索引的表的存 储方法相同。创建聚集索引后，可以为视图添加非聚集索引。
7 7
8.1 使用索引
9 9
8.1 使用索引
(2) 查询准则
为经常用于查询中的谓词和联接条件的所有列创建非聚 集索引。
涵盖索引可以提高查询性能，因为符合查询要求的全部 数据都存在于索引本身中 。 将插入或修改尽可能多的行的查询写入单个语句内，而 不要使用多个查询更新相同的行。 评估查询类型以及如何在查询中使用列 。例如，在完全 匹配查询类型中使用的列就适合用于非聚集索引或聚集索引。
主XML索引名不得以#、##、@ 或@@字符开头。
column：索引所基于的一列或多列 。
指定两个或多个列名，可为指定列的组合值创建组合索 引。在table_or_view_name后的括号中，按排序优先级列出 组合索引中要包括的列 。 一个组合索引键中最多可组合16列。组合索引键中的所 有列必须在同一个表或视图中 。
17 17
8.1 使用索引
将索引创建为约束的一部分后，会自动将索引命名为与 约束名称相同的名称。 独立于约束的索引 指定非聚集主键约束后，可以对非主键列的列创建聚集 索引 索引视图 若要创建索引视图，需要对一个或多个视图列定义唯一 聚集索引。视图将具体化，并且结果集存储在该索引的页级 别中，其存储方式与表数据存储在聚集索引中的方式相同 。
27 27
8.1 使用索引
例如：禁用索引。下面的示例禁用了对course表的 IX_Cname索引。 USE teaching GO
ALTER INDEX IX_Cname ON course DISABLE
GO
28 28
8.1 使用索引
(3) 使用图形工具禁用索引
8.1 使用索引
(4) 索引特征
在确定某一索引适合某一查询之后，可以选择最适合具 体情况的索引类型。创建索引时需确定以下选项。
聚集还是非聚集
唯一还是非唯一
单列还是多列 索引中的列是升序排序还是降序排序
13 13
8.1 使用索引
创建索引
1. 创建索引步骤如下：
(1) 设计索引 索引设计包括确定要使用的列，选择索引类型(例如聚集 或非聚集)，选择适当的索引选项，以及确定文件组或分区 方案布置 。 (2) 确定最佳的创建方法 使用CREATE TABLE或ALTER TABLE对列定义 PRIMARY KEY或UNIQUE约束。
10 10
8.1 使用索引
(3) 列准则
对于聚集索引，应保持较短的索引键长度。另外，对唯 一列或非空列创建聚集索引可以使聚集索引效率高 .
不能将ntext、text、image、varchar(max)、 nvarchar(max)和varbinary(max)数据类型的列指定为索引键 列 ，不过，varchar(max)、nvarchar(max)、varbinary(max) 和xml数据类型的列可以作为非键索引列参与非聚集索引。 xml数据类型的列只能在XML索引中用作键列 。
表具有聚集索引，则该表称为聚集表。否则，其数据行 存储在一个称为堆的无序结构中 。
5 5
8.1 使用索引
(3) 非聚集索引 非聚集索引包含非聚集索引键值，并且每个键值项都有 指向包含该键值的数据行的指针。具有独立于数据行的结构 。 从非聚集索引中的索引行指向数据行的指针称为行定位 器。行定位器的结构取决于数据页是存储在堆中还是聚集表 中。对于堆，行定位器是指向行的指针。对于聚集表，行定 位器是聚集索引键。 注意： 聚集索引和非聚集索引都可以是唯一的，即任何两行都 不能有相同的索引键值 ；索引也可以不是唯一的，即多行可 以共享同一键值 ；每当修改了表数据后，都会自动维护表或 视图的索引 。
8 8
8.1 使用索引
索引设计准则
要设计出好的索引集，需要了解数据库、查询和数据列 的特征 。
(1) 数据库准则
避免对经常更新的表创建过多的索引，并且列要尽可能 少；使用多个索引可以提高更新少而数据量大的查询的性能。
对小表进行索引可能不会产生优化效果 。
当视图包含聚合、表联接或聚合和联接的组合时，视图 的索引可以显著地提升性能 。
15 15
8.1 使用索引
(3) 创建索引
一个重要因素需要考虑：是对空表还是对包含数据的表 创建索引。对空表创建索引在创建索引时不会对性能产生任 何影响，而向表中添加数据时，会对性能产生影响。
创建索引后，索引将自动启用并可以使用。可以通过禁 用索引来删除对该索引的访问。
16 16
8.1 使用索引
11 11
8.1 使用索引
如果索引包含多个列，则应考虑列的顺序 。
用于等于 (=)、大于 (>)、小于 (<) 或BETWEEN搜索条件 的WHERE子句或者参与联接的列应该放在最前面。其他列 应该基于其非重复级别进行排序，就是说，从最不重复的列 到最重复的列。
考虑对计算列进行索引 。
12 12
18 18
8.1 使用索引
3. 创建非聚集索引
通常，创建非聚集索引是为了提高聚集索引未包含的常 用查询的性能。
可以通过下列方法实现非聚集索引 ：
PRIMARY KEY和UNIQUE约束
独立于约束的索引：默认情况下，如果未指定聚集，将 创建非聚集索引 。
索引视图的非聚集索引：对视图创建唯一的聚集索引后， 便可以创建非聚集索引 。