MSSQL中的索引碎片
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) |
查看碎片也可以通过SSMS工具来查看：
重新组织索引使用的系统资源最少，并且是联机操作。也就是说，不保留长期阻塞性表锁，且对基础表的查询或更新可以在 ALTER INDEX REORGANIZE 事务处理期间继续进行。数据库引擎 通过以物理方式重新排序叶级别页，以匹配叶节点逻辑顺序（从左到右），从而对表和视图中的聚集索引和非聚集索引的叶级别进行碎片整理。重新组织还会根据索引的填充因子值压缩索引页。
重新生成索引将会删除并重新创建索引。重新生成操作可以联机或脱机执行，具体取决于索引类型和 数据库引擎 版本。对于行存储索引，重新生成操作会：删除碎片；根据指定或现有的填充因子设置来压缩页，从而回收磁盘空间；还会在连续页中重新排序索引行。如果指定 ALL，将删除表中的所有索引，然后在一个事务中重新生成。不必预先删除外键约束。重新生成具有 128 个区或更多区的索引时，数据库引擎延迟实际的页释放及其关联的锁，直到事务提交。
不过也有注意以下事项：
- 如果删除并重建了聚集索引，那么非聚集索引也将重建。
- 对于堆表的产生的碎片，可以创建并删除聚集索引，必要可保留聚集索引。
- 当索引重新生成发生时，物理介质必须有足够的空间来存储索引的两个副本。
以下场景强制自动在表上重新生成所有行存储非聚集索引：
- 在表上创建聚集索引
- 删除聚集索引，从而使表存储为堆
- 更改聚集键以包括或排除列
以下场景不需要在表上自动重新生成所有行存储非聚集索引：
- 重新生成唯一聚集索引
- 重新生成非唯一聚集索引
- 更改索引架构，例如将分区方案应用于聚集索引或将聚集索引移到其他文件组
函数sys.dm_db_index_physical_stats
返回SQL Server中指定表或视图的数据和索引的大小和碎片信息。 对于索引，针对每个分区中的 B 树的每个级别，返回与其对应的一行。 对于堆，针对每个分区的 IN_ROW_DATA 分配单元，返回与其对应的一行。 对于大型对象 (LOB) 数据，针对每个分区的 LOB_DATA 分配单元返回与其对应的一行。 如果表中存在行溢出数据，则针对每个分区中的 ROW_OVERFLOW_DATA 分配单元，返回与其对应的一行。 不返回有关 xVelocity 内存优化的列存储索引的信息。
在对表进而对表中定义的索引进行数据修改（INSERT、UPDATE 和 DELETE 语句）的整个过程中都会出现碎片。 由于这些修改通常并不在表和索引的行中平均分布，所以每页的填充度会随时间而改变。 对于扫描表的部分或全部索引的查询，这种碎片会导致额外的页读取。 这会妨碍数据的并行扫描。
索引或堆的碎片级别显示在 avg_fragmentation_in_percent 列中。 对于堆，此值表示堆的区碎片。 对于索引，此值表示索引的逻辑碎片。 与 DBCC SHOWCONTIG 不同，这两种情况下的碎片计算算法都会考虑跨越多个文件的存储，因而结果是精确的。
为了获得最佳性能，avg_fragmentation_in_percent 的值应尽可能接近零。 但是，从 0 到 10％ 范围内的值都可以接受。 所有减少碎片的方法（例如重新生成、重新组织或重新创建）都可用于降低这些值。 有关如何分析索引中碎片程度的详细信息，请参阅 重新组织和重新生成索引。
语法
1 2 3 4 5 6 7 | 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 } ) |
参数
database_id |NULL |0 |默认
数据库的 ID。 database_id 为 smallint。 有效的输入包括数据库的 ID 号、NULL、0 或 DEFAULT。 默认值为 0。 在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL 可返回 SQL Server 实例中所有数据库的信息。 如果为 database_id指定 NULL，则还必须为 object_id、 index_id和 partition_number指定 NULL。
可以指定内置函数 DB_ID。 如果在不指定数据库名称的情况下使用 DB_ID，则当前数据库的兼容级别必须是 90 或更高。
object_id |NULL |0 |默认
索引所针对的表或视图的对象 ID。 object_id 为 int。
有效的输入包括表和视图的 ID 号、NULL、0 或 DEFAULT。 默认值为 0。 在此上下文中，NULL、0 和 DEFAULT 是等效值。 从 2016 (13.x) SQL Server 起，有效输入还包括 Service Broker 队列名称或队列内部表名称。 当 (所有对象、所有索引等) 应用默认参数时，所有队列的碎片信息将包含在结果集中。
指定 NULL 可返回指定数据库中的所有表和视图的信息。 如果为 object_id指定 NULL，则还必须为 index_id 和 partition_number指定 NULL。
index_id |0 |NULL |-1 |默认
索引的 ID。 index_id 为 int。有效输入是索引的 ID 号;如果 object_id 为堆、NULL、-1 或 DEFAULT，则为 0。 默认值为 -1。 NULL、-1 和 DEFAULT 是此上下文中的等效值。
指定 NULL 可返回基表或视图的所有索引的信息。 如果为 index_id指定 NULL，则还必须为 partition_number指定 NULL。
partition_number |NULL |0 |默认
对象中的分区号。 partition_number 为 int。有效输入是索引或堆 的partion_number 、NULL、0 或 DEFAULT。 默认值为 0。 在此上下文中，NULL、0 和 DEFAULT 是等效值。
指定 NULL，以返回有关所属对象的所有分区的信息。
partition_number 从 1 开始。 非分区索引或堆 partition_number 设置为 1。
模式 |NULL |默认
模式的名称。 mode 指定用于获取统计信息的扫描级别。 mode 为 sysname。 有效输入为 DEFAULT、NULL、LIMITED、SAMPLED 或 DETAILED。 默认值 (NULL) 为 LIMITED。
减少索引中的碎片
当索引分段的方式导致碎片影响查询性能时，有三种方法可减少碎片：
删除并重新创建聚集索引。
重新创建聚集索引将对数据进行重新分布，从而使数据页填满。 填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。 这种方法的缺点是索引在删除和重新创建周期内为脱机状态，并且操作属原子级。 如果中断索引创建，则不能重新创建索引。 有关详细信息，请参阅 CREATE INDEX (Transact-SQL)。
使用 ALTER INDEX REORGANIZE（代替 DBCC INDEXDEFRAG）按逻辑顺序重新排序索引的叶级页。 由于这是联机操作，因此在语句运行时仍可使用索引。 中断此操作时不会丢失已经完成的任务。 此方法的缺点是在重新组织数据方面不如索引重新生成操作的效果好，而且不更新统计信息。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
