SQL Server中的索引內(nèi)部結(jié)構(gòu):到SQL Server索引級別10的階梯。
大衛(wèi)?杜蘭特2012/01/20
該系列
本文是樓梯系列的一部分:SQL Server索引的階梯。
索引是數(shù)據(jù)庫設(shè)計的基礎(chǔ)，并且告訴開發(fā)人員使用數(shù)據(jù)庫對設(shè)計人員的意圖有很大的影響。不幸的是，當性能問題出現(xiàn)時，索引常常被添加為事后考慮。這里最后是一個簡單的系列文章，應(yīng)該讓任何數(shù)據(jù)庫專業(yè)人員迅速“跟上”他們。
在以前的水平上，我們對索引采取了一種合乎邏輯的方法，專注于他們能為我們做什么。現(xiàn)在是時候采取物理方法，檢查指標的內(nèi)部結(jié)構(gòu)了;為了理解索引內(nèi)部，可以理解索引開銷。只有了解索引結(jié)構(gòu)，以及如何維護它，才能理解和最小化索引創(chuàng)建、更改和刪除的成本;以及行插入、更新和刪除。
因此，從這個層次開始，我們將重點擴展到包括索引的成本，以及索引的好處。畢竟，將成本最小化是利益最大化的一部分;最大化你的索引的好處就是這個樓梯的目的。
葉和葉水平
任何指標的結(jié)構(gòu)都是由葉級和非葉級組成。雖然我們從未明確地說過，所有以前的水平都集中在一個指數(shù)的葉級上。因此，它是一個聚集索引的葉級，即表本身;每個葉級條目都是表的一行。對于非聚集索引，它是每一行包含一個條目的葉級(過濾后的索引除外);每個條目由索引鍵列組成，可選的包含列和書簽，它要么是聚集索引鍵列，要么是RID(行ID)值。
索引項也稱為索引行;無論它是表行(聚集索引葉級別條目)，都是指表行(非聚集索引頁級)，或指向較低級別的頁面(非葉級)。
非葉級是構(gòu)建在葉級之上的結(jié)構(gòu)，它使SQL Server能夠:
在索引鍵序列中維護索引的條目。
快速查找給定索引鍵值的葉級行。
在第1級，我們使用電話簿作為類比來解釋索引的好處。我們的電話簿用戶，正在尋找“邁耶，海倫”，知道條目將會在任何排序的姓氏列表中間，然后直接跳到中間的白色頁面開始搜索。然而，SQL Server并沒有這樣的內(nèi)部知識，即英語的姓氏或其他任何數(shù)據(jù)。它也不知道哪個頁面是“中間”頁面，除非它從頭到尾遍歷整個索引。因此，SQL Server為索引構(gòu)建了一些額外的結(jié)構(gòu)。

非葉水平
這種附加結(jié)構(gòu)稱為索引的非葉級或節(jié)點級別;它被認為是建立在葉層之上，不管它的頁面在哪里。它的目的是為SQL Server提供每個索引的單個頁面入口點，以及從該頁面到包含任何給定搜索鍵值的頁面的短遍歷。
索引中的每個頁面，無論其級別如何，都包含索引行或條目。在頁級頁面中，正如我們反復看到的，每個條目都指向一個表行或者是表行。因此，如果表包含10億行，索引的葉級別將包含10億個條目。
在葉級以上的水平，即最低的非葉層;每個條目指向一個葉級頁面。如果我們的10億條目索引平均每個頁面100個條目，對于一個索引的搜索鍵包含幾個數(shù)字、日期和代碼列，這是一個現(xiàn)實的數(shù)字;然后，葉級將包含1000,000,000 / 100 = 10,000,000頁。反過來，最低的非葉級將包含10,000,000個條目，每個條目指向一個葉級頁面，并且將跨越100,000頁。
每一個較高的非葉級別都有頁面，每個頁面的條目都指向下一級的頁面。因此，我們的下一個更高的非葉級別將包含100,000個條目，并有1000頁大小。上面的級別包含1000個條目，大小為10頁;上面的那一頁只有一頁上的10個條目;這就是它停止的地方。
位于索引頂部的唯一頁面稱為根頁面。在根頁級和葉級以上的索引的級別稱為中間層。等級的編號從零開始，從葉級開始向上。因此，最低的中級水平總是1級。
非葉級條目只包含索引鍵列和指向較低級頁面的指針。包含的列只存在于葉級條目中;它們不在非葉級條目中進行。
除了根頁面之外，索引中的每個頁面都包含兩個額外的指針。這些指針指向下一頁和前一頁，在索引序列中，在同一級別上。由此產(chǎn)生的雙向頁面鏈使SQL Server能夠以升序或降序的順序掃描任何級別的頁面。
一個簡單的例子
下面的圖1所示的簡單圖幫助說明了這種樹狀結(jié)構(gòu)的索引。此圖表示在理論人員的LastName / FirstName列上創(chuàng)建的索引。Employee表，使用以下SQL:

CREATE NONCLUSTERED INDEX IX_Full_Name ON Personnel.Employee ( LastName, FirstName, ) GO

圖注:
指向頁面的指針包括數(shù)據(jù)庫文件號和頁碼。因此，指針值為5:4567指向數(shù)據(jù)庫文件#5的4567頁。
大多數(shù)樣本值都是從這個人那里取來的。在AdventureWorksdatabase中的聯(lián)系人表。為了說明目的，還添加了一些其他的內(nèi)容。
卡爾·奧爾森是樣本中最受歡迎的名字。有這么多卡爾·奧爾森，他們的條目跨越了整個中間水平的索引頁。

圖1 -索引的垂直切片。
為了清晰和說明，圖表不同于一個典型的指數(shù):
一個典型索引中的每個頁面的條目數(shù)將大于圖中所示的數(shù)字，因此，除了根之外，每個級別的頁面數(shù)都大于顯示的頁數(shù)。葉級，特別是，將有遠多于可以顯示在我們的空間有限圖。
實際索引的條目沒有在頁面上進行排序。這是頁面的條目偏移指針，它提供對條目的排序訪問。(請參閱第4級-頁面和擴展，以獲得關(guān)于偏移指針的更多信息。)
通常情況下，索引的物理和邏輯序列之間的相關(guān)性比圖表中顯示的要多。該索引的物理和邏輯序列之間缺乏相關(guān)性稱為外部碎片，并在第11層中討論。
如前所述，索引可以有多個中間級別。
就好像我們的白頁用戶在尋找海倫·邁耶，打開電話簿，發(fā)現(xiàn)第一頁，也只有第一頁，是粉紅色的。在“粉紅頁”的排序條目列表中，有一個人說:“在費爾南德斯、塞爾達和奧爾森之間的名字”，卡爾“見藍頁5:431”。當我們的用戶瀏覽到blue page 5:431時，頁面上的一個條目寫道:“在庫瑪爾、凱文”和“奈良”之間的名字，參見“白色頁5:2006”。粉色的頁面對應(yīng)的是根，藍色的頁面是中間的，白色的頁面是葉子。
指數(shù)深度
根頁面的位置存儲在系統(tǒng)表中，以及其他關(guān)于索引的信息。每當SQL Server需要訪問與索引鍵值相匹配的索引項時，它就從根頁面開始，并在索引中每一級的一個頁面上工作，直到到達包含該索引鍵的條目的葉級頁面為止。在我們的10億行表示例中，5頁的讀取將把SQL Server從根頁面帶到葉級頁面及其所需的條目;在我們的圖表示例中，三個讀取就足夠了。在聚集索引中，這個葉級別條目將是實際的數(shù)據(jù)行;在非聚集索引中，該條目將包含聚集索引鍵列或RID值。
索引的級別或深度的數(shù)量取決于索引鍵的大小和條目的數(shù)量。在AdventureWorks數(shù)據(jù)庫中，沒有索引的深度大于3。在具有非常大的表或非常寬的索引鍵列的數(shù)據(jù)庫中，可能會出現(xiàn)6個或更大的深度。
sys。dm_db_index_physical_statsfunction提供了索引類型、深度和大小等索引的信息。它是一個可查詢的表值函數(shù)。清單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) PJOIN sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID AND I.index_id = P.index_id;清單1:查詢sys。dm_db_index_physical_stats函數(shù)，如圖2所示。

圖2:查詢系統(tǒng)的結(jié)果。dm_db_index_physical_stats函數(shù)
相反，清單2中所示的代碼請求特定索引的詳細信息，這是表中SalesOrderDetail表的惟一標識符列的非聚集索引。它返回每個索引級別的一行，如圖3所示。
清單2:查詢系統(tǒng)。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') PJOIN sys.indexes I ON I.OBJECT_ID = P.OBJECT_ID AND I.index_id = P.index_id;

圖3:查詢系統(tǒng)的結(jié)果。dm_db_index_physical_stats為詳細信息

從圖3所示的結(jié)果中，我們可以看到:
該指數(shù)的葉級分布在407頁。
唯一的中級水平只需要兩頁。
根級別始終是一個頁面。
索引的非葉部分的大小通常是葉級的十分之一到百分之一。根據(jù)哪些列組成了搜索鍵、書簽的大小，以及是否指定了包含的列。換句話說，相對而言，索引是非常寬且非常短的。這與大多數(shù)的索引示例圖不同，如圖1中所示，它通常是高而窄的。
記住，包含的列只適用于非聚集索引，它們只出現(xiàn)在葉級條目中;它們從較高級別的條目中刪除，這就是為什么它們不添加到非葉級別的大小。
由于聚集索引的葉級別是表的數(shù)據(jù)行，所以只有聚集索引的非葉部分是附加信息，需要額外的存儲。數(shù)據(jù)行將存在是否創(chuàng)建索引。因此，創(chuàng)建聚集索引可能需要時間和消耗資源;但是當創(chuàng)建完成時，數(shù)據(jù)庫中消耗的額外空間很少。
結(jié)論
索引結(jié)構(gòu)使SQL Server能夠快速訪問特定索引鍵值的任何條目。一旦找到該條目，SQL Server可以:
訪問該條目的行。
從這一點通過升序或降序遍歷索引。
這種索引樹結(jié)構(gòu)已經(jīng)使用了很長時間，甚至比關(guān)系數(shù)據(jù)庫還要長，而且隨著時間的推移已經(jīng)證明了它自己。

轉(zhuǎn)載于:https://www.cnblogs.com/yyyz516/p/8257247.html

總結(jié)

以上是生活随笔為你收集整理的翻译：SQL Server中的索引内部结构:到SQL Server索引级别10的阶梯。的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。