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

一 索引碎片一 索引碎片 无论是索引组织表 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 无论是