【转】SQL Server索引优化方案

转自：http://blog.csdn.net/ylqmf/article/details/5368903

 

一、 主键与聚集索引并不是一对一匹配的

一般情况下我们都认为, 聚集索引和主键是相互匹配的, 因为只要你在SQLServer 表中定义了一个主键, 那么SQLServer 会为这个主键自动添加聚集索引. 但是, 如果你先在表中基于任意一列建立聚集索引, 然后再选择另一列作为主键, 这时, 这个SQLServer 将会基于这个主键建立一个唯一非聚集索引.

二、 聚集索引会被应用到每个查询中

一个SQLServer 表内, 最多只能有一个聚集索引, 并且表中数据行存储的位置由聚集索引来决定. 表中非聚集索引与数据行的映射, 是通过聚集索引来定位的. 当聚集索引执行插入操作时, 数据表中的行要进行移动, 同时该表中所有非聚集索引要重新排列, 这是非常消耗资源并且影响SQLServer 响应速度的.

所以SQLServer 表中的聚集索引一定要避免新添加数据时执行插入操作, 保证表中新添加的数据是从索引的尾部追加的. 这样做可以保证聚集索引相对静态, 对非聚集索引的影响也会减小.

创建聚集索引时, 要选择那些数据会不断增加的字段, 最好的例子就是bbs 发帖表中, 帖子发布时间, 这个字段中的数据是随时间增长的, 理论上讲是不会重复的, 最适合建立聚集索引.

如果你的聚集索引真的做到了不断增加, 那么它的填充因子就应该是 100% , 这个数值越高, 每个8KB 大小的索引页记录的行数就越多, 进行相同的扫描时IO 、内存和CPU 资源就用的越少, 换句话说就是查询效率更高.

三、 聚集索引的数据类型位宽将影响查询效率

聚集索引列的数据类型位宽越小, 查询效率越高, 并且由于非聚集索引是依靠聚集索引来影射SQLServer 表中数据行的, 聚集索引的位宽必将影响表中所有非聚集索引的大小. 通常适合作为聚集索引的类型包括:

Smallint,int,bigint,datetime 和 UNIQUEIDENTIFIER .

评论

1 楼 北极的。鱼 2011-08-10  

索引分为两大类:聚集索引和非聚集索引

一、聚集索引

当数据表中的一列被确定为主键后,SQLServer会自动为它建立聚集索引,因为聚集索引是标识每个记录行的键,所以它将被应用到每个查询中.

二、非聚集索引

非聚集索引的情况就比较复杂了,因为它是相对于表独立组织的,在SQLServer中有单独的结构来存储非聚集索引.



有一点是要注意的,不要代替查询优化器去指定某个索引,DBA应该想办法避免优化器不使用索引进行查询:

1、高碎片率

当索引碎片率超过40%的时候,优化器会放弃使用索引,因为在碎片率如此高的索引中扫描数据还不如直接到表中去扫描.

DBA应该常常监视索引碎片率,当达到30%的时候就要考虑重新组织索引

2、唯一性

如果一个索引不是唯一索引,那么优化器会认为直接到数据表中扫描比使用这个索引的效率要高

3、查询中使用了函数

当查询中使用了函数时,在该列上建立的索引是不会被使用的.

例如:col建立了索引,但是在查询中使用了 convert(nvarchar,col)=’test’,这时查询优化器不会使用该索引

4、查询错误的字段

如果建立组合字段的索引,那么只有当第一个列被查询使用时,该索引才会被使用

例如:建立一个索引(col1,col2,col3),当查询中使用col2=’test’时索引不会被使用.只有col1=’test’这样的查询,这个索引才会被使用

如果你想对col3查询时使用索引,那么只能建立两个索引,一个建立在col1上,另一个建立在col3上



另外一个要注意的是,非聚集索引与聚集索引不同,聚集索引填充因子为100%并且没有碎片,因为数据时顺序追加的.非聚集索引要考虑到数据填充时,索引页填充因子.

在重建索引时还要根据前一阶段的监视重新设定填充因子.一般的情况是70%-80%,我最低使用过65%的填充因子.