SQL 作为面向集合的语言

引言

SQL 是一种面向集合的语言。能够一步处理所有行是很好的。逐行处理然后移动到下一行再做同样的事情则不那么好。这是经典的编程方式，在值存储在数组或列表中时很有用。但 SQL 有自己的表和索引，并希望找到优化代码的最佳方法。所以我们必须使用 SQL 的面向集合的特性。我们从一个基于行的示例开始，然后逐步将其代码更改为面向集合的版本。

示例

请看下面的示例

给定一个包含运动员或书籍的表 tbl_rangeList。该表有一个 ID，一个 NAME 和该对象的 RANGE。现在，运动员停止运动，书籍不再可用，因此一些行需要从列表中删除。所有要删除的 ID 都可能列在一个名为 tbl_toRemove 的表中。而——这是任务——您必须更正这些范围。

示例表 tbl_rangeList

ID	名称	range
1055	Mueller	1
7138	史密斯	2
916	John Public (*)	3
3024	Keller	4
6211	Jeanni Public (*)	5
4809	O'Neill	6

标记为 (*) 的两项需要删除，因此结果应该是

ID	名称	range
1055	Mueller	1
7138	史密斯	2
3024	Keller	3
4809	O'Neill	4

第一个解决方案：基于游标，逐行计算范围

该示例是用 Transact-SQL 编写的，但也可以用带有 mySql 连接的 PHP 编写。SQL 新手可能会这样写：

Declare @id int,
    @range int

Declare cs_rangeList Cursor
    Local Fast_Forward
For
Select A.Id, A.range From tbl_rangeList

Open cs_rangeList

Fetch Next From cs_rangeList Into @id, @range

While (@@Fetch_Status = 0)
Begin
    -- check, if the row has to remove
    If ((Select Count(*) From tbl_toRemove As A
        Where A.Id = @id) = 0)
    Begin
        -- compute the new range
        Select @new_range = Count(*)
        From tbl_rangeList As A
        Where A.range <= @range
        And A.Id Not In
            (Select B.Id From tbl_toRemove As B)

        -- and update it
        Update tbl_rangeList
            Set range = @new_range
        Where Id = @Id
    End
    Else
        -- remove it
        Delete From tbl_rangeList
        Where Id = @Id

    Fetch Next From cs_rangeList Into @id, @range
End
Close cs_rangeList
Deallocate cs_rangeList

现在 SQL 新手很高兴，对自己说：“太棒了，表就像数组一样，我是一个数组专家”——并且不理解为什么在处理 10,000 行的表时速度如此之慢。

这个解决方案很糟糕，它是一种创就业措施。但它确实有效，并且有助于理解该做什么。

第二个解决方案：基于游标，带有排序、额外索引和先删除所有过时行

• 第一个想法：在做其他事情之前删除所有行。
• 第二个想法：对游标进行排序。所以第一个范围是 1，将值放入一个变量并递增它。

Declare @id int,
    @new_range int

Delete From tbl_rangeList
Where Id In
    (Select Id From tbl_toRemove)

Declare cs_rangeList Cursor
    Local Fast_Forward
For
Select A.Id
From tbl_rangeList As A
-- new: Order the list
Order By A.range

Open cs_rangeList

Fetch Next From cs_rangeList Into @id
-- new: begin with 1
Set @new_range = 1

While (@@Fetch_Status = 0)
Begin
    Update tbl_rangeList
        Set range = @new_range
    Where Id = @id

    -- great computing
    Set @new_range = @new_range + 1

    Fetch Next From cs_rangeList Into @id
End
-- close and deallocate the cursor - skipped

这有所改进，但它仍然是面向行的，而不是面向集合的。如果它是过程式语言的一部分，并且表是数组，这可能是一个不错的解决方案。但 SQL——不行，那是一条死胡同。

但为什么 SQL 不行呢？SQL 代码会被解析。但之后并不清楚表的扫描顺序，ON 子句的计算方式，WHERE 子句是应生成完整的表扫描还是应使用索引。因此，必须创建一个执行计划。该计划将被编译、缓存并稍后重用。所以——这是与其它编程语言的区别——相同的存储过程或批处理可能会产生完全不同的执行计划。但如果代码是面向行的，执行计划将几乎（可能：大部分）时间相同，不存在内部优化。因此，我们必须创建远离这种一步一步操作的 SQL 代码，以便优化器可以在 SQL 代码和表之间放置一个层——执行计划。

第三个解决方案：面向集合，无游标

现在，我们将删除游标。我们必须一步计算所有行的新的范围。所以，让我们创建一个自连接，ON 部分是 B.range >= C.range，并计算所有行。结果就是该 ID 的新范围。

Select B.Id, Count(*) As new_range
From tbl_rangeList As B Inner Join tbl_rangeList As C
On B.range >= C.range
Group By B.Id

这会产生以下输出：

ID	new_range
1055	1
7138	2
3024	3
4809	4

每个 Id 都列出了其新范围。此表可用作连接中的子查询。

Delete From tbl_rangeList
Where Id In
    (Select Id From tbl_toRemove)

Update tbl_rangeList

    Set range = D.new_range

From tbl_rangeList As A Inner Join

    (Select B.Id,
        Count(*) As new_range
    From tbl_rangeList As B Inner Join
        tbl_rangeList As C
    On B.range >= C.range
    Group By B.Id) As D

On A.Id = D.Id

内部查询包含所有新的范围，并允许在 update 命令中使用。但有一个问题：所有行都被更新了，而不仅仅是那些范围发生变化的行。

第四个解决方案：跳过范围未更改的行 - 使用 HAVING

使用 Having 子句，我们可以测试新范围是否小于旧范围。子查询仅收集这些行。

Update tbl_rangeList

    Set range = D.new_range

From tbl_rangeList As A Inner Join

    (Select B.Id,
        Count(*) As new_range
    From tbl_rangeList As B Inner Join
        tbl_rangeList As C
    On B.range >= C.range
    Group By B.Id, B.range
    Having Count(*) < B.range) As D

On A.Id = D.Id

现在内部表只包含需要范围更新的行。好的，这看起来好多了——但还不够好。如果我们有 10,000 行，而要删除的第一行的范围是 9,523，那么我们必须计算 9,522 行的新范围并将这些结果丢弃。但这允许在第一行删除后查看并使用此信息通过 WHERE 子句来缩小子查询的范围。WHERE 更好——因为它是在 GROUP BY 之前完成的。

第五个（也是最后一个）解决方案：跳过范围未更改的行 - 在 WHERE 中使用最小值

Declare @i int

Select @i = Min(A.range)
From tbl_rangeList As A
    Inner Join tbl_toRemove As B
On A.Id = B.Id

-- @i holds now the lowest range to remove

Delete From tbl_rangeList
Where Id In
    (Select B.Id From tbl_toRemove As B)


Update tbl_rangeList

    Set range = D.new_range

From tbl_rangeList As A Inner Join

    (Select B.Id,
        Count(*) As new_range
    From tbl_rangeList As B Inner Join
        tbl_rangeList As C
    On B.range >= C.range
    Where B.range >= @i
    Group By B.Id) As D

On A.Id = D.Id

现在，Count(*) 只为需要的行执行。优化器可以计算最佳执行计划，它会检查直接扫描表是否更好，还是使用索引。我们的 SQL 代码确实是面向集合的：第一个命令存储最小范围，第二个删除所有过时的行，第三个用一个命令完成工作。

感谢您的阅读——并加以运用。

本文的德文版可以在 SQL 作为面向集合的语言 找到。Group By 和子查询的示例在 SQL 教程 中有很多。