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

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

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