模块八作业

作业:设计消息队列存储消息数据的 MySQL 表格

【作业要求】

1. 包括表名、字段、索引;2. 用文字描述设计思路和理由，例如:为什么设计某个索引?3. 一页 PPT 即可。

【提示】

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

解答：

• 不同任务的队列建不同的表，比如 xx_q, yy_q 等。这样可以减少每个消息表的大小，提高性能；减少不同消息之间的干扰，提高 SQL 执行速度。

• 表结构

这里以 xx_q 举例，其主要字段如下：

CREATE TABLE `xx_q` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT, // 自增主键

  `status` tinyint(4) DEFAULT 0, // 队列种类：0: todo, 1: done

  `ps` int(11) DEFAULT 0, //获得消息的进程号，相当于一个锁

  `ut` timestamp, //时间戳

  `msg` json, //消息体参数，json 格式保存

  PRIMARY KEY (`id`),

  KEY `ut` (`ps`,`status`) //遍历队列的索引, 加快检索消息速度

)

• 工作原理：

1. 锁定本次工作消息： update xx_q set ps=connection_id() , status=1 where ps = 0 and status=0 limit 1;

2. 获取消息体： select id, msg from xx_q where ps = connection_id() and status=1;

3. 进行 消息消费

4. 删除消费完毕的消息 delete from xx_q where ps = connection_id() and status=1;

• 清理僵尸进程占据的消息：定时清除 会话 id 已经不存在（异常退出）但 status=1 且 僵尸时间超过 30 分钟的消息

update xx_q set status=0, ps=0 where status=1 and ps not in (`connection_id of 'show processlist'`) and ut < current_timestamp - interval 30 minute;