如何檢測和測量 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 中索引碎片的更多信息，我建議您查看這篇深入的文章。對於希望深入研究該主題的任何人來說，這是一個很好的資源。

請記住，就像保持播放列表有序一樣，維護索引是一項持續的任務。但是有了正確的知識和工具，這項任務可以在數據庫性能方面獲得巨大回報。快樂索引！