首页 / 知识

关于sql：为什么基于关系集的查询比游标更好？

2023-04-13 00:07:00

Why are relational set-based queries better than cursors?

在以TSQL或PLSQL之类的方式编写数据库查询时，我们通常可以选择使用游标遍历行以完成任务，或设计一条一次性执行同一工作的SQL语句。

此外，我们可以选择简单地将一大组数据拉回到我们的应用程序中，然后使用C#或Java或PHP或其他东西逐行处理它。

为什么使用基于集合的查询更好？这种选择背后的理论是什么？什么是基于游标的解决方案及其等效关系的良好示例？

我知道的主要原因是引擎可以通过在多个线程中运行基于集合的操作来优化它们。例如，考虑一下快速排序-您可以将要排序的列表分成多个"块"，然后在各自的线程中分别对它们进行排序。 SQL引擎可以在一个基于集合的查询中对大量数据执行类似的操作。

执行基于游标的操作时，引擎只能顺序运行，并且该操作必须是单线程的。

除了上面的"让DBMS进行工作"(这是一个很好的解决方案)外，还有其他一些很好的理由将查询保留在DBMS中：

(主观上)更容易阅读。稍后查看代码时，您是希望尝试使用循环和事物来解析复杂的存储过程(或客户端代码)，还是希望查看简洁的SQL语句？
它避免了网络往返。为什么将所有数据推给客户端，然后再推回去呢？如果不需要，为什么要破坏网络？
这很浪费。您的DBMS和应用服务器将需要缓冲部分/全部数据以对其进行处理。如果您没有无限的内存，则可能会调出其他数据。为什么从内存中踢出可能重要的东西来缓冲几乎没有用的结果集？
你为什么不呢？您购买了(或正在使用)高度可靠，非常快速的DBMS。你为什么不使用它？

基于集合的查询通常(通常)更快，原因是：

他们有更多信息供查询优化器优化

他们可以批量从磁盘读取

回滚，事务日志等涉及的日志较少。

采取更少的锁，这减少了开销

基于集合的逻辑是RDBMS的重点，因此已经对其进行了大量优化(通常以过程性能为代价)

将数据拉到中间层进行处理可能会很有用，因为它消除了DB服务器的处理开销(这是最难扩展的事情，通常也可以做其他事情)。同样，您通常在中间层没有相同的间接费用(或收益)。诸如事务日志记录，内置锁定和阻止之类的东西-有时这些是必要且有用的，而其他时候它们只是浪费资源。

具有过程逻辑和基于集合的示例(T-SQL)的简单游标，它将基于电话交换机分配区号：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

--Cursor
DECLARE @phoneNumber CHAR(7)
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT PhoneNumber FROM Customer WHERE AreaCode IS NULL
OPEN c
FETCH NEXT FROM c INTO @phoneNumber
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @exchange CHAR(3), @areaCode CHAR(3)
SELECT @exchange = LEFT(@phoneNumber, 3)

SELECT @areaCode = AreaCode
FROM AreaCode_Exchange
WHERE Exchange = @exchange

IF @areaCode IS NOT NULL BEGIN
UPDATE Customer SET AreaCode = @areaCode
WHERE CURRENT OF c
END
FETCH NEXT FROM c INTO @phoneNumber
END
CLOSE c
DEALLOCATE c
END

--Set
UPDATE Customer SET
AreaCode = AreaCode_Exchange.AreaCode
FROM Customer
JOIN AreaCode_Exchange ON
LEFT(Customer.PhoneNumber, 3) = AreaCode_Exchange.Exchange
WHERE
Customer.AreaCode IS NULL

您想要一些真实的例子。我公司的光标花了40分钟才能处理30,000条记录(有时候我需要更新200,000条记录)。不使用光标就花了45秒完成相同的任务。在另一种情况下，我删除了一个游标，并将处理时间从超过24小时发送到不到一分钟。一个是使用values子句而不是select的插入，另一个是使用变量而不是联接的更新。一个好的经验法则是，如果它是插入，更新或删除，则应寻找一种基于集合的方式来执行任务。

游标有其用途(或代码最初不是它们的用途)，但是在查询关系数据库时(除Oracle进行了优化以使用它们外)，它们应该非常少见。一种可能更快的方法是根据先前记录的值(运行总计)进行计算。甚至应该测试。

使用游标的另一个有限情况是进行一些批处理。如果您尝试以基于集合的方式一次执行太多操作，则可以将该表锁定给其他用户。如果您有一个真正大的集合，则最好将其分解为较小的，基于集合的插入，更新或删除，这些插入，更新或删除不会将锁保持太长时间，然后使用光标在这些集合中运行。

游标的第三种用法是通过一组输入值来运行系统存储的proc。因为这仅限于通常很小的一组，而且任何人都不应干扰系统进程，所以对于管理员来说这是可以接受的事情。我不建议对用户创建的存储过程进行相同的操作，以处理大量的批处理并重用代码。最好编写一个基于集合的版本，因为在大多数情况下性能应胜过代码重用，因此它的性能更好。

我认为，真正的答案是，像编程中的所有方法一样，它取决于哪个更好。通常，基于集合的语言将更加高效，因为这正是它的初衷。游标在两个地方占有优势：

您正在更新数据库中的大数据集，在该数据库中，锁定行是不可接受的(可能在生产时间内)。基于集合的更新可能会将表锁定几秒钟(或几分钟)，而游标(如果写得正确)则不会。光标可以遍历行，一次更新一个行，而您不必担心会影响其他任何行。

使用SQL的优势在于，大多数情况下，优化工作的大部分由数据库引擎处理。借助企业级的db引擎，设计人员竭尽全力确保系统高效地处理数据。缺点是SQL是一种基于集合的语言。您必须能够定义一组数据才能使用它。尽管这听起来很简单，但在某些情况下却并非如此。查询可能是如此复杂，以至于引擎中的内部优化器无法有效地创建执行路径，无法猜测会发生什么...您的具有32个处理器的超级强大的框使用单个线程来执行查询，因为它不知道如何执行其他操作，因此浪费了数据库服务器上的处理器时间，而这通常是多个应用程序服务器中只有一个服务器的原因(所以回到原因1，您会遇到资源争用以及其他需要在数据库服务器上运行的事情)。使用基于行的语言(C#，PHP，JAVA等)，您可以更好地控制发生的情况。您可以检索数据集并强制其执行所需的方式。 (将数据分开以在多个线程上运行等)。大多数时候，它仍然无法像在数据库引擎上运行一样高效，因为它仍然必须访问引擎来更新行，但是当您必须执行1000次计算来更新行时(以及假设您有一百万行)，那么数据库服务器可能会开始出现问题。

如前所述，数据库针对设置操作进行了优化。从字面上看，工程师坐了很长时间，对该数据库进行调试/调整。您优化它们的机会很小。如果您要处理一组数据，则可以使用各种有趣的技巧，例如批处理磁盘读写在一起，缓存，多线程。另外，某些操作的开销成本很高，但是如果您一次对一组数据进行操作，则每条数据的成本较低。如果您一次只工作一行，那么很多这些方法和操作就根本不可能发生。

例如，仅查看数据库的连接方式。通过查看解释计划，您可以看到几种进行联接的方法。最有可能用光标在一个表中一行一行地走，然后从另一张表中选择所需的值。基本上，这就像一个嵌套循环，只是没有紧密的循环(它很可能被编译成机器语言并进行了超级优化)。 SQL Server本身具有多种连接方式。如果对行进行了排序，它将使用某种类型的合并算法；如果一个表较小，则可能会将一个表转换为哈希查找表，并通过从一个表到查找表执行O(1)查找来进行联接。许多DBMS拥有许多联接策略，它们会击败您从游标中的一个表中查找值。

仅查看创建哈希查找表的示例。如果要连接长度为n的两个表和长度为m的表(其中m是较小的表)，则建立表可能是m个操作。每次查找应为恒定时间，因此为n次操作。因此，基本上，哈希联接的效率约为m(设置)n(查找)。如果您自己做，并且假设没有查找/索引，那么对于n行中的每行，您将必须搜索m条记录(平均而言，它等于m / 2个搜索)。因此，基本上，操作级别从m n(一次加入一堆记录)到m * n / 2(通过游标进行查找)。而且操作是简化的。根据游标的类型，获取游标的每一行可能与从第一张表进行另一选择相同。

锁也会杀死您。如果您在表上有游标，则您将锁定行(在SQL Server中，这对于静态游标和forward_only游标而言并不那么严重...但是我看到的大多数游标代码只是打开了一个游标而未指定任何这些选项)。如果您在一组中执行该操作，则这些行仍将被锁定，但时间较短。优化器还可以看到您在做什么，并且可以决定锁定整个表而不是一堆行或页会更有效。但是，如果您逐行进行，则优化器不知道。

另一件事是，我听说在Oracle的情况下，它对执行游标操作进行了超级优化，因此与SQL Server中的Oracle游标相比，基于集合的操作与游标之间的罚款相差无几。我不是Oracle专家，所以不能肯定地说。但是有多个Oracle人士告诉我，游标在Oracle中效率更高。因此，如果您为Oracle牺牲了长子，那么您可能不必担心游标，请咨询您当地薪资较高的Oracle DBA：)

我认为归结为使用数据库是为了使用而设计的。关系数据库服务器经过专门开发和优化，可以最好地响应以集合逻辑表示的问题。

在功能上，游标的惩罚因产品而异。一些(大多数？)rdbmss至少部分构建在isam引擎之上。如果问题合适，并且单板足够薄，则实际上使用游标可能同样有效。但是，在尝试使用dbms品牌之前，这是您应该熟悉的事情之一。

简而言之，在大多数情况下，让数据库为您完成任务更快/更容易。

数据库的生命目的是以设置的格式存储和检索/处理数据，并且要真正快。您的VB.NET/ASP.NET代码可能不如专用数据库引擎那么快。利用这是对资源的明智使用。

真正的答案是去获得Codd E.F. Codd的书之一，并复习关系代数。然后获得有关Big O符号的好书。经过近二十年的IT工作，这就是IMHO，这是现代MIS或CS学位的重大悲剧之一：很少实际研究计算。您知道..."计算机"的"计算"部分吗？结构化查询语言(及其所有超集)只是关系代数的实际应用。是的，RDBMS已经优化了内存管理和读/写，但是对于过程语言也可以这样说。在我阅读本文时，最初的问题不是关于IDE(即软件)，而是关于一种计算方法与另一种计算方法的效率。

即使快速熟悉Big O表示法也将开始阐明为什么在处理数据集时，迭代比声明性语句要昂贵。

基于

set的操作只需完成一次
游标的操作与游标的行集的数量相同

首选在查询中进行工作的想法是，数据库引擎可以通过重新格式化来优化。这就是为什么要对查询运行EXPLAIN来查看数据库实际在做什么的原因。 (例如利用索引，表大小，有时甚至还了解列中值的分布。)

也就是说，要在实际情况下获得良好的性能，您可能必须弯腰或破坏规则。

哦，另一个原因可能是约束：如果在所有更新之后都检查了约束，则将唯一列增加一个就可以了，但是如果一个接一个地进行，则会产生冲突。

游标查询数据库遍历