w wyl123ly 的博客
MD

数据库原理

41 KB 2026.10.11 原文件

数据库原理

这篇笔记讲什么

从「为什么不用文件存数据」讲起,覆盖关系模型与 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表 Tablestudent
元组 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)按用途分五类:

分类全称作用代表语句能否回滚
DDLData Definition Language定义结构CREATE / ALTER / DROP / TRUNCATE多数数据库隐式提交,不可回滚
DMLData Manipulation Language增删改数据INSERT / UPDATE / DELETE / MERGE可回滚
DQLData Query Language查询SELECT不涉及
DCLData Control Language权限GRANT / REVOKE视数据库而定
TCLTransaction 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) → namename 只依赖 sno,是部分依赖
(sno, course) → creditcredit 只依赖 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) 是一种多路平衡查找树,特点是:

  1. 所有数据都在叶子节点,内部节点只存键用于导航。
  2. 叶子节点用双向链表串联,天然支持范围查询与排序。
  3. 树高很低: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 的实现要点:

  1. 隐藏列:每行有 DB_TRX_ID(最后修改它的事务 ID)与 DB_ROLL_PTR(指向 undo log 中旧版本的指针)。
  2. undo log 版本链:每次修改都把旧值写进 undo log,形成一条可回溯的版本链。
  3. 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 常用优化手段

  1. 加对索引:优先覆盖 WHERE、JOIN ON、ORDER BY 涉及的列。
  2. **避免 SELECT ***:减少网络传输,也让覆盖索引有机会生效。
  3. 深分页优化: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;
  1. 拆分大事务:批量更新分批提交(如每 1000 行一提交),避免长事务撑大 undo 与锁等待。
  2. 减少 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 片,再映射到物理库扩容只改映射表需提前规划

一致性哈希为什么能减少迁移

把节点和键都映射到 的环上,键归属顺时针第一个节点。增加一个节点只接管环上相邻区间,迁移量约为 ;而取模法改变 会导致约 的数据需要搬迁。

分片带来的必答题:

  1. 全局唯一 ID:不能用单库自增。方案有 Snowflake(时间戳+机器 ID+序列)、号段模式(Leaf)、UUID(无序,不推荐做主键)。
  2. 跨片查询:ORDER BY create_time LIMIT 10 需要在每个分片取 10 条再归并(内存归并)。
  3. 跨片分页与聚合:COUNT(*)、SUM() 需要各分片计算后二次汇总。
  4. 分布式事务:优先用最终一致性(本地消息表、事务消息);强一致可用 Seata 的 AT/TCC 模式,但性能损耗明显。
  5. 扩容:提前用成倍扩容(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-8

MySQL 的 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 行为也在版本间有调整。本文示例基于写作时的常见版本,具体以官方最新文档为准。