索引深入浅出(5/10):非聚集索引的B树结构在堆表
在“索引深入浅出:非聚集索引的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 --叶子节点/索引页
根据上述信息进行非聚集索引逻辑示意图的绘制:
现在我们来分析下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)操作了。
参考文章:
注:此文章为WoodyTu学习MS SQL技术,收集整理相关文档撰写,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,有了您的支持才能激发作者更大的写作热情,非常感谢!
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· 周边上新:园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源!
· 分享 3 个 .NET 开源的文件压缩处理库,助力快速实现文件压缩解压功能!
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试?测试工程师会被淘汰吗?