数据库游标使用场景与分页查询优化

数据库游标本质上是一种逐行处理查询结果集的机制，它允许你在程序中一条一条地遍历数据，而不是一次性把所有结果加载到内存。但在实际开发中，游标往往是性能杀手——它占用连接资源、锁住数据行、消耗大量内存，尤其在大数据量场景下会拖垮整个数据库。真正该用游标的场景其实很少：复杂的逐行计算、需要根据上一行结果决定下一行操作的逻辑、跨行聚合无法用SQL一次性表达的业务。而分页查询优化则是另一个核心话题，传统的OFFSET分页在深分页时性能急剧下降，需要用游标分页（Keyset Pagination）或覆盖索引来解决。把这两个话题放在一起讲，是因为它们都指向同一个核心问题：如何在大数据量下高效、可控地处理数据。

一、数据库游标的基本原理与工作机制

游标（Cursor）是数据库提供的一种指针结构，它指向查询结果集的当前行。当你执行一条SELECT语句返回多行数据时，游标允许你从第一行开始，逐行读取、处理，然后移动到下一行。不同数据库对游标的支持程度不同：MySQL通过存储过程中的DECLARE CURSOR来实现，SQL Server和Oracle则提供了更完善的游标体系，包括静态游标、动态游标、只进游标和可滚动游标等多种类型。

游标的工作流程可以简单概括为四步：声明游标（DECLARE）、打开游标（OPEN）、逐行提取数据（FETCH）、关闭游标（CLOSE）。每一步都会消耗数据库资源。特别是OPEN操作，数据库需要为游标分配临时存储空间，如果结果集很大，这个开销不可忽视。很多开发者在写存储过程时习惯性使用游标，却没有意识到这可能是整个系统慢查询的根源。

二、游标真正适用的使用场景

不是所有逐行处理都需要游标，但以下几种场景确实是游标的合理用武之地。第一种是需要逐行进行复杂业务逻辑计算的场景，比如银行利息计算，每一行的利息可能依赖于上一行的累计余额，这种行间依赖关系用纯SQL很难优雅表达。第二种是数据迁移和清洗任务，当你需要对每一行数据做格式转换、字段映射、异常处理时，游标可以让你在程序层面精细控制每一条记录的处理逻辑。第三种是需要在遍历过程中动态执行其他SQL的场景，比如根据当前行的某个字段值去查询关联表并更新主表，这种"读一行、查一表、写一行"的模式用游标实现比较直观。

下面是一个MySQL存储过程中使用游标的典型示例：

DELIMITER //
CREATE PROCEDURE process_orders()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE order_id INT;
    DECLARE order_amount DECIMAL(10,2);
    DECLARE cur CURSOR FOR SELECT id, amount FROM orders WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO order_id, order_amount;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 逐行处理逻辑，比如计算折扣、更新状态
        IF order_amount > 1000 THEN
            UPDATE orders SET discount = 0.15 WHERE id = order_id;
        ELSE
            UPDATE orders SET discount = 0.05 WHERE id = order_id;
        END IF;
    END LOOP;

    CLOSE cur;
END //
DELIMITER ;

这段代码展示了游标逐行读取待处理订单并根据金额设置不同折扣的逻辑。但要注意，如果orders表有百万行数据，这个过程会非常慢，因为每一行都要单独执行一次UPDATE语句。

三、游标的性能陷阱与替代方案

游标最大的问题是它把集合操作变成了逐行操作，这违背了SQL的设计哲学。SQL是面向集合的语言，一条UPDATE语句可以同时更新百万行，而游标要执行百万次。此外，游标在打开期间会持有锁，在默认的可重复读隔离级别下，游标读取的行会加共享锁，这会阻塞其他事务的写入操作。更严重的是，长时间持有游标会导致连接池耗尽，因为每个打开的游标都占用一个数据库连接。

替代方案首先是用集合操作代替逐行处理。上面的折扣计算例子完全可以用一条SQL搞定：

UPDATE orders 
SET discount = CASE 
    WHEN amount > 1000 THEN 0.15 
    ELSE 0.05 
END 
WHERE status = 'pending';

这条语句的执行效率比游标高几个数量级。如果业务逻辑实在复杂，无法用单条SQL表达，可以考虑分批处理（Batch Processing），每次用LIMIT取几千行做集合操作，而不是用游标逐行处理。另一个方案是使用临时表配合集合操作，先把需要处理的数据筛选到临时表，再用JOIN和批量UPDATE来完成。

四、分页查询的常见方式与性能瓶颈

分页查询是几乎所有Web应用都会用到的功能，最常见的写法是LIMIT offset, count，也就是OFFSET分页。比如查询第11页、每页10条：SELECT * FROM articles ORDER BY id LIMIT 10 OFFSET 100。这种写法在前几页没问题，但当offset值很大时，比如OFFSET 100000，数据库需要先扫描前100000行然后丢弃，只返回后面的10行。这个扫描过程的代价是线性增长的，offset越大越慢。

OFFSET分页的另一个问题是数据不稳定。如果在分页过程中有新数据插入或旧数据删除，会导致翻页时出现重复数据或跳过数据的情况。比如你在第1页看到了id为100的记录，翻到第2页时，如果前面插入了一条新记录，原来第11条就变成了第12条，你可能会漏掉id为101的记录。

五、游标分页（Keyset Pagination）的原理与实现

游标分页，也叫Keyset Pagination或Seek Method，是解决深分页问题的最佳方案之一。它的核心思想是：不用OFFSET跳过前面的行，而是记住上一页最后一行的排序键值，下一页直接从这个键值之后开始查询。比如按id排序，第一页查id > 0的前10条，第二页查id > 10的前10条（假设第一页最后一条id是10）。这样数据库可以直接利用索引定位起始位置，不需要扫描前面的数据。

具体实现如下：

-- 第一页
SELECT * FROM articles 
WHERE id > 0 
ORDER BY id ASC 
LIMIT 10;

-- 第二页（假设上一页最后一条id是10）
SELECT * FROM articles 
WHERE id > 10 
ORDER BY id ASC 
LIMIT 10;

-- 第三页（假设上一页最后一条id是20）
SELECT * FROM articles 
WHERE id > 20 
ORDER BY id ASC 
LIMIT 10;

这种方式的性能非常稳定，无论翻到第几页，查询速度都差不多，因为每次都是索引范围扫描。但它有一个限制：只能"往后翻"，不能跳页，也不能按任意页码直接定位。如果业务需要跳页功能，可以结合两种方案，浅层分页用OFFSET，深层分页用游标分页。

六、覆盖索引与延迟关联优化分页

另一种优化OFFSET分页的方法是延迟关联（Deferred Join）。思路是先通过覆盖索引快速定位到目标行的主键，再用主键回表查询完整数据。这样可以大幅减少回表次数，尤其当查询字段很多、表很宽的时候效果明显。

-- 先通过索引获取主键
SELECT id FROM articles 
ORDER BY create_time DESC 
LIMIT 10 OFFSET 100000;

-- 再用主键回表查完整数据
SELECT * FROM articles 
WHERE id IN (上面查询结果的id列表) 
ORDER BY create_time DESC;

这种写法把原来的全表扫描变成了索引扫描加少量回表，在MySQL中可以通过子查询实现：

SELECT a.* FROM articles a
INNER JOIN (
    SELECT id FROM articles 
    ORDER BY create_time DESC 
    LIMIT 10 OFFSET 100000
) b ON a.id = b.id
ORDER BY a.create_time DESC;

覆盖索引的关键是确保ORDER BY和WHERE条件中的字段都有合适的索引。如果排序字段是create_time，那就需要在create_time上建索引，最好是联合索引覆盖查询需要的所有字段，这样连回表都可以省掉，直接从索引中读取所有需要的数据。

七、不同数据库的分页特性对比

不同数据库对分页的支持差异很大。MySQL从8.0开始支持窗口函数，可以用ROW_NUMBER()来实现更灵活的分页逻辑，但性能上不如Keyset Pagination。PostgreSQL对游标的支持比较完善，支持HOLD和SCROLL选项，可以在事务中保持游标打开状态，适合需要在一个事务中跨多次调用的场景。SQL Server提供了OFFSET FETCH语法，语法上更简洁，但底层性能和LIMIT OFFSET一样有深分页问题。Oracle则有ROWNUM和ROW_NUMBER()两种机制，配合分析函数可以实现非常高效的分页，尤其是在有合适索引的情况下。

对于海量数据场景（千万级以上），还需要考虑分库分表后的分页问题。这时候游标分页的优势更加明显，因为跨分片的OFFSET计算几乎不可行，而基于分片键的游标分页可以在每个分片内独立执行，最后合并结果。

八、实战建议与最佳实践总结

综合来看，给开发者几条实操建议。第一，能不用游标就不用游标，优先考虑集合操作和批量处理。第二，分页查询首选游标分页（Keyset Pagination），如果必须支持跳页，浅层用OFFSET、深层用游标分页的混合策略。第三，永远确保分页查询的排序字段有索引，这是性能的基础。第四，避免在分页查询中使用SELECT *，只查需要的字段，减少IO和网络传输。第五，对于实时性要求高的场景，可以考虑用Redis缓存热门页码的数据，减少数据库压力。第六，定期用EXPLAIN分析分页查询的执行计划，确认是否走了索引、是否有全表扫描。

游标和分页看似是两个独立话题，但本质上都是在解决"如何高效处理大量数据"这个问题。理解它们的原理、适用场景和性能特征，是每一个后端开发者和DBA的基本功。在实际项目中，根据数据量级、业务需求和数据库类型选择合适的方案，才能真正做到既满足功能又保证性能。

数据库游标使用场景与分页查询优化

服务指南

帮助中心