数据库索引碎片如何整理_索引重建与碎片整理方法-SQL-PHP中文网

索引碎片整理需根据碎片程度选择REBUILD或REORGANIZE：REBUILD彻底但耗资源，适用于高碎片、可停机场景；REORGANIZE轻量在线，适合日常维护。

数据库索引碎片如何整理_索引重建与碎片整理方法

数据库索引碎片整理，核心在于理解其成因，并恰当运用

REBUILD

登录后复制

（重建）与

REORGANIZE

登录后复制

（重组）这两种主要方法。简单来说，重建更彻底，但资源消耗大；重组更轻量，可在线进行，适合日常维护。选择哪种方式，往往取决于碎片程度、业务对停机时间的容忍度以及数据库版本支持的功能。

解决方案

在我看来，处理数据库索引碎片，从来就不是一个“一劳永逸”的活儿，它更像是一种持续的、策略性的维护。当我们谈论碎片，其实是在说数据在物理存储上的不连续性，这直接影响到数据库读取数据的效率。

具体到方法，我们主要依赖两种操作：

1. 索引重建 (REBUILD): 这是一种“大刀阔斧”的策略。当你执行索引重建时，数据库会先创建一个全新的、物理上连续的索引结构，然后用这个新索引替换掉旧的。这个过程会彻底消除索引的所有碎片，并重新整理索引页，通常还能回收未使用的空间，从而使索引变得更紧凑、更高效。

我个人觉得，重建的优势在于它的彻底性。如果你发现某个索引的碎片率已经高到离谱，或者索引页密度（Page Density）非常低，那么重建通常是最好的选择。它能显著提升后续查询的性能，因为数据访问不再需要在磁盘上“跳来跳去”。

然而，重建并非没有代价。在大多数数据库系统中（尤其是SQL Server的Standard Edition），重建操作通常是离线的，这意味着在重建期间，相关的表或索引可能会被锁定，导致应用程序无法访问。即使是Enterprise Edition支持的在线重建，也会消耗大量的系统资源，比如CPU、内存和事务日志空间。所以，选择重建，你需要仔细评估系统的负载和可接受的停机时间窗口。

2. 索引重组 (REORGANIZE): 相比重建，重组就显得“温柔”许多。它不会创建一个全新的索引，而是在现有索引的物理结构上进行调整。简单来说，它会扫描索引页，然后将逻辑上连续但物理上不连续的页重新排列，使其在物理上尽可能连续。它还会压缩索引页，但通常不如重建那样彻底地回收空间。

我更倾向于将重组视为一种日常的、预防性的维护手段。它的最大优势在于，通常可以“在线”进行，对应用程序的影响非常小，几乎不会造成长时间的锁定。这意味着你可以在业务高峰期之外的任何时间点运行它，而不用担心会影响到用户体验。

当然，重组的缺点也很明显，它并不能完全消除所有类型的碎片，特别是那些由于页分裂（Page Splits）导致的内部碎片，效果就不如重建。如果碎片率非常高，重组可能需要运行很长时间，并且最终效果可能也不尽如人意。

所以，我的建议是，将两者结合起来：定期进行轻量级的重组来维持索引健康，而对于那些碎片严重、性能瓶颈明显的索引，则选择在维护窗口进行一次彻底的重建。

为什么数据库索引会产生碎片？

这个问题其实挺有意思的，它揭示了数据库底层存储的一些机制。在我看来，索引碎片就像是我们日常生活中的“杂物堆”，随着时间的推移，我们不断地往里面添加、取出、修改东西，自然而然地就变得凌乱不堪。

在数据库里，这个“凌乱”主要来源于以下几个方面：

首先，数据插入 (INSERT)。当我们往表中插入新行时，如果索引页没有足够的空间来容纳新数据，数据库就需要进行“页分裂”（Page Split）。想象一下，一个房间满了，为了放新东西，你得把这个房间的东西分一半到隔壁的新房间。这样，原本逻辑上挨着的两部分数据，物理上可能就隔开了，这就是碎片。

其次，数据更新 (UPDATE)。如果更新操作改变了索引列的值，并且这个新值导致数据行需要移动到不同的索引页，或者更新后的数据行大小发生了变化，也可能触发页分裂，或者在原位置留下空洞。如果更新后的数据比原来小，那么原来的空间就可能被浪费，形成内部碎片。

再者，数据删除 (DELETE)。删除数据行时，对应的索引条目也会被移除。但数据库通常不会立即回收这些被删除条目所占用的物理空间，而是将它们标记为“可用”。随着大量删除操作，索引页中会出现很多零散的空闲空间，这些空间可能不足以容纳新的数据行，导致即使有空闲空间，也无法被有效利用，形成“外部碎片”或“逻辑碎片”。

最后，填充因子 (Fill Factor) 的设置也会影响碎片产生的速度。填充因子决定了索引页在创建或重建时预留多少空闲空间。如果填充因子设置得太高（例如100%），意味着页几乎被填满，那么后续的插入和更新操作就更容易导致页分裂，碎片产生得更快。反之，如果填充因子设置得太低，虽然能延缓碎片产生，但会浪费更多的存储空间。这本身就是一个权衡。

这些操作日积月累，最终就导致了索引页在物理存储上的不连续，使得数据库在遍历索引时需要执行更多的I/O操作，从而降低查询性能。

索引重建 (REBUILD) 与索引重组 (REORGANIZE) 有何区别？何时选择？

这两种操作，虽然目的都是为了优化索引性能，但它们的实现机制和适用场景差异巨大。我常常把它们比作两种不同的“大扫除”方式。

纳米搜索

纳米搜索：360推出的新一代AI搜索引擎

查看详情

索引重建 (REBUILD) 更像是一次彻底的“装修”。它会：

创建新索引： 数据库会从头开始，根据表数据重新构建一个全新的索引结构。这个新索引在物理存储上是完全连续的，没有碎片。
回收空间： 彻底回收索引中所有未使用的空间，包括因删除操作留下的空洞，使得索引文件更紧凑。
更新统计信息： 自动更新索引的统计信息，这对查询优化器选择最佳执行计划至关重要。
性能提升显著： 由于物理连续性极佳，查询性能提升通常非常明显。

何时选择 REBUILD？

高碎片率： 当索引的
```
avg_fragmentation_in_percent
```
登录后复制
（平均碎片百分比）超过一个较高阈值（比如30%以上）时，重建是首选。
低页密度： 如果索引页的密度很低，说明有很多空闲空间没有被有效利用，重建可以有效压缩空间。
存在性能瓶颈： 当你怀疑某个特定查询的性能问题与索引碎片高度相关时。
有足够的维护窗口： 由于重建通常是离线的（或在线但资源消耗大），你需要有足够长的维护时间窗口来执行。
数据库版本支持： 如果你的数据库是企业版，并且支持在线重建，那么在很多情况下都可以考虑重建。

索引重组 (REORGANIZE) 则更像是一次“整理房间”。它会：

原地整理： 不会创建新索引，而是在现有索引页内部进行整理和排序。它会移动物理上不连续的索引页，使其尽可能连续。
部分回收空间： 它可以压缩索引页，但不如重建那样彻底地回收所有未使用的空间。
在线操作： 大多数数据库系统都支持在线重组，这意味着在操作期间，索引仍然可用，对应用程序的影响最小。
资源消耗低： 相比重建，重组的CPU、内存和I/O消耗通常要小得多。

何时选择 REORGANIZE？

中等碎片率： 当索引的
```
avg_fragmentation_in_percent
```
登录后复制
在较低到中等范围（比如5%到30%之间）时，重组是一个很好的选择。
无可用维护窗口： 如果业务对停机时间非常敏感，无法进行离线操作，那么在线的重组是唯一的选择。
日常维护： 作为一种预防性措施，定期对所有索引进行重组，可以有效延缓高碎片率的出现。
资源受限： 当系统资源有限，无法承受重建带来的高负载时。

简而言之，我的经验是：对于那些“病入膏肓”的索引，用重建；对于那些“亚健康”的索引，用重组来维持。两者结合，才能达到最佳的维护效果。

如何判断数据库索引是否需要整理？

判断索引是否需要整理，不能拍脑袋，得看数据。毕竟，我们做这些操作是为了提升性能，而不是为了整理而整理。我通常会关注几个关键指标，并结合实际的性能表现来做决策。

最直接的方法是查询数据库提供的系统视图或动态管理视图（DMVs），它们能告诉我们索引的物理状态。以SQL Server为例，我们可以使用

sys.dm_db_index_physical_stats

登录后复制

这个DMV。

以下是一个我常用的查询示例，它可以帮助我们快速评估数据库中索引的碎片情况：

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent, -- 平均碎片百分比
    ips.page_count,                   -- 索引页数量
    ips.avg_page_space_used_in_percent -- 平均页空间使用百分比
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ips
JOIN
    sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE
    ips.avg_fragmentation_in_percent > 5 -- 只看碎片率超过5%的索引
    AND ips.page_count > 100             -- 排除页数过少的索引（碎片影响不大）
ORDER BY
    ips.avg_fragmentation_in_percent DESC;

登录后复制

关键指标解读：

```
avg_fragmentation_in_percent
```
登录后复制
(平均碎片百分比):
- 这是最核心的指标。它表示索引页的逻辑顺序与物理顺序不匹配的程度。
- 我的经验是：
  - 低于5%： 通常不需要任何操作，碎片影响微乎其微。
  - 5% - 30%： 可以考虑进行
```
REORGANIZE
```
    登录后复制
    操作。它对性能有轻微影响，但可以在线完成，风险较低。
  - 高于30%： 强烈建议进行
```
REBUILD
```
    登录后复制
    操作。此时碎片已经很严重，对查询性能的影响可能非常大。
```
page_count
```
登录后复制
(索引页数量):
- 这个指标也很重要。如果一个索引只有很少的页（比如几十页），即使碎片率很高，其对整体性能的影响也可能非常小。毕竟，数据库读取几十个不连续的页和读取几万个不连续的页，效率差异是巨大的。我通常会设置一个阈值，比如
```
page_count > 100
```
  登录后复制
  或
```
page_count > 1000
```
  登录后复制
  ，只关注那些真正“大”的索引。
```
avg_page_space_used_in_percent
```
登录后复制
(平均页空间使用百分比):
- 这个指标反映了索引页的填充程度。如果这个值很低（比如低于70%），说明索引页内部有很多空闲空间，可能是因为大量删除操作，或者填充因子设置过低。这种情况下，
```
REBUILD
```
  登录后复制
  可以有效回收这些空间，让索引更紧凑。