为什么SQL开发应尽量避免使用游标：性能瓶颈分析

在处理大量数据时，许多开发者习惯使用游标（Cursor）逐行读取和处理记录。这种写法在逻辑上直观，但在数据量增长后，往往会导致响应时间线性甚至指数级上升，并伴随严重的锁等待问题。

游标的核心问题在于它将数据库擅长的“集合运算”退化为了应用层或存储过程层的“循环迭代”。本文将拆解游标的性能瓶颈，展示如何用集合操作替代逐行处理，并在必须逐行逻辑时如何通过分批控制代价。

游标慢在哪里

游标的执行机制决定了它在大数据量下的低效。当使用 DECLARE 定义游标并进入 FETCH 循环时，数据库引擎需要为每一行数据执行以下步骤：

1. 上下文切换与往返：每次 FETCH 都是一次独立的请求-响应周期。即使在同一会话中，引擎也需要维护状态机，判断下一行位置，这产生了大量的 CPU 开销和网络往返（如果游标在客户端代码中实现）。
2. 锁持有时间延长：游标通常在事务中运行。随着逐行处理时间的累积，事务持续时间变长，导致相关行或页的锁被长时间持有。这不仅阻塞其他并发事务，还可能引发死锁。
3. 无法利用集合优化：数据库优化器针对集合操作（如 JOIN、GROUP BY）有成熟的算法（如 Hash Join、Merge Join）。逐行处理迫使引擎使用嵌套循环或索引查找，失去了并行处理和批量 I/O 的优势。

相比之下，集合操作一次性描述“要做什么”，由引擎决定“怎么做”，能充分利用内部优化策略。

先改写成集合操作

绝大多数游标场景都可以转化为单条 SQL 语句。核心思路是：不要问“下一行是什么”，而是问“哪些行满足条件”。

场景示例：批量更新订单状态

假设需要将超过 30 天未支付的订单状态更新为“已取消”，并记录更新时间。

游标写法（低效）：

DO $$
DECLARE
    rec RECORD;
    cur CURSOR FOR SELECT id FROM orders WHERE status = 'pending' AND created_at < NOW() - INTERVAL '30 days';
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        -- 逐行更新，每次产生一次 WAL 日志和锁竞争
        UPDATE orders SET status = 'cancelled', updated_at = NOW() WHERE id = rec.id;
    END LOOP;
    CLOSE cur;
END $$;

集合操作写法（高效）：

UPDATE orders
SET status = 'cancelled', updated_at = NOW()
WHERE status = 'pending'
  AND created_at < NOW() - INTERVAL '30 days';

这条语句让 PostgreSQL 优化器选择最优的执行路径（如顺序扫描或索引扫描），并批量应用更改。它不仅代码简洁，而且减少了事务内的语句数量，降低了 WAL 日志生成的频率。

逐行 FETCH 与集合更新的执行路径和资源占用对比

对于更复杂的逻辑，如涉及多表关联的计算，可以使用 UPDATE ... FROM 或 INSERT INTO ... SELECT。如果逻辑依赖前一行状态（如累计计算），优先考虑窗口函数（Window Functions）而非游标。

必须逐行时控制代价

有些业务逻辑确实无法用纯 SQL 表达，例如需要调用外部 API、执行复杂的状态机跳转或依赖非确定性函数。此时仍需逐行处理，但必须控制其对系统的影响。

分批提交与缩短事务

长事务是游标性能杀手。通过将大事务拆分为多个小批次，可以释放锁资源，允许其他事务介入。

优化策略：

1. 分批处理：每次只处理固定数量的行（如 1000 行）。
2. 显式提交：每批处理后执行 COMMIT，释放锁。
3. 及时关闭：确保异常路径下也能 CLOSE 游标，避免资源泄漏。

DO $$
DECLARE
    rec RECORD;
    cur CURSOR FOR SELECT id FROM orders WHERE status = 'pending' ORDER BY id;
    batch_count INT := 0;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;
        
        -- 执行复杂逻辑
        UPDATE orders SET status = process_order(rec.id) WHERE id = rec.id;
        
        batch_count := batch_count + 1;
        
        -- 每处理 1000 行提交一次
        IF batch_count >= 1000 THEN
            COMMIT; -- 注意：在 DO 块中需使用动态 SQL 或调整事务边界，此处为逻辑示意
            batch_count := 0;
        END IF;
    END LOOP;
    CLOSE cur;
END $$;

注：在 PL/pgSQL 的 DO 块或函数中，通常不能直接执行 COMMIT（除非使用 PostgreSQL 11+ 的过程 Procedure）。在实际应用中，建议在应用层（如 Python/Java）控制分批查询和提交，或使用自治事务。

分批处理、提交并关闭游标以缩短锁持有时间

例外场景

• 实时性要求极高且数据量小：游标可以快速返回首屏数据。
• 复杂游标变量：某些遗留系统强依赖游标变量传递结果集。

即便在这些场景中，也应限制游标生命周期，避免在高峰时段运行。

用观测验证改写

优化不能凭感觉，必须通过数据库提供的工具验证效果。

1. 查看执行计划

使用 EXPLAIN (ANALYZE, BUFFERS) 对比改写前后的 SQL。

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending' AND created_at < NOW() - INTERVAL '30 days';

关注指标：

• Total Time：总耗时是否显著下降。
• Buffers：共享缓冲区命中率和读写次数。集合操作通常能更有效地利用缓存。
• Plan Node：确认优化器选择了 Index Scan 或 Seq Scan，而非低效的 Nested Loop。

2. 监控锁等待

在 PostgreSQL 中，可以通过查询 pg_locks 和 pg_stat_activity 观察锁竞争情况。

SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

如果改写后该查询返回的行数减少或为空，说明锁竞争得到缓解。

3. 回归检查

• 数据一致性：对比改写前后受影响的行数和数据状态，确保逻辑等价。
• 超时监控：观察应用日志中是否有数据库超时错误减少。

下一步建议

1. 审查现有代码：搜索项目中的 CURSOR、FETCH 关键字，评估是否可转化为集合操作。
2. 小范围试点：选择一个非核心业务的报表或批处理任务，尝试用 UPDATE ... FROM 或窗口函数重构。
3. 建立监控基线：在测试环境记录重写前后的执行时间和锁等待指标，作为后续优化的参考。

通过减少对游标的依赖，你可以显著提升数据库的吞吐量和系统的整体稳定性。

参考资料

• PostgreSQL cursors
• PostgreSQL performance tips