♥数据库基础和SQL知识体系详解♥
♥数据库基础和SQL知识体系详解♥
本系列从「关系模型 → 存储与索引 → 事务与并发 → 日志与恢复」这条主线出发，梳理数据库原理的公共底座，再向 SQL 语言、MySQL 实现等方向展开。本文作为
db区的入口，先给出整体地图与核心结论，细节再按需下钻到各专题。
1. 为什么要先学原理
会用 SELECT 和能写高效的 SELECT 之间，隔着一整套数据库内部机制。只背语法的人，遇到下面这些现象往往只能靠猜：
- 同样的查询，加了索引快十倍，而换个字段顺序又慢下来 —— 索引是有序结构，比的是列的顺序，不是列的多少。
- 一个长事务卡住，其他更新全部排队 —— 写操作持有锁，直到事务结束才释放。
- 主从复制下「写完马上读」读到旧值 —— 复制默认是异步的，一致性要单独权衡。
- 断电重启后，已提交的事务没丢、未提交的自动回滚 —— 日志（WAL）先于数据落盘。
把这些机制串起来，就是本文的四块核心：索引、事务、并发控制、日志与恢复。它们不是孤立知识点，而是共同支撑「数据既快又对」这一个目标。
2. 关系模型与 SQL 的分工
关系模型把数据抽象成二维表（关系）：行是元组，列是属性，每个属性的取值范围是域；表与表通过主键、外键建立联系。SQL 是操作关系模型的声明式语言——它只描述「要什么」，不规定「怎么取」，「怎么取」交由查询优化器 + 执行引擎决定。
这构成了数据库的清晰分层：声明式接口（SQL）→ 逻辑层（关系代数、范式）→ 物理层（存储、索引、执行算子）。判断一个问题出在哪一层，决定了你是改 SQL、加索引，还是要换存储引擎。
设计层面的函数依赖、候选键与范式（1NF/2NF/3NF）见 关系型数据库设计理论、关系型数据库设计流程；数据库整体如何运转，可先读 关系型数据库是如何工作的。
3. 存储与索引：为什么是 B+ 树
数据以页（page）为单位在磁盘与内存之间搬运，InnoDB 默认页大小是 16KB。一次磁盘 I/O 取一整页，因此减少 I/O 次数的关键，是让一次 I/O 带回尽可能多的有用数据，这正是 B+ 树的强项：
- 矮胖多叉：一个节点存很多键，树高很低。B+ 树通常 3~4 层即可索引上千万行，查一行最多几次 I/O（根与中间层常驻内存）。
- 叶子有序且互链：叶子按主键顺序排列并组成双向链表，范围查询、
BETWEEN、ORDER BY都是顺序扫描，而非随机跳转。 - 数据只在叶子层：非叶节点只存键与指针，索引体积小，缓存命中率高。
对比 B 树（数据可落在任意节点，范围遍历需回溯）与哈希索引（等值查询 O(1)，但不支持范围与排序），B+ 树是关系库在点查、范围、排序之间的通用最优解。
InnoDB 里主键索引即数据本身（聚簇索引），二级索引只存「索引列 + 主键值」，回表就是拿主键再查一次聚簇索引。索引细节见 MySQL 索引与 B+ 树。
4. 事务：ACID 靠什么实现
事务是一组操作全做或全不做。四个特性（ACID）各有对应手段：
| 特性 | 含义 | 主要实现 |
|---|---|---|
| 原子性 Atomicity | 要么全成功，要么全回滚 | undo log（记反向操作，回滚时重放） |
| 一致性 Consistency | 数据始终满足约束与业务规则 | 其他三个特性 + 应用约束共同保证 |
| 隔离性 Isolation | 并发事务按隔离级别互不干扰 | 锁 + MVCC |
| 持久性 Durability | 提交后即使崩溃也不丢 | redo log（WAL） |
注意「一致性」是目的而非手段：数据库只能保证约束（主键、外键、非空）层面的一致性，业务语义上的一致性仍要靠应用与事务边界设计。
5. 并发控制：隔离级别、锁与 MVCC
并发事务会带来三类「读异常」：
- 脏读：读到了别的事务尚未提交、可能回滚的数据。
- 不可重复读：同一事务内两次读同一行结果不同（别的事务改了并提交）。
- 幻读：同一事务内两次范围查询，第二次多出了「幻影行」。
SQL 标准用隔离级别在一致性与并发度之间取舍：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 Read Uncommitted | 可能 | 可能 | 可能 |
| 读已提交 Read Committed | 否 | 可能 | 可能 |
| 可重复读 Repeatable Read | 否 | 否 | 标准下可能（InnoDB 基本避免） |
| 串行化 Serializable | 否 | 否 | 否 |
实现手段有两条路：
- 基于锁（两阶段封锁 2PL）：读加共享锁、写加排它锁，事务结束才统一释放。粒度可为表级或行级；InnoDB 的行锁并不直接锁行，而是锁索引项，因此「没有索引的更新」会退化成锁大量记录甚至接近全表。
- 多版本并发控制（MVCC）：读操作读某个历史版本（快照），不阻塞写；写操作产生新版本，旧版本留给仍需要它的读事务。它用「版本链 + 一致性读」把读写解耦，显著提升并发。InnoDB 靠隐藏的事务版本列、undo 版本链与 Read View 判定可见性，详见 MySQL MVCC 与事务。
幻读在 InnoDB 的可重复读下，额外用间隙锁（Gap Lock）+ 临键锁（Next-Key Lock）封锁「记录及其之间的间隙」，阻止别的事务在范围内插入新行。代价是锁范围更大、更容易死锁。
6. 日志与恢复：WAL 是一切的地基
数据库要保证「提交即持久」，但每次提交都同步刷数据页代价极高。主流做法是预写日志（Write-Ahead Logging, WAL）：先把「要做的修改」顺序写入日志并落盘，数据页之后异步刷；崩溃后按日志重做（redo）或撤销（undo）。
- redo log（重做日志）：循环写，记录「某页做了什么物理修改」。崩溃恢复时重放已提交的修改，保证持久性。
- undo log（回滚日志）：记录逻辑上的反向操作，用于事务回滚与 MVCC 读旧版本。
- 检查点（checkpoint）：定期把脏页刷盘并记录「截至此处都已落盘」，缩短崩溃恢复时需重放的范围。
- 两阶段提交（2PC）：redo log 与 binlog 必须一致，否则恢复/主从会不一致；InnoDB 用「写 redo 的 prepare 阶段 → 写 binlog → 提交 redo」保证二者原子。
一句话：先写日志、后写数据、定期检查点。这条规则解释了为什么数据库敢承诺「提交了就不丢」，也解释了为什么大批量写入要盯住日志空间与刷盘策略。
7. 本组文档导航
- 数据库如何工作：关系型数据库是如何工作的
- 设计理论（术语、函数依赖、范式）：关系型数据库设计理论
- 设计流程（六个阶段）：关系型数据库设计流程
- 系统核心知识点（事务、封锁、隔离、MVCC）：数据库系统核心知识点
- 落到 MySQL：MySQL 知识体系
8. 小结
- 数据库分三层：声明式 SQL → 关系模型 → 物理存储；遇到问题先判断出在哪一层。
- B+ 树凭「矮胖 + 叶子有序 + 数据在叶层」同时满足点查、范围与排序，是索引的地基。
- ACID 各有实现：undo 保原子、redo 保持久、锁与 MVCC 保隔离、约束与应用保一致。
- 隔离级别是取舍；InnoDB 的读写并发靠 MVCC，幻读额外靠间隙锁，且行锁实际锁的是索引项。
- WAL（先日志后数据）+ checkpoint，是崩溃恢复与「提交即持久」的核心机制。