非聚集索引

概述

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

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