数据库设计基础：从零开始学习关系型数据库设计¶
概述¶
数据库设计是后端开发的核心技能之一。无论是Web应用、移动应用还是物联网系统，都需要通过数据库来存储和管理数据。良好的数据库设计能够提高系统性能、保证数据完整性、简化开发维护。
完成本文学习后，你将能够：
- 理解关系型数据库的基本概念和设计原则
- 掌握表结构设计和字段类型选择
- 理解主键、外键和索引的作用
- 学会编写基本的SQL查询语句
- 了解数据库性能优化的基本方法
- 设计符合规范的数据库结构
背景知识¶
什么是数据库？¶
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。它是一个长期存储在计算机内、有组织的、可共享的数据集合。
数据库的特点： - 持久化存储：数据保存在磁盘上，程序关闭后数据不会丢失 - 结构化管理：数据按照特定的结构组织 - 高效访问：通过索引等机制快速查询数据 - 并发控制：支持多个用户同时访问 - 数据完整性：通过约束保证数据的正确性
关系型数据库 vs 非关系型数据库¶
| 特性 | 关系型数据库 | 非关系型数据库 |
|---|---|---|
| 数据模型 | 表格（行和列） | 键值对、文档、图等 |
| 数据结构 | 固定的表结构 | 灵活的数据结构 |
| 查询语言 | SQL | 各自的查询API |
| 事务支持 | 强事务支持（ACID） | 部分支持或不支持 |
| 扩展性 | 垂直扩展为主 | 水平扩展容易 |
| 适用场景 | 结构化数据、复杂查询 | 大数据、高并发、灵活数据 |
| 典型产品 | MySQL、PostgreSQL、Oracle | MongoDB、Redis、Cassandra |
本文主要讲解关系型数据库的设计。
常见的关系型数据库¶
MySQL： - 最流行的开源数据库 - 性能好，易于使用 - 适合Web应用和中小型系统
PostgreSQL： - 功能强大的开源数据库 - 支持复杂查询和高级特性 - 适合企业级应用
SQLite： - 轻量级嵌入式数据库 - 无需服务器，直接读写文件 - 适合移动应用和嵌入式系统
Oracle： - 企业级商业数据库 - 功能最全面，性能最强 - 适合大型企业应用
SQL Server： - 微软的商业数据库 - 与Windows生态集成好 - 适合.NET应用
核心概念¶
数据库的基本组成¶
1. 数据库（Database）¶
数据库是表、视图、存储过程等对象的集合。一个数据库系统可以包含多个数据库。
2. 表（Table）¶
表是数据库中存储数据的基本单位，由行和列组成。
表的组成： - 列（Column）：也称为字段（Field），定义数据的类型和属性 - 行（Row）：也称为记录（Record），表示一条完整的数据
示例：用户表（users）
| id | username | created_at | |
|---|---|---|---|
| 1 | zhangsan | zhang@example.com | 2024-01-01 |
| 2 | lisi | li@example.com | 2024-01-02 |
| 3 | wangwu | wang@example.com | 2024-01-03 |
3. 字段（Field）¶
字段定义了数据的类型、长度和约束。
常用数据类型：
数值类型：
- INT：整数，范围 -2147483648 到 2147483647
- BIGINT：大整数，范围更大
- DECIMAL(M,D)：精确小数，M是总位数，D是小数位数
- FLOAT：单精度浮点数
- DOUBLE：双精度浮点数
字符串类型：
- CHAR(N)：固定长度字符串，最大255字符
- VARCHAR(N)：可变长度字符串，最大65535字符
- TEXT：长文本，最大65535字符
- LONGTEXT：超长文本，最大4GB
日期时间类型：
- DATE：日期，格式 YYYY-MM-DD
- TIME：时间，格式 HH:MM:SS
- DATETIME：日期时间，格式 YYYY-MM-DD HH:MM:SS
- TIMESTAMP：时间戳，自动更新
其他类型：
- BOOLEAN：布尔值，实际存储为 TINYINT(1)
- ENUM：枚举类型，从预定义的值中选择
- JSON：JSON格式数据（MySQL 5.7+）
4. 约束（Constraint）¶
约束用于限制表中数据的规则，保证数据的完整性。
主键约束（PRIMARY KEY）： - 唯一标识表中的每一行 - 不能为NULL - 一个表只能有一个主键
唯一约束（UNIQUE）： - 保证字段值唯一 - 可以为NULL - 一个表可以有多个唯一约束
非空约束（NOT NULL）： - 字段值不能为NULL
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
);
默认值约束（DEFAULT）： - 为字段设置默认值
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
检查约束（CHECK）： - 限制字段值的范围
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2) CHECK (price > 0),
stock INT CHECK (stock >= 0)
);
外键约束（FOREIGN KEY）： - 建立表之间的关联关系 - 保证引用完整性
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
数据库设计原则¶
三大范式¶
数据库设计遵循范式（Normal Form）可以减少数据冗余，提高数据一致性。
第一范式（1NF）：原子性¶
每个字段都是不可再分的原子值。
❌ 违反第一范式：
用户表
| id | name | contact |
|----|------|---------|
| 1 | 张三 | 电话:13800138000,邮箱:zhang@example.com |
✅ 符合第一范式：
用户表
| id | name | phone | email |
|----|------|-------|-------|
| 1 | 张三 | 13800138000 | zhang@example.com |
第二范式（2NF）：完全依赖¶
在满足1NF的基础上，非主键字段完全依赖于主键，不能只依赖主键的一部分。
❌ 违反第二范式：
订单明细表（主键：订单ID + 商品ID）
| 订单ID | 商品ID | 商品名称 | 数量 | 单价 |
|--------|--------|----------|------|------|
| 1 | 101 | 键盘 | 2 | 100 |
✅ 符合第二范式：
订单明细表
| 订单ID | 商品ID | 数量 |
|--------|--------|------|
| 1 | 101 | 2 |
商品表
| 商品ID | 商品名称 | 单价 |
|--------|----------|------|
| 101 | 键盘 | 100 |
第三范式（3NF）：消除传递依赖¶
在满足2NF的基础上，非主键字段之间不能有依赖关系。
❌ 违反第三范式：
用户名和用户地址依赖于用户ID，而不是直接依赖订单ID。✅ 符合第三范式：
订单表
| 订单ID | 用户ID |
|--------|--------|
| 1 | 1001 |
用户表
| 用户ID | 用户名 | 用户地址 |
|--------|--------|----------|
| 1001 | 张三 | 北京市 |
反范式化¶
在某些情况下，为了提高查询性能，可以适当违反范式，增加数据冗余。
使用场景： - 频繁查询的统计数据 - 需要避免多表关联的场景 - 读多写少的场景
示例：
-- 在订单表中冗余用户名，避免每次都关联用户表
CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
user_name VARCHAR(50), -- 冗余字段
total_amount DECIMAL(10,2)
);
命名规范¶
表名： - 使用小写字母和下划线 - 使用复数形式 - 见名知意
字段名： - 使用小写字母和下划线 - 避免使用保留字 - 布尔字段使用 is_ 前缀
索引名： - 普通索引：idx_表名_字段名 - 唯一索引：uk_表名_字段名 - 外键索引：fk_表名_字段名
实践示例：设计博客系统数据库¶
让我们通过一个实际案例来学习数据库设计。我们将设计一个简单的博客系统，包含用户、文章、评论和标签功能。
需求分析¶
功能需求： 1. 用户可以注册、登录 2. 用户可以发布文章 3. 用户可以对文章发表评论 4. 文章可以添加标签 5. 用户可以点赞文章
实体识别¶
根据需求，我们识别出以下实体： - 用户（User） - 文章（Article） - 评论（Comment） - 标签（Tag）
关系分析¶
实体之间的关系： - 用户 - 文章：一对多（一个用户可以发布多篇文章） - 文章 - 评论：一对多（一篇文章可以有多条评论） - 用户 - 评论：一对多（一个用户可以发表多条评论） - 文章 - 标签：多对多（一篇文章可以有多个标签，一个标签可以属于多篇文章） - 用户 - 文章（点赞）：多对多（一个用户可以点赞多篇文章，一篇文章可以被多个用户点赞）
表结构设计¶
1. 用户表（users）¶
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
avatar VARCHAR(255) DEFAULT NULL COMMENT '头像URL',
bio TEXT DEFAULT NULL COMMENT '个人简介',
status ENUM('active', 'inactive', 'banned') DEFAULT 'active' COMMENT '状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX idx_username (username),
INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
字段说明：
- id：主键，自增
- username：用户名，唯一
- email：邮箱，唯一
- password_hash：密码哈希值（不存储明文密码）
- avatar：头像URL
- bio：个人简介
- status：用户状态（活跃、未激活、封禁）
- created_at：创建时间，自动设置
- updated_at：更新时间，自动更新
2. 文章表（articles）¶
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '文章ID',
user_id INT NOT NULL COMMENT '作者ID',
title VARCHAR(200) NOT NULL COMMENT '标题',
content TEXT NOT NULL COMMENT '内容',
summary VARCHAR(500) DEFAULT NULL COMMENT '摘要',
cover_image VARCHAR(255) DEFAULT NULL COMMENT '封面图',
status ENUM('draft', 'published', 'archived') DEFAULT 'draft' COMMENT '状态',
view_count INT DEFAULT 0 COMMENT '浏览量',
like_count INT DEFAULT 0 COMMENT '点赞数',
comment_count INT DEFAULT 0 COMMENT '评论数',
published_at TIMESTAMP NULL DEFAULT NULL COMMENT '发布时间',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
INDEX idx_user_id (user_id),
INDEX idx_status (status),
INDEX idx_published_at (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';
字段说明：
- user_id：外键，关联用户表
- title：文章标题
- content：文章内容
- summary：文章摘要
- status：文章状态（草稿、已发布、已归档）
- view_count：浏览量（冗余字段，提高查询性能）
- like_count：点赞数（冗余字段）
- comment_count：评论数（冗余字段）
- published_at：发布时间
3. 评论表（comments）¶
CREATE TABLE comments (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '评论ID',
article_id INT NOT NULL COMMENT '文章ID',
user_id INT NOT NULL COMMENT '用户ID',
parent_id INT DEFAULT NULL COMMENT '父评论ID（用于回复）',
content TEXT NOT NULL COMMENT '评论内容',
status ENUM('normal', 'hidden', 'deleted') DEFAULT 'normal' COMMENT '状态',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,
INDEX idx_article_id (article_id),
INDEX idx_user_id (user_id),
INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='评论表';
字段说明：
- article_id：外键，关联文章表
- user_id：外键，关联用户表
- parent_id：父评论ID，用于实现评论回复功能
- content：评论内容
- status：评论状态
4. 标签表（tags）¶
CREATE TABLE tags (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '标签ID',
name VARCHAR(50) NOT NULL UNIQUE COMMENT '标签名',
slug VARCHAR(50) NOT NULL UNIQUE COMMENT '标签别名',
description VARCHAR(200) DEFAULT NULL COMMENT '标签描述',
article_count INT DEFAULT 0 COMMENT '文章数量',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
INDEX idx_name (name),
INDEX idx_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='标签表';
5. 文章标签关联表（article_tags）¶
CREATE TABLE article_tags (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
article_id INT NOT NULL COMMENT '文章ID',
tag_id INT NOT NULL COMMENT '标签ID',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
UNIQUE KEY uk_article_tag (article_id, tag_id),
INDEX idx_article_id (article_id),
INDEX idx_tag_id (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章标签关联表';
说明： - 多对多关系需要中间表 - 使用联合唯一索引防止重复关联
6. 文章点赞表（article_likes）¶
CREATE TABLE article_likes (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
article_id INT NOT NULL COMMENT '文章ID',
user_id INT NOT NULL COMMENT '用户ID',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
UNIQUE KEY uk_article_user (article_id, user_id),
INDEX idx_article_id (article_id),
INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章点赞表';
ER图¶
erDiagram
users ||--o{ articles : "发布"
users ||--o{ comments : "发表"
articles ||--o{ comments : "包含"
articles }o--o{ tags : "标记"
articles }o--o{ users : "点赞"
users {
int id PK
string username
string email
string password_hash
timestamp created_at
}
articles {
int id PK
int user_id FK
string title
text content
int view_count
timestamp created_at
}
comments {
int id PK
int article_id FK
int user_id FK
int parent_id FK
text content
timestamp created_at
}
tags {
int id PK
string name
string slug
}
article_tags {
int article_id FK
int tag_id FK
}
article_likes {
int article_id FK
int user_id FK
}SQL基础操作¶
创建和删除¶
创建数据库¶
-- 创建数据库
CREATE DATABASE blog_system
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;
-- 使用数据库
USE blog_system;
创建表¶
-- 创建用户表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
删除表和数据库¶
插入数据（INSERT）¶
-- 插入单条数据
INSERT INTO users (username, email)
VALUES ('zhangsan', 'zhang@example.com');
-- 插入多条数据
INSERT INTO users (username, email) VALUES
('lisi', 'li@example.com'),
('wangwu', 'wang@example.com'),
('zhaoliu', 'zhao@example.com');
-- 插入并返回ID（MySQL）
INSERT INTO users (username, email)
VALUES ('qianqi', 'qian@example.com');
SELECT LAST_INSERT_ID();
查询数据（SELECT）¶
基本查询¶
-- 查询所有字段
SELECT * FROM users;
-- 查询指定字段
SELECT id, username, email FROM users;
-- 查询并重命名字段
SELECT
id AS user_id,
username AS name,
email
FROM users;
-- 查询去重
SELECT DISTINCT status FROM users;
条件查询（WHERE）¶
-- 等于
SELECT * FROM users WHERE id = 1;
-- 不等于
SELECT * FROM users WHERE status != 'banned';
-- 大于、小于
SELECT * FROM articles WHERE view_count > 1000;
-- 范围查询
SELECT * FROM articles WHERE view_count BETWEEN 100 AND 1000;
-- IN查询
SELECT * FROM users WHERE status IN ('active', 'inactive');
-- 模糊查询
SELECT * FROM articles WHERE title LIKE '%数据库%';
-- 多条件查询
SELECT * FROM articles
WHERE status = 'published'
AND view_count > 100
AND created_at > '2024-01-01';
排序（ORDER BY）¶
-- 升序排序
SELECT * FROM articles ORDER BY created_at ASC;
-- 降序排序
SELECT * FROM articles ORDER BY view_count DESC;
-- 多字段排序
SELECT * FROM articles
ORDER BY status ASC, view_count DESC;
分页（LIMIT）¶
-- 查询前10条
SELECT * FROM articles LIMIT 10;
-- 分页查询（跳过前20条，查询10条）
SELECT * FROM articles LIMIT 20, 10;
-- 或者使用OFFSET
SELECT * FROM articles LIMIT 10 OFFSET 20;
聚合函数¶
-- 计数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT user_id) FROM articles;
-- 求和
SELECT SUM(view_count) FROM articles;
-- 平均值
SELECT AVG(view_count) FROM articles;
-- 最大值和最小值
SELECT MAX(view_count), MIN(view_count) FROM articles;
分组查询（GROUP BY）¶
-- 按用户分组统计文章数
SELECT user_id, COUNT(*) as article_count
FROM articles
GROUP BY user_id;
-- 分组后筛选（HAVING）
SELECT user_id, COUNT(*) as article_count
FROM articles
GROUP BY user_id
HAVING article_count > 5;
多表查询（JOIN）¶
-- 内连接（INNER JOIN）
SELECT
a.id,
a.title,
u.username as author
FROM articles a
INNER JOIN users u ON a.user_id = u.id;
-- 左连接（LEFT JOIN）
SELECT
u.username,
COUNT(a.id) as article_count
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
GROUP BY u.id;
-- 右连接（RIGHT JOIN）
SELECT
a.title,
u.username
FROM articles a
RIGHT JOIN users u ON a.user_id = u.id;
-- 多表连接
SELECT
a.title,
u.username as author,
c.content as comment
FROM articles a
INNER JOIN users u ON a.user_id = u.id
LEFT JOIN comments c ON a.id = c.article_id;
更新数据（UPDATE）¶
-- 更新单个字段
UPDATE users
SET email = 'newemail@example.com'
WHERE id = 1;
-- 更新多个字段
UPDATE users
SET
username = 'newname',
email = 'newemail@example.com',
updated_at = CURRENT_TIMESTAMP
WHERE id = 1;
-- 批量更新
UPDATE articles
SET status = 'archived'
WHERE created_at < '2023-01-01';
-- 基于计算更新
UPDATE articles
SET view_count = view_count + 1
WHERE id = 1;
删除数据（DELETE）¶
-- 删除指定记录
DELETE FROM users WHERE id = 1;
-- 批量删除
DELETE FROM comments WHERE created_at < '2023-01-01';
-- 删除所有记录（保留表结构）
DELETE FROM users;
-- 清空表（更快，重置自增ID）
TRUNCATE TABLE users;
索引优化¶
什么是索引？¶
索引是一种数据结构，用于快速查找数据。类似于书的目录，可以快速定位到需要的内容。
索引的优点： - 大大加快数据查询速度 - 可以保证数据的唯一性 - 加速表与表之间的连接
索引的缺点： - 占用额外的存储空间 - 降低数据插入、更新、删除的速度 - 需要维护索引
索引类型¶
1. 主键索引（PRIMARY KEY）¶
2. 唯一索引（UNIQUE）¶
-- 创建表时添加
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);
-- 后续添加
CREATE UNIQUE INDEX uk_username ON users(username);
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);
3. 普通索引（INDEX）¶
-- 创建表时添加
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200),
user_id INT,
INDEX idx_user_id (user_id),
INDEX idx_title (title)
);
-- 后续添加
CREATE INDEX idx_created_at ON articles(created_at);
ALTER TABLE articles ADD INDEX idx_status (status);
4. 组合索引（复合索引）¶
-- 创建组合索引
CREATE INDEX idx_user_status ON articles(user_id, status);
-- 查询时可以利用组合索引
SELECT * FROM articles WHERE user_id = 1 AND status = 'published';
SELECT * FROM articles WHERE user_id = 1; -- 也能使用索引
-- 但这个查询不能使用索引（不符合最左前缀原则）
SELECT * FROM articles WHERE status = 'published';
5. 全文索引（FULLTEXT）¶
-- 创建全文索引
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_content (content)
);
-- 使用全文索引搜索
SELECT * FROM articles
WHERE MATCH(content) AGAINST('数据库设计' IN NATURAL LANGUAGE MODE);
索引使用原则¶
1. 最左前缀原则¶
组合索引遵循最左前缀原则，查询条件必须从索引的最左列开始。
-- 索引：(user_id, status, created_at)
✅ 可以使用索引：
WHERE user_id = 1
WHERE user_id = 1 AND status = 'published'
WHERE user_id = 1 AND status = 'published' AND created_at > '2024-01-01'
❌ 不能使用索引：
WHERE status = 'published'
WHERE created_at > '2024-01-01'
WHERE status = 'published' AND created_at > '2024-01-01'
2. 选择性高的列建索引¶
选择性 = 不同值的数量 / 总行数
3. 避免在索引列上使用函数¶
-- ❌ 不能使用索引
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- ✅ 可以使用索引
SELECT * FROM users
WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01';
4. 避免使用 != 和 NOT IN¶
-- ❌ 不能使用索引
SELECT * FROM users WHERE status != 'banned';
-- ✅ 可以使用索引
SELECT * FROM users WHERE status IN ('active', 'inactive');
5. 使用覆盖索引¶
查询的字段都在索引中，不需要回表查询。
-- 创建覆盖索引
CREATE INDEX idx_user_email ON users(id, username, email);
-- 这个查询可以直接从索引获取数据，不需要回表
SELECT id, username, email FROM users WHERE id = 1;
查看和管理索引¶
-- 查看表的索引
SHOW INDEX FROM users;
-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';
-- 删除索引
DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_email;
EXPLAIN分析查询¶
EXPLAIN SELECT
a.title,
u.username
FROM articles a
INNER JOIN users u ON a.user_id = u.id
WHERE a.status = 'published'
ORDER BY a.created_at DESC
LIMIT 10;
EXPLAIN输出字段：
- type：连接类型（ALL < index < range < ref < eq_ref < const）
- possible_keys：可能使用的索引
- key：实际使用的索引
- rows：扫描的行数
- Extra：额外信息
性能优化¶
查询优化¶
1. 只查询需要的字段¶
2. 避免在WHERE子句中使用函数¶
-- ❌ 不能使用索引
SELECT * FROM users WHERE LOWER(username) = 'zhangsan';
-- ✅ 可以使用索引
SELECT * FROM users WHERE username = 'zhangsan';
3. 使用LIMIT限制返回行数¶
-- 分页查询
SELECT * FROM articles
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;
4. 避免使用子查询，改用JOIN¶
-- ❌ 子查询性能较差
SELECT * FROM articles
WHERE user_id IN (
SELECT id FROM users WHERE status = 'active'
);
-- ✅ 使用JOIN性能更好
SELECT a.* FROM articles a
INNER JOIN users u ON a.user_id = u.id
WHERE u.status = 'active';
5. 使用EXISTS代替IN¶
-- ❌ IN性能较差
SELECT * FROM users
WHERE id IN (SELECT user_id FROM articles);
-- ✅ EXISTS性能更好
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM articles a WHERE a.user_id = u.id
);
表结构优化¶
1. 选择合适的数据类型¶
-- ❌ 使用过大的类型
CREATE TABLE users (
id BIGINT, -- 如果用户数不会超过21亿，INT就够了
age VARCHAR(10), -- 年龄应该用TINYINT
is_active VARCHAR(10) -- 布尔值应该用TINYINT(1)
);
-- ✅ 使用合适的类型
CREATE TABLE users (
id INT,
age TINYINT,
is_active TINYINT(1)
);
2. 使用NOT NULL¶
-- ✅ 尽量使用NOT NULL
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
bio TEXT DEFAULT '' -- 使用默认值代替NULL
);
3. 适当使用冗余字段¶
-- 在文章表中冗余统计数据，避免频繁COUNT
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200),
view_count INT DEFAULT 0, -- 冗余字段
like_count INT DEFAULT 0, -- 冗余字段
comment_count INT DEFAULT 0 -- 冗余字段
);
4. 垂直分表¶
将不常用的大字段分离到另一个表。
-- 主表（常用字段）
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200),
summary VARCHAR(500),
user_id INT,
created_at TIMESTAMP
);
-- 扩展表（不常用的大字段）
CREATE TABLE article_contents (
article_id INT PRIMARY KEY,
content LONGTEXT,
FOREIGN KEY (article_id) REFERENCES articles(id)
);
5. 水平分表¶
当单表数据量过大时，按照某个规则分成多个表。
-- 按年份分表
CREATE TABLE articles_2023 (...);
CREATE TABLE articles_2024 (...);
CREATE TABLE articles_2025 (...);
-- 按用户ID范围分表
CREATE TABLE users_0 (...); -- user_id 0-999999
CREATE TABLE users_1 (...); -- user_id 1000000-1999999
缓存策略¶
1. 查询缓存¶
MySQL的查询缓存（MySQL 8.0已移除）：
2. 应用层缓存¶
使用Redis等缓存热点数据：
# Python示例
import redis
r = redis.Redis(host='localhost', port=6379)
# 缓存文章详情
def get_article(article_id):
# 先从缓存获取
cache_key = f'article:{article_id}'
cached = r.get(cache_key)
if cached:
return json.loads(cached)
# 缓存未命中，从数据库查询
article = db.query('SELECT * FROM articles WHERE id = ?', article_id)
# 写入缓存，过期时间1小时
r.setex(cache_key, 3600, json.dumps(article))
return article
连接池¶
使用连接池减少数据库连接开销：
# Python示例（使用SQLAlchemy）
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool
engine = create_engine(
'mysql://user:password@localhost/blog_system',
poolclass=QueuePool,
pool_size=10, # 连接池大小
max_overflow=20, # 最大溢出连接数
pool_timeout=30, # 获取连接超时时间
pool_recycle=3600 # 连接回收时间
)
数据库安全¶
SQL注入防护¶
SQL注入是最常见的数据库安全问题。
危险示例¶
# ❌ 危险：直接拼接SQL
username = request.get('username')
password = request.get('password')
sql = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
result = db.execute(sql)
# 攻击者输入：username = "admin' OR '1'='1"
# 实际执行的SQL：SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = ''
安全做法¶
# ✅ 安全：使用参数化查询
username = request.get('username')
password = request.get('password')
sql = "SELECT * FROM users WHERE username = ? AND password = ?"
result = db.execute(sql, (username, password))
密码存储¶
永远不要存储明文密码。
import hashlib
import os
# ❌ 危险：存储明文密码
password = "user_password"
db.execute("INSERT INTO users (password) VALUES (?)", (password,))
# ✅ 安全：使用哈希存储
def hash_password(password):
# 生成随机盐值
salt = os.urandom(32)
# 使用PBKDF2哈希
key = hashlib.pbkdf2_hmac('sha256', password.encode('utf-8'), salt, 100000)
# 存储盐值和哈希值
return salt + key
def verify_password(stored_password, provided_password):
salt = stored_password[:32]
stored_key = stored_password[32:]
key = hashlib.pbkdf2_hmac('sha256', provided_password.encode('utf-8'), salt, 100000)
return key == stored_key
权限控制¶
1. 最小权限原则¶
-- 创建只读用户
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON blog_system.* TO 'readonly'@'localhost';
-- 创建应用用户（有限权限）
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON blog_system.* TO 'app_user'@'localhost';
-- 不要使用root用户连接应用
2. 行级权限¶
-- 用户只能查看自己的数据
SELECT * FROM articles WHERE user_id = current_user_id;
-- 用户只能修改自己的数据
UPDATE articles
SET title = 'new title'
WHERE id = 1 AND user_id = current_user_id;
数据备份¶
1. 定期备份¶
# 备份整个数据库
mysqldump -u root -p blog_system > backup_$(date +%Y%m%d).sql
# 备份指定表
mysqldump -u root -p blog_system users articles > backup_tables.sql
# 只备份结构
mysqldump -u root -p --no-data blog_system > schema.sql
2. 恢复数据¶
# 恢复数据库
mysql -u root -p blog_system < backup_20240101.sql
# 恢复到新数据库
mysql -u root -p -e "CREATE DATABASE blog_system_restore"
mysql -u root -p blog_system_restore < backup_20240101.sql
数据加密¶
1. 传输加密¶
使用SSL/TLS加密数据库连接：
# Python示例
import mysql.connector
conn = mysql.connector.connect(
host='localhost',
user='user',
password='password',
database='blog_system',
ssl_ca='/path/to/ca.pem',
ssl_cert='/path/to/client-cert.pem',
ssl_key='/path/to/client-key.pem'
)
2. 字段加密¶
对敏感字段进行加密：
-- 使用AES加密
INSERT INTO users (username, phone)
VALUES ('zhangsan', AES_ENCRYPT('13800138000', 'encryption_key'));
-- 解密查询
SELECT username, AES_DECRYPT(phone, 'encryption_key') as phone
FROM users;
常见问题¶
Q1: 什么时候应该使用索引？¶
A: 以下情况应该建立索引： - 经常作为WHERE条件的字段 - 经常用于JOIN的字段 - 经常用于ORDER BY的字段 - 经常用于GROUP BY的字段 - 选择性高的字段（不同值多）
不应该建立索引的情况： - 表数据量很小（几百行） - 频繁更新的字段 - 选择性低的字段（如性别） - 很少使用的字段
Q2: 主键应该选择什么类型？¶
A: 推荐使用自增整数作为主键：
优点： - 占用空间小 - 查询效率高 - 插入性能好 - 避免页分裂
不推荐使用UUID作为主键（除非有特殊需求）： - 占用空间大（36字节 vs 4字节） - 无序，导致页分裂 - 索引效率低
Q3: 如何处理软删除？¶
A: 使用deleted_at字段标记删除：
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50),
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_deleted_at (deleted_at)
);
-- 软删除
UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1;
-- 查询未删除的数据
SELECT * FROM users WHERE deleted_at IS NULL;
-- 恢复数据
UPDATE users SET deleted_at = NULL WHERE id = 1;
Q4: 如何设计树形结构（如评论回复）？¶
A: 常见的三种方案：
方案1：邻接列表（Adjacency List）
优点：简单易懂 缺点：查询整棵树需要递归方案2：路径枚举（Path Enumeration）
优点：查询子树方便 缺点：路径长度有限方案3：嵌套集（Nested Set）
优点：查询效率高 缺点：插入和更新复杂Q5: 如何优化大数据量的分页查询？¶
A: 使用延迟关联或子查询：
-- ❌ 传统分页（深度分页慢）
SELECT * FROM articles
ORDER BY id
LIMIT 1000000, 20;
-- ✅ 使用子查询优化
SELECT * FROM articles
WHERE id >= (
SELECT id FROM articles ORDER BY id LIMIT 1000000, 1
)
LIMIT 20;
-- ✅ 使用上次查询的最大ID
SELECT * FROM articles
WHERE id > last_id
ORDER BY id
LIMIT 20;
最佳实践¶
设计原则¶
- 遵循范式，适度反范式
- 基础设计遵循第三范式
- 根据性能需求适度反范式
-
权衡数据一致性和查询性能
-
使用有意义的命名
- 表名使用复数形式：users, articles
- 字段名见名知意：created_at, user_id
-
避免使用保留字和特殊字符
-
合理使用数据类型
- 选择最小的合适类型
- 整数优于字符串
-
定长优于变长（在合适的场景）
-
添加必要的约束
- 主键约束保证唯一性
- 外键约束保证引用完整性
- 非空约束保证数据完整性
-
默认值约束简化插入操作
-
合理使用索引
- 为常用查询字段建索引
- 避免过多索引
- 定期分析索引使用情况
开发规范¶
1. 表设计规范¶
-- ✅ 推荐的表结构
CREATE TABLE users (
-- 主键：自增整数
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
-- 业务字段：合适的类型和约束
username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
-- 状态字段：使用ENUM
status ENUM('active', 'inactive', 'banned') DEFAULT 'active' COMMENT '状态',
-- 时间字段：自动维护
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
-- 索引
INDEX idx_username (username),
INDEX idx_email (email),
INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
2. SQL编写规范¶
-- ✅ 推荐的SQL写法
SELECT
u.id,
u.username,
u.email,
COUNT(a.id) as article_count
FROM users u
LEFT JOIN articles a ON u.id = a.user_id
WHERE u.status = 'active'
AND u.created_at >= '2024-01-01'
GROUP BY u.id
HAVING article_count > 0
ORDER BY article_count DESC
LIMIT 20;
-- 规范要点：
-- 1. 关键字大写
-- 2. 字段和表名小写
-- 3. 适当换行和缩进
-- 4. 使用表别名
-- 5. 明确指定字段
3. 事务使用规范¶
-- 开启事务
START TRANSACTION;
-- 执行多个操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
INSERT INTO transactions (from_id, to_id, amount) VALUES (1, 2, 100);
-- 提交事务
COMMIT;
-- 或者回滚
-- ROLLBACK;
性能监控¶
1. 慢查询日志¶
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 超过2秒的查询记录
-- 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';
2. 查询分析¶
-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM articles WHERE user_id = 1;
-- 查看查询执行时间
SET profiling = 1;
SELECT * FROM articles WHERE user_id = 1;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
3. 表状态监控¶
-- 查看表状态
SHOW TABLE STATUS LIKE 'articles';
-- 查看表大小
SELECT
table_name,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.TABLES
WHERE table_schema = 'blog_system'
ORDER BY size_mb DESC;
总结¶
本文介绍了数据库设计的核心知识：
基础概念： - 理解数据库、表、字段、约束等基本概念 - 掌握常用的数据类型和约束类型 - 了解关系型数据库和非关系型数据库的区别
设计原则： - 遵循三大范式减少数据冗余 - 合理使用反范式提高查询性能 - 使用规范的命名和结构
SQL操作： - 掌握基本的增删改查操作 - 学会使用JOIN进行多表查询 - 了解聚合函数和分组查询
性能优化： - 合理使用索引加速查询 - 优化查询语句提高效率 - 使用缓存减少数据库压力
安全防护： - 防止SQL注入攻击 - 安全存储敏感数据 - 做好权限控制和数据备份
数据库设计是一个需要不断实践和优化的过程。建议你： 1. 多动手实践，设计实际的数据库 2. 学习优秀的开源项目的数据库设计 3. 关注性能监控，持续优化 4. 保持学习，了解新的技术和最佳实践
延伸阅读¶
推荐进一步学习的资源：
- MySQL官方文档 - 权威的MySQL参考手册
- PostgreSQL官方文档 - PostgreSQL完整文档
- 数据库设计规范 - 阿里巴巴数据库设计规范
- 高性能MySQL - 经典的MySQL性能优化书籍
- SQL必知必会 - SQL入门经典教材
参考资料¶
- MySQL官方文档 - https://dev.mysql.com/doc/
- PostgreSQL官方文档 - https://www.postgresql.org/docs/
- 数据库设计三大范式 - 数据库理论基础
- 阿里巴巴Java开发手册（数据库部分）
- 高性能MySQL（第3版）- Baron Schwartz等著
练习题：
- 设计一个电商系统的数据库，包含用户、商品、订单、购物车等功能
- 为你设计的数据库添加合适的索引，并使用EXPLAIN分析查询性能
- 编写SQL查询：统计每个用户的订单总金额，按金额降序排列
- 实现一个树形评论系统，支持多级回复
- 优化一个慢查询，将执行时间从5秒降低到1秒以内
下一步：建议学习 RESTful API设计与开发，了解如何通过API访问数据库。