《SQL Server 2000应用基础与实训教程（李国彬） 第5章 索引》由会员分享，可在线阅读，更多相关《SQL Server 2000应用基础与实训教程（李国彬） 第5章 索引（81页珍藏版）》请在金锄头文库上搜索。
1、第5章 索引,5.1 索引及其结构与类型 5.2 创建索引的列 5.3 操作索引 习题,5.1 索引及其结构与类型,5.1.1 什么是索引 SQL Server中的索引与书的目录很相似，表中的数据类似于书的内容。我们在看书的时候，总是通过书的页号来查看书的内容而不必翻遍书中的每一页。同样SQL Server索引中记录了表中的关键值，提供了指向表中行的指针，使得SQL Server应用程序能够不扫描全表就能够找到想要的数据。因此，索引被定义成一种为了加速对表中数据行的检索而创建的分散存储结构。,索引是针对一个表而建立的，它是由除存放表的数据页面以外的索引页面构成。每个索引页面中的行都含有逻辑指针
2、，以便加速检索物理数据。因此，对表中的列是否创建索引以及创建什么样的索引，对于查询的响应速度都会有很大的影响。创建了索引的列几乎是立即响应，而不创建索引的列，就需较长的等待时间。因为对未创建索引的列，SQL Server 要一行一行地去查询，这种扫描所耗费的时间将随着表中数据量的增加而成正比地增加。 索引既可在定义表时创建，也可以在定义了表之后随时创建。,5.1.2 索引的结构 SQL Server索引的结构一般是一个B树，B树结构以一个根节点开始，这个根节点是索引的起始点，如图5-1所示。根节点包含索引行(索引数据行)，索引行含有索引键值的范围(AH)和指向下一个索引节点分支节点(AD，EH
3、)的指针。分支节点依次含有索引行，它们带有指向其他分支节点的更细化的值(AB，GH)。每级分支节点被称为一个索引级别。在B树最底层的节点称为叶子节点。叶子节点含有索引键数据(A，H)加上被引用数据位置或数据自身的信息，这主要依赖于索引是非聚集的还是聚集的(这两种索引类型在下一小节介绍)。到达一个叶子节点必须经过的索引级数，决定了找到需要的数据行所必需的I/O数量。,当然，并不一定每个节点都如图5-1所示只包含两个指针。实际上，节点中的每条索引行都含有一个指向其他节点的指针。图5-2是常规索引结构示意图。,图5-1 索引示例,图5-2 索引结构,5.1.3 索引类型 SQL Server索引有4
4、种类型，即聚集索引、非聚集索引、惟一索引及全文本索引。其中，聚集索引和非聚集索引是两种主要的索引类型。对于这两种索引类型之中的任何一种索引，你都可以指定该索引是否应该是一个惟一索引(实际上它是一种特殊的聚集或非聚集索引)。而全文本索引更像一个目录，它具有与聚集索引和非聚集索引不同的特性，是代表它自己的一类索引。,下面将分别介绍这几种索引。 1聚集索引(Clustered Index) 聚集索引是一种指明表数据物理存储顺序的索引。在聚集索引中，行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序。表数据按照指定作为聚集索引的一个或多个键列排序并存储。聚集索引类似于一本字典，字典
5、按照字母顺序存储信息，并提供引导字帮助用户快速定位信息的位置，在同一页上找到数据和它的引导字。相似的，聚集索引含有索引页和实际数据页，数据页组成聚集索引的最底层(叶子节点)，如图5-3所示。,图5-3 聚集索引的结构,在图5-3中，根节点中的每行将分别指向分支节点，分支节点的行又将指向其他的分支节点，最后一组分支节点将最终指向叶子节点。当你到达索引的最底层(即叶子节点)时，也就已经到达实际数据页了。因为数据按照一个指定的顺序物理地存储，所以你只能为每个表创建一个聚集索引。 由于聚集索引的顺序与数据行存放的物理顺序相同，因此，聚集索引最适合于范围搜索。因为相邻的行将被物理地存放在相同的页面上或相
6、邻近的页面上，所以当在用于查找一定范围值的列上创建聚集索引时，聚集索引非常有效。,例如，在图书馆中存放着很多书，这些书可以按照作者顺序存放，也可以按照书名顺序存放，还可以按照书的出版社排序存放。假设现在这些书是杂乱存放的，并且在书名列上建立了聚集索引，那么这些书就必须按照书名的顺序重新排放，使得数据(书)按照索引排序。这就是聚集索引。 创建聚集索引应该注意以下事项： 每张表只能有一个聚集索引。 创建聚集索引时所需的可用空间应是数据库表中数据量的120%。这是因为在创建聚集索引时表中的数据将被复制以便进行排序，排序完成后，再将旧的未加索引的表删除，所以数据库必须有足够的用来复制的空间。, 主键是
7、聚集索引的良好候选者。 缺省设置是非聚集索引。 2. 非聚集索引(Nonclustered Index) 非聚集索引与聚集索引不同，它并不在物理上排列数据，即索引中的逻辑顺序并不等同于表中行的物理顺序。索引仅仅记录指向表中行的位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速地定位数据。 图5-4描述了非聚集索引的结构。在它的叶子节点中不含有实际表数据，取而代之的是，索引本身完全与数据分离，就像一本在书的后面带有索引的书(索引只是指明到哪一页，而数据并不与索引本身在一起)。,图5-4 非聚集索引的结构,非聚集索引的叶子节点含有索引行，用于存储决定行确定位置的索引数据和信息。位置信息可
8、以是两种类型之一，取决于表上是否存在聚集索引。如果有聚集索引，那么对于每行，聚集索引的键值存储在非聚集索引的叶子节点之中，作为该行的定位器。这个值可以被直接用来定位数据，它位于聚集索引的叶子节点之中。如果聚集索引不是一个惟一索引， SQL Server就自动地为所有相同的索引键值分配一个内部值，让它们惟一地以非聚集索引方式使用。这个内部值对于用户来说是不可见的。如果表上没有聚集索引，那么每个叶子节点含有一个行ID作为行的定位器，而不是聚集索引的一个键值。行ID是一个指针，由文件ID、页数目和页中的行数目组成。这个指针能够精确地指示在哪里可以找到行，所以一旦到达行ID，只需要再进行一次I/O便可
9、以读取数据行。,创建非聚集索引应该注意以下事项： SQL Server允许一个表上最多可以有249个非聚集索引。 为一个表建立索引默认都是非聚集索引。 在一列上设置惟一性约束时也自动在该列上创建非聚集索引 3惟一索引(Unique Index) 惟一索引既可以采用聚集索引的结构，又可以采用非聚集索引的结构，被特殊地创建成惟一的。插入到键值列或列组的值必须是惟一的，不允许有重复的键值。对于复合键，这意味着值的组合必须是惟一的。,惟一索引会被经常使用，因为在一个表中，可能会有很多列需要值的惟一性(如：在人员表中，有身份证号、驾驶证号、E-mail地址、电话号等)。这样，你可以在这些列上创建惟一索引
10、。当你希望惟一索引只在一个列上，即当希望一个单独的列自身是惟一的时，就可以在该列上创建一个惟一索引。例如，一个含有身份证号码的列，就可以在该列上创建一个惟一索引，以确保在表中没有相同的身份证号码。,另外，还可以通过创建UNIQUE约束的方法强制特定列的数据的惟一性。当创建UNIQUE约束时，SQL Server自动地创建一个惟一非聚集索引以强制该约束。一旦你在一个表上拥有惟一索引，SQL Server会阻止向表中输入相同的数据，并将给出一条错误消息，指出试图插入相同的行。 所以，创建惟一性索引时，应注意如下事项： 创建惟一性索引时，应保证创建索引的列不包括重复的数据，并且没有两个或两个以上的空
11、值。因为创建索引时将两个空值也视为重复的数据，所以如果有这种数据，则必须先将其删除，否则索引不能成功创建。,4. 全文本索引(Full-text Index) 与前面所述的索引相比，全文本索引相对要复杂一些。全文本索引实际上就像一个目录，它不是简单地在有序B树中进行查找，而是允许你使用关键字组进行查找。全文本索引是微软查找服务的一部分，它被广泛地应用在Web站点搜索引擎和其他基于文本的操作中。与B树索引不同，这种索引存储在数据库的外部，但仍由数据库来维护。通过这种方式，索引可以保持它自己的结构。 需要说明的是，如果使用全文本索引，你必须安装SQL Server标准版或企业版提供的全文本搜索服务
12、。要选择这个选项，就必须在安装时选择自定义安装。,全文本索引拥有普通B树索引所没有的一个有价值的特性，因为这种索引被设计为是一个文本搜索引擎，所以它支持普通文本搜索能力以外的功能。使用全文本索引，你可以执行搜索词或短语的操作、搜索单个词或成组词的操作以及搜索彼此相似的词的操作。 创建和维护全文本索引需要注意如下事项： 只可以在char(字符)或varchar(变长字符)类型的列上创建全文本索引。 每个表只允许有一个全文本索引。 全文本索引不能够自动地更新，这点不同于B树索引。 使用存储过程或全文本索引向导来创建、管理和删除全文本索引。,5.2 创建索引的列,5.2.1 创建索引的理由 对表中的
13、列是否创建索引，以及创建何种索引，对于查询的响应速度会有很大差别。创建了索引的列几乎是立即响应，而不创建索引的列则需要较长时间的等待。因为列上未创建索引，在用该列为条件查询时，SQL Server就要一行一行地去查看整个表，这种扫描所耗费的时间直接同表中的数据量成正比。所以，创建索引的一个最大优点就是能够加速数据检索。 比如，要在表“图书信息”中查询名称为“SQL Server 2000数据库实训教程”信息，需要执行以下T-SQL语句：,SELECT 学生图书借阅管理系统 FROM 图书信息 WHERE 图书名称 =SQL Server 2000数据库实训教程 如果“图书名称”列上没有索引，那
14、么SQL Server就会按照表中行的存储顺序一行一行地查询，查看每一行“图书名称”列是否符合查询条件，这样为了找出满足查询条件的记录必须访问表中的每一行。对于一个具有成千上万行的大型表来说，这种全表搜索可能要花费数小时的时间。,如果在“图书名称”列上创建了索引，那么SQL Server就可以首先搜索这个索引，在索引中找到符合查询条件的值(SQL Server 2000数据库实训教程)，然后按照索引中记录的相应行在表中的位置找到表中的行。由于索引进行了分类，而且索引包括的行和列比全表少得多，因此索引搜索是很快的。 另外，创建索引还能加速连接、ORDER BY和GROUP BY语句的执行。我们知
15、道，连接、ORDER BY和GROUP BY都需要数据检索，在建立索引后，其数据检索速度就会加快，从而也就加速了连接等操作。除此之外，通过创建惟一索引，还可以保证表中的数据不重复，强制实施行的惟一性。,5.2.2 不在每一列上建索引的理由 既然在上节中阐述了索引的重要性，那么为什么不在每个列上都创建索引呢？其实，定义一个表的索引的效果在很大程度上取决于对表访问的形式。如果索引和用户访问数据的形式相匹配，则它是最有效的。由于索引的创建通常有助于改进大型的或频繁更新的数据库的性能，因而每个索引必须保持是最新的。因此对一个表所进行的每个插入、更新和删除操作，如果所涉及的列也包含在索引定义中，都需要对
16、索引进行更新。,当我们在创建聚集索引时，索引所需要的可用空间应该是数据库表中数据量的120 % ,该空间不包括现存表已经占用的空间。在建立索引时，数据被复制以便建立聚集索引，索引建立后，再将旧的未加索引的表数据删除，而且创建索引也需要时间。所以，创建索引要花费时间和占用存储空间。 此外，建立索引加快了检索速度，却减慢了数据修改速度。因为每当执行一次数据修改(包括插入、删除和更新)，就要维护索引，修改的数据越多，涉及维护索引的开销也就越大。所以修改数据时要动态维护其索引，对建立了索引的列执行修改操作要比未建立索引的列执行修改操作所花的时间长。,也就是说，索引虽然可以加快数据查询的速度，但是会减慢数据插入的速度。并且，如果将一些数据行插入到一个已经放满行的数据页面上，就必须将这个数据页面中最后一些数据移到下一个页面中去。这样，你必须改变索引页中的内容，以保持数据顺序的正确性，这就是对索引的维护。它必须花费代价，而且减慢了数据插入的速度。 因此，虽说创建索引有很多优点，但也并不是在每一列上都要创建索引。此外，一
