在“索引深入淺出:非聚集索引的B樹結構在聚集表”裡,我們讨論了在聚集表上的非聚集索引,這篇文章我們讨論下在堆表上的非聚集索引。
非聚集索引可以在聚集表或堆表上建立。當我們在聚集表上建立非聚集索引時,聚集索引鍵擔當為行指針。在堆表裡,檔案号,頁号和槽号(file id , page number and slot number)的組合在非聚集索引裡擔當為行指針。
我們來看下手頭的一個例子。我們建立salesorderdetail表的副本,并在上面的productid和salesorderid 列建立建立非聚集索引。
1 DROP TABLE SalesOrderDetailHeap
2
3 SELECT * INTO dbo.SalesOrderDetailHeap FROM AdventureWorks2008r2.Sales.SalesOrderDetail
4 GO
5 CREATE UNIQUE INDEX Ix_ProductId ON SalesOrderDetailHeap(ProductId,Salesorderid)
收集非聚集索引相關資訊:
1 TRUNCATE TABLE dbo.sp_table_pages
2 INSERT INTO sp_table_pages EXEC('DBCC IND(IndexDB,SalesOrderDetailHeap,2)')
3 GO
4
5 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根節點/索引頁
6 DBCC TRACEON(3604)
7 DBCC PAGE(IndexDB,1,3720,3)
8
9 DBCC TRACEON(3604)
10 DBCC PAGE(IndexDB,1,3608,3)--葉子節點/索引頁
11
12 DBCC TRACEON(3604)
13 DBCC PAGE(IndexDB,1,3908,3)--葉子節點/索引頁
14 SELECT * FROM dbo.sp_table_pages WHERE IndexLevel=0 --葉子節點/索引頁
根據上述資訊進行非聚集索引邏輯示意圖的繪制:
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIyZuBnL3IzNxgTO3EjMyMjNxQTMvwVNwUTMwIzLchDNzATN38CX1EDMyc2bsJ2Lc12bj5ycn9Gbi52YuAzcldWYtl2Lc9CX6MHc0RHaiojIsJye.png)
現在我們來分析下SQL Server如何存儲堆表的非聚集索引,首先我們通過DBCC IND指令檢視非聚集索引的頁配置設定情況,最後一個參數,2是Ix_ProductId的索引号。
1 DBCC ind('IndexDB','SalesOrderDetailHeap',2)
一共傳回298條記錄,包括1個IAM頁,288個索引頁,我們用下列語句找下根層的頁号:
1 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC
可以看到,indexlevel列最大值1的頁号是3270,這個頁就是根頁,因為indexlevel列最大值是1,是以這個堆表的非聚集索引的B樹結構隻有2層,即根層和葉子層,也就是說288個索引頁中,1個頁是根層的根頁(也是索引頁),287個頁是葉子層的索引頁。我們來看看3270頁的資訊。
1 DBCC TRACEON(3604)
2 DBCC PAGE(IndexDB,1,3720,3)
輸出結果,和聚集表裡的非聚集索引的根頁結構是一樣的。
我們來看看葉子層的3608頁。
1 DBCC TRACEON(3604)
2 DBCC PAGE(IndexDB,1,3608,3)--葉子節點/索引頁
在聚集表的非聚集索引的葉子層,聚集鍵與非聚集鍵一齊加入了葉子層的頁。這裡我們沒有聚集索引,索引SQL Server加了個行辨別号(8 bytes大小),由檔案号(2 bytes),頁号(4 bytes)和槽号(2 bytes)組合而成。
從上圖我們可以清楚看出,productid值為707,salesorderid值為43665的記錄完整資訊,可以在HeapRID 0xB800000001003E00位置找到。下面的查詢可以幫我們把RID轉為檔案号:頁号:槽号(FileId:PageId:SlotNo)格式。
1 DECLARE @HeapRid BINARY(8)
2 SET @HeapRid = 0xB800000001003E00
3 SELECT
4 CONVERT (VARCHAR(5),
5 CONVERT(INT, SUBSTRING(@HeapRid, 6, 1)
6 + SUBSTRING(@HeapRid, 5, 1)))
7 + ':'
8 + CONVERT(VARCHAR(10),
9 CONVERT(INT, SUBSTRING(@HeapRid, 4, 1)
10 + SUBSTRING(@HeapRid, 3, 1)
11 + SUBSTRING(@HeapRid, 2, 1)
12 + SUBSTRING(@HeapRid, 1, 1)))
13 + ':'
14 + CONVERT(VARCHAR(5),
15 CONVERT(INT, SUBSTRING(@HeapRid, 8, 1)
16 + SUBSTRING(@HeapRid, 7, 1)))
17 AS 'Fileid:Pageid:Slot'
1:184:62表示檔案号:1 ,頁号:184 ,槽号:62。我們來看看184頁。
1 DBCC TRACEON(3604)
2 DBCC PAGE(IndexDB,1,184,3)
從輸出我們可以看到,productid值為707,salesorderid值為43665的記錄所有列可以在槽号62找到,與1:184:62表示檔案号:1 ,頁号:184 ,槽号:62完全一緻。
我們通過下面的查詢看看SQL Server如何使用非聚集索引查找堆表上的資料,點選工具欄的
顯示包含實際的執行計劃。
1 SET STATISTICS IO ON
2 GO
3 SELECT * FROM SalesOrderDetailHeap WHERE productid=707 AND SalesOrderid=43665
SQL Server需要進行2次I/O操作到達非聚集索引的葉子層,1次I/O操作通過使用RID查找(堆)拿到剩下的資料。執行計劃如下所示:
即使我們将查詢語句修改為,隻要 ProductId,SalesOrderid,SalesorderDetailId 這3列,SQL Server還是要進行鍵查找(Key lookup)操作。
1 SET STATISTICS IO ON
2 GO
3 SELECT * FROM SalesOrderDetailHeap WHERE productid=707 AND SalesOrderid=43665
4
5 SET STATISTICS IO ON
6 GO
7 SELECT ProductId,SalesOrderid,SalesOrderDetailID FROM SalesOrderDetailHeap WHERE productid=707 AND SalesOrderid=43665
這是因為,SalesorderDetailId列沒有定義為聚集鍵,在非聚集索引的葉子層沒有這列。為了避免鍵查找(key lookup)操作,我們需要将列限制到隻有非聚集索引鍵(ProductKey ,salesorderid)。
1 SET STATISTICS IO ON
2 GO
3 SELECT ProductId,SalesOrderid FROM SalesOrderDetailHeap WHERE productid=707 AND SalesOrderid=43665
如上圖所示,隻有非聚集索引查找操作,沒有鍵查找(Key lookup)操作了。
參考文章:
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/03/17/sql-server-part-5-explaining-non-clustered-index-on-heap/
注:此文章為
WoodyTu學習MS SQL技術,收集整理相關文檔撰寫,歡迎轉載,請在文章頁面明顯位置給出此文連結!
若您覺得這篇文章還不錯請點選下右下角的推薦,有了您的支援才能激發作者更大的寫作熱情,非常感謝!