存储落地：表结构与索引设计

前面几章反复出现“落库”“查历史”“推进位点”，本章把它们落到纸上：会话表、成员表、消息表（按会话分表）、已读位点表、同步位点表，以及支撑它们的索引。目标只有一句话：列表查询永远命中索引、消息写入永远追加、冷热数据永远可分离。

本章目标

给出可直接建库的完整 DDL：会话表、成员表、消息分表、已读位点表、同步位点表与辅助表；
讲清每个索引为什么这么建，并给出分表路由代码、验证用的 EXPLAIN 与归档策略。

表清单与分片策略

表	主键	分片/分区
`conv` / `conv_member`	`conv_id` / `(conv_id, uid)`	单表，成员表加 uid 二级索引
`conv_seq` / `msg_00`~`msg_15`	`conv_id` / `(conv_id, seq)`	序号表单表；消息表按 `conv_id % 16` 分表、只追加
`user_read_cursor` / `user_sync_cursor`	`(uid, conv_id)` / 加 `device_id`	按 `uid % 16` 分表，高频写（第 09 篇）

分片键的原则是让最高频的查询变成单分片查询：消息按会话读 → 用 conv_id 分；收件箱与位点按人读 → 用 uid 分。不要指望一张表同时满足两类查询，这正是 msg 与 offline_msg 必须分开的原因。

会话表与成员表

CREATE TABLE conv (
  conv_id    BIGINT UNSIGNED  NOT NULL COMMENT '会话 ID（雪花 ID）',
  conv_type  TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=单聊 2=群聊',
  owner_uid  BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '群主；单聊为 0',
  name       VARCHAR(64)      NOT NULL DEFAULT '' COMMENT '群名',
  member_cnt INT UNSIGNED     NOT NULL DEFAULT 0 COMMENT '成员数（冗余，扩散时预分配用）',
  max_seq    BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '已分配的最大序号（冗余，会话列表用）',
  created_at DATETIME(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (conv_id),
  KEY idx_owner (owner_uid, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='会话表';

CREATE TABLE conv_member (
  conv_id  BIGINT UNSIGNED  NOT NULL,
  uid      BIGINT UNSIGNED  NOT NULL,
  role     TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=成员 1=管理员 2=群主',
  read_seq BIGINT UNSIGNED  NOT NULL DEFAULT 0 COMMENT '已读位点：未读数 = conv.max_seq - read_seq',
  join_at  DATETIME(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (conv_id, uid),
  KEY idx_uid_join (uid, join_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='会话成员表';

PRIMARY KEY (conv_id, uid) 支撑群聊扩散（按会话顺序扫出全部成员，一次范围扫描），KEY idx_uid_join (uid, join_at) 支撑**“我的会话”**（按用户倒序取最近加入的会话，避免全表扫）。

read_seq 放在成员表而不是另建表，因为“用户 × 会话”的笛卡尔积本就在这张表里。单聊也要有两条成员行（同一 conv_id，两个 uid），这样单聊与群聊的扩散、权限、已读逻辑共用一套代码；用 conv_id = min(uidA, uidB) 之类的规则“省掉”单聊会话表看似聪明，代价是后面所有会话级功能都要写两套分支。

消息表：按会话分表

-- msg_00 ~ msg_15，按 conv_id % 16 分表；以下是单张分表的完整 DDL
CREATE TABLE msg_00 (
  conv_id    BIGINT UNSIGNED  NOT NULL COMMENT '会话 ID（分片键）',
  seq        BIGINT UNSIGNED  NOT NULL COMMENT '会话内序号，由 conv_seq 原子分配',
  msg_id     BIGINT UNSIGNED  NOT NULL COMMENT '全局消息 ID（雪花）',
  sender_uid BIGINT UNSIGNED  NOT NULL COMMENT '发送者',
  msg_type   TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=文本 2=图片 3=撤回 4=系统',
  content    VARBINARY(4096)  NOT NULL COMMENT 'zstd 压缩后的消息体（大对象走 OSS，此处存 URL）',
  status     TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=正常 1=已撤回 2=已删除',
  created_at DATETIME(3)      NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (conv_id, seq),
  UNIQUE KEY uk_msg_id (msg_id),
  KEY idx_sender (sender_uid, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='消息分片 00';

// 分片号来自取模，表名不接受外部输入，所以可以用字符串拼接（MySQL 占位符不能用于表名）
func msgTable(convID int64) string { return fmt.Sprintf("msg_%02d", uint64(convID)%msgShards) }

// Range 按会话增量拉历史：主键最左前缀 + 索引顺序与 ORDER BY 一致，无 filesort
func (s *MsgStore) Range(ctx context.Context, convID int64, fromSeq uint64, limit int) ([]*Msg, error) {
	q := fmt.Sprintf(`SELECT seq, msg_id, sender_uid, msg_type, content, created_at
	                  FROM %s WHERE conv_id = ? AND seq > ? ORDER BY seq ASC LIMIT ?`, msgTable(convID))
	rows, err := s.db.QueryContext(ctx, q, convID, fromSeq, limit) // limit 上限 100，由调用方收口
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make([]*Msg, 0, limit)
	for rows.Next() { // Scan 各列 + zstd 解压 content 后 append，此处略
		var m Msg
		out = append(out, &m)
	}
	return out, rows.Err()
}

uk_msg_id 只在分表内唯一就够：同一会话的消息恒定落在同一张分表，msg_id 是全局雪花，跨分表不可能撞号；这条唯一键让“重试导致重复写”在数据库层被挡下，是第 10 篇去重的最后一道闸。

已读位点与会话列表读模型

已读是用户级的（手机读过的，桌面也该标已读），同步位点是设备级的（第 09 篇），因此必须分成两张表：

CREATE TABLE user_read_cursor (
  uid        BIGINT UNSIGNED NOT NULL,
  conv_id    BIGINT UNSIGNED NOT NULL,
  read_seq   BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '已读到的会话内序号',
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (uid, conv_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户已读位点';

列表页则用一张读模型宽表 user_conv_list(uid, conv_id, last_seq, last_preview, unread, pinned, updated_at)：主键 (uid, conv_id)、索引 (uid, pinned, updated_at)。写入时多一次 upsert，换读路径永远命中索引、不必 join conv、conv_member、msg 三张表；宁可异步更新、接受列表最多晚 1 秒，也不要让 App 首页做多表 join。

索引设计的判断标准

三条判断：最左前缀是否被查询满足（建了 (conv_id, uid) 却按 uid 查成员，就得补 KEY idx_uid_join (uid, join_at)）；排序字段是否与索引同序（想按 seq DESC 取最新，范围列与排序列就要放进同一索引并保持同序，否则出现 filesort）；是否避免回表（列表只取 msg_id/seq/created_at，SELECT * 带出 4 KB 的 content 会让一次拉 100 条的查询翻十倍代价）。

两条硬规则：索引列不要参与运算或隐式转换（WHERE uid = '1001' 隐式转换、WHERE DATE(created_at) = ... 函数包裹列，都会失效）；写入放大要算总账——msg 表每多一个二级索引就多一次随机 IO，idx_sender 只服务低频的“搜我发的消息”，收益不够就该搬到 ES/ClickHouse。

归档与冷热分层

热表只留 180 天：定时任务每 10 分钟按主键范围搬一批到 msg_archive_xx（同结构、InnoDB 压缩页）或对象存储（按 conv_id/年月 存压缩包，元数据留在 MySQL），INSERT INTO ... SELECT 与 DELETE 各带 LIMIT 2000 循环执行以免大事务打满 binlog，两句的 affected rows 必须相等，不等说明有并发写入落进窗口需要重试。历史查询走“先热后冷”：若 fromSeq 小于热表最小 seq，再查归档表。

验证方式

-- 1) 会话内增量拉取：期望 type=range、key=PRIMARY、Extra 无 Using filesort
EXPLAIN SELECT seq, msg_id, content FROM msg_07
WHERE conv_id = 88001 AND seq > 1200 ORDER BY seq ASC LIMIT 50;

-- 2) 索引是否踩到 utf8mb4 的 3072 字节上限（SUB_PART 非空即用了前缀索引）
SELECT INDEX_NAME, COLUMN_NAME, SUB_PART FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'im' AND TABLE_NAME = 'msg_00' ORDER BY INDEX_NAME, SEQ_IN_INDEX;

常见坑

ORDER BY created_at 拉消息 / utf8mb4 长列建索引：同毫秒内多条时间戳相同会让顺序不稳定、客户端看到消息跳动，会话内排序只认 seq；VARCHAR(255) 在 utf8mb4 下占 1020 字节，多列组合索引容易撞上 3072 字节上限导致建表失败，这类列只取前缀或改存 hash。
大字段与热数据同表 / 未读数用 COUNT(*) 实时算：把图片 base64 塞进 content 会让热表体积膨胀十倍、缓存命中率暴跌，大对象一律走对象存储只存 URL；会话列表每次刷新几十次 count 是典型的 CPU 打满源头，未读数应该用位点差值或读模型冗余。
跨分片聚合 / 软删除滥用：SELECT COUNT(*) FROM msg_* 必须应用层 fan-out，统计需求走离线数仓；只标 status = 2 不删行会让索引里全是死行。

小结：存储设计的落点是“会话日志按 conv_id 分片 + 用户维度数据按 uid 分片”。msg 表以 (conv_id, seq) 为主键、msg_id 做唯一键，让追加写与增量读都退化为主键范围扫描；conv_member 的 (conv_id, uid) 支撑群聊扩散、(uid, join_at) 支撑会话列表；user_read_cursor 与 user_sync_cursor 分别管“读到哪”与“哪台设备收到哪”，列表页再用 user_conv_list 读模型换掉多表 join；索引按最左前缀与排序需求建，注意隐式转换与 utf8mb4 长度上限，超过 180 天的数据用主键范围批量归档。

笔记加载中…

标签：#IM #Go #项目实战

返回知识库