MySQL分组聚合场景下的分页性能优化问题

问题背景 #

常规LIMIT offset, size分页跳转到后页时，MySQL会全量扫描前序所有记录，性能暴跌；而常用的键集分页（WHERE id > last_id LIMIT size）直接套用到带GROUP BY和SUM的聚合查询里，会因为截断分组范围导致求和结果错误。

可行优化方案 #

• 预计算聚合结果并缓存
  直接把分组求和的最终结果预先计算好，存在一张独立的聚合结果表（比如inventory_agg）里，字段包含分组键（如category_id）、求和值（如total_stock）以及更新时间。

  • 数据刷新：可以用凌晨定时任务批量计算，或者给库存表加触发器，在数据新增/修改/删除时同步更新聚合表的对应记录。
  • 分页方式：直接在聚合结果表上用键集分页，比如WHERE category_id > last_category_id LIMIT 20，既保证求和结果准确，又彻底避免了全表扫描，性能最优。

• 用覆盖索引优化原聚合查询
  如果不能用预计算，就给库存表建覆盖索引，包含分组字段和求和字段，比如：

  CREATE INDEX idx_category_stock ON inventory (category_id, stock);
  

  调整查询逻辑，基于分组后的排序字段做键集分页，而非原始表的主键id：

  -- 第一页
  SELECT category_id, SUM(stock) AS total_stock
  FROM inventory
  GROUP BY category_id
  ORDER BY category_id
  LIMIT 20;
  
  -- 后续页（假设上一页最后一个category_id是100）
  SELECT category_id, SUM(stock) AS total_stock
  FROM inventory
  WHERE category_id > 100
  GROUP BY category_id
  ORDER BY category_id
  LIMIT 20;
  

  覆盖索引能让MySQL直接从索引里完成分组和求和，不用回表扫描全量数据，同时基于分组键的键集分页不会截断分组范围，求和结果完全准确。

• 分段分批计算聚合结果
  把分组字段（比如category_id）拆成多个连续区间，每次只计算一个区间内的聚合结果，比如：

  -- 第一页：category_id 1-20
  SELECT category_id, SUM(stock) AS total_stock
  FROM inventory
  WHERE category_id BETWEEN 1 AND 20
  GROUP BY category_id;
  
  -- 第二页：category_id 21-40
  SELECT category_id, SUM(stock) AS total_stock
  FROM inventory
  WHERE category_id BETWEEN 21 AND 40
  GROUP BY category_id;
  

  这种方式避免了一次性处理全部分组，每段查询的范围可控，性能稳定，且每个区间内的分组求和都是完整计算的，结果不会出错。

示例对比 #

原低效分页查询（跳转到第500页，需扫描前10000条分组记录）：

SELECT category_id, SUM(stock) AS total_stock
FROM inventory
GROUP BY category_id
LIMIT 10000, 20;

优化后的键集分页+覆盖索引查询（仅扫描category_id > 100的索引数据）：

SELECT category_id, SUM(stock) AS total_stock
FROM inventory
WHERE category_id > 100
GROUP BY category_id
ORDER BY category_id
LIMIT 20;

内容的提问来源于stack exchange，提问作者Wong Yat Cheong Jacky