分页场景下全量行统计的性能瓶颈及优化方案咨询

一、彻底规避全量统计的思路 #

1. 多请求1条数据判断（通用所有分页方式） #

不管是页码分页还是游标分页，都可以放弃统计总条数，改为请求比每页需求多1条的数据：

• 比如每页要50条，就执行LIMIT 51
• 如果返回51条，说明还有下一页，前端只展示前50条；如果返回≤50条，说明已经到最后一页
• 这种方式完全绕开全量统计，性能最优，适合不需要显示总页数/总条数的场景

2. 缓存总条数（仅页码分页适用） #

如果必须显示总页数或总条数：

• 第一次加载时执行一次全量统计，把结果存入缓存（比如Redis），缓存时间根据数据更新频率设置（比如5分钟到1小时）
• 后续分页请求直接读取缓存里的总条数，不用重复执行统计查询
• 当数据发生新增、删除等更新操作时，主动更新缓存中的总条数

二、优化全量统计的性能（必须统计时） #

你的当前SQL存在严重性能问题：子查询引用了外部表的wst.deleted_by，导致这是一个相关子查询，主查询每返回一行，就要执行一次统计，性能消耗极大。可以通过以下方式优化：

方式1：拆分查询（推荐） #

分开执行"统计总条数"和"查询分页数据"两个独立查询，避免关联子查询的性能损耗：

统计总条数的SQL

SELECT COUNT(DISTINCT wp.id) AS total 
FROM workers_plan wp
LEFT JOIN users u ON u.id = wp.user_id
LEFT JOIN workers_plan_time wst ON wst.workers_plan_id = wp.id
WHERE wst.tenant_id = $1 
  AND wst.deleted_by IS NULL 
  AND ($3 OR u.company_id = $2)

查询分页数据的SQL

SELECT 
    wp.id,
    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,
    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_plan_time 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 
    wst.tenant_id = $1 
    AND wst.deleted_by IS NULL 
    AND ($3 OR u.company_id = $2)
GROUP BY 
    wp.id, wst.id, u.id, c.id, l.name
ORDER BY 
    wp.id, wp.created_at 
LIMIT 50 OFFSET {page*50} -- 页码分页用OFFSET，游标分页替换为WHERE wp.id > last_id

注：原SQL中LEFT JOIN workers_plan wp ON wst.workers_plan_id = wp.id表名重复，这里修正为workers_plan_time wst（假设时间维度表为该名称）

方式2：用CTE预计算总条数 #

通过公共表表达式（CTE）先计算总条数，再通过CROSS JOIN关联到分页结果中，避免重复执行统计逻辑：

WITH total_count AS (
    SELECT COUNT(DISTINCT wp.id) AS total 
    FROM workers_plan wp
    LEFT JOIN users u ON u.id = wp.user_id
    LEFT JOIN workers_plan_time wst ON wst.workers_plan_id = wp.id
    WHERE wst.tenant_id = $1 
      AND wst.deleted_by IS NULL 
      AND ($3 OR u.company_id = $2)
)
SELECT 
    wp.id,
    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,
    l.name as location,
    tc.total
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_plan_time 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  
CROSS JOIN total_count tc
WHERE 
    wst.tenant_id = $1 
    AND wst.deleted_by IS NULL 
    AND ($3 OR u.company_id = $2)
GROUP BY 
    wp.id, wst.id, u.id, c.id, l.name, tc.total
ORDER BY 
    wp.id, wp.created_at 
LIMIT 50

三、游标分页的专属优化 #

游标分页本身基于上一页最后一条数据的唯一标识（如id、created_at）查询，天然适合无总条数的场景：

• 同样采用"多查1条"的方式判断是否有下一页，返回结果时标记has_more字段（如果返回51条则has_more: true，否则为false）
• 完全不需要统计总条数，性能远优于页码分页，适合数据量较大的业务场景

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