游标分页实现页码式展示：如何高效计算总页数？

一、总页数计算的性能优化方案 #

担心全量统计行数开销大，可尝试以下替代方案：

• 轻量精确计数：不要基于原查询做COUNT，写一个仅关联必要表的统计SQL，跳过不影响行数的LEFT JOIN表（比如users、clients、location）。示例：

SELECT COUNT(DISTINCT wp.id, wst.id) AS total_rows
FROM workers_plan wp
LEFT JOIN workers_send_times wst ON wst.workers_plan_id = wp.id
LEFT JOIN location_orders lo ON lo.id = wp.location_orders_id
WHERE lo.id = $1 AND wp.tenant_id = $2

该SQL仅统计分组后的总条数，无需返回大量字段，大幅减少JOIN和数据传输开销。

• 近似计数：若业务允许非精确页数，用数据库近似统计功能。比如PostgreSQL可通过系统表获取估算行数：

SELECT reltuples::bigint AS approx_count
FROM pg_class
WHERE relname = 'workers_plan' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

MySQL可通过EXPLAIN的rows字段估算，这种方式几乎无性能消耗，结果为近似值。

• 异步预计算：后台定时任务（如每小时）统计一次总条数，存入Redis或缓存表，前端直接读取缓存值。适合数据更新不频繁的场景，避免实时统计的性能损耗。

二、带页码的游标分页实现 #

不用OFFSET实现页码跳转，核心是获取目标页码对应的起始游标，具体有两种方式：

1. 缓存页码-游标映射 #

• 逻辑：用户访问第1页时，查询并缓存该页最后一条数据的(wp.id, wp.updated_wst)作为第2页的起始游标；访问第2页时，缓存第2页最后一条的游标作为第3页的起始，以此类推。
• 实现示例：
  • 第1页查询（起始游标设为最小有效值）：

    SELECT 
        wp.id, wp.updated_wst,
        u.id as user_id, u.firstname, u.lastname, 
        c.id as company_id, c.company,
        wst.id as wst_id,
        wst.start_time, wst.end_time,
        wst.send_start_at, wst.send_end_at,
        wst.accepted_by, wst.accepted_at,
        l.name as location
    FROM workers_plan wp
    LEFT JOIN users u ON u.id = wp.user_id  
    LEFT JOIN clients c ON c.id = u.company_id
    LEFT JOIN workers_send_times wst ON wst.workers_plan_id = wp.id
    LEFT JOIN location_orders lo ON lo.id = wp.location_orders_id
    LEFT JOIN location l ON l.id = lo.location_id  
    WHERE lo.id = $1 AND wp.tenant_id = $2 AND (wp.id, wp.updated_wst) > (0, '1970-01-01')          
    GROUP BY wp.id, wst.id, u.id, c.id, l.name
    ORDER BY wp.id, wp.updated_wst LIMIT 50
    

  • 拿到第1页最后一条的last_id和last_updated_wst，存入缓存（如Redis的key为page:2:cursor，值为last_id,last_updated_wst）。
  • 用户跳转第2页时，从缓存取出游标代入查询：

    SELECT ... 
    WHERE lo.id = $1 AND wp.tenant_id = $2 AND (wp.id, wp.updated_wst) > ($last_id, $last_updated_wst)          
    ... LIMIT 50
    

• 优点：跳转页码时性能极高，直接用游标查询；缺点：数据更新（新增、删除、修改updated_wst）后缓存游标会失效，需清理对应页码缓存或设置较短过期时间。

2. 直接计算起始游标（适合排序字段连续场景） #

若wp.id是自增主键，且(wp.id, wp.updated_wst)严格递增，可估算目标页码的起始id：

• 第K页的起始id约为50*(K-1)+1（假设每页50条），查询大于该id的第一条数据的(wp.id, wp.updated_wst)作为游标：

  SELECT wp.id, wp.updated_wst
  FROM workers_plan wp
  LEFT JOIN location_orders lo ON lo.id = wp.location_orders_id
  WHERE lo.id = $1 AND wp.tenant_id = $2 AND wp.id >= 50*(K-1)+1
  ORDER BY wp.id, wp.updated_wst LIMIT 1
  

• 拿到游标后执行原查询获取第K页数据。
• 优点：无需缓存；缺点：若存在id不连续（如删除数据），估算的起始id会有误差，导致跳转页码不准确。

三、最优性能实现建议 #

1. 索引优化：给workers_plan建复合索引(tenant_id, location_orders_id, id, updated_wst)，该索引覆盖WHERE条件（tenant_id、location_orders_id）和排序字段（id、updated_wst），查询时直接走索引，避免全表扫描和排序操作。
2. 减少冗余操作：若某些字段（如u.firstname、c.company）非必须，不要SELECT；统计总条数时跳过不影响行数的LEFT JOIN表。
3. 分页边界处理：查询时同时返回当前页首尾游标，方便快速跳转上下页；仅缓存热门页码（如前10页），冷门页码实时计算。
4. 简化GROUP BY：若wp.id和wst.id组合已唯一，其他字段无需加入GROUP BY（取决于数据库版本，如PostgreSQL 10+支持功能依赖），减少分组开销。

内容的提问来源于stack exchange，提问作者wepro01