设计消息队列存储消息数据的 MySQL 表格
【作业要求】
1. 包括表名、字段、索引；
2. 用文字描述设计思路和理由，例如：为什么设计某个索引？
3. 一页 PPT 即可。
【提示】
1. 需要考虑每个消息队列一张表，还是所有消息放一张表，里面加一个“队列名称”的字段。
系统分析
消息队列可以分为如下的操作步骤：
管理员创建 topic
生产数据
消费数据
同时根据实际需求，可以增加如下能力：
设计实现
为了简化消息队列的设计，首先不考虑消息分区和多消费组的情况。为了降低不同 topic 之间的相互影响，所以每个 topic 一张表。
数据库表设计
topic 信息表
存储 topic 的基础信息
CREATE TABLE `topic` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'topic id', `name` VARBINARY(255) NOT NULL COMMENT 'topic 名称', `description` text NOT NULL COMMENT 'topic 描述', `create_time` bigint(11) unsigned NOT NULL COMMENT '创建时间', `update_time` bigint(11) unsigned NOT NULL COMMENT '更新时间', PRIMARY KEY (`id`),) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制代码
topic 表
存储 topic 对应的消息，用实际的 topic 名称替换表名中的 xxxx，表索引的使用参见后文的访问情况
CREATE TABLE `topic_xxxx` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'message id', `message` text NOT NULL COMMENT 'topic 名称', `owner` VARBINARY(255) COMMENT '消费者标识', `create_time` bigint(11) unsigned NOT NULL DEFAULT NOW() COMMENT '创建时间', `update_time` bigint(11) unsigned NOT NULL DEFAULT NOW() COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_id_owner` (`id`,`owner`),) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
复制代码
生产者
多个生产者直接将消息写入到 topic_xxxx 表既可以，通过 Mysql 的原子性保证消息的写入，通过自增 id 标识消息的顺序，生产者执行的命令为：
insert into topic_xxxx(message) values ('a message');
复制代码
消费者
消费者首先通过 update 语句更新 owner 字段，表示已经消费这个消息，然后使用 select 查询这个消息，在客户端确认之后，使用 delete 删除对应的消息，对应的执行语句为：
update topic_xxxx set owner='me' where owner is null ORDER BY ID ASC limit 1;select * from topic_xxxx where owner='me';delete from topic_xxxx where owner='me';
复制代码
在考虑客户端失败重试的场景，可以在 update 查询的时候，检查更新 owner 的时间是否过期，则查询语句更改为：
update topic_xxxx set owner='me',update_time=NOW() where owner is null or update_time < SUBTIME(NOW(), SEC_TO_TIME(60)) ORDER BY ID ASC limit 1;
复制代码
架构演进
评论