大营销分布式系统--用户奖品记录列表优化

作为大营销系统的用户奖品记录表，主要由两个场景会被使用，第一个场景即用户在抽奖结束之后查询自己获得的奖品，第二个场景即任务扫描机制对用户奖品表中还未发放的奖品进行定期发放（系统端、全表扫描）。

本文主要讨论在用户查询奖品的性能优化。

用户奖品表：（Id, User_id, Activity_id, Strategy_id, Order_id, Award_id, Award_time, Award_state, Create_time, Update_time）

image-20241112101843025

查询语句

# X默认是0 ， offset
select * from user_award_record where user_id = #{userId} limit X, 10

为进行任何操作情况下， 该语句会遍历数据库表中所有数据进行处理，耗时时间长。

1. 分库分表

因为用户奖品记录表作为大营销系统的核心，所有抽奖活动的行为最终都会反应到用户奖品记录表中，会导致整个数据库表非常庞大。所以通过我们通过user_id对整个数据库进行了分库分表， 将整个用户奖品表放置到不同数据库同，并且每个数据库中放置N张奖品表，由此降低单表的数据量大小，增加数据的查询速度。因为是通过user_id去分库分表，所以不存在记录存在不同数据库表的情况。（标记：这里存在一个问题，通过user_id去标识极端情况下会出现单表数据量接近之前的整表，这点在Mysql数据库内部也有相应的问题处理）

具体分库分表的做法可以参考（ 大营销分布式系统--从零开始实现数据库分库分表组件）

2. 索引

索引的原理类似于书籍的目录，它允许数据库快速定位到数据的物理位置，而不需要扫描整个表。我们要根据user_id去查询对应的所有数据，实际上只需要对user_id建立索引变能加速数据查询速度。

索引查找过程：

  1. 解析SQL语句，确定匹配的uesr_id值
  2. 访问以user_id为键的索引树，找到对应子节点（具体过程从根节点出发，不断向下判断是否达到user_id）
  3. 一般索引中只包含主键、user_id，非完整列。找到主键
  4. 如果索引中的数据满足要求，则直接返回。否则从原数据库表中读取主键读取对应数据，再返回，即回表

这样便能加快整个查询速度。

具体关于Mysql数据库的索引原理可以参考（Mysql底层机制解析）

深分页索引的索引

还是回到我们的需求，我们要从用户记录表中查询对应的奖品记录并返回其中10条，这个过程涉及到了分页，如果数据只是在前几页查询并不会有什么问题，但如果offset过大，整个索引会被拖垮。在有分页过程中，数据库会根据索引找到X+10条数据，每条数据都需要进行回表查询，最后再取其中的10条数据，整个性能损耗巨大。

解决方案1. 延迟Join

延迟join的核心思想是通过覆盖索引快速获取所需的主键，然后再根据这些主键关联原表以获取需要的行数据。这种方法可以避免回表查询，特别是当处理排序加分页查询时，可以显著提高查询效率。

根据延迟join的方式将上面sql语句修改为

select * 
from user_award_record 
where id in (
	select id from user_award_record where user_id = #{userId} limit X, 10
)

或者

SELECT a.* 
FROM user_award_record a
JOIN (
    SELECT id 
    FROM user_award_record 
    WHERE user_id = #{userId} 
    LIMIT X, 10
) b ON a.id = b.id;

切记不能， 下面这种方式会进行全表扫描

select a.* from user_award_record a, (select id from user_award_record where user_id = #{userId} limit X, 10) b where a.id = b.id

通过这种方式，每次找到需要查询对应奖品表的id， 然后在数据库中找到其完整的数据，避免了回表带来的性能损耗。

解决方案2. Seek Method

通过新增一个变量定位上次分页的位置，每次查询从上次查询的结束位置开始，这样能避免前X条数据的读取，使得每次查询的数据仅需10个。

对应的SQL的修改为

select * from user_award_record where user_id = #{userId} and id > #{last} limit 10

但这种方式存在两点缺陷

  1. 无法支持跳页（采用瀑布流设计的方式解决

  2. 排序场景下有限制

    在排序场景下，通过以抽奖奖品的时间排序

    select * from user_award_record where user_id = #{userId} and create_time < #{last_create_time} order by create_time  desc limit 10

    但是存在一种情况就是，非主键字段存在重复的可能，假如同一时间，有N个奖品被抽中，在数据库读取中只读取N中的第一个就满足需求了，那么第二次读取会忽略剩下的N-1个，造成数据丢失。

    解决思路1） 就是<改为<= ,但存在问题，虽然不会丢失，但是会重复，如果相同时间的奖品超过10个，将永远不能翻页跳转。

    解决思路2）保存是时间（非主键数据）和ID的顺序一致性，这样就能用ID来作为Last判断。

关于大数据量SQL分页解决也还有其它的解决思路, 因为需要跳页，我们最后采用了延迟Join的方案来解决奖品查询的问题。

3. 缓存

一般来讲，所有查询都涉及着缓存，因为Mysql数据库连接资源相当宝贵，通常会使用Redis来作为缓存的数据库。

对于加入Redis缓存后查询中奖的流程为

  1. 从Redis缓存中查询是否存在对应中奖记录，若存在则返回
  2. 若不存在，则从数据库中查询相应数据，并放入缓存中，返回

此方法在数据量比较小的时候完全抗的住，但是如果数据量达到一定规模将出现以下问题。

  • 数据一致性问题。中奖的某些奖品不能进行立即发放会存在时延，由此造成奖品的状态会发送变化，照成Redis数据和数据库的数据不一致。常规做法是，是更新之后对缓存里面的数据进行更新。但是在读写并发的情况，动态维护缓存很困难，所以也可以直接删除整个list缓存。

  • 那么问题又来了，如果删除整个缓存，在高并发的情况下很容易过多请求直接访问数据库照成宕机。

因为整个奖品记录表变动的字段不多，所以最初采用的这种简洁的方案。这种方案主要是问题是在于每次保存到Redis中是一个List，进行变更的代价很大。

一种修改的思路是，先在数据库里面查询对应的分页里面的ID，接着依次根据ID到Redis中查询对应的元素（并行）。在这种方式中，如果奖品变更只需要删掉单条缓存，维护缓存相对容易。这个方案的缺点在于需要一次数据库的连接。

既然需要数据库连接，那么优化的思路就是，将这个从数据库里面查询的分页ID也存储起来，因为用户大多情况下是访问第一页，所以缓存命中率较高。在ID缓存里面数据发生变化后，直接删除ID数据缓存，等待下一次查询更新。

这个就是最终缓存优化方案, 构成一个二级缓存。

总结

整个大营销分布式系统的用户奖品记录查询从分库分表开始优化， 到数据库表的索引优化，再到Redis优化，通过三个部分极大加快查询速度。