SQL应用与开发：（九）提高效率的索引

在数据库中，索引是一个特殊的对象，是一种可以加快数据检索的数据库结构，它可以从大量的数据中迅速找到需要的内容，使得数据查询时不必检索整个数据库。索引是一种基于表中数据的对象，与视图不同，索引需要占用物理存储。使用数据库的索引，使我们能够较快的查询数据。

1.简介

索引是表示数据的一种方式，它提供的数据顺序不用于数据在磁盘上的物理存储顺序。索引基于表的一列或多列组合建立，在表内重新排列记录的物理位置。当使用索引时，数据是以分类排序的方式提供给用户的，排列顺序可以用创建索引语句控制。通常，通过在正确的列上建立索引，可以使数据库性能显著提高，特别是在表之间的联接列上建立索引更是如此。

索引和图书目录的作用类似。如果把数据表中的数据看作是书的内容，则索引就是书的目录。书的目录指向了书的内容(通过页码)。同样，索引是表的关键值，它提供了指向表中行的指针。目录中的页码是达到书内容的直接路径，而索引也是到达表数据的直接路径，从而更高效地访问数据。

在数据库关系图中，可以为选择的表创建、编辑或删除索引/键属性页中的每个索引类型。当保存附加在此索引上的表或包含此表的数据库关系图中，索引同时也被保存。

1.1优点

索引是一个独立的、物理的数据库结构，数据库用户可以利用索引快速访问数据库表中的特定信息。

通常情况下，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引将占用磁盘空间，并且降低添加、删除和更新行的速度。不过在大多数情况下，索引所带来的数据检索数据的优势大大超过它的不足之处。然而，如果应用程序频繁的更新数据，或者磁盘空间有限，那么最好不限制索引的数据。

总体来说，索引是为了加速检索速度而创建的一种存储结构。索引针对一个表建立，它由存放有数据页面以外的索引页面组成。每个索引页面中的行都包含逻辑指针，通过该指针可以直接检索到数据，这机会加速物理数据的检索。

对表中的列是否创建索引以及创建何种索引，对数据的检索速度有很大影响。创建了索引的列几乎可以立即响应查询，而未创建索引的列查询时就需要等待很长时间。因为对于未创建索引的列，需要逐行进行搜索，这种索引耗费的时间直接同表中的数据量成正比。

利用索引进行查询具有以下优点：

提高查询速度：如果一个表中没有索引，在进行查询时，数据库就会强制按照表的顺序逐行进行搜索，若要找到满足条件的行，则需要访问表的每一行，显而易见要花费很长的时间。但是如果要查询的表中的位置信息确定表中的行，这样便缩短了查询的时间；

提高连接、ORDER BY和GROUP BY的执行速度：连接、ORDER BY和GROUP BY都需要对数据进行检索，如果在表中建立索引，则连接、ORDER BY和GROUP BY执行的速度将大大提高；

查询优化分析器依靠索引起作用：在执行查询的过程中，数据库会自动地对查询进行优化。由于数据库的优化依靠索引进行，所以在建立索引后，数据库会依据所建立的索引，为使索引的速度最快而采取哪些索引；

强制实施行的唯一性：创建唯一索引可以保证表中的数据不重复。

总之，索引可以改善数据查询性能，但是这是需要付出代价。带索引的数据表在数据库中会占据更多的存储空间。另外，为了维护索引，对数据进行插入、更新、删除操作的命令所花费的时间会更长。在设计和创建索引时，应确保对性能的提高程度大一在存储空间和处理资源方面的代价。

1.2注意事项

用户可根据环境的需要，在基表上建立一个或者多个索引，以提供多种存取方法，加快数据检索速度。通常，建立与删除索引由数据库管理员或表的拥有者负责完成。系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能选择索引。

索引是一把双刃剑。虽然它会加速SELECT语句，但是它也可能减缓数据所做的更改，这是因为必须在运行时刻动态更新SQL中的索引。无论什么时候表中的数据发生改变，表中的所有索引都必须发生改变，如果这样会导致重组索引，则当索引重组发生时，可能会发生延迟。

创建索引应考虑的主要因素有：

i.一个表如果建立大量索引会影响INSERT、UPDATE和DELECT语句的性能，因为在表中的数据更改时，所有索引都需要进行适当的调整。另一方面，对于不需要修改数据的查询(SELECT语句)，大量索引有助于提高性能。因为查询分析器有更多的索引可供选择，以便确定以最快的速度访问数据的最佳方法；

ii.通常我们是通过主键来对表进行查询的，因此首先应该考虑在主键列上建立索引。另外，连接中频繁使用的列(包括 外键)应作为建立索引的考虑选项；

iii.对小型表进行索引可能不会产生优化效果，因为查询分析器在遍历索引以搜索数据时，花费的时间可能比简单的表扫描更长的时间。

综上所述，由于建立索引使用需要一定的开销，而且当使用INSERT或者UPDATE对数据进行插入和更新操作时，维护索引也需要花费时间和存储空间。因此，没有必要对表中所有列建立索引。

2.类型

依据索引的顺序和数据库的物理存储顺序是否相同，可以将索引分为两类：聚集索引(Clustered Index)和非聚集索引(Non-clustered Index)。聚集索引和非聚集索引都使用B-Tree结构创建，而且都包括索引页和数据页，其中索引页用来存放索引和指向下一层的的指针，数据页用来存放记录。

根据索引键的组成，可以把索引分为唯一索引和组合索引。唯一索引(UNIQUE Index)可以确保索引列不包含重复的值，组合索引是使用表中多个列对数据进行索引的索引。另外，因SQL环境的不同还有其他的索引类型。例如，Oracle中位图索引和函数索引等。

索引一旦创建，将由数据库自动管理和维护。例如，在向表中插入、更新或者删除一条记录时，数据库会自动在索引中作出相应的修改。在编写SQL查询语句时，具有索引的表不具有索引的表没有任何区别。索引只是提供一种快速访问指定记录的方法。数据表具有索引，只会影响到表的查询速度，而不会影响到其他任何方面。

2.1B-Tree索引

B-Tree索引的顶端节点称为根节点(Root Node)，底层节点称为叶节点(Leaf Node)，在根节点和叶节点之间的节点称为中间节点(Intermediate Node)。每级索引中的页链接在双向链接列表中。B-Tree数据结构从根节点开始，以左右平衡的方式排列数据，中间可以根据需要分成许多层，B-Tree索引可以很方便地为更新提供可用空间。

下图为B-Tree的总体结构图：

由于各叶节点按照所含的索引码值有一个线性顺序，所以就可以利用各个节点的指针Pn将叶节点按照索引码值顺序链接在一起。这种顺序能够高效地对文件进行顺序处理，而B-Tree索引的其他结构能够高效地对文件进行随机处理。

2.2聚集索引

聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得表的物理顺序与索引顺序一致。一个表只能包含一个聚集索引。如果不是聚集索引，表中各行的物理顺序和键值的逻辑顺序就不会匹配。

聚集索引的B-Tree是由下而上构建的，一个数据页(索引页的叶节点)包含一笔记录，再由多个数据页生成一个中间节点的索引页。然后由多个中间节点的索引页合成更上层的索引页，组合后生成最顶层的根节点的索引页。

下图为聚集索引的数据结构：

在创建聚集索引时，会对表进行复制，并且对表中的数据进行排序，然后删除原始表。因此，数据库上必须有足够的空闲空间，以容纳数据副本。

创建聚集索引时，应该注意一下事项：

· 大多数的表 都应该有聚集索引或使用分区来降低对表尾页的争用，在一个高度事务性的环境中，对最后一页的封锁将会严重影响系统的吞吐量。

· 在聚集索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(BETWEEN、<、<=、>、>=)或使用GROUP BY和ORDER BY的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行就可以保证物理上毗连在一起，而不必进一步搜索。这可以避免大范围扫描，提高了查询速度。

· 在一个频繁发生插入操作的表上建立聚集索引时，不要将索引在具有单调上升值得列(如IDENTITY)上，否则会经常引起封锁冲突。

·在聚聚索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。

· 选择聚集索引应基于WHERE子句和连接操作的类型。

聚集索引的候选键列是主键列，该列在WHERE子句中使用并且可以随机插入；按范围存取的列；在GROUP BY或ORDER BY中使用的列；不经常修改的列；在连接操作中使用的列。

2.3非聚集索引

非聚集索引与图书的索引类似。数据存储的一个位置，索引存储在另一个位置，索引带有指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储(这里可以由聚集索引定)。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。

非聚集索引与聚集索引一样具有B-Tree结构，但是它与聚集索引有两个重大差别：

· 数据行不按非聚集索引键的顺序排序和存储。

· 非聚集索引的页层不包含数据页，相反，叶节点包含索引行。每个索引行包含非聚集键值以及一个或多个行定位器，这些行定位器指向有该键值的数据行(如果索引不唯一，则可能是多行)。

非聚集索引具有完全独立于数据行的结构。非聚集索引的最低行包含非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。数据行不按基于非聚集键的次序存储。

下图为非聚集索引的数据结构：

在使用非聚集索引时应注意以下问题：

· 索引需要使用多少空间；

· 建立索引的列是否稳定；

· 索引键是如何选择的，扫描效果是否更佳；

· 是否有许多重复值。

我们常在以下情况下使用非聚集索引：常用于集合函数的列和经常使用JOIN，ORDER BY和GROUP BY子句的列。

2.4唯一索引和组合索引

主键用来标识唯一行，它定义两个表的关系，用于生成索引的一个或一组列。根据索引键的组成，可以把索引分为三种类型：唯一索引、组合索引和覆盖索引。

2.4.1唯一索引

唯一索引不允许两行具有相同的索引值。在多列唯一索引的情况下，该索引可以确保索引列中每个值组合都是唯一的。如果现有数据中存在重复的键值，则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时，数据库也拒绝接受此数据。在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

在一个数据库表中，如果单列中有不止一行包含NULL值，则无法再该列上创建唯一索引。在列的组合中，如果其中有多个列包含NULL值，则这些NULL值被视为重复值。因此，在这样的多个列上也不能创建唯一索引。

2.4.2组合索引和覆盖索引

顾名思义，组合索引是指在一个表中使用不止一个列对数据进行索引的索引，它是通过连接两个或多个列值而创建的。

组合索引中可以使用2~16个列的索引，以两列或多列作为一个单位搜索时最好，或者许多查询只引用索引中指定的列时，应使用组合索引。组合索引中的所有列必须在同一个表中，并且组合索引值所允许的最大大小为900字节，即组成组合索引的固定大小列的总长度不得超过900字节。

当索引中包含了需要的所有信息时，这个索引称为覆盖索引。它的键值包含了满足查询的所有数据。

2.5其他索引类型

在各个版本的数据库系统中可以创建多种类型的索引，以适应各种数据表的特点，满足不同情况下的需求。下面简单举一个例子：

位图索引

在位图索引中并不是以索引列的值得的方式来存储索引信息的。位图索引为每个唯一的索引列建立一个位图，在这个位图中使用一个单元(Bit，取值为0或1)来对应一条记录的列值。如果该位元为1，说明与该位元对应的列值是一条包含该位图的索引列的记录。位元到列值得对应关系通过位图索引中的映射函数来完成。

这样，位图索引就能够以一种完全不同的内部机制来完成与B-Tree索引相同的功能。如果索引列的取值范围只包括少数几个固定的值，位图索引将会十分有效，并且能够节省存储空间。

3.创建

创建索引要知道哪些索引带来的好处大于开销。创建索引的最基本的命令是：

<span style="font-size:18px;color:#000099;"><strong>CREATE INDEX Index_name
ON Table_name(Column_name1,Column_name2,...)</strong></span>

如同前面多次遇到的情况一样，CREATE INDEX语句的语法在不同数据库系统中差别很大。但所有的数据库系统都支持上述基本命令。

3.1复合索引

复合索引是指一个索引中包含了一个以上的列，也称为组合索引。在SQL Server中最多可以由16个列组合到一个索引中。

举例来说，在数据库“珠宝销售系统”中，查询所在城市为北京市的珠宝商信息。由于珠宝商所在城市的列值多次出现了重复值，则该列的选择性就比较低，如果在该列创建一个单列索引，那么气查询性能不是很高、注意珠宝商编号列的每一个值都是唯一值。为了提高索引的选择性，可以创建一个组合珠宝商所在城市和珠宝商编号的复合索引。其代码如下：

<span style="font-size:18px;color:#000099;"><strong>CREATE INDEX  所在城市_Index
ON 珠宝商信息(珠宝商所在城市，珠宝商编号)</strong></span>

3.2唯一索引和主键索引

唯一索引可以确保列中不包含重复的值。当向具有唯一索引的表添加数据时，数据库会检查新添加的行在该列是否具有重复值。如果新添加的行与其他已经存在行在唯一索引列出现重复值，则数据库会显示错误，并且撤销输入操作。

只有当唯一性是数据本身的特性时，指定唯一索引才有意义。例如，当建立一个学籍管理系统数据库时，我们不想对学生姓名建立唯一索引，因为可能存在姓名相同的学生。正确的做法是，为每个学生建立一个学号，并在学号字段上建立唯一索引。

创建唯一索引的方法非常简单，只需在CREATE INDEX中使用UNIQUE关键字即可，这样就组成了唯一符合索引。

举例来说，在“销售管理系统”的操作人员信息表中规定：同名的操作人员必须设置不用密码，以保证系统的安全性。那么应该在密码和操作人员列上建立一个唯一符合索引：

<span style="font-size:18px;color:#000099;"><strong>CREATE UNIQUE INDEX 操作人员安全_Index
ON 操作人员信息(操作人员姓名，密码) </strong></span>

现在向数据表操作人员信息添加一个新的操作人员信息，新添加的操作人员信息是表中已重复的数据。

<span style="font-size:18px;color:#000099;"><strong></strong></span><pre name="code" class="sql">INSERT INTO 操作人员信息(操作人员编号，操作人员姓名，密码)
VALUES(1009，'周慧芳'，'9698xie')

执行上述语句应该会收到一个错误信息，提示用户不允许使用INSERT命令。这是由于该INSERT指令添加的数据违反了“操作人员安全_Index”索引。修改INSERT指令添加的密码值。

<strong><span style="color:#000099;">INSERT INTO 操作人员信息(操作人员编号，操作人员姓名，密码)
VALUES(1009，'周慧芳'，'9698')</span></strong>

主键索引是唯一索引的特殊类型。主键索引是在为表定义主键时自动创建的。在表中创建主键只需要列定义前使用关键字PRIMARY KEY即可。主键索引要求主键中的每一个值是唯一的。当在查询中使用主键索引时，它还不允许快速访问数据。

当在表上定义主键或者唯一性约束时，如果表中已经有了使用CREATE INDEX语句创建的标准索引时，那么主键约束或者唯一性约束创建的索引会覆盖以前创建的标准索引。也就是说，主键索引或唯一性约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。

3.3聚集和非聚集索引

当表被使用聚集索引时，数据存储在同一数据块中，这样就只需要读取极少的数据库块，从而导致更快的执行性能。当使用聚集索引时，表内数据的物理排列就修改了。使用聚集索引通常比非聚集索引更快。

用于建立聚集索引的列通常是主键。在SQL Server中，默认情况下建立的是索引为非聚集索引。除非使用关键字CLUSTERED指定建立聚集索引，当然也可以使用关键字NONCLUSTERED指定建立非聚集索引。

举例来说，在数据库“销售管理系统”中，在供应商信息表的供应商名称列上创建唯一聚集索引。

<span style="font-size:18px;color:#000099;"><strong>USE 销售管理系统
GO
CREATE UNIQUE CLUSTERED INDEX 供应商名称_Index
ON 供应商信息(供应商名称)
GO</strong></span>

当为数据表建立聚集索引时，数据库会对索引列进行排序。关键字ASC或DESC可以确保索引列的升序或降序排序方向。默认设置为ASC升序排列。

4.查看和删除

在SQL Server中，索引的查看和删除可以通过企业管理器和SQL语言两种方式。

对于索引的查看，大家可以在SQL Server中实践一把，很基础的操作，这里便不多做介绍。

当一个索引不再需要时，可以将其从数据库中删除，以回收它当前使用的存储空间。这些回收的空间可以由数据库中的任何对象使用。

索引的删除方式和索引的创建时的使用方式有关。如果索引是使用CREATE INDEX语句显式创建的，则可以使用DROP INDEX语句将其删除。其语法格式为：

<span style="font-size:18px;color:#000099;"><strong>DROP INDEX 'table.index | view.index'[,...n]</strong></span>

其中，table和view表示索引所在的表或视图；index表示要删除的索引名称，索引名称必须是符合标识符的规则；[,...]表示前面的项可重复n次。

举例来说，下面的SQL语句用于删除数据库“经销商”的“商品信息”中的索引“商品_INDEX”。

<strong><span style="font-size:18px;color:#000099;">USE 经销商
GO
DROP INDEX 商品信息.商品_INDEX</span></strong>

如果想删除一个索引，但是不能够确定这个索引是否存在，同样可以添加简单的判断语句先进行判断。

举例来说，删除数据库“经销商”的“顾客信息表”中名为“顾客信息表_index”索引。

<span style="font-size:18px;color:#000099;"><strong>USE 经销商
IF EXISTS(SELECT name FROM sysindexes)
          WHERE name='顾客信息表_index'
     DROP INDEX 顾客信息表.顾客信息表_index
GO</strong></span>

5.学习小结

对于索引这一章节的学习总结，感觉自己又学会了一个工具，而这个工具的作用正是我们想要的——提高效率。尤其是在做项目的时候，数据很大而不得做好索引，便于查找和利用，很开心学习和总结这一章节的内容，收获还蛮不错的，主要还是先前所说，对于SQL的知识，实践一把，很是值得，愿与大家分享一下吧！