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

一、索引碎片 无论是索引组织表（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）、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）、dbcc indexdefrag一样，它默认对表上所有分区进行索引重组，不可以重新指定填充因子，因为只是索引叶子节点的重新排序。但它比dbcc indexdefrag的选项更丰富些； B：dbcc indexdefrag 这是SQL SERVER 200