CSDN私信功能聊天系统表结构设计优化方案

核心表结构设计

1. 用户表 (users)

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    avatar VARCHAR(255),
    status TINYINT COMMENT '0-离线,1-在线',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username)
);

2. 会话表 (conversations)

CREATE TABLE conversations (
    conversation_id BIGINT PRIMARY KEY,
    conversation_type TINYINT NOT NULL COMMENT '1-单聊,2-群聊',
    title VARCHAR(100) COMMENT '群聊标题',
    creator_id BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_creator (creator_id),
    INDEX idx_updated (updated_at)
) ENGINE=InnoDB;

3. 会话参与者表 (conversation_participants)

CREATE TABLE conversation_participants (
    id BIGINT PRIMARY KEY,
    conversation_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    join_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_read_message_id BIGINT COMMENT '最后阅读的消息ID',
    unread_count INT DEFAULT 0 COMMENT '未读消息数',
    is_muted TINYINT DEFAULT 0 COMMENT '是否静音',
    INDEX idx_conversation_user (conversation_id, user_id),
    INDEX idx_user_conversation (user_id, conversation_id),
    FOREIGN KEY (conversation_id) REFERENCES conversations(conversation_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB;

4. 消息表 (messages)

CREATE TABLE messages (
    message_id BIGINT PRIMARY KEY,
    conversation_id BIGINT NOT NULL,
    sender_id BIGINT NOT NULL,
    content TEXT,
    content_type TINYINT DEFAULT 1 COMMENT '1-文本,2-图片,3-文件,4-链接',
    status TINYINT DEFAULT 1 COMMENT '1-发送中,2-已发送,3-已送达,4-已读',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_conversation_created (conversation_id, created_at),
    INDEX idx_sender (sender_id),
    FOREIGN KEY (conversation_id) REFERENCES conversations(conversation_id),
    FOREIGN KEY (sender_id) REFERENCES users(user_id)
) ENGINE=InnoDB PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p2023 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01')),
    PARTITION p2024 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

5. 消息已读状态表 (message_read_status)

CREATE TABLE message_read_status (
    id BIGINT PRIMARY KEY,
    message_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_message_user (message_id, user_id),
    INDEX idx_user_message (user_id, message_id),
    FOREIGN KEY (message_id) REFERENCES messages(message_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=InnoDB;

查询优化策略

1. 会话列表查询优化

-- 获取用户会话列表（按最后更新时间排序）
SELECT 
    c.conversation_id,
    c.conversation_type,
    c.title,
    m.content AS last_message,
    m.created_at AS last_message_time,
    cp.unread_count
FROM 
    conversation_participants cp
JOIN 
    conversations c ON cp.conversation_id = c.conversation_id
LEFT JOIN 
    messages m ON m.message_id = (
        SELECT message_id 
        FROM messages 
        WHERE conversation_id = c.conversation_id 
        ORDER BY created_at DESC 
        LIMIT 1
    )
WHERE 
    cp.user_id = ?
ORDER BY 
    m.created_at DESC;

优化点： - 使用LEFT JOIN和子查询获取最新一条消息 - 添加复合索引 (user_id, conversation_id) 和 (conversation_id, created_at)

2. 消息历史查询优化

-- 分页获取会话消息
SELECT 
    m.message_id,
    m.sender_id,
    u.username AS sender_name,
    m.content,
    m.content_type,
    m.status,
    m.created_at
FROM 
    messages m
JOIN 
    users u ON m.sender_id = u.user_id
WHERE 
    m.conversation_id = ?
ORDER BY 
    m.created_at DESC
LIMIT ?, ?;

优化点： - 使用分区表按时间范围分区 - 添加复合索引 (conversation_id, created_at) - 使用延迟关联优化深度分页

3. 未读消息计数优化

-- 使用触发器或定期任务更新未读计数
UPDATE conversation_participants cp
SET cp.unread_count = (
    SELECT COUNT(*) 
    FROM messages m
    WHERE m.conversation_id = cp.conversation_id
    AND m.created_at > (
        SELECT read_at 
        FROM message_read_status 
        WHERE user_id = cp.user_id 
        AND message_id = cp.last_read_message_id
    )
)
WHERE cp.user_id = ?;

高级优化方案

1. 读写分离：将消息查询和会话列表查询分离到不同数据库实例

2. 缓存策略：

   • 使用Redis缓存热门会话的最新消息
   • 缓存用户会话列表
   • 使用布隆过滤器减少无效查询

3. 消息分区策略：

   • 按时间范围分区（如每月一个分区）
   • 对历史消息使用归档表

4. 异步处理：

   • 消息已读状态异步更新
   • 未读计数异步计算

5. 数据分片：

   • 按用户ID哈希分片存储消息
   • 按会话ID哈希分片存储参与者信息

6. 索引优化：

   • 为高频查询添加覆盖索引
   • 定期分析并优化索引使用情况

通过以上设计，可以高效支持CSDN私信功能的会话列表展示、消息历史查询和实时消息推送等核心功能，同时保证系统在大规模用户下的性能表现。