SQL Server 查询优化(4)_索引的碎片与管理.doc

一、索引碎片
无论是索引组织表（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非常频繁的表，为了保证数据扫描的效率，可以考虑利用作业来在系统相对空闲的时候定期进行索引整理。