千万级流水表计算可按时段查询排行榜的优化

Question

接了一个需求，是一个排行榜的
这个排行榜的筛选条件：
时间筛选，姓名，手机号，会员编码。
因为时间筛选，所以只能通过流水表去查
流水表存有发生时间和会员的各种信息，但姓名、手机号随时可变，所以需要编码来查询。
同积分同排名，比如积分 8 6 6 5 4 的排名：1 2 2 3 4。
经测试 数据库百万级数据传入代码会导致服务崩溃，故需分页后传回。
前端需要排名总条数来显示分页。

目前的解决方案是
在流水表中将会员分组sum后通过sql排序 使用dense_rank进行排名计算，在当前sql外层进行in查询

select
        subquery.`rank`,
        subquery.会员编码 as 会员编码,
        subquery.积分 as 积分
from(
        select
          dense_rank() over (order by ifnull(SUM(积分变化值),0) desc) as `rank`,
          会员id AS 会员id,
          ifnull(SUM(积分变化值),0) as 积分
        from
          积分流水表 mg
        where
          删除标识 = 0
          and 增减标识 = 1
        <if test="dto.startTime!=null">
            and create_time &gt;= #{dto.startTime}
        </if>
        <if test="dto.endTime!=null">
            and create_time &lt;= #{dto.endTime}
        </if>
        GROUP BY
        会员id
        order by null
        )as subquery
        <if test="dto.会员id拼接的字符串 != null and dto.会员id拼接的字符串.size() > 0">
            where
            subquery.member_id in ${dto.会员id拼接的字符串}
        </if>
        limit 0,10

大老们有什么优化方案~

Answer 1

让阿豪来帮你解答，本回答参考chatgpt3.5编写提供，如果还有疑问可以评论或留言

针对百万级数据的查询，可以考虑将数据分片存储，或者采用分布式计算的方式进行查询，以减轻单点服务的压力。 另外，在SQL语句中使用了子查询，可能会影响查询性能。可以考虑将子查询的结果通过临时表或者视图保存下来，提高查询效率。 代码示例： -- 创建临时表 CREATE TEMPORARY TABLE temp_ranking AS SELECT dense_rank() over (order by ifnull(SUM(积分变化值),0) desc) as rank, 会员id AS 会员id, ifnull(SUM(积分变化值),0) as 积分 FROM 积分流水表 mg WHERE 删除标识 = 0 AND 增减标识 = 1 AND create_time >= #{dto.startTime} AND create_time <= #{dto.endTime} GROUP BY 会员id; -- 查询排行榜数据 SELECT tr.rank, tr.会员编码, tr.积分 FROM temp_ranking tr WHERE tr.会员编码 IN ${dto.会员编码拼接的字符串} LIMIT 0, 10; -- 查询总排名条数 SELECT COUNT(*) as 总条数 FROM temp_ranking tr; 需要注意的是，在临时表创建后，如果流水表的数据有更新，需要重新创建临时表或者更新临时表中的数据，以保证查询结果正确。