SQL Server 执行计划(5) - 执行计划运算符详解2

最新推荐文章于 2023-04-16 10:53:46 发布

albatross76

最新推荐文章于 2023-04-16 10:53:46 发布

阅读量1.2k

点赞数 1

分类专栏：数据库文章标签：数据库 sqlserver

原文链接：https://www.sqlshack.com/sql-server-execution-plan-operators-part-2/

版权

数据库专栏收录该内容

14 篇文章

订阅专栏

本文深入探讨SQL Server执行计划中的运算符，包括行标识查找（RID Lookup）、键查找（Key Lookup）和排序（Sort）运算符。通过示例解释这些运算符的工作原理和对查询性能的影响，提出优化建议，如创建覆盖索引以减少额外的I/O开销和提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在上一篇文章中，我们讨论了您在使用 SQL Server 执行计划时可能会遇到的第一组运算符。我们介绍了表扫描(Table Scan), 非聚集索引查找(Non-Clustered Index Seek)、聚集索引扫描(Clustered Index Scan) 和 聚集索引查找(Clustered Index Seek) 运算符。在本文中，我们将讨论第二组 SQL Server 执行计划运算符。

让我们首先创建以下测试表并填充 3000条记录。创建表并填充数据的 T-SQL 脚本如下：

CREATE TABLE ExPlanOperator_P2
( ID INT IDENTITY (1,1),
  EmpFirst_Name VARCHAR(50),
  EmpLast_name VARCHAR(50),
  EmpAddress VARCHAR(MAX),
  EmpPhoneNum varchar(50)
 )
 GO
 INSERT INTO ExPlanOperator_P2 VALUES ('AB','BA','CB','123123')
 GO 1000
  INSERT INTO ExPlanOperator_P2 VALUES ('DA','EB','FC','456456')
 GO 1000
 INSERT INTO ExPlanOperator_P2 VALUES ('DC','EA','FB','789789')
 GO 1000

行标识查找运算符(RID Lookup Operator)

假设我们在 ExPlanOperator_P2 表的 EmpFirst_Name 列上有一个非聚集索引(Non-Clustered index )，可以使用下面的 CREATE INDEX T-SQL 语句创建的：

CREATE INDEX IX_ExPlanOperator_P2_EmpFirst_Name ON ExPlanOperator_P2 (EmpFirst_Name)

如果运行以下 SELECT 语句以检索有关具有特定 EmpFirst_Name 值的所有员工的信息，并选择显示实际 SQL Server 执行计划：

SELECT * FROM ExPlanOperator_P2 WHERE EmpFirst_Name = 'BB'

查看执行查询后生成的 SQL Server 执行计划，我们会看到 SQL Server 查询优化器将使用 非聚集索引(Non-Clustered index ) 来查找 EmpFirst_Name 值等于 ‘BB’ 的所有员工，而无需扫描整个表。另一方面，SQL Server 引擎无法从该非聚集索引获取所有的请求数据，因为查询要求得到目标记录的所有列。但是 非聚集索引(Non-Clustered index ) 仅包含键列值和指向该键值对应的数据记录在基表位置的指针。

要深入了解非聚集索引主题，请参阅设计有效的 SQL Server 非聚集索引一文。

由于我们创建的测试表不包含聚集索引(Clustered index)，所以该表将被视为一个堆表(Heap Table)，SQL Server 引擎在保存数据时不会对数据进行排序。

*有关堆表(Heap Table)结构的详细信息，请查看SQL Server 表结构概述。

因此，SQL Server 引擎将使用来自非聚集索引的指针来查找数据，该指针指向基表上其余列的位置，使用嵌套循环运算符(Nested Loops )将索引查找数据与从RID 查找运算符(RID Lookup)（也称为行识别运算符）检索到的数据集连接起来。SQL Server 执行计划的示意图如下：

RID 是一个行定位器，其中包含有关该记录位置的信息，例如数据库文件、页面、Slot Number等有助于快速识别行位置的信息。如果将鼠标移动到执行计划上的 RID Lookup 查看该运算符的工具提示。将可以在输出列表中看到由该运算符返回的数据列的列表，因为这些列不在非聚集索引中，需要通过再次查找获取。工具提示的示意图如下：
RID 查找运算符-工具提示

如果再深入查看执行计划中的行标识查找运算符(RID Lookup)，您会发现该运算符的执行成本在执行计划的整体执行成本中占了较大比重，在我们的示例中为 50%。这是因为将行标识查找运算符(RID Lookup)与嵌套循环操作组合之后会被执行多次，从而产生大量的额外 I/O 开销。在处理记录较少时，额外的I/O开销可以忽略。但是涉及到大量记录时，需要对该查询进行优化。可以通过重写查询限制检索的数据列或为该查询创建 覆盖索引(Covering Index ) 的方式进行优化。如果通过创建覆盖索引(Covering Index ) 消除了 RID 查找，则此 SQL Server 执行计划中的嵌套循环运算符也不需要了。

如果想深入了解覆盖索引概念，请参考文章使用不同的 SQL Server 索引类型。

键查找运算符(Key Lookup Operator)

键查找运算符(Key Lookup)是在上一节中所描述的行标识查找运算符(RID Lookup)在集群状态下的等效运算符。
假设我们在 EmpFirst_Name 表的 ID 列上创建了聚集索引。使用下面的 CREATE INDEX T-SQL 语句：

CREATE CLUSTERED INDEX IX_ExPlanOperator_P2_ID on ExPlanOperator_P2 (ID)

再次运行先前的 SELECT 语句来检加粗样式索有关具有特定 EmpFirst_Name 值的所有员工的信息：

SELECT * FROM ExPlanOperator_P2 WHERE EmpFirst_Name = 'BB'

检查执行查询后生成的 SQL Server 执行计划，您会注意到 SQL Server 引擎对非聚集索引执行了查找操作，以检索 EmpFirst_Name 列值等于 ‘BB’ 的所有员工。但同样，并非所有列的数据都可以从非聚集索引中检索到。因此，SQL Server 引擎将使用非聚集索引上的数据指针来获取其余数据，这些指针指向基础表中的其余列。由于该表是一个聚集表，具有对该表的数据进行排序的聚集索引，所以非聚集索引指针将指向聚集索引而不是指向基础表。因为 SQL Server 引擎无法在一次检索中检索所有数据行，所以它基于在上一步键查找(Key Lookup)中获取的结果数据，通过**嵌套循环运算符(Nested Loops )与索引查找运算符(Index Seek)**的结合操作来获取其余的列的数据。换句话说，SQL Server 引擎将使用聚集索引键作为参考，使用存储在非聚集索引中的聚集键值来查找存储在聚集索引中的数据。详细的SQL Server 执行计划如下：
键查找运算符

与 行标识查找(RID Lookup) 一样，键查找(Key Lookup) 运算的执行成本也非常高，因为它需要额外的 I/O 开销。具体开销取决于记录的数量。此外，如果执行计划中存在了键查找(Key Lookup) 运算符则表明关联数据需要覆盖索引或聚集索引。并且可以改善索引来消除**键查找(Key Lookup)**和 **嵌套循环(Nested Loops )**运算符，从而提升查询的性能。例如，如果我们重新创建非聚集索引，并在新索引中包含所有必需的列。创建索引的 T-SQL 语句如下：

CREATE INDEX IX_ExPlanOperator_P2_EmpFirst_Name ON ExPlanOperator_P2 (EmpFirst_Name) INCLUDE (ID,EmpLast_name, EmpAddress, EmpPhoneNum) WITH (DROP_EXISTING = ON)

接下来再次运行前面的 SELECT 语句。在新的执行计划中没有再使用**键查找(Key Lookup)**和 **嵌套循环(Nested Loops )**运算符。因为 SQL Server 引擎可以通过查找非聚集索引来获取所有请求的数据。详细的执行计划图如下：
键查找运算(改善后)

排序运算符(Sort Operator)

假设我们运行以下 SELECT 语句，该语句返回 ExPlanOperator_P2 表中具有特定 EmpFirst_Name 值的员工列表，使用 ORDER BY 子句按 EmpLast_Name 列值降序排序：

SELECT * FROM ExPlanOperator_P2 WHERE EmpFirst_Name = 'BB' ORDER BY EmpLast_name desc

查看执行查询后生成的SQL Server执行计划。首先我们看到SQL Server引擎通过索引查找(Index Seek) 操作在非聚集(Non-Clustered) 索引上检索请求的数据，然后**索引查找(Index Seek)操作的输出会传递给 排序运算(Sort )，在排序运算(Sort)**中在按照 ORDER BY 子句中的指定对数据进行排序处理。如果未指定排序方向，则使用默认的 ASC 排序方向。这个例子的详细执行计划如下：
排序运算符

如果移动鼠标指向 排序(Sort) 运算符，您将看到 排序(Sort ) 运算符的输出与输入列相同，但按指定列排序。详细的提示内容如下图：
排序运算-提示

另外从 SQL Server 执行计划中可以看出，**排序(Sort)**运算是一种开销很大的运算。在上面的例子中，**排序(Sort)**运算的成本是总查询成本的 78%。这个成本是主要是由于 ORDER BY 子句中指定的列上没有定义索引。因此，您需要始终考虑是否真的需要这个排序操作，如果不是必要的排序，最好去掉。如果排序是必要的，那就在需要排序的列上建立索引以获取一个有序的数据副本，从而消除 **排序(Sort)**运算导致的额外开销。

请继续关注下一篇文章，我们将在其中讨论第三组 SQL Server 执行计划运算符。