v管理与设计实现

1、第第6章章 索引索引6.1 索引的概念索引的概念6.2 索引的类型索引的类型6.3 创建索引创建索引6.4 删除索引删除索引6.1 索引的概念索引的概念 数据库中的索引与书籍中的索引类似，在一本数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的的索引是一个词语列表，其中注明了包含各个词的页码。而数据库

2、中的索引是某个表中一列或者若干页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。据页的逻辑指针清单。索引：索引：是是SQL Server编排数据的内部方法。编排数据的内部方法。它为它为SQL Server提供一种方法来编排查询数据的路由。提供一种方法来编排查询数据的路由。索引页：索引页：数据库中存储索引的数据页；索引页存放检索数据行的数据库中存储索引的数据页；索引页存放检索数据行的关键字页以及该数据行的地址指针。关键字页以及该数据行的地址指针。索引的作用索引的作用通过使用索引，可以大大提高数据

3、库的检索速度，改善通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。数据库性能。注意：注意：索引是对数据表中一个或多个字段的值进行排序的索引是对数据表中一个或多个字段的值进行排序的结构；索引键可以是单个字段，也可是包含多个字结构；索引键可以是单个字段，也可是包含多个字段的组合字段。段的组合字段。使用索引可加快数据检索速度，但为每个字段都建使用索引可加快数据检索速度，但为每个字段都建立索引是没有必要。因为索引自身也需维护，并占立索引是没有必要。因为索引自身也需维护，并占用一定的资源，一般只在经常用来检索的字段上建用一定的资源，一般只在经常用来检索的字段上建立索引。立索引。一、聚集索引与

4、非聚集索引一、聚集索引与非聚集索引依据索引对数据表中的记录顺序的影响分：依据索引对数据表中的记录顺序的影响分：聚集索引聚集索引(clustered index)对表的物理数据页中的数据按对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它的叶节点中存储的是实际的数据。是混为一体的，它的叶节点中存储的是实际的数据。 非聚集索引非聚集索引(nonclustered index)具有完全独立于数据行具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排的结构，使用非聚集索引不用将物理数据页

5、中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和行定位器。和行定位器。6.2 索引的类型索引的类型如果在数据表中建立聚集索引，则表中的数据页会依照如果在数据表中建立聚集索引，则表中的数据页会依照该索引的顺序来存放。该索引的顺序来存放。一个数据表只能有一种实际的存储顺序，所以一个表只一个数据表只能有一种实际的存储顺序，所以一个表只能建立一个聚集索引。能建立一个聚集索引。未建立聚集索引，则记录的存储顺序将按照输入顺序进未建立聚集索引，则记录的存储顺序将按照输入顺序进行存储。行存储。非聚集索引不会影响数据表中记录的实际存储顺序。

6、一非聚集索引不会影响数据表中记录的实际存储顺序。一个表可建立多个非聚集索引。个表可建立多个非聚集索引。二、唯一索引与复合索引二、唯一索引与复合索引聚集索引与非聚集索引考虑索引键是否重复，可判定是聚集索引与非聚集索引考虑索引键是否重复，可判定是否为唯一索引；考虑索引字段的组合情况，可以判断是否为否为唯一索引；考虑索引字段的组合情况，可以判断是否为复合索引。复合索引。唯一索引唯一索引不允许两行具有相同的索引值。不允许两行具有相同的索引值。如果现有数据中存在重复的键值，则大多数数据库都不如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中允许将新创建的

7、唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。的键值重复时，数据库也拒绝接受此数据。复合索引复合索引是基于多个字段创建的索引。它可同时也为唯是基于多个字段创建的索引。它可同时也为唯一索引。即字段组合不能重复，但单独字段值可重复。一索引。即字段组合不能重复，但单独字段值可重复。复合索引组合字段有限制：在同一表中不多于复合索引组合字段有限制：在同一表中不多于16个的字个的字段组合。段组合。三、主键索引三、主键索引数据库表通常有一列或列组合，其值用来唯一标识表中数据库表通常有一列或列组合，其值用来唯一标识表中的每一行。该列称为表的主键。的每一行。该列称为表的主键。在数据

8、库关系图中为表定义一个主键将自动创建主键索在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。快速访问数据。6.3 创建索引创建索引 SQL Server 2000创建索引的方法创建索引的方法:(1) 由系统自动创建由系统自动创建(2) 利用索引向导创建利用索引向导创建(3) 利用企业管理器直接创建利用企业管理器直接创建(4) 利用利用Transact-SQL语句创建语句创建1.

9、 系统自动创建索引系统自动创建索引 在在SQL Server 2000中建立或修改数据中建立或修改数据表时，创建或添加主键表时，创建或添加主键约束或唯一性约束时，约束或唯一性约束时，系统将基于这个字段自系统将基于这个字段自动创建唯一性索引。动创建唯一性索引。这个索引可以是这个索引可以是聚集的，也可是非聚集聚集的，也可是非聚集的。取决于是否使用的。取决于是否使用clustered。例例1： 创建新表创建新表“产品表产品表”，设置，设置“产品名称产品名称”为聚集的为聚集的唯一索引。利用系统存储过程唯一索引。利用系统存储过程sp_helpindex查看索引信查看索引信息。息。User testdbG

10、oCreate table 产品表产品表 (产品产品ID int, 产品名称产品名称 char(20) unique clustered)GoExec sp_helpindex 产品表产品表自动命名自动命名2. 利用索引向导创建索引利用索引向导创建索引 如图如图6-1 -图图6-6 所示。所示。图图6-1 欢迎使用创建索引向导对话框欢迎使用创建索引向导对话框图图6-2 选择数据库和对象对话框选择数据库和对象对话框图图6-3 显示当前索引信息对话框显示当前索引信息对话框图图6-4 选择创建索引的列对话框选择创建索引的列对话框图图6-5 指定索引选项对话框指定索引选项对话框图图6-6 指定索引名称

11、对话框指定索引名称对话框3. 利用企业管理器直接创建索引利用企业管理器直接创建索引其具体步骤如下：其具体步骤如下：（）在企业管理器中，展开指定的服务器和数（）在企业管理器中，展开指定的服务器和数据库，选择要创建索引的表，用右键单击该表，从弹据库，选择要创建索引的表，用右键单击该表，从弹出的快捷菜单中选择所有任务项的管理索引选项（如出的快捷菜单中选择所有任务项的管理索引选项（如图图6-7所示），就会出现管理索引对话框，如图所示），就会出现管理索引对话框，如图6-8所所示。在该对话框中，可以选择要处理的数据库和表示。在该对话框中，可以选择要处理的数据库和表 。（）单击（）单击“新建新建”按钮，则出

12、现新建索引对话按钮，则出现新建索引对话框，如图框，如图6-9所示。所示。 （）选择完成后单击（）选择完成后单击“确定确定”按钮，即可生成按钮，即可生成新的索引；单击新的索引；单击“取消取消”按钮，则取消新建索引的操按钮，则取消新建索引的操作。作。图图6-7 选择管理索引选项对话框选择管理索引选项对话框图图6-8 管理索引对话框管理索引对话框图图6-9 新建索引对话框新建索引对话框4. 利用利用Transact-SQL语句创建索引语句创建索引（CREATE INDEX命令）命令） CREATE UNIQUE CLUSTERED | NONCLUSTERED INDEX 索引名索引名 ON 表名表

13、名 | 视图名视图名 ( 列列1 ASC | DESC , , 列列n ASC | DESC ) WITH PAD_INDEX | FILLFACTOR = 填充因子填充因子 | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB ON 文件组文件组 各参数说明如下：各参数说明如下： UNIQUE：用于指定为表或视图创建唯一索引，即不允：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。许存在索引值相同的两行。 CLUSTERED：用于指定创建的索引为聚集索引。：用于指定创建的索引为聚集索

14、引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。：用于指定创建的索引为非聚集索引。 索引名索引名 index_name：用于指定所创建的索引的名称。用于指定所创建的索引的名称。 表名表名 table：用于指定创建索引的表的名称。用于指定创建索引的表的名称。 视图名视图名 view：用于指定创建索引的视图的名称。用于指定创建索引的视图的名称。 ASC|DESC：用于指定具体某个索引列的升序或降序排：用于指定具体某个索引列的升序或降序排序方向。序方向。 列列Column：用于指定被索引的列。用于指定被索引的列。 PAD_INDEX：用于指定索引中间级中每个页（节点）：用于指定索引中

15、间级中每个页（节点）上保持开放的空间。上保持开放的空间。FILLFACTOR = fillfactor：用于指定在创建索引时，每：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，个索引页的数据占索引页大小的百分比，fillfactor的值为的值为1到到100。IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时集索引中的列中插入重复数据时SQL Server所作的反应。所作的反应。 DROP_EXISTING：用于指定应删除并重新创建已命名：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。的先前存在的

16、聚集索引或者非聚集索引。 STATISTICS_NORECOMPUTE：用于指定过期的索引：用于指定过期的索引统计不会自动重新计算。统计不会自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序：用于指定创建索引时的中间排序结果将存储在结果将存储在 tempdb 数据库中。数据库中。 ON filegroup：用于指定存放索引的文件组。：用于指定存放索引的文件组。 例子例子2：新建表新建表employeeemployee，表中包含字段，表中包含字段:员工员工IDID、 姓姓 、 名名 、 工资工资 ；然后使用；然后使用CREATE INDEXCREATE INDEX语句基于

17、语句基于 员工员工ID ID 创建，基于创建，基于 姓姓 和和 名名 创建组合唯一索引，并设置重复值；创建组合唯一索引，并设置重复值；最后查看索引信息。最后查看索引信息。源源代代码码见见后后页页CREATE TABLE employee(员工员工ID int not null, 姓姓 char(10), 名名 char(10), 工资工资 money)create unique clustered index ix_eid on employee(员工员工ID)create unique index ix_name on employee(姓姓,名名) with PAD_INDEX, FILL

18、FACTOR =80, IGNORE_DUP_KEYexec sp_helpindex employee6.4 查看、修改和删除索引查看、修改和删除索引 1. 利用企业管理器查看、修改和删除索引利用企业管理器查看、修改和删除索引要查看和修改索引的详细信息，可以在企业管理器要查看和修改索引的详细信息，可以在企业管理器中，展开指定的服务器和数据库项，用右键单击要查看中，展开指定的服务器和数据库项，用右键单击要查看的表，从弹出的快捷菜单中选择所有任务子菜单中的的表，从弹出的快捷菜单中选择所有任务子菜单中的“管理索引管理索引”选项，则会出现管理索引对话框。选择要选项，则会出现管理索引对话框。选择要查看

19、或者修改的索引，单击查看或者修改的索引，单击“编辑编辑”按钮，就会出现修按钮，就会出现修改索引对话框。在该对话框中，可以修改索引的大部分改索引对话框。在该对话框中，可以修改索引的大部分设置，还可以直接修改其设置，还可以直接修改其SQL脚本，只需按下脚本，只需按下“编辑编辑SQL”按钮，即可出现编辑按钮，即可出现编辑SQL脚本对话框，其中可以脚本对话框，其中可以编辑、测试和运行索引的编辑、测试和运行索引的SQL脚本。脚本。要删除索引，可以在企业管理器中，从管理索引对要删除索引，可以在企业管理器中，从管理索引对话框中或者表的属性对话框中，选择要删除的索引，单话框中或者表的属性对话框中，选择要删除的索引，单击击“删除删除”按钮，即可删除索引。按钮，即可删除索引。2. 用系统存储过程查看和更改索引名称用系统存储过程查看和更改索引名称系统存储过程系统存储过程sp_helpindex可以返回表的所有索可以返回表的所有索引信息，其语法形式如下：引信息，其语法形式如下：sp_helpindex objname=name其中其中objname=name参数用于指定当前数据参数用于指定当前数据库中的表的名称。库中的表的名称。系统存储过程系统存储过程sp_rename可以用来更改索引的名可以用来更改索引的名称，其语法形式如下：称，其语法形式如下：sp_renameobjname=ob