表设计与范式

开篇：一张设计糟糕的表能有多可怕？

想象你接手了一个电商项目，打开数据库一看：用户表里有个 address 字段，塞着"浙江省杭州市西湖区xxx路xxx号xxx小区xxx单元xxx室"这样一整坨字符串。前端说要按城市筛选用户？后端说要按省份统计订单？对不起，全都要 LIKE 模糊匹配，索引用不上，全表扫描伺候。

更惨的是，有人用 VARCHAR(20) 存手机号，用 DOUBLE 存金额（精度丢失直接少收钱），用 TEXT 存一个只有"是/否"两种值的状态字段。结果呢？单表 500 万行，存储膨胀了 10 倍，查询全是全表扫描。

表设计是数据库的地基。地基歪了，后面索引优化、SQL 调优全是在歪楼上贴瓷砖。这篇文章带你系统学习表设计的核心知识：范式、数据类型选择、主键设计和常用规范。

一、三大范式与反范式

1.1 什么是范式？

范式就是数据库表设计的"规矩"。遵守这些规矩，可以让表结构更清晰、数据更干净、冗余更少。常用的有三个范式，一个比一个严格。

1.2 第一范式（1NF）：字段不可再拆

核心思想：每个字段的值必须是原子的，不能再拆分。

举个生活中的例子。你去邮局寄快递，收件地址写"浙江省杭州市西湖区文三路 100 号"，邮局能送。但如果你的数据库也这样存，要按城市查询就麻烦了。

-- 不符合 1NF：地址是一个整体，无法按省/市/区检索
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    address VARCHAR(200)   -- '浙江省杭州市西湖区...'
);

-- 符合 1NF：拆分成独立字段
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    province VARCHAR(20),
    city VARCHAR(20),
    district VARCHAR(20),
    street VARCHAR(100)
);

1.3 第二范式（2NF）：消除部分依赖

核心思想：在满足 1NF 的基础上，所有非主键字段必须完全依赖主键，不能只依赖主键的一部分。

这个主要针对联合主键的场景。比如一张"学生选课成绩表"：

-- 联合主键 (student_id, course_id)
-- 但 student_name 只依赖 student_id，跟 course_id 无关
-- 这就是"部分依赖"，不符合 2NF
CREATE TABLE score (
    student_id INT,
    course_id INT,
    student_name VARCHAR(50),  -- 只依赖 student_id
    score DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id)
);

解决办法是拆表：学生信息放一张表，成绩放一张表。

1.4 第三范式（3NF）：消除传递依赖

核心思想：所有非主键字段之间不能有依赖关系，必须直接依赖主键。

比如一张订单表里存了 customer_id 和 customer_name，而 customer_name 其实依赖的是 customer_id，不是直接依赖订单主键。这就是传递依赖。

-- 不符合 3NF：customer_name 通过 customer_id 传递依赖
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(50),  -- 依赖 customer_id 而非 order_id
    amount DECIMAL(10,2)
);

-- 符合 3NF：customer_name 只出现在 customers 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2)
);
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

1.5 什么时候该反范式？

范式虽好，但严格遵守意味着查询时要大量 JOIN。在读多写少、高并发的互联网场景下，JOIN 是很重的操作。

这时候可以适当做反范式 --- 在表中增加冗余字段，用空间换时间。

适合反范式的场景：

字段几乎不修改（比如用户的真实姓名）
查询时频繁需要，不冗余就得 JOIN
数据量大，JOIN 性能不可接受

反范式的代价：

存储空间增大
冗余字段修改时需要同步更新，否则数据不一致
更新频繁的字段不适合做冗余

一句话总结

范式是理想，反范式是现实。先按范式设计，再根据查询性能有针对性地做冗余。

二、数据类型选择

选对数据类型，就像给脚选对鞋 --- 大了浪费空间，小了挤脚出错。

2.1 整数类型：够用就好

类型	字节	有符号范围	无符号范围
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
INT	4	-21 亿 ~ 21 亿	0 ~ 42 亿
BIGINT	8	-922 京 ~ 922 京	0 ~ 1844 京

实战建议：

状态字段（0/1/2）：用 TINYINT，1 个字节搞定
普通业务 ID：用 INT UNSIGNED，42 亿完全够用
分布式 ID / 雪花算法 ID：用 BIGINT UNSIGNED
别用 INT(11) 里的 11 来限制存储范围，那个数字只影响显示宽度（而且 MySQL 8.0 已弃用这个特性）

-- 推荐写法
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status TINYINT UNSIGNED NOT NULL DEFAULT 0,
    age TINYINT UNSIGNED NOT NULL DEFAULT 0
);

2.2 字符串：CHAR vs VARCHAR

这是面试高频题，也是日常开发最容易踩坑的地方。

对比项	CHAR(N)	VARCHAR(N)
存储方式	定长，不够补空格	变长，按实际长度存储
额外开销	无	1-2 字节记录长度
适用场景	固定长度（手机号、身份证号、MD5）	可变长度（姓名、地址、描述）
内存碎片	少	可能产生碎片
尾部空格	检索时自动去掉	保留原样

VARCHAR(100) 和 VARCHAR(10) 有区别吗？

磁盘存储上，如果都存 6 个字符，占用空间一样。但在排序时有区别：MySQL 做 ORDER BY 时，会按字段定义的最大长度在内存中预分配空间。VARCHAR(100) 比 VARCHAR(10) 更容易撑爆 sort buffer，触发磁盘排序，性能更差。

所以原则是：按实际最大长度定义，不要随手写 VARCHAR(255)。

-- 手机号固定 11 位，用 CHAR
phone CHAR(11) NOT NULL,

-- 用户名长度不定，用 VARCHAR
username VARCHAR(32) NOT NULL,

2.3 时间类型：DATETIME vs TIMESTAMP

对比项	DATETIME	TIMESTAMP
存储空间	8 字节	4 字节
范围	1000-01-01 ~ 9999-12-31	1970-01-01 ~ 2038-01-19
时区	不受时区影响	存储 UTC，查询时自动转换
默认值	不支持 CURRENT_TIMESTAMP（5.6 之前）	支持

实战建议：

业务时间（如订单创建时间）：推荐 DATETIME，范围大，不受时区困扰
记录行的变更时间：TIMESTAMP 更方便，自动转时区
注意 TIMESTAMP 的 2038 年问题，长期数据别用它

create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2.4 金额存储：DECIMAL vs BIGINT

永远不要用 FLOAT / DOUBLE 存金额！ 浮点数有精度丢失问题，0.1 + 0.2 在计算机里可能等于 0.30000000000000004，直接影响资金安全。

两种靠谱方案：

方案	示例	优点	缺点
DECIMAL	`DECIMAL(10,2)`	精确计算，SQL 层直接运算	性能略低于整数
BIGINT（单位：分）	存 1999 表示 19.99 元	性能好，无精度问题	展示时需除以 100

-- 方案一：DECIMAL
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,

-- 方案二：BIGINT 存分
price_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,

互联网公司常用 BIGINT 存分 的方案，因为整数运算快、不存在精度问题。

2.5 BLOB vs TEXT

对比项	BLOB	TEXT
存储内容	二进制数据（图片、音频）	文本数据（文章、日志）
字符集转换	不支持	支持
排序	不支持	支持
变种	TINYBLOB / BLOB / MEDIUMBLOB / LONGBLOB	TINYTEXT / TEXT / MEDIUMTEXT / LONGTEXT

实际开发中，图片、视频等大文件一般存 OSS/CDN，数据库只存 URL。TEXT 类型也要慎用，尽量控制在必要的场景（如文章正文）。

2.6 字符编码：utf8mb3 vs utf8mb4

MySQL 中的 utf8 其实是 utf8mb3，每个字符最多 3 字节，不支持 emoji 表情和部分生僻汉字。

utf8mb4 每个字符最多 4 字节，是真正的 UTF-8，完整支持 Unicode。

-- 建表时指定 utf8mb4
CREATE TABLE posts (
    id BIGINT PRIMARY KEY,
    content TEXT NOT NULL
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 已有表从 utf8 迁移到 utf8mb4
ALTER TABLE posts
  DEFAULT CHARACTER SET utf8mb4,
  MODIFY content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

提示

MySQL 8.0 官方已明确 utf8mb3 将被废弃。建议所有新项目直接使用 utf8mb4。

三、主键设计

主键是一张表的身份证号，选错了后果很严重。

3.1 三种主流方案对比

方案	存储大小	是否有序	全局唯一	适用场景
AUTO_INCREMENT	4/8 字节	有序自增	单表唯一	单库单表，中小项目
UUID	36 字节（字符串）	无序	全局唯一	分布式系统，但不推荐做主键
雪花算法 (Snowflake)	8 字节	趋势递增	全局唯一	分布式系统，推荐

3.2 为什么推荐自增主键？

InnoDB 的数据按主键顺序存储（聚簇索引）。自增主键保证新数据总是追加在最后，避免了页分裂：

自增主键：新数据追加在最后，像排队一样有序
[1] [2] [3] [4] [5] [6] [7] [8] → [9] 追加

UUID 主键：新数据随机插入，导致频繁页分裂
[1] [5] [3] [8] [2] [7] → [4] 插到中间，页分裂！

3.3 UUID 的问题

太长：36 个字符，索引体积大，占内存多
无序：频繁导致 B+ 树页分裂，写入性能差
无业务含义：看到 550e8400-e29b-41d4-a716-446655440000 你知道它是什么吗？
不适合分页：无法用 WHERE id > last_id 做游标分页

UUID v7

UUID v7 基于时间戳排序，可以实现趋势递增，一定程度上解决了无序问题。但 JDK 原生尚未支持，需要引入第三方库。

3.4 自增主键的注意事项

自增主键一定是连续的吗？ 不一定。以下情况会导致不连续：

事务回滚：已分配的自增值不会回收
删除记录：被删行的 ID 不会被重用
INSERT ... ON DUPLICATE KEY UPDATE：尝试插入时已分配 ID，即使最终走了更新
服务器重启：MySQL 8.0 之前，自增计数器不持久化，重启后按 MAX(id)+1 重新计算

自增主键用完了会怎样？

显式自增 ID（如 INT UNSIGNED）：达到上限 42 亿后，下次申请还是 42 亿，插入会报主键冲突
隐式 row_id（没定义主键时）：达到上限后从 0 重新开始，覆盖旧数据且不报错

所以一定要显式定义主键，否则数据被悄悄覆盖比报错更可怕。

3.5 获取自增 ID 的性能瓶颈

MySQL 通过 AUTO-INC 锁 保证自增值的唯一性。高并发插入时，这个锁可能成为瓶颈。

优化方案：

号段模式：一次从数据库取一段 ID 缓存在本地，用完再取（如 TDDL、Leaf 的号段模式）
Redis 自增：用 Redis 的 INCR 命令生成 ID，性能远高于数据库
雪花算法：完全在内存中生成，不依赖数据库

四、常用设计规范

这些是互联网大厂总结出来的实战经验，建议作为团队规范推行。

4.1 命名规范

-- 表名：小写 + 下划线，带业务前缀
order_item, user_address, pay_record

-- 字段名：小写 + 下划线
user_name, create_time, is_deleted

-- 索引名：前缀 + 表名 + 字段名
idx_user_name      -- 普通索引
uk_user_phone      -- 唯一索引
pk_order_id        -- 主键索引

4.2 NULL 处理

尽量将字段设置为 NOT NULL，并给默认值。

为什么？

NULL 参与计算结果为 NULL：SELECT 1 + NULL 得到 NULL
NULL 值不走普通索引的等值查询
聚合函数（COUNT、SUM）会忽略 NULL
NULL != NULL 为 TRUE，容易引发逻辑 Bug

-- 推荐
status TINYINT UNSIGNED NOT NULL DEFAULT 0,
remark VARCHAR(200) NOT NULL DEFAULT '',

-- 不推荐
status TINYINT,
remark VARCHAR(200),

4.3 逻辑删除

线上数据一般不做物理删除（DELETE），而是用一个字段标记删除状态：

is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0-正常 1-已删除',

好处是数据可恢复、可审计。但要注意唯一索引要配合 is_deleted 一起建，否则"删除"后无法重新插入同值记录。

4.4 乐观锁字段

高并发更新场景下，用 version 字段实现乐观锁，避免脏写：

version INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',

更新时：

UPDATE orders
SET status = 2, version = version + 1
WHERE order_id = 1001 AND version = 3;
-- 影响行数为 0 说明被别人改过了，业务层做重试

4.5 时间字段

每张表都应该有 create_time 和 update_time：

create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

4.6 不推荐使用外键

阿里巴巴 Java 开发手册明确规定：不得使用外键与级联。一切外键概念必须在应用层解决。

原因：

性能：每次 INSERT/UPDATE/DELETE 都要检查外键约束
锁竞争：检查外键需要加锁，高并发下容易死锁
无法分库分表：外键不能跨库建立关系
逻辑删除冲突：外键是物理约束，与逻辑删除方案矛盾

数据一致性交给应用层保证，通过代码逻辑或消息队列最终一致性来实现。

表设计四件事
├── 范式与反范式：先规范，再按需冗余
├── 数据类型：够用就好，精确优先
├── 主键设计：自增 > 雪花 > UUID
└── 设计规范：NOT NULL、逻辑删除、乐观锁、时间字段

记住一个原则：表设计服务于查询。不是范式越高越好，也不是字段越少越好，而是让最频繁的查询跑得最快。设计表之前，先想清楚这张表最常被怎么查，答案自然就出来了。