SQL Server 2005实用教程 - 万水书苑-出版资源网

全文

(1)第8章. 索引的创建和使用. 本章学习目标本章主要介绍 SQL Server 2005 中索引的创建和使用方法。通过本章的学习，读者应该掌握以下内容： l 了解索引的概念 l 了解索引的优点 l 掌握索引的分类 l 掌握创建、修改和删除索引的方法. 8.1. 概述. 8.1.1 索引的概念数据库中的索引与书籍中的目录类似，如果想在一本书中快速查找所需的信息，可以利用书籍的目录快速定位，而不必阅读整本书。类似地，在数据库中，如果想在某个表中快速查找满足条件的记录，可以创建索引。索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。索引与表或视图关联，创建索引可以加快从表或视图中检索数据的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构中，使 SQL Server 可以快速有效地查找与键值关联的行。当 SQL Server 进行数据查询时，查询优化器会自动计算现有的几种执行查询方案中，哪种方案的开销最小、速度最快，然后 SQL Server 就会按照该方案来查询。如果没有建立索引，在数据库表中查询符合某种条件的记录时，系统将会从第一条记录开始对表中的所有记录进行扫描。如果有索引存在，就可以通过索引快速地找到查询的结果。如果扫描整个数据表，是指从存储数据表的起始地址开始，依次比较记录，直到找到位置。而通过索引查找记录时，因为索引是有序排列的，所以可以通过高效的有序查找算法（如折半查找等）找到索引项，再根据索引项中记录的物理地址，找到查询结果的存储位置。 8.1.2 索引的优点使用索引可以大大提高系统的性能，其具体表现在： l 可以大大加快数据检索速度。 l 通过创建唯一索引，可以保证数据记录的唯一性。 l 在使用 ORDER BY 和 GROUP BY 子句进行检索数据时，可以显著减少查询中分组和排序的时间。. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(2) SQL Server 2005 实用教程. 156. 使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。 l 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。但是，并不是在任何查询中都需要建立索引。索引带来的查找效率提高是有代价的，因为索引也要占用存储空间，而且为了维护索引的有效性，当往数据表中插入新的数据或者更新数据时，数据库还要执行额外的操作来维护索引。所以，过多的索引不一定能提高数据库性能，必须科学地设计索引，才能带来数据库性能的提高。建立索引的一般原则是： l 对经常用来检索的字段建立索引。 l 对数据表中的主键建立索引。 l 对数据表中的外键建立索引。 l 对经常用于连接的字段建立索引。对于满足以上要求的字段建立索引，可以提高系统的查询速度。 l. 8.1.3 索引的分类在 SQL Server 2005 中提供的索引类型主要有以下几类：聚集索引、非聚集索引、唯一索引、包含性列索引、索引视图、全文索引以及 XML 索引。这里主要介绍前三种索引的特点及其创建方法。 1．聚集索引和非聚集索引按照存储结构的不同，可以将索引分为两类：聚集索引和非聚集索引。每个表最多可以有 1 个聚集索引和 249 个非聚集索引。其中包括为支持表中所定义的 PRIMARY KEY 和 UNIQUE 约束而生成的索引。下面分别进行介绍。聚集索引对表在物理数据页中的数据按列进行排序，然后再重新存储到磁盘上。由于表中的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个聚集索引。聚集索引对表中的数据一一进行了排序，因此用聚集索引查找数据很快。但由于聚集索引需要将表的所有数据完全重新排列，它所需要的空间也就特别大，大概相当于表中数据所占空间的 120%。聚集索引一般创建在表中经常搜索的列或按顺序访问的列上，创建聚集索引时应该考虑以下几个因素： l 每个表只能有一个聚集索引。 l 表中的物理顺序和索引中行的物理顺序是相同的，创建任何非聚集索引之前要首先创建聚集索引，这是因为聚集索引改变了表中行的物理顺序。 l 关键值的唯一性使用 UNIQUE 关键字或由内部的唯一标识符明确维护。 l 在索引的创建过程中，SQL Server 临时使用当前数据库的磁盘空间，所以要保证有足够的空间创建聚集索引。非聚集索引具有完全独立于数据行的结构，使用非聚集索引不会影响数据表中记录的实际存储顺序。非聚集索引中存储了组成非聚集索引的关键字值和行定位器。行定位器的结构和存储内容取决于数据的存储方式。如果数据是以聚集索引方式存储的，则行定位器中存储的是聚集索引的索引键；如果数据不是以聚集索引方式存储的，则行定位器存储的是指向数据行的指针。非聚集索引将行定位器按关键字的值用一定的方式排序，这个顺序与表的行在数据页中的排序是不匹配的。由于非聚集索引使用索引页存储，因此它比聚集索引需要较少的存储空间，. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(3) 第8章. 索引的创建和使用. 157. 但检索效率比聚集索引低。由于一个表只能建立一个聚集索引，当用户需要建立多个索引时，就需要使用非聚集索引了。每个表中最多只能创建 249 个非聚集索引。在下列情况下，可以考虑使用非聚集索引： l 含有大量唯一值的字段。 l 返回很小的或者单行结果集的检索。 l 使用 ORDER BY 子句的查询。 2．唯一索引如果要求索引中的字段值不能重复，可以建立唯一索引。创建唯一索引时，对于具有单个字段的索引，要求这个字段中的各个值不能重复。在创建索引时，根据需要也可以对多个字段的组合创建索引，即一个索引中含有多个字段。索引中包含多个字段的复合索引也可以是唯一索引，对于复合索引来说，多个字段的组合取值不能重复，但对其中某个单独字段的取值可以重复。建立唯一索引的字段最好设置为 NOT NULL，因为两个 NULL 值将被认为是重复的字段值。对于已建立了唯一索引的数据表，当向表中添加记录或修改原有记录时，系统将检查添加的记录或修改后的记录是否满足唯一性的要求，如果不满足这个条件，系统会给出提示信息，此次添加或修改记录的操作将不会被接受。注意：一个索引中最多可以包含 16 个字段的组合，并且索引中的所有字段必须属于同一个数据表。. 8.2. 创建索引. 在 SQL Server 2005 中，索引可以由系统自动创建，也可以由用户手工创建。系统在创建表中的其他对象时可以附带地创建新索引，例如创建表时，如果创建主键或者唯一性约束，系统会自动创建相应的索引。例 8-1 在 STUDENT 数据库中创建一个新表 T_DEPARTMENT，并将其中的 D_NAME 字段设置为聚集的唯一索引。程序清单如下： USE STUDENT GO CREATE TABLE T_DEPARTMENT ( D_NUMBER INT, D_NAME CHAR(20) UNIQUE CLUSTERED ). 在 SQL Server 管理控制台中执行上面的程序，会创建新的数据表 T_DEPARTMENT，系统同时自动创建了唯一聚集索引，在 SQL Server 管理控制台中，可以使用系统存储过程 sp_helpindex查看索引，这里输入的 SQL 语句为： sp_helpindex T_DEPARTMENT. 程序的执行结果如图 8-1 所示。. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(4) SQL Server 2005 实用教程. 158. 图 8-1 索引显示结果窗口. 由上面的结果可以看出，系统自动生成了名称为“UQ__T_DEPARTMENT__145C0A3F” 的唯一聚集索引，使用的索引字段为 D_NAME。对数据表中的某个字段设置主键约束时，系统也会在该字段上自动创建唯一索引，该索引可以是聚集的，也可以是非聚集的。系统自动创建的索引名也会因为创建主键的场所和方法不同而有所不同。如果在 SQL Server 管理控制台中用鼠标设置主键，系统会自动创建一个唯一的聚集索引，索引名为“PK_表名” 。如果使用 Transact-SQL 语句添加主键约束，也会创建一个唯一索引，但索引名称为“PK_表名_xxxxxxxx” ，其中 x 是由系统自动生成的。这个索引可能是聚集的，也可能是非聚集的，取决于在 PRIMARY KEY 后面使用的关键字，如果使用 NONCLUSTERED 关键字，会生成非聚集的唯一索引；如果使用 CLUSTERED 关键字，会生成聚集的唯一索引。不使用关键字时，如果此表存在聚集索引，则生成非聚集的唯一索引，否则生成聚集的唯一索引。例 8-2 在 STUDENT 数据库中创建一个新表 T_SPECIALTY，并将其中的 P_NUMBER 字段设置为主键。程序清单如下： USE STUDENT GO CREATE TABLE T_SPECIALTY ( P_NUMBER INT PRIMARY KEY, P_NAME CHAR(20) ). 在 SQL Server 管理控制台中执行上面的程序，会创建新的数据表 T_SPECIALTY，系统同时自动创建了唯一聚集索引，在 SQL Server 管理控制台中使用系统存储过程 sp_helpindex 对自动生成的索引进行查看的程序语句为： sp_helpindex T_SPECIALTY. 程序的执行结果如图 8-2 所示。由上面的执行结果可以看出，系统自动生成了名称为“PK__T_SPECIALTY__164452B1” 的唯一聚集索引，使用的索引字段为 P_NUMBER。除了系统自动生成的索引外，用户也可以根据实际需要，使用以下方法创建索引： l 利用 SQL Server 管理控制台直接创建索引。 l 利用 Transact-SQL 语句中的 CREATE INDEX 命令创建索引。. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(5) 第8章. 索引的创建和使用. 159. 图 8-2 查询索引结果窗口. 注意：只有表或视图的所有者才能为表创建索引。表或视图的所有者可以随时创建索引，无论表中是否有数据，还可以通过指定限定的数据库名称，为另一个数据库中的表或视图创建索引。 8.2.1 使用 SQL Server 管理控制台创建索引使用 SQL Server 管理控制台直接创建索引可以使用两种方法。第一种方法的操作步骤如下：（1）在 SQL Server 管理控制台中展开指定的服务器和数据库，单击要创建索引的表，并右击其中的“索引”文件夹，从弹出的快捷菜单中选择“新建索引”选项，如图 8-3 所示。. 图 8-3 新建索引选择窗口. （2）在打开的“新建索引”窗口中选择“常规”选择页，在窗口右部可以输入索引的名称、选择索引的类型（聚集索引、非聚集索引等）、确定是否是唯一索引，并在索引键列框中添加索引所要用到的字段。例 8-3 创建一个新的非聚集索引，要求按照 T_STUDENT 表中的 S_NAME 字段的降序创建，索引名为 IX_S_NAME。. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(6) 160. SQL Server 2005 实用教程. 实现的步骤如下：（1）单击 T_STUDENT 数据表，并右击其中的“索引”文件夹，从弹出的快捷菜单中选择“新建索引”选项，在打开的“新建索引”窗口的“索引名称”中输入 IX_S_NAME，在“索引类型”下拉列表框中选择“非聚集”选项，单击“添加”按钮，打开“选择列”对话框，在其中选中 S_NAME 字段前面的复选框，如图 8-4 所示。. 图 8-4 “选择列”对话框. 单击“确定”按钮，关闭“选择列”对话框，在“新建索引”窗口中将 S_NAME 字段的排序顺序改为“降序” ，设置结果如图 8-5 所示，最后单击“确定”按钮，就创建了 IX_S_NAME 索引。. 图 8-5 新建索引设置结果窗口. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(7) 第8章. 索引的创建和使用. 161. 第二种方法的操作步骤如下：（1）进入表结构的设计窗口，在窗口中右击鼠标，从快捷菜单中选择“索引/键” ，会打开“索引/键”对话框，如图 8-6 所示。. 图 8-6 “索引/键”对话框. （2）在“索引/键”对话框中可以看到已经创建的索引，可以添加新索引或删除已有的索引。这里要求按照姓名的升序和出生日期的降序添加一个新索引，方法是单击“常规”选项下对应的“列”旁边的按钮，打开添加“索引列”对话框，第一个字段选择“S_NAME” ，排序顺序为“升序” ，第二个字段选择“BIRTHDAY” ， “降序” ，设置的结果如图 8-7 所示。. 图 8-7 “索引列”对话框. （3）单击“确定”按钮，关闭“索引列”对话框。在“索引/键”对话框中给这个索引起一个名称，并单击“关闭”按钮。在表结构的设计窗口中保存所做的设置，就建立好了上面的索引。 8.2.2 使用 Transact-SQL 语句创建索引利用 Transact-SQL 语句中的 CREATE INDEX 命令可以创建索引，CREATE INDEX 命令既可以创建一个可改变表的物理顺序的聚集索引，也可以创建提高查询性能的非聚集索引，其. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(8) SQL Server 2005 实用教程. 162. 基本语法形式如下： CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ). 其中，各参数的说明如下： l UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。在列包含重复值时，不能创建唯一索引。如要使用此选项，则应确定索引所包含的列均不允许 NULL 值，否则在使用时会经常出错。 l CLUSTERED：用于指定创建的索引为聚集索引。如果此选项默认，则创建的索引为非聚集索引。 l NONCLUSTERED：用于指定创建的索引为非聚集索引。其索引数据页中包含了指向数据库中实际的表数据页的指针。 l index_name：用于指定所创建的索引名称。索引名称在一个表或视图中必须唯一，但在数据库中不必唯一。索引名必须遵循 SQL Server 2005 中标识符的命名规则。 l table：用于指定创建索引的表名称。必要时还可以选择指定的数据库名称和所有者名称。 l view：用于指定创建索引的视图名称。必须使用 SCHEMABINDING 定义视图才能为视图创建索引。必须先为视图创建唯一的聚集索引，才能为该视图创建非聚集索引。 l ASC|DESC：用于指定某个具体索引列的升序或降序排序方向。默认值为升序（ASC）。 l Column：用于指定被索引的列。指定两个或者多个列名组成一个索引时，可以为指定列的组合值创建组合索引，在 table 后的圆括号中列出组合索引中要包括的列（按排序优先级排列），这种索引称为复合索引。一个索引中最多可以指定 16 个列，但列的数据类型的长度之和不能超过 900 个字节。例 8-4 使用 CREATE INDEX 语句为 T_STUDENT 表创建一个非聚集索引，索引字段为 S_NAME，索引名为 IX_STUDENTName。 USE STUDENT GO CREATE INDEX IX_STUDENTName ON T_STUDENT (S_NAME). 例 8-5 为 T_STUDENT 表创建一个复合索引，使用的字段为 SEX 和 BIRTHDAY，排序顺序 SEX 为降序，BIRTHDAY 为升序。程序清单如下： USE STUDENT GO CREATE INDEX IX_T_STUDENT2 on T_STUDENT(SEX DESC,BIRTHDAY ASC). 8.3. 查看、修改和删除索引. 8.3.1 使用 SQL Server 管理控制台查看、修改和删除索引在 SQL Server 管理控制台中展开指定的服务器和数据库，右击某个表，从弹出的快捷菜. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(9) 第8章. 索引的创建和使用. 163. 单中选择“修改”选项，进入表结构的设计窗口，在窗口中右击，选择“索引/键” ，即可打开 “管理索引”窗口，与创建索引时类似，在窗口的左部选中某个索引，在窗口右部可以查看此索引的信息，并可修改相关信息，也可以单击“删除”按钮，删除索引。 8.3.2 使用系统存储过程查看索引信息和更改索引名称 1．使用系统存储过程查看索引信息系统存储过程 sp_helpindex可以返回表的所有索引信息，其语法形式如下； sp_helpindex [@objname=]'name'. 其中，[@objname=]'name'参数用于指定当前数据库中表的名称。例 8-6 使用系统存储过程查看 T_STUDENT 表的索引信息。程序清单如下： USE STUDENT GO sp_helpindex T_STUDENT. 程序执行结果如图 8-8 所示。. 图 8-8 查看索引信息显示结果窗口. 2．使用系统存储过程更改索引名称可以使用系统存储过程 sp_rename 更改索引的名称，其语法形式如下： sp_rename[@objname=]'object_name', [@newname=]'new_name' [ , [ @objtype = ] 'object_type' ]. 例 8-7 将 T_STUDENT 表中的索引 IX_T_STUDENT2 的名称更改为 IX_T_STUDENT_ SexAndBirth。程序清单如下： USE STUDENT GO sp_rename 'T_STUDENT.IX_T_STUDENT2','IX_T_STUDENT_SexAndBirth','index'. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(10) 164. SQL Server 2005 实用教程. 8.3.3 使用 Transact-SQL 语句删除索引当不再需要某个索引时，可以使用 Transact-SQL 语句中的 DROP INDEX 命令删除索引。 DROP INDEX 命令可以删除一个或多个当前数据库中的索引，其语法形式如下： DROP INDEX 'table.index | view.index' [ ,...n ]. 其中，table | view 用于指定索引列所在的表或索引视图；index 用于指定要删除的索引名称。注意：DROP INDEX 命令不能删除由 CREATE TABLE 或者 ALTER TABLE 命令创建的主键或者唯一性约束索引。例 8-8 删除 T_STUDENT 表中的索引 IX_T_STUDENT_SEXAndBirth。程序清单如下： USE STUDENT GO DROP INDEX T_STUDENT.IX_T_STUDENT_SEXAndBirth. 在 SQL Server 管理控制台中执行上述 SQL 语句，显示结果如图 8-9 所示。. 图 8-9 删除索引结果窗口. 8.4. 思考与练习. 1．使用索引有哪些优点？ 2．聚集索引和非聚集索引各有什么特点？ 3．使用 Transact-SQL 语句创建索引的命令是什么？ 4．使用哪个系统存储过程可以查看索引信息？ 5．上机使用两种方法创建一个唯一的聚集索引，然后查看此索引的信息，最后删除此索引。. PDF created with pdfFactory Pro trial version www.pdffactory.com.

(11)