用户信息表的设计

一、核心需求分析

用户需要展示：

• 总消息条数
• 未读消息条数（已读数量可通过 总条数 - 未读条数 计算，无需单独存储）
  核心设计目标：查询高效、写入无锁、易于扩展

二、数据库设计方案

1. 基础表结构（核心表）

（1）消息主体表（message_template）

存储消息的公共内容（避免重复存储相同消息内容，如系统公告），一对多关联用户消息表：

CREATE TABLE `message_template` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '消息模板ID',
  `msg_type` tinyint NOT NULL COMMENT '消息类型：1-系统通知 2-用户私信 3-订单提醒',
  `title` varchar(128) NOT NULL COMMENT '消息标题',
  `content` text NOT NULL COMMENT '消息内容',
  `sender_id` bigint unsigned NULL COMMENT '发送者ID（系统消息为0）',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '消息创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_sender_create_time` (`sender_id`, `create_time`) COMMENT '发送者+创建时间索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消息模板表（公共内容）';

（2）用户消息关联表（user_message）

存储用户与消息的关联关系及阅读状态（核心表，高频读写）：

CREATE TABLE `user_message` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint unsigned NOT NULL COMMENT '接收用户ID',
  `template_id` bigint unsigned NOT NULL COMMENT '关联消息模板ID',
  `is_read` tinyint NOT NULL DEFAULT 0 COMMENT '是否已读：0-未读 1-已读',
  `read_time` datetime NULL COMMENT '阅读时间（已读时填充）',
  `receive_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户接收时间',
  PRIMARY KEY (`id`),
  -- 核心索引：用户ID + 已读状态（用于快速统计未读数量）
  KEY `idx_user_is_read` (`user_id`, `is_read`),
  -- 辅助索引：用户ID + 接收时间（用于按时间排序查询消息列表）
  KEY `idx_user_receive_time` (`user_id`, `receive_time`),
  -- 关联索引：模板ID（用于反向查询）
  KEY `idx_template_id` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户消息关联表（存储阅读状态）';

2. 设计说明

• 分离模板表与关联表：系统公告、批量通知等场景下，多条用户消息可关联同一模板，减少数据冗余；
• 核心字段 is_read：仅用 0/1 标识状态，统计未读数量时只需 WHERE user_id = ? AND is_read = 0；
• 索引优化：
  • idx_user_is_read 是统计未读数量的核心索引，MySQL 可通过索引快速计数，无需全表扫描；
  • idx_user_receive_time 用于用户查询消息列表时按时间排序，提升分页查询效率。

三、核心操作 SQL 示例

1. 统计用户消息数量

-- 1. 统计总消息条数
SELECT COUNT(*) AS total_count 
FROM user_message 
WHERE user_id = 10001;

-- 2. 统计未读消息条数（核心查询）
SELECT COUNT(*) AS unread_count 
FROM user_message 
WHERE user_id = 10001 AND is_read = 0;

-- 3. 已读条数 = 总条数 - 未读条数（无需单独查询）

2. 标记消息为已读

-- 单条消息标记已读
UPDATE user_message 
SET is_read = 1, read_time = CURRENT_TIMESTAMP 
WHERE user_id = 10001 AND id = 12345;

-- 批量标记已读（如用户点击“全部已读”）
UPDATE user_message 
SET is_read = 1, read_time = CURRENT_TIMESTAMP 
WHERE user_id = 10001 AND is_read = 0;

3. 发送消息（系统通知示例）

-- 步骤1：插入消息模板
INSERT INTO message_template (msg_type, title, content, sender_id)
VALUES (1, '账户安全提醒', '您的密码于2025-12-09 10:00修改成功', 0);

-- 步骤2：获取模板ID，插入用户关联记录（假设发送给用户10001、10002）
SET @template_id = LAST_INSERT_ID();

INSERT INTO user_message (user_id, template_id)
VALUES (10001, @template_id), (10002, @template_id);

4. 查询用户消息列表（带已读状态）

SELECT 
  um.id, mt.title, mt.content, mt.create_time, um.is_read, um.read_time
FROM user_message um
LEFT JOIN message_template mt ON um.template_id = mt.id
WHERE um.user_id = 10001
ORDER BY um.receive_time DESC
LIMIT 0, 20; -- 分页查询

四、性能优化方案

1. 缓存优化（核心）

高频查询“未读数量”时，直接查询数据库会产生大量读请求，建议结合 Redis 缓存：

• 缓存结构：key: user:{user_id}:unread_count，value: 未读数量；
• 更新策略：
  • 发送新消息：INCR user:{user_id}:unread_count；
  • 标记已读：DECR user:{user_id}:unread_count（批量已读时计算差值后 DECR）；
  • 缓存失效：设置过期时间（如 1 小时），或通过数据库更新触发缓存刷新；
• 兜底逻辑：缓存失效时，从数据库查询并更新缓存。

示例 Redis 操作（伪代码）：

// 获取未读数量
public Integer getUnreadCount(Long userId) {
    String key = "user:" + userId + ":unread_count";
    // 先查缓存
    String cacheCount = redisTemplate.opsForValue().get(key);
    if (cacheCount != null) {
        return Integer.parseInt(cacheCount);
    }
    // 缓存失效，查数据库
    Integer dbCount = userMessageMapper.countUnread(userId);
    // 更新缓存，设置1小时过期
    redisTemplate.opsForValue().set(key, dbCount.toString(), 1, TimeUnit.HOURS);
    return dbCount;
}

// 发送新消息，更新缓存
public void sendMessage(Long userId, Long templateId) {
    userMessageMapper.insert(userId, templateId);
    // 缓存自增
    String key = "user:" + userId + ":unread_count";
    redisTemplate.opsForValue().increment(key);
}

2. 分表优化（数据量较大时）

若用户消息量极大（单用户百万级），可对 user_message 表按 user_id 哈希分表：

-- 分表示例：user_message_00 ~ user_message_99
CREATE TABLE `user_message_00` LIKE `user_message`;
CREATE TABLE `user_message_01` LIKE `user_message`;
-- ...

分表规则：table_index = user_id % 100，应用层根据用户ID路由到对应分表。

3. 延迟统计（非实时场景）

若业务允许“未读数量”非实时更新，可通过定时任务批量统计：

• 每 5 分钟执行一次 SQL，统计所有用户的未读数量并更新到 Redis；
• 适用于对实时性要求不高的场景（如社区类网站）。

五、扩展设计（可选）

1. 消息已读状态同步

• 多端同步：用户在 PC 端标记已读后，移动端需实时同步，可通过缓存/消息队列实现；
• 批量操作：用户删除消息时，建议软删除（新增 is_deleted 字段），避免物理删除导致统计错误。

2. 消息类型扩展

若需按类型统计未读数量（如“系统通知未读”“私信未读”），可修改 user_message 表：

ALTER TABLE `user_message` ADD COLUMN `msg_type` tinyint NOT NULL COMMENT '消息类型，关联message_template的msg_type';
-- 新增索引：用户ID + 消息类型 + 已读状态
KEY `idx_user_type_is_read` (`user_id`, `msg_type`, `is_read`);

-- 按类型统计未读数量
SELECT COUNT(*) AS unread_count 
FROM user_message 
WHERE user_id = 10001 AND msg_type = 2 AND is_read = 0;

六、总结

设计方案	适用场景	优势	注意事项
基础表结构	中小规模网站	结构简单，易于维护	高频查询需配合缓存
缓存 + 数据库	高并发场景	大幅降低数据库读压力	需保证缓存与数据库数据一致性
分表 + 哈希路由	超大规模用户消息	分散单表读写压力	应用层需封装分表路由逻辑

核心原则：

1. 未读数量统计优先通过 user_id + is_read 索引实现，避免全表扫描；
2. 高并发场景下，Redis 缓存是降低数据库压力的关键；
3. 分离消息模板与用户关联表，减少数据冗余。