SQL Server 执行计划2--函数计算

SORT（排序）

排序操作资源消耗较高的操作，sort操作是占用内存的操作，当内存不足时会占用tempdb。

-- ProductID 不是索引
SET STATISTICS IO, TIME ON
select  ProductID  from dbo.SalesOrderDetail
order by ProductID

-- ProductID 为非聚集索引
SET STATISTICS IO, TIME ON
select  ProductID  from dbo.SalesOrderDetail
order by ProductID

Hash Match 聚合

Hash Match（哈希匹配）聚合是一种高效的聚合算法，适用于输入数据没有预排序或分组键没有索引的情况。

Hash Match 聚合的示例

假设有一个表 Sales，包含 ProductID 和 Quantity 列。我们希望计算每种产品的总销售量。数据如下：

ProductID	Quantity
1	10
2	15
1	20
3	8
2	5

执行以下查询：

SELECT
    ProductID,
    SUM(Quantity) AS TotalQuantity
FROM
    Sales
GROUP BY
    ProductID;

在执行计划中，SQL Server 可能会选择使用 Hash Match 聚合算法来执行这个聚合操作。Hash Match 聚合的执行过程如下：

1. 构建阶段（Build Phase），遍历 Sales 表，根据 ProductID 计算哈希值并分配到不同的哈希桶：

• 记录 (1, 10) 和 (1, 20) 分配到同一个哈希桶。
• 记录 (2, 15) 和 (2, 5) 分配到同一个哈希桶。
• 记录 (3, 8) 分配到一个单独的哈希桶。

2. 聚合阶段（Aggregate Phase），对每个哈希桶中的记录进行聚合：

• 对于 ProductID = 1 的哈希桶，计算总销售量：10 + 20 = 30。
• 对于 ProductID = 2 的哈希桶，计算总销售量：15 + 5 = 20。
• 对于 ProductID = 3 的哈希桶，计算总销售量：8。

3. 输出结果，输出每个 ProductID 及其对应的总销售量：

• ProductID = 1，TotalQuantity = 30
• ProductID = 2，TotalQuantity = 20
• ProductID = 3，TotalQuantity = 8

Hash Match 聚合的执行逻辑

1. 构建阶段（Build Phase）：

• 遍历输入数据集，根据分组键计算每个记录的哈希值。
• 根据哈希值将记录分配到不同的哈希桶（哈希表中的槽位）。
• 每个哈希桶中存储具有相同哈希值的记录，并且根据分组键进行分组

2. 聚合阶段（Aggregate Phase）：

• 对每个哈希桶中的记录进行聚合操作（如 SUM、COUNT、AVG 等）。
• 聚合结果存储在哈希表中。

3. 输出结果：

• 聚合操作完成后，输出每个分组键及其对应的聚合结果。

Hash Match 聚合的优缺点

• 优点：
  不需要输入数据排序，因此在数据未排序且数据量较大的情况下非常高效。
  能够处理大规模数据集并行化。
• 缺点：
  在构建哈希表时可能需要消耗大量内存。
  当数据量特别大时，可能需要将哈希表部分存储到磁盘，导致性能下降。

Hash Match 聚合的测试

--ProductID 不是索引
SET STATISTICS IO, TIME ON
select  ProductID,count(*)  from dbo.SalesOrderDetail
group by ProductID

-- ProductID 非聚簇索引
SET STATISTICS IO, TIME ON
select  ProductID,count(*)  from dbo.SalesOrderDetail
group by ProductID

-- ProductID 不是索引
SET STATISTICS IO, TIME ON
select  distinct ProductID from dbo.SalesOrderDetail


-- ProductID 非聚簇索引
SET STATISTICS IO, TIME ON
select  distinct ProductID from dbo.SalesOrderDetail

Stream Aggregate（流聚合）

Stream Aggregate（流聚合）是一种高效的聚合算法，适用于输入数据已经按分组键排序的情况。Stream Aggregate 的执行逻辑依赖于输入数据的排序，因此常用于排序后的数据集或者在有索引支持的情况下。

Stream Aggregate 的示例

假设有一个表 Sales，包含 ProductID 和 Quantity 列，ProductID 已排序。我们希望计算每种产品的总销售量。数据如下：

ProductID	Quantity
1	10
1	20
2	15
2	5
3	8

执行以下查询：

SELECT
    ProductID,
    SUM(Quantity) AS TotalQuantity
FROM
    Sales
GROUP BY
    ProductID
ORDER BY
    ProductID;

Stream Aggregate 的执行过程如下：

1. 初始化：当前组为空，TotalQuantity 为 0。
2. 遍历第一行：读取 ProductID = 1，Quantity = 10。当前组为 ProductID = 1，TotalQuantity 为 10。
3. 遍历第二行：读取 ProductID = 1，Quantity = 20。当前组为 ProductID = 1，TotalQuantity 为 30。
4. 遍历第三行：读取 ProductID = 2，Quantity = 15。分组键变化，输出结果 ProductID = 1，TotalQuantity = 30。当前组为 ProductID = 2，TotalQuantity 为 15。
5. 遍历第四行：读取 ProductID = 2，Quantity = 5。当前组为 ProductID = 2，TotalQuantity 为 20。
6. 遍历第五行：读取 ProductID = 3，Quantity = 8。分组键变化，输出结果 ProductID = 2，TotalQuantity = 20。当前组为 ProductID = 3，TotalQuantity 为 8。
7. 结束：输出最后一个组的结果 ProductID = 3，TotalQuantity = 8。

Stream Aggregate 的执行逻辑

1. 初始化：

• 创建一个空的结果集，用于存储聚合结果。
• 初始化聚合函数（如 SUM、COUNT、AVG 等）的内部状态

2. 遍历输入数据：

• 逐行读取输入数据。假设输入数据已经按分组键排序

3. 分组和聚合：

• 当读取第一行数据时，将当前行的分组键作为当前组，并开始聚合操作。
• 对于每一行数据，如果分组键与当前组相同，则更新聚合函数的内部状态（例如，SUM 就累加当前行的值）。
• 如果分组键发生变化，则表示当前组已经处理完毕：

• 将当前组的聚合结果添加到结果集中。
• 将新行的分组键作为新的当前组，重新初始化聚合函数的内部状态

4. 结束
   当所有行都处理完毕后，最后一个组的聚合结果也被添加到结果集中。

Stream Aggregate 的优缺点

• 优点：
  高效：当输入数据已经排序时，Stream Aggregate 是非常高效的，因为它只需要一次顺序扫描。
  内存使用少：由于数据按分组键排序，可以逐行处理并及时输出结果，不需要将所有数据加载到内存中。
• 缺点：
  排序依赖：Stream Aggregate 依赖输入数据的排序。如果数据未排序，可能需要先执行排序操作，增加了额外的开销。
  适用性有限：适用于输入数据已经排序或有索引支持的情况，对于未排序的大规模数据集可能不合适。

Stream Aggregate 的测试

-- 按聚集索引排序直接进行的流聚合
select count(*) from dbo.SalesOrderDetail

--ProductID不是索引时，对ProductID排序去重后再进行流聚合
SET STATISTICS IO, TIME ON
select  count(distinct ProductID) from dbo.SalesOrderDetail

--ProductID 是非聚簇索引，不再进行排序去重，进行两次流聚合
SET STATISTICS IO, TIME ON
select  count(distinct ProductID) from dbo.SalesOrderDetail