SQL Server 查询优化之四_索引的碎片与管理

一、索引碎片
无论是索引组织表（IOT）还是堆表（HEAP），随着数据的增删改，都会或多或多的产生碎片。碎片的存在，主要对于数据扫描效率有着较大的影响，对于数据查找效率几乎没有影响或者说影响很小，如果想要改善数据查找的效率，进行索引碎片整理并没有什么效果。以下的碎片分类也主要是从数据扫描着眼。

1、内部碎片
内部指的是页内，即页面的空闲空间。其实填充因子就是一种碎片，为了减少页拆分，宁愿适当地去制造这种碎片。但在大量内部碎片一直处于无法被数据填充的情况下，是没有益处的，它会导致扫描过程中读取额外的页面。

对于LOB和ROW_OVERFLOW_DATA页面，这是唯一的碎片形式，因为在这两种列上无法建立B树索引。

 

2、外部碎片
（1）逻辑碎片

索引叶子节点页的逻辑顺序与物理顺序不一致，比如：有页号1，2的两个页面，此时1页面发生页拆分，这时新申请的页面页号为3，此时逻辑顺序为1-3-2，但物理顺序是1-2-3，1页面没有直接指向磁盘的下一个物理页，这就造成了不一致，即逻辑碎片；

 

（2）扩展碎片

SQL SERVER通常给表或索引分配新的空间是以EXTENT（区或扩展）的形式，一个区是8个页面，所以区的第一个页号应该是8的倍数，比如：一个包含有序区的表，第一个页面的页号应该是8-16-24，这样下去，如果是8-24，那么说明第一个页面页号为16的区被分配给了另一个表，那么8-24的表在物理上就存在一个间隙，即扩展碎片；

 

外部碎片是对数据连续性的度量，扩展碎片是堆表数据连续性的度量，数据的连续性越差，扫描的成本也会越大。

 

二、查看与管理
对于索引树的管理主要考虑两方面：一是B树的平衡性，这一点数据库系统会自动维护；二是索引碎片，这需要手动去维护。

1、碎片的查看
SQL SERVER支持两种碎片查看方式，至于这两种碎片查看方式的使用方法，帮助文档里有很详细的说明。

（1）dbcc showcontig是SQL SERVER 2000中的唯一碎片查看方式，在SQL SERVER 2005中无法支持LOB类型、ROW_OVERFLOW_DATA及整个分区表的碎片查看；

 

（2）sys.dm_db_index_physical_stats是SQL SERVER 2005新的碎片查看方式；

 

对于碎片的检测以及是否需要进行碎片处理，主要从以下几个参数来看：

（1）内部碎片检测

avg_page_space_used_in_percent：页面空间平均使用比例；

对于数据扫描而言，该参数越大越好，这意味着读取较少的页面即可返回想要的数据。

 

fragment_count：IN_ROW_DATA碎片的数量；

avg_fragment_size_in_pages：IN_ROW_DATA碎片的平均页大小；

以上参数反应了行内数据页碎片的数量及碎片的大小，即便碎片数量很多，但如果碎片很大即很整块的话，通常要大于64KB即一个EXTENT，对于数据扫描而言，效率也是很高的，因为SQL SERVER会跳过这些整块的碎片。

 

ghost_record_count和version_ghost_record_count：虚影纪录数；

对于数据扫描而言，该参数越小越好。

 

forwarded_record_count：前转纪录数；

前转纪录只会在堆中存在，对于数据扫描而言，是非常有帮助的。

 

（2）外部碎片检测

avg_fragmentation_in_percent：页面平均外部碎片的比例；

对于数据扫描而言，该参数越小越好。

 

2、碎片的整理
（1）、索引重建

顾名思义，重新建立索引，对索引的数据进行重新排列。在SQL SERVER 2000中这是一个脱机操作，即索引重建完成前，索引无法访问或使用，在SQL SERVER 2005/2008中支持联机操作。

 

联机过程的实现，就是先维护另一份新索引，然后同步旧索引中变化的部分，同步完成后使用架构锁锁定旧索引，切换表到新的索引上，再释放架构锁，切换的过程是数据库系统目录的维护过程，速度很快，所以可以理解为是联机操作。

 

索引重建的方式有四种：

A：drop index再create index

这种方法是最差的，它会导致表上非聚集索引的两次重建，一次在DROP时指向ROWID，一次在CREATE时重新指向聚集键。以下三种方法只需重新非聚集索引一次。

而且对于主键约束或唯一键约束产生的索引无法直接删除，必须要先删除约束，才可以删除索引；

 

B：create index的drop_existing选项

这种方法完全重建索引，并可以重新指定索引参数；

 

C：alter index的rebuild选项
这是SQL SERVER 2005/2008新的重建索引的方式，它不会重建非聚集索引，除非指定ALL关键字，同时它也更灵活，可以针对表上某个分区重建索引；

 

D：dbcc dbreindex；

这是SQL SERVER 2000时重建索引的方法，它默认使用表上原来create index的参数重建索引。填充因子可重新指定。

 

（2）、索引重组

将索引树的叶节点页重新排序，以消除外部碎片。这是一个联机操作。但不同于索引重建的是，索引重组后统计信息不会得到更新，而且索引重组使用的是冒泡排序法，效率比较低。同时，索引重组只能在单个文件内进行，无法跨文件重组索引。

 

索引重组的方式有两种：

A：alter index的reorgnize选项

这是SQL SERVER 2005/2008新的重组索引的方式，和dbcc indexdefrag一样，它默认对表上所有分区进行索引重组，不可以重新指定填充因子，因为只是索引叶子节点的重新排序。但它比dbcc indexdefrag的选项更丰富些；

 

B：dbcc indexdefrag

这是SQL SERVER 2000时重组索引的方法。

 

注意：

（1）碎片是不可避免，但并不是说一旦检测到碎片就进行碎片整理，有时少量碎片的整理工作反而会带来更大的成本消耗，所以在不能确定索引整理能否带来良好性能时，不要轻易进行索引整理。

（2）不管是内部碎片还是外部碎片，即便进行了碎片整理，也并不一定能够完全消除，对于外部碎片，可以将表独立存放在一个文件组上，并在文件初始化时分配足够的磁盘空间，这样的表经过碎片整理后外部碎片可以消除，但太过浪费磁盘空间；

（3）无论是索引重建、索引重组都会对数据页进行压缩，页面的填充程度由填充因子决定。它们都是事务操作，会产生日志，为了减少事务日志的大小，可在索引整理后进行日志截断或日志备份。

（4）如有必要，对于DML非常频繁的表，为了保证数据扫描的效率，可以考虑利用作业来在系统相对空闲的时候定期进行索引整理。