数据库设计
数据库设计
🎯 生活类比
想象你要开一家图书馆：
- 需求分析：先搞清楚要借什么书、谁来借、怎么登记 → 了解业务需求
- ER图：画出"读者—借阅—书籍"的关系图 → 实体关系建模
- 规范化：不要在借书卡上重复抄写读者地址，地址只存一份在读者档案里 → 消除冗余
- 反规范化：热门书排行榜直接算好贴在墙上，不用每次都翻借阅记录 → 用空间换时间
- 命名规范：书架编号统一用"A-01"格式，不一会儿叫"架子1"一会儿叫"第一排" → 统一命名
📚 核心概念
1. 需求分析
在写任何 SQL 之前，先回答这些问题：
| 问题 | 示例 |
|---|---|
| 系统要管理哪些实体？ | 用户、文章、评论、标签 |
| 实体之间是什么关系？ | 一个用户写多篇文章（一对多） |
| 需要支持哪些查询？ | 按标签搜索、按时间排序 |
| 数据量预估？ | 百万级文章、千万级评论 |
| 读多写少还是写多读少？ | 博客系统读多写少 |
2. ER 图（实体关系图）
ER 图用三种核心元素描述数据模型：
[实体] —— 属性1、属性2、属性3
|
(关系) cardinality: 1:1, 1:N, M:N
|
[实体] —— 属性1、属性2
常见关系类型：
| 关系 | 示例 | 实现方式 |
|---|---|---|
| 一对一 (1:1) | 用户 ↔ 用户详情 | 外键或合并为一张表 |
| 一对多 (1:N) | 用户 → 文章 | 文章表中加 user_id 外键 |
| 多对多 (M:N) | 文章 ↔ 标签 | 中间表 article_tag |
3. 规范化理论
第一范式（1NF）：列不可再分
SQL
-- ❌ 违反 1NF：电话号码列存了多个值
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
phones VARCHAR(200) -- '138xxx,139xxx,137xxx'
);
-- ✅ 符合 1NF：每个字段只存一个原子值
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE user_phones (
id INT PRIMARY KEY,
user_id INT,
phone VARCHAR(20)
);
第二范式（2NF）：非主键列完全依赖于整个主键
SQL
-- ❌ 违反 2NF：在订单明细中，product_name 只依赖 product_id，不依赖 order_id
CREATE TABLE order_items (
order_id INT,
product_id INT,
product_name VARCHAR(100), -- 部分依赖
quantity INT,
PRIMARY KEY (order_id, product_id)
);
-- ✅ 符合 2NF：拆分出商品表
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
第三范式（3NF）：非主键列不能传递依赖
SQL
-- ❌ 违反 3NF：city_name 依赖 city_id，city_id 依赖 id → 传递依赖
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
city_id INT,
city_name VARCHAR(50) -- 传递依赖
);
-- ✅ 符合 3NF：拆分出城市表
CREATE TABLE cities (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
city_id INT
);
BCNF（Boyce-Codd 范式）
比 3NF 更严格：每个决定因素都必须是候选键。实际开发中，满足 3NF 通常已足够。
4. 反规范化
有时为了查询性能，故意引入冗余：
SQL
-- 在文章表中冗余存储评论数，避免每次 COUNT
ALTER TABLE articles ADD COLUMN comment_count INT DEFAULT 0;
-- 更新冗余字段
UPDATE articles SET comment_count = (
SELECT COUNT(*) FROM comments WHERE comments.article_id = articles.id
);
何时反规范化：
- 读远多于写
- 聚合查询频率极高
- 已有索引优化仍不满足性能要求
5. 表关系设计
主键选择：
| 类型 | 优点 | 缺点 | 适用场景 |
|---|---|---|---|
自增整数 AUTO_INCREMENT |
有序、紧凑、插入快 | 可预测、分库分表麻烦 | 大多数业务表 |
| UUID | 全局唯一、不可预测 | 占空间大、无序导致索引慢 | 分布式系统 |
| 复合主键 | 语义明确 | 外键引用麻烦 | 中间表、关联表 |
| 雪花算法 ID | 有序、全局唯一 | 需要额外依赖 | 分布式高并发 |
SQL
-- 自增主键
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
-- UUID 主键
CREATE TABLE users (
id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
name VARCHAR(50)
);
-- 复合主键（中间表）
CREATE TABLE article_tag (
article_id BIGINT,
tag_id BIGINT,
PRIMARY KEY (article_id, tag_id)
);
6. 字段命名规范
| 规范 | ✅ 推荐 | ❌ 避免 |
|---|---|---|
| 使用小写+下划线 | user_name |
UserName、username |
| 表名用复数 | users |
user、tbl_user |
| 外键字段名 | user_id |
uid、userId |
| 布尔字段 | is_deleted |
deleted、flag |
| 时间字段 | created_at |
createTime、add_time |
| 金额字段 | DECIMAL(10,2) |
FLOAT、DOUBLE |
7. 设计最佳实践
- 每张表必须有主键
- 外键列加索引（即使数据库不强制外键约束）
- 选择最小够用的数据类型：
TINYINT代替INT存状态值 - 金额用
DECIMAL，不用浮点数 - 时间用
DATETIME或TIMESTAMP，不用字符串 - 预留扩展字段：
extra JSON或status位运算 - 软删除代替物理删除：
is_deleted TINYINT DEFAULT 0
💡 基本语法
SQL
-- 创建表的基本模板
CREATE TABLE table_name (
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
-- 业务字段
name VARCHAR(100) NOT NULL COMMENT '名称',
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 1-正常 0-禁用',
-- 外键字段
user_id BIGINT NOT NULL COMMENT '用户ID',
-- 时间字段
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
-- 索引
INDEX idx_user_id (user_id),
INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表注释';
💡 提示：设计阶段多花 1 小时，能节省后期 10 小时的重构时间。先用纸笔画 ER 图，再写 SQL。
示例：设计一个简单的学生选课系统（难度⭐）
需求：管理学生、课程和选课记录。
SQL
-- 学生表
CREATE TABLE students (
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '学生ID',
student_no VARCHAR(20) NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(50) NOT NULL COMMENT '姓名',
gender TINYINT NOT NULL DEFAULT 0 COMMENT '性别: 0-未知 1-男 2-女',
enrollment_year INT NOT NULL COMMENT '入学年份',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';
-- 课程表
CREATE TABLE courses (
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '课程ID',
course_no VARCHAR(20) NOT NULL UNIQUE COMMENT '课程编号',
name VARCHAR(100) NOT NULL COMMENT '课程名称',
credit DECIMAL(3,1) NOT NULL COMMENT '学分',
teacher VARCHAR(50) COMMENT '授课教师',
max_students INT NOT NULL DEFAULT 60 COMMENT '最大选课人数',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';
-- 选课表（中间表，多对多关系）
CREATE TABLE enrollments (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
student_id BIGINT NOT NULL COMMENT '学生ID',
course_id BIGINT NOT NULL COMMENT '课程ID',
score DECIMAL(5,2) COMMENT '成绩',
enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间',
UNIQUE KEY uk_student_course (student_id, course_id),
INDEX idx_course_id (course_id),
INDEX idx_student_id (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='选课表';
示例：设计电商订单系统核心表（难度⭐⭐）
SQL
-- 商品分类表
CREATE TABLE categories (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL COMMENT '分类名称',
parent_id BIGINT DEFAULT NULL COMMENT '父分类ID，NULL表示顶级分类',
sort_order INT NOT NULL DEFAULT 0 COMMENT '排序',
INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';
-- 商品表
CREATE TABLE products (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
category_id BIGINT NOT NULL COMMENT '分类ID',
name VARCHAR(200) NOT NULL COMMENT '商品名称',
price DECIMAL(10,2) NOT NULL COMMENT '售价',
stock INT NOT NULL DEFAULT 0 COMMENT '库存',
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 1-上架 0-下架',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_category (category_id),
INDEX idx_status_price (status, price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';
-- 订单表（反规范化：冗余存储收货地址快照）
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
user_id BIGINT NOT NULL COMMENT '用户ID',
total_amount DECIMAL(12,2) NOT NULL COMMENT '订单总金额',
status TINYINT NOT NULL DEFAULT 0 COMMENT '状态: 0-待付款 1-已付款 2-已发货 3-已完成 4-已取消',
receiver_name VARCHAR(50) NOT NULL COMMENT '收货人姓名',
receiver_phone VARCHAR(20) NOT NULL COMMENT '收货人电话',
receiver_address VARCHAR(500) NOT NULL COMMENT '收货地址',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
paid_at DATETIME COMMENT '付款时间',
INDEX idx_user_id (user_id),
INDEX idx_status (status),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
-- 订单明细表
CREATE TABLE order_items (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT NOT NULL COMMENT '订单ID',
product_id BIGINT NOT NULL COMMENT '商品ID',
product_name VARCHAR(200) NOT NULL COMMENT '商品名称（快照）',
product_price DECIMAL(10,2) NOT NULL COMMENT '商品单价（快照）',
quantity INT NOT NULL 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 COMMENT='订单明细表';
设计要点：
- 订单表冗余了收货地址（下单时快照），即使用户后来修改地址也不影响历史订单
- 订单明细表冗余了商品名称和价格，即使商品改价也不影响已下单记录
- 金额字段统一用
DECIMAL(12,2) - 状态字段用
TINYINT+ 注释说明各值含义
🏢 场景一：设计博客系统数据库
需求：支持用户注册、写文章、打标签、发评论。
SQL
-- 用户表
CREATE TABLE blog_users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
avatar_url VARCHAR(500),
bio TEXT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='博客用户表';
-- 文章表
CREATE TABLE blog_articles (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
category_id BIGINT,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
status TINYINT NOT NULL DEFAULT 0 COMMENT '0-草稿 1-已发布 2-已下架',
view_count INT NOT NULL DEFAULT 0,
like_count INT NOT NULL DEFAULT 0,
published_at DATETIME,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_category (category_id),
INDEX idx_status_published (status, published_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';
-- 标签表
CREATE TABLE blog_tags (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签表';
-- 文章-标签中间表
CREATE TABLE blog_article_tag (
article_id BIGINT NOT NULL,
tag_id BIGINT NOT NULL,
PRIMARY KEY (article_id, tag_id),
INDEX idx_tag_id (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章标签关联表';
-- 评论表（支持嵌套评论）
CREATE TABLE blog_comments (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
article_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
parent_id BIGINT COMMENT '父评论ID，NULL表示顶级评论',
content TEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_article_id (article_id),
INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论表';
🏢 场景二：多租户 SaaS 系统设计
需求：一套系统服务多个企业客户，数据相互隔离。
SQL
-- 租户表
CREATE TABLE tenants (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL COMMENT '企业名称',
plan VARCHAR(20) NOT NULL DEFAULT 'free' COMMENT '套餐: free/basic/pro',
max_users INT NOT NULL DEFAULT 10,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='租户表';
-- 用户表（每条记录属于一个租户）
CREATE TABLE tenant_users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL COMMENT '租户ID',
username VARCHAR(50) NOT NULL,
role VARCHAR(20) NOT NULL DEFAULT 'member',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_tenant_username (tenant_id, username),
INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='租户用户表';
-- 业务表都带 tenant_id 字段
CREATE TABLE tenant_orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
tenant_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_tenant_id (tenant_id),
INDEX idx_tenant_user (tenant_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='租户订单表';
💡 关键：每条 SQL 查询都必须带
WHERE tenant_id = ? 条件，避免数据泄露。
❓ 常见问题
Q：什么时候该拆表，什么时候该留一张大表？ A： 当某些字段更新频率远高于其他字段时拆表（如用户基本信息 vs 登录日志）；当字段间关联查询非常频繁时保持一张表。
Q：软删除和物理删除怎么选？ A： 涊及财务、订单等核心数据用软删除（
is_deleted）；日志、缓存等辅助数据可物理删除。
Q：JSON 字段适合什么场景？ A： 存储结构不固定、查询频率低的扩展属性，如用户偏好设置、商品规格参数。不要把需要索引查询的字段放进 JSON。
Q：自增 ID 和 UUID 哪个更好？ A： 单机系统优先自增 ID（有序、紧凑）；分布式系统考虑 UUID 或雪花算法（全局唯一）。
📖 小节
本课系统讲解了数据库设计的完整方法论：
- 需求分析是设计的起点，明确实体、关系和查询模式
- ER 图帮助可视化实体间的一对一、一对多、多对多关系
- 规范化（1NF→2NF→3NF→BCNF）逐步消除数据冗余
- 反规范化在性能瓶颈处适度引入冗余
- 主键选择需权衡自增、UUID、复合主键的优缺点
- 命名规范保证团队协作的一致性
📝 作业
- 为一个在线考试系统设计数据库，包含：用户、试卷、题目、选项、答题记录、成绩。画出 ER 图并写出建表语句。
- 对以下表进行规范化：
orders(order_id, customer_name, customer_phone, product_name, product_price, quantity)，拆分为至少 3NF。 - 为你的项目选择合适的主键策略（自增 vs UUID），并说明理由。
