数据建模方法论：从业务到表结构的设计经验总纲

⭐⭐⭐DDL 与大表5.7 & 8.0数据建模范式表设计命名规范外键反模式ER 模型建表规范

表结构一旦上线就活十年：数据建模的方法论与经验清单

很多性能问题在 CREATE TABLE 敲下去的那一刻就已经注定了。字段类型随手选、实体关系靠直觉、命名全看心情，数据量小的时候毫无感知，三年后改不动、迁不走、拆不开。

本案例不讲某一个具体优化点，而是讲建表与数据建模的完整方法论：从业务里识别实体、用范式约束结构、为查询设计索引、为生命周期预留拆分手段，最后给出一份可直接落地的建表 Review 清单。它是 DDL 分类下各专题的总纲：

主题	深挖案例
字段类型 / 长度 / 精度	案例55
主键与自增序列	案例69、14、61
增删列的版本能力	案例46、48
大表拆分 / 分区 / 碎片	案例54、45、47
软删除 / JSON / 冷热分离	案例65、57、54

一、建模流程：先有业务模型，再有表结构

业务文档/需求
   │  ① 名词梳理：找出需求描述中的"名词"——订单、用户、商品、优惠券
   ▼
实体识别（Entity）
   │  ② 关系梳理：一个订单属于一个用户；一个订单包含多个商品
   ▼
ER 图（实体-关系图）
   │  ③ 属性落位：每个实体有哪些属性？属性挂在哪个实体上才不冗余？
   ▼
关系模式（表 + 列 + 主外键）
   │  ④ 范式校验 + 反范式取舍 + 查询场景反推索引
   ▼
物理建表（DDL + 索引 + 注释 + 容量评估）

经验 1：需求里的名词就是实体，动词就是关系或服务。"用户下单、订单包含商品、优惠券抵扣订单金额"——名词提取出 user / order / product / coupon，动词提取出"下单（关系）"和"抵扣（关联属性）"。

经验 2：属性挂错实体是最早期的冗余。比如把"商品名称"同时放在订单明细表和商品表，订单快照看似合理，但若没有明确的"快照"语义，改名后数据就会对不上。判断方法：问一句"这个属性描述的是谁？"——商品名称描述商品，订单数量描述订单与商品的关系。

经验 3：设计时要同时拿到两份输入——实体关系（决定结构）和核心查询场景（决定索引和反范式）。只看 ER 建出来的表范式完美但查询全靠 JOIN，只看查询建出来的表上线即冗余。

二、命名规范：让表结构会"自我介绍"

对象	规范	正例 / 反例
表名	全小写蛇形、单数名词、业务前缀分组	`t_order_item` ✅ / `OrderItems`、`dingdan` ❌
字段名	蛇形、表意完整、不带表名前缀	`user_id`、`paid_at` ✅ / `uid`、`order_amount`（在订单表里）❌
主键	统一叫 `id`	—
外键列	`<引用表单数>_id`	`order_id`、`user_id`
唯一索引	`uk_<列名组合>`	`uk_order_no`
普通索引	`idx_<列名组合>`	`idx_user_status`
时间字段	动宾过去式 + `_at`	`created_at`、`paid_at`、`updated_at`
布尔字段	`is_ / has_` 开头	`is_vip`、`is_deleted`
金额字段	`<业务含义>_amount`	`pay_amount`、`refund_amount`

三条红线:

不使用数据库保留字:order、group、desc、range、type 做列名会让每条 SQL 都要加反引号,漏一个就语法错误。
不使用拼音和中英混用:shoujihao、wx_user 三个月后自己都忘了含义;缩写只用团队公认的(no 编号、amt 金额这类少用,优先完整单词)。
命名体系全公司一套:表名前缀、时间后缀、布尔前缀写进开发规范,新成员按表名就能推断结构。

三、主键设计:内部主键与对外编号分离

要点(完整展开见案例69):

决策点	建议
主键类型	一律 `BIGINT NOT NULL`,不用 INT(耗尽见案例68)
主键生成	单库自增;分布式用雪花/号段;TiDB 热点写入用 AUTO_RANDOM
业务含义	主键无业务含义,不对外展示、不放进 URL、不用作订单号
对外编号	独立 `order_no` 列 + 唯一索引,日期+业务码+随机位
自然键	身份证号、手机号会变更、有隐私问题,不做主键,只做唯一索引

经验:主键策略要在项目第一天定死。中途从自增切雪花、从单库改分片,是存量数据迁移里最贵的操作之一。

四、范式与反范式:先学会守规矩,再学会破规矩

三个范式的实用记法

范式	要求	反例 → 修正
1NF	列不可再分,一个字段只存一个值	`tags = "1,3,7"` → 关联子表或 JSON
2NF	非主属性完全依赖整个主键(联合主键下尤其)	(订单号,商品名)做主键,商品名只依赖商品名 → 拆商品表
3NF	非主属性直接依赖主键,不传递依赖	订单表存 `user_id + user_name + user_level` → 用户信息拆用户表

什么时候反范式(有意识地冗余)

范式消除冗余、保证一致,代价是查询要 JOIN;反范式用冗余换查询性能,必须有配套的一致性手段:

场景	冗余方式	一致性保障
订单展示商品当时的名称/价格	订单明细冗余 `product_name`、`price`	快照语义:下单那一刻的值永不更新,不算冗余算历史
列表页高频读用户等级名称	冗余 `level_name`	用户改等级时双写 / 异步消息同步
报表计数(用户订单数)	冗余 `order_count`	下单/退款事务内维护,或定时校准
大宽表报表	汇总表整表冗余	离线/准实时重算,见案例60

判断口诀:这个冗余是"快照"还是"副本"? 快照(下单时的价格)不需要同步,放心冗余;副本(当前的用户名)必须回答"谁更新它、多久一致、不一致时以谁为准",回答不了就先按范式拆。

五、字段设计:除了类型,还要决定 NULL、默认值和时间语义

类型选择见案例55,这里讲类型之外的四个决策:

5.1 NULL 还是 NOT NULL

策略	适用
`NOT NULL` + 合理默认值	绝大多数业务字段;NULL 会让 `!=`、`NOT IN`、索引统计、聚合都产生反直觉行为
允许 NULL	业务上"未知/不适用"与"默认值"语义确实不同时(如 `paid_at`:未支付必须是 NULL,不能用 1970 年时间冒充)

经验:把 NULL 当成第三种布尔值对待——一旦允许,每个查询都要想 IS NULL 的分支;COUNT(col) 不统计 NULL、col NOT IN (NULL 集合) 恒为空,这些坑案例27 有演示。

5.2 默认值

默认值要表达"未设置时的真实语义",不要偷懒用 '' 或魔法值(0、-1)填空;
状态类默认值取初始状态(订单默认"待支付");
8.0 加列的默认值只存元数据,不逐行写入(案例46/48)——但反过来,改默认值语义要确认历史数据的处理。

5.3 时间字段(重灾区)

决策	建议
类型	`DATETIME` 为主(与时区无关的本地时间);存跨时区的统一时刻再考虑 TIMESTAMP,注意 2038 和自动更新行为
审计四件套	`created_at / updated_at` 必备;状态机字段用事件时间:`paid_at`、`shipped_at`、`closed_at`
禁止	时间存 VARCHAR(案例33)、用字符串拼日期做比较
自动维护	`updated_at` 用 `ON UPDATE CURRENT_TIM_TIMESTAMP`,但注意应用层批量更新框架可能绕过

5.4 状态与枚举

枚举值稳定且少 → TINYINT + 应用层映射/字典表(首选)
需要直接在库内可读 → ENUM(改值需 DDL,见案例55 对比)
禁止 → VARCHAR 存中文状态('待支付'),索引大、比较慢、易写错

状态机建议画清楚流转图(待支付→已支付→发货→完成;任意状态→取消/退款),代码和表结构按同一套流转实现,避免出现无法流转的"死状态"。

六、表关系:1:1、1:N、N:N 与外键取舍

三种关系的物理实现

关系	实现	例子
1:1	任一侧加唯一外键;或主键同时作外键	用户 ↔ 用户实名信息(按访问频率/字段大小拆,见案例54)
1:N	"多"的一侧放"一"的外键	用户 1:N 订单 → `order.user_id`
N:N	中间关联表,两端各一个外键,联合唯一	订单 N:M 商品 → `order_item(order_id, product_id)`

外键约束:用还是不用

	物理外键	应用层保证
一致性	数据库强制,最强	依赖代码质量,需审查
写入性能	额外检查,有锁成本,级联操作可能放大锁范围	无额外开销
迁移/分库分表	障碍大,分片后基本不可用	无影响
适用	内部系统、低并发、强一致要求	互联网高并发、预规划分片的业务(主流选择)

经验:互联网业务通常不用物理外键,但不等于不做关系校验——在应用服务层集中保证引用完整性,并给每个外键列建索引(MySQL 不会自动给外键列以外的列建索引;user_id 这类列的索引同时服务 JOIN 和反查)。级联删除(ON DELETE CASCADE)在生产上慎用,一条删除沿着外键链可能锁掉半库。

七、经典建模反模式(见到就要重构)

反模式 1:EAV 万能属性表

sql

-- 一张表存所有实体的动态属性
CREATE TABLE entity_attribute(entity_id BIGINT, attr_name VARCHAR(50), attr_value VARCHAR(500));

问题:值被迫存成字符串(丧失类型和约束)、查一个实体的多个属性要多次自连接、无法加外键、无法做 NOT NULL/唯一约束。数据量和查询复杂度同时爆炸。替代:稳定属性进主表;真·稀疏动态属性用 JSON(MySQL 8.0,案例64)或文档数据库;频繁变化的属性模型先回去改业务设计。

反模式 2:多态关联(一个外键指向多张表)

sql

CREATE TABLE comment(id BIGINT, target_type VARCHAR(20), target_id BIGINT);
-- target_type='order' 指向订单,target_type='post' 指向帖子

问题:无法建物理外键、关联查询要按类型分支、target_id 在不同表里类型可能不同、索引基本失效。替代:拆成 order_comment / post_comment 各自带真正的外键(推荐);或用"交集表(基表)+ 各类型扩展表"。

反模式 3:单表继承与"万能状态列"

所有业务对象共用一张表,靠 type 区分,大半列对大半行恒为 NULL。查询要带 type、约束无法表达、表越来越宽。替代:字段差异大就拆表(具体表继承);共享部分抽公共表,差异部分各建扩展表(类表继承)。

反模式 4:列里存列表(逗号分隔)

sql

SELECT * FROM t WHERE FIND_IN_SET(7, tag_ids);   -- 用不上索引、无法 JOIN、无法约束

替代:标准 N:M 关联表;纯展示且无需按标签关联查询时,才用 JSON 数组并配合 JSON_CONTAINS/多值索引。

其他高频小反模式

反模式	问题	正解
图片/文件本体入库(BLOB)	表膨胀、备份慢、列表 IO 放大(案例47)	存对象存储,库里只存 URL/key
重复造审计字段(`create_name` 存中文名)	用户改名后历史失真	存 `created_by` 用户 ID
布尔状态用多列(`is_paid/is_done/is_finish`)	状态散落,组合出非法状态	单一状态列 + 状态机
预留"扩展字段" ext1/ext2/ext3	无人知道含义,最终全是垃圾	用 JSON,或真有需求时正常加列(8.0 INSTANT 很便宜)

八、索引先行:建表时就要回答"这张表怎么查"

建表阶段就要列出 Top 5 查询 并反推索引,而不是等慢查询报警后补:

每列的查询方式:等值 / 范围 / 排序 / 模糊 —— 决定联合索引的列序(等值列在前、范围列在后,案例7);
高频列表能否用覆盖索引(案例8);外键列、状态列是否需要独立索引;
控制索引数量:每个二级索引都有写入和空间成本(案例20、10),一般单表不超过 5 个,超出要解释取舍;
低基数列不单独建索引(案例12),组合索引里放在后面。

经验:索引是查询的物化设计。建表评审时说不出"这个索引服务哪条 SQL"的索引,先不要建。

九、生命周期设计:为三年后预留退路

手段	什么时候在建模时就预留
时间字段	任何会持续增长的表都要有可靠的 `created_at`——它是以后分区、归档、分片的唯一依据
RANGE 分区	日志/流水类表按时间分区,裁剪见案例49
冷热分离	明确状态字段(完成/关闭)支持归档判定,案例61
分片键	高增长表提前选好分片维度(通常是 user_id),避免以后按非分片键全广播,案例66
软删除	需要留痕/可恢复时用 `deleted_at`(案例65);不需要时物理删除或归档,别让软删数据拖慢所有查询
容量评估	建表时估算 `行数 × 行宽 × (1+索引系数)` 得出一年/三年体积,提前规划磁盘和分库

核心经验:时间维度和状态维度是大表的两条逃生通道,建表时就要留好。

十、工程规范:字符集、注释与审计

sql

CREATE TABLE t_order (
    id           BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键',
    order_no     VARCHAR(32)  NOT NULL COMMENT '对外订单号：日期+渠道+随机位',
    user_id      BIGINT       NOT NULL COMMENT '下单用户 ID',
    status       TINYINT      NOT NULL DEFAULT 0 COMMENT '状态：0待支付 1已支付 2已发货 3完成 4取消',
    pay_amount   DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '实付金额，精确到分',
    created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单主表';

字符集全库统一 utf8mb4:表情、生僻字不炸库;排序规则 8.0 用 utf8mb4_0900_ai_ci,跨实例同步注意与 utf8mb4_general_ci 的比较差异;
每个列、每个枚举值写注释:枚举注释直接写编码映射(如上 status),后人不用翻代码;
表注释写用途和归属:哪张服务在用、生命周期多长;
同一张表的建表语句进版本管理(SQL migration 工具:Flyway/Liquibase/自研变更脚本),禁止直接在生产手敲 DDL 不留痕。

建表 Review 清单(可直接拷到 PR 模板)

上线前逐项过

结构

[ ] 实体来自需求名词,属性挂在正确的实体上,有 ER 图或等价说明
[ ] 命名符合规范(蛇形、后缀、索引前缀),无保留字、无拼音
[ ] 主键 BIGINT 无业务含义;对外编号独立列
[ ] 范式校验过;每处反范式冗余标明"快照 or 副本"及同步手段
[ ] 关系实现正确(1:1/1:N/N:N);不用物理外键时有应用层一致性方案

字段

[ ] 类型按案例55 选择:金额 DECIMAL、枚举 TINYINT、VARCHAR 按需定长
[ ] NULL 策略明确,不用魔法默认值
[ ] created_at / updated_at 齐全;状态机有流转图
[ ] 无反模式:EAV、多态关联、逗号分隔、文件入库、ext 预留列

性能与生命周期

[ ] 列出 Top 查询并反推索引;每个索引能指出服务的 SQL
[ ] 外键列有索引;索引数量合理
[ ] 有时间/状态两条逃生通道;增长表有分区、归档或分片规划
[ ] 容量估算:一年/三年行数与体积

工程

[ ] utf8mb4;列/枚举/表注释完整
[ ] DDL 走 migration 留痕,评审过回滚方案

一个完整示例:同一张订单表的两种设计

sql

-- ── bad:一次典型的"随手建模" ──
CREATE TABLE dingdan (
    dingdan_id   INT AUTO_INCREMENT,
    OrderNumber  VARCHAR(255),              -- 编号含义不清,VARCHAR(255)
    shouji       VARCHAR(255),              -- 拼音;本该在用户表
    userName     VARCHAR(255),              -- 冗余用户当前名,无同步方案
    jine         FLOAT,                     -- 金额用浮点
    zhuangtai    VARCHAR(20) DEFAULT '待支付', -- 中文枚举
    ext1         VARCHAR(255),              -- 预留垃圾位
    biaoji       VARCHAR(100),              -- 逗号分隔标签 "1,3,7"
    PRIMARY KEY(dingdan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;       -- 非 utf8mb4

问题:命名混乱、类型全错、无时间审计字段、反模式齐全、没有任何逃生通道。重构后的版本见第十节的标准订单表——结构、字段、索引、注释、生命周期一次到位。

指标	优化前 (bad)	优化后 (good)
访问类型	-	-
使用索引	-	-
扫描行数	-	-
附加信息	-	-

🚀 从一次注定三年后重构的随手设计,到结构、性能、可演进性都可评审的工程化设计

常见误区答疑

Q:范式越高越好吗? 不是。范式是防错工具不是性能目标。OLTP 系统按 3NF 打底再按需反范式;分析型宽表本就反范式。为了"纯粹"而拒绝订单快照这类冗余,反而是新手表现。

Q:小表也要这么重的流程吗? 清单是上限不是下限。配置表、字典表可以极简,但主键 BIGINT、utf8mb4、时间字段这三条几乎零成本,建议无差别执行——今天的配置表可能就是明天的核心表。

Q:物理外键到底禁不禁? 看阶段:单体内部系统、并发不高、没有分片计划,物理外键是便宜的一致性保险;一旦规划高并发或分库分表,提前用应用层方案,不要等数据量大了再拆外键(同样是锁表迁移)。

Q:JSON 是不是新的万能字段? 不是,它替代的是 EAV 和 ext1/ext2,不是所有关系。需要强约束、需要频繁按字段 JOIN、字段集合稳定的,进正式列。JSON 的使用边界案例64 有完整展开。

本地复现

本篇是方法论,建议用"给坏表做 Review"的方式练习:

bash

# 1. 进入 MySQL 8.0 客户端
docker exec -it sql-treasure-mysql80 mysql -uroot -proot sql_treasure

# 2. 建出文中的 bad 表,对照 Review 清单逐条找问题
#    (dingdan 表 DDL 见"完整示例"一节)

# 3. 不删表,用 ALTER 逐项修正,体会设计债的修复成本:
ALTER TABLE dingdan MODIFY jine DECIMAL(10,2);     -- 改类型:案例53 的锁风险
ALTER TABLE dingdan ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
-- 结论先行:建表时花两小时评审,省的是上线后几十小时的迁移

# 4. 对照阅读:51(类型) → 114(主键) → 58(软删除) → 45(分区)

数据建模方法论：从业务到表结构的设计经验总纲 ​

表结构一旦上线就活十年：数据建模的方法论与经验清单 ​

一、建模流程：先有业务模型，再有表结构 ​

二、命名规范：让表结构会"自我介绍" ​

三、主键设计:内部主键与对外编号分离 ​

四、范式与反范式:先学会守规矩,再学会破规矩 ​

三个范式的实用记法 ​

什么时候反范式(有意识地冗余) ​

五、字段设计:除了类型,还要决定 NULL、默认值和时间语义 ​

5.1 NULL 还是 NOT NULL ​

5.2 默认值 ​

5.3 时间字段(重灾区) ​

5.4 状态与枚举 ​

六、表关系:1:1、1:N、N:N 与外键取舍 ​

三种关系的物理实现 ​

外键约束:用还是不用 ​

七、经典建模反模式(见到就要重构) ​

反模式 1:EAV 万能属性表 ​

反模式 2:多态关联(一个外键指向多张表) ​

反模式 3:单表继承与"万能状态列" ​

反模式 4:列里存列表(逗号分隔) ​

其他高频小反模式 ​

八、索引先行:建表时就要回答"这张表怎么查" ​

九、生命周期设计:为三年后预留退路 ​

十、工程规范:字符集、注释与审计 ​

建表 Review 清单(可直接拷到 PR 模板) ​

一个完整示例:同一张订单表的两种设计 ​

常见误区答疑 ​

本地复现 ​