SQL Server 查询执行计划初学者2 – 非聚集索引运算符

在之前的文章SQL Server 查询执行计划初学者 – 聚集索引运算符中我们介绍了什么是聚集索引扫描和聚集索引查找，它们是如何发生的，以及如何消除表扫描。这篇文章中我们主要介绍非聚集索引关联运算符。

首先我们从非聚集索引扫描开始……

非聚集索引扫描(Non-Clustered Index Scan)

出现场合：
当查询的数据列是非聚集索引的一部分并且查询要访问此特定索引上的大量数据时，就会发生非聚集索引扫描
好或坏：
除了获取大量数据行的情况之外，出现非聚集索引扫描操作符都是不好的状况
改善行动：
重新定义或优化的非聚集索引。可以通过调整该非聚集索引中的列项目来优化非聚集索引。

通过优化索引，非聚集索引扫描(Non-Clustered Index Scan)操作可以被改善为非聚集索引查找(Non-Clustered Index Seek) 操作。

非聚集索引查找(Non-Clustered Index Seek)

出现场合：
当查询访问的数据列保存可以通过非聚集索引的B+ 树定位时，就会使用非聚集索引查找操作
好或坏：
通常情况下，非聚集索引查找操作是最优操作
改善行动：
通常情况下无需改善

接下来我们通过一个简单的示例来深入了解一下这几个操作符。

我们将使用在上一篇文章中创建的同一个表。

首先，让我们基于 [OrderQty] 和 [ProductID] 列创建一个非聚集索引。

CREATE NONCLUSTERED INDEX [IX_MySalesOrderDetail_OrderQty_ProductID] 
ON [dbo].[MySalesOrderDetail]
([OrderQty],[ProductID])
GO

接下来，我们执行以下脚本并显示实际执行计划。

SET STATISTICS IO ON
GO
SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty
FROM [dbo].[MySalesOrderDetail]

从上面的截图我们可以看到，本次查询执行了303 次逻辑读操作，同时返回了121,317 行数据。另外，因为查询语句中的SELECT数据列都定义在非集群索引的键中，所以执行计划中使用了**非聚集索引扫描(Non-Clustered Index Scan)**操作。

我们在给查询语句添加一个 [ProductID] 列上的WHERE 条件：

SET STATISTICS IO ON
GO
SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty
FROM [dbo].[MySalesOrderDetail]
WHERE ProductID = 799
GO

新的查询结果是 648 行。但是 SQL Server 做了同样的 303 次逻辑读取，执行计划仍然没有使用非聚集索引查找(Non-Clustered Index Seek)。 那么，如何更改和优化以重新定义非聚集索引，以便它可以开始使用更高效的执行计划呢？

这里可以使用的方法之一是给WHERE 条件添加附加信息，以便 SQL Server 可以有效地使用非聚集索引。

请记住，我们在订单数量和产品 ID 上已经创建了索引。

因为订单数量总是大于零，所以我么可以在查询语句中添加>0的订单数量条件。这样的修改不会影响最后的查询结果。

SET STATISTICS IO ON
GO
SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty
FROM [dbo].[MySalesOrderDetail]
WHERE ProductID = 799 AND OrderQty > 0 
GO

执行上面更新后的查询时，我们得到了相同结果的 648 行。

查看执行计划我们可以看到，虽然逻辑读的执行次数没有变化仍为 303，但是查找操作已经被优化为 非聚集索引查找(Non-Clustered Index Seek) 。

另一种改善方式是调整索引中的列顺序：

drop INDEX [IX_MySalesOrderDetail_OrderQty_ProductID] 
ON [dbo].[MySalesOrderDetail]
CREATE NONCLUSTERED INDEX [IX_MySalesOrderDetail_ProductID_OrderQty] 
ON [[dbo].[MySalesOrderDetail]
([ProductID],[OrderQty])
GO

然后我们再次执行之前的使用 [ProductID] 列的条件的原始查询：

SET STATISTICS IO ON
GO
SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty
FROM [dbo].[MySalesOrderDetail]
WHERE ProductID = 799
GO

查看最新的执行计划，可以得知我们获得了最佳的执行计划，逻辑读取次数仅为 5次。

为了让执行计划有效使用已创建的非聚集索引，在上面的两个例子中，我们分别使用了两种优化方式，第一种方式是重新定义 WHERE子句来让执行计划强制使用索引查找操作，但这种方式没有减少 IO 操作。

第二种方式是依据的实际查询的列的顺序来重新定义非聚集索引中的键和顺序。从上面的示例我们可以看到优化后 IO 显着减少。

这就是我们为什么需要将将非聚集索引扫描(Non-Clustered Index Scan)转换为非聚集索引查找(Non-Clustered Index Seek)。

现在让我们看看下一个运算符，查找……

查找(Lookups)

出现场合：
查询优化器使用非聚集索引获取少量列的数据，同时检索基表以获取其他列数据
好或坏：
坏
改善行动：
创建覆盖覆盖索引或使用包含查询数据列的索引

下面是一个查找操作的典型示例：

SET STATISTICS IO ON
GO
SELECT	SalesOrderID, SalesOrderDetailID, 
		ProductID, OrderQty,
		SpecialOfferID
FROM [dbo].[MySalesOrderDetail]
WHERE ProductID = 789
GO

查询结果数据为364行，执行了1854次逻辑读取操作。

执行计划首先使用非聚集索引查找(Non-Clustered Index Seek)操作符,基于 WHERE条件的[ProductID] 列查找数据。同时使用键查找(Lookup)操作符了来获取不在Index定义中的额外字段 [SpecialOfferID]。

针对这个查询，有两种优化方式。
第一种优化方式就是创建一个包含所有查询字段的覆盖非聚集索引。索引的创建SQL如下：

DROP INDEX [IX_MySalesOrderDetail_ProductID_OrderQty_SpecialOfferID] ON .[dbo].[MySalesOrderDetail]
 
CREATE NONCLUSTERED  INDEX [IX_MySalesOrderDetail_ProductID_OrderQty_SpecialOfferID] 
ON [dbo].[MySalesOrderDetail]
([ProductID])
INCLUDE ([OrderQty],[SpecialOfferID])
GO

并再次执行相同的查询：

从执行计划详细可以看到，查询结果还是364 行，但是逻辑读次数降到 6。而且执行计划中还消除了键查找(Lookup)运算符。

第二种优化方式，就是在憧憬非聚集索引并包含SELECT语句中需要的所有列：

DROP INDEX [IX_MySalesOrderDetail_ProductID_OrderQty] ON 
[dbo].[MySalesOrderDetail]
 
CREATE NONCLUSTERED  INDEX [IX_MySalesOrderDetail_ProductID_OrderQty_SpecialOfferID] 
ON [dbo].[MySalesOrderDetail]
([ProductID],[OrderQty],[SpecialOfferID])
GO

并再次执行相同的查询：


查看最新的执行计划，我们可以看到它采用了非聚集索引查找(Non-Clustered Index Seek),并且消除了键查找(Lookup)操作符。

概括

非聚集索引是性能优化生命周期中非常重要的一部分。本文介绍了非聚集索引运算符的基础知识，希望这篇文章对您有所帮助。