imsdk 数据库设计

字符编码，字符集

后面字符编码，字符集统一 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

开发数据库

10.10.60.195 9701 ksuser ksuser

数据分库分表思想

如果用户id 有序自增最好的方式以用户id 分段模式决定分库，然后对用户id 进行取模决定分表
目前公司的用户id是uuid 所以说不能做到分段分库。但是我们可以根据用户创建时间来做分段
比如 2021年 7 月起到 2023年 8 月为库1 （可为逻辑库）
2023年 8 月起到 2024年 8 月为库2 （可为逻辑库）
每个库下面的表，以用户id uuid hash 取模分32个分片。保证用户会路由到指定数据库的指定table上

会话消息表需要单独设计，每张表1000 w ，128张表也就12亿会话消息。保存会话未读消息

历史消息，以月进行一个划分，每个月32张表。后面应该会用hbase ，历史消息会存在大量数据，而这个数据保存期限，需要和产品商量。目前保存一个月。一个月后清除（时间可以配置）

数据库分表设计

分库目前没做，但是预留了分库的代码，可以后面接入。

分表目前是按照：用户uuid的hash值模以32取绝对值来分，Math.abs(uuid.hashCode()) % 32)。对应32张表。表命名格式：基础表_取模值，取模值为[0,31] 例如：conversation _ index _ 31

私信相关表：
conversation_index_31 // 会话索引表，分表
conversation_msg_31 // 会话表，分表
msg_record_31 // 未读消息表，分表
msg_record_history_202106_31 // 消息历史表，按月份和 uuid取模两个维度分，单独说明
关注相关表：
attention_conversation_31 // 会话表，分表
attention_msg // 消息记录表，未分表
点赞回复相关表：
system_conversation_31 // 系统会话表，分表
system_conversation_msg // 系统会话消息记录表，未分表

msg_record和msg_record_history：

msg_record_history可以看成msg_record的备份表，私信聊天的消息会双写到这两张表中，已读回调的时候会删掉msg_record中所有的消息，msg_record中专门存放未读消息，msg_record_history中存放所有的消息记录。
msg_record_history会在每个月的4号5号0点时，定时任务创建下个月的分表。以年月为索引，创建32张分表。

数据库原始表

/*
 Navicat Premium Data Transfer

 Source Server         : 10.10.60.195 开发
 Source Server Type    : MySQL
 Source Server Version : 50620
 Source Host           : 10.10.60.195:9701
 Source Schema         : private_message

 Target Server Type    : MySQL
 Target Server Version : 50620
 File Encoding         : 65001

 Date: 15/04/2021 15:00:02
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for conversation_index
-- ----------------------------
DROP TABLE IF EXISTS `conversation_index`;
CREATE TABLE `conversation_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(15) unsigned NOT NULL DEFAULT '0' COMMENT '会话id',
  `read_msg_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '已读消息id',
  `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `ext_index1` int(11) NOT NULL DEFAULT '0',
  `ext_index2` int(11) NOT NULL DEFAULT '0',
  `ext_field1` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_cid` (`conversation_id`) USING BTREE,
  KEY `idx_read_msg_id` (`read_msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '会话索引表';

-- ----------------------------
-- Table structure for conversation_msg
-- ----------------------------
DROP TABLE IF EXISTS `conversation_msg`;
CREATE TABLE `conversation_msg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(15) unsigned NOT NULL DEFAULT '0' COMMENT '会话id',
  `msg_to` varchar(16) NOT NULL DEFAULT '' COMMENT '接受者userid',
  `msg_from` varchar(16)  NOT NULL DEFAULT '' COMMENT '发送者userid',
  `sender_nickname` varchar(50) NOT NULL DEFAULT '' COMMENT '发送者nickname',
  `msg_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '会话的最新的msgid   ',
  `msg_content` text COMMENT '消息内容',
  `msg_img` varchar(255)  NOT NULL DEFAULT '' COMMENT '会话展示图片 红点地址  ',
  `client_seq` int(14) unsigned NOT NULL DEFAULT '0' COMMENT '客户端 时间戳   ',
  `unread_num` int(5) unsigned NOT NULL DEFAULT '1' COMMENT '未读消息数  ',
  `is_del` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ext_field1` varchar(255) NOT NULL DEFAULT '',
  `ext_field2` varchar(255) NOT NULL DEFAULT '',
  `create_time` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `msg_to` (`msg_to`,`msg_from`),
  UNIQUE KEY `idx_cid` (`conversation_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '会话表';

-- ----------------------------
-- Table structure for msg_record
-- ----------------------------
DROP TABLE IF EXISTS `msg_record`;
CREATE TABLE `msg_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(15) unsigned NOT NULL DEFAULT '0' COMMENT '会话id',
  `msg_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'msg id',
  `msg_to` varchar(32) NOT NULL DEFAULT '' COMMENT '接受者userid',
  `msg_from` varchar(32)  NOT NULL DEFAULT '' COMMENT '发送者userid',
  `msg_content` text  NOT NULL COMMENT '消息内容',
  `client_seq` int(14) unsigned NOT NULL DEFAULT '0' COMMENT '客户端序列号',
  `msg_type` int(5) unsigned NOT NULL DEFAULT '0' COMMENT '消息类型',
  `msg_code` int(5) unsigned NOT NULL DEFAULT '0' COMMENT '消息码，映射他趣消息类型',
  `is_del` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `create_time` bigint(20) NOT NULL DEFAULT '0',
  `push_time` bigint(20) NOT NULL DEFAULT '0',
  `ext_field1` varchar(255)  NOT NULL DEFAULT '',
  `ext_field2` varchar(255)  NOT NULL DEFAULT '',
  `ext_field3` varchar(255)  NOT NULL DEFAULT '',
  `ext_field4` varchar(255)  NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_cid` (`conversation_id`,`msg_id`) USING BTREE,
  KEY `idx_msg_to` (`msg_to`),
  KEY `idx_msg_from` (`msg_from`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '消息记录表';

-- ----------------------------
-- Table structure for system_msg
-- ----------------------------
DROP TABLE IF EXISTS `system_msg`;
CREATE TABLE `system_msg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` bigint(15) unsigned NOT NULL DEFAULT '0' COMMENT '会话id',
  `msg_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'msgid',
  `action` varchar(255)  NOT NULL DEFAULT '' COMMENT '红包点击跳转    ',
  `file` varchar(255)  NOT NULL DEFAULT '' COMMENT '红包图片地址 or 文件地址 or 语音地址 or 视频    ',
  `content` varchar(255)  NOT NULL DEFAULT '' COMMENT '保存title 或者富文本消息  ',
  `is_del` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `create_time` bigint(11) NOT NULL DEFAULT '0',
  `ext_field1` varchar(255)  NOT NULL DEFAULT '',
  `ext_field2` varchar(255)  NOT NULL DEFAULT '',
  `ext_field3` varchar(255)  NOT NULL DEFAULT '',
  `ext_field4` varchar(255)  NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_cmid` (`conversation_id`,`msg_id`)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '系统消息表';

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- Table structure for attention_msg
-- ----------------------------
CREATE TABLE `attention_msg` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水号',
  `msg_to` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '接受者userid',
  `msg_from` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '发送者userid',
  `msg_content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息内容',
  `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `conversation_id` int(15) unsigned NOT NULL DEFAULT '0' COMMENT '会话id',
  `msg_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '消息id',
  PRIMARY KEY (`id`),
  KEY `idx_msg_to` (`msg_to`),
  KEY `idx_msg_from` (`msg_from`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=186 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='关注消息表';

-- ----------------------------
-- Table structure for attention_conversation
-- ----------------------------
CREATE TABLE `attention_conversation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` int(15) unsigned NOT NULL DEFAULT '0' COMMENT '会话id',
  `uuid` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '被关注用户id',
  `un_read_num` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '未读数量',
  `read_msg_id` int(11) NOT NULL DEFAULT '0' COMMENT '最新已读的msg_id',
  `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_uuid` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='关注会话表';

-- ----------------------------
-- Table structure for system_conversation
-- ----------------------------
CREATE TABLE `system_conversation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` int(15) unsigned NOT NULL DEFAULT '0' COMMENT '会话id',
  `uuid` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户id',
  `un_read_num` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '未读数量',
  `msg_content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '最新消息内容',
  `type` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '类型：0:点赞，1:回复，....',
  `read_msg_id` int(11) NOT NULL DEFAULT '0' COMMENT '最新已读的msg_id',
  `last_msg_id` int(11) NOT NULL DEFAULT '0' COMMENT '最新的msg_id',
  `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uuid` (`uuid`,`type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统会话表';

-- ----------------------------
-- Table structure for system_conversation_msg
-- ----------------------------
CREATE TABLE `system_conversation_msg` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水号',
  `msg_to` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '接受者userid',
  `msg_from` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '发送者userid',
  `msg_content` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '消息内容',
  `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',
  `conversation_id` int(15) unsigned NOT NULL DEFAULT '0' COMMENT '会话id',
  `msg_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '消息id',
  PRIMARY KEY (`id`),
  KEY `idx_msg_to` (`msg_to`),
  KEY `idx_msg_from` (`msg_from`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=212 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统会话对应的消息表';

旧设计待删除

不会出现在会话列表中数据，应该是不需要插入会话列表的。但如果哪天这些消息需要出现在会话列表呢。
这时候，我们在插入也不迟。因为会话表是保持单一会话一条消息。

系统消息表和私信是否需要区分呢。个人建议的话，还是区分
私信和系统消息，读写访问量是不同的。
最好能根据不同场景定义不同消息。而不是一张表care 所有消息。

目前关注消息，红点消息，回执流程。是否可以多次关注。

1 . 分库uuid 研究（暂时不开发）0.5天
2 . 分表从etcd 获取配置 tablename ， num （num 用于取模），分表路由 2天
3 . 定时任务创建历史消息表，其他分表脚本 1天
4 . 私信历史消息表对应发送私信接口，历史消息接口变更（2天）
5 . 系统消息关注 attention_msg ，回复，点赞只是日志表可以不用管，会话表需要处理（1天）。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 199,711评论 5赞 468
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 83,932评论 2赞 376
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 146,770评论 0赞 330
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 53,799评论 1赞 271
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 62,697评论 5赞 359
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,069评论 1赞 276
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,535评论 3赞 390
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,200评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,353评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,290评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,331评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,020评论 3赞 315
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,610评论 3赞 303
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,694评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,927评论 1赞 255
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,330评论 2赞 346
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 41,904评论 2赞 341