MySQL 5.7用户累计求和排序慢查询优化求助

1. 索引优化 #

• 给pointHistoryTable创建覆盖复合索引：CREATE INDEX idx_ph_point_gain_use ON pointHistoryTable(point_id, gain, \use`);`，该索引覆盖分组字段和求和字段，避免查询时回表扫描，大幅提升子查询分组求和的效率。
• 给pointTable创建复合索引：CREATE INDEX idx_pt_user_point ON pointTable(user_id, point);，关联user表时快速匹配用户，同时支持按current（即point字段）排序时的索引扫描。
• 确保user表的id字段为主键（默认主键自带索引），保证关联查询的效率。

2. 查询逻辑重构 #

替代原有的两种查询方式，采用LEFT JOIN + 单次分组求和的方式，减少重复查询并保证数据完整性（包含无积分历史的用户）：

SELECT 
    u.nickname AS nickname,
    pt.point AS current,
    IFNULL(sub.cGainSum, 0) AS cGainSum,
    IFNULL(sub.cUseSum, 0) AS cUseSum
FROM
    pointTable pt
INNER JOIN user u ON u.id = pt.user_id
LEFT JOIN (
    SELECT 
        point_id, 
        SUM(CASE WHEN gain > 0 THEN gain ELSE 0 END) AS cGainSum,
        SUM(CASE WHEN \`use\` > 0 THEN \`use\` ELSE 0 END) AS cUseSum
    FROM pointHistoryTable
    GROUP BY point_id
) sub ON pt.id = sub.point_id
ORDER BY cGainSum DESC
LIMIT 20 OFFSET 0;

该方案将原查询中多次子查询的逻辑合并到单次分组里，降低数据库IO开销。

3. 预计算缓存（最优长期方案） #

由于cGainSum和cUseSum是累计型字段，可通过字段冗余+实时同步的方式避免每次查询时的计算：

• 在pointTable中新增两个字段：total_gain（累计获得积分）、total_use（累计使用积分），类型与gain/use一致。
• 每次往pointHistoryTable插入数据时，通过触发器或业务代码同步更新pointTable的对应字段：

  -- 触发器示例：插入积分历史时更新累计值
  DELIMITER //
  CREATE TRIGGER trg_update_point_total AFTER INSERT ON pointHistoryTable
  FOR EACH ROW
  BEGIN
      IF NEW.gain > 0 THEN
          UPDATE pointTable SET total_gain = total_gain + NEW.gain WHERE id = NEW.point_id;
      END IF;
      IF NEW.\`use\` > 0 THEN
          UPDATE pointTable SET total_use = total_use + NEW.\`use\` WHERE id = NEW.point_id;
      END IF;
  END //
  DELIMITER ;
  

• 优化后的查询直接使用冗余字段，排序和分页完全依赖索引，性能接近单表查询：

  SELECT 
      u.nickname AS nickname,
      pt.point AS current,
      pt.total_gain AS cGainSum,
      pt.total_use AS cUseSum
  FROM pointTable pt
  INNER JOIN user u ON u.id = pt.user_id
  ORDER BY pt.total_gain DESC
  LIMIT 20 OFFSET 0;
  

• 针对动态排序需求，给pointTable创建对应复合索引：
  • 按current排序：idx_pt_point_user (point DESC, user_id)
  • 按cGainSum排序：idx_pt_totalgain_user (total_gain DESC, user_id)
  • 按cUseSum排序：idx_pt_totaluse_user (total_use DESC, user_id)

4. 分页逻辑优化 #

当分页偏移量（OFFSET）较大时，传统LIMIT ... OFFSET会导致数据库扫描大量无用数据，建议改用游标分页：

• 记录上一页结果集中最后一条数据的排序字段值（如total_gain）和唯一标识（如user_id）
• 下一页查询时通过条件过滤直接定位起始位置：

  SELECT 
      u.nickname AS nickname,
      pt.point AS current,
      pt.total_gain AS cGainSum,
      pt.total_use AS cUseSum
  FROM pointTable pt
  INNER JOIN user u ON u.id = pt.user_id
  WHERE pt.total_gain < @last_total_gain 
     OR (pt.total_gain = @last_total_gain AND u.id < @last_user_id)
  ORDER BY pt.total_gain DESC, u.id DESC
  LIMIT 20;
  

该方式利用索引快速定位，避免全表扫描。

5. MySQL配置调优 #

• 适当增大sort_buffer_size（建议设置为2M，根据服务器内存调整）：SET GLOBAL sort_buffer_size = 2*1024*1024;，足够的排序缓冲区可避免磁盘临时文件排序，提升排序效率。
• 确保optimizer_switch开启derived_merge（MySQL 5.7默认开启），让数据库自动优化子查询为JOIN操作。

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