非聚集索引
概述
? ? ?對于非聚集索引,涉及的信息要比聚集索引更多一些,由于整個篇幅比較大涉及接下來的要寫的“ 包含列的索引 ”,“索引碎片”等一些知識點(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)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
