在“索引深入淺出：非聚集索引的B樹(shù)結(jié)構(gòu)在聚集表”里，我們討論了在聚集表上的非聚集索引，這篇文章我們討論下在堆表上的非聚集索引。

非聚集索引可以在聚集表或堆表上創(chuàng)建。當(dāng)我們?cè)诰奂砩蟿?chuàng)建非聚集索引時(shí)，聚集索引鍵擔(dān)當(dāng)為行指針。在堆表里，文件號(hào)，頁(yè)號(hào)和槽號(hào)（file id , page number and slot number）的組合在非聚集索引里擔(dān)當(dāng)為行指針。

我們來(lái)看下手頭的一個(gè)例子。我們創(chuàng)建salesorderdetail表的副本，并在上面的PRoductid和salesorderid列創(chuàng)建創(chuàng)建非聚集索引。

1 DROP TABLE SalesOrderDetailHeap2 3 SELECT * INTO dbo.SalesOrderDetailHeap FROM AdventureWorks2008r2.Sales.SalesOrderDetail4 GO5 CREATE UNIQUE INDEX Ix_ProductId ON SalesOrderDetailHeap(ProductId,Salesorderid) 

收集非聚集索引相關(guān)信息：

 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 --根節(jié)點(diǎn)/索引頁(yè) 6 DBCC TRACEON(3604) 7 DBCC PAGE(IndexDB,1,3720,3) 8  9 DBCC TRACEON(3604)10 DBCC PAGE(IndexDB,1,3608,3)--葉子節(jié)點(diǎn)/索引頁(yè)11 12 DBCC TRACEON(3604)13 DBCC PAGE(IndexDB,1,3908,3)--葉子節(jié)點(diǎn)/索引頁(yè)14 SELECT * FROM dbo.sp_table_pages WHERE IndexLevel=0 --葉子節(jié)點(diǎn)/索引頁(yè)

根據(jù)上述信息進(jìn)行非聚集索引邏輯示意圖的繪制：

現(xiàn)在我們來(lái)分析下SQL Server如何存儲(chǔ)堆表的非聚集索引，首先我們通過(guò)DBCC IND命令查看非聚集索引的頁(yè)分配情況，最后一個(gè)參數(shù)，2是Ix_ProductId的索引號(hào)。

1 DBCC ind('IndexDB','SalesOrderDetailHeap',2)

一共返回298條記錄，包括1個(gè)IAM頁(yè)，288個(gè)索引頁(yè)，我們用下列語(yǔ)句找下根層的頁(yè)號(hào)：

1 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC 

可以看到，indexlevel列最大值1的頁(yè)號(hào)是3270，這個(gè)頁(yè)就是根頁(yè)，因?yàn)閕ndexlevel列最大值是1，所以這個(gè)堆表的非聚集索引的B樹(shù)結(jié)構(gòu)只有2層，即根層和葉子層，也就是說(shuō)288個(gè)索引頁(yè)中，1個(gè)頁(yè)是根層的根頁(yè)（也是索引頁(yè)），287個(gè)頁(yè)是葉子層的索引頁(yè)。我們來(lái)看看3270頁(yè)的信息。

1 DBCC TRACEON(3604)2 DBCC PAGE(IndexDB,1,3720,3)

輸出結(jié)果，和聚集表里的非聚集索引的根頁(yè)結(jié)構(gòu)是一樣的。

我們來(lái)看看葉子層的3608頁(yè)。

1 DBCC TRACEON(3604)2 DBCC PAGE(IndexDB,1,3608,3)--葉子節(jié)點(diǎn)/索引頁(yè)

在聚集表的非聚集索引的葉子層，聚集鍵與非聚集鍵一齊加入了葉子層的頁(yè)。這里我們沒(méi)有聚集索引，索引SQL Server加了個(gè)行標(biāo)識(shí)號(hào)（8 bytes大小），由文件號(hào)（2 bytes），頁(yè)號(hào)（4 bytes）和槽號(hào)（2 bytes）組合而成。

從上圖我們可以清楚看出，productid值為707，salesorderid值為43665的記錄完整信息，可以在HeapRID 0xB800000001003E00位置找到。下面的查詢可以幫我們把RID轉(zhuǎn)為文件號(hào)：頁(yè)號(hào)：槽號(hào)（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表示文件號(hào)：1 ，頁(yè)號(hào)：184 ，槽號(hào):62。我們來(lái)看看184頁(yè)。

1 DBCC TRACEON(3604)2 DBCC PAGE(IndexDB,1,184,3)       

從輸出我們可以看到，productid值為707，salesorderid值為43665的記錄所有列可以在槽號(hào)62找到，與1:184:62表示文件號(hào)：1 ，頁(yè)號(hào)：184 ，槽號(hào):62完全一致。

我們通過(guò)下面的查詢看看SQL Server如何使用非聚集索引查找堆表上的數(shù)據(jù)，點(diǎn)擊工具欄的顯示包含實(shí)際的執(zhí)行計(jì)劃。

1 SET STATISTICS IO ON2 GO3 SELECT *  FROM SalesOrderDetailHeap WHERE productid=707 AND SalesOrderid=43665    

SQL Server需要進(jìn)行2次I/O操作到達(dá)非聚集索引的葉子層，1次I/O操作通過(guò)使用RID查找（堆）拿到剩下的數(shù)據(jù)。執(zhí)行計(jì)劃如下所示：

即使我們將查詢語(yǔ)句修改為，只要ProductId,SalesOrderid,SalesorderDetailId這3列,SQL Server還是要進(jìn)行鍵查找（Key lookup）操作。

1 SET STATISTICS IO ON2 GO3 SELECT *  FROM SalesOrderDetailHeap WHERE productid=707 AND SalesOrderid=43665    4  5 SET STATISTICS IO ON6 GO7 SELECT ProductId,SalesOrderid,SalesOrderDetailID FROM SalesOrderDetailHeap WHERE productid=707 AND SalesOrderid=43665       

這是因?yàn)椋琒alesorderDetailId列沒(méi)有定義為聚集鍵，在非聚集索引的葉子層沒(méi)有這列。為了避免鍵查找（key lookup）操作，我們需要將列限制到只有非聚集索引鍵（ProductKey ,salesorderid）。

1 SET STATISTICS IO ON2 GO3 SELECT ProductId,SalesOrderid FROM SalesOrderDetailHeap WHERE productid=707 AND SalesOrderid=43665  

如上圖所示，只有非聚集索引查找操作，沒(méi)有鍵查找（Key lookup）操作了。