关于 mysql 分表的方案设计，求助

我们有一个活动发放奖品的记录表，业务逻辑是一场活动 activity_id 会给用户 user_id 发放奖品 reward_id,随着活动场次的增多，给用户的奖品发放记录已经达到了亿级别的数据，现在准备分表，要支持一个查询一个用户领取的所有奖品，和一场活动的所有发放奖品用户列表两种常用查询，请问如何分表？

感觉水平分表案 user_id hash,mod 和按 activity_id hash，mod 都不太好支持这种场景，请问 v 友有什么更好的方案吗？

别的数据库 es，mongodb 暂不考虑，可以支持这种场景的 mysql 中间件可以考虑，但我参考了 mycat，感觉不太可行

奖品

Hash

MySQL

user_id

20 条回复 • 2020-10-09 10:19:01 +08:00

lasuar

2020-10-06 21:56:29 +08:00

partition

xmpx310

2020-10-06 22:02:21 +08:00

@lasuar mysql 的分区表吗？

lasuar

2020-10-06 22:12:19 +08:00

@xmpx310 对

wangritian

2020-10-06 22:33:10 +08:00

冗余一下如何，用户和活动各自分表，一次领奖插 2 条记录

guoer

2020-10-06 22:49:10 +08:00

activity_reward_idx_xxx
user_reward_idx_xxx
reward_xxx

按三个维度分表。

xmpx310

2020-10-06 22:56:19 +08:00 via iPhone

@guoer 意思是领一次奖插入 3 条记录吗

xmpx310

2020-10-06 22:57:25 +08:00 via iPhone

@wangritian 考虑过，想看看有没有更优的方案

guoer

2020-10-06 22:57:48 +08:00

@xmpx310 #6 是，用事务保证数据一致性

opengps

2020-10-07 00:55:48 +08:00 via Android

优先看下表分区能否够用
实在要水平分表，按照活动开始时间，分多个物理表

MarioLuo

2020-10-07 03:28:20 +08:00 via Android

如题只能通过数据冗余，满足多个查询键的需求。建议按照楼上老哥说的按照时间纬度分表，或者不分表做个历史归档表，不过需要从业务上面做一定调整

hpeng

2020-10-07 09:14:42 +08:00 via iPhone

这种不会变的数据做冷热分离，数据归档省事多了

xx6412223

2020-10-07 09:22:37 +08:00

这种查询固定的只读数据，分表不是最好方案。即使不用其他数据库的话，考虑下将数据序列化后按查询做成单条数据

lihongming

2020-10-07 13:23:22 +08:00 via iPhone

这场景用 nosql 真的挺合适，实在不想换数据库，也可以按 nosql 的方式来用 mysql 。

首先根据活动分表，然后建立用户 ID 和发奖记录的索引表，就行了。索引表可以用 trigger 自动维护。

wakzz

2020-10-07 13:33:46 +08:00

楼主这种场景，一般用异构索引或者用双数据源。
我们公司的处理方案是后者，因为需要通过用户维度和活动维度两个维度查询，对于消费者来说必须实时查询，所以主数据源是用户 ID 来分表，然后通过同步工具或者直接双写将数据同步到另一个数据源，次数据源通过活动维度分表，因为活动维度分表是给商户分析用的，可以有延迟，甚至可以通过大数据批跑来实现。

user8341

2020-10-07 16:13:54 +08:00

@wakzz 所以你的答案跟 4 楼是一个意思？

wakzz

2020-10-07 17:20:56 +08:00 via Android

@user8341 四楼是我的方案的一个子集，不建议写两次，一般是主库通过同步工具同步到从库，然后主库和从库各自分库分表。
实际上，楼主这种业务场景，活动纬度的查询一般是商户分析使用，是不需要实时的。完全可以主库按用户纬度分表，然后由大数据的同事一天一次或者一小时一次增量读 /监听队列之内的方案来提供分析数据

lihongming

2020-10-08 00:30:37 +08:00 via iPhone

其实大家说的方案思路都差不多，都是按照用户和活动分别组织数据。

但昨晚仔细想了一下，这些方案怕是有个共同的缺点——冷热分布太不均匀，有的表忙死，有的表闲死，可能仍会产生瓶颈。

所以，最好还是按照哈希值随机分表，然后分别根据活动和用户建两个索引表，这样就比较均衡了。

yangzixue

2020-10-08 15:17:08 +08:00

tidb

q474818917

2020-10-08 22:51:40 +08:00

如果不想用异构数据源（ nosql ）之类的话，4 楼正解

xmpx310

2020-10-09 10:19:01 +08:00

感谢大家，方案大概确定了，还是维护两个索引表，用事务保证数据的一致性，这个方案比较简单。用空间换时间