详细讲解SQL Server索引的性能问题(2)

四、索引的选择技术

p_detail是住房公积金管理系统中记录个人明细的表，有890000行，观察在不同索引下的查询运行效果，测试在C/S环境下进行，客户机是IBM PII350(内存64M),服务器是DEC Alpha1000A(内存128M),数据库为SYBASE11.0.3。

1、

select count(*) from p_detail where 
op_date>’19990101’ and op_date<’ 
19991231’ and pri_surplus1>300 

2、

select count(*),sum(pri_surplus1) from p_detail 
where op_date>’19990101’ and 
pay_month between‘199908’ and’199912’ 

不建任何索引查询1 1分15秒

查询21分7秒

在op_date上建非聚簇索引查询1 57秒

查询2 57秒

在op_date上建聚簇索引查询1 <1秒

查询2 52秒

在pay_month、op_date、pri_surplus1上建索引查询1 34秒

查询2 <1秒

在op_date、pay_month、pri_surplus1上建索引查询1 <1秒

查询2 <1秒

从以上查询效果分析，索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件体现于where从句和join表达式中。一般来说建立索引的思路是：

(1)主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。
(2)有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。
(3)经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。
(4)如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。
(5)在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。
(6)在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。

五、索引的维护

上面讲到,某些不合适的索引影响到SQL Server的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将会影响到索引的使用。这时需要用户自己来维护索引。索引的维护包括：

1、重建索引

随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：

(1)数据和使用模式大幅度变化。

(2)排序的顺序发生改变。

(3)要进行大量插入操作或已经完成。

(4)使用大块I/O的查询的磁盘读次数比预料的要多。

(5)由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。

(6)dbcc检查出索引有问题。

当重建聚簇索引时，这张表的所有非聚簇索引将被重建。

2、索引统计信息的更新

当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行update statistics命令：

(1)数据行的插入和删除修改了数据的分布。

(2)对用truncate table删除数据的表上增加数据行。

(3)修改索引列的值。

六、结束语

实践表明，不恰当的索引不但于事无补，反而会降低系统的执行性能。因为大量的索引在插入、修改和删除操作时比没有索引花费更多的系统时间。例如下面情况下建立的索引是不恰当的：

1、在查询中很少或从不引用的列不会受益于索引，因为索引很少或从来不必搜索基于这些列的行。
2、只有两个或三个值的列，如男性和女性(是或否)，从不会从索引中得到好处。

另外，鉴于索引加快了查询速度，但减慢了数据更新速度的特点。可通过在一个段上建表，而在另一个段上建其非聚簇索引，而这两段分别在单独的物理设备上来改善操作性能。 

【相关文章】

• 详细讲解大型数据库的设计原则与开发技巧

• SQL Server 2008关系数据库引擎的新增功能

• SQL Server 2005数据库管理与应用前言