数 据 库 索 引 对 数 据 操 作 的 影 响

□付利军，杨金劳

(运城农业职业技术学院，山西 运城 044000)

一、建立索引的理由

索引是对数据库表中一列或多列的值进行排序的一种结构，创建索引可以大大提高系统的性能。首先，建立索引主要是为了加快表的查询速度，因为对于创建了索引的列几乎是立即响应，而不创建索引的列则需要等待较长的时间。这也是创建索引的最主要的原因。其次，创建索引还能加速连接、ORDER BY和GROUP BY语句的执行。因为连接、ORDER BY和GROUP BY都需要数据检索，在建立索引后，其数据检索速度就会加快，从而也就加速了连接等操作。再次，通过创建惟一索引，可以保证数据库表中每一行数据的惟一性。除此之外，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

二、不合理的使用索引对数据操作的影响

在某些表中创建索引后，数据检索的速度有所提高，但对表的操作(新增、修改、删除)速度明显变慢，而在某些表中创建索引后，数据检索的速度降低，表的操作也受到影响。

SQL Server的索引数据保存在数据分页中，在对表进行新增、修改、删除时，SQL Server需要维护索引数据，因此在表中创建索引后，对数据的新增、修改、删除会造成一定的影响，当然这种影响不一定是负面的，如果索引合理，对于带条件的修改、删除操作，则可以使用索引定位数据，所以合理的索引会提高带条件的修改、删除操作的处理效率。

一般来说，不合理的索引表现在以下几个方面：

在表中创建了过多的索引。SQL Server 2000支持为每个表创建1个聚集索引，249个非聚集索引。不过，就一般情况而言，如果不是相对固定的表(很少做数据新增、修改、删除)，建议每个表的索引不要超过15个，过多的索引会因为SQL Server维护索引的工作而大大影响表的新增、修改、删除处理速度。索引应该只建立在经常用于排序和条件的列上。

过多的索引还会影响Transact-SQL 批处理语句的执行，SQL Server生成执行计划时，会生成资源尽可能小的方案，过多的索引必须导致SQL Server评估更多的Transact-SQL批处理语句执行计划，而这部分处理是最耗CPU资源的。

在键值大的列上建立索引。SQL Server 2000支持的单个索引最大字节数为900字节，不过，由于索引存放在数据页中，因此越小的索引检索的效率越高(越小的索引意味着每个数据页包含的索引键值越多，检索数据所需要读取的数据页就越少，大大降低了磁盘I/O)。对于大量字符数据的检索，应该考虑配置全文检索。

不合理地使用聚集索引。由于聚集索引决定表中记录的存储顺序，因此，如果将聚集索引建立在不合适的字段上，会导致新增、修改、删除记录时，大量数据的移动。如聚集索引不适用于频繁更改的行，因为数据必须按聚集索引的键值改变行的物理顺序存储，如果聚集索引列中的数据频繁修改，将导致聚集索引频繁修改，数据整行移动，消耗大量的系统资源。例如，在一个记录用户操作的日志记录表中，由于日志是按时间顺序记录的，在用户ID上建立聚集索引就不太合适。

不合理地使用填充因子。在创建聚集索引时，表中的数据按照索引列中的值的顺序存储在数据库的数据页中。在表中插入新的数据行或更改索引列中的值时，SQL Server可能必须重新组织表中的数据存储，以便为新行腾出空间，保持数据的有序存储。这同样适用于非聚集索引。添加或更改数据时，SQL Server可能不得不重新组织非聚集索引页中的数据存储。向一个已满的索引页添加某个新行时，SQL Server把大约一半的行移到新页中以便为新行腾出空间。这种重组称为页拆分。页拆分会降低性能并使表中的数据存储产生碎片。创建索引时，可以指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分的可能性。填充因子的值是从0～100的百分比数值，指定在创建索引后对数据页的填充比例。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。提供填充因子选项是为了对性能进行微调。但是，使用sp_configure系统存储过程指定的服务器范围的默认填充因子，在大多数情况下都是最佳的选择。

如果为经常做新增、修改、删除记录的表建立索引时，指定了过大的填充因子，则会因为频繁的页拆分操作而大大降低数据处理的效率。由于页拆分也会导致表中的数据存储产生碎片，所以查询性能也会受到影响。一般来说，数据操作(新增、修改、删除)越频繁，填充因子的设置越小，以调高数据处理效率。反之，则填充因子应该设置得越大以节约存储空间。

三、索引建立建议

虽然索引很重要，但也不是越多越好：一是因为创建索引要花费时间，这种时间随着数据量的增加而增加。二是因为创建索引要占用存储空间。除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。三是因为索引虽然加快了检索速度，却减慢了数据修改的速度。因为每当执行一次数据修改(包括插入、删除、更新)时，就需要进行索引的维护，对建立了索引的列执行操作要比未建立索引的列执行修改操作所花的时间长，修改的数据越多，涉及维护索引的开销也就越大。

过多的索引影响数据操作(新增、修改、删除)，过少的索引影响查询效率，所以索引的建立应该恰到好处。在确定是否应该建立索引的时候，除了索引合理性的考虑外，还应该参考下面几方面：

选择性。选择性是符合查询条件的记录占总记录数的百分比，百分比越小，选择性高，越适合于建立索引。

数据密度。数据密度是1除以代表键惟一的记录数。数据密度越小，该字段越适合建立索引。

数据的使用时机。索引可以建立，使用完成后再删除。这适用于在某个时间要处理的数据。比如：每个月都要做一些月报，平时是不做的，那么在做月报的时候，可以根据取数的需要，建立适当的索引，在做完月报后删除索引，以免影响平时的数据处理。

索引可以先删除，等处理完后再建立以适应不同的操作要求。比如，要对表中的数据进行大批量的更新和插入时，应先删除索引，等大批量数据插入或更新完成后，再重新建立索引。因为在更新或插入时需要花费代价来进行索引的维护。

多尝试。由于更新索引结构不会对前段程序的数据存取，因此对于是否需要建立索引不确定的处理，可以分别测试建立索引与不建立索引后，对查询及数据处理的影响，以根据实际测试的结果来确定是否建立索引，或者调整索引。

一般来说，可以在以下的列考虑建立索引：(1)用作查询条件的列，如主键。一般而言，存取表的最常用的方法是通过主键来进行。因此，我们应该在主键上建立索引。(2)在按排序顺序频繁检索的列和某一范围内频繁搜索的列。(3)连接中频繁使用的列。因为用于连接的列如果按顺序存放，系统可以很快执行连接。 在如下情况的列上一般不考虑建立索引：(1)很少或从来不在查询中引用的列。因为系统很少或从来不根据这个列的值去查着行，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。(2)只有两个或若干个值的列(如性别：男或女)，也得不到建立索引的好处。这是因为这些列的取值很少，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。(3)小表(行数很少的表)一般也没有必要创建索引。因为在小表中通过索引查找行可能会比简单地进行全表扫描还慢。(4)当数据修改的性能比数据查询的性能更重要时不应创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

总之，创建索引是为了加速对表中数据的检索，可以提高系统的性能。但不合理的使用索引会带来负面的影响，使表的操作受到影响，所以在表中要创建合适的索引，以提高数据库的性能。

参考文献：

[1]刘世峰等.数据库基础与应用[M].北京: 中央广播电视大学出版社, 2003.

[2]赵松涛，吴维元.SQL Server 2000 系统管理实录[M]. 北京: 电子工业出版社, 2006.

[3]余金山.SQL Server 2000/2005 数据库开发实例入门与提高[M].北京: 电子工业出版社, 2005.

[4]萨师煊，王珊. 数据库系统概论[M]. 北京:高等教育出版社, 2000.

[5]邹建.中文版SQL Server 2000 开发与管理应用实例[M].北京: 人民邮电出版社, 2005.