数据库原理

这一课你将能做到

1. 用关系模型语言描述表、主键、外键、视图，并说出范式化与反范式化的取舍
2. 解释 ACID 四种特性与四个隔离级别，区分脏读、不可重复读、幻读
3. 说清 B+ 树索引的结构、聚簇与二级索引的区别，以及最左前缀原则

为什么要学

数据库是后端持久化的核心。判题平台的用户、题目、提交记录、判题结果全部存放在 MySQL 这类关系数据库里。一个后端工程师每天最多的性能问题往往不在 Java 代码，而在 SQL：

• 列表接口慢：没走索引还是索引失效？
• 两个用户同时报名/扣减名额：数据对不上怎么办？
• 表字段越加越多，JOIN 越写越乱：数据模型当初该怎么设计？

本课不背 SQL 语法，而是建立四组底层认知：关系模型、事务、索引、架构直觉。理解它们之后，MySQL 的大部分行为都能推导，而不是靠死记规则。

先建立图像

关系模型：一切皆表（关系）
┌──────────────────── 表 problems 题目表 ───────────────────┐
│ id(PK) │ title    │ difficulty │ created_at              │
│ 1      │ 两数之和  │ easy       │ 2026-01-01              │
│ 2      │ 反转链表  │ easy       │ 2026-01-02              │
└─────────────────────────────────────────────────────────┘
       id 作为主键被 submissions 表的外键引用（一对多）

事务并发的三种「读异常」
  脏读：     读到别人还没提交、随时可能回滚的数据
  不可重复读：同一事务内两次读同一行，结果不同（别人 UPDATE 了）
  幻读：     同一事务内两次范围查询，行数不同（别人 INSERT/DELETE 了）

索引结构（B+ 树，矮胖的多叉平衡树）
          [ 30 | 60 ]
         /      |       \
   [10|20]  [30|40|50] [60|70|80]   ← 真实数据都在叶子节点
   叶子之间首尾相连 ──▶ 支持范围扫描

跟着做

步骤 1：设计判题平台的核心表并体会范式

-- 第一范式：字段原子；主键唯一
CREATE TABLE problems (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    title       VARCHAR(200) NOT NULL,
    difficulty  VARCHAR(16)  NOT NULL,
    time_limit  INT          NOT NULL DEFAULT 1000,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 提交记录单独成表，通过外键（或逻辑外键）引用题目 —— 第二/三范式的直觉：
-- 不把题目标题冗余进提交表，避免题目改名后成千上万行数据不一致
CREATE TABLE submissions (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    problem_id  BIGINT NOT NULL,
    user_id     BIGINT NOT NULL,
    language    VARCHAR(16) NOT NULL,
    status      VARCHAR(16) NOT NULL,   -- AC / WA / TLE / RE
    code        MEDIUMTEXT  NOT NULL,
    created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_time (user_id, created_at),
    INDEX idx_problem_status (problem_id, status)
);

范式直觉：1NF 原子、2NF 完全依赖主键、3NF 非主属性不传递依赖。范式化减少冗余和更新异常；反范式化（冗余字段）用空间换查询性能、减少 JOIN。工程现实：OLTP 业务表保持 3NF 左右，列表页可适当冗余（如 submissions 里冗余 title 快照，因为提交时刻的题目具有「存档」语义）；报表/数仓则大量反范式。

步骤 2：在 Java 中正确使用事务

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class SubmissionService {

    private final SubmissionRepository submissionRepository;
    private final ProblemStatRepository statRepository;

    public SubmissionService(SubmissionRepository submissionRepository,
                             ProblemStatRepository statRepository) {
        this.submissionRepository = submissionRepository;
        this.statRepository = statRepository;
    }

    // ACID 场景：写提交记录 + 更新题目统计，要么都成功要么都回滚
    @Transactional
    public void submitAndCount(Submission submission) {
        submissionRepository.save(submission);
        if ("AC".equals(submission.status())) {
            statRepository.incrementAc(submission.problemId());
        }
        // 抛出运行时异常时整个事务回滚，不会出现「记录写了但统计没加」
    }
}

record Submission(Long problemId, Long userId, String language, String status, String code) {}

ACID 逐个看：原子性 Atomicity（事务不可分割，靠 undo log 回滚）、一致性 Consistency（事务前后数据库满足约束，是目的而非手段）、隔离性 Isolation（并发事务互不干扰的程度）、持久性 Durability（提交后掉电不丢，靠 redo log 落盘）。

步骤 3：隔离级别与读异常

隔离级别          脏读   不可重复读  幻读
读未提交 RU        可能    可能        可能
读已提交 RC(Oracle) 避免   可能        可能       ← 多数互联网公司常用
可重复读 RR(MySQL)  避免   避免        基本避免*   ← MySQL InnoDB 默认
串行化 Serializable 避免   避免        避免       ← 最强也最慢

*InnoDB 在 RR 下用 next-key lock（记录锁+间隙锁）在很大程度上防止幻读

理解方式：隔离级别是「一致性 vs 并发性」的旋钮，拧得越紧越正确但等待越多。MySQL 默认 RR 配合间隙锁；大量公司改成 RC 以获得更好并发，配合行锁与应用层校验。面试中要能说出三种现象的区别：脏读针对「未提交数据」，不可重复读针对「同行被 UPDATE」，幻读针对「范围行数变化」。

步骤 4：用 EXPLAIN 验证索引是否生效

-- 联合索引 idx_user_time(user_id, created_at)
EXPLAIN SELECT * FROM submissions
        WHERE user_id = 1001
        ORDER BY created_at DESC
        LIMIT 20;
-- 关注：key=idx_user_time（走了哪个索引）、type=ref/range、rows（预估扫描行数）

-- 最左前缀：索引按 (user_id, created_at) 排序
-- ✅ WHERE user_id=?                 用到第 1 列
-- ✅ WHERE user_id=? AND created_at>? 两列都用
-- ❌ WHERE created_at>?              跳过最左列，用不上（无法定位 user_id）
-- ❌ WHERE user_id=? AND score>90    第 2 列不是索引列，排序用不上

B+ 树为什么适合做索引：矮胖（百万数据通常 3-4 层，一次查询 3-4 次磁盘 I/O）、数据全在叶子节点且叶子链表相连，范围查询顺着叶子扫即可。聚簇索引（InnoDB 主键索引）叶子节点直接存整行数据；二级索引叶子存主键值，查到后需要「回表」再查聚簇索引——覆盖索引（查询列都在索引里）能避免回表，是常用优化手段。

讲清楚要点

1. 关系模型 = 表（关系）+ 元组（行）+ 属性（列），表与表通过键关联；关系代数（选择、投影、连接）是 SQL 的理论基础，理解它有助于读懂执行计划。
2. 外键约束的争议：它保证引用完整性，但在高并发、分库分表场景带来性能与灵活性负担，互联网系统常用应用层逻辑保证，小规模内部系统则推荐使用。
3. 锁的直觉：行锁锁索引记录（没索引会升级为锁大量行甚至表锁）；写写互斥，读写在 MVCC 下可并发（读快照、写最新版本），这就是 RC/RR 下读不阻塞写的机制。
4. 索引不是越多越好：每个索引占空间且让 INSERT/UPDATE/DELETE 多维护一棵树；优先为高频 WHERE、JOIN、ORDER BY 建联合索引，区分度低的列（如 status）单独建索引意义不大。
5. CAP 的正确直觉：分布式下一致性（C）、可用性（A）、分区容忍（P）不能同时完美，网络分区必然存在，所以实际是 CP 或 AP 的取舍；它和单机 ACID 不是一个层面的概念，不要混用。

你可能会懵的地方

• 为什么 MySQL 用 B+ 树而不用红黑树或哈希表？ 红黑树是二叉树，百万数据树高约 20，意味着 20 次磁盘 I/O；B+ 树多叉、3-4 层即可。哈希索引 O(1) 快但不支持范围查询和排序（Memory 引擎、InnoDB 自适应哈希是补充）。B+ 树的叶子链表是为范围扫描量身设计的。

• 可重复读级别下两次读为什么能一样？ MVCC 多版本并发控制：每行有多个版本，事务启动时建立一致性视图（read view），只看视图之前已提交的版本，别人之后的提交对它不可见。所以同事务内重读结果稳定。

• @Transactional 为什么有时候不生效？ 常见原因：方法不是 public、同类内部自调用（绕过代理）、异常被 catch 没抛出来、抛出的是受检异常而默认只回滚运行时异常、数据库引擎不支持事务（MyISAM）。写事务代码时逐条排查。

• 范式越高越好吗？ 不是。范式高 → 冗余少但查询要 JOIN 多、写入分散；为了性能与查询简单，适度反范式是常规操作。范式是设计起点而非宗教，关键看读写比例。

• 分库分表后 ID 怎么办？ 自增主键在分片后冲突，需要全局唯一 ID：雪花算法（时间+机器+序列）、号段模式（数据库批量取号）、UUID（无序导致索引页分裂，不推荐做主键）。这也呼应了第 5 课的数论拼接。

错法对照

错法	问题	正解
字符串拼 SQL 接参数	SQL 注入 + 无法利用预编译缓存	参数化查询 / MyBatis #{}
索引列上用函数 WHERE DATE(created_at)=...	索引失效，全表扫描	改为范围条件 created_at >= ... AND < ...
用 % 开头的 LIKE	B+ 树无法定位前缀	前缀匹配；全文检索上 ES
大事务包住远程调用	连接与行锁长时间占用	事务只包数据库操作，远程调用移出
offset 翻到百万页	跳过的行都要扫描，深分页慢	游标/seek 分页：WHERE id < 上次最大 id

卡点急救

1. 慢 SQL 排查三步：开慢查询日志定位 → EXPLAIN 看 type、key、rows、Extra → 针对性加索引/改写法；避免一上来就「加 Redis」掩盖问题。
2. 死锁报错：SHOW ENGINE INNODB STATUS 看死锁日志；统一多表更新顺序、缩小事务、降低隔离级别或用乐观锁（版本号 CAS）。
3. 连接池打满：检查是否有慢查询和长事务占着连接；设合理超时（connection/query timeout），监控活跃连接数而不是只看池大小。

面试常问

• 讲一下 ACID，以及 InnoDB 怎么实现隔离性和持久性？ 原子性靠 undo log 回滚，持久性靠 redo log（WAL，先写日志再刷数据页），隔离性靠锁 + MVCC，一致性是三者共同保证的结果。可以补充两阶段提交保证 redo 与 binlog 一致。

• 为什么索引要用 B+ 树？什么是回表和覆盖索引？ B+ 树矮胖、磁盘 I/O 少、叶子链表支持范围扫描。二级索引叶子存主键，查其他列需回表；查询所需列全部包含在索引中即覆盖索引，可避免回表。

• 脏读、不可重复读、幻读的区别？如何解决幻读？ 分别对应读到未提交数据、同行被修改、范围行数变化。InnoDB RR 用 next-key lock（间隙锁）防止插入，或在业务层用串行化校验、唯一约束兜底。

自测三问

1. 画出 InnoDB 里一条「按二级索引查询并回表」的完整路径。
2. 判题平台把提交记录从 100 万涨到 10 亿，索引、分页、事务各要怎么调整？
3. RC 和 RR 各会出现什么读异常？为什么很多公司主动把 MySQL 改成 RC？

小结一句话

关系数据库用「表 + 键」规整数据、用 ACID 事务保证可靠、用 B+ 树索引把查找压到对数级——三者协同，才让海量数据上的并发读写既安全又足够快。

延伸

• 《数据库系统概念》（Silberschatz）配套资源——关系模型、事务与存储引擎理论的权威教材
• MySQL 8.0 官方参考手册——InnoDB、隔离级别与 EXPLAIN 的一手定义
• 《Designing Data-Intensive Applications》（DDIA）主页——现代数据系统与分布式取舍必读
• 下一课：计算机网络——数据有了可靠存储，接下来学习它如何在机器之间流动：OSI 分层、TCP 握手与 TLS 加密。