SQL Server 索引碎片
Tags： MSSQLSQL Server索引碎片
简介
今天聊聊碎片吧，什么是索引碎片，为什么我应该关注它：
当索引包含的页中，索引中的逻辑排序（基于索引中的键值）与索引页中的物理排序不匹配时，就存在碎片。
无论何时对基础数据执行插入、更新或删除操作，数据库引擎 都会自动修改索引。例如，在表中添加行可能会导致拆分行存储索引中的现有页，以腾出空间来插入新键值。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。
大量碎片式索引可能会降低查询性能，因为需要额外 I/O 来查找索引指向的数据。较多的 I/O 会导致应用程序响应缓慢，特别是在涉及扫描操作时。
一般我们可以把碎片分为外部碎片和内部碎片。外部碎片，可分为逻辑碎片（索引）和区碎片（堆）。内部碎片主要有页填充密度衡量。
逻辑碎片，这是索引的叶级页中出错页所占的百分比。对于出错页，分配给索引的下一个物理页不是当前叶级页中的下一页 指针所指向的页。
区碎片，这是堆的叶级页中出错区所占的百分比。出错区是指：包含堆当前页的区在物理上不是包含前一页的区后的下一个区。
内部碎片，指示索引页的平均填充率（以百分比表示）。100% 表示索引页完全填充，没有碎片。不过这种理想状态很难存在。
先说说内部碎片，右键索引属性下可以查看其页填充度。
该索引的页填充度为 97.90%，说明还有一部分空间未使用，有点浪费了。当经过多次DML操作后（如下图），页内有较多的空间空闲出来了，而实际存储的数据只是很少一部分。所以当我们查询部分数据时，需要查询更多的数据页才能获取完整数据，这样增加了IO。
那外部碎片又是什么样子呢？什么样的数据页是物理不连续呢？
我们创建一个数据文件，数据文件将划分成数据页来存储数据。假设我们预先给数据文件分配一定的空间，数据页分配理应是按顺序在磁盘文件上分配空间（如下图）。当新的数据插入表后，页内空间不足以填充新的行数据，因此产生了页拆分。由于索引键的顺序性，逻辑上要保证其页是顺序的，但是物理上已经出现了跨多个页的情况。这种就是外部碎片。同理，堆表的区碎片也类似。
如何查看索引碎片，可通过系统函数sys.dm_db_index_physical_stats 查看。
| 列 | 说明 |
|---|---|
| avg_fragmentation_in_percent | 堆的区碎片/逻辑碎片（索引中的无序页）的百分比 |
| avg_page_space_used_in_percent | 平均页密度 |
既然索引碎片产生了更多的空间和磁盘IO，定期清理仍是有必要的。参考以上的字段 avg_fragmentation_in_percent ，进行索引重组或者索引重建。
| avg_fragmentation_in_percent | 处理方法 |
|---|---|
| > 5% 且 < = 30% | ALTER INDEX REORGANIZE |
| > 30% | ALTER INDEX REBUILD WITH (ONLINE = ON) |
重新组织索引使用的系统资源最少，并且是联机操作。也就是说，不保留长期阻塞性表锁，且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续进行。数据库引擎 通过以物理方式重新排序叶级别页，以匹配叶节点逻辑顺序（从左到右），从而对表和视图中的聚集索引和非聚集索引的叶级别进行碎片整理。重新组织还会根据索引的填充因子值压缩索引页。
重新生成索引将会删除并重新创建索引。重新生成操作可以联机或脱机执行，具体取决于索引类型和 数据库引擎 版本。对于行存储索引，重新生成操作会：删除碎片；根据指定或现有的填充因子设置来压缩页，从而回收磁盘空间；还会在连续页中重新排序索引行。如果指定 ALL，将删除表中的所有索引，然后在一个事务中重新生成。不必预先删除外键约束。重新生成具有 128 个区或更多区的索引时，数据库引擎延迟实际的页释放及其关联的锁，直到事务提交。
不过也有注意以下事项：
- 如果删除并重建了聚集索引，那么非聚集索引也将重建。
- 对于堆表的产生的碎片，可以创建并删除聚集索引，必要可保留聚集索引。
- 当索引重新生成发生时，物理介质必须有足够的空间来存储索引的两个副本。
以下场景强制自动在表上重新生成所有行存储非聚集索引：
- 在表上创建聚集索引
- 删除聚集索引，从而使表存储为堆
- 更改聚集键以包括或排除列
以下场景不需要在表上自动重新生成所有行存储非聚集索引：
- 重新生成唯一聚集索引
- 重新生成非唯一聚集索引
- 更改索引架构，例如将分区方案应用于聚集索引或将聚集索引移到其他文件组
