理学院数据库原理上机实验三.docx

理学院数据库原理上机实验三.docx

《理学院数据库原理上机实验三.docx》由会员分享，可在线阅读，更多相关《理学院数据库原理上机实验三.docx（15页珍藏版）》请在冰点文库上搜索。

理学院数据库原理上机实验三

实验三：

索引

实验内容：

1.通过企业管理器创建索引

在“学生表”学号列上创建名为“PK_学生表”的具有唯一值的聚集索引，文件组为PRIMARY。

2.通过企业管理器删除索引

删除在“学生表”学号列上创建的“PK_学生表”索引

3.使用全文索引

使用全文索引时，需要启动SQLServer和MicrosoftSearch两项服务。

当SQLServer接受全文查询的请求时，会将检索的条件等信息发送给MicrosoftSearch服务来处理；当MicrosoftSearch找到所需要的数据时，再回传给SQLServer进行后续操作。

在数据库pubs上建立全文目录后，在其titles表上建立全文索引，要求选择可用列title_id，创建一个新的全文目录newindex。

全文索引创建后，需要先手动更新。

删除刚才创建的全文索引。

附录：

索引

可以利用索引快速访问数据库表中的特定信息。

索引是对数据库表中一个或多个列（例如，employee表的姓氏（lname）列）的值进行排序的结构。

如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针。

数据库使用索引的方式与使用书的目录很相似：

通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。

在数据库关系图中，可以为选定的表创建、编辑或删除索引/键属性页中的每个索引类型。

当保存附加在此索引上的表或包含此表的数据库关系图时，索引同时被保存。

通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。

索引将占用磁盘空间，并且降低添加、删除和更新行的速度。

不过在多数情况下，索引所带来的数据检索速度的优势大大超过它的不足之处。

然而，如果应用程序非常频繁地更新数据，或磁盘空间有限，那么最好限制索引的数量。

索引列

可基于数据库表中的单列或多列创建索引。

当某些行中的某一列具有相同的值时，多列索引能区分开这些行。

如果经常在同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。

例如，如果经常在同一查询中为姓和名两列设置准则，那么在这两列上创建多列索引将很有意义。

确定索引的有效性：

检查查询中的WHERE和JOIN子句。

在任一子句中包括的每一列都是索引可以选择的对象。

试验新的索引，检查它对运行查询性能的影响。

考虑表中已创建的索引数量。

最好不要在一个表中创建大量的索引。

检查表中已创建的索引定义。

最好避免包含共享列的重叠索引。

检查列中唯一数据值的数量，并与表中的行数进行比较。

比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型是什么。

索引类型

根据数据库的功能，可在数据库设计器中创建三种类型的索引—唯一索引、主键索引和聚集索引。

尽管唯一索引有助于找到信息，但为了获得最佳性能，建议使用主键约束或唯一约束。

唯一索引

唯一索引不允许两行具有相同的索引值。

如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。

当新数据将使表中的键值重复时，数据库也拒绝接受此数据。

例如，如果在employee表中的职员姓氏（lname）列上创建了唯一索引，则所有职员不能同姓。

主键索引

数据库表通常有一列或列组合，其值用来唯一标识表中的每一行。

该列称为表的主键。

在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。

主键索引要求主键中的每个值是唯一的。

当在查询中使用主键索引时，它还允许快速访问数据。

聚集索引

在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。

表只能包含一个聚集索引。

如果不是聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。

聚集索引比非聚集索引有更快的数据访问速度。

全文索引

对Microsoft®SQLServer™2000数据的全文支持涉及两个功能：

对字符数据发出查询的能力和创建及维护基础索引以简化这些查询的能力。

全文索引在许多地方与普通的SQL索引不同。

普通SQL索引

全文索引

存储时受定义它们所在的数据库的控制。

存储在文件系统中，但通过数据库管理。

每个表允许有若干个普通索引。

每个表只允许有一个全文索引。

当对作为其基础的数据进行插入、更新或删除时，它们自动更新。

将数据添加到全文索引称为填充，全文索引可通过调度或特定请求来请求，也可以在添加新数据时自动发生。

不分组。

在同一个数据库内分组为一个或多个全文目录。

使用SQLServer企业管理器、向导或Transact-SQL语句创建和除去。

使用SQLServer企业管理器、向导或存储过程创建、管理和除去。

这些差异使大量管理任务变得不可缺少。

全文管理是在几个层次上实施的：

服务器

可以对服务器范围的某些属性（如resource_usage）加以设置，以便增加或减少全文服务所使用的系统资源数量。

全文引擎作为名为Microsoft搜索的服务在MicrosoftWindowsNT®Server和MicrosoftWindows®2000Server上运行。

对于MicrosoftSQLServer个人版，Microsoft搜索服务不可用。

尽管这意味着Microsoft搜索服务既未安装在MicrosoftWindows95/98上，也未安装在WindowsNT工作站或Windows2000Professional客户端上，但这些客户端在连接到SQLServer标准版安装或企业版实例时可以使用这项服务。

数据库

必须启用数据库才能使用全文服务。

可以在已启用的数据库中创建和除去一个或多个全文目录的元数据。

全文目录

全文目录包含数据库中的全文索引。

每个目录可以用于数据库内的一个或多个表的索引需求。

该目录中的索引是使用这里介绍的管理功能来填充的。

（全文目录必须驻留在与SQLServer实例相关联的本地硬盘驱动器上。

不支持可移动的驱动器、软盘和网络驱动器）。

在每个服务器上最多可创建256个全文目录。

表

首先，必须为全文支持启用表。

然后，为与该表相关联的全文索引创建元数据（如表名及其全文目录）。

表启用后，可以用为全文支持而启用的列中的数据填充它。

如果表的全文定义被更改（例如，添加一个也将为全文检索而索引的新列），则必须重新填充相关的全文目录以使全文索引与新的全文定义同步。

列

可以从非活动的注册表中添加或除去支持全文查询的列。

在所有这些级别上，可使用工具检索元数据和状态信息。

和常规SQL索引一样，当在相关表中修改数据时，可自动更新全文索引。

或者，也可以适当的间隔手工重新填充全文索引。

这种重写可能既耗时又大量占用资源，因此，在数据库活动较少时，这通常是在后台运行的异步进程。

应将具有相同更新特性的表（如更改少的与更改多的，或在一天的特定时段内频繁更改的表）组合在一起，并分配给相同的全文目录。

通过以此方法设置全文目录填充调度，使得全文索引和表保持同步，且在数据库活动较多时不对数据库服务器的资源使用产生负面影响。

为全文目录中的表安排全文索引的位置是非常重要的。

在为全文目录指定表时，应该注意下列基本原则：

始终选择可用于全文唯一键的最小唯一索引。

（4个字节且基于整数的索引是最佳的。

）这将显著减少文件系统中Microsoft搜索服务所需要的资源。

如果主键很大（超过100字节），可以考虑选择表中其它唯一索引（或创建另一个唯一索引）作为全文唯一键。

否则，如果全文唯一键的大小达到允许的上限（450字节），全文填充将无法继续进行。

如果进行索引的表有成千上万行，请将该表指定给其自己的全文目录。

应该考虑对其进行全文索引的表中发生的更改数以及表的行数。

如果要更改的总行数，加上上次全文填充期间表中出现的行数达到成千上万行，请将该表指定给其自己的全文目录。

索引键的最大值

MicrosoftSQLServer2000保留900字节的最大索引键值限制，但更改了CREATEINDEX检查指定索引键是否超出允许的900字节最大键值时所使用的算法。

新的CREATEINDEX算法与用于CREATETABLE的行大小算法类似。

在创建索引时，SQLServer检查下列条件：

所有参与索引定义的固定数据列的总长度必须小于或等于900字节。

当所要创建的索引只由固定数据列构成时，固定数据列的总计大小必须小于或等于900字节。

否则将不能创建索引，且SQLServer将返回错误。

如果索引定义由固定类型列和可变类型列组成，且固定数据列满足前面的条件（小于或等于900字节），则SQLServer仍要检查可变类型列的总大小。

如果可变类型列的最大大小与固定数据列大小的和大于900字节，则SQLServer将创建索引，不过将给用户返回警告消息以提醒用户：

如果随后在可变类型列上的插入或更新操作导致总大小超过900字节，则操作将失败且用户将收到运行时错误。

同样，如果索引定义只由可变类型列组成，且这些列的最大总大小大于900字节，则SQLServer将创建索引，不过将返回警告消息。

代码法创建、删除索引

CREATEINDEX为给定表或视图创建索引。

只有表或视图的所有者才能为表创建索引。

表或视图的所有者可以随时创建索引，无论表中是否有数据。

可以通过指定限定的数据库名称，为另一个数据库中的表或视图创建索引。

语法：

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_name

ON{table|view}（column[ASC|DESC][,...n]）

[WITH[,...n]]

[ONfilegroup]

:

:

=

{PAD_INDEX|

        FILLFACTOR=fillfactor|

        IGNORE_DUP_KEY|

        DROP_EXISTING|

    STATISTICS_NORECOMPUTE|

    SORT_IN_TEMPDB 

}

参数

UNIQUE

为表或视图创建唯一索引（不允许存在索引值相同的两行）。

视图上的聚集索引必须是UNIQUE索引。

在创建索引时，如果数据已存在，Microsoft®SQLServer™会检查是否有重复值，并在每次使用INSERT或UPDATE语句添加数据时进行这种检查。

如果存在重复的键值，将取消CREATEINDEX语句，并返回错误信息，给出第一个重复值。

当创建UNIQUE索引时，有多个NULL值被看作副本。

如果存在唯一索引，那么会产生重复键值的UPDATE或INSERT语句将回滚，SQLServer将显示错误信息。

即使UPDATE或INSERT语句更改了许多行但只产生了一个重复值，也会出现这种情况。

如果在有唯一索引并且指定了IGNORE_DUP_KEY子句情况下输入数据，则只有违反UNIQUE索引的行才会失败。

在处理UPDATE语句时，IGNORE_DUP_KEY不起作用。

SQLServer不允许为已经包含重复值的列创建唯一索引，无论是否设置了IGNORE_DUP_KEY。

如果尝试这样做，SQLServer会显示错误信息；重复值必须先删除，才能为这些列创建唯一索引。

CLUSTERED

创建一个对象，其中行的物理排序与索引排序相同，并且聚集索引的最低一级（叶级）包含实际的数据行。

一个表或视图只允许同时有一个聚集索引。

具有聚集索引的视图称为索引视图。

必须先为视图创建唯一聚集索引，然后才能为该视图定义其它索引。

在创建任何非聚集索引之前创建聚集索引。

创建聚集索引时重建表上现有的非聚集索引。

如果没有指定CLUSTERED，则创建非聚集索引。

说明  因为按照定义，聚集索引的叶级与其数据页相同，所以创建聚集索引时使用ONfilegroup子句实际上会将表从创建该表时所用的文件移到新的文件组中。

在特定的文件组上创建表或索引之前，应确认哪些文件组可用并且有足够的空间供索引使用。

文件组的大小必须至少是整个表所需空间的1.2倍，这一点很重要。

NONCLUSTERED

创建一个指定表的逻辑排序的对象。

对于非聚集索引，行的物理排序独立于索引排序。

非聚集索引的叶级包含索引行。

每个索引行均包含非聚集键值和一个或多个行定位器（指向包含该值的行）。

如果表没有聚集索引，行定位器就是行的磁盘地址。

如果表有聚集索引，行定位器就是该行的聚集索引键。

每个表最多可以有249个非聚集索引（无论这些非聚集索引的创建方式如何：

是使用PRIMARYKEY和UNIQUE约束隐式创建，还是使用CREATEINDEX显式创建）。

每个索引均可以提供对数据的不同排序次序的访问。

对于索引视图，只能为已经定义了聚集索引的视图创建非聚集索引。

因此，索引视图中非聚集索引的行定位器一定是行的聚集键。

index_name

是索引名。

索引名在表或视图中必须唯一，但在数据库中不必唯一。

索引名必须遵循标识符规则。

table

包含要创建索引的列的表。

可以选择指定数据库和表所有者。

view

要建立索引的视图的名称。

必须使用SCHEMABINDING定义视图才能在视图上创建索引。

视图定义也必须具有确定性。

如果选择列表中的所有表达式、WHERE和GROUPBY子句都具有确定性，则视图也具有确定性。

而且，所有键列必须是精确的。

只有视图的非键列可能包含浮点表达式（使用float数据类型的表达式），而且float表达式不能在视图定义的其它任何位置使用。

若要在确定性视图中查找列，请使用COLUMNPROPERTY函数（IsDeterministic属性）。

该函数的IsPrecise属性可用来确定键列是否精确。

必须先为视图创建唯一的聚集索引，才能为该视图创建非聚集索引。

在SQLServer企业版或开发版中，查询优化器可使用索引视图加快查询的执行速度。

要使优化程序考虑将该视图作为替换，并不需要在查询中引用该视图。

在创建索引视图或对参与索引视图的表中的行进行操作时，有7个SET选项必须指派特定的值。

SET选项ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER、ANSI_NULLS、ANSI_PADDING和ANSI_WARNING必须为ON。

SET选项NUMERIC_ROUNDABORT必须为OFF。

如果与上述设置有所不同，对索引视图所引用的任何表执行的数据修改语句（INSERT、UPDATE、DELETE）都将失败，SQLServer会显示一条错误信息，列出所有违反设置要求的SET选项。

此外，对于涉及索引视图的SELECT语句，如果任何SET选项的值不是所需的值，则SQLServer在处理该SELECT语句时不考虑索引视图替换。

在受上述SET选项影响的情况中，这将确保查询结果的正确性。

如果应用程序使用DB-Library连接，则必须为服务器上的所有7个SET选项指派所需的值。

（默认情况下，OLEDB和ODBC连接已经正确设置了除ARITHABORT外所有需要的SET选项。

）

如果并非所有上述SET选项均有所需的值，则某些操作（例如BCP、复制或分布式查询）可能无法对参与索引视图的表执行更新。

在大多数情况下，将ARITHABORT设置为ON（通过服务器配置选项中的useroptions）可以避免这一问题。

强烈建议在服务器的任一数据库中创建计算列上的第一个索引视图或索引后，尽早在服务器范围内将ARITHABORT用户选项设置为ON。

column

应用索引的列。

指定两个或多个列名，可为指定列的组合值创建组合索引。

在table后的圆括号中列出组合索引中要包括的列（按排序优先级排列）。

说明  由ntext、text或image数据类型组成的列不能指定为索引列。

另外，视图不能包括任何text、ntext或image列，即使在CREATEINDEX语句中没有引用这些列。

当两列或多列作为一个单位搜索最好，或者许多查询只引用索引中指定的列时，应使用组合索引。

最多可以有16个列组合到一个组合索引中。

组合索引中的所有列必须在同一个表中。

组合索引值允许的最大大小为900字节。

也就是说，组成组合索引的固定大小列的总长度不得超过900字节。

有关组合索引中可变类型列的更多信息，请参见注释部分。

[ASC|DESC]

确定具体某个索引列的升序或降序排序方向。

默认设置为ASC。

n

表示可以为特定索引指定多个columns的占位符。

PAD_INDEX

指定索引中间级中每个页（节点）上保持开放的空间。

PAD_INDEX选项只有在指定了FILLFACTOR时才有用，因为PAD_INDEX使用由FILLFACTOR所指定的百分比。

默认情况下，给定中间级页上的键集，SQLServer将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行。

如果为FILLFACTOR指定的百分比不够大，无法容纳一行，SQLServer将在内部使用允许的最小值替代该百分比。

说明  中间级索引页上的行数永远都不会小于两行，无论FILLFACTOR的值有多小。

FILLFACTOR=fillfactor

指定在SQLServer创建索引的过程中，各索引页叶级的填满程度。

如果某个索引页填满，SQLServer就必须花时间拆分该索引页，以便为新行腾出空间，这需要很大的开销。

对于更新频繁的表，选择合适的FILLFACTOR值将比选择不合适的FILLFACTOR值获得更好的更新性能。

FILLFACTOR的原始值将在sysindexes中与索引一起存储。

如果指定了FILLFACTOR，SQLServer会向上舍入每页要放置的行数。

例如，发出CREATECLUSTEREDINDEX...FILLFACTOR=33将创建一个FILLFACTOR为33%的聚集索引。

假设SQLServer计算出每页空间的33%为5.2行。

SQLServer将其向上舍入，这样，每页就放置6行。

说明  显式的FILLFACTOR设置只是在索引首次创建时应用。

SQLServer并不会动态保持页上可用空间的指定百分比。

用户指定的FILLFACTOR值可以从1到100。

如果没有指定值，默认值为0。

如果FILLFACTOR设置为0，则只填满叶级页。

可以通过执行sp_configure更改默认的FILLFACTOR设置。

只有不会出现INSERT或UPDATE语句时（例如对只读表），才可以使用FILLFACTOR100。

如果FILLFACTOR为100，SQLServer将创建叶级页100%填满的索引。

如果在创建FILLFACTOR为100%的索引之后执行INSERT或UPDATE，会对每次INSERT操作以及有可能每次UPDATE操作进行页拆分。

如果FILLFACTOR值较小（0除外），就会使SQLServer创建叶级页不完全填充的新索引。

例如，如果已知某个表包含的数据只是该表最终要包含的数据的一小部分，那么为该表创建索引时，FILLFACTOR为10会是合理的选择。

FILLFACTOR值较小还会使索引占用较多的存储空间。

下表说明如何在已指定FILLFACTOR的情况下填充索引页。

FILLFACTOR

中间级页

叶级页

0

一个可用项

100%填满

1%-99

一个可用项

<=FILLFACTOR%填满

100%

一个可用项

100%填满

一个可用项是指页上可以容纳另一个索引项的空间。

重要  用某个FILLFACTOR值创建聚集索引会影响数据占用存储空间的数量，因为SQLServer在创建聚集索引时会重新分布数据。

IGNORE_DUP_KEY

控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。

如果为索引指定了IGNORE_DUP_KEY，并且执行了创建重复键的INSERT语句，SQLServer将发出警告消息并忽略重复的行。

如果没有为索引指定IGNORE_DUP_KEY，SQLServer会发出一条警告消息，并回滚整个INSERT语句。

下表显示何时可使用IGNORE_DUP_KEY。

索引类型

选项

聚集

不允许

唯一聚集

允许使用IGNORE_DUP_KEY

非聚集

不允许

唯一非聚集

允许使用IGNORE_DUP_KEY

DROP_EXISTING

指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。

指定的索引名必须与现有的索引名相同。

因为非聚集索引包含聚集键，所以在除去聚集索引时，必须重建非聚集索引。

如果重建聚集索引，则必须重建非聚集索引，以便使用新的键集。

为已经具有非聚集索引的表重建聚集索引时（使用相同或不同的键集），DROP_EXISTING子句可以提高性能。

DROP_EXISTING子句代替了先对旧的聚集索引执行DROPINDEX语句，然后再对新的聚集索引执行CREATEINDEX语句的过程。

非聚集索引只需重建一次，而且还只是在键不同的情况下才需要。

如果键没有改变（提供的索引名和列与原索引相同），则DROP_EXISTING子句不会重新对数据进行排序。

在必须压缩索引时，这样做会很有用。

无法使用DROP_EXISTING子句将聚集索引转换成非聚集索引；不过，可以将唯一聚集索引更改为非唯一索引，反之亦然。

说明  当执行带DROP_EXISTING子句的CREATEINDEX语句时，SQLServer假定索引是一致的（即索引没有损坏）。

指定索引中的行应按CREATEINDEX语句中引用的指定键排序。

STATISTICS_NORECOMPUTE

指定过期的索引统计不会自动重新计算。

若要恢复自动更新统计，可执行没有NORECOMPUTE子句的UPDATESTATISTICS。

重要  如果禁用分布统计的自动重新计算，可能会妨碍SQLServer查询优化器为涉及该表的查询选取最佳执行计划。

SORT_IN_TEMPDB

指定用于生成索引的中间排序结果将存储在tempdb数据库中。

如果tempdb与用户数据库不在同一磁盘集，则此选项可能减少创建索引所需的时间，但会增加创建索引时使用的磁盘空间。

ONfilegroup

在给定的filegroup上创建指定的索引。

该文件组必须已经通过执行CREATEDATABASE或ALTERDATABASE创建。

注释

为表或索引分配空间时，每次递增一个扩展盘区（8个8KB的页）。

每填满一个扩展盘区，就会再分配一个。

如果表非常小