数据库“游标”(cursor)是什么？怎么用？

最近在做jmeter给sqlserver做压测时，采用单线程做测试时，没有什么明显的问题，而当采用多线程模拟用户并发时，jmeter测试停不下来，并且数据库卡死，无法连接数据库，以至于要把数据库服务重启才行，而测试数据也没有流入数据库。排查后发现问题在于sql存储过程，借这个机会顺便温故一下数据库的基础知识。

一、Jmeter参数设置

1.设置线程组
线程数：并发用户数
Ramp-up时间(秒)：启动所有线程所需要的时长
（1）0---->代表同时并发；
（2）100---->线程数100，时间100秒。代表每隔1s启动一个线程（100/100=1）
循环次数：Loop Count
（1）默认值是1；
（2）2---->线程数100，循环次数2。代表启动200个线程；
（3）勾选“永远”，会一直执行，需要人工停止，一般配合调度器使用。

本次测试参数配置如下

开始时我一直采用以下的存储过程

 
CREATE OR ALTER PROCEDURE dbz_test
AS
BEGIN
    PRINT 'Begin: ' + CONVERT(VARCHAR(19), GETDATE(), 120);
    DECLARE @h_c_id INT, @h_c_d_id INT, @h_c_w_id INT, @h_d_id INT, @h_w_id INT, @h_date DATETIME2(3), @h_amount DECIMAL(6, 2), @h_data VARCHAR(24);
     
    DECLARE source_cursor CURSOR FOR
    SELECT h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data
    FROM bmsql_history;
     
    OPEN source_cursor;
    FETCH NEXT FROM source_cursor INTO @h_c_id, @h_c_d_id, @h_c_w_id, @h_d_id, @h_w_id, @h_date, @h_amount, @h_data;
     
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO bmsql_history_copy (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data)
        VALUES (@h_c_id, @h_c_d_id, @h_c_w_id, @h_d_id, @h_w_id, @h_date, @h_amount, @h_data);
        COMMIT;
         
        FETCH NEXT FROM source_cursor INTO @h_c_id, @h_c_d_id, @h_c_w_id, @h_d_id, @h_w_id, @h_date, @h_amount, @h_data;
    END;
     
    CLOSE source_cursor;
    DEALLOCATE source_cursor;
     
    PRINT 'End: ' + CONVERT(VARCHAR(19), GETDATE(), 120);
END;

这个存储过程的操作是，通过游标锁定bmsql_history_copy 表并逐行地复制插入到bmsql_history_copy1206表里，很显然，当数据量太大时，这样逐行的操作会大大影响性能，降低执行效率。

所以解决办法是，采用下列存储过程，用批量插入来替代逐行插入

CREATE OR ALTER PROCEDURE dbz_test
AS
BEGIN
    PRINT 'Begin: ' + CONVERT(VARCHAR(19), GETDATE(), 120);
 
    INSERT INTO bmsql_history_copy1206 (h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data)
    SELECT h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id, h_date, h_amount, h_data
    FROM bmsql_history;
 
    PRINT 'End: ' + CONVERT(VARCHAR(19), GETDATE(), 120);
END;

二、游标是什么

游标是SQL 的一种数据访问机制 ，游标是一种处理数据的方法。众所周知，使用SQL的select查询操作返回的结果是一个包含一行或者是多行的数据集，如果我们要对查询的结果再进行查询，比如（查看结果的第一行、下一行、最后一行、前十行等等操作）简单的通过select语句是无法完成的，因为这时候索要查询的结果不是数据表，而是已经查询出来的结果集。游标就是针对这种情况而出现的。
我们可以将“ 游标 ”简单的看成是结果集的一个指针，可以根据需要在结果集上面来回滚动，浏览我需要的数据。

简单概括游标是一种数据库对象，用于在 SQL 查询中遍历结果集的记录。它提供了一种逐行处理结果集的机制，允许在每次迭代中获取一条记录并对其进行处理。

三、游标的操作

声明游标 — 打开游标 — 取出数据 — 关闭游标 — 删除游标

1、声明游标——Declare cursorname Cursor

declare cursor_name Corsor [ LOCAL | GLOBAL]  [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ]（这些为可选参数）

for

Slect查询的相关语句

2、打开游标——Open Cursorname

3、取出数据——Fetch.........From

Fetch   [ NEXT | PRIOR | FIRST | LAST  | ABSOLUTE { n | @nvar }  | RELATIVE { n | @nvar }]     FROM Cursorname            

[ INTO @variable_name [ ,...n ] ]

4、关闭游标——Close Cursorname

5、删除游标——Deallocate Cursorname

四、游标的用途

逐行处理数据： 游标允许开发人员逐行处理结果集。这对于需要对每一行数据执行复杂的逻辑或业务规则的情况很有用。

数据导航： 游标允许在结果集中进行导航，可以通过 FETCH 语句逐行获取记录。这对于需要在结果集中定位到特定记录的情况很有用。

数据修改： 游标可以用于更新或删除结果集中的数据。通过游标，可以在迭代过程中执行对记录的更改。

复杂逻辑： 在某些情况下，由于业务逻辑的复杂性，需要使用游标逐行处理数据。例如，需要在每一行上执行一系列复杂的计算或判断，而这些计算可能依赖于先前的行。

五、游标的弊端

资源占用： 游标需要在数据库服务器上占用一些资源，包括内存和 CPU。当数据量大时，这些资源的消耗会相应增加。

逐行处理： 游标通常是逐行处理结果集的，每次只处理一行数据。当处理大量数据时，这样的逐行操作效率较低，特别是与批量操作相比。

阻塞： 在并发环境中，游标可能导致阻塞，因为游标通常使用了事务。当一个事务正在使用游标读取数据时，其他事务可能需要等待锁的释放，从而导致阻塞。并且，游标默认情况下使用的是共享锁（shared lock），这可能阻止其他事务对相同数据进行写操作。

数据库引擎优化问题： 一些数据库引擎对游标的性能优化可能有限，尤其在大规模数据集上。数据库引擎在执行计划中可能无法充分优化游标的使用，导致性能下降。

举个例子：

假设有一个包含百万行数据的表，并且你使用游标逐行处理这个表。

每次循环都涉及一次数据库查询和一次结果集的传输，这可能导致大量的数据库操作和网络通信开销。

如果在并发环境中，多个事务同时使用游标处理数据，可能会导致阻塞和性能下降。

-----------------------------------------------------------------------------------------

所以，在许多情况下，可以通过使用集合操作或批量操作来替代游标，从而提高性能。

批量操作也会有一定的弊端：

内存消耗： 批量操作可能需要在内存中缓存大量的数据，特别是在将数据从应用程序传输到数据库时。对于非常大的数据集，这可能导致内存消耗较大。

事务大小： 批量操作通常在事务中执行。如果批量操作涉及大量的数据修改，事务可能会变得很大，这可能导致锁定和事务日志方面的性能问题。较大的事务也可能增加回滚的代价。

锁定和并发： 批量操作可能引起锁定问题，尤其是在并发环境中。当一个批量操作正在进行时，其他事务可能被阻塞，直到批量操作完成。这可能导致性能问题和并发性下降。

日志增长： 大规模的批量操作可能导致数据库事务日志的大量增长。这可能会影响备份和恢复操作，以及数据库的总体性能。

长时间事务： 批量操作可能需要很长的时间来执行，特别是在处理大量数据时。长时间运行的事务可能导致其他事务无法获得所需的锁定，或者可能导致数据库引擎中其他资源的争用。

在实际应用中，批量操作通常是提高性能的有效手段，合理的批量操作设计，结合适当的事务管理和性能调优，可以帮助最大程度地减轻这些潜在问题。