Db Schema Design

---
name: db-schema-design
description: 为 Go 后端设计或评审数据库结构：MySQL / PostgreSQL 表、MongoDB 集合、索引与 migration，产出设计文档与迁移文件。用户说「设计表结构」「建表」「数据库设计」「加字段」「加索引」「评审 schema」「设计集合」，或新功能需要新增持久化数据时使用。只修改查询语句、不涉及结构变更时不使用。
---

# 数据库结构设计

产出两样东西：`docs/design/` 下的设计说明，以及项目 migration 目录中的迁移文件。逐条规则在 `.claude/rules/database/` 中，编辑相应文件时会自动加载；本 skill 负责设计流程与取舍。

## 1. 收集输入

项目里能查到的直接查，查不到再问用户：

- 业务实体、字段、状态流转；哪些字段由其他系统定义（订单号、SKU、用户名等）
- 读写模式：主要查询条件与排序、每秒读写量、单表一年内的数据量、是否需要归档或清理
- 报表与统计的口径，例如「成功率」按发起时间还是结果时间统计
- 已知的下一期需求
- 存储选型：MySQL、PostgreSQL 或 MongoDB；现有 migration 工具（golang-migrate、goose、atlas 等）与命名方式

## 2. 明确大小写语义

跨系统数据（定义方与使用方不在同一个系统）最容易出现大小写不一致导致匹配失败：

- 在设计文档中逐个写明标识类字段（编码、用户名、外部单号）是否区分大小写；没有特别说明时按**区分大小写**处理。
- MySQL 默认排序规则不区分大小写，区分大小写的字段要显式设 `COLLATE utf8mb4_bin`；不要在已上线的表上直接修改排序规则，先评估所有调用方。
- 接口文档同样写明入参与返回值是否区分大小写。
- 由用户输入的跨系统数据，先按定义方的规则校验，并用校验返回的标准值替换用户输入后再存储或传给下游。

## 3. 设计

按节点推进，每个节点的产物写进设计文档对应章节，前一个节点没定下来不进入下一个。

1. **概念模型**：从需求中圈出名词，区分实体（有独立生命周期，需要单独查询）、属性和关系。留意隐含信号：「一单多个商品」意味着多对多关联表；「当时的」「不能跟着变」意味着快照；「可以存好几个」意味着独立子表；「以后可能加层级」意味着自参照。把下单、支付、履约、退款、对账逐环走一遍，补上界面看不到的实体。
2. **逻辑模型**：先做到第三范式，再按「这个字段变了，历史该不该跟着变」决定反正规化，区分快照型与冗余计算型，后者写明维护方式和校验 SQL。
3. **物理模型**：字段类型、默认值、约定值、注释、大小写语义；对外编号与内部 `id` 分开。
4. **索引**：列出每个查询的 SQL 骨架，逐条反推索引，按 ESR 排列；子表的父 id 都要覆盖；对应不上查询的索引不建。
5. **容量与演进**：三年容量，索引空间单独算；分表触发条件、分表前的手段、归档方案、为下一期的预留。

参考资料：

- MySQL：`references/mysql.md`（规则理由、ESR 原理与实测、软删除、反正规化、容量与演进）；`references/sql-pitfalls.md`（会静默出错或拖慢的 SQL 写法）；`references/case-ecommerce.md`（电商订单库的取舍与销售排行索引实测）；`references/case-ai-platform.md`（放弃代理键后的连锁代价，以及 PostgreSQL 设计搬到 MySQL 时的问题）；`references/anti-patterns.md`（评审中反复出现的错误）。
- MongoDB：`references/mongodb.md`（建模三步、内嵌与引用、设计模式、索引与分页实测、读写关注与事务实测、过时说法）；`references/case-mongodb.md`（电商库存与聊天未读数的取舍与实测）。节点 2 改为按读写工况决定内嵌、引用或冗余；节点 4 列出每个查询的过滤、排序骨架并用 `explain` 核对。
- 需要缓存时接着使用 `cache-design`；需要异步消息时使用 `mq-design`。

## 4. 产出

设计文档 `docs/design/<模块>-db.md` 按 `references/design-doc-template.md` 编写；小改动（加一两个字段或索引）只填相关章节，并补上对现有数据的影响和回滚方式。

迁移文件按项目工具的格式成对编写（up / down）。

MySQL 的迁移文件提交前在临时库验证（以下步骤只在 MySQL 8.0 上实测过，PostgreSQL、MongoDB 未验证）：

- 临时库用一次性容器（`docker run --rm`、不映射端口），不在项目共用或测试用的库里建库，也不借容器内 root 绕过只读账号。
- 先灌入种子数据再跑；空库通过不算验证，唯一索引、数据转换类 migration 要用含重复或边界值的数据试跑。
- 逐表比对 up、down 前后的 `SHOW CREATE TABLE` 结果，排除工具的版本表，并去掉 `AUTO_INCREMENT=` 值（down 之后它不会回到原值）。只查 `information_schema.columns`、`statistics` 会漏掉表注释等表选项。
- 用 `COUNT(*)` 比对 up 前后每张表的行数，不用 `information_schema.tables.table_rows`（估算值）。
- schema 与行数都相同，不代表 down 能恢复数据：删列或改写数据的 migration，在 up、down 之后与原值比对 `CHECKSUM TABLE`（schema 变了它本来就会变，不用在 up 前后比）；确认不可恢复时在回滚步骤写明。

## 5. 自查

DDL 写完后自查一遍 `.claude/rules/database/` 中的规则与下列清单，向用户汇报设计要点、与规则不一致之处（如有）及理由，以及没有实测、需要上线前验证的假设。

- 概念：每个实体都对应到需求条目；多对多有关联表；快照需求逐条落实；业务链路走过一遍。
- 逻辑：每处冗余都标明类型和维护方式；冗余计算字段有校验 SQL。
- 物理：每张表自增 `bigint` 主键，表间关联存父表 `id`；全部 `NOT NULL`，约定值写进注释，例外的 `NULL` 有 ADR；状态字段注释列全取值；标识类字段用 `VARCHAR`；没有保留字；唯一约束基于稳定的业务规则。
- 索引：每个索引都有对应的查询；ESR 顺序；范围列与排序列不同时的取舍已写明；没有冗余索引；分页排序键唯一；全表聚合改为预计算。
- MongoDB：内嵌数组有上限；冗余字段写明对方修改时如何同步；数组条件用 `$elemMatch`；组合索引最多一个数组字段；需要事务的写入先确认无法用单文档原子更新替代。
- 容量：按实际平均长度估算；加上隐藏列和膨胀系数；索引空间单独列出。
- 交付：ADR 的代价已量化；待确认问题排了优先级；ER 图、文档与 DDL 一致。

## 6. 上线前提供的信息

新库或新集合上线时，向 DBA 或运维提供：系统名、库名、用户类型（C 端、B 端、内部）、数据库版本、是否需要备份及保留天数、宕机影响的业务、敏感字段、一年内的 QPS 峰值与最大表数据量。