欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Key Lookup开销过大导致聚集索引扫描

程序员文章站 2023-11-14 11:18:34
以前总结过一篇文章SQL SERVER中什么情况会导致索引查找变成索引扫描 介绍了几种索引查找(Index Seek)变成索引扫描(Index Scan)的情形。昨天写一篇文章的时候,也遇到了一个让人奇怪的执行计划。一时没有想明白为什么优化器会选择聚集索引扫描。案例详情请见SQL Server OP... ......

以前总结过一篇文章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 。

 

 

 

 

 

总结: 任何现象背后都有一定的规律,有时候,只要你静下心来,仔细分析一下。就能一窥究竟。如果总是不问为什么,那么你总是不了解背后原理!也就永远止步不前!