SQL Server索引內部結構：SQL Server索引的階梯層級10

標籤：style   簡單的   ima   個數   div   方法   函數   nbsp   建立   

作者David Durant，2012年1月20日 該系列 本文是“Stairway系列：SQL Server索引的階梯”的一部分 索引是資料庫設計的基礎，並告訴開發人員使用資料庫關於設計者的意圖。不幸的是，當效能問題出現時，索引往往被添加為事後考慮。這裡最後是一個簡單的系列文章，應該使他們快速地使任何資料庫專業人員“快速” 在之前的水平上，我們採取了合理的方法來指標，重點是他們能為我們做些什麼。現在是時候採取物理方法，檢查指標的內部結構;瞭解索引的內部特性導致了對索引開銷的理解。只有通過瞭解指數結構，以及如何維持指數結構，才能瞭解和最大限度地減少指數創造，變動和消除的成本;和行插入，更新和刪除。 因此，從這個層面開始，我們把重點放在包括指標成本和指標收益上。畢竟，最小化成本是最大化收益的一部分。並最大化您的指標的好處是這個階梯是全部。 葉和非葉水平 任何指標的結構都由葉片和非葉片組成。儘管我們從來沒有明確表示過，但以前的所有層級都集中在索引的葉級上。因此，叢集索引的葉級是表本身;每個分葉層級條目是該表的一行。對於非叢集索引，每個行中包含一個條目的分葉層級（除了已過濾的索引）;每個條目由索引鍵列，可選的包含列和書籤組成，這是叢集索引鍵列或RID（行ID）值。 索引條目也被稱為索引行;無論是表行（聚簇索引分葉層級條目），是指表行（非聚簇索引分葉層級）還是指向較低層級（非分葉層級）的頁面。 非分葉層級是在葉級上構建的結構，它使SQL Server能夠： ?維護索引鍵序列中的索引條目。 ?根據索引索引值快速找到分葉層級的行。 在1級中，我們使用電話簿作為比喻來協助解釋索引的好處。我們正在尋找“Meyer，Helen”的電話簿使用者知道，入口將接近任何已排序的姓氏列表的中間，並直接跳到白頁的中間以開始搜尋。但是，SQL Server沒有英文姓氏或其他資料的內在知識。也不會知道哪個頁面是“中間”頁面，除非它從頭到尾遍曆整個索引。所以SQL Server在索引中建立了一些額外的結構。 非分葉層級 這個額外的結構稱為索引的非分葉層級或節點層級;並被認為是建立在葉級的頂部，而不管其頁面的物理位置在哪裡。它的目的是為SQL Server提供每個索引的單個頁面進入點，以及從該頁面到包含任何給定搜尋索引鍵值的頁面的簡短遍曆。 索引中的每個頁面（無論其層級）都包含索引行或條目。在葉級頁面中，正如我們一再看到的，每個條目都指向一個表行或者是表行。所以如果表中包含10億行，索引的葉級將包含10億條目。 在葉級以上的層級，即最低的非葉級;每個入口指向一個葉級頁面。如果我們的10億條目索引平均每頁有100個條目，這對於其搜尋索引鍵由幾個數字，日期和代碼列組成的索引是一個現實的數字;那麼葉級將包含1,000,000,000 / 100 = 10,000,000個頁面。反過來，最低的非葉級將包含10,000,000個條目，每個條目指向葉級頁面，並且將跨越100,000個頁面。 每個較高的非分葉層級的頁面的條目均指向下一級的頁面。因此，我們下一個較高的非葉級將包含100,000個條目，並且大小為1,000頁。以上層級將包含1,000個條目，並且大小為10頁;上面那個只包含十個條目的條目就只有一個頁面;這就是停止的地方。 位於索引頂部的獨立頁面稱為根頁面。位於根頁面之下和葉級之上的索引的層級被稱為中間層級。層級的編號從零開始，從葉級向上工作。因此，最低的中間層級總是等級1。 非分葉層級條目僅包含索引鍵列和指向較低層級頁面的指標。 包含的列僅存在於分葉層級條目中; 它們不在非分葉層級條目中進行。 除了根頁面之外，索引中的每個頁面都包含兩個額外的指標。 這些指標在索引序列中指向下一頁和前一頁，處於同一層級。 產生的雙向頁面鏈使SQL Server能夠以升序或降序掃描任何層級的頁面。 一個簡單的例子 下面的圖1所示的簡單圖協助說明了這種樹狀結構的索引。 此圖表示使用以下SQL在理論Personnel.Employee表的LastName / FirstName列上建立的索引：

CREATE NONCLUSTERED INDEX IX_Full_Name

ON Personnel.Employee

(

LastName,

FirstName,

)

GO

圖表注釋： 指向頁面的指標由資料庫檔案編號和頁碼組成。 因此，指標值為5：4567指向資料庫檔案＃5的第4567頁。 大部分樣本值都來自AdventureWorks資料庫中的Person.Contact表。 為了說明的目的，還添加了其他一些內容。 卡爾·奧爾森是樣本中最受歡迎的名字。 有很多Karl Olsens，他們的條目跨越了整個中級索引頁面。

圖1 - 索引的垂直切片 為了清晰起見，圖表與以下方面的典型索引不同： 典型索引中每頁的條目數量將大於圖中所示的數量，因此，除根之外的每個層級的頁面數量將大於所示的數量。尤其是，葉級將比我們的空間限制圖中顯示的要多得多。 實際索引的條目在頁面上不排序。這是頁面的條目位移指標，提供順序訪問條目。 （有關位移指標的更多資訊，請參

索引的物理順序和邏輯順序之間的相關性往往比圖中所示的要高。索引的物理和邏輯順序之間缺乏相關性被稱為外部片段，在第11級 - 片段中討論。 如前所述，一個指數可以有多個中間水平。 就好像我們的白頁使用者正在尋找海倫·邁耶，開啟電話簿，發現第一頁，只有第一頁是粉紅色的。在粉色頁面的排序條目列表中，有一個表示“對於”費爾南德斯，塞爾達“和”奧爾森，卡爾“之間的名字見藍色頁面5：431。當我們的使用者轉到藍頁5：431時，該頁面上的一個條目說：“Kumar，Kevin和Nara，Alison之間的名字見第5頁：2006”。粉紅色的頁面對應於根，藍色頁面對應中介層次，白色頁面是葉子。 指數深度 根頁面的位置與索引的其他資訊一起儲存在系統資料表中。每當SQL Server需要訪問與索引索引值相匹配的索引條目時，它都會從根頁面開始，並在索引中的每個層級處理一個頁面，直到到達包含該索引鍵的條目的葉級頁面。在我們的十億行表中的例子中，五個頁面讀取將SQL Server從根頁面轉移到葉級頁面及其所需的條目;在我們的圖解例子中，三個閱讀就足夠了。在叢集索引中，該分葉層級條目將是實際的資料行;在非叢集索引中，此條目將包含聚簇索引鍵列或RID值。 索引的級數或深度取決於索引鍵的大小和條目數。在AdventureWorks資料庫中，沒有索引的深度大於三。在具有非常大的表格或非常寬的索引鍵列的資料庫中，可能會出現6或更大的深度。 sys.dm_db_index_physical_stats函數提供有關索引的資訊，包括索引類型，深度和大小。這是一個可以查詢的資料表值函式。清單1中顯示的樣本返回SalesOrderDetailtable的所有索引的摘要資訊。

 

SELECT OBJECT_NAME(P.OBJECT_ID) AS ‘Table‘

 

     , I.name AS ‘Index‘

 

     , P.index_id AS ‘IndexID‘

 

     , P.index_type_desc

 

     , P.index_depth

 

     , P.page_count

 

  FROM sys.dm_db_index_physical_stats (DB_ID(),

 

                                       OBJECT_ID(‘Sales.SalesOrderDetail‘),

 

                                       NULL, NULL, NULL) P

 

  JOIN sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID

 

                    AND I.index_id = P.index_id;

 

清單1：查詢sys.dm_db_index_physical_stats函數結果2所示。

閱第4級 - 頁面和範圍。）

 

 

圖2：查詢sys.dm_db_index_physical_stats函數的結果相反，清單2中顯示的代碼請求特定索引的詳細資料，即SalesOrderDetail表的表的uniqueidentifier列上的非叢集索引。 它會為每個索引級返回一行，3所示。 清單2：查詢sys.dm_db_index_physical_stats擷取詳細資料。

 

SELECT OBJECT_NAME(P.OBJECT_ID) AS ‘Table‘

 

     , I.name AS ‘Index‘

 

     , P.index_id AS ‘IndexID‘

 

     , P.index_type_desc

 

     , P.index_level  

 

     , P.page_count

 

  FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(‘Sales.SalesOrderDetail‘), 2, NULL, ‘DETAILED‘) P

 

  JOIN sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID

 

                    AND I.index_id = P.index_id;

 

圖3：查詢sys.dm_db_index_physical_stats擷取詳細資料的結果

從圖3的結果可以看出： ?這個指數的葉級分布在407頁。 ?唯一的中間層級只需要兩頁。 ?根級始終是一個頁面。 索引的非葉部分的大小通常是葉級的大小的十分之一至二百分之一;取決於哪些列包括搜尋索引鍵，書籤的大小，以及哪些（如果有的話）被包括的列被指定。換句話說，相對而言，指數非常寬泛且很短。這與大多數索引樣本圖不同，比1中的索引樣本圖，索引圖往往比較高而且很窄。 請記住，包含的列僅適用於非聚簇索引，它們只出現在分葉層級條目中;它們從較進階別的條目中被省略，這就是為什麼它們不添加到非分葉層級的大小。 由於聚簇索引的分葉層級是該表的資料行，因此只有聚簇索引的非葉子部分是附加資訊，需要額外的儲存空間。無論索引是否建立，資料行都會存在。因此，建立叢集索引可能需要時間並消耗資源;但是當建立完成時，資料庫中消耗的空間很少。 結論 索引的結構使SQL Server能夠快速存取特定索引索引值的任何條目。一旦找到該條目，SQL Server就可以： ?訪問該條目的行。 ?從該點開始以升序或降序的順序遍曆索引。 這種索引樹結構已經使用了很長時間，甚至比關聯式資料庫還要長，並且隨著時間的推移已經證明了它自己。   本文是SQL Server索引階梯的一部分

SQL Server索引內部結構：SQL Server索引的階梯層級10