SQL Server 索引和表體繫結構（二）

標籤：des   style   blog   http   os   使用   io   strong   ar   

 

轉自：http://www.cnblogs.com/chenmh

非叢集索引

概述

     對於非叢集索引，涉及的資訊要比叢集索引更多一些，由於整個篇幅比較大涉及接下來的要寫的“內含資料行索引”，“索引片段”等一些知識點，可能要結合起來閱讀理解起來要更容易一些。非叢集索引和叢集索引一樣都是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_pagesFROM 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）在這方面已經講述的非常到位，所以借鑒了一下，同樣如果文章中有講述的不合理的地方還望大家提出。

SQL Server 索引和表體繫結構（二）