架构训练营模块八作业

设计消息队列存储消息数据的 MySQL 表格。

【作业要求】

1. 包括表名、字段、索引；

2. 用文字描述设计思路和理由，例如：为什么设计某个索引？

3. 一页 PPT 即可。

【提示】需要考虑每个消息队列一张表，还是所有消息放一张表，里面加一个“队列名称”的字段。

场景一：如果消息量不大（如：所有消息每天不超过 100 万条）

建议将所有消息放到一张表，通过队列名称进行区分，通过时间进行表分区（通过定时任务自动新增删除分区，如果每天消息小于 10w 条）。

表设计

表 1：消息发送表

• 消息 id:自增

• 发送日期

• 队列名称

• 消息内容

• 发送时间

主键: 消息 id+队列名称+发送日期

索引 1：发送时间

分区字段：发送日期

表 2：消费表

• 消费 id：自增 id

• 消费日期

• 消费客户端编码：ip 或者 host 名称+队列名称（启动时注册到这个表）

• 上次消费的消息 id

• 消费时间

主键: 消费 id+发送日期

索引 1：消费客户端编码 + 消费时间

索引 2：上次消费的消息 id

分区字段：消费日期

操作说明

1 发送表在发送成功后插入一条，消费表在消费成功后插入一条

2 消费者启动后，第一次消费

先查上次成功消费的 id。

select max(上次消费的消息 id） from 消费表 where 消费客户端编码 =#{消费客户端编码 } and 消费日期 > #{最近 2 天} and 消费时间 > #{最近 12 小时}

然后根据这个 id 从发送表获取数据

select 消息 id+内容 from 发送表 where 消息 id =#{上次消费的消息 id } and 队列名称=#{队列名称 } and 发送日期 > #{最近 2 天} limit #{每次消费数量}

3 消费者启动后第 n（n > 1）次消费，因为这个时候消费者程序知道上次消费 id，只需要查询下面 sql

select 消息 id+内容 from 发送表 where 消息 id =#{上次消费的消息 id } and 队列名称=#{队列名称 } and 发送日期 > #{最近 2 天} limit #{每次消费数量}

原因

多个队列名称放到一个表，以后新增队列，无需额外维护成本，采用分区主要是为了方便清除历史数据

场景二：如果消息量比较大（如：所有消息每天超过 100 万条，甚至更多）

建议将不同的消息队列单独放到不同表（或库），通过配置表（包含队列名称和表（或库）名称进行关联），单个表根据时间分区（通过定时任务自动新增删除分区）

表结构除了消息发送表少了队列名称字段，消息 id 采用雪花算法生成以外，其他表设计都和方案一一样。可以由中间件 mycat 或者 sharding-jdbc 减少分库分表带来的复杂度。

配置信息字段如下

1. 队列名称

2. 表（或库）

原因

单表或单库数据量大带来查询或插入性能问题。