如何检测和测量 SQL Server 中的索引碎片？

已发表: 2023-06-15

今天，我们将探索 SQL Server ( wiki ) 的一个迷人方面，这是一个用于管理数据库的系统。我们今天的主题是“SQL Server 中的索引碎片”。我们将学习如何检测和测量它。别担心，它并不像听起来那么复杂！

让我们考虑一下您最喜欢的歌曲播放列表。歌曲按特定顺序排列，因此您可以随心所欲地欣赏它们。但是，如果随着时间的推移，一些歌曲被删除，新歌曲被添加，其他歌曲被移动怎么办？您的播放列表的顺序被打乱了，对吗？这类似于我们谈论索引碎片时数据库中发生的情况。

在数据库中，数据以特定方式组织，以便快速轻松地访问。但随着数据的添加、更新或删除，此顺序可能会被打乱，从而导致我们所说的“索引碎片”。这会减慢数据库的速度，就像随机播放列表会破坏您的聆听体验一样。

在本文中，我们将学习如何发现这种“洗牌”何时发生，以及如何衡量数据的“洗牌”程度。这就像是一名 DJ，但对于数据库而言！所以，准备好旋转甲板，让我们开始吧！

目录显示

了解索引碎片
检测索引碎片
衡量指标碎片化
解释结果
结论

了解索引碎片

好吧，让我们更深入地了解索引碎片到底是什么。还记得我们的播放列表示例吗？就像播放列表中的歌曲一样，数据库中的数据以特定顺序存储。此顺序使用称为“索引”的东西来维护，它就像一张地图或指南，用于指示所有内容的存储位置。

现在，当我们添加新歌曲（或数据）、删除一些歌曲或四处移动它们时，我们的播放列表（或索引）可能会被打乱或分散。在数据库术语中，我们称之为“索引碎片”。

有两种类型的碎片：内部碎片和外部碎片。

当数据页面中有空白空间时，就会发生内部碎片，比如我们的播放列表中有空白曲目。
另一方面，外部碎片是指页面的逻辑顺序与其物理顺序不匹配的情况，例如当我们的歌曲不符合我们希望的顺序时。

现在，我们为什么要关心索引碎片？好吧，当索引变得碎片化时，SQL Server 必须更加努力地工作才能找到它需要的数据。这就像尝试按特定顺序收听随机播放的播放列表——需要更多的努力，对吧？同样，碎片化的索引会降低数据库的性能，使数据检索速度变慢且效率降低。

在接下来的部分中，我们将学习如何检测这种碎片化以及我们可以做些什么来修复它。这就像学习如何组织我们的播放列表，以便我们可以按照自己喜欢的方式欣赏音乐！那么，让我们继续我们旅程的下一部分。

为您推荐： SQL 注入：它仍然是一种威胁吗？你怎么能避免它？

检测索引碎片

现在我们了解了什么是索引碎片，让我们谈谈如何检测它。 SQL Server 为我们提供了一些方便的工具和命令来执行此操作。这就像有一个特殊的应用程序，可以在我们的播放列表被随机播放并需要重新组织时告诉我们。

我们在 SQL Server 中使用的主要工具是一个名为sys.dm_db_index_physical_stats的系统函数。满嘴的，不是吗？但别担心，它并不像听起来那么复杂。这个函数就像一个侦探，可以检查我们的数据库并告诉我们索引的碎片化程度。下面是我们如何使用它：

1.选择数据库和表：

首先，我们告诉函数我们要检查哪个数据库和表。这就像选择我们要检查的播放列表一样。

2.运行功能：

然后，我们运行该函数。这是通过执行如下所示的 SQL 命令来完成的：

 SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabaseName'), OBJECT_ID(N'YourTableName'), NULL, NULL, 'DETAILED');

在此命令中，将“YourDatabaseName”和“YourTableName”替换为您的数据库和表的名称。

3. 阅读结果：

该函数将返回大量信息，但我们感兴趣的关键是一个名为avg_fragmentation_in_percent的值。这告诉我们索引的碎片化程度（百分比）。这就像告诉我们播放列表的混乱程度。

衡量指标碎片化

就像我们测量自己的身高或体重一样，我们也可以测量我们的指数的碎片化程度。在 SQL Server 中，我们使用一些关键指标来做到这一点。可以把它想象成衡量我们的播放列表中有多少是乱序的。我们是这样做的：

了解指标：

我们使用的主要指标称为avg_fragmentation_in_percent 。这告诉我们索引中逻辑碎片（无序页面）的百分比。这就像告诉我们播放列表中有多少百分比被随机播放一样。

另一个重要指标是page_count 。这告诉我们索引中的索引或数据页的总数。将其视为我们播放列表中的歌曲总数。

运行命令：

我们通过运行sys.dm_db_index_physical_stats函数来测量索引碎片，就像我们检测碎片一样。但是这一次，我们要注意avg_fragmentation_in_percent和page_count值。

这是命令：

 SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabaseName'), OBJECT_ID(N'YourTableName'), NULL, NULL, 'DETAILED');

请记住将“YourDatabaseName”和“YourTableName”替换为您的数据库和表的名称。以下是您可能会看到的示例，为简单起见，仅包含几列：

在这张简化表中：

object_id 是表的 ID。
index_id 是索引的 ID。
index_type_desc 描述了索引的类型（例如，“CLUSTERED INDEX”）。
avg_fragmentation_in_percent 是索引的平均碎片，以百分比表示。
fragment_count 是索引中的片段数（连续的页面组）。
page_count 是索引中的总页数。

这张表让您清楚地了解索引的碎片状态。

解释结果：

如果avg_fragmentation_in_percent小于 5%，那么我们的索引状态非常好——就像一个播放列表，只是稍微打乱了顺序。如果它介于 5% 和 30% 之间，我们的指数可能需要进行一些重组。如果超过 30%，我们可能需要完全重建我们的索引——比如从头开始重新排序我们的播放列表。

page_count值告诉我们索引（或播放列表）有多大。如果它是一个小数字，我们可能不需要太担心碎片化。但如果它很大，碎片化确实会减慢速度，我们绝对应该采取措施修复它。

解释结果

请记住，我们正在查看一个表，该表告诉我们有关索引状态的信息，有点像我们数据库的健康检查报告。

1. 了解碎片级别

avg_fragmentation_in_percent列就像我们索引的心跳。它告诉我们我们的索引是多么分散或混乱。一个较低的数字，如 0 或 1%，意味着我们的索引状态良好——它就像一个保存完好的图书馆一样井井有条。但是一个很高的数字，比如 60% 或 70%，意味着我们的索引非常分散——它更像是一个凌乱的房间而不是一个整洁的图书馆。

2. 片段数和页数

fragment_count和page_count列为我们提供了有关索引的更多详细信息。您可以将“片段”想象成一本书的一部分，而“页面”就像那本书中的页面。如果我们有很多碎片，这意味着我们的书被分成了很多部分，这会使快速阅读变得更加困难。如果我们有很多页，这意味着我们的书（或者在这种情况下，我们的索引）非常大。

3. 何时采取行动

那么，我们什么时候应该开始担心碎片化呢？那么，作为一般规则，如果avg_fragmentation_in_percent小于 5%，我们的索引是健康的，我们不需要做任何事情。如果它在 5% 到 30% 之间，我们的指数可能需要稍微整理一下，有点像清理稍微凌乱的房间。如果它超过 30%，我们的索引就会严重分散，我们需要采取行动对其进行重组，就像如果我们的房间很乱，我们需要进行一次大扫除一样。

请记住，这些只是指南。确切的数字可能因数据库的特定需求和性能而异。但是通过了解这些结果，您可以让您的索引和数据库顺利运行。

您可能还喜欢：如何在 Asp.Net MVC 开发中使用 SQL 的 GeoGraphy 数据类型。

结论

正如组织良好的播放列表可以轻松查找和播放您喜欢的歌曲一样，组织良好的数据库也可以使 SQL Server 轻松查找和检索所需的数据。这就是检测和测量索引碎片如此重要的原因——它可以帮助我们保持数据库平稳高效地运行。

在本文中，我们了解到索引碎片有点像随机播放列表。当我们的索引碎片化或混乱时，SQL Server 必须更加努力地工作才能找到它需要的数据。这会减慢我们的查询速度并降低我们的数据库效率。

但是通过使用我们讨论过的工具和命令，我们可以检测和测量索引碎片。这使我们能够识别任何问题并采取措施解决它们，无论是通过重新组织我们的索引还是完全重建它们。这有点像对随机播放列表进行重新排序——通过将所有内容放回原位，我们可以更轻松地找到我们要查找的内容。

最后，维护我们的索引是维护我们数据库的关键部分。通过定期检查和解决索引碎片，我们可以确保我们的数据库继续以最佳状态运行。

如果您有兴趣了解有关 SQL Server 中索引碎片的更多信息，我建议您查看这篇深入的文章。对于希望深入研究该主题的任何人来说，这是一个很好的资源。

请记住，就像保持播放列表有序一样，维护索引是一项持续的任务。但是有了正确的知识和工具，这项任务可以在数据库性能方面获得巨大回报。快乐索引！