数据库基础#

表#

表是关系型数据库中最基本的数据组织形式，通常对应一个业务对象，例如用户表、订单表、商品表。

字段#

字段是表中的一列，用来描述某个属性，例如用户名、密码、手机号、创建时间。

记录#

记录是表中的一行，代表一条具体数据。

主键#

主键用于唯一标识一条记录，不能重复，也不能为 NULL。

常见主键设计：

• 自增整数：简单、查询效率高。
• UUID：全局唯一，但长度较长，索引成本高。
• 雪花算法 ID：适合分布式系统，能保证全局唯一，并且整体趋势递增。

外键#

外键用于建立表与表之间的关系。例如订单表中的 user_id 可以关联用户表的 id。

外键可以保证数据完整性，但在高并发项目中，有时会选择不使用数据库外键，而是在业务代码中维护关联关系，减少数据库约束带来的性能和维护成本。

DDL：数据定义语言#

DDL 用来定义数据库结构，例如创建表、修改表、删除表。

1
CREATE TABLE user (
2
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
3
  username VARCHAR(50) NOT NULL,
4
  age INT,
5
  email VARCHAR(100),
6
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
7
);

常见语句：

• CREATE：创建数据库、表、索引。
• ALTER：修改表结构。
• DROP：删除数据库、表、索引。

DML：数据操作语言#

DML 用来操作表中的数据。

1
INSERT INTO user(username, age, email)
2
VALUES ('zhangsan', 18, 'zhangsan@example.com');
3

4
UPDATE user
5
SET age = 19
6
WHERE id = 1;
7

8
DELETE FROM user
9
WHERE id = 1;

常见语句：

• INSERT：新增数据。
• UPDATE：修改数据。
• DELETE：删除数据。

DQL：数据查询语言#

DQL 主要指 SELECT 查询。

1
SELECT id, username, age
2
FROM user
3
WHERE age >= 18
4
ORDER BY created_at DESC
5
LIMIT 10;

常见关键字：

• WHERE：过滤条件。
• GROUP BY：分组。
• HAVING：对分组后的结果继续过滤。
• ORDER BY：排序。
• LIMIT：限制返回条数。

DCL：数据控制语言#

DCL 用来控制用户权限。

1
GRANT SELECT, INSERT ON blog.* TO 'blog_user'@'%';
2
REVOKE INSERT ON blog.* FROM 'blog_user'@'%';

主键约束#

主键约束保证字段唯一且不为空。

1
id BIGINT PRIMARY KEY

唯一约束#

唯一约束保证字段值不能重复。

1
email VARCHAR(100) UNIQUE

适合加唯一约束的字段：

• 用户名
• 邮箱
• 手机号
• 订单号

非空约束#

非空约束保证字段必须有值。

1
username VARCHAR(50) NOT NULL

默认值约束#

默认值约束用于字段没有显式赋值时自动填充。

1
status TINYINT DEFAULT 1

外键约束#

外键约束用于保证关联表之间的数据一致性。

1
user_id BIGINT,
2
FOREIGN KEY (user_id) REFERENCES user(id)

第一范式#

第一范式要求字段具有原子性，也就是属性不可再分。

不符合第一范式的例子：

contact 同时包含手机号和邮箱，字段内部还可以继续拆分。

更合理的设计：

第二范式#

第二范式建立在第一范式基础上，要求非主属性必须完全依赖于主键，不能只依赖联合主键的一部分。

例如学生选课表：

如果主键是 (student_id, course_id)，那么：

• score 依赖完整主键。
• student_name 只依赖 student_id。
• course_name 只依赖 course_id。

这就存在部分函数依赖，不符合第二范式。

更合理的拆分方式：

• 学生表：student_id、student_name
• 课程表：course_id、course_name
• 成绩表：student_id、course_id、score

第三范式#

第三范式建立在第二范式基础上，要求非主属性不能传递依赖于主键。

例如：

这里存在：

1
student_id -> dept_name
2
dept_name -> dept_leader

也就是说 dept_leader 不是直接依赖学生，而是依赖院系。此时应该拆分为学生表和院系表。

范式不是越高越好#

范式可以减少数据冗余，但过度拆表会导致查询时需要频繁 JOIN，影响性能和开发复杂度。

实际项目中通常会在规范化和性能之间做平衡：

• 核心数据尽量满足第三范式。
• 高频查询可以适当冗余字段。
• 冗余字段要通过业务代码、定时任务或消息队列保证一致性。

ACID 特性#

事务具有四个重要特性，简称 ACID。

脏读#

一个事务读取到了另一个事务尚未提交的数据。

例如事务 A 修改了用户余额但还没提交，事务 B 就读取到了这个临时余额。如果事务 A 回滚，事务 B 读到的数据就是脏数据。

不可重复读#

同一个事务中，多次读取同一条数据，结果不一致。

例如事务 A 第一次读取余额是 100，事务 B 修改并提交余额为 200，事务 A 再次读取时变成 200。

幻读#

同一个事务中，多次按条件查询，查询到的记录数量不一致。

例如事务 A 查询年龄大于 18 的用户有 10 条，事务 B 新增了一条符合条件的数据并提交，事务 A 再次查询时变成 11 条。

常见索引类型#

• 主键索引：主键自动创建索引，用于唯一定位一条记录。
• 唯一索引：保证字段值唯一，同时提升查询速度。
• 普通索引：用于提升查询效率。
• 联合索引：多个字段组成的索引。

示例：

1
CREATE UNIQUE INDEX uk_user_email ON user(email);
2
CREATE INDEX idx_user_age ON user(age);
3
CREATE INDEX idx_user_age_name ON user(age, username);

使用联合索引时要注意最左前缀原则。

例如索引是 (age, username)：

• WHERE age = 18 可以使用索引。
• WHERE age = 18 AND username = 'zhangsan' 可以使用索引。
• WHERE username = 'zhangsan' 通常不能充分使用这个联合索引。

表锁#

表锁会锁住整张表，粒度大，并发性能较低，但实现简单。

行锁#

行锁只锁住具体的数据行，粒度小，并发性能更好。InnoDB 支持行锁。

共享锁#

共享锁也叫读锁。多个事务可以同时持有共享锁。

1
SELECT *
2
FROM user
3
WHERE id = 1
4
LOCK IN SHARE MODE;

排他锁#

排他锁也叫写锁。一个事务持有排他锁时，其他事务不能再对同一行加共享锁或排他锁。

1
SELECT *
2
FROM user
3
WHERE id = 1
4
FOR UPDATE;

间隙锁和临键锁#

InnoDB 为了解决并发插入导致的幻读问题，引入了间隙锁和临键锁。

• 记录锁：锁住已经存在的记录。
• 间隙锁：锁住记录之间的范围。
• 临键锁：记录锁 + 间隙锁。

Inner Join#

内连接只返回两张表都匹配的数据。

1
SELECT u.username, o.order_no
2
FROM user u
3
INNER JOIN orders o ON u.id = o.user_id;

Left Join#

左连接返回左表全部数据，右表匹配不到时返回 NULL。

1
SELECT u.username, o.order_no
2
FROM user u
3
LEFT JOIN orders o ON u.id = o.user_id;

Right Join#

右连接返回右表全部数据，左表匹配不到时返回 NULL。

1
SELECT u.username, o.order_no
2
FROM user u
3
RIGHT JOIN orders o ON u.id = o.user_id;

字段设计#

字段设计要尽量清晰、稳定、可扩展。

常见建议：

• 主键尽量使用无业务含义的 ID。
• 金额不要使用浮点数，推荐使用 DECIMAL 或以分为单位的整数。
• 时间字段建议统一使用 created_at、updated_at。
• 状态字段可以使用 TINYINT，并在代码中定义枚举。
• 重要字段加 NOT NULL 和默认值。

表设计#

表设计要围绕业务对象展开。

例如订单系统通常会拆分为：

• 订单主表：保存订单整体信息。
• 订单明细表：保存商品明细。
• 支付记录表：保存支付流水。
• 物流记录表：保存物流状态。

这样可以避免一张表字段过多，也能让不同业务数据边界更清楚。

索引设计#

索引设计要根据查询场景来决定，不是越多越好。

适合建立索引的字段：

• 经常作为查询条件的字段。
• 经常用于排序的字段。
• 经常用于分组的字段。
• 表关联字段。
• 唯一业务字段。

不适合建立索引的字段：

• 数据量很小的表。
• 区分度很低的字段，例如性别。
• 经常更新但很少查询的字段。

索引会提高查询速度，但也会增加写入成本，因为新增、修改、删除数据时，数据库需要同步维护索引结构。

使用 EXPLAIN 分析执行计划#

1
EXPLAIN
2
SELECT *
3
FROM user
4
WHERE username = 'zhangsan';

重点关注：

• type：访问类型，通常越接近 const、ref 越好。
• key：实际使用的索引。
• rows：预计扫描行数。
• Extra：是否出现 Using filesort、Using temporary。

检查是否命中索引#

如果查询没有使用索引，要检查：

• 查询条件字段是否有索引。
• 是否触发了索引失效。
• 联合索引顺序是否合理。

减少返回数据量#

不要习惯性写：

1
SELECT *
2
FROM user;

更推荐只查询需要的字段：

1
SELECT id, username, email
2
FROM user;

避免深分页#

深分页会导致数据库扫描大量数据。

1
SELECT *
2
FROM article
3
ORDER BY id
4
LIMIT 100000, 20;

可以改为基于上一页最后一条 ID 查询：

1
SELECT *
2
FROM article
3
WHERE id > 100000
4
ORDER BY id
5
LIMIT 20;

垂直拆分#

按照业务模块拆分。

例如：

• 用户库
• 订单库
• 商品库
• 支付库

水平拆分#

按照某个规则把同一张表的数据拆到多张表中。

例如根据用户 ID 取模：

1
user_id % 4

拆分为：

• order_0
• order_1
• order_2
• order_3

分库分表可以提升扩展能力，但也会带来复杂度：

• 跨库事务更难处理。
• 跨库 Join 更复杂。
• 全局唯一 ID 需要单独设计。
• 分页、排序、统计会更麻烦。

所以分库分表通常是数据量和并发压力达到一定程度后的方案，不应该一开始就过度设计。