数据库原理
这篇笔记讲什么
从「为什么不用文件存数据」讲起,覆盖关系模型与 SQL、范式、索引与 B+ 树、事务 ACID 与隔离级别、锁与 MVCC、EXPLAIN 查询优化、备份恢复、分库分表。
读完你应该能:把一张不合范式的表改造成 3NF、判断一条 SQL 为什么没走索引、说清「可重复读」下幻读是否真的不存在、看懂 EXPLAIN 的 type 与 Extra 列、给出一个可落地的分库分表方案。
一、是什么:数据库不是「高级文件系统」
数据库(Database) 是按一定数据模型组织、存储和管理的数据集合;数据库管理系统(DBMS, Database Management System) 是管理它的软件,如 MySQL、PostgreSQL、SQLite。
关系型数据库的理论基础是 E. F. Codd 于 1970 年提出的关系模型(Relational Model),把数据看成二维表(关系)的集合,用关系代数做查询。
二、为什么不用文件存数据
用 CSV/JSON 文件也能存数据,但一旦数据量、并发量上来,就会遇到文件系统无法解决的问题:
| 维度 | 文件系统 | 数据库 |
|---|---|---|
| 并发控制 | 无,多进程写会互相覆盖 | 锁 + MVCC,支持高并发读写 |
| 事务 | 无,写到一半崩溃就是脏数据 | ACID 保证原子性与持久性 |
| 查询能力 | 只能顺序扫描,自己写过滤逻辑 | 声明式 SQL + 查询优化器 |
| 索引 | 要自己实现 | B+ 树/哈希/全文/向量索引 |
| 约束 | 全靠应用代码保证 | 主键/外键/唯一/检查约束在引擎层兜底 |
| 崩溃恢复 | 无 | WAL/redo/undo 日志,可恢复到一致点 |
| 权限与审计 | 文件系统级,粒度粗 | 表/列级授权、审计日志 |
| 数据量 | 单文件到 GB 级开始吃力 | 分库分表可到 PB 级 |
一句话总结
数据库 = 文件存储 + 索引 + 事务 + 并发控制 + 查询优化器 + 权限。它的价值不在于「能存」,而在于「在并发、故障、大数据量下仍然正确且可查询」。
三、关系模型
3.1 基本术语
| 关系模型术语 | 日常说法 | 例子 |
|---|---|---|
| 关系 Relation | 表 Table | student |
| 元组 Tuple | 行 Row / 记录 | (1, '张三', '男') |
| 属性 Attribute | 列 Column / 字段 | name |
| 域 Domain | 列的取值范围 | gender ∈ {'男','女'} |
| 基数 Cardinality | 行数 | 1000 行 |
| 度 Degree | 列数 | 3 列 |
| 候选键 Candidate Key | 能唯一标识元组的最小属性集 | {id}、{email} |
| 主键 Primary Key | 从候选键中选定的一个 | id |
| 外键 Foreign Key | 引用另一关系主键的属性 | class_id → class.id |
关系模型的两条基本要求:属性不可再分(原子性)、元组不重复且无序。
3.2 完整性约束
| 约束 | 含义 | 违反示例 |
|---|---|---|
| 实体完整性 | 主键非空且唯一 | 插入 id = NULL |
| 参照完整性 | 外键值必须是目标表存在的主键值,或为 NULL | 插入 class_id = 999 而该班级不存在 |
| 域完整性 | 值属于定义的域 | age = -5 |
| 用户定义完整性 | 业务规则 | stock >= 0 |
CREATE TABLE student (
id BIGINT NOT NULL AUTO_INCREMENT, -- 实体完整性
name VARCHAR(50) NOT NULL,
gender CHAR(1) NOT NULL,
age TINYINT UNSIGNED NULL,
class_id BIGINT NULL,
email VARCHAR(120) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_email (email), -- 候选键约束
KEY idx_class (class_id),
CONSTRAINT fk_student_class FOREIGN KEY (class_id) -- 参照完整性
REFERENCES class(id) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT chk_gender CHECK (gender IN ('男','女')), -- 域完整性
CONSTRAINT chk_age CHECK (age IS NULL OR (age BETWEEN 0 AND 150))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
外键的性能代价
外键会在每次写入时检查目标表,并要求目标列有索引,高并发写入场景下会形成锁竞争。互联网公司常在数据库层去掉外键,改由应用层保证一致性——但这就把责任转移给了代码,必须有配套的测试与校验任务。
四、SQL 分类
SQL(Structured Query Language)按用途分五类:
| 分类 | 全称 | 作用 | 代表语句 | 能否回滚 |
|---|---|---|---|---|
| DDL | Data Definition Language | 定义结构 | CREATE / ALTER / DROP / TRUNCATE | 多数数据库隐式提交,不可回滚 |
| DML | Data Manipulation Language | 增删改数据 | INSERT / UPDATE / DELETE / MERGE | 可回滚 |
| DQL | Data Query Language | 查询 | SELECT | 不涉及 |
| DCL | Data Control Language | 权限 | GRANT / REVOKE | 视数据库而定 |
| TCL | Transaction Control Language | 事务控制 | BEGIN / COMMIT / ROLLBACK / SAVEPOINT | — |
4.1 典型语句速览
-- DDL:建表后加列与索引(大表加列在 MySQL 8.0 支持 INSTANT 算法,速度快)
ALTER TABLE student ADD COLUMN phone VARCHAR(20) NULL;
CREATE INDEX idx_name ON student(name);
-- DML
INSERT INTO student (name, gender, age, class_id, email)
VALUES ('张三', '男', 20, 1, 'zhangsan@example.com');
UPDATE student SET age = 21 WHERE id = 1;
DELETE FROM student WHERE id = 1;
-- DQL:JOIN + 聚合 + 分组过滤 + 排序分页
SELECT c.name AS class_name,
COUNT(*) AS student_count,
AVG(s.age) AS avg_age
FROM student s
JOIN class c ON c.id = s.class_id
WHERE s.created_at >= '2026-01-01'
GROUP BY c.name
HAVING COUNT(*) >= 5
ORDER BY student_count DESC
LIMIT 10 OFFSET 20;
-- TCL
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT; -- 出错时用 ROLLBACK
4.2 JOIN 类型对照
| JOIN | 结果集 |
|---|---|
| INNER JOIN | 两表都匹配的行 |
| LEFT JOIN | 左表全部 + 右表匹配(不匹配补 NULL) |
| RIGHT JOIN | 右表全部 + 左表匹配 |
| FULL OUTER JOIN | 两表全部(MySQL 不支持,用 UNION 模拟) |
| CROSS JOIN | 笛卡尔积 |
| SELF JOIN | 自己和自己连接,如查员工的上级 |
-- MySQL 模拟 FULL OUTER JOIN
SELECT s.id, s.name, c.name FROM student s LEFT JOIN class c ON s.class_id = c.id
UNION
SELECT s.id, s.name, c.name FROM student s RIGHT JOIN class c ON s.class_id = c.id;
五、范式:为什么要拆表
范式(Normal Form)解决的是数据冗余与更新异常——同一份数据存多份,改一处忘了改另一处就出现不一致。
5.1 先看一个「坏表」
| 学号 | 姓名 | 课程 | 课程学分 | 教师 | 教师办公室 |
|---|---|---|---|---|---|
| S01 | 张三 | 数据库,网络 | 3,2 | 李老师 | A301 |
| S02 | 李四 | 数据库 | 3 | 李老师 | A301 |
问题:单元格里有多个值、教师办公室重复存储、修改李老师办公室要改多行(更新异常)、删掉最后一门课会丢掉教师信息(删除异常)。
5.2 1NF:属性原子性
定义:每个属性都是不可再分的原子值,不允许「表中表」或逗号分隔的列表。
| 不满足 1NF | 改造后满足 1NF |
|---|---|
课程 = '数据库,网络' | 拆成多行:(S01, 数据库)、(S01, 网络) |
-- 不符合 1NF 的写法
CREATE TABLE bad (sno VARCHAR(10), name VARCHAR(20), courses VARCHAR(200));
-- 符合 1NF:一行一个课程
CREATE TABLE course_choice (
sno VARCHAR(10),
name VARCHAR(20),
course VARCHAR(50),
credit INT
);
实践中的取舍
有时为了减少 JOIN 会刻意存 JSON 数组或逗号串(反范式)。这在只读、不需要按该字段查询的场景可以接受,但必须明确放弃在数据库层对该字段做条件过滤与统计。
5.3 2NF:消除部分函数依赖
定义:在 1NF 基础上,非主属性完全依赖于候选键,不能只依赖主键的一部分(针对复合主键)。
假设主键是 (sno, course):
| 不满足 2NF | 问题 |
|---|---|
(sno, course) → name | name 只依赖 sno,是部分依赖 |
(sno, course) → credit | credit 只依赖 course,是部分依赖 |
改造:把部分依赖的属性拆到各自的表。
-- 拆成三张表,每个非主属性都完全依赖本表主键
CREATE TABLE student (sno VARCHAR(10) PRIMARY KEY, name VARCHAR(20));
CREATE TABLE course (course VARCHAR(50) PRIMARY KEY, credit INT);
CREATE TABLE sc (sno VARCHAR(10), course VARCHAR(50), grade INT,
PRIMARY KEY (sno, course));
5.4 3NF:消除传递函数依赖
定义:在 2NF 基础上,非主属性不传递依赖于候选键,即不允许 键 → A → B。
| 不满足 3NF | 问题 |
|---|---|
sno → dept → dept_office | 系办公室由系决定,系由学号决定,构成传递依赖 |
改造:
-- 改造前:student(sno, name, dept, dept_office) —— dept_office 传递依赖
-- 改造后:
CREATE TABLE department (dept VARCHAR(50) PRIMARY KEY, dept_office VARCHAR(50));
CREATE TABLE student (sno VARCHAR(10) PRIMARY KEY, name VARCHAR(20),
dept VARCHAR(50),
FOREIGN KEY (dept) REFERENCES department(dept));
5.5 BCNF:消除主属性对键的部分与传递依赖
定义:在 3NF 基础上,每一个决定因素都必须是候选键。3NF 允许主属性被非候选键决定,BCNF 不允许。
经典反例:授课(学生, 课程, 教师),业务规则为「每个教师只教一门课,每门课有多个教师,学生选某教师的课就确定课程」。
函数依赖:(学生, 课程) → 教师,教师 → 课程。
候选键是 (学生, 课程) 与 (学生, 教师),课程 是主属性,但被非候选键 教师 决定 → 违反 BCNF(3NF 允许,因为它约束的是非主属性)。
| 不满足 BCNF | 问题 |
|---|---|
| 同一教师教不同课会产生矛盾数据;删除某教师信息可能丢失「教师-课程」关联 | 更新异常 |
改造:拆成 教师课程(教师, 课程) 与 选课(学生, 教师)。
CREATE TABLE teacher_course (teacher VARCHAR(20) PRIMARY KEY, course VARCHAR(50) NOT NULL);
CREATE TABLE student_choice (sno VARCHAR(10), teacher VARCHAR(20),
PRIMARY KEY (sno, teacher));
5.6 范式对照与工程取舍
| 范式 | 消除的问题 | 一句话判据 |
|---|---|---|
| 1NF | 非原子属性 | 单元格里只有一个值 |
| 2NF | 部分函数依赖 | 非主属性不依赖复合主键的一部分 |
| 3NF | 传递函数依赖 | 非主属性之间不存在决定关系 |
| BCNF | 主属性被非候选键决定 | 每个决定因素都是候选键 |
工程上做到 3NF 通常就够了
互联网业务常主动反范式:冗余统计字段(如
comment_count)、冗余用户名快照(订单表存下单时的收货人),目的是减少 JOIN、提升读性能。反范式的前提是明确了谁来负责同步冗余字段(触发器、应用层事务、或异步补偿任务)。
六、索引
6.1 B+ 树:为什么数据库都用它
B+ 树(B Plus Tree) 是一种多路平衡查找树,特点是:
- 所有数据都在叶子节点,内部节点只存键用于导航。
- 叶子节点用双向链表串联,天然支持范围查询与排序。
- 树高很低:InnoDB 页大小 16 KB,若主键 BIGINT(8 字节)+ 页指针(6 字节),一个内部节点可存约 个键,三层 B+ 树可容纳 行左右——即三千万行数据只需 3 次磁盘 I/O。
B+ 树 vs B 树:
| 对比项 | B 树 | B+ 树 |
|---|---|---|
| 数据存放位置 | 所有节点都可能存数据 | 只有叶子节点存数据 |
| 内部节点 | 存键+数据+指针 | 只存键+指针,扇出更大 |
| 树高 | 较高 | 更低,I/O 更少 |
| 范围查询 | 需要中序遍历,跳跃大 | 叶子链表顺序扫描,高效 |
| 查询稳定性 | 命中位置不同耗时不同 | 每次都到叶子,耗时稳定 |
6.2 哈希索引
哈希索引用哈希函数把键映射到桶,等值查询 。但它不支持范围查询(WHERE age > 20 无法用)、不支持排序、不支持最左前缀。MySQL 的 MEMORY 引擎支持显式哈希索引;InnoDB 有自适应哈希索引(Adaptive Hash Index),由引擎自动为热点页建立,用户无法直接控制。PostgreSQL 也提供 HASH 索引类型。
6.3 聚簇索引与非聚簇索引
| 对比项 | 聚簇索引 Clustered | 非聚簇索引 Secondary |
|---|---|---|
| 叶子节点内容 | 完整的行数据 | 索引列 + 主键值 |
| 数量 | 一张表只有一个 | 可以有多个 |
| InnoDB 中的体现 | 主键即聚簇索引 | 普通索引 |
| 查询流程 | 一次 B+ 树查找得到整行 | 先查二级索引拿到主键,再回表查聚簇索引 |
| PostgreSQL 中的体现 | 无(堆表存储,CLUSTER 是一次性重排) | 所有索引都是二级索引 |
InnoDB 的两条推论
1. 主键要短且单调递增:主键过大会让所有二级索引都变大(二级索引叶子存主键);随机主键(如 UUID)会导致页分裂与碎片。
2. 没有显式主键时,InnoDB 会选第一个非空唯一索引,都没有就隐式创建 6 字节 ROW_ID。
6.4 复合索引与最左前缀原则
复合索引 idx(a, b, c) 的排序规则是「先按 a 排,a 相同按 b 排,b 相同按 c 排」,因此只有从最左列开始的连续前缀才能用于定位:
| 查询条件 | 能否用 idx(a,b,c) |
|---|---|
WHERE a = 1 | ✅ 用 a |
WHERE a = 1 AND b = 2 | ✅ 用 a,b |
WHERE a = 1 AND b = 2 AND c = 3 | ✅ 全用 |
WHERE b = 2 | ❌ 跳过最左列 |
WHERE a = 1 AND c = 3 | ⚠️ 只用 a,c 无法用于定位(MySQL 8.0 有索引下推可减少回表) |
WHERE a = 1 AND b > 2 AND c = 3 | ⚠️ 范围查询后的列无法用于定位 |
WHERE a = 1 ORDER BY b | ✅ 索引本身有序,免排序 |
设计口诀:等值条件在前,范围条件在后,排序列放最后。
-- 覆盖索引(Covering Index):查询所需列全在索引里,无需回表
ALTER TABLE orders ADD KEY idx_user_status_time (user_id, status, created_at);
EXPLAIN SELECT user_id, status, created_at FROM orders
WHERE user_id = 100 AND status = 'PAID'; -- Extra: Using index
6.5 索引失效的典型场景
这些写法会让索引失效
```sql
-- 1. 对索引列做函数/运算(把列包起来)
SELECT * FROM t WHERE YEAR(created_at) = 2026; -- ❌
SELECT * FROM t WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01'; -- ✅ 改写成范围
-- 2. 隐式类型转换:phone 是 VARCHAR,传数字会让全表转成数字比较
SELECT * FROM t WHERE phone = 13800000000; -- ❌
SELECT * FROM t WHERE phone = '13800000000'; -- ✅
-- 3. 前导模糊匹配
SELECT * FROM t WHERE name LIKE '%张'; -- ❌ 无法定位起点
SELECT * FROM t WHERE name LIKE '张%'; -- ✅
-- 4. OR 连接非索引列
SELECT * FROM t WHERE a = 1 OR not_indexed = 2; -- ❌ 退化为全表扫描
-- 5. NOT / != / NOT IN 在低区分度列上(优化器认为全表更快)
SELECT * FROM t WHERE status != 'PAID'; -- ⚠️ 视区分度而定
-- 6. 使用 SELECT * 导致无法覆盖索引
SELECT * FROM t WHERE user_id = 1; -- 需回表
```
还有一类是统计信息过期导致优化器选错执行计划,
ANALYZE TABLE t;可刷新。
七、事务
7.1 ACID
| 特性 | 含义 | 实现手段 |
|---|---|---|
| 原子性 Atomicity | 事务内操作要么全成功要么全失败 | undo log 回滚 |
| 一致性 Consistency | 事务前后数据库满足所有约束 | 由 A、I、D 与应用共同保证 |
| 隔离性 Isolation | 并发事务互不干扰 | 锁 + MVCC |
| 持久性 Durability | 提交后即使断电也不丢 | redo log(WAL)+ 刷盘 |
-- 转账:原子性最典型的例子
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
7.2 并发异常
| 异常 | 现象 | 例子 |
|---|---|---|
| 脏读 Dirty Read | 读到别的事务未提交的数据,对方回滚后数据是假的 | A 改了余额未提交,B 读到新余额 |
| 不可重复读 Non-repeatable Read | 同一事务内两次读同一行结果不同 | A 第一次读余额 100,B 改成 200 并提交,A 再读得 200 |
| 幻读 Phantom Read | 同一事务内两次范围查询行数不同 | A 查「年龄>20」得 5 行,B 插入 1 行并提交,A 再查得 6 行 |
| 丢失更新 Lost Update | 两个事务基于旧值更新,后写覆盖先写 | 双方都读 100 再各自减 10,结果应是 80 却得 90 |
7.3 四种隔离级别对照
SQL 标准(SQL:1992)定义四级隔离,MySQL InnoDB 的默认级别是 REPEATABLE READ,PostgreSQL 的默认级别是 READ COMMITTED。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 实现方式(InnoDB) |
|---|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 | 不加读锁,直接读最新数据 |
| READ COMMITTED | 不可能 | 可能 | 可能 | 每条 SELECT 生成新 ReadView |
| REPEATABLE READ | 不可能 | 不可能 | InnoDB 基本不可能 | 事务首次 SELECT 生成 ReadView,配合间隙锁 |
| SERIALIZABLE | 不可能 | 不可能 | 不可能 | 读加共享锁,所有 SELECT 变 LOCK IN SHARE MODE |
InnoDB 的 REPEATABLE READ 为何能防幻读
标准只要求 RR 防不可重复读,但 InnoDB 通过 MVCC 快照读 + 间隙锁(Gap Lock) 在 RR 下也基本消除了幻读。但要注意:快照读(普通 SELECT) 靠 MVCC,当前读(
SELECT ... FOR UPDATE、UPDATE、DELETE) 靠间隙锁。两者混用时仍可能出现「先快照读、再当前读看到新行」的现象,这是 MySQL 特有而非标准行为。
-- 查看与设置隔离级别(MySQL 8.0)
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- PostgreSQL
SHOW transaction_isolation;
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
SERIALIZABLE 不是「性能差的 REPEATABLE READ」
标准 SERIALIZABLE 若真用两阶段锁实现,并发度极低。PostgreSQL 从 9.1 起用 SSI(Serializable Snapshot Isolation) 实现,靠检测危险结构而非全表加锁,性能损耗小得多。所以「PostgreSQL 的 SERIALIZABLE 比 MySQL 的好用」是有依据的说法。
八、锁与 MVCC
8.1 锁的分类
| 锁 | 英文 | 兼容性 | 用途 |
|---|---|---|---|
| 共享锁 | S Lock / 读锁 | S-S 兼容,S-X 互斥 | SELECT ... LOCK IN SHARE MODE |
| 排他锁 | X Lock / 写锁 | 与任何锁互斥 | SELECT ... FOR UPDATE、UPDATE |
| 意向共享锁 | IS | 表级,表明「打算在某些行加 S 锁」 | 快速判断表级冲突 |
| 意向排他锁 | IX | 表级,表明「打算在某些行加 X 锁」 | 同上 |
| 记录锁 | Record Lock | 锁单条索引记录 | 唯一索引等值命中 |
| 间隙锁 | Gap Lock | 锁索引记录之间的空隙 | RR 下防幻读 |
| 临键锁 | Next-Key Lock | 记录锁 + 间隙锁 | InnoDB RR 默认行锁形态 |
| 插入意向锁 | Insert Intention | 特殊的间隙锁,插入前加 | 提高插入并发 |
意向锁的意义:事务 A 要给某行加 X 锁,只需在表上加 IX;事务 B 想给整表加锁,只需检查表上有没有 IX/IS,不必逐行扫描是否被锁。
-- 会话 1
START TRANSACTION;
SELECT * FROM student WHERE id = 1 FOR UPDATE; -- 加 X 锁
-- 会话 2(会阻塞直到会话 1 提交)
START TRANSACTION;
SELECT * FROM student WHERE id = 1 FOR UPDATE; -- 等待
-- 查看锁等待情况(MySQL 8.0)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM sys.innodb_lock_waits; -- 更易读,含阻塞源线程
8.2 死锁
死锁是两个事务各持有对方需要的锁,循环等待。InnoDB 会自动检测并回滚「代价小」的那个事务,报错 Deadlock found when trying to get lock。
降低死锁概率的四条经验
1. 按固定顺序访问表与行(如始终按主键升序更新)。
2. 缩短事务,不要在事务里做 RPC、文件 IO、等用户输入。
3. 给 WHERE 条件加索引,避免扫描过程中锁住过多记录或升级为表锁。
4. 捕获死锁异常并重试,这是正常的工程手段而不是掩盖 bug。
```bash
# 查看最近一次死锁详情
SHOW ENGINE INNODB STATUS\G # 看 LATEST DETECTED DEADLOCK 段
```
8.3 MVCC 原理
MVCC(Multi-Version Concurrency Control,多版本并发控制) 让「读」不阻塞「写」,「写」也不阻塞「读」,代价是保留多版本数据。
InnoDB 的实现要点:
- 隐藏列:每行有
DB_TRX_ID(最后修改它的事务 ID)与DB_ROLL_PTR(指向 undo log 中旧版本的指针)。 - undo log 版本链:每次修改都把旧值写进 undo log,形成一条可回溯的版本链。
- ReadView:快照读时生成,包含
m_ids:生成时活跃(未提交)的事务 ID 集合;min_trx_id:其中最小者;max_trx_id:下一个将分配的事务 ID;creator_trx_id:创建该 ReadView 的事务 ID。
可见性判断规则(对版本链上某版本的 trx_id):
若 trx_id >= max_trx_id 或 trx_id ∈ m_ids,说明该版本由未提交的并发事务产生,沿着 DB_ROLL_PTR 找上一个版本继续判断。
RC 与 RR 的差别就在 ReadView 的生成时机:
| 隔离级别 | ReadView 生成时机 | 效果 |
|---|---|---|
| READ COMMITTED | 每条 SELECT 都重新生成 | 能看到已提交的最新数据 → 不可重复读 |
| REPEATABLE READ | 事务内第一条 SELECT 生成一次并复用 | 整个事务看到同一快照 |
九、查询优化
9.1 EXPLAIN 输出解读
EXPLAIN SELECT s.name, c.name
FROM student s JOIN class c ON s.class_id = c.id
WHERE s.age > 20 ORDER BY s.name LIMIT 10;
-- MySQL 8.0.18+ 可看真实执行代价;8.0.19+ 支持 EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT ...;
-- 传统格式化输出
EXPLAIN FORMAT=JSON SELECT ...;
| 列 | 含义 | 关注点 |
|---|---|---|
| id | 查询序号 | 越大越先执行;相同则从上到下 |
| select_type | 查询类型 | SIMPLE / PRIMARY / SUBQUERY / DERIVED / UNION |
| table | 涉及的表 | — |
| partitions | 命中分区 | 分区裁剪是否生效 |
| type | 访问类型 | 性能从好到差:system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能用的索引 | — |
| key | 实际用的索引 | 为 NULL 说明没走索引 |
| key_len | 使用索引的字节数 | 可推断用了复合索引的前几列 |
| ref | 与索引列比较的对象 | const / 列名 |
| rows | 预估扫描行数 | 越小越好 |
| filtered | 过滤后剩余比例 | 越低说明过滤效果越差 |
| Extra | 额外信息 | 见下表 |
Extra 常见值含义:
| Extra | 含义 | 好坏 |
|---|---|---|
Using index | 覆盖索引,无需回表 | 👍 好 |
Using where | 在 Server 层过滤 | 中性 |
Using index condition | 索引下推(ICP) | 👍 好 |
Using filesort | 需要额外排序 | ⚠️ 差,考虑加排序索引 |
Using temporary | 用了临时表(常见于 GROUP BY) | ⚠️ 差 |
Using join buffer | 被驱动表无索引,用了连接缓冲 | ❌ 很差 |
Impossible WHERE | 条件恒假 | 检查 SQL |
9.2 慢查询定位
-- 1. 开启慢查询日志(MySQL 8.0,动态生效,重启失效需写入配置文件)
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒记录
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未走索引的查询(生产慎用,日志量大)
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 2. 统计汇总(需安装 sys schema,MySQL 5.7+ 自带)
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
-- 3. 用 mysqldumpslow 或 pt-query-digest 分析日志文件
# 命令行分析慢日志(Percona Toolkit)
pt-query-digest /var/log/mysql/slow.log | head -50
# 内置工具:按总耗时排序取前 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
别在生产环境随便开 general_log
general_log会记录所有 SQL(含明文密码),且带来明显 IO 开销。排查问题用慢查询日志 +performance_schema。
9.3 常用优化手段
- 加对索引:优先覆盖 WHERE、JOIN ON、ORDER BY 涉及的列。
- **避免
SELECT ***:减少网络传输,也让覆盖索引有机会生效。 - 深分页优化:
LIMIT 1000000, 20会扫描并丢弃 100 万行,改用延迟关联或游标法:
-- ❌ 慢:扫描 1000020 行,丢弃前 1000000 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- ✅ 延迟关联:先在覆盖索引上翻页拿到主键,再回表取 20 行
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t ON t.id = o.id;
-- ✅ 游标法:记住上一页最后一条的 id(要求 id 单调)
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
- 拆分大事务:批量更新分批提交(如每 1000 行一提交),避免长事务撑大 undo 与锁等待。
- 减少 JOIN 表数:超过 3~4 张表的 JOIN 考虑拆查询或冗余字段。
十、备份与恢复
| 方式 | 工具 | 特点 | 适用场景 |
|---|---|---|---|
| 逻辑备份 | mysqldump、pg_dump | 输出 SQL 文本,跨版本可移植,慢 | 小库、跨环境迁移 |
| 物理备份 | Percona XtraBackup、pg_basebackup | 直接拷数据文件,快,需同版本 | 大库全量备份 |
| 增量备份 | binlog(MySQL)、WAL 归档(PostgreSQL) | 记录变更,可做时间点恢复 | PITR |
| 快照 | 云盘快照、LVM 快照 | 秒级,依赖存储层 | 云环境日常备份 |
# MySQL 逻辑备份(含建库语句、触发器、存储过程;--single-transaction 保证一致性快照不锁表)
mysqldump -u root -p --single-transaction --routines --triggers --events \
--databases shop > /backup/shop_$(date +%F).sql
# 恢复
mysql -u root -p < /backup/shop_2026-10-11.sql
# 时间点恢复:先恢复全量,再重放 binlog 到指定时间
mysqlbinlog --start-datetime="2026-10-11 00:00:00" \
--stop-datetime="2026-10-11 12:00:00" \
/var/lib/mysql/binlog.000123 | mysql -u root -p shop
# PostgreSQL
pg_dump -Fc -U postgres shop > /backup/shop.dump # 自定义格式,支持并行与选择性恢复
pg_restore -d shop_new -j 4 /backup/shop.dump # 4 个并行任务恢复
备份没有验证过就等于没有备份
必须定期做恢复演练:在隔离环境恢复一次,校验行数与关键业务表。另外
mysqldump默认不带--single-transaction时会加读锁阻塞写入,生产环境务必加上(InnoDB 才有效)。
十一、分库分表与读写分离
11.1 为什么要拆
单机 MySQL 的瓶颈通常来自三处:单表数据量过大(B+ 树变高、DDL 困难)、单库写入 QPS 上限、单机磁盘/内存上限。
| 方案 | 解决的问题 | 引入的复杂度 |
|---|---|---|
| 读写分离 | 读多写少,读 QPS 瓶颈 | 主从延迟导致读到旧数据 |
| 垂直分库 | 按业务拆库(用户库、订单库) | 跨库 JOIN 不可用,分布式事务 |
| 垂直分表 | 大字段/低频字段拆到扩展表 | 需要额外一次查询 |
| 水平分表 | 单表数据量过大 | 路由、跨片查询、全局 ID、扩容 |
| 水平分库 | 写入 QPS 瓶颈 | 同上 + 分布式事务 |
11.2 读写分离
应用 → 写请求 → 主库(Master) --binlog--> 从库(Slave1/2/3) ← 读请求 ← 应用
关键问题与对策:
- 主从延迟:写入后立刻读可能读不到。对策:① 强制走主库(如「刚下单后跳转订单详情」);② 用 GTID/位点判断从库是否追上;③ 关键业务用
SELECT ... FOR UPDATE走主库。 - 多从库负载:用中间件(ProxySQL、MyCat、ShardingSphere)或客户端 SDK 做负载均衡与故障转移。
-- 查看主从状态(MySQL 8.0.22+ 用 SHOW REPLICA STATUS)
SHOW REPLICA STATUS\G
-- 关注字段:Replica_IO_Running / Replica_SQL_Running 均为 Yes
-- Seconds_Behind_Source 表示延迟秒数
-- Retrieved_Gtid_Set 与 Executed_Gtid_Set 的差集表示未重放的事务
11.3 分片键与路由
分片键(Sharding Key)的选择决定了方案的成败:
| 策略 | 算法 | 优点 | 缺点 |
|---|---|---|---|
| 范围分片 | 按 id 区间 | 易扩容、范围查询高效 | 热点集中在新数据 |
| 哈希取模 | 数据均匀 | 扩容需重新分布( 变化几乎全量迁移) | |
| 一致性哈希 | 哈希环 + 虚拟节点 | 扩容只影响相邻节点 | 实现复杂,仍有少量迁移 |
| 预分片 | 先分 1024 片,再映射到物理库 | 扩容只改映射表 | 需提前规划 |
一致性哈希为什么能减少迁移
把节点和键都映射到 的环上,键归属顺时针第一个节点。增加一个节点只接管环上相邻区间,迁移量约为 ;而取模法改变 会导致约 的数据需要搬迁。
分片带来的必答题:
- 全局唯一 ID:不能用单库自增。方案有 Snowflake(时间戳+机器 ID+序列)、号段模式(Leaf)、UUID(无序,不推荐做主键)。
- 跨片查询:
ORDER BY create_time LIMIT 10需要在每个分片取 10 条再归并(内存归并)。 - 跨片分页与聚合:
COUNT(*)、SUM()需要各分片计算后二次汇总。 - 分布式事务:优先用最终一致性(本地消息表、事务消息);强一致可用 Seata 的 AT/TCC 模式,但性能损耗明显。
- 扩容:提前用成倍扩容(2 的幂次)或预分片,避免全量迁移。
-- 分片后「每片各取前 10,再在应用层归并」的思路
-- 分片 1
SELECT id, user_id, amount, created_at FROM orders_1 ORDER BY created_at DESC LIMIT 10;
-- 分片 2
SELECT id, user_id, amount, created_at FROM orders_2 ORDER BY created_at DESC LIMIT 10;
-- 应用层把两个结果集合并排序后取前 10
不要过早分库分表
单表千万行以内、QPS 几千的量级,靠索引优化 + 读写分离 + 缓存基本都能扛住。分库分表会让 JOIN、事务、分页、运维复杂度全面上升,应当在有明确指标(如单表超过 2000 万行、写入 QPS 逼近上限)时再做。
十二、注意事项与常见坑
坑一:
utf8不是真 UTF-8MySQL 的
utf8别名是utf8mb3,最多 3 字节,存不了 emoji 与部分生僻字。建库建表一律用utf8mb4与utf8mb4_0900_ai_ci(MySQL 8.0 默认排序规则)。以官方最新文档为准。
坑二:用
FLOAT/DOUBLE存金额浮点数有精度误差,
0.1 + 0.2 != 0.3。金额必须用DECIMAL(m, d),在 MySQL 中DECIMAL是精确存储的。
**坑三:
COUNT(*)、COUNT(1)、COUNT(col)结果可能不同**
COUNT(col)不统计 NULL,COUNT(*)与COUNT(1)统计所有行。业务统计人数时用错列会少算。
坑四:长事务拖垮数据库
长事务会:撑大 undo 表空间、让 MVCC 快照无法清理、长期持有锁。监控方法:
```sql
-- MySQL 8.0
SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS secs,
trx_rows_locked, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;
```
应用侧要确保事务里没有网络调用,并设置
innodb_lock_wait_timeout与语句超时。
坑五:
ALTER TABLE锁表MySQL 8.0 支持
ALGORITHM=INSTANT(多数加列场景)、INPLACE(重建表但不阻塞 DML,末尾仍需短暂元数据锁)。执行前用ALGORITHM=INPLACE, LOCK=NONE显式声明,失败则说明不支持,可改用 gh-ost / pt-online-schema-change。
坑六:把隔离级别当成万能的并发保护
隔离级别保证的是单行读写的语义,业务层面的「检查后写入」仍需显式加锁或唯一约束。例如「先查是否有余额再扣减」在 RC/RR 下都可能丢失更新,正确做法是:
```sql
-- 用条件更新让数据库来保证原子性(推荐)
UPDATE account SET balance = balance - 100
WHERE id = 1 AND balance >= 100; -- 受影响行数为 0 说明余额不足
```
十三、练习题
练习 1:范式改造
表
emp_project(emp_id, emp_name, dept, dept_phone, project_id, project_name, hours),业务规则:一个员工属于一个部门,一个部门一个电话;一个项目有唯一名称;员工与项目多对多并记录工时。请指出它违反了哪些范式,并给出 3NF 的拆表方案与建表语句。
参考思路:主键为
(emp_id, project_id)。emp_name, dept, dept_phone部分依赖于emp_id(违反 2NF);project_name部分依赖于project_id(违反 2NF);dept_phone经dept传递依赖于emp_id(违反 3NF)。拆为department(dept, dept_phone)、employee(emp_id, emp_name, dept)、project(project_id, project_name)、emp_project(emp_id, project_id, hours)。
练习 2:索引设计
有查询:
SELECT id, title, created_at FROM article WHERE author_id = 5 AND status = 'PUBLISHED' ORDER BY created_at DESC LIMIT 20;请设计一个复合索引,说明列顺序理由,并指出该查询在 EXPLAIN 中理想的
key与Extra是什么。参考思路:索引
(author_id, status, created_at)。等值列author_id、status在前,排序列created_at在后,这样索引本身有序可免排序;由于查询列id(二级索引自带主键)、title不在索引中,仍会回表,Extra为Using index condition或Using where。若把title也加入索引则成为覆盖索引,Extra显示Using index。
练习 3:隔离级别判断
会话 A 与 B 的隔离级别都是 READ COMMITTED。按时间顺序:A 开始事务 → A 查询
SELECT balance FROM account WHERE id=1得 100 → B 更新该行为 200 并提交 → A 再查一次。问:A 第二次读到多少?若隔离级别改为 REPEATABLE READ(MySQL InnoDB)呢?若 A 用的是
SELECT ... FOR UPDATE呢?参考思路:RC 下第二次读到 200(不可重复读);RR 下快照读仍读到 100;但
SELECT ... FOR UPDATE是当前读,会读最新已提交版本 200。
练习 4:EXPLAIN 诊断
某查询
EXPLAIN输出type=ALL、key=NULL、rows=1250000、Extra=Using where; Using filesort。请列出至少 3 种可能原因与验证方法。参考思路:① 条件列无索引或索引被函数包裹 → 检查
WHERE写法与SHOW INDEX FROM t;② 前导模糊匹配LIKE '%x'→ 改全文索引或前缀索引;③ 隐式类型转换(字符串列传数字)→ 统一参数类型;④OR连接了非索引列 → 拆成UNION;⑤ 统计信息过期 →ANALYZE TABLE。
练习 5:分片方案设计
一个日增 500 万行的订单表,预计 3 年数据量约 55 亿行。请给出分片键选择、分片数、全局 ID 方案,并说明「查询某用户最近 10 笔订单」与「按订单号查详情」两种访问路径如何路由。
参考思路:分片键选
user_id(绝大多数查询按用户);按 1024 片预分片映射到 16 个物理库;全局 ID 用 Snowflake 或号段模式并把user_id编码进订单号前缀;「用户最近订单」直接路由到hash(user_id) % 1024;「按订单号查」从订单号解出user_id再路由(基因法),避免全片扫描。
十四、常见问题 FAQ
Q1:索引越多越好吗?
不是。每个二级索引都会:占磁盘空间、拖慢 INSERT/UPDATE/DELETE(要同步维护索引)、可能让优化器选错计划。建议单表索引控制在 5~6 个以内,且优先为高频查询建复合索引而不是为每列建单列索引。
Q2:为什么给列加了索引还是慢?
按顺序排查:① 看
EXPLAIN的type与key是否真的用了索引;② 是否因函数、隐式转换、前导%导致失效;③ 是否回表次数太多(考虑覆盖索引);④ 是否rows很大说明区分度太低(如性别列建索引没意义);⑤ 是否锁等待或磁盘 IO 瓶颈,看SHOW ENGINE INNODB STATUS与慢日志。
Q3:REPEATABLE READ 下到底还有没有幻读?
在 MySQL InnoDB 的 RR 下,快照读通过 MVCC 保证不会看到新插入的行,当前读通过临键锁(Next-Key Lock)阻止其他事务在范围内插入。但同一事务里先快照读、再当前读时会看到新行,表现为「幻读」。因此严谨的说法是:InnoDB RR 在标准定义的层面消除了幻读,但在混合读模式下仍可能观察到。以官方最新文档为准。
Q4:主键用自增 BIGINT 还是 UUID?
单库场景推荐
BIGINT AUTO_INCREMENT:占用小、插入有序、页分裂少。分布式场景如果必须用 UUID,选 UUIDv7(含时间戳前缀,基本有序)而不是 UUIDv4(完全随机);更稳妥的做法是用 Snowflake/号段生成有序的 64 位 ID。InnoDB 中随机主键会让每个二级索引都变大并造成页分裂,这是实测可观测的性能差异。
相关笔记
- 00-索引:全库导航,「数据库」一节可找到上下游笔记。
- 操作系统:文件系统、页缓存、磁盘调度是数据库存储引擎的底层依赖。
- 数据结构-树与堆:理解 B+ 树之前先掌握二叉搜索树与平衡树的旋转逻辑。
- 数据结构-哈希表:哈希索引与一致性哈希分片都建立在这里。
- 后端-05-数据库操作与ORM:把本文的 SQL 与事务知识落到连接池、ORM 与 N+1 问题上。
- 后端-09-后端安全与性能:SQL 注入防御、缓存策略与数据库性能调优的工程实践。
- 工具-04-测试基础:数据访问层的单元测试与事务回滚测试。
- 学习路线图:不确定学习顺序时看这里。
参考来源
- MySQL 8.4 Reference Manual:https://dev.mysql.com/doc/refman/8.4/en/
- MySQL — InnoDB Locking(锁类型与临键锁):https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html
- MySQL — InnoDB Multi-Versioning(MVCC 与 ReadView):https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-versioning.html
- MySQL — EXPLAIN Output Format:https://dev.mysql.com/doc/refman/8.4/en/explain-output.html
- MySQL — Optimization Overview:https://dev.mysql.com/doc/refman/8.4/en/optimization.html
- MySQL — Backup and Recovery:https://dev.mysql.com/doc/refman/8.4/en/backup-and-recovery.html
- PostgreSQL Documentation:https://www.postgresql.org/docs/current/
- PostgreSQL — Transaction Isolation:https://www.postgresql.org/docs/current/transaction-iso.html
- PostgreSQL — Indexes:https://www.postgresql.org/docs/current/indexes.html
- PostgreSQL — Backup and Restore(pg_dump / pg_restore):https://www.postgresql.org/docs/current/backup-dump.html
- Redis 官方文档(缓存与共享存储常与数据库配合):https://redis.io/docs/latest/
- SQLite — SQL Syntax(SQL 语法速查):https://www.sqlite.org/lang.html
版本敏感提示
隔离级别的默认值、
ALTER TABLE支持的算法、SHOW REPLICA STATUS等语句名称随 MySQL 版本变化;PostgreSQL 的 SSI 行为也在版本间有调整。本文示例基于写作时的常见版本,具体以官方最新文档为准。