Key Lookup开销过大导致聚集索引扫描
2018-09-21 15:11 潇湘隐者 阅读(3797) 评论(0) 编辑 收藏 举报以前总结过一篇文章SQL SERVER中什么情况会导致索引查找变成索引扫描 介绍了几种索引查找(Index Seek)变成索引扫描(Index Scan)的情形。昨天写一篇文章的时候,也遇到了一个让人奇怪的执行计划。一时没有想明白为什么优化器会选择聚集索引扫描。案例详情请见SQL Server OPTION (OPTIMIZE FOR UNKNOWN) 测试总结 。 如下所示,测试环境为SQL Server 2014,数据库为AdventureWorks2014
CREATE PROCEDURE test (@pid int)
AS
SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = @pid OPTION (OPTIMIZE FOR UNKNOWN);
[Sales].[SalesOrderDetail]的索引信息如下如下。其实这里优化器选择聚集索引扫描是因为Cost缘故。因为走非聚集索引查找(Index Seek)的话,Key Lookup的开销较大。整体开销比聚集索引扫描还大。我们可以测试验证一下
如下所示,我们新增一个SQL语句,强制其走索引查找(具体索引为IX_SalesOrderDetail_ProductID),然后执行对比查看执行计划的开销
ALTER PROCEDURE test (@pid int)
AS
SELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = @pid OPTION (OPTIMIZE FOR UNKNOWN);
SELECT * FROM [Sales].[SalesOrderDetail] WITH (INDEX =IX_SalesOrderDetail_ProductID)
WHERE ProductID = @pid;
GO
如下测试所示,两种实际执行计划的开销比为 22% VS 78%。 所以优化器肯定会选开销小的执行计划。也就是说如果优化器发现当索引查找时,如果Key Lookup开销过大,那么优化器会选择聚集索引索引扫描。 这个案例就是一个活生生的案例。 也许有人会反问:不是Index Seek效率表Index Scan要高吗?你这有点不合逻辑,注意,这个特定条件下,虽然Index Seek变成 Index Scan,但是你注意一下上下文,索引变了, 从IX_SalesOrderDetail_ProductID变成了聚集索引PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID 。
总结: 任何现象背后都有一定的规律,有时候,只要你静下心来,仔细分析一下。就能一窥究竟。如果总是不问为什么,那么你总是不了解背后原理!也就永远止步不前!

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2010-09-21 SQL Server 2008 打SP1 补丁遇到的问题
2010-09-21 SQL Server 2008 筛选器报错Microsoft.SqlServer.Management.Sdk.Sfc