非聚集索引

概述

? ? ?對(duì)于非聚集索引，涉及的信息要比聚集索引更多一些，由于整個(gè)篇幅比較大涉及接下來(lái)的要寫(xiě)的“ 包含列的索引 ”，“索引碎片”等一些知識(shí)點(diǎn)，可能要結(jié)合起來(lái)閱讀理解起來(lái)要更容易一些。非聚集索引和聚集索引一樣都是B-樹(shù)結(jié)構(gòu)，但是非聚集索引不改變數(shù)據(jù)的存儲(chǔ)方式，所以一個(gè)表允許建多個(gè)非聚集索引； 非聚集索引的葉層是由索引頁(yè)而不是由數(shù)據(jù)頁(yè)組成，索引行包含索引鍵值和指向表數(shù)據(jù)存儲(chǔ)位置的行定位器，

既可以使用聚集索引來(lái)為表或視圖定義非聚集索引，也可以根據(jù)堆來(lái)定義非聚集索引。非聚集索引中的每個(gè)索引行都包含非聚集鍵值和行定位符。此定位符指向聚集索引或堆中包含該鍵值的數(shù)據(jù)行。

正文

• 單個(gè)分區(qū)中的非聚集索引結(jié)構(gòu)

非聚集索引 Index_id>1 可以結(jié)合語(yǔ)句查詢(xún)

      
        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;
    

?

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

• 如果表是堆（意味著該表沒(méi)有聚集索引），則行定位器是指向行的指針。該指針由文件標(biāo)識(shí)符 (ID)、頁(yè)碼和頁(yè)上的行數(shù)生成。整個(gè)指針?lè)Q為行 ID (RID)。
  
  

如果表有聚集索引或索引視圖上有聚集索引，則行定位器是行的聚集索引鍵。如果聚集索引不是唯一的索引，SQL Server 將添加在內(nèi)部生成的值（稱(chēng)為 唯一值 ）以使所有重復(fù)鍵唯一。此四字節(jié)的值對(duì)于用戶(hù)不可見(jiàn)。僅當(dāng)需要使聚集鍵唯一以用于非聚集索引中時(shí)，才添加該值。SQL Server 通過(guò)使用存儲(chǔ)在非聚集索引的葉行內(nèi)的聚集索引鍵搜索聚集索引來(lái)檢索數(shù)據(jù)行。

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

A）葉子結(jié)點(diǎn)并非數(shù)據(jù)結(jié)點(diǎn)
B）葉子結(jié)點(diǎn)為每一真正的數(shù)據(jù)行存儲(chǔ)一個(gè)“鍵-指針”對(duì)
C）葉子結(jié)點(diǎn)中還存儲(chǔ)了一個(gè)指針偏移量，根據(jù)頁(yè)指針及指針偏移量可以定位到具體的數(shù)據(jù)行。
D）類(lèi)似的，在除葉結(jié)點(diǎn)外的其它索引結(jié)點(diǎn)，存儲(chǔ)的也是類(lèi)似的內(nèi)容，只不過(guò)它是指向下一級(jí)的索引頁(yè)的。

?

聚集索引是一種稀疏索引，數(shù)據(jù)頁(yè)上一級(jí)的索引頁(yè)存儲(chǔ)的是頁(yè)指針，而不是行指針。而對(duì)于非聚集索引，則是密集索引，在數(shù)據(jù)頁(yè)的上一級(jí)索引頁(yè)它為每一個(gè)數(shù)據(jù)行存儲(chǔ)一條索引記錄。

注意：上圖中的數(shù)據(jù)頁(yè)是聚集索引或者堆數(shù)據(jù)行，而不是非聚集索引的數(shù)據(jù)頁(yè)，在非聚集索引中不存在數(shù)據(jù)頁(yè)，非聚集索引中的葉子層和根節(jié)點(diǎn)與中間節(jié)點(diǎn)有點(diǎn)不同，它的指針是指向數(shù)據(jù)行，且如果非聚集索引如果是包含列索引，那么包含列僅僅存儲(chǔ)在葉級(jí)別，而鍵值可以存儲(chǔ)在所有級(jí)別，這塊會(huì)在接下來(lái)的包含列索引中講述。

對(duì)于根與中間級(jí)的索引記錄，它的結(jié)構(gòu)包括：
A）索引字段值
B）RowId（即對(duì)應(yīng)數(shù)據(jù)頁(yè)的頁(yè)指針+指針偏移量）。在高層的索引頁(yè)中包含RowId是為了當(dāng)索引允許重復(fù)值時(shí)，當(dāng)更改數(shù)據(jù)時(shí)精確定位數(shù)據(jù)行。
C）下一級(jí)索引頁(yè)的指針

?

對(duì)于葉子層的索引對(duì)象，它的結(jié)構(gòu)包括：
A）索引字段值
B）RowId

?

由于索引建值存儲(chǔ)在索引頁(yè)中，所以檢索單獨(dú)的索引鍵值效率是很高的，因?yàn)椴恍枰ㄎ坏綌?shù)據(jù)頁(yè)在索引頁(yè)中就能找到數(shù)據(jù)，對(duì)于當(dāng)個(gè)字段建索引非聚集索引所占的空間要小于聚集索引，因?yàn)榉蔷奂饕恍枰鎯?chǔ)數(shù)據(jù)行，對(duì)于建全覆蓋索引除外。

?

• 非聚集索引列的選擇

1. 同樣非聚集索引避免選擇寬列，這點(diǎn)與聚集索引一樣。
2. 包含經(jīng)常包含在查詢(xún)的搜索條件（例如返回完全匹配的 WHERE 子句）中的列
3. 經(jīng)常作為 JOIN 或 GROUP BY 子句
4. 盡量避免使用組合列建索引，除非組合列在where中有使用，否則可以用包含列索引替代組合索引，選擇組合字段做索引，組合字段的第一個(gè)字段選擇很重要，第一個(gè)字段一定要經(jīng)常被使用的字段，例如AB字段作為組合字段，當(dāng)WHERE用A字段作為檢索條件的時(shí)候，查詢(xún)會(huì)使用索引查找；當(dāng)你使用B作為WHERE的檢索條件的時(shí)候，查詢(xún)使用的是索引掃描，雖然我們不能絕對(duì)肯定查找的效率就一定比掃描要好，但是這也是告訴我們要合適的選擇索引列，甚至的列之間的先后順序。
5. 大量非重復(fù)值，如姓氏和名字的組合（前提是聚集索引被用于其他列）。不要選擇例如性別這種重復(fù)值多的列，這種情況表掃描比查找效率會(huì)更高，所以有時(shí)候當(dāng)我們用查詢(xún)計(jì)劃分析時(shí)不一定掃描就一定比查找就要差，我們要根據(jù)實(shí)際情況去分析問(wèn)題。
6. 覆蓋查詢(xún)。
   當(dāng)索引包含查詢(xún)中的所有列時(shí)，性能可以提升。查詢(xún)優(yōu)化器可以找到索引內(nèi)的所有列值；不會(huì)訪(fǎng)問(wèn)表或聚集索引數(shù)據(jù)，這樣就減少了磁盤(pán) I/O 操作。使用具有包含列的索引來(lái)添加覆蓋列，而不是創(chuàng)建寬索引鍵。有關(guān)詳細(xì)信息，請(qǐng)參閱具有包含列的索引。
   如果表有聚集索引，則該聚集索引中定義的列將自動(dòng)追加到表上每個(gè)非聚集索引的末端。這可以生成覆蓋查詢(xún)，而不用在非聚集索引定義中指定聚集索引列。例如，如果一個(gè)表在 C 列上有聚集索引，則 B 和 A 列的非聚集索引將具有其自己的鍵值列 B 、 A 和 C

世界上沒(méi)有絕對(duì)完美的事情，索引也是一樣，給我們帶來(lái)查詢(xún)效率的同時(shí)也會(huì)有弊端

• 對(duì)表編制大量索引會(huì)影響 INSERT、UPDATE、DELETE 和 MERGE 語(yǔ)句的性能，因?yàn)楫?dāng)表中的數(shù)據(jù)更改時(shí)，所有索引都須進(jìn)行適當(dāng)?shù)恼{(diào)整

總結(jié)

?? 這篇文章更重要的是講述索引的存儲(chǔ)結(jié)構(gòu)和查找方式，沒(méi)有講述索引的一些基本概念和語(yǔ)句的寫(xiě)法，網(wǎng)上有很多寫(xiě)的很好這方面的文章。希望寫(xiě)這篇文章能給大家?guī)?lái)幫助，文章中有一些內(nèi)容是從別的作者哪里拷貝過(guò)來(lái)的，因?yàn)槲矣X(jué)得原作者（KissKnife）在這方面已經(jīng)講述的非常到位，所以借鑒了一下，同樣如果文章中有講述的不合理的地方還望大家提出。

?

?

SQL Server 索引和表體系結(jié)構(gòu)（非聚集索引）