SQL Server 索引和表體系結構（非聚集索引）

pursuer.chen發表於2014-06-12
SQLServer索引

非聚集索引

概述

     對於非聚集索引，涉及的資訊要比聚集索引更多一些，由於整個篇幅比較大涉及接下來的要寫的“包含列的索引”，“索引碎片”等一些知識點，可能要結合起來閱讀理解起來要更容易一些。非聚集索引和聚集索引一樣都是B-樹結構，但是非聚集索引不改變資料的儲存方式，所以一個表允許建多個非聚集索引；非聚集索引的葉層是由索引頁而不是由資料頁組成，索引行包含索引鍵值和指向表資料儲存位置的行定位器，

既可以使用聚集索引來為表或檢視定義非聚集索引，也可以根據堆來定義非聚集索引。非聚集索引中的每個索引行都包含非聚集鍵值和行定位符。此定位符指向聚集索引或堆中包含該鍵值的資料行。

正文

  • 單個分割槽中的非聚集索引結構

非聚集索引 Index_id>1 可以結合語句查詢

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number,p.rows,
x.first_page,x.root_page,x.first_iam_page,x.filegroup_id,x.total_pages,x.used_pages
FROM sys.allocation_units AS au
    JOIN sys.partitions AS p ON au.container_id = p.partition_id
    JOIN sys.objects AS o ON p.object_id = o.object_id
    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
    join sys.system_internals_allocation_units as x on au.container_id=x.container_id  
ORDER BY o.name, p.index_id;

 

非聚集索引行中的行定位器或是指向行的指標，或是行的聚集索引鍵，如下所述：

  • 如果表是堆（意味著該表沒有聚集索引），則行定位器是指向行的指標。該指標由檔案識別符號 (ID)、頁碼和頁上的行數生成。整個指標稱為行 ID (RID)。

如果表有聚集索引或索引檢視上有聚集索引，則行定位器是行的聚集索引鍵。如果聚集索引不是唯一的索引，SQL Server 將新增在內部生成的值（稱為唯一值）以使所有重複鍵唯一。此四位元組的值對於使用者不可見。僅當需要使聚集鍵唯一以用於非聚集索引中時，才新增該值。SQL Server 通過使用儲存在非聚集索引的葉行內的聚集索引鍵搜尋聚集索引來檢索資料行。

  • 非聚集索引與聚集索引相比：

A）葉子結點並非資料結點
B）葉子結點為每一真正的資料行儲存一個“鍵-指標”對
C）葉子結點中還儲存了一個指標偏移量，根據頁指標及指標偏移量可以定位到具體的資料行。
D）類似的，在除葉結點外的其它索引結點，儲存的也是類似的內容，只不過它是指向下一級的索引頁的。

 

聚集索引是一種稀疏索引，資料頁上一級的索引頁儲存的是頁指標，而不是行指標。而對於非聚集索引，則是密集索引，在資料頁的上一級索引頁它為每一個資料行儲存一條索引記錄。

注意：上圖中的資料頁是聚集索引或者堆資料行，而不是非聚集索引的資料頁，在非聚集索引中不存在資料頁，非聚集索引中的葉子層和根節點與中間節點有點不同，它的指標是指向資料行，且如果非聚集索引如果是包含列索引，那麼包含列僅僅儲存在葉級別，而鍵值可以儲存在所有級別，這塊會在接下來的包含列索引中講述。

對於根與中間級的索引記錄，它的結構包括：
A）索引欄位值
B）RowId（即對應資料頁的頁指標+指標偏移量）。在高層的索引頁中包含RowId是為了當索引允許重複值時，當更改資料時精確定位資料行。
C）下一級索引頁的指標

 

對於葉子層的索引物件，它的結構包括：
A）索引欄位值
B）RowId

 

由於索引建值儲存在索引頁中，所以檢索單獨的索引鍵值效率是很高的，因為不需要定位到資料頁在索引頁中就能找到資料，對於當個欄位建索引非聚集索引所佔的空間要小於聚集索引，因為非聚集索引不需要儲存資料行，對於建全覆蓋索引除外。

 

  • 非聚集索引列的選擇
  1. 同樣非聚集索引避免選擇寬列，這點與聚集索引一樣。
  2. 包含經常包含在查詢的搜尋條件（例如返回完全匹配的 WHERE 子句）中的列
  3. 經常作為JOIN 或 GROUP BY 子句
  4. 儘量避免使用組合列建索引，除非組合列在where中有使用，否則可以用包含列索引替代組合索引，選擇組合欄位做索引，組合欄位的第一個欄位選擇很重要，第一個欄位一定要經常被使用的欄位，例如AB欄位作為組合欄位，當WHERE用A欄位作為檢索條件的時候，查詢會使用索引查詢；當你使用B作為WHERE的檢索條件的時候，查詢使用的是索引掃描，雖然我們不能絕對肯定查詢的效率就一定比掃描要好，但是這也是告訴我們要合適的選擇索引列，甚至的列之間的先後順序。
  5. 大量非重複值，如姓氏和名字的組合（前提是聚集索引被用於其他列）。不要選擇例如性別這種重複值多的列，這種情況表掃描比查詢效率會更高，所以有時候當我們用查詢計劃分析時不一定掃描就一定比查詢就要差，我們要根據實際情況去分析問題。
  6. 覆蓋查詢。
    當索引包含查詢中的所有列時，效能可以提升。查詢優化器可以找到索引內的所有列值；不會訪問表或聚集索引資料，這樣就減少了磁碟 I/O 操作。使用具有包含列的索引來新增覆蓋列，而不是建立寬索引鍵。有關詳細資訊，請參閱具有包含列的索引。
    如果表有聚集索引，則該聚集索引中定義的列將自動追加到表上每個非聚集索引的末端。這可以生成覆蓋查詢，而不用在非聚集索引定義中指定聚集索引列。例如，如果一個表在 C 列上有聚集索引，則 B 和 A 列的非聚集索引將具有其自己的鍵值列 B、A 和 C

世界上沒有絕對完美的事情，索引也是一樣，給我們帶來查詢效率的同時也會有弊端

  • 對錶編制大量索引會影響 INSERT、UPDATE、DELETE 和 MERGE 語句的效能，因為當表中的資料更改時，所有索引都須進行適當的調整

總結

   這篇文章更重要的是講述索引的儲存結構和查詢方式，沒有講述索引的一些基本概念和語句的寫法，網上有很多寫的很好這方面的文章。希望寫這篇文章能給大家帶來幫助，文章中有一些內容是從別的作者哪裡拷貝過來的，因為我覺得原作者（KissKnife）在這方面已經講述的非常到位，所以借鑑了一下，同樣如果文章中有講述的不合理的地方還望大家提出。

 

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。

《歡迎交流討論》

相關文章