关系型数据库设计实战指南

好的数据库设计是系统的骨架——骨架歪了，后面的代码、性能、扩展性全是在替设计缺陷还债。

一、为什么数据库设计值得深究

数据库设计是后端开发中最容易被低估、却影响最深远的工作。很多团队在需求初期为了"快速上线"，随意拍脑袋建表，结果随着业务增长，暴露出以下问题：

变更成本指数级增长：上线前改表结构只需一条 DDL，上线后涉及数据迁移、停服窗口、回滚方案，成本相差 10-100 倍。
性能瓶颈的根源：慢查询的根因往往不是 SQL 写得差，而是表结构本身就不合理——字段类型选错导致隐式转换、缺少冗余字段导致多表 JOIN、主键策略不当导致写入热点。
扩展性受限：初期没有考虑分库分表的可能性，后期不得不做痛苦的数据拆分和迁移。
数据一致性隐患：缺少约束、外键依赖缺失、软删除设计不当，导致脏数据越积越多。

数据库设计不是"能存数据就行"，它决定了系统的全生命周期质量。 本文从实战角度，梳理从需求到落地的完整设计流程、核心原则、通用模式，并结合电商场景给出可直接参考的实践。

二、设计流程

数据库设计不是一上来就写 DDL，而是一个从抽象到具体的逐步细化过程。

2.1 需求分析 → 实体识别 → 关系梳理

一切从业务需求出发。拿到需求文档后，第一步是提取名词识别实体、提取动词识别关系。

以电商场景为例：

需求描述	提取的实体	提取的关系
用户浏览商品并加入购物车	User、Product、Cart	User 拥有 Cart，Cart 包含 Product
用户下单并支付	Order、Payment	User 创建 Order，Order 关联 Payment
商品属于某个分类	Category	Product 属于 Category

输出物：实体清单 + 实体关系图（ER 图草稿）。

2.2 概念模型 → 逻辑模型 → 物理模型

这是数据库设计的核心链路，三个阶段关注点不同：

阶段	关注点	输出物
概念模型	"系统里有哪些东西，它们之间什么关系"	ER 图
逻辑模型	"每个实体有哪些属性，主外键怎么定"	逻辑表结构
物理模型	"字段类型、长度、索引、约束"	可执行的 DDL

概念→逻辑这一步中高级开发者通常已很熟悉，不再展开。重点落在逻辑→物理的转化，这是实战中最容易踩坑的环节。

逻辑模型转物理模型的关键决策

(1) 多对多关系如何落地

逻辑模型中的多对多关系（如 Order ↔ Product），物理层面必须通过中间表拆解为两个一对多：

order              order_item              product
┌──────────┐      ┌──────────────────┐     ┌──────────┐
│ id (PK)  │──┐   │ id (PK)          │  ┌──│ id (PK)  │
│ ...      │  └──▶│ order_id (FK)    │  │  │ ...      │
└──────────┘      │ product_id (FK)  │◀─┘  └──────────┘
                  │ quantity         │
                  │ unit_price       │
                  └──────────────────┘

中间表不仅要存两个外键，还应存关系本身的属性——如订单明细中的 quantity（购买数量）和 unit_price（下单时单价，快照避免后续商品调价影响历史订单）。

(2) 冗余字段的引入决策

范式要求消除冗余，但实战中需要有目的地打破范式。判断标准：

读多写少的冗余：可以冗余。如 order_item 中冗余 product_name，避免查询时 JOIN product 表。
写频繁的冗余：避免冗余。如冗余 user 表的余额到 order 表，每次余额变更需要同步更新所有关联订单，一致性成本极高。
关键原则：冗余字段必须能通过源字段确定性地计算出来，且变更时能通过事务或消息队列保证最终一致性。

(3) 字段长度与类型的精确选择

这不是"差不多就行"的事情，直接影响存储和性能：

场景	错误选择	正确选择	原因
手机号	`VARCHAR(255)`	`VARCHAR(11)`	手机号固定 11 位，浪费存储空间
金额	`FLOAT` / `DOUBLE`	`DECIMAL(10,2)`	浮点数精度丢失，金额计算会出错
状态字段	`VARCHAR(20)`	`TINYINT`	枚举值用数值存储，节省空间且索引效率高
IP 地址	`VARCHAR(45)`	`VARBINARY(16)` / `VARCHAR(45)`	IPv4 可用 `INT UNSIGNED`，IPv6 用 `VARBINARY`
时间	`VARCHAR`	`DATETIME` / `TIMESTAMP`	字符串无法直接比较和计算

(4) 字符集选择

统一使用 utf8mb4，不要用 utf8（MySQL 的 utf8 最多 3 字节，存不了 emoji 和部分生僻字）。
同一个数据库、同一张表的所有字段保持统一字符集，避免字符集不一致导致索引失效。

2.3 原型验证与迭代演进

表结构设计完成后，不要直接上线，做以下验证：

小数据量验证：插入边界数据（超长字符串、特殊字符、null 值），验证约束是否生效。
数据量估算：根据业务预估日增数据量，计算 1 年、3 年后的数据规模，判断是否需要提前考虑分区或分表。
压测验证：用真实业务的 SQL 模式进行压测，观察慢查询和锁竞争。
灰度发布：表结构变更先在预发环境验证，再灰度到生产。

2.4 表结构变更的兼容性策略

上线后的表结构变更，遵循以下策略：

变更类型	风险	策略
新增字段（有默认值）	低	直接执行
新增字段（无默认值且 NOT NULL）	中	先加默认值，后续通过数据回填再改约束
删除字段	高	先标记废弃（代码不再读写），观察 1-2 个版本后再物理删除
修改字段类型	高	先新增字段 → 双写迁移数据 → 切换读新字段 → 删除旧字段
新增索引	低	在线执行（MySQL 5.6+ 支持 Online DDL）
删除索引	低	直接执行，但需确认无 SQL 依赖

核心原则：表结构变更必须是向后兼容的，永远假设旧版本代码可能还在运行。

三、核心设计原则

3.1 范式与反范式的取舍

三大范式（1NF / 2NF / 3NF）的本质目标是消除数据冗余和更新异常。但实战中，完全遵守范式会导致过多的表 JOIN，影响查询性能。

取舍原则：

OLTP 系统（增删改查为主）：建议至少满足第三范式（3NF），在确认性能瓶颈后再有目的地反范式化。
OLAP 系统（分析查询为主）：天然倾向反范式，星型模型或宽表是常见选择。
反范式化的红线：
- 不能因为"可能用到"就冗余，必须有明确的查询场景支撑。
- 冗余字段的更新路径必须明确（同步更新、异步同步、或只读快照）。

典型反范式场景：

订单表中冗余 user_name（避免用户表 JOIN，用户改名不影响历史订单展示）。
订单明细中冗余 product_name、product_image（商品下架后订单详情仍能正常展示）。

3.2 命名规范

统一的命名规范是团队协作的基础，也是后续维护的前提。

对象	规范	示例
表名	`小写 + 下划线`，名词复数或业务前缀	`t_order`、`sys_user`、`order_item`
字段名	`小写 + 下划线`	`user_name`、`create_time`
主键	`id`（数值型）	`id BIGINT`
外键	`{关联表名}_id`	`user_id`、`order_id`
普通索引	`idx_{表名}_{字段列表}`	`idx_order_user_id`
唯一索引	`uk_{表名}_{字段列表}`	`uk_user_email`
布尔字段	`is_` 前缀	`is_deleted`、`is_active`

约定：

禁止使用数据库保留字作为表名或字段名（如 order、key、group）。
布尔字段统一用 is_ 前缀，值为 0/1 而非 true/false 字符串。
同一含义的字段在不同表中保持统一命名（如创建时间统一用 create_time，不要有的表叫 created_at，有的表叫 gmt_create）。

3.3 数据类型选型

数据类型是数据库设计中最基础的决策，选错后改起来成本最高。

数值类型

类型	字节	范围	适用场景
`TINYINT`	1	-128 ~ 127（无符号 0~255）	状态、类型、年龄
`SMALLINT`	2	-32768 ~ 32767	较小的计数器
`INT`	4	-21亿 ~ 21亿	普通主键、外键
`BIGINT`	8	极大	雪花 ID、订单号
`DECIMAL(M,D)`	变长	精确小数	金额、汇率、折扣

关键建议：

金额字段一律使用 DECIMAL，不要用 FLOAT / DOUBLE。DECIMAL(10,2) 表示最多 8 位整数 + 2 位小数，覆盖 99999999.99 的范围。
状态、类型等枚举值用 TINYINT，配合注释说明含义。

字符串类型

类型	说明	适用场景
`CHAR(n)`	固定长度，不足补空格	固定长度数据（身份证号、手机号）
`VARCHAR(n)`	变长，按需存储	大部分字符串
`TEXT`	大文本（最大 64KB）	文章正文、描述信息
`LONGTEXT`	超大文本（最大 4GB）	JSON 存储、日志

关键建议：

VARCHAR(n) 的 n 是字符数不是字节数，按业务最大长度合理设定，不要全部 VARCHAR(255)。
VARCHAR 过大不会浪费存储空间（按需分配），但会影响排序和临时表的内存占用。
超过 500 字符的字段考虑拆分到扩展表，避免主表行宽过大。

时间类型

类型	说明	适用场景
`DATETIME`	与时区无关，存储绝对时间	业务时间（订单创建时间）
`TIMESTAMP`	与时区相关，自动转 UTC 存储	审计时间（记录修改时间）
`BIGINT`	存毫秒/秒级时间戳	需要跨数据库迁移的场景

关键建议：

优先用 DATETIME，可读性好且不受时区影响。
TIMESTAMP 在 MySQL 中有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 的自动维护能力，适合审计字段。
需要跨时区展示时，统一存 UTC 时间，在应用层转换。

四、通用设计模式

4.1 主键策略对比

主键是每行数据的唯一标识，也是聚簇索引的核心。主键的选择直接影响写入性能和查询效率。

策略	优点	缺点	适用场景
自增 INT/BIGINT	有序、写入性能高、节省存储空间	分布式环境下需额外处理	单机数据库
UUID	全局唯一、生成简单	无序导致页分裂、存储空间大、可读性差	分布式系统（不推荐）
雪花算法	全局唯一、趋势递增、性能高	依赖时钟同步	分布式系统（推荐）
号段模式	可控范围、可预测	需要额外的号段分配服务	需要业务可读编号

推荐：分布式系统优先使用雪花算法生成的 BIGINT 主键。有序性保证写入时页追加而非页分裂，性能最优。

4.2 审计字段设计

每张业务表都应该包含审计字段，用于追溯数据的创建和修改信息。

-- 通用审计字段模板
create_time      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
creator          VARCHAR(64)  DEFAULT '' COMMENT '创建人',
updater          VARCHAR(64)  DEFAULT '' COMMENT '更新人',
is_deleted       TINYINT      NOT NULL DEFAULT 0 COMMENT '是否删除：0-否 1-是'

约定：

create_time 和 update_time 由数据库自动维护，避免应用层遗漏。
creator 和 updater 在应用层通过拦截器或 MyBatis 插件自动填充。
如果项目需要记录操作人 ID 而非名称，可用 creator_id（BIGINT）。

4.3 软删除 vs 硬删除

方式	实现	优点	缺点
硬删除	`DELETE FROM`	释放存储空间、数据量小	无法追溯、无法恢复
软删除	`UPDATE is_deleted = 1`	可追溯、可恢复	数据量增长、唯一索引需特殊处理

软删除的注意事项：

唯一约束冲突：软删除后，"已删除"记录仍占用唯一索引，导致新增相同数据失败。解决方案：使用 (字段, is_deleted) 的联合唯一索引，或者软删除时将关键字段置为 null / 追加删除时间戳。
查询必须带 WHERE is_deleted = 0：通过 MyBatis 插件或 ORM 的全局过滤器统一处理，避免遗漏。
定期归档：软删除数据累积到一定量后，迁移到归档表。

4.4 字典表与枚举设计

系统中大量的状态、类型、分类等枚举值，有两种管理方式：

(1) 代码枚举（推荐大部分场景）

public enum OrderStatus {
    PENDING(0, "待支付"),
    PAID(1, "已支付"),
    SHIPPED(2, "已发货"),
    COMPLETED(3, "已完成"),
    CANCELLED(4, "已取消");
}

数据库存储数值（TINYINT），注释说明映射关系。

(2) 字典表（适合可配置的场景）

CREATE TABLE sys_dict_type (
    id          BIGINT       PRIMARY KEY COMMENT '主键',
    dict_code   VARCHAR(64)  NOT NULL COMMENT '字典编码',
    dict_name   VARCHAR(128) NOT NULL COMMENT '字典名称',
    UNIQUE KEY uk_dict_code (dict_code)
) COMMENT '字典类型表';

CREATE TABLE sys_dict_data (
    id          BIGINT       PRIMARY KEY COMMENT '主键',
    dict_code   VARCHAR(64)  NOT NULL COMMENT '字典编码',
    dict_label  VARCHAR(128) NOT NULL COMMENT '标签',
    dict_value  VARCHAR(64)  NOT NULL COMMENT '值',
    sort        INT          DEFAULT 0 COMMENT '排序',
    status      TINYINT      DEFAULT 1 COMMENT '状态',
    INDEX idx_dict_code (dict_code)
) COMMENT '字典数据表';

选择标准：枚举值固定不变（如订单状态流转）用代码枚举；需要运营人员动态配置的（如商品分类、活动类型）用字典表。

4.5 多对多关系表设计

多对多关系通过中间表（关联表）实现，设计规范：

CREATE TABLE order_product (
    id           BIGINT  PRIMARY KEY COMMENT '主键',
    order_id     BIGINT  NOT NULL COMMENT '订单ID',
    product_id   BIGINT  NOT NULL COMMENT '商品ID',
    quantity     INT     NOT NULL DEFAULT 1 COMMENT '数量',
    unit_price   DECIMAL(10,2) NOT NULL COMMENT '下单时单价',
    UNIQUE KEY uk_order_product (order_id, product_id),
    INDEX idx_product_id (product_id)
) COMMENT '订单商品关联表';

关键点：

中间表必须有自己独立的主键（便于扩展和维护）。
两个外键建联合唯一索引，防止重复关联。
关系本身的属性（如 quantity、unit_price）存在中间表中。

4.6 大表设计阈值

实战阈值建议： "单表字段不超过 26 个，索引不超过 6 个" ，此要求并非数据库的硬性限制，而是实战经验值，其背后的原理是：

字段数建议 ≤ 20~30 个：

InnoDB 的行格式（Row Format）中，当行数据过大时会被拆分到溢出页（Overflow Page），导致额外的 IO 开销。
字段过多的表通常违反了单一职责原则，应该考虑垂直拆分：
- 常用字段放在主表（高频查询的轻量字段）。
- 大字段（TEXT、LONGTEXT）或低频字段拆到扩展表（_ext 后缀）。

索引数建议 ≤ 5~6 个：

每个索引都是一棵独立的 B+Tree，写入时需要同时更新所有索引树。
6 个索引意味着每次 INSERT / UPDATE 要维护 7 棵树（1 棵聚簇索引 + 6 棵二级索引），写入放大明显。
索引的取舍原则：没有查询场景支撑的索引一律不建。

五、索引设计

5.1 索引类型与选型

索引类型	说明	适用场景
聚簇索引	数据本身存储在叶子节点，每张表只有一个	InnoDB 默认按主键建立
普通索引	叶子节点存储主键值（回表查询）	常规查询条件
唯一索引	约束字段值唯一	业务唯一标识（手机号、邮箱）
联合索引	多列组合索引	多字段组合查询
前缀索引	只索引字符串前 N 个字符	长字符串（URL、长文本摘要）

选择建议：

查询条件中等值查询的字段优先考虑建索引。
区分度高的字段（如手机号、订单号）适合建索引，区分度低的字段（如性别、状态只有 2-3 个值）索引效果差。

5.2 联合索引的最左前缀原则

联合索引 (a, b, c) 等价于同时创建了三个索引：(a)、(a, b)、(a, b, c)。

查询能否命中索引，取决于是否匹配最左前缀：

查询条件	是否命中索引	说明
`WHERE a = ?`	✅	命中 `(a)`
`WHERE a = ? AND b = ?`	✅	命中 `(a, b)`
`WHERE a = ? AND b = ? AND c = ?`	✅	命中 `(a, b, c)`
`WHERE b = ?`	❌	缺少最左列 `a`
`WHERE a = ? AND c = ?`	部分	只有 `a` 命中，`c` 无法利用索引

设计建议：

等值查询的字段放前面，范围查询的字段放后面。
区分度高的列放前面，过滤效果更好。

5.3 设计层面的索引失效场景

以下场景与表结构设计直接相关，必须在设计阶段规避：

(1) 隐式类型转换

当字段类型与查询值类型不匹配时，索引失效：

-- phone 字段是 VARCHAR 类型
SELECT * FROM user WHERE phone = 13800138000;  -- 数值型比较，触发隐式转换，索引失效
SELECT * FROM user WHERE phone = '13800138000'; -- 字符串比较，正常走索引

规避：应用层参数类型必须与数据库字段类型严格一致。MyBatis 的 #{param} 会自动处理类型，但 ${param} 是直接拼接，容易出问题。

(2) 字符集不一致

当 JOIN 的两个字段字符集不同，或者字段字符集与连接字符集不同，索引失效：

-- user.phone 是 utf8mb4，order.contact_phone 是 utf8
SELECT * FROM user u JOIN order o ON u.phone = o.contact_phone;
-- 字符集不一致导致隐式转换，索引失效

规避：全库、全表、全字段统一字符集（推荐 utf8mb4）。

(3) 函数运算

对索引列使用函数或计算，索引失效：

SELECT * FROM user WHERE YEAR(create_time) = 2024;  -- 函数作用于索引列，失效
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';  -- 正常

规避：避免在 WHERE 条件中对字段使用函数，改用范围查询。

(4) LIKE 前缀通配符

SELECT * FROM user WHERE name LIKE '%张%';   -- 前缀通配，索引失效
SELECT * FROM user WHERE name LIKE '张%';    -- 前缀匹配，走索引

规避：避免前缀通配查询，需要模糊搜索时考虑全文索引或搜索引擎（Elasticsearch）。

5.4 索引设计检查要点

每个索引都有明确的查询场景支撑。
联合索引的列顺序遵循最左前缀原则。
索引列不参与函数运算和隐式类型转换。
唯一索引覆盖业务唯一性约束。
定期清理无用索引（可通过慢查询日志或 performance_schema 观察索引使用情况）。

六、电商场景实战

6.1 业务需求梳理

以一个简化版的电商系统为例，核心业务：

用户管理：注册、登录、个人信息维护
商品管理：商品分类、商品信息、库存管理
购物车：用户添加/删除购物车商品
订单管理：下单、支付、发货、确认收货、评价
地址管理：用户收货地址管理

6.2 ER 图设计

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│   t_user    │       │ t_category  │       │ t_address   │
├─────────────┤       ├─────────────┤       ├─────────────┤
│ id          │       │ id          │       │ id          │
│ username    │       │ parent_id   │       │ user_id     │
│ phone       │       │ name        │       │ receiver    │
│ email       │       │ sort        │       │ phone       │
│ password    │       └──────┬──────┘       │ address     │
│ status      │              │              │ is_default  │
│ ...         │              │              └─────────────┘
└──────┬──────┘              │               (1:N)
       │                     │
       │ 1:N                 │ 1:N
       │                     │
       ▼                     ▼
┌─────────────┐       ┌─────────────┐
│ t_order     │◄─────►│ t_product   │
├─────────────┤       ├─────────────┤
│ id          │       │ id          │
│ user_id     │       │ category_id │
│ order_no    │       │ name        │
│ total_amount│       │ price       │
│ status      │       │ stock       │
│ address_id  │       │ status      │
│ ...         │       │ ...         │
└──────┬──────┘       └─────────────┘
       │
       │ 1:N
       ▼
┌─────────────────┐
│ t_order_item    │
├─────────────────┤
│ id              │
│ order_id        │
│ product_id      │
│ product_name    │  ← 冗余，避免 JOIN
│ quantity        │
│ unit_price      │  ← 快照，记录下单时价格
│ subtotal        │
└─────────────────┘

6.3 核心表 DDL 落地

用户表

CREATE TABLE t_user (
    id              BIGINT          PRIMARY KEY COMMENT '主键',
    username        VARCHAR(64)     NOT NULL COMMENT '用户名',
    phone           VARCHAR(11)     DEFAULT '' COMMENT '手机号',
    email           VARCHAR(128)    DEFAULT '' COMMENT '邮箱',
    password        VARCHAR(256)    NOT NULL COMMENT '密码（加密存储）',
    nickname        VARCHAR(64)     DEFAULT '' COMMENT '昵称',
    avatar          VARCHAR(256)    DEFAULT '' COMMENT '头像URL',
    status          TINYINT         NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
    create_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    creator         VARCHAR(64)     DEFAULT '' COMMENT '创建人',
    updater         VARCHAR(64)     DEFAULT '' COMMENT '更新人',
    is_deleted      TINYINT         NOT NULL DEFAULT 0 COMMENT '是否删除：0-否 1-是',
    UNIQUE KEY uk_phone (phone),
    UNIQUE KEY uk_email (email),
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

商品分类表

CREATE TABLE t_category (
    id              BIGINT          PRIMARY KEY COMMENT '主键',
    parent_id       BIGINT          NOT NULL DEFAULT 0 COMMENT '父分类ID，0表示顶级分类',
    name            VARCHAR(64)     NOT NULL COMMENT '分类名称',
    icon            VARCHAR(256)    DEFAULT '' COMMENT '图标URL',
    sort            INT             NOT NULL DEFAULT 0 COMMENT '排序号',
    status          TINYINT         NOT NULL DEFAULT 1 COMMENT '状态：0-禁用 1-启用',
    create_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted      TINYINT         NOT NULL DEFAULT 0 COMMENT '是否删除：0-否 1-是',
    INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品分类表';

商品表

CREATE TABLE t_product (
    id              BIGINT          PRIMARY KEY COMMENT '主键',
    category_id     BIGINT          NOT NULL COMMENT '分类ID',
    name            VARCHAR(256)    NOT NULL COMMENT '商品名称',
    subtitle        VARCHAR(512)    DEFAULT '' COMMENT '副标题',
    main_image      VARCHAR(256)    DEFAULT '' COMMENT '主图URL',
    images          JSON            DEFAULT NULL COMMENT '商品图集',
    detail          TEXT            DEFAULT NULL COMMENT '商品详情',
    price           DECIMAL(10,2)   NOT NULL COMMENT '售价',
    original_price  DECIMAL(10,2)   DEFAULT NULL COMMENT '原价',
    stock           INT             NOT NULL DEFAULT 0 COMMENT '库存',
    sales           INT             NOT NULL DEFAULT 0 COMMENT '销量',
    status          TINYINT         NOT NULL DEFAULT 1 COMMENT '状态：0-下架 1-上架',
    create_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted      TINYINT         NOT NULL DEFAULT 0 COMMENT '是否删除：0-否 1-是',
    INDEX idx_category_id (category_id),
    INDEX idx_name (name(64))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品表';

订单表

CREATE TABLE t_order (
    id              BIGINT          PRIMARY KEY COMMENT '主键',
    user_id         BIGINT          NOT NULL COMMENT '用户ID',
    order_no        VARCHAR(32)     NOT NULL COMMENT '订单编号',
    total_amount    DECIMAL(12,2)   NOT NULL COMMENT '订单总金额',
    pay_amount      DECIMAL(12,2)   DEFAULT 0.00 COMMENT '实付金额',
    status          TINYINT         NOT NULL DEFAULT 0 COMMENT '状态：0-待支付 1-已支付 2-已发货 3-已完成 4-已取消 5-已退款',
    receiver_name   VARCHAR(64)     NOT NULL COMMENT '收货人',
    receiver_phone  VARCHAR(11)     NOT NULL COMMENT '收货电话',
    receiver_address VARCHAR(512)   NOT NULL COMMENT '收货地址',
    remark          VARCHAR(512)    DEFAULT '' COMMENT '订单备注',
    pay_time        DATETIME        DEFAULT NULL COMMENT '支付时间',
    ship_time       DATETIME        DEFAULT NULL COMMENT '发货时间',
    finish_time     DATETIME        DEFAULT NULL COMMENT '完成时间',
    create_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted      TINYINT         NOT NULL DEFAULT 0 COMMENT '是否删除：0-否 1-是',
    UNIQUE KEY uk_order_no (order_no),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

订单明细表

CREATE TABLE t_order_item (
    id              BIGINT          PRIMARY KEY COMMENT '主键',
    order_id        BIGINT          NOT NULL COMMENT '订单ID',
    product_id      BIGINT          NOT NULL COMMENT '商品ID',
    product_name    VARCHAR(256)    NOT NULL COMMENT '商品名称（冗余，避免JOIN）',
    product_image   VARCHAR(256)    DEFAULT '' COMMENT '商品主图（冗余）',
    unit_price      DECIMAL(10,2)   NOT NULL COMMENT '下单时单价（价格快照）',
    quantity        INT             NOT NULL DEFAULT 1 COMMENT '购买数量',
    subtotal        DECIMAL(12,2)   NOT NULL COMMENT '小计金额',
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单明细表';

6.4 设计决策说明

(1) 为什么 t_order 中有 receiver_* 字段，不从 t_address 关联查询？

这是快照设计的典型场景。订单创建时的收货地址是当时的状态，后续用户修改地址不应影响历史订单展示。因此下单时将地址信息冗余到订单表中，确保订单信息的不可变性。

(2) 为什么 t_order_item 中冗余 product_name 和 product_image？

商品可能下架、改名、换图，但历史订单的展示必须保持下单时的状态。这种只读快照冗余避免了 JOIN 查询，且不会引发一致性问题（历史数据不需要回写）。

(3) 为什么 order_no 用 VARCHAR(32) 而不是直接用主键 id？

主键 id 是技术标识，order_no 是业务编号。业务编号有规则要求（如日期 + 序列号 202401011234567890），且可能暴露给用户和外部系统，与技术主键解耦更安全。

(4) 为什么 t_order 有 idx_status 索引？

订单列表查询通常按用户 ID + 状态过滤：WHERE user_id = ? AND status = ?。单列 idx_status 可以配合 idx_user_id 进行索引合并（Index Merge），或者后续根据实际慢查询情况调整为联合索引 idx_user_status (user_id, status)。

七、常见设计陷阱

7.1 EAV 模式（Entity-Attribute-Value）

-- ❌ 反模式：用 EAV 存储动态属性
CREATE TABLE entity_attr (
    entity_id   BIGINT,
    attr_name   VARCHAR(64),
    attr_value  VARCHAR(256)
);

EAV 用行存储列数据，看似灵活实则灾难：

查询某个实体的所有属性需要多行转多列，SQL 复杂。
无法在 attr_value 上建有意义的索引。
数据类型全部变成字符串，丢失类型安全。

正确做法：MySQL 5.7+ 支持原生 JSON 类型，动态属性用 JSON 列存储，配合生成的虚拟列建索引。

7.2 万能表（God Table）

把所有业务的字段堆在一张表里，试图"一张表搞定一切"：

-- ❌ 反模式：用户表包含了地址、偏好、统计等所有字段
CREATE TABLE t_user (
    id, username, ...,
    province, city, address,  -- 地址信息
    favorite_color, ...,      -- 偏好信息
    login_count, order_count  -- 统计信息
);

万能表违反单一职责原则，字段膨胀后导致：

行宽过大，InnoDB 溢出页。
修改任意字段都锁住整行所有字段。
无法针对不同字段设置不同的更新频率。

正确做法：垂直拆分，将不同职责的字段拆到独立表中（t_user_address、t_user_preference、t_user_stat）。

7.3 过度反范式化

-- ❌ 反模式：在用户表中冗余订单金额总和、最新订单时间等
CREATE TABLE t_user (
    ...,
    total_order_amount DECIMAL(12,2),  -- 累计消费
    last_order_time    DATETIME,       -- 最近下单
    order_count        INT             -- 订单总数
);

这些聚合字段的每次订单变更都需要回写用户表，在高并发场景下成为写热点。

正确做法：

读场景需要时通过查询实时计算（加索引优化）。
确实需要缓存的聚合数据，放到 Redis 中维护。
或通过异步任务 / 消息队列定期更新。

7.4 过早分库分表

系统刚上线、数据量不到百万，就引入分库分表中间件，带来：

跨库 JOIN 需要应用层处理。
分布式事务复杂度急剧上升。
分页、排序、统计查询变得极其困难。

正确做法：

单表 500 万行以下不考虑分表，优先通过索引优化和读写扩展。
单表 500 万 ~ 2000 万行，考虑分区表或垂直拆分。
超过 2000 万行且有持续增长趋势，再评估水平拆分（分库分表）。
设计时预留分片键（如 user_id），后续拆分时改造成本低。

7.5 忽视外键约束

-- ❌ 反模式：没有外键约束，存在孤儿数据
-- order 表中的 user_id 指向不存在的用户

业界共识：生产环境不建议使用数据库外键约束（影响写入性能、分布式场景下无法跨库）。但必须在应用层实现同等约束逻辑，确保数据一致性。

正确做法：

不在 DDL 中定义 FOREIGN KEY。
在 Service 层通过业务逻辑保证关联数据的完整性。
定期运行数据一致性检查脚本。

八、数据库设计检查表（Checklist）

以下检查表可直接用于设计评审和 Code Review。

8.1 表结构检查

每张表都有明确的主键（推荐数值型 BIGINT）
主键策略符合系统架构（单机用自增，分布式用雪花算法）
表名遵循命名规范（t_ 前缀或业务前缀，小写下划线）
字段名遵循命名规范（小写下划线，同一含义字段名统一）
没有使用数据库保留字
字符集统一为 utf8mb4
每张表包含审计字段（create_time、update_time、is_deleted 等）
字段数量建议 ≤ 25 个，超过则评估垂直拆分
大字段（TEXT、LONGTEXT）是否考虑拆分到扩展表

8.2 字段类型检查

金额字段使用 DECIMAL（非 FLOAT / DOUBLE）
状态、类型等枚举字段使用 TINYINT
时间字段使用 DATETIME 或 TIMESTAMP（非 VARCHAR）
手机号、身份证号等固定长度字段使用 CHAR
VARCHAR 长度按业务最大值设定（非一律 255）
布尔字段使用 is_ 前缀，值为 0/1
应用层参数类型与数据库字段类型严格一致

8.3 索引检查

每个索引都有明确的查询场景支撑
索引数量建议 ≤ 6 个（含主键）
联合索引列顺序遵循最左前缀原则
等值查询列在前，范围查询列在后
区分度高的列优先放在联合索引前面
唯一索引覆盖业务唯一性约束
没有对区分度低的字段建索引（如性别）
索引列不会被函数运算或隐式类型转换影响

8.4 业务逻辑检查

多对多关系通过中间表实现
软删除处理了唯一索引冲突问题
冗余字段有明确的更新路径和一致性保障
快照字段（如订单明细中的价格）有业务合理性
字典值有统一的枚举管理方案
没有使用 EAV 模式存储动态属性

8.5 扩展性检查

单表预估 1 年数据量，是否需要提前考虑分表
预留了分片键（如 user_id），后续拆分成本低
表结构变更有兼容性策略（向后兼容）
大表的 DDL 变更有在线执行方案（Online DDL）

8.6 安全性检查

密码字段加密存储（非明文）
敏感字段（身份证号、银行卡号）加密或脱敏存储
没有 SELECT * 的查询依赖
SQL 使用参数化查询（防 SQL 注入）

结语

数据库设计是一门"前期多花一小时，后期节省十小时"的功夫。好的设计不是追求理论上的完美范式，而是在数据一致性、查询性能、扩展灵活性之间找到当前业务阶段的最优平衡。

本文的电商场景案例可以直接参考，但更重要的是理解每个设计决策背后的"为什么"。当你的业务场景与案例不同时，用同样的分析思路——从需求出发，识别实体和关系，评估范式与反范式的取舍，设计合理的索引——就能得出适合自己场景的方案。

posted @ 2026-05-24 20:06 flycloudy 阅读(21) 评论(0) 收藏举报

刷新页面返回顶部

Continued

关系型数据库设计实战指南

一、为什么数据库设计值得深究

二、设计流程

2.1 需求分析 → 实体识别 → 关系梳理

2.2 概念模型 → 逻辑模型 → 物理模型

逻辑模型转物理模型的关键决策

2.3 原型验证与迭代演进

2.4 表结构变更的兼容性策略

三、核心设计原则

3.1 范式与反范式的取舍

3.2 命名规范

3.3 数据类型选型

数值类型

字符串类型

时间类型

四、通用设计模式

4.1 主键策略对比

4.2 审计字段设计

4.3 软删除 vs 硬删除

4.4 字典表与枚举设计

4.5 多对多关系表设计

4.6 大表设计阈值

五、索引设计

5.1 索引类型与选型

5.2 联合索引的最左前缀原则

5.3 设计层面的索引失效场景

5.4 索引设计检查要点

六、电商场景实战

6.1 业务需求梳理

6.2 ER 图设计

6.3 核心表 DDL 落地

用户表

商品分类表

商品表

订单表

订单明细表

6.4 设计决策说明

七、常见设计陷阱

7.1 EAV 模式（Entity-Attribute-Value）

7.2 万能表（God Table）

7.3 过度反范式化

7.4 过早分库分表

7.5 忽视外键约束

八、数据库设计检查表（Checklist）

8.1 表结构检查

8.2 字段类型检查

8.3 索引检查

8.4 业务逻辑检查

8.5 扩展性检查

8.6 安全性检查

结语

公告