理解 SQL 中的“基于集合”和“过程式”方法

引言

好的，您知道在开发数据访问例程时，您应该使用“基于集合的方法”而不是“过程式方法”来编写 SQL。但是，这两种方法到底是什么？为什么过程式方法更好？我在网上搜索了答案，但没有找到足够的信息来满足我（虽然我找到了许多技术性解释，但它们只是触及了这个问题，并没有完全解释清楚）。因此，我尝试自己去探索和理解这两种方法之间的实际“诀窍”和差异，以及“基于集合的方法”为何更优的原因。

在本文中，我想通过一些简单的观察和解释与您分享我的想法和发现。除此之外，我还会介绍一些将“过程式 SQL”转换为“基于集合 SQL”的简单技巧，这些技巧可能有助于您编写更好的 SQL :)

请注意，我写本文时以 SQL Server 为例。但这些原则对于任何数据库服务器的实现都基本适用。

使用代码

未经作者许可，本文不得在任何其他地方发布。代码可用于学习目的。

什么是“过程式方法”？

简单来说，过程式方法实际上是我们日常编程生活中习惯使用的“程序化方法”。在这种方法中，我们告诉系统“做什么”以及“如何做”。我们查询数据库以获得结果集，然后使用循环、条件和处理语句编写数据操作和处理逻辑来生成最终结果。运行时会按照我们想要的方式，执行我们想要的操作。

在 T-SQL 中，任何逐行处理结果集的 UDF（用户定义函数）或游标都属于过程式方法。例如，如果您查询数据库以获取结果集，然后使用游标逐行遍历结果集进行进一步处理，那么您就使用了过程式方法。同样，如果您在 SQL 中使用 UDF 来处理结果集中的每一行以计算标量输出，那么您就使用了过程式方法。寻找示例代码？继续阅读..

什么是“基于集合的方法”？

基于集合的方法实际上是一种只允许您指定“做什么”，而不允许您指定“如何做”的方法。也就是说，您只需指定从“数据集”（可以是简单的表/视图，也可以是表/视图的连接）中获取已处理结果的要求，并通过可选的条件进行过滤。有时，获取所需数据的“集合”的规范可能需要使用复杂的连接/子查询/条件 CASE 语句，但最终，结果集是从一个数据集合中获得的。您永远不需要指定“如何”在内部实现数据检索操作。您也永远不需要指定“如何”在内部实现“连接操作”。此外，您也不需要指定如何对行应用过滤条件。数据库引擎会自行决定最佳的算法或处理逻辑来完成这些操作。

例如，以下 SQL 是使用“基于集合的方法”编写的

SELECT ProductName,CategoryName
FROM Product INNER JOIN Category
ON Product.CategoryID = Category.ID 
WHERE Price > 100 

在上面的 SQL 中，“Product INNER JOIN Caterogy ON Product.CategoryID = Category.CategoryID”是显示 ProductName 和 CategoryName 的“数据集”，条件是 Price > 100。您只需指定您的需求和条件，SQL 引擎就会自动完成其余工作以生成结果。

为什么“基于集合的方法”优于“过程式方法”？

坦率地说，数据库的内部执行引擎的设计和优化是为了接收“基于集合的指令”（SQL）作为输入，并以最佳方式（根据许多标准而变化）执行这些指令以产生输出。这就是为什么“基于集合的方法”几乎总是更好的选择。

让我们通过一个例子来理解这一点。

我们经常需要连接两个或多个数据集（例如，两个表）以获得所需的结果集。在连接时，我们只需指定参与的表/视图名称和连接条件。现在，SQL 引擎需要根据我们提供的连接条件来实现实际的“连接操作”。

在 SQL Server 执行引擎中，有三种连接技术。它们是：

• Merge join (合并连接)：最优化的连接算法。当两个表在已排序的索引列上进行连接时发生。在合并连接操作中，SQL Server 对已排序的输入进行一次扫描，并将数据合并在一起。
• Nested loop join (嵌套循环连接)：中等成本的连接算法。当连接子句中的一个表包含少量记录（内表）与另一个参与表（外表）中的记录数量相比时发生。嵌套循环对每个内表行扫描一次，并在外表中查找对应的行。
• Hash join (哈希连接)：成本最高的连接算法。当连接条件中使用大型、未排序、非索引列时发生。SQL Server 引擎通过对每一行的连接属性应用哈希函数来为较小的关系准备一个哈希表。然后，它扫描较大的关系，并通过查找哈希表来找到相关的行。

现在，每当我们为 SQL Server 中的任何 SQL 指定任何连接谓词时，SQL Server 会根据参与的列、数据量、索引结构以及参与列中的值集，来确定并使用最佳的连接算法，并实现逻辑以在内存中执行实际的连接操作。请注意，您不必在 SQL 中指定连接算法的类型。SQL Server 会自行完成，并尽最大努力快速提供结果。

这就是数据库引擎青睐“基于集合”方法的原因。

另一个重要事实是，无论使用“基于集合的方法”编写的任何 SQL 发送到数据库，查询优化器都会先生成一个执行计划，然后执行引擎执行该计划以从物理存储中检索数据并高效地处理输出。也就是说，无论 SQL 语句简单还是复杂，每个 SQL 语句都有一个执行计划树。执行这个单一的执行计划树通常是一个更快的操作。

但是，当我们使用另一个对结果集进行逐行操作的 SQL（可能使用 UDF）来指定我们自己的处理结果集的方式时，数据库引擎必须为每一行执行一个执行计划，即使在执行一个执行计划获得结果集之后。想象一下，一个逐行操作在一个包含 100 万行结果集上执行。在这种情况下，初始数据检索操作需要执行一个执行计划，然后，为了处理每一行，需要另外执行 100 万次执行计划。这就是 UDF 对结果集中的每一行执行时发生的情况。使用 UDF 的额外开销是调用 UDF 时发生的堆栈 I/O 量。

另一方面，如果您使用游标逐行处理结果集，在执行过程中，游标会锁定相应表中的行，并在处理完成后解锁这些行。这涉及大量的服务器资源使用，并且在结果集很大的情况下，会严重降低性能。

这个实验表明，对于最多 1000 行的逐行操作使用 UDF，性能可能在可接受的范围内。但是，随着行数的增加，使用 UDF 会导致性能急剧下降。游标也是如此。

这就是为什么“基于集合的 SQL”始终优于“过程式 SQL”，特别是当要处理的结果集变大时。

好的，明白了。现在告诉我如何摆脱“过程式方法”？

这并不难。只需遵循以下简单技巧并重构一些 SQL。

使用内联子查询替换用户定义函数

假设，对于一个自引用的表 Employee(ID, Name, MgrID)，有一个使用用户定义函数以“过程式方法”编写的查询。该查询输出员工姓名和相应的经理姓名。

这是查询

SELECT Name AS [Employee Name],dbo.fnGetManagerName(MgrID) as [Manager Name] FROM Employee

这里，dbo.fnGetManagerName(MgrID) 是一个 UDF，它返回经理的姓名（也就是同一个Employee表中的另一个员工），如下所示：

ALTER FUNCTION [dbo].[fnGetManagerName](@ID int) RETURNS VARCHAR(50) AS
BEGIN
          --Declare the variable to hold result 
          DECLARE @ManagerName varchar(50)
          --Determine the Employee name by the given ID 
          SELECT @ManagerName = Name FROM Employee WHERE ID = @ID
          --Return the result
          RETURN @ManagerName
END

上面的“过程式 SQL”可以用“基于集合的方法”中的子查询重写，如下所示：

SELECT E.Name AS [Employee Name],
(
    SELECT Name FROM Employee WHERE ID = E.MgrID
) AS [Manager Name] 
FROM Employee E 

在我参与的一个项目中，我们在一个中等规模的 SQL Server 2000 数据库中遇到了一个性能缓慢的存储过程。该 SP 使用大约 20,000 条记录来生成一个结果集。我们需要做的唯一优化就是用一个内联子查询替换一个 UDF（因为所有其他优化都已完成）。相信我，这把总执行时间从 90 秒缩短到了 1 秒！

请注意，上面的查询也可以使用自连接来编写，同样能提供更快的查询结果。

尝试用相关子查询重写您的基于游标的 T-SQL

您可以使用相关子查询来重写基于游标的代码。

通常，您使用游标获取结果集，然后逐行处理以形成所需的结果。这种处理可以被相关子查询（在大多数情况下）取代。

什么是相关子查询？

相关子查询是外部查询必须先执行，然后对于外部查询的每一行，才执行内部查询的子查询。这意味着，在为特定行执行内部查询之前，必须已经处理完外部查询中的该特定行（因此，内部查询与外部查询相关）。

再次查看以下查询（您已经看过了）。这是一个相关子查询

SELECT E.Name AS [Employee Name],
(
    SELECT Name FROM Employee WHERE ID = E.MgrID
) AS [Manager Name] 
FROM Employee E

让我们分解这个查询。外部查询是：

SELECT E.Name as [Employee Name] FROM Employee E

内部查询是：

SELECT Name FROM Employee WHERE ID = E.MgrID

注意内部查询中的 WHERE 子句 (ID = E.MgrID)。为了执行此内部查询，查询处理引擎需要 E.MgrID 已经可用。因此，为了确定整体结果集的每一行，查询的处理方式如下：

1. 从 Employee 表获取“Name”列值（作为员工姓名）和“MgrID”列值。
2. 从 Employee 表中获取“Name”列值（作为经理姓名），条件是 ID = MgrID。

如您所见，为了确定结果集的每一行，SQL 执行引擎必须执行两个不同的 SQL。但是，这个查询的执行比 UDF 和基于游标的查询效率高得多，因为在子查询方式下，SQL Server 会在其执行计划中决定最优化和最佳的实现内部查询的方式（例如，决定实现连接的最佳算法），因此查询执行速度更快（基于集合的方法）。

但是，如果您已经使用游标实现了一些复杂的逐行处理逻辑，并且您认为使用基于集合的方法实现相同的逻辑很困难或几乎不可能，您可以遵循以下方法：

使用表变量替换游标来处理结果集

什么是“表”变量？

在 T-SQL（自 SQL Server 2000 起）中，“表”变量是一种特殊的变量，它与实际表非常相似。但最重要的是，“表”变量几乎 100% 的时间都驻留在内存中（除非表变量本身太大；在这种情况下，表变量可能会驻留在 tempdb 数据库中）。

与临时表相比，使用表变量通常更高效（在内存和执行时间方面），原因如下：

1. 临时表驻留在 tempdb 数据库中，对临时表的操作会产生跨数据库通信。这注定很慢。但表变量大部分是“内存中”的变量，因此表变量的 I/O 速度很快。
2. 操作临时表会产生大量的磁盘活动和资源使用，因为：
• 临时表必须被创建
• 数据必须插入到临时表中
• 通常，临时表必须与物理表连接才能获得结果
• 更新临时表中的数据时必须建立锁
• 临时表必须被删除

但是，操作表变量不需要对资源进行锁定。此外，与临时表相比，向表变量插入数据要快得多，因为没有磁盘 I/O 和跨数据库通信。另外，当相应的 SQL 块超出范围时，表变量也会超出范围。因此，表变量无需删除。所有这些都使表变量成为实现更快的 T-SQL 的绝佳选择。

好了，现在很明显，在大多数情况下，表变量优于临时表。但是，您可以使用表变量来代替游标吗？

是的，您可以。以下是使用表变量处理结果集的示例（这里没有包含使用游标处理结果集的 SQL，因为我不想让您学习游标……哈哈）

--Declare the Table variable 
DECLARE @Elements TABLE
(
    Number INT IDENTITY(1,1), --Auto incrementing Identity column
    ProductName VARCHAR(300) --The string value
)

--Decalre a variable to remember the position of the current delimiter
DECLARE @N INT 

--Decalre a variable to remember the number of rows in the table
DECLARE @Count INT

--Populate the TABLE variable using some logic
INSERT INTO @Elements SELECT Name FROM dbo.Products

--Initialize the looper variable
SET @N = 1

--Determine the number of rows in the Table
SELECT @Count=max(Number) from @Elements

--A variable to hold the currently selected value from the table
DECLARE @CurrentValue varchar(300);

--Loop through until all row processing is done
WHILE @N <= @Count

BEGIN
    --Load current value from the Table
    SELECT @CurrentValue = ProductName FROM @Elements WHERE Number = @N
    --Process the current value
    print @CurrentValue
    --Increment loop counter
    SET @N = @N + 1;
END

我敢打赌，通过用基于表变量的代码替换您可能已经编写的基于游标的代码，您会惊讶于性能的提升。

请注意，您仍然应该尽量避免使用“过程式方法”编写 T-SQL（使用表变量仍然是过程式方法）。但是，如果您出于某种原因确实需要编写自己的结果集处理方式，至少可以使用表变量来避免使用游标。

祝您编写“基于集合”的 SQL 愉快！

历史

• 初始版本：2009 年 3 月 15 日。
• 修改
• 2009 年 3 月 16 日
• 2009 年 3 月 17 日
• 2009 年 3 月 23 日