如何重组和重建索引在SQL Server数据库?

在MS-SQL Server数据库中，数据存储在页面其中每个页宽为8 KB, 8个连续的页形成一个程度上。

下图显示了这些页面是如何链接在一起的通过b树索引结构。

B树索引结构(其中字母“B”代表平衡)是具有根、中间和叶级别的多层结构。层之间链接的页允许索引查找操作。在Leaf级别，一个“双链表”链接页面。该链接用于扫描索引。

我们可以看到这是一个聚集索引，因为在Leaf级别上有数据页。这意味着:

• 物理数据存储在聚类索引中
• 聚集索引定义表的逻辑顺序

另一方面，非聚类索引具有相同的b树结构，但指向其数据的聚类索引。

什么是索引碎片?

碎片是一种自然现象，被定义为没有连续数据的情况。这是由于不断地在数据库中插入、更新和删除数据。

当存在逻辑排序(在索引中，基于索引的键值)与索引页的物理排序不匹配的页面时，我们可以说b树索引是碎片化的。

例如，当我们向表中添加行时，索引变得碎片化(也称为分散)。数据库引擎也会自动修改所涉及的索引。这可能会导致现有页面拆分，以便为新行腾出空间。

如果在页中插入新行，但是没有足够的空间，则会分配一个新的数据页甚至一个新的区段，同时将现有数据页中的部分数据移动到新分配的数据页中。为了保持索引中的逻辑排序顺序，更新了两个页面上的指针。

索引碎片有两种类型:

• 〇逻辑碎片化当页的逻辑顺序与物理顺序不匹配时。
• 内部分裂-当索引中的数据页包含空闲空间时。

从性能的角度来看，逻辑碎片会导致物理读取次数的增加。内部碎片导致逻辑读取的增加。

更新表中的字段也可能产生碎片。当我们写入更多数据时，可能会出现一页数据填满而需要另一页数据的情况。

我们如何解决这个问题?

为了减少碎片，我们可以执行微软开发的两个任务之一。

1.重组任务

重组任务将索引页移动到更有效的搜索顺序中。要做到这一点:

• 将索引行压缩在一起以尝试释放一些索引页。
• 在小事务中交换剩余的页面，直到所有页面都符合逻辑顺序。在流程结束时，页面的物理顺序与逻辑顺序相同。

结果将生成逻辑上有序的页面，而不是物理上连续的页面。这种方法非常节省空间，因为它只需要8 KB的页面作为要移动的页面的临时存储区域。

当重组任务运行时，底层表或索引不被锁定。但是，如果索引在逻辑上是高度碎片化的，那么该任务将比重新索引花费更长的时间。重组任务的一个重要方面是它在一个小的原子事务中执行。

当处理一个巨大的索引时，我们可以停止任务，只回滚一小部分工作，然后稍后恢复任务。

2.重建任务

重建任务的操作与重组任务不同。这个过程建立一个新索引并删除旧索引。无论旧索引中存在多少碎片，都会发生这种情况。这种处理方式意味着我们需要为新索引提供足够的空间。

MS SQL Server有两种不同类型的重建索引任务:联机和脱机。MS SQL Server的标准版只支持“离线”类型，企业版和开发版还支持“在线”类型。

条款在线和离线指示索引在重建操作期间是否可以保持可访问性。

离线重建

• 该操作移动页，使它们在物理上是连续的。
• 这个操作在单索引级别是原子的。这意味着如果我们中断进程，索引上的所有工作都将回滚并丢失。
• 在此过程期间，索引或表将被锁定，无法更新。

在线重建

• 联机索引重建与脱机过程完全相同。
• 主要区别在于在线重建锁定底层索引或表。

碎片整理的策略

让我们讨论一下如何获得碎片的程度，以及如何通过使用SQL Server Management Studio (SSMS)或Transact-SQL来重新组织或重建碎片索引。

破碎度

我们不能在知道之前开始碎片整理破碎程度。我们可以通过使用DMF得到这个信息sys.dm_db_index_physical_stats。

使用下面的语句，我们可以得到每个表的每个索引的碎片百分比:

在得到碎片百分比后，我们可以选择最佳的方法来整理索引。

微软在线图书提供以下价值:

• 如果碎片小于5%，则不需要进行碎片整理。
• 如果碎片率在5%到30%之间，我们应该重组索引。
• 如果破碎度大于30%，我们应该重建索引。

使用T-SQL命令进行碎片整理

假设我们有一个带有索引的OrdTes表IDX_OrdTes_DateDoc。

我们可以使用第一种T-SQL语法重新组织索引，并使用第二种语法重建索引(见下图)。

我们可以使用以下语法重新组织或重建表的所有索引。

使用SQL Server Management Studio (SSMS)进行碎片整理

以下是使用SSMS重组索引的步骤:

• 在对象资源管理器中，展开包含要在其上重组索引的表的数据库。
• 展开Tables文件夹。
• 展开要在其上重组索引的表。
• 右键单击Indexes文件夹并选择Reorganize All。
• 在“重新组织索引”对话框中，验证要重新组织的索引中是否包含正确的索引。要从“要重新组织的索引”网格中删除索引，请选择该索引，然后按Delete键。
• 选中Compact大对象列数据复选框，以指定对包含大对象(LOB)数据的所有页面也进行压缩。
• 单击OK。

下面是重建索引的步骤:

• 在对象资源管理器中，展开包含要在其上重建索引的表的数据库。
• 展开Tables文件夹。
• 展开要在其上重建索引的表。
• 展开Indexes文件夹。
• 右键单击索引并选择Rebuild。
• 在“重建索引”对话框中，确认“要重建的索引”网格中存在正确的索引，然后单击“确定”。
• 选中Compact大对象列数据复选框，以指定对包含大对象(LOB)数据的所有页面也进行压缩。
• 单击OK。

维护计划中的碎片整理索引

如前所述，必须始终有适当的碎片整理策略。有必要制定一个定期执行此活动的维护计划。维护计划不仅要整理索引，还要执行其他重要操作，如统计维护和数据库备份。

要创建新的维护计划，请在Management菜单中右键单击维护项。然后，选择“新建维护计划”项。

在维护计划中，我们可以添加各种任务。

要执行Reorganize索引任务，请将Reorganize索引任务拖放到屏幕中央。要执行重建索引任务，将重建索引任务拖放到屏幕中央。

一旦我们添加了一个或两个任务，我们就应该设置它们。

设置Reorganize任务。

• 指定在哪个数据库上执行该操作。
• 选择仅重组必要的数据库，或仅重组用户数据库或仅重组系统数据库。
• 选择仅在某些条件下重新组织索引，例如碎片超过一定百分比或页面超过一定数量。

要设置重建任务:

• 指定在哪个数据库上执行该操作。
• 选择仅重组必要的数据库，或仅重组用户数据库或仅重组系统数据库。
• 选择仅在某些条件下重建索引，例如超过一定百分比的碎片或超过一定数量的页面。

配置结束后，保存维护计划，并定时运行。

结论

在碎片整理过程中，可能会发生数据库损坏并返回错误。如果手动的错误恢复方法不起作用，我建议您尝试一下恒星修复MS SQL软件这个易于使用的软件允许通过解析SQL数据库的每个表和每个对象来完全恢复损坏的数据库。