Sql server 索引碎片

数据库索引体系

要了解数据库的索引碎片，首先需要了解数据的索引和数据的存储方式

数据库中的每一个表要么是堆表（非聚集索引），要么就是有序表（聚集索引）。堆表，在使用非聚集索引查询数据的时候，会使用书签查找去底层的数据表中去检索需要的数据，这个书签查找会通过每一个索引中包含的行标识（RID）去定位每一个底层数据表的数据行。如果表上面有聚集索引，那么在使用非聚集索引查找其他需要数据的时候，就会使用聚集索引键去定位底层的数据行。

索引系统是由索引页组成的，索引中的每一个条目包含在页中。每8个页组成一个块。索引的层级是从底向上的，就是一个树结构，最下面的就是第0层，也是叶节点。索引中的根节点处于整个索引的最上层。
如果要扫描整个索引，那么就意味着必须要读取页节点中的每一个页（要么是数据页，要么是索引页）。其中，每个页都包含着一个指向它前面的页和一个指向它后面也的指针。

而数据库中的页（不管是数据页，还是索引页 ，还是其他的类型的页）处于的逻辑顺序和它的物理顺便不一定就是一样的，也就说，在A页中的指针指向了它的下一个页B，也就说A和B页在逻辑上面是一起的，但是它们在物理上面可能不一样，甚至B页和A页在物理上相隔几百个页。

如果在逻辑上面相连的页在物理存储级别相隔的越近，那么在读取这些页的时候所花的I/O成本也就越小，因为产生磁盘的磁头移动带来的延迟。相反，如果他们的物理存储顺序和逻辑顺序一致，那么SQL Server在读取的时候，就可以一次读取，因为每次会读取一个块（8个页）。

数据库碎片（只讨论索引碎片）

碎片的概念：任何情况下，访问一个表时，会造成比适量更多或更长的磁盘IO的数据库页填充情形，而对于一个Select操作，最佳场景是在表的数据页是尽可能连续的，而且页是尽可能完整打包（fully packed）的。碎片会破坏这个规则，降低查询的性能。

在Sql Server中存储数据的最小单位是页，每一页能容纳的数据为8kb，页的组织方式是通过B树结构，如图：

只有叶子节点实际存储数据，其他节点存放查找叶子节点的数据

一个叶子节点为一页，Sql Server向每页中存储数据的最小单位是Row，当叶子节点插入新行或者更新的数据超出本页容量，就会分页，从而产生碎片

索引碎片

内部索引碎片

每一个索引页中都包含一些索引的条目（就类似数据页包含很多的数据行）。但是，很多的时候，不是每个页都包含了最大的条数。例如，一个页的大小8k，也就是4096字节，除去一些页头，页脚等，还剩下8000多字节，如果每个索引条目的大小事100字节，那么这个索引页最大就可以包含80个条目，但是很多的情况下，却没有包含这么多。
在当前页不足于存储新的数据，但是同时页没有填满的情况下，碎片就产生了，即 内部碎片在索引页里有可用空间时发生，这个可以由insert/update/delete等DML语句操作造成
内部碎片用索引的平均页饱和度（average page fullness of the index）来衡量（页密度）

图解：

在8K的页里，最大有8060bytes用来保存数据。剩下的空间被页头和行偏移数组使用。假设我们有100bytes定长的索引，共有800个索引条目。因此我们每页可以保存 8060/100=80条索引，剩下的60 bytes没有足够的空间保存更多的索引，这就需要10页来保存全部的索引结构。如果你要计算这个索引的平均饱和度，这个情况下是992.26%（8000/8060）。

假设我们随机删除表里一半的索引，即将索引条目减小为400。那页的分布情况会如下图所示。这里有40600 bytes空余可用。这时候我们计算下平均饱和度 4000 * 10 /80600=49.62%。可以看到近一半的页面是空的，且索引有了内部碎片。

内部碎片如何影响SQL Server的性能？

1. 内部碎片会增加IO操作。当执行的查询扫描部分或全部的表/索引时，如果那个表/索引上面有内部碎片，它会增加额外的页读取。在我们的例子里，全部数据可以在5个页面里保存。当查询扫描索引时，需要读取10个页面，而不是5个页面，增加了近50%的IO。
2. 内部碎片降低缓存效率。当索引有碎片时，在缓存里就会占用更多的空间。在我们的例子里，这个索引会使用5个额外页来占用缓存，这个是可以用来缓存其他索引页的。这会降低缓冲命中率。依次增加物理IO，同样增加逻辑读。
3. 增加数据库文件。需要更多的空间来存储额外页，并降低备份和还原性能。

外部索引碎片

外部碎片发生在页的逻辑顺序和页的物理顺序不一致。外部碎片指的是索引的逻辑顺序和物理顺序缺少修正。它以索引叶子层页的无序占比来衡量。无序页就是分配给索引的下一个物理页和当前叶子页里里下一页指针指向的页不一致。

图解：

可以看到，这3页数据是按顺序存储的。换句话说，逻辑顺序和物理顺序是一样的，它保存了索引键从1到16。除了第3页，所有页都已经满了。现在插入页面4后，页面发生的变化。

在插入的4的时候，默认情况下应该在3和5之间插入，可惜页面1已经没有空间再插入一条记录了。唯一的选择只能把页面1分开，一半数据在页面1，另一半数据在新页（页面4）。从图中我们看出页面4的逻辑顺序和物理顺序已经不一致了。

造成外部碎片有下列原因：

1. 当给新表分配页的时候，SQL Server是从混合区开始分配页面的，直到满8个页。因此很有可能第一个8页是来自不同的8个区。
2. 当从表里删除一条记录时，这个页会从索引页里取消分配（页面取消分配不会立即发生），这会产生缺口，并增加碎片。
3. 一旦对象达到8页。SQL Server会从统一区开始分配新页。当把统一区分配给索引时，下一个区很可能已经分配给了其他的对象/索引。
   外部碎片如何影响SQL Server的性能？

当读取单条记录时，外部碎片不会影响性能，因为是直接到页里拿记录。无序扫描也不会受外部碎片影响，因为使用IAM页就可以找到需要的区。在有序的索引扫描里，外部碎片才会成为性能下降因素。性能下降的原因是磁盘的磁头需要物理磁盘上来回跳，相比下连续的话就会持续的读操作。还要注意的是，一旦页被载入缓冲池，外部碎片也不会影响到性能。

索引碎片的查询

DBCC查看索引碎片

DBCC SHOWCONTIG   
[ (   
    { table_name | table_id | view_name | view_id }   
    [ , index_name | index_id ]   
) ]   
    [ WITH   
        {   
         [ , [ ALL_INDEXES ] ]   
         [ , [ TABLERESULTS ] ]   
         [ , [ FAST ] ]  
         [ , [ ALL_LEVELS ] ]   
         [ NO_INFOMSGS ]  
         }  
    ]

或者用 sys.dm_db_index_physical_stats 判断碎片程度

sys.dm_db_index_physical_stats (   
  { database_id | NULL | 0 | DEFAULT }  
, { object_id | NULL | 0 | DEFAULT }  
, { index_id | NULL | 0 | -1 | DEFAULT }  
, { partition_number | NULL | 0 | DEFAULT }  
, { mode | NULL | DEFAULT }  
)

索引碎片一般解决方案

1. 删除索引并重建

重新创建聚集索引将重新组织数据，从而使数据页填满。 填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。 这种方法的缺点是索引在删除/重新创建周期内为脱机状态，并且该操作是一个整体，不可中断。 如果中断索引创建，则不能重新创建索引。

DROP INDEX index_name ON tabel_name
CREATE CLUSTERED INDEX index_name ON tabel_name(Id)

2. 对索引的叶级页按逻辑顺序重新排序

使用 INDEX…REORGANIZE，对索引的页级页按逻辑顺序重新排序。 由于此操作是联机操作，因此语句运行时索引可用。 此外，中断该操作不会丢失已完成的工作。 这种方法的缺点是在重新组织数据方面没有聚集索引的删除/重建操作有效。

REORGANIZE 操作通过对叶级页以物理方式重新排序，使之与叶节点的从左到右的逻辑顺序相匹配，进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。 重新组织还会压缩索引页。 压缩基于现有的填充因子值。

ALTER INDEX index_name ON table_name REORGANIZE

3. 重新生成索引

使用 REBUILD 和 ALTER INDEX 重新生成索引。此操作将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。 如果指定 ALL，将删除表中的所有索引，然后在单个事务中重新生成。

ALTER INDEX index_name ON table_name REBUILD WITH (ONLINE=OFF)

REBUILD 模式

online模式下，REBUILD 操作会复制旧索引来新建索引，此时旧的索引依然可以被读取和修改，但是所有在旧索引上的修改都会同步更新到新索引下。中间会有一些冲突解决机制。然后在REBUILD 即将完成的时候，会对table上锁一段时间，在这段时间里会用新索引来替换旧索引，当这个过程完成以后再释放table上面的锁。如果索引列包含 LOB对象的话，在SQL Server 2005等版本中rebuild index online会失败。在SQL server 2012中消除了这个限制

offline模式下，REBUILD 会对table上锁，所有对这个table的读写操作都会被阻塞，在这期间新索引根据旧索引来创建，其实就是一个复制的过程，但是新索引没有碎片，最后使用新索引替换旧索引。当REBUILD 整个过程完成以后，table上面的锁才会被释放。

索引填充因子

在向索引中添加新行时容易发生分页，不仅在页拆分时会降低性能，还会导致产生过多的索引碎片（内部碎片）。

SQL Server允许在创建索引时指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，减少页拆分的可能性。

填充因子的值是从 0 到 100 之间的百分比数值，指定在创建索引后对数据页的填充比例。值为 0或100 时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会频繁发生更改时，这种设置更为适当。