索引与数据完整性

《索引与数据完整性》由会员分享，可在线阅读，更多相关《索引与数据完整性（15页珍藏版）》请在金锄头文库上搜索。

1、2018年9月18日,SQL Server 2000实用教程,在数据库的管理中，为了迅速地从庞大的数据库中找到所需要的数据，提供了类似书籍目录作用的索引技术。通过在数据库中对表建立索引，可以大大加快数据的检索速度。在数据查询时，如果表的数据量很大且没有建立索引，SQL Server将从第一条记录开始，逐行扫描整个表，直到找到符合条件的数据行。这样，在查询上的开销将很大，浪费宝贵的CPU时间，系统的效率会很低。如果建立索引，SQL Server将根据索引的有序排列，通过高效的有序查找算法找到索引项，然后通过索引项直接定位数据，从而加快查找速度。此外，本章还将介绍在数据库规划过程中很重要的一步实现

2、数据完整性的概念。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,5.1.1 索引的概念 1. 索引的概念索引是一个单独的，物理的数据库结构。它由某个表中的一列或者若干列的值，以及这些值的记录在数据表中存储位置的物理地址所组成。当SQL Server进行数据查询时，查询优化器会自动计算现有的几种查询方案，通过方案的开销大小和速度快慢决定方案是否被执行。如果使用索引查找的速度不如直接扫描表格速度快时，SQL Server会使用直接扫描表格的方法。建立不能被SQL Server使用的索引会给系统增加负担，索引要占用存储空间，而且为了自动维护索引，在插入、删除或者更新数

3、据的时候，SQL Server还要花费额外的操作来维护索引的有效性。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,2. 索引的特点 使用索引可以极大的改善数据库的性能，其表现在： 通过创建唯一性索引，可以保证每一行数据的唯一性 可以大大的加快数据的检索速度，这正是使用索引的最主要的原因 在使用ORDER BY和GROUP BY子句进行数据检索时， 可以减少查询中分组和排序的时间 加速表与表之间的连接，特别是在实现数据库的参照完整性上很有意义 可以在检索数据的过程中提高系统性能,2018年9月18日,SQL Server 2000实用教程,5.1 索引,但是，建立

4、索引也会消耗硬盘空间并招致开销和维护成本。当在建立索引的列上修改数据时，SQL Server将会更新相关的索引，从而花费额外的时间和资源。因此，不要创建不经常使用的索引，不要在含有大量重复数据的列上创建索引，也不要在定义为 text、ntext 或者 image 数据类型的列上创建索引。建立索引应该遵循以下原则： 在主键上创建索引 在经常需要检索的字段上创建索引 在外键上创建索引 在经常要排序的列上创建索引,2018年9月18日,SQL Server 2000实用教程,5.1 索引,3. 索引的分类从索引表的物理顺序、属性列的重复值以及索引列中所包含的列数等不同的角度，可以把索引分为以下几类：

5、 聚集索引和非聚集索引 唯一索引和非唯一索引单列索引和复合索引 （1）聚集索引和非聚集索引P111根据索引的顺序与数据表的物理顺序是否相同，可以把索引分为聚集索引和非聚集索引。（2）唯一索引和非唯一索引 （3）单列索引和复合索引单列索引是对表中单个字段建立的索引，复合索引是对表中的两个或两个以上的字段的组合建立的索引。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,5.1.2 索引的建立 在SQL Server 2000中，只有表或视图的拥有者才可以为表创建索引，即使表中没有数据也可以创建索引。创建索引有3种的方法： 使用企业管理器创建索引 使用向导创建索引 使用

6、CREATE INDEX语句创建索引此外，当对表中的字段设置主键约束时，如果表中没有聚集索引，且主键约束未使用关键字NONCLUSTERED系统会自动创建一个唯一性索引,2018年9月18日,SQL Server 2000实用教程,5.1 索引,1使用企业管理器创建索引 P115 使用企业管理器创建索引的操作步骤如下：（1）在企业管理器中展开指定的服务器和数据库，选择要创建索引的表，右击该表，从弹出的快捷菜单中依次选择“所有任务管理索引”。 （2）进入“管理索引”对话框中。此对话框可以更改选择要建立索引的数据库和表，还可以对已经存在的索引进行编辑和删除操作。在现有索引区内显示的是现存索引的名称

7、、是否聚集索引以及对应索引的列 （3）单击“新建”按钮，在弹出的“新建索引”对话框中输入索引名称，如I_进货时间。在对话框中部的窗口内是可供选择的建立索引的字段，对所选的字段可以指定按升序或降序组织列值。此外，对话框内还可以设定索引的属性，如是否聚集、是否唯一、指定填充因子和更改列顺序等,2018年9月18日,SQL Server 2000实用教程,5.1 索引,（4）设置完成后单击“确定”按钮，返回“管理索引”对话框，在现有索引区内即可看到新建立的索引，单击“关闭”按钮，返回企业管理器。 2使用向导创建索引 使用向导创建索引的操作步骤如下： （1）在企业管理器菜单栏中，选择“工具向导”，打开

8、“选择向导”对话框，展开“数据库”选项,选择“创建索引向导”，单击“确定”按钮。 2）进入“欢迎使用创建索引向导”对话框，此对话框显示当前向导所能完成的功能，如图5.6所示。单击“下一步”按钮。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,（3）进入“选择数据库和表”对话框，选择新建索引所属的数据库和表。为销售表中的销售时间创建一个索引，首先选择sales数据库下的sell表，再单击“下一步”按钮。 （4）进入“当前的索引信息”对话框，表中已经存在的索引信息会显示在对话框中，单击“下一步”按钮。 （5）进入“选择列”对话框，可以选择一个或者多个列建立索引并指定排

9、序方式。在这里为“售出时间”列的降序建立索引，单击“下一步”按钮。 （6）进入“指定索引选项”对话框，指定该索引的属性和填充因子。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,（7）进入“正在完成创建索引向导”对话框，显示索引名称和索引包含的列，索引名称可以修改，单击“完成”按钮。 （8）弹出“创建索引成功”的提示框，单击“确定”即可3使用CREATE INDEX语句创建索引 。P112-114 createuniqueclustered|nonclusteredindex 索引名 on 表名|视图名（列名asc|desc,n） with ,fillfactor

10、= ,drop_existing on文件组名 unique指定创建唯一索引。 Clustered指定创建聚集索引 Nonclustered指定创建非聚集索引 asc|desc指定特定索引的排序方式 fillfactor：称为填充因子，用于指定每个索引页的数据占索引大小的百分比。 drop_existing指定应删除并重建已命名的先前存在的聚集索引或非聚集索引。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,5.1.3 索引的删除当不在需要某个索引时，可以使用企业管理器或者DROP INDEX语句删除索引。删除索引时，SQL Server将回收索引所占用的磁盘空间

11、。如果删除聚集索引，表中所有的非聚集索引将会被自动重建。删除表时，表中的索引也将随之删除。 1.使用企业管理器删除索引在企业管理器中展开指定的服务器和数据库，选择将删除的索引所在的表，右击该表，从弹出的快捷菜单中依次选择“所有任务管理索引”。进入“管理索引”对话框。此对话框可以创建、删除和编辑索引。在现有索引区内显示的是现存索引的名称及其类型和索引包含的列。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,选择要删除的索引名称，单击“删除”按钮，即可删除索引。此外，如果要查看或者修改现存索引，可以单击“编辑”按钮，进入“编辑现有索引”对话框，在此对话框内除了可以修改

12、索引的大部分设置以外，还可以选择“编辑SQL(E)”按钮对索引脚本进行分析、修改。 2.使用DROP INDEX语句删除索引在查询分析器中使用DROP INDEX语句可以删除表的索引。DROP INDEX语句不能用于删除系统表中的索引，也不能用于删除由PRIMARY或UNIQUE约束创建的索引，必须先删除这些约束才能删除索引。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,5.1.4 索引的分析与维护在执行查询时，SQL Server动态选择使用哪个索引。为此，SQL Server根据每个索引上分布在该关键字上的统计量来决定使用哪个索引，我们可以通过执行DBCC

13、SHOWCONTIG来查看统计量的状态。值得注意的是，经过日常的数据库活动（如插入、删除和更新表格），SQL Server用到的这些统计量可能已经“过期”了，需要更新。这时可以执行该表的UPDATE STATISTICS命令，这样SQL Server就刷新了关于该索引的信息了。 创建索引后，必须对索引进行维护，确保索引的统计信息是的有效的，才能够提高查找速度。随着更新操作不断的执行，数据会变得支离破碎，这些数据碎片会导致额外的页读取，防碍数据的并行扫描。应该定期整理索引清除数据碎片，提高数据读取的性能。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,1.数据碎片

14、随着时间的变化，当往表中添加或从表中删除数据行以及索引列的值发生改变，SQL Server 调整索引页以维护索引数据的存储页拆分时会产生碎片，可以使用DBCC SHOWCONTIG命令，显示指定的表或视图的数据和索引的碎片信息。 2.重建和整理索引在SQL Server中有两种整理碎片的方法，一是删除和重新创建聚集索引并用 FILLFACTOR 选项指定填充因子的值;二是重建索引并指定填充因子的值。 （1）DBCC DBREINDEX语句DBCC DBREINDEX用于重建指定数据库中表的一个或多个索引。,2018年9月18日,SQL Server 2000实用教程,5.1 索引,（2）DBCC INDEXDEFRAG语句使用 DBCC DBREINDEX整理指定的表或视图的聚集索引和非聚集索引碎片，不必单独重建每个索引。 （3）DROP_EXISTING子句CREATE INDEX 的 WITH DROP_EXISTING 子句可以对重建索引工作进行优化，用一个步骤重新创建索引，以避免重建两次非聚集索引的开销。,