非聚集索引
概述
? ? ?對于非聚集索引，涉及的信息要比聚集索引更多一些，由于整個篇幅比較大涉及接下來的要寫的“ 包含列的索引 ”，“索引碎片”等一些知識點(diǎn)，可能要結(jié)合起來閱讀理解起來要更容易一些。非聚集索引和聚集索引一樣都是B-樹結(jié)構(gòu)，但是非聚集索引不改變數(shù)據(jù)的存儲方式，所以一個表允許建多個非聚集索引； 非聚集索引的葉層是由索引頁而不是由數(shù)據(jù)頁組成，索引行包含索引鍵值和指向表數(shù)據(jù)存儲位置的行定位器，
既可以使用聚集索引來為表或視圖定義非聚集索引，也可以根據(jù)堆來定義非聚集索引。非聚集索引中的每個索引行都包含非聚集鍵值和行定位符。此定位符指向聚集索引或堆中包含該鍵值的數(shù)據(jù)行。
正文
- 單個分區(qū)中的非聚集索引結(jié)構(gòu)
非聚集索引 Index_id>1 可以結(jié)合語句查詢
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;
?
非聚集索引行中的行定位器或是指向行的指針，或是行的聚集索引鍵，如下所述：
-
如果表是堆（意味著該表沒有聚集索引），則行定位器是指向行的指針。該指針由文件標(biāo)識符 (ID)、頁碼和頁上的行數(shù)生成。整個指針稱為行 ID (RID)。
如果表有聚集索引或索引視圖上有聚集索引，則行定位器是行的聚集索引鍵。如果聚集索引不是唯一的索引，SQL Server 將添加在內(nèi)部生成的值（稱為 唯一值 ）以使所有重復(fù)鍵唯一。此四字節(jié)的值對于用戶不可見。僅當(dāng)需要使聚集鍵唯一以用于非聚集索引中時，才添加該值。SQL Server 通過使用存儲在非聚集索引的葉行內(nèi)的聚集索引鍵搜索聚集索引來檢索數(shù)據(jù)行。
- 非聚集索引與聚集索引相比：
A）葉子結(jié)點(diǎn)并非數(shù)據(jù)結(jié)點(diǎn)
B）葉子結(jié)點(diǎn)為每一真正的數(shù)據(jù)行存儲一個“鍵-指針”對
C）葉子結(jié)點(diǎn)中還存儲了一個指針偏移量，根據(jù)頁指針及指針偏移量可以定位到具體的數(shù)據(jù)行。
D）類似的，在除葉結(jié)點(diǎn)外的其它索引結(jié)點(diǎn)，存儲的也是類似的內(nèi)容，只不過它是指向下一級的索引頁的。
?
聚集索引是一種稀疏索引，數(shù)據(jù)頁上一級的索引頁存儲的是頁指針，而不是行指針。而對于非聚集索引，則是密集索引，在數(shù)據(jù)頁的上一級索引頁它為每一個數(shù)據(jù)行存儲一條索引記錄。
注意：上圖中的數(shù)據(jù)頁是聚集索引或者堆數(shù)據(jù)行，而不是非聚集索引的數(shù)據(jù)頁，在非聚集索引中不存在數(shù)據(jù)頁，非聚集索引中的葉子層和根節(jié)點(diǎn)與中間節(jié)點(diǎn)有點(diǎn)不同，它的指針是指向數(shù)據(jù)行，且如果非聚集索引如果是包含列索引，那么包含列僅僅存儲在葉級別，而鍵值可以存儲在所有級別，這塊會在接下來的包含列索引中講述。
對于根與中間級的索引記錄，它的結(jié)構(gòu)包括：
A）索引字段值
B）RowId（即對應(yīng)數(shù)據(jù)頁的頁指針+指針偏移量）。在高層的索引頁中包含RowId是為了當(dāng)索引允許重復(fù)值時，當(dāng)更改數(shù)據(jù)時精確定位數(shù)據(jù)行。
C）下一級索引頁的指針
?
對于葉子層的索引對象，它的結(jié)構(gòu)包括：
A）索引字段值
B）RowId
?
由于索引建值存儲在索引頁中，所以檢索單獨(dú)的索引鍵值效率是很高的，因?yàn)椴恍枰ㄎ坏綌?shù)據(jù)頁在索引頁中就能找到數(shù)據(jù)，對于當(dāng)個字段建索引非聚集索引所占的空間要小于聚集索引，因?yàn)榉蔷奂饕恍枰鎯?shù)據(jù)行，對于建全覆蓋索引除外。
?
- 非聚集索引列的選擇
- 同樣非聚集索引避免選擇寬列，這點(diǎn)與聚集索引一樣。
- 包含經(jīng)常包含在查詢的搜索條件（例如返回完全匹配的 WHERE 子句）中的列
- 經(jīng)常作為 JOIN 或 GROUP BY 子句
- 盡量避免使用組合列建索引，除非組合列在where中有使用，否則可以用包含列索引替代組合索引，選擇組合字段做索引，組合字段的第一個字段選擇很重要，第一個字段一定要經(jīng)常被使用的字段，例如AB字段作為組合字段，當(dāng)WHERE用A字段作為檢索條件的時候，查詢會使用索引查找；當(dāng)你使用B作為WHERE的檢索條件的時候，查詢使用的是索引掃描，雖然我們不能絕對肯定查找的效率就一定比掃描要好，但是這也是告訴我們要合適的選擇索引列，甚至的列之間的先后順序。
- 大量非重復(fù)值，如姓氏和名字的組合（前提是聚集索引被用于其他列）。不要選擇例如性別這種重復(fù)值多的列，這種情況表掃描比查找效率會更高，所以有時候當(dāng)我們用查詢計(jì)劃分析時不一定掃描就一定比查找就要差，我們要根據(jù)實(shí)際情況去分析問題。
-
覆蓋查詢。
當(dāng)索引包含查詢中的所有列時，性能可以提升。查詢優(yōu)化器可以找到索引內(nèi)的所有列值；不會訪問表或聚集索引數(shù)據(jù)，這樣就減少了磁盤 I/O 操作。使用具有包含列的索引來添加覆蓋列，而不是創(chuàng)建寬索引鍵。有關(guān)詳細(xì)信息，請參閱具有包含列的索引。
如果表有聚集索引，則該聚集索引中定義的列將自動追加到表上每個非聚集索引的末端。這可以生成覆蓋查詢，而不用在非聚集索引定義中指定聚集索引列。例如，如果一個表在 C 列上有聚集索引，則 B 和 A 列的非聚集索引將具有其自己的鍵值列 B 、 A 和 C
世界上沒有絕對完美的事情，索引也是一樣，給我們帶來查詢效率的同時也會有弊端
- 對表編制大量索引會影響 INSERT、UPDATE、DELETE 和 MERGE 語句的性能，因?yàn)楫?dāng)表中的數(shù)據(jù)更改時，所有索引都須進(jìn)行適當(dāng)?shù)恼{(diào)整
總結(jié)
?? 這篇文章更重要的是講述索引的存儲結(jié)構(gòu)和查找方式，沒有講述索引的一些基本概念和語句的寫法，網(wǎng)上有很多寫的很好這方面的文章。希望寫這篇文章能給大家?guī)韼椭恼轮杏幸恍﹥?nèi)容是從別的作者哪里拷貝過來的，因?yàn)槲矣X得原作者（KissKnife）在這方面已經(jīng)講述的非常到位，所以借鑒了一下，同樣如果文章中有講述的不合理的地方還望大家提出。
?
?
|
備注： ??? 作者： pursuer.chen ??? 博客： http://www.cnblogs.com/chenmh 本站點(diǎn)所有隨筆都是原創(chuàng)，歡迎大家轉(zhuǎn)載；但轉(zhuǎn)載時必須注明文章來源，且在文章開頭明顯處給明鏈接，否則保留追究責(zé)任的權(quán)利。 《歡迎交流討論》 |
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
