SQL Server 执行计划(6) - 执行计划运算符详解3

在本系列的前几篇文章中，我们介绍了表扫描(Table Scan)、聚集索引扫描(Clustered Index Scan)、聚集索引查找(Clustered Index Seek)、非聚集索引查找(Non-Clustered Index Seek)、RID 查找(RID Seek)、**键查找(Key Lookup)和排序(Sort)**执行计划运算符。在本文中，我们将介绍这些 SQL 执行计划运算符的第三组。

我们先创建一个包含 3K 记录的测试表，以在本文的示例中使用它。创建表的T-SQL 如下：

CREATE TABLE ExPlanOperator_P3 (
  ID INT IDENTITY(1, 1)
  ,STD_Name VARCHAR(50)
  ,STD_BirthDate DATETIME
  ,STD_Address VARCHAR(MAX)
  ,STD_Grade INT
  )
GO
 
INSERT INTO ExPlanOperator_P3
VALUES (
  'AA'
  ,'1998-05-30'
  ,'BB'
  ,93
  ) GO 1000
 
INSERT INTO ExPlanOperator_P3
VALUES (
  'CC'
  ,'1998-10-13'
  ,'DD'
  ,78
  ) GO 1000
 
INSERT INTO ExPlanOperator_P3
VALUES (
  'EE'
  ,'1998-06-24'
  ,'FF'
  ,85
  ) GO 1000

聚合运算符—流聚合(Stream Aggregate)

聚合(Aggregate)运算符主要用于通过对聚合列的值进行分组来计算查询中的聚合表达式。聚合表达式包括 MIN、MAX、COUNT、AVG、SUM 运算。下面的 SELECT 语句，用来检索获得最高分的前 10 名学生。查询语句使用 了MAX 聚合函数聚合学生的成绩以获得最高成绩来实现。如果需要这些优秀学生的所有信息，需要在 GROUP BY 子句中添加所有需要获取的数据列。查询语句的详细如下：

SELECT TOP 10 ID
  ,STD_Name
  ,STD_BirthDate
  ,STD_Address
  ,MAX(STD_Grade)
FROM ExPlanOperator_P3
GROUP BY ID
  ,STD_Name
  ,STD_BirthDate
  ,STD_Address

检查执行查询后生成的 SQL 执行计划，可以看到看到 SQL Server 引擎先从表中检索数据行，然后根据 GROUP BY 子句中指定的列对这些值进行排序，并使用最快的聚合方法聚合这些值，这就是流聚合运算。流聚合(Stream Aggregate) 运算很快，因为它要求在聚合这些值之前根据 GROUP BY 子句中指定的列对行进行排序。如果行没有在 查找(Seek) 或 扫描(Scan) 运算中排序，在聚合运算之前,SQL Server引擎将强制使用排序 (Sort)运算进行数据的预排序。详细的执行计划如下：

**流聚合(Stream Aggregate)**运算符的另一个值得注意的示例是 AVG 聚合函数的执行顺序，在计算AVG 值之前，会先计算聚合列的 SUM 值和 COUNT 值并将值存储在 内部临时变量Expr1004 和 Expr1005 中。详细信息如下：

计算标量运算符(Compute Scalar Operator)

**计算标量(Compute Scalar)**操作用于通过执行标量计算操作从现有存储值获取新的输出值。这些标量计算包括标量值的转换或串联。

让我们运行下面的 SELECT T-SQL 语句来生成一个描述每个学生成绩的句子。

SELECT STD_Name + '_ has achieved _ ' + cast(STD_Grade AS VARCHAR(50)) AS STD_Result
FROM ExPlanOperator_P3

从执行查询后生成的执行计划中可以看到，SQL Server 引擎使用**计算标量(Compute Scalar)**运算符对两个指定列执行连接以返回新的计算值。详细如下图所示：

从上面的 SQL 执行计划可以看出，计算标量(Compute Scalar)**操作并不是一个开销很大的操作，它的开销仅为我们查询总权重的 2%。

连接运算符(Concatenation Operator)

连接(Concatenation) 运算符按顺序获取一组或多组数据，并返回每个输入数据集中的所有记录。此运算符最典型的示例之一就是 UNION ALL T-SQL 语句。让我们执行一下下面的查询示例。

SELECT *
FROM ExPlanOperator_P3 AS P1
 
UNION ALL
 
SELECT *
FROM ExPlanOperator_P3 AS P2

查看执行查询后生成的执行计划，会看到两条SELECT语句返回的结果被连接(Concatenation) 运算符连接起来，合并成了一个结果集。执行计划的详细如下图：

断言运算符(Assert Operator)

SQL Server断言(Assert) 运算符用于验证插入的值是否满足先前在表上定义的 CHECK 或 FOREIGN KEY 约束。假设我们在演示表上定义了以下约束以确保只有满足要求的值才能插入 STD_Grade 列：

ALTER TABLE ExPlanOperator_P3 ADD CONSTRAINT CK_Grade_Positive CHECK (STD_Grade >0)

如果尝试执行以下 INSERT 语句

INSERT INTO ExPlanOperator_P3 VALUES ('GG','1998-01-28','HH',74)

在执行查询后生成的 SQL 执行计划中可以看到，SQL Server 引擎使用 断言(Assert) 运算符验证为该学生插入的成绩是否满足定义的 CHECK 约束 执行计划详细如下：

哈希匹配联接运算符(Hash Match Join Operator)

对两个表做连接(Join)操作时，SQL Server 引擎将表的数据划分为大小相同的类别，称为存储区，以便快速访问这些数据。这种数据结构称为哈希表(Hash Table)。它使用一种算法来处理数据并将其均匀分布存储。这种算法称为散列函数(Hash Function)。

我们先通过以下T-SQL创建一个新表用于保存学生的缺席信息：

CREATE TABLE ExPlanOperator_JOIN (
  STD_ID INT
  ,STD_AbsenceDays INT
  )
 
INSERT INTO ExPlanOperator_JOIN
VALUES (
  1
  ,5
  ) GO 100
 
INSERT INTO ExPlanOperator_JOIN
VALUES (
  10
  ,2
  ) GO 100

之后，我们将运行以下 SELECT 语句，将基本 Students 表与 Absence 表连接起来：

SELECT STD_Name
  ,STD_Grade
  ,STD_AbsenceDays
FROM ExPlanOperator_P3 P3
INNER JOIN ExPlanOperator_JOIN AB ON P3.ID = AB.STD_ID

从执行查询后生成的执行计划中可以看到，SQL Server引擎从两个连接的表中读取数据后，使用了哈希匹配联接(Hash Match Join) 运算符。在哈希匹配联接处理中，先用小的数据填充哈希表，这个过程被称为Probe table，然后处理第二个大表，这个过程被称为Build table。根据哈希表的值，可以加速对请求数据的访问。详细的执行计划如下图：

哈希匹配聚合运算符(Hash Match Aggregate Operator)

哈希匹配聚合(Hash Match Aggregate) 运算符用于处理未使用索引排序的大表。它在内存中建立一个哈希表，为每条记录计算一个哈希值，然后扫描所有其他记录以获取该哈希键。如果该值不存在于哈希表中，它将在该哈希表中创建一个新条目。这样，SQL Server引擎就会保证每组数据只有一条记录。

让我们运行下面的 SELECT 语句，该语句返回每个 ID 值的数目。

SELECT ID
  ,COUNT(*)
FROM ExPlanOperator_P3
GROUP BY ID

从执行查询后生成的SQL执行计划可以看出，SQL Server引擎会使用哈希匹配聚合(Hash Match Aggregate) 运算符来执行和加速COUNT聚合操作，如下图：

合并联接运算符(Merge Join Operator)

合并联接(Merge Join) 运算符与可以不同类型的 JOIN 操作一起使用，但适用的前提是两个 JOIN 数据集已经根据连接条件字段进行了排序。合并联接(Merge Join) 运算符将同时从两个输入数据集中读取数据行，对两边的数据行进行比较然后返回匹配的结果。

让我们运行以下查询，将 ExPlanOperator_P3 表与其自身连接起来:

SELECT *
FROM ExPlanOperator_P3 E1
INNER JOIN ExPlanOperator_P3 E2 ON E1.ID = E2.ID

从执行查询后生成的 SQL 执行计划中可以看到，SQL Server引擎将扫描每个输入。因为该表尚未创建索引，所以先根据 ID 列对数据进行排序，然后将通过快速合并操作讲两个输入数据连接起来。详细执行计划如下：

尽管 合并联接(Merge Join) 运算符虽然连接了已排序的表，但仍然存在额外开销，因为两个输入数据集都将加载到内存中以进行比较。合并联接(Merge Join)运算符的成本是两个数据集输入操作成本的总和。

嵌套循环联接运算符(Nested Loops Join Operator)

嵌套循环(Nested Loops Join) 运算符用于循环执行上层输入（也称为外部输入）与下层输入（也称为内部输入）连接。对于每一条外部输入记录，内部需要执行次数为与该外部输入匹配的记录的件数。SQL Server 查询优化器仅在外部输入表较小且内部输入表在连接键上创建索引时才会使用 嵌套循环连接(Nested Loops Join) 运算符。

假设我们已经在我们的演示表上创建了以下两个索引，如下所示：

CREATE INDEX IX_ExPlanOperator_P3_ID ON ExPlanOperator_P3 (ID)
GO
 
CREATE INDEX IX_ExPlanOperator_JOIN ON ExPlanOperator_JOIN (
  STD_ID
  ,STD_AbsenceDays
  )
  WITH (DROP_EXISTING = ON)

然后运行下面的 SELECT 语句，通过将两个演示表连接在一起，返回缺课超过几天的学生：

SELECT E1.ID
FROM ExPlanOperator_P3 E1
INNER JOIN ExPlanOperator_JOIN E2 ON E1.ID = E2.STD_ID
WHERE E2.STD_AbsenceDays > 4

从执行查询后生成的 SQL 执行计划中，可以看到 SQL Server引擎使用嵌套循环(Nested Loops Join) 运算符连接两个表，但对外部输入只了执行一次 索引扫描(Index Scan) 操作，然后内部操作 索引查找(Index Seek) 执行100次。详细执行计划如下图：

虽然在上面的示例中嵌套循环运算符的成本占查询的整体权重比相对较低。但需要注意的是该运算符的成本高度依赖于外部输入表的数据量和内部输入表的数据量的乘积的大小。

请继续关注我们将讨论第四组 SQL Server 执行计划运算符的下一篇文章。

系列目录

SQL Server 执行计划(1) - 概述
SQL Server 执行计划(2) - 如何查看执行计划
SQL Server 执行计划(3) - 如何分析图形执行计划
SQL Server 执行计划(4) - 执行计划运算符详解1
SQL Server 执行计划(5) - 执行计划运算符详解2
SQL Server 执行计划(6) - 执行计划运算符详解3
SQL Server 执行计划(7) - 执行计划运算符详解4
SQL Server 执行计划(8) - 使用执行计划进行查询性能调优
[SQL Server 执行计划(9) - 保存和比较执行计划]