主题
数据库设计
本文系统梳理数据库设计中的核心知识体系，涵盖范式理论、ER 建模、索引原理、事务与锁机制、慢查询优化、分库分表等关键主题，并通过一个完整的博客系统实战案例将理论串联起来。
一、数据库设计范式
1.1 第一范式（1NF）—— 原子性
第一范式要求关系中每个属性都是不可再分的原子值。简单来说，表中的每一列都只存储单一类型的数据，不允许嵌套表或重复组。
反例 —— 违反 1NF：
+----+--------+----------------------+
| id | name | phones |
+----+--------+----------------------+
| 1 | 张三 | 13800001111,13900002222 |
| 2 | 李四 | 15000003333 |
+----+--------+----------------------+phones 列存储了多个电话号码，不满足原子性。
修正 —— 满足 1NF：
+----+--------+-------------+
| id | name | phone |
+----+--------+-------------+
| 1 | 张三 | 13800001111 |
| 1 | 张三 | 13900002222 |
| 2 | 李四 | 15000003333 |
+----+--------+-------------+或者更好的做法是拆分为两张表：
sql
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
CREATE TABLE user_phones (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
phone VARCHAR(20) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);1.2 第二范式（2NF）—— 消除部分依赖
在满足 1NF 的基础上，2NF 要求非主属性必须完全依赖于候选键，不能只依赖候选键的一部分。这一规则主要针对联合主键的场景。
反例 —— 违反 2NF：
订单明细表（联合主键: order_id + product_id）
+----------+------------+-----------+----------+---------------+
| order_id | product_id | quantity | price | product_name |
+----------+------------+-----------+----------+---------------+
| 1001 | 201 | 2 | 59.90 | Node.js实战 |
| 1001 | 202 | 1 | 89.00 | MySQL高性能 |
| 1002 | 201 | 3 | 59.90 | Node.js实战 |
+----------+------------+-----------+----------+---------------+product_name 只依赖于 product_id，不依赖完整的联合主键，产生部分依赖。
函数依赖关系:
(order_id, product_id) → quantity ✅ 完全依赖
(order_id, product_id) → price ✅ 完全依赖
product_id → product_name ❌ 部分依赖 (只依赖联合主键的一部分)修正 —— 满足 2NF：
sql
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);1.3 第三范式（3NF）—— 消除传递依赖
在满足 2NF 的基础上，3NF 要求非主属性不能传递依赖于候选键。即非主属性之间不能存在依赖关系。
反例 —— 违反 3NF：
+----------+-----------+-------------+---------------+
| staff_id | name | dept_id | dept_name |
+----------+-----------+-------------+---------------+
| 1 | 王五 | 101 | 技术部 |
| 2 | 赵六 | 102 | 产品部 |
| 3 | 孙七 | 101 | 技术部 |
+----------+-----------+-------------+---------------+dept_name 依赖于 dept_id，而 dept_id 依赖于 staff_id，产生传递依赖。
传递依赖链:
staff_id → dept_id → dept_name
staff_id 直接决定 dept_id
dept_id 直接决定 dept_name
因此 staff_id 传递决定 dept_name ← 违反 3NF修正 —— 满足 3NF：
sql
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE staff (
staff_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INT NOT NULL,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);1.4 BCNF（Boyce-Codd 范式）
BCNF 是 3NF 的加强版，要求每一个决定因素都必须是候选键。3NF 只要求非主属性不传递依赖于候选键，但没有约束主属性之间的依赖。BCNF 弥补了这一缺口。
BCNF 与 3NF 的区别：
3NF 的要求: 非主属性 不能传递依赖 候选键
BCNF 的要求: 所有属性（包括主属性）的决定因素都必须是候选键
BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF (BCNF 比 3NF 更严格)违反 BCNF 但满足 3NF 的经典案例：
考虑一个"学生选课"场景：一个学生可以选多门课，每门课可以有多个老师教，但每个老师只教一门课。
学生选课表（候选键: {学生, 课程} 或 {学生, 教师}）
+--------+--------+--------+
| 学生 | 课程 | 教师 |
+--------+--------+--------+
| 张三 | 数据库 | 李老师 |
| 张三 | 编译原理| 王老师 |
| 李四 | 数据库 | 赵老师 |
+--------+--------+--------+
函数依赖:
{学生, 课程} → 教师 (一个学生的一门课对应一个老师)
教师 → 课程 (一个老师只教一门课)
问题: "教师 → 课程" 中，教师不是候选键，违反 BCNF修正 —— 满足 BCNF：
sql
CREATE TABLE teacher_courses (
teacher_id INT PRIMARY KEY,
course_id INT NOT NULL,
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
CREATE TABLE student_teachers (
student_id INT NOT NULL,
teacher_id INT NOT NULL,
PRIMARY KEY (student_id, teacher_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (teacher_id) REFERENCES teacher_courses(teacher_id)
);1.5 范式等级对比
| 范式 | 核心要求 | 消除的问题 | 典型场景 |
|---|---|---|---|
| 1NF | 属性原子性 | 重复组、多值字段 | 所有关系表必须满足 |
| 2NF | 消除部分依赖 | 联合主键下的冗余 | 多对多关联表 |
| 3NF | 消除传递依赖 | 非主属性间冗余 | 主表设计 |
| BCNF | 决定因素必须是候选键 | 主属性对非候选键的依赖 | 严格要求场景 |
范式层级关系:
1NF ──► 2NF ──► 3NF ──► BCNF
│ │ │ │
│ │ │ └── 所有决定因素都是候选键
│ │ └────────── 消除非主属性的传递依赖
│ └────────────────── 消除非主属性的部分依赖
└────────────────────────── 属性不可再分1.6 反范式化 —— 适度冗余换性能
严格遵循范式会带来大量 JOIN 操作，在高并发读场景下可能成为性能瓶颈。此时可以有意引入冗余来减少查询复杂度。
适合反范式化的场景：
| 场景 | 做法 | 收益 |
|---|---|---|
| 高频读、低频写 | 在订单表中冗余商品名称 | 避免 JOIN 商品表 |
| 统计/报表查询 | 预计算汇总字段 | 避免实时聚合运算 |
| 分布式环境跨库 JOIN 困难 | 冗余关联字段 | 减少跨库查询 |
| 展示类字段高频访问 | 冗余用户昵称/头像到评论表 | 减少关联查询 |
权衡原则：
严格范式 反范式化
◄─────────────────────────────────────────────►
数据一致性高 查询性能高
存储空间小 存储空间大
更新简单 更新复杂（需同步冗余字段）
查询需多表 JOIN 单表即可完成查询范式化 vs 反范式化决策流程：
┌─────────────────┐
│ 新表/新字段设计 │
└────────┬────────┘
│
┌────────▼────────┐
│ 数据写入频繁？ │
└───┬─────────┬───┘
是│ │否
▼ ▼
┌──────────┐ ┌──────────────┐
│ 优先范式化 │ │ 查询是否需要 │
│ 减少写放大 │ │ 多表 JOIN？ │
└──────────┘ └──┬────────┬──┘
是│ │否
▼ ▼
┌────────────┐ ┌──────────┐
│ 考虑反范式化│ │ 维持范式化 │
│ 冗余高频字段│ └──────────┘
└────────────┘反范式化示例：
sql
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
user_name VARCHAR(50) NOT NULL,
product_id INT NOT NULL,
product_name VARCHAR(100) NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);user_name 和 product_name 是冗余字段，避免了查询订单列表时的 JOIN 操作。但要注意在用户修改名称或商品修改名称时，需要同步更新订单表中的冗余数据（或接受历史快照不变的策略）。
二、ER 模型设计
2.1 基本概念
ER（Entity-Relationship）模型由三个核心元素组成：
┌─────────────────────────────────────────────────────┐
│ ER 模型三要素 │
├─────────────────────────────────────────────────────┤
│ │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ 实体 │ │ 属性 │ │ 关系 │ │
│ │ (Entity) │ │(Attribute)│ │(Relation) │ │
│ │ 矩形 │ │ 椭圆 │ │ 菱形 │ │
│ └──────────┘ └──────────┘ └──────────┘ │
│ │
│ 现实世界中 实体的特征 实体之间的 │
│ 可区分的对象 或性质 联系 │
└─────────────────────────────────────────────────────┘2.2 三种关系类型
一对一（1:1）
一个实体恰好关联另一个实体的一条记录，如"用户"与"用户详情"。
┌──────────┐ 1 1 ┌──────────────┐
│ User │─────────────────│ UserProfile │
│ │ has │ │
│ user_id │ │ profile_id │
│ name │ │ user_id (FK) │
│ email │ │ avatar │
└──────────┘ │ bio │
└──────────────┘sql
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE user_profiles (
profile_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL UNIQUE,
avatar VARCHAR(255),
bio TEXT,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);1:1 关系的设计考量： 为什么不直接将 avatar、bio 放到 users 表中？常见理由包括：垂直分表减少单行数据量（提高热数据查询效率）、不同的访问频率（基本信息高频、详情低频）、以及不同的安全等级。
一对多（1:N）
一个实体关联多条另一个实体的记录，如"部门"与"员工"。
┌──────────────┐ 1 N ┌──────────────┐
│ Department │──────────────────│ Employee │
│ │ contains │ │
│ dept_id │ │ emp_id │
│ dept_name │ │ emp_name │
└──────────────┘ │ dept_id (FK) │
└──────────────┘sql
CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
dept_id INT NOT NULL,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);多对多（M:N）
两个实体之间互相关联多条记录，如"学生"与"课程"。需要通过**中间表（JOIN 表）**来实现。
┌──────────┐ M N ┌──────────┐
│ Student │────────┐ ┌──────────│ Course │
│ │ │ │ │ │
│ stu_id │ ┌───┴────┴───┐ │ course_id│
│ stu_name │ │stu_course │ │ title │
└──────────┘ │ │ │ credits │
│ stu_id (FK) │ └──────────┘
│ course_id(FK)│
│ score │
└─────────────┘sql
CREATE TABLE students (
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(50) NOT NULL
);
CREATE TABLE courses (
course_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100) NOT NULL,
credits INT NOT NULL
);
CREATE TABLE student_courses (
stu_id INT NOT NULL,
course_id INT NOT NULL,
score DECIMAL(5,2),
PRIMARY KEY (stu_id, course_id),
FOREIGN KEY (stu_id) REFERENCES students(stu_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);2.3 外键约束与级联操作
外键是关系模型中维护引用完整性的核心机制。当父表记录被更新或删除时，可以通过级联操作自动处理子表中的关联记录。
级联操作类型：
| 操作 | 含义 | 示例 |
|---|---|---|
| CASCADE | 级联删除/更新子记录 | 删除用户时自动删除该用户的所有文章 |
| SET NULL | 将子记录外键设为 NULL | 删除分类时，文章的分类字段置空 |
| RESTRICT | 拒绝操作（默认） | 有文章引用该分类时，不允许删除分类 |
| SET DEFAULT | 将子记录外键设为默认值 | InnoDB 不支持 |
| NO ACTION | 等同于 RESTRICT（SQL 标准） | 检查时机略有不同 |
sql
CREATE TABLE articles (
article_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
user_id INT NOT NULL,
category_id INT DEFAULT NULL,
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(category_id)
ON DELETE SET NULL
ON UPDATE CASCADE
);级联删除执行流程:
DELETE FROM users WHERE user_id = 1;
┌────────────┐
│ users │ 删除 user_id=1
└─────┬──────┘
│ ON DELETE CASCADE
▼
┌────────────┐
│ articles │ 自动删除 user_id=1 的所有文章
└─────┬──────┘
│ ON DELETE CASCADE (article_tags 也设置了级联)
▼
┌──────────────┐
│ article_tags │ 自动删除关联的标签记录
└──────────────┘外键的实际使用争议：
| 观点 | 理由 |
|---|---|
| 支持使用外键 | 数据库层面保证完整性、减少脏数据、语义清晰 |
| 反对使用外键 | 影响写入性能、分库分表后无法跨库外键、增加运维复杂度 |
| 折中方案 | 逻辑外键（代码层面维护关系）+ 定时数据校验脚本 |
在互联网高并发场景下，很多团队选择不使用数据库外键约束，而是在应用层维护引用完整性。但在数据一致性要求极高的金融、医疗等领域，外键约束仍然是首选。
2.4 完整 ER 图示例 —— 博客系统
┌──────────┐ ┌──────────┐
│ User │ │ Tag │
│──────────│ │──────────│
│ user_id │◄─┐ ┌────►│ tag_id │
│ username │ │ │ │ tag_name │
│ email │ │ │ └──────────┘
│ password │ │ │ │
└──────────┘ │ │ │ M:N
│ │ │ │
│ 1:N │ 1:N │ ┌──────┴──────┐
│ │ │ │ article_tags │
▼ │ │ │─────────────│
┌──────────┐ │ ┌──────────────┐ │ │ article_id │
│ Article │ │ │ Comment │ │ │ tag_id │
│──────────│ │ │──────────────│ │ └─────────────┘
│article_id│──┼───►│ comment_id │ │
│ title │ │ │ article_id │ │
│ content │ │ │ user_id │─┘
│ user_id │──┘ │ content │
│created_at│ │ created_at │
└──────────┘ └──────────────┘
│
│ 关系说明:
│ User 1:N Article (一个用户发表多篇文章)
│ User 1:N Comment (一个用户发表多条评论)
│ Article 1:N Comment (一篇文章有多条评论)
│ Article M:N Tag (文章与标签多对多)三、索引原理
3.1 B 树 vs B+ 树
MySQL InnoDB 引擎默认使用 B+ 树 作为索引数据结构。要理解 B+ 树的优势，需要先对比 B 树。
B 树结构（所有节点都存数据）：
B 树 (3阶)
┌───────────────────┐
│ [30 | data30] │
│ [60 | data60] │ ← 非叶子节点也存完整数据
└───┬─────┬─────┬───┘
│ │ │
┌─────────┘ │ └─────────┐
▼ ▼ ▼
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ [10 | data10]│ │ [40 | data40]│ │ [70 | data70]│
│ [20 | data20]│ │ [50 | data50]│ │ [80 | data80]│
└──────────────┘ └──────────────┘ └──────────────┘
叶子节点间无链接，范围查询需回溯B+ 树结构（数据只在叶子节点）：
B+ 树结构示意图
┌───────────────────┐
┌─────────│ [30 | 60] │──────────┐
│ └───────────────────┘ │
▼ │ ▼
┌───────────────┐ ▼ ┌───────────────┐
│ [10 | 20] │ ┌──────────────┐ │ [70 | 80] │
└───┬───┬───┬───┘ │ [40 | 50] │ └───┬───┬───┬───┘
│ │ │ └──┬───┬──┬──┘ │ │ │
▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼
┌───┬───┬───┐ ┌───┬───┬───┐ ┌───┬───┬───┐
│ 5 │10 │20 │─────►│30 │40 │50 │────►│60 │70 │80 │
│ 8 │15 │25 │ │35 │45 │55 │ │65 │75 │90 │
└───┴───┴───┘ └───┴───┴───┘ └───┴───┴───┘
叶子节点 叶子节点 叶子节点
(双向链表连接，支持范围查询)B 树 vs B+ 树对比：
| 对比维度 | B 树 | B+ 树 |
|---|---|---|
| 数据存储位置 | 所有节点都存数据 | 只有叶子节点存数据 |
| 非叶子节点容量 | 小（存了数据占空间） | 大（只存键值和指针） |
| 树高度 | 相对更高 | 相对更矮（同数据量） |
| 范围查询 | 需要中序遍历，效率低 | 叶子链表顺序遍历，极快 |
| 等值查询 | 可能在非叶子命中（少数情况更快） | 必须到叶子节点 |
| 磁盘 IO | 相对更多 | 更少（核心优势） |
| 适用场景 | 文件系统（如 MongoDB 早期） | 关系数据库索引（MySQL, PostgreSQL） |
为什么 MySQL 选择 B+ 树：
磁盘 IO 是数据库最大的性能瓶颈:
内存访问: ~100ns
SSD 随机读: ~100μs (慢 1000 倍)
HDD 随机读: ~10ms (慢 100000 倍)
B+ 树的核心优化思路:
1. 非叶子节点不存数据 → 单节点能容纳更多键值 → 树更矮 → IO 更少
2. InnoDB 默认页大小 16KB，一个非叶子节点可存约 1200 个指针
3. 三层 B+ 树可索引: 1200 × 1200 × 每页行数 ≈ 数千万行
4. 叶子链表 → 范围查询只需顺序读，不用回溯父节点3.2 查找过程
以查找 WHERE id = 45 为例：
第1次 IO: 读取根节点 [30|60]
45 > 30 且 45 < 60 → 走中间指针
第2次 IO: 读取中间节点 [40|50]
45 > 40 且 45 < 50 → 走中间指针
第3次 IO: 读取叶子节点，找到 45 对应的数据行
总共: 3 次磁盘 IO 即可定位数据B+ 树核心特征：
| 特征 | 说明 |
|---|---|
| 所有数据存在叶子节点 | 非叶子节点只存索引键值 |
| 叶子节点形成有序链表 | 支持高效的范围查询和排序 |
| 树高度通常 2-4 层 | 千万级数据只需 3-4 次 IO |
| 节点大小等于磁盘页 | InnoDB 默认 16KB，减少 IO 次数 |
3.3 聚簇索引 vs 非聚簇索引
聚簇索引 (Clustered Index) 非聚簇索引 (Secondary Index)
┌─────────────────────────────┐ ┌─────────────────────────────┐
│ [30 | 60] │ │ [E | M] │
│ (主键索引树) │ │ (name 索引树) │
└──────┬──────┬───────────────┘ └──────┬──────┬──────────────┘
│ │ │ │
▼ ▼ ▼ ▼
┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐
│ id=10 │ │ id=30 │ │ name=Amy │ │ name=Eva │
│ 完整行数据│ │ 完整行数据│ │ id=30 │ │ id=10 │
│ name,age │ │ name,age │ │ (主键值) │ │ (主键值) │
│ email... │ │ email... │ └──────────┘ └──────────┘
└──────────┘ └──────────┘ │
│ 回表查询
▼
再次查询聚簇索引
获取完整行数据| 对比维度 | 聚簇索引 | 非聚簇索引（二级索引） |
|---|---|---|
| 叶子节点存储 | 完整行数据 | 索引列 + 主键值 |
| 每张表数量 | 只能有一个 | 可以有多个 |
| 默认选择 | InnoDB 主键 | 其他索引 |
| 查询效率 | 直接获取数据 | 可能需要回表 |
| 数据物理顺序 | 按主键排列 | 独立的排序顺序 |
回表的代价：
查询: SELECT * FROM users WHERE name = 'Amy'
步骤 1: 在 name 索引树上查找 'Amy' → 得到 id=30 (1~2 次 IO)
步骤 2: 用 id=30 回表查聚簇索引 → 得到完整行数据 (1~2 次 IO)
──────────
总计: 2~4 次 IO
如果使用覆盖索引(只查 name 和 id):
步骤 1: 在 name 索引树上查找 'Amy' → 直接返回 (1~2 次 IO)
无需回表!3.4 联合索引与最左前缀匹配
联合索引按照字段声明顺序构建 B+ 树排序规则。
sql
CREATE INDEX idx_abc ON users(a, b, c);联合索引 (a, b, c) 的 B+ 树排列方式：
先按 a 排序 → a 相同时按 b 排序 → b 相同时按 c 排序
┌─────────────────────────────────────────┐
│ (1,1,1) (1,1,3) (1,2,1) (2,1,1) ... │
│ a=1 a=1 a=1 a=2 │
│ b=1 b=1 b=2 b=1 │
│ c=1 c=3 c=1 c=1 │
└─────────────────────────────────────────┘最左前缀匹配规则：
| 查询条件 | 是否命中索引 | 说明 |
|---|---|---|
WHERE a = 1 | ✅ 命中 | 使用索引第一列 |
WHERE a = 1 AND b = 2 | ✅ 命中 | 使用索引前两列 |
WHERE a = 1 AND b = 2 AND c = 3 | ✅ 命中 | 完整使用索引 |
WHERE b = 2 | ❌ 不命中 | 缺少最左列 a |
WHERE b = 2 AND c = 3 | ❌ 不命中 | 缺少最左列 a |
WHERE a = 1 AND c = 3 | ⚠️ 部分命中 | 只用到 a 列，c 无法使用 |
WHERE a > 1 AND b = 2 | ⚠️ 部分命中 | a 用范围查询后 b 无法使用索引 |
为什么跳过中间列就不能使用后续索引：
联合索引 (a, b, c):
查询 WHERE a = 1 AND c = 3:
在 B+ 树上先找到 a=1 的区间:
(1,1,1) (1,1,3) (1,2,1) (1,2,5) (1,3,3) ...
此时 b 的值是无序的: 1, 1, 2, 2, 3...
c 的值更是无序的: 1, 3, 1, 5, 3...
由于跳过了 b，无法利用 c 的有序性
只能在 a=1 的结果集中逐行过滤 c=33.5 覆盖索引
当查询所需的所有字段都包含在索引中时，无需回表查询，称为覆盖索引。
sql
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = '张三';查询过程:
┌──────────────────────┐
│ idx_name_age 索引树 │
│ │
│ 叶子节点包含: │
│ name + age + 主键id │
│ │
│ SELECT 只需要 │
│ name + age │
│ │
│ ✅ 索引已覆盖所有字段 │
│ ✅ 无需回表 │
│ │
│ EXPLAIN 中 │
│ Extra: Using index │
└──────────────────────┘3.6 索引失效场景
| 序号 | 失效场景 | 示例 | 原因 |
|---|---|---|---|
| 1 | 对索引列使用函数 | WHERE YEAR(created_at) = 2024 | 函数破坏了索引的有序性 |
| 2 | 隐式类型转换 | WHERE phone = 13800001111（phone 是 VARCHAR） | 触发隐式转换等同于函数操作 |
| 3 | LIKE 左模糊 | WHERE name LIKE '%张' | 无法利用 B+ 树的前缀匹配 |
| 4 | OR 连接非索引列 | WHERE a = 1 OR d = 2（d 无索引） | 需全表扫描 d 列 |
| 5 | 不满足最左前缀 | 联合索引 (a,b,c)，查 WHERE b = 1 | 跳过了最左列 |
| 6 | 对索引列做运算 | WHERE id + 1 = 10 | 同函数，破坏有序性 |
| 7 | NOT IN / NOT EXISTS | 视优化器判断 | 部分场景优化器放弃索引 |
| 8 | 数据量过小 | 表只有几十行 | 全表扫描比索引更快 |
| 9 | 索引选择性低 | WHERE gender = '男' （仅两种值） | 优化器判断全表扫描更高效 |
索引失效修正示例：
sql
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01';sql
SELECT * FROM orders WHERE id + 1 = 10;
SELECT * FROM orders WHERE id = 9;sql
SELECT * FROM users WHERE phone = 13800001111;
SELECT * FROM users WHERE phone = '13800001111';四、事务与并发控制
4.1 ACID 四特性
┌─────────────────────────────────────────────────────────────┐
│ ACID 特性 │
├──────────────┬──────────────────────────────────────────────┤
│ A: 原子性 │ 事务中的所有操作要么全部成功，要么全部回滚 │
│ Atomicity │ 转账: 扣款+收款必须同时成功或同时失败 │
├──────────────┼──────────────────────────────────────────────┤
│ C: 一致性 │ 事务执行前后，数据库从一个一致状态转变到 │
│ Consistency │ 另一个一致状态。如转账前后总金额不变 │
├──────────────┼──────────────────────────────────────────────┤
│ I: 隔离性 │ 并发事务之间相互隔离，一个事务的中间状态 │
│ Isolation │ 对其他事务不可见 │
├──────────────┼──────────────────────────────────────────────┤
│ D: 持久性 │ 事务提交后，对数据库的修改是永久性的 │
│ Durability │ 即使系统崩溃也不会丢失 │
└──────────────┴──────────────────────────────────────────────┘ACID 之间的关系：
┌─────────────────────────────────────────────────────┐
│ │
│ 原子性 (Atomicity) ──┐ │
│ ├──► 一致性 (Consistency) │
│ 隔离性 (Isolation) ──┘ ▲ │
│ │ │
│ 持久性 (Durability) ────────────┘ │
│ │
│ 一致性是最终目标 │
│ 原子性、隔离性、持久性是实现一致性的手段 │
└─────────────────────────────────────────────────────┘Node.js 中的事务操作示例（使用 mysql2）：
javascript
const mysql = require('mysql2/promise');
async function transfer(fromId, toId, amount) {
const conn = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'password',
database: 'bank'
});
try {
await conn.beginTransaction();
const [fromRows] = await conn.execute(
'SELECT balance FROM accounts WHERE id = ? FOR UPDATE',
[fromId]
);
if (fromRows[0].balance < amount) {
throw new Error('余额不足');
}
await conn.execute(
'UPDATE accounts SET balance = balance - ? WHERE id = ?',
[amount, fromId]
);
await conn.execute(
'UPDATE accounts SET balance = balance + ? WHERE id = ?',
[amount, toId]
);
await conn.commit();
console.log('转账成功');
} catch (err) {
await conn.rollback();
console.error('转账失败，已回滚:', err.message);
throw err;
} finally {
await conn.end();
}
}4.2 四种隔离级别
隔离级别由低到高：
读未提交 ──► 读已提交 ──► 可重复读 ──► 串行化
(最低) (MySQL默认) (最高)
并发性能： 高 ────────────────────────► 低
数据安全： 低 ────────────────────────► 高4.3 并发问题详解
脏读（Dirty Read）
读取到其他事务尚未提交的数据，如果该事务最终回滚，则读取到的数据是无效的"脏数据"。
时间线 事务A 事务B
─────────────────────────────────────────────
T1 BEGIN
T2 UPDATE balance=500
WHERE id=1
(原值1000)
T3 BEGIN
T4 SELECT balance
WHERE id=1
读到 500 ← 脏读!
T5 ROLLBACK
(balance 恢复为1000)
T6 基于 500 做业务计算
← 数据错误!不可重复读（Non-Repeatable Read）
同一事务内两次读取同一行数据，因为其他事务的提交导致结果不同。侧重于已有数据的修改。
时间线 事务A 事务B
─────────────────────────────────────────────
T1 BEGIN
T2 SELECT balance
WHERE id=1
读到 1000
T3 BEGIN
T4 UPDATE balance=500
WHERE id=1
T5 COMMIT
T6 SELECT balance
WHERE id=1
读到 500 ← 不可重复读!
(同一事务两次读取结果不同)幻读（Phantom Read）
同一事务内两次查询同一范围的数据，因为其他事务插入了新记录导致结果集行数不同。侧重于新增或删除的行。
时间线 事务A 事务B
─────────────────────────────────────────────
T1 BEGIN
T2 SELECT COUNT(*)
FROM users
WHERE age > 20
结果: 5 行
T3 BEGIN
T4 INSERT INTO users
(name, age)
VALUES('新人', 25)
T5 COMMIT
T6 SELECT COUNT(*)
FROM users
WHERE age > 20
结果: 6 行 ← 幻读!
(多出了"幻影"行)三种并发问题的核心区别：
| 问题 | 关注点 | 原因 | 关键词 |
|---|---|---|---|
| 脏读 | 读到未提交的数据 | 其他事务未提交就能读到 | 未提交 |
| 不可重复读 | 同一行数据两次读不一致 | 其他事务修改并提交了该行 | 修改（UPDATE） |
| 幻读 | 同一范围查询两次行数不一致 | 其他事务插入了新行 | 新增（INSERT） |
4.4 隔离级别对比表
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 实现机制 |
|---|---|---|---|---|
| READ UNCOMMITTED | ✅ 可能 | ✅ 可能 | ✅ 可能 | 无任何锁/快照 |
| READ COMMITTED | ❌ 避免 | ✅ 可能 | ✅ 可能 | 每次读生成新快照 |
| REPEATABLE READ | ❌ 避免 | ❌ 避免 | ✅ 可能 | 事务开始时生成快照(MVCC) |
| SERIALIZABLE | ❌ 避免 | ❌ 避免 | ❌ 避免 | 加锁串行执行 |
MySQL InnoDB 默认隔离级别为 REPEATABLE READ，并通过 Next-Key Lock 在一定程度上解决了幻读问题。
sql
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;4.5 MVCC 多版本并发控制
MySQL InnoDB 在 REPEATABLE READ 级别下使用 MVCC（Multi-Version Concurrency Control） 实现一致性读，而非加锁。MVCC 的核心思想是：为每行数据维护多个版本，通过版本链和可见性规则让不同事务看到不同版本的数据。
┌──────────────────────────────────────────────────────┐
│ MVCC 原理 │
├──────────────────────────────────────────────────────┤
│ │
│ 每行数据隐藏两列: │
│ DB_TRX_ID : 最后修改该行的事务 ID │
│ DB_ROLL_PTR: 指向 undo log 中旧版本的指针 │
│ │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ 当前版本 │───►│ 上一版本 │───►│ 更早版本 │ │
│ │ trx_id=5 │ │ trx_id=3 │ │ trx_id=1 │ │
│ │ name=李四 │ │ name=张三 │ │ name=王五 │ │
│ └──────────┘ └──────────┘ └──────────┘ │
│ │
│ ReadView (快照读): │
│ 事务开始时记录当前活跃事务列表 │
│ 根据可见性规则判断应该读取哪个版本 │
│ │
└──────────────────────────────────────────────────────┘ReadView 可见性判断规则：
ReadView 包含四个关键字段:
m_ids : 创建 ReadView 时的活跃事务 ID 列表
min_trx_id : m_ids 中的最小值
max_trx_id : 系统下一个将分配的事务 ID
creator_trx_id: 创建该 ReadView 的事务 ID
判断规则 (对于某行数据的某个版本，其 trx_id = T):
┌────────────────────────────────────────────────┐
│ T < min_trx_id ? │
│ 是 → 该版本在 ReadView 创建前已提交 → ✅ 可见 │
│ 否 ↓ │
│ T >= max_trx_id ? │
│ 是 → 该版本在 ReadView 创建后才开始 → ❌ 不可见│
│ 否 ↓ │
│ T 在 m_ids 中 ? │
│ 是 → 该版本的事务还未提交 → ❌ 不可见 │
│ 否 → 该版本的事务已提交 → ✅ 可见 │
└────────────────────────────────────────────────┘RC 与 RR 的 ReadView 区别：
| 隔离级别 | ReadView 创建时机 | 效果 |
|---|---|---|
| READ COMMITTED | 每次 SELECT 都创建新的 ReadView | 能看到其他事务最新提交的结果 |
| REPEATABLE READ | 事务第一次 SELECT 时创建，后续复用 | 整个事务期间看到的数据快照一致 |
4.6 乐观锁 vs 悲观锁
┌─────────────────────────────────┬─────────────────────────────────┐
│ 悲观锁 │ 乐观锁 │
├─────────────────────────────────┼─────────────────────────────────┤
│ 假设冲突经常发生 │ 假设冲突很少发生 │
│ 先加锁再操作 │ 操作时不加锁，提交时检查冲突 │
│ SELECT ... FOR UPDATE │ 通过版本号/时间戳实现 │
│ 适合写多读少 │ 适合读多写少 │
│ 开销大，但安全 │ 开销小，但可能需重试 │
└─────────────────────────────────┴─────────────────────────────────┘悲观锁实现：
javascript
async function deductStock(productId, quantity) {
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const [rows] = await conn.execute(
'SELECT stock FROM products WHERE id = ? FOR UPDATE',
[productId]
);
if (rows[0].stock < quantity) {
throw new Error('库存不足');
}
await conn.execute(
'UPDATE products SET stock = stock - ? WHERE id = ?',
[quantity, productId]
);
await conn.commit();
} catch (err) {
await conn.rollback();
throw err;
} finally {
conn.release();
}
}乐观锁实现（版本号机制）：
javascript
async function deductStockOptimistic(productId, quantity, maxRetries = 3) {
const conn = await pool.getConnection();
for (let attempt = 0; attempt < maxRetries; attempt++) {
try {
const [rows] = await conn.execute(
'SELECT stock, version FROM products WHERE id = ?',
[productId]
);
const { stock, version } = rows[0];
if (stock < quantity) {
throw new Error('库存不足');
}
const [result] = await conn.execute(
'UPDATE products SET stock = stock - ?, version = version + 1 WHERE id = ? AND version = ?',
[quantity, productId, version]
);
if (result.affectedRows === 1) {
return;
}
console.log(`版本冲突，第 ${attempt + 1} 次重试`);
} catch (err) {
throw err;
}
}
throw new Error('乐观锁重试次数耗尽');
}锁选择决策：
| 场景 | 推荐 | 理由 |
|---|---|---|
| 电商库存扣减 | 悲观锁 | 冲突概率高，重试成本大 |
| 社交点赞/收藏 | 乐观锁 | 冲突概率低，重试代价小 |
| 金融转账 | 悲观锁 | 必须保证数据准确 |
| CMS 内容编辑 | 乐观锁 | 并发编辑概率低 |
五、锁机制
5.1 共享锁与排他锁
┌──────────────────────────────────────────────────────┐
│ 锁的兼容性矩阵 │
├──────────┬──────────────┬──────────────┬─────────────┤
│ │ 共享锁 (S) │ 排他锁 (X) │ 说明 │
├──────────┼──────────────┼──────────────┼─────────────┤
│ 共享锁(S) │ ✅ 兼容 │ ❌ 冲突 │ 读读并行 │
├──────────┼──────────────┼──────────────┼─────────────┤
│ 排他锁(X) │ ❌ 冲突 │ ❌ 冲突 │ 写写/读写 │
│ │ │ │ 互斥 │
└──────────┴──────────────┴──────────────┴─────────────┘sql
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
SELECT * FROM users WHERE id = 1 FOR UPDATE;5.2 行锁、间隙锁与 Next-Key Lock
InnoDB 在 REPEATABLE READ 级别下使用三种行级锁来解决幻读问题：
假设索引中有记录: 10, 20, 30
┌─────────────────────────────────────────────────────────┐
│ InnoDB 锁类型 │
├─────────────────────────────────────────────────────────┤
│ │
│ Record Lock (记录锁): │
│ 锁定索引上的单条记录 │
│ ──[10]──────[20]──────[30]── │
│ ^ │
│ 锁住 id=10 这条记录 │
│ │
│ Gap Lock (间隙锁): │
│ 锁定索引记录之间的"间隙"，不包含记录本身 │
│ ──(10)─────(20)──────(30)── │
│ ^^^^^ │
│ 锁住 (10, 20) 这个开区间 │
│ 阻止其他事务在此区间 INSERT │
│ │
│ Next-Key Lock (记录锁 + 间隙锁): │
│ 锁定记录本身 + 记录前面的间隙 │
│ ──(10]─────(20]──────(30]── │
│ ^^^^^^ │
│ 锁住 (10, 20] 这个左开右闭区间 │
│ │
└─────────────────────────────────────────────────────────┘5.3 行锁与表锁
| 锁粒度 | 加锁范围 | 并发度 | 开销 | 死锁 | 适用引擎 |
|---|---|---|---|---|---|
| 表锁 | 锁定整张表 | 低 | 小 | 不会 | MyISAM/InnoDB |
| 行锁 | 锁定特定行 | 高 | 大 | 可能 | InnoDB |
| 页锁 | 锁定一页数据 | 中 | 中 | 可能 | BDB |
InnoDB 的行锁是通过索引实现的。如果 SQL 没有使用索引，会退化为表锁。
sql
UPDATE users SET name = '张三' WHERE id = 1;
UPDATE users SET name = '张三' WHERE name = '李四';5.4 死锁及其避免
死锁场景示意:
事务A 事务B
───── ─────
锁住 id=1 的行 锁住 id=2 的行
│ │
│ 尝试锁 id=2 │ 尝试锁 id=1
│ ┌──────────┐ │
└─────►│ 互相等待 │◄───────────┘
│ 死 锁! │
└──────────┘死锁避免策略：
| 策略 | 说明 |
|---|---|
| 固定加锁顺序 | 所有事务按相同的顺序访问资源（如按 id 升序） |
| 缩小事务范围 | 尽量减少事务中持有锁的时间 |
| 设置超时 | innodb_lock_wait_timeout（默认 50 秒） |
| 降低隔离级别 | 在可接受范围内使用较低的隔离级别 |
| 使用乐观锁 | 减少显式加锁 |
sql
SHOW ENGINE INNODB STATUS;
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SET innodb_lock_wait_timeout = 10;六、慢查询分析与优化
6.1 开启慢查询日志
sql
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';mysqldumpslow 分析工具：
bash
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log| 参数 | 含义 |
|---|---|
| -s t | 按查询时间排序 |
| -s c | 按执行次数排序 |
| -s r | 按返回行数排序 |
| -t N | 只显示前 N 条 |
6.2 EXPLAIN 命令解读
sql
EXPLAIN SELECT u.name, a.title
FROM users u
JOIN articles a ON u.user_id = a.user_id
WHERE u.status = 1
ORDER BY a.created_at DESC
LIMIT 10;EXPLAIN 输出关键字段：
| 字段 | 含义 | 关注要点 |
|---|---|---|
| id | 查询序号 | 相同 id 从上到下执行，不同 id 大的先执行 |
| select_type | 查询类型 | SIMPLE/PRIMARY/SUBQUERY/DERIVED |
| table | 访问的表 | 实际表名或别名 |
| type | 访问类型 | 性能从好到差，见下表 |
| possible_keys | 可能使用的索引 | 候选索引列表 |
| key | 实际使用的索引 | 为 NULL 表示未使用索引 |
| key_len | 使用的索引长度 | 越短说明使用的索引列越少 |
| rows | 预估扫描行数 | 越小越好 |
| filtered | 条件过滤百分比 | 越大越好，100% 最理想 |
| Extra | 额外信息 | 重点关注，见下表 |
type 字段性能排序（从优到劣）：
system > const > eq_ref > ref > range > index > ALL
┌────────┬────────────────────────────────────────┐
│ system │ 表只有一行记录 │
│ const │ 通过主键/唯一索引精确匹配一行 │
│ eq_ref │ JOIN 时使用主键/唯一索引 │
│ ref │ 使用非唯一索引匹配 │
│ range │ 索引范围查找 (BETWEEN, >, <, IN) │
│ index │ 全索引扫描（遍历索引树） │
│ ALL │ 全表扫描（最差，必须优化） │
└────────┴────────────────────────────────────────┘Extra 字段重要取值：
| Extra 值 | 含义 | 好坏 |
|---|---|---|
| Using index | 覆盖索引，无需回表 | ✅ 好 |
| Using where | 在存储引擎返回后再过滤 | ⚠️ 一般 |
| Using temporary | 使用临时表 | ❌ 差 |
| Using filesort | 文件排序（非索引排序） | ❌ 差 |
| Using index condition | 索引下推 (ICP) | ✅ 好 |
EXPLAIN 实战分析示例：
+----+-------------+-------+------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | rows | filtered | Extra |
+----+-------------+-------+------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | u | ref | idx_status | idx_status| 1 | 5000 | 100.00 | NULL |
| 1 | SIMPLE | a | ref | idx_user_id | idx_user_id| 4 | 10 | 100.00 | Using filesort |
+----+-------------+-------+------+---------------+----------+---------+------+------+-------------+
分析:
1. users 表通过 idx_status 索引，type=ref，预估扫描 5000 行 → 合理
2. articles 表通过 idx_user_id 索引关联 → 合理
3. Extra 出现 Using filesort → ORDER BY created_at 未走索引排序
4. 优化: 建联合索引 (user_id, created_at) 消除 filesort6.3 索引优化策略
sql
CREATE INDEX idx_status ON users(status);
CREATE INDEX idx_user_created ON articles(user_id, created_at);
CREATE INDEX idx_cover ON orders(user_id, status, created_at, total_amount);
SELECT user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 100 AND status = 1
ORDER BY created_at DESC;索引设计原则：
| 原则 | 说明 |
|---|---|
| 选择性高的列优先 | 区分度大的列放在联合索引前面 |
| 覆盖查询字段 | 将 SELECT 的字段纳入索引，避免回表 |
| 避免过多索引 | 每个索引占额外存储，且影响写入性能 |
| 前缀索引 | 对长字符串取前 N 个字符建索引 |
| 利用索引排序 | ORDER BY 字段放入联合索引 |
索引选择性计算：
sql
SELECT
COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
COUNT(DISTINCT email) / COUNT(*) AS email_selectivity,
COUNT(DISTINCT username) / COUNT(*) AS username_selectivity
FROM users;
+---------------------+---------------------+------------------------+
| status_selectivity | email_selectivity | username_selectivity |
+---------------------+---------------------+------------------------+
| 0.0003 | 1.0000 | 1.0000 |
+---------------------+---------------------+------------------------+选择性越接近 1.0，索引效果越好。status 只有 0.0003 的选择性，不适合单独建索引。
6.4 查询重写技巧
技巧 1：避免 SELECT *
sql
SELECT * FROM users WHERE status = 1;
SELECT user_id, name, email FROM users WHERE status = 1;技巧 2：用 EXISTS 替代 IN（子查询数据量大时）
sql
SELECT * FROM orders
WHERE user_id IN (SELECT user_id FROM users WHERE status = 1);
SELECT o.* FROM orders o
WHERE EXISTS (
SELECT 1 FROM users u
WHERE u.user_id = o.user_id AND u.status = 1
);技巧 3：避免在 WHERE 中对列做运算
sql
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';技巧 4：用 UNION ALL 替代 UNION（不需要去重时）
sql
SELECT name FROM table_a WHERE status = 1
UNION
SELECT name FROM table_b WHERE status = 1;
SELECT name FROM table_a WHERE status = 1
UNION ALL
SELECT name FROM table_b WHERE status = 1;6.5 分页优化（深分页问题）
传统分页在偏移量很大时性能急剧下降：
sql
SELECT * FROM articles ORDER BY id LIMIT 1000000, 10;问题分析:
LIMIT 1000000, 10 的执行过程:
1. 从索引中取出 1000010 行
2. 丢弃前 1000000 行
3. 返回最后 10 行
→ 白白扫描了 100 万行!优化方案 1：游标分页（推荐）
sql
SELECT * FROM articles
WHERE id > 1000000
ORDER BY id
LIMIT 10;优化方案 2：延迟关联
sql
SELECT a.* FROM articles a
INNER JOIN (
SELECT id FROM articles
ORDER BY id
LIMIT 1000000, 10
) t ON a.id = t.id;优化方案对比：
| 方案 | 原理 | 优点 | 缺点 |
|---|---|---|---|
| 传统 LIMIT | OFFSET 跳过行 | 简单通用 | 深分页极慢 |
| 游标分页 | 基于上次最后 ID | O(1) 性能 | 不支持跳页 |
| 延迟关联 | 子查询走覆盖索引 | 支持跳页 | 仍有一定开销 |
| 搜索引擎 | ES 等外部方案 | 适合全文搜索 | 架构复杂 |
6.6 N+1 查询问题与解决方案
N+1 问题是 ORM 中最常见的性能陷阱之一。当查询一个列表（1 次查询）后，对列表中每条记录都执行一次关联查询（N 次），总共执行 N+1 次查询。
N+1 问题示例：
javascript
const articles = await db.query('SELECT * FROM articles LIMIT 20');
for (const article of articles) {
const [author] = await db.query(
'SELECT * FROM users WHERE user_id = ?',
[article.user_id]
);
article.author = author;
}执行的 SQL:
第 1 次: SELECT * FROM articles LIMIT 20 (1 次)
第 2 次: SELECT * FROM users WHERE user_id = 1 (第1篇文章)
第 3 次: SELECT * FROM users WHERE user_id = 3 (第2篇文章)
第 4 次: SELECT * FROM users WHERE user_id = 1 (第3篇文章)
...
第 21 次: SELECT * FROM users WHERE user_id = 7 (第20篇文章)
──────────
总计: 1 + 20 = 21 次数据库查询!解决方案 1：JOIN 查询（预加载）
javascript
const articles = await db.query(`
SELECT a.*, u.username, u.avatar
FROM articles a
INNER JOIN users u ON a.user_id = u.user_id
LIMIT 20
`);执行的 SQL: 仅 1 次!解决方案 2：批量查询（IN 查询）
javascript
const articles = await db.query('SELECT * FROM articles LIMIT 20');
const userIds = [...new Set(articles.map(a => a.user_id))];
const users = await db.query(
`SELECT * FROM users WHERE user_id IN (${userIds.map(() => '?').join(',')})`,
userIds
);
const userMap = new Map(users.map(u => [u.user_id, u]));
articles.forEach(a => {
a.author = userMap.get(a.user_id);
});执行的 SQL:
第 1 次: SELECT * FROM articles LIMIT 20
第 2 次: SELECT * FROM users WHERE user_id IN (1, 3, 5, 7, ...)
──────────
总计: 仅 2 次数据库查询! (从 21 次优化到 2 次)解决方案 3：DataLoader 模式（批量 + 缓存）
javascript
class UserLoader {
constructor(db) {
this.db = db;
this.cache = new Map();
this.batch = [];
this.batchScheduled = false;
}
async load(userId) {
if (this.cache.has(userId)) {
return this.cache.get(userId);
}
return new Promise((resolve) => {
this.batch.push({ userId, resolve });
if (!this.batchScheduled) {
this.batchScheduled = true;
process.nextTick(() => this.executeBatch());
}
});
}
async executeBatch() {
const currentBatch = this.batch.splice(0);
this.batchScheduled = false;
const ids = currentBatch.map(item => item.userId);
const users = await this.db.query(
`SELECT * FROM users WHERE user_id IN (${ids.map(() => '?').join(',')})`,
ids
);
const userMap = new Map(users.map(u => [u.user_id, u]));
currentBatch.forEach(({ userId, resolve }) => {
const user = userMap.get(userId);
this.cache.set(userId, user);
resolve(user);
});
}
}N+1 解决方案对比：
| 方案 | 查询次数 | 优点 | 缺点 |
|---|---|---|---|
| 原始 N+1 | N+1 | 代码简单 | 性能灾难 |
| JOIN | 1 | 最少查询次数 | 结果集可能膨胀（一对多 JOIN） |
| 批量 IN | 2 | 灵活，避免结果膨胀 | 需手动拼接 |
| DataLoader | 2（自动批量） | 自动合并、带缓存 | 实现较复杂 |
七、数据库分库分表
7.1 为什么要分库分表
单库单表的瓶颈:
┌──────────────────────────────────────────────┐
│ 数据量: 单表超过 2000 万行性能下降 │
│ QPS: 单库连接数有上限 (通常 3000-5000) │
│ 存储: 单机磁盘空间有限 │
│ 可用性: 单点故障风险 │
└──────────────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────┐
│ 解决方案: 分库分表 │
│ │
│ ┌──────────┐ ┌──────────┐ │
│ │ 垂直拆分 │ │ 水平拆分 │ │
│ │ │ │ │ │
│ │ 按业务分库│ │ 按规则分片│ │
│ │ 按字段分表│ │ 同结构多表│ │
│ └──────────┘ └──────────┘ │
└──────────────────────────────────────────────┘7.2 垂直拆分
垂直分库 —— 按业务模块拆分为独立数据库：
拆分前: 拆分后:
┌──────────────┐ ┌──────────────┐
│ 单体数据库 │ │ 用户库 db_user│
│ │ │ users │
│ users │ │ user_profiles│
│ orders │ ────► └──────────────┘
│ products │ ┌──────────────┐
│ payments │ │ 订单库 db_order│
│ logistics │ │ orders │
└──────────────┘ │ payments │
└──────────────┘
┌──────────────┐
│ 商品库 db_prod│
│ products │
│ categories │
└──────────────┘垂直分表 —— 将一张宽表的字段拆分为多张表：
sql
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
status TINYINT DEFAULT 1
);
CREATE TABLE user_details (
user_id INT PRIMARY KEY,
avatar VARCHAR(255),
bio TEXT,
address TEXT,
preferences JSON,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);7.3 水平拆分
将同一张表的数据行按照某种规则分散到多张结构相同的表或多个库中。
水平拆分示意:
orders (原表 1亿行)
┌───────────────────┐
│ order_id | ... │
│ 1~1亿条数据 │
└───────────────────┘
│
┌───────────┼───────────┐
▼ ▼ ▼
┌────────────┐ ┌────────────┐ ┌────────────┐
│ orders_00 │ │ orders_01 │ │ orders_02 │
│ 3333万行 │ │ 3333万行 │ │ 3334万行 │
└────────────┘ └────────────┘ └────────────┘
DB_0 节点 DB_1 节点 DB_2 节点7.4 分片策略
Hash 分片
javascript
function getShardIndex(userId, shardCount) {
return userId % shardCount;
}
const shardCount = 4;
const userId = 12345;
const shardIndex = getShardIndex(userId, shardCount);
const tableName = `orders_${String(shardIndex).padStart(2, '0')}`;Hash 分片示意:
user_id = 12345
12345 % 4 = 1 → orders_01
┌─────────────────────────────────────────┐
│ user_id % 4 = 0 → orders_00 │
│ user_id % 4 = 1 → orders_01 ← 命中 │
│ user_id % 4 = 2 → orders_02 │
│ user_id % 4 = 3 → orders_03 │
└─────────────────────────────────────────┘Range 分片
javascript
function getShardByRange(orderId) {
if (orderId <= 10000000) return 'orders_00';
if (orderId <= 20000000) return 'orders_01';
if (orderId <= 30000000) return 'orders_02';
return 'orders_03';
}Range 分片示意:
orders_00 orders_01 orders_02
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ id: 1~1000万 │ │ id: 1000万~ │ │ id: 2000万~ │
│ │ │ 2000万 │ │ 3000万 │
└──────────────┘ └──────────────┘ └──────────────┘一致性哈希
普通 Hash 分片的最大问题是扩容时需要大量数据迁移。一致性哈希通过引入虚拟的"哈希环"来最小化迁移量。
一致性哈希环 (0 ~ 2^32):
0 / 2^32
│
Node C ●
╱ ╲
╱ ╲
●──────╱ ╲──────●
Node B ╱ ╲ Node A
│ 数据 │
│ 顺时针找到 │
│ 第一个节点 │
╲ ╱
╲ ╱
╲ ╱
●
Node D
数据路由: hash(key) → 在环上顺时针找到第一个节点
扩容只需迁移相邻节点的部分数据:
新增 Node E 在 Node A 和 Node B 之间:
只需要将 Node B 中属于 Node E 范围的数据迁移过去
其他节点完全不受影响javascript
const crypto = require('crypto');
class ConsistentHash {
constructor(nodes, virtualNodes = 150) {
this.ring = new Map();
this.sortedKeys = [];
this.virtualNodes = virtualNodes;
nodes.forEach(node => this.addNode(node));
}
hash(key) {
return parseInt(
crypto.createHash('md5').update(key).digest('hex').slice(0, 8),
16
);
}
addNode(node) {
for (let i = 0; i < this.virtualNodes; i++) {
const virtualKey = this.hash(`${node}#${i}`);
this.ring.set(virtualKey, node);
this.sortedKeys.push(virtualKey);
}
this.sortedKeys.sort((a, b) => a - b);
}
removeNode(node) {
for (let i = 0; i < this.virtualNodes; i++) {
const virtualKey = this.hash(`${node}#${i}`);
this.ring.delete(virtualKey);
this.sortedKeys = this.sortedKeys.filter(k => k !== virtualKey);
}
}
getNode(key) {
const hashVal = this.hash(key);
for (const ringKey of this.sortedKeys) {
if (hashVal <= ringKey) {
return this.ring.get(ringKey);
}
}
return this.ring.get(this.sortedKeys[0]);
}
}
const ch = new ConsistentHash(['db_0', 'db_1', 'db_2']);
console.log(ch.getNode('user_12345'));
console.log(ch.getNode('order_67890'));分片策略对比：
| 对比维度 | Hash 分片 | Range 分片 | 一致性哈希 |
|---|---|---|---|
| 数据均匀性 | ✅ 分布均匀 | ❌ 可能倾斜（热点） | ✅ 较均匀（虚拟节点） |
| 范围查询 | ❌ 需要跨分片 | ✅ 连续数据在同一分片 | ❌ 需要跨分片 |
| 扩容难度 | ❌ 全量数据迁移 | ✅ 新增分片即可 | ✅ 仅迁移相邻数据 |
| 适用场景 | 点查为主 | 范围查询为主 | 弹性扩缩容需求 |
7.5 分布式 ID 生成 —— 雪花算法
分库分表后，数据库自增 ID 不再全局唯一。雪花算法（Snowflake） 是一种经典的分布式 ID 生成方案。
雪花算法 ID 结构 (64 bit):
┌──────┬──────────────────────────┬────────────┬──────────────┐
│ 符号 │ 41位时间戳 │ 10位机器 │ 12位序列号 │
│ 1bit │ (毫秒级，可用69年) │ (1024台) │ (4096/ms) │
│ 0 │ │ │ │
└──────┴──────────────────────────┴────────────┴──────────────┘
63 62 22 21 12 11 0javascript
class SnowflakeIdGenerator {
constructor(workerId, datacenterId) {
this.epoch = 1609459200000n;
this.workerIdBits = 5n;
this.datacenterIdBits = 5n;
this.sequenceBits = 12n;
this.maxWorkerId = (1n << this.workerIdBits) - 1n;
this.maxDatacenterId = (1n << this.datacenterIdBits) - 1n;
this.workerIdShift = this.sequenceBits;
this.datacenterIdShift = this.sequenceBits + this.workerIdBits;
this.timestampShift = this.sequenceBits + this.workerIdBits + this.datacenterIdBits;
this.sequenceMask = (1n << this.sequenceBits) - 1n;
if (BigInt(workerId) > this.maxWorkerId || BigInt(workerId) < 0n) {
throw new Error(`workerId 超出范围: 0 ~ ${this.maxWorkerId}`);
}
if (BigInt(datacenterId) > this.maxDatacenterId || BigInt(datacenterId) < 0n) {
throw new Error(`datacenterId 超出范围: 0 ~ ${this.maxDatacenterId}`);
}
this.workerId = BigInt(workerId);
this.datacenterId = BigInt(datacenterId);
this.sequence = 0n;
this.lastTimestamp = -1n;
}
currentTimestamp() {
return BigInt(Date.now());
}
tilNextMillis(lastTimestamp) {
let timestamp = this.currentTimestamp();
while (timestamp <= lastTimestamp) {
timestamp = this.currentTimestamp();
}
return timestamp;
}
nextId() {
let timestamp = this.currentTimestamp();
if (timestamp < this.lastTimestamp) {
throw new Error('时钟回拨，拒绝生成 ID');
}
if (timestamp === this.lastTimestamp) {
this.sequence = (this.sequence + 1n) & this.sequenceMask;
if (this.sequence === 0n) {
timestamp = this.tilNextMillis(this.lastTimestamp);
}
} else {
this.sequence = 0n;
}
this.lastTimestamp = timestamp;
return (
((timestamp - this.epoch) << this.timestampShift) |
(this.datacenterId << this.datacenterIdShift) |
(this.workerId << this.workerIdShift) |
this.sequence
);
}
}
const idGen = new SnowflakeIdGenerator(1, 1);
for (let i = 0; i < 5; i++) {
console.log(idGen.nextId().toString());
}常见分布式 ID 方案对比：
| 方案 | 有序性 | 性能 | 依赖 | 缺点 |
|---|---|---|---|---|
| UUID | ❌ 无序 | 高 | 无 | 过长(36字符)，索引效率低 |
| 数据库自增 | ✅ 有序 | 低 | 数据库 | 单点瓶颈 |
| Redis INCR | ✅ 有序 | 高 | Redis | 需保证 Redis 高可用 |
| 雪花算法 | ✅ 趋势递增 | 极高 | 无 | 依赖时钟，有时钟回拨问题 |
| Leaf (美团) | ✅ 有序 | 高 | ZK/DB | 架构复杂 |
八、实战案例 —— 博客系统数据库设计
8.1 需求分析
┌──────────────────────────────────────────────────────┐
│ 博客系统功能需求 │
├──────────────────────────────────────────────────────┤
│ │
│ 用户模块: │
│ - 注册、登录 │
│ - 用户资料（头像、简介） │
│ │
│ 文章模块: │
│ - 发布、编辑、删除文章 │
│ - 文章支持分类和标签 │
│ - 文章列表分页展示 │
│ │
│ 评论模块: │
│ - 对文章发表评论 │
│ - 支持评论回复（二级评论） │
│ │
│ 标签模块: │
│ - 文章可打多个标签 │
│ - 按标签筛选文章 │
│ │
│ 统计需求: │
│ - 文章浏览量 │
│ - 用户文章数量 │
│ - 热门文章排行 │
│ │
└──────────────────────────────────────────────────────┘8.2 实体关系分析
实体关系梳理:
User ──── 1:N ────► Article 一个用户发布多篇文章
User ──── 1:N ────► Comment 一个用户发表多条评论
Article ── 1:N ────► Comment 一篇文章下有多条评论
Article ── M:N ────► Tag 文章与标签多对多
Comment ── 1:N ────► Comment 评论的回复（自关联）
Article ── N:1 ────► Category 文章属于一个分类8.3 完整 ER 图
┌─────────────┐ ┌──────────────┐ ┌─────────────┐
│ categories │ │ users │ │ tags │
│─────────────│ │──────────────│ │─────────────│
│ category_id │◄──┐ │ user_id (PK) │ │ tag_id (PK) │
│ name │ │ │ username │ │ name │
│ sort_order │ │ │ email │ │ created_at │
└─────────────┘ │ │ password_hash│ └──────┬──────┘
│ │ avatar │ │
│ │ bio │ │ M:N
│ │ status │ │
│ │ created_at │ ┌──────┴──────┐
│ │ updated_at │ │article_tags │
│ └──────┬───────┘ │─────────────│
│ │ │ article_id │
│ │ 1:N │ tag_id │
│ │ └──────┬──────┘
│ ┌──────┴───────┐ │
│ │ articles │ │
│ │──────────────│◄───────────────┘
└────►│ article_id │
│ user_id (FK) │
│ category_id │
│ title │ ┌──────────────┐
│ summary │ │ comments │
│ content │ │──────────────│
│ cover_image │ ┌───►│ comment_id │
│ view_count │ │ │ article_id │
│ status │ │ │ user_id (FK) │
│ is_top │ │ │ parent_id │──┐
│ created_at │ │ │ content │ │
│ updated_at │ │ │ status │ │
└──────┬───────┘ │ │ created_at │ │
│ │ └──────────────┘ │
│ 1:N │ ▲ │
└────────────┘ │ 自关联 │
└──────────┘8.4 完整建表 SQL
sql
CREATE DATABASE blog_system DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE blog_system;
CREATE TABLE users (
user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
avatar VARCHAR(255) DEFAULT NULL,
bio VARCHAR(500) DEFAULT NULL,
status TINYINT UNSIGNED NOT NULL DEFAULT 1,
article_count INT UNSIGNED NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_username (username),
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB;
CREATE TABLE categories (
category_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
sort_order INT UNSIGNED NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_name (name)
) ENGINE=InnoDB;
CREATE TABLE tags (
tag_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
article_count INT UNSIGNED NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_name (name)
) ENGINE=InnoDB;
CREATE TABLE articles (
article_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED DEFAULT NULL,
title VARCHAR(200) NOT NULL,
summary VARCHAR(500) DEFAULT NULL,
content MEDIUMTEXT NOT NULL,
cover_image VARCHAR(255) DEFAULT NULL,
view_count INT UNSIGNED NOT NULL DEFAULT 0,
comment_count INT UNSIGNED NOT NULL DEFAULT 0,
status TINYINT UNSIGNED NOT NULL DEFAULT 0,
is_top TINYINT UNSIGNED NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id),
KEY idx_user_id (user_id),
KEY idx_category_id (category_id),
KEY idx_status_created (status, created_at),
KEY idx_is_top_created (is_top, created_at)
) ENGINE=InnoDB;
CREATE TABLE article_tags (
article_id INT UNSIGNED NOT NULL,
tag_id INT UNSIGNED NOT NULL,
PRIMARY KEY (article_id, tag_id),
FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(tag_id) ON DELETE CASCADE,
KEY idx_tag_id (tag_id)
) ENGINE=InnoDB;
CREATE TABLE comments (
comment_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
article_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
parent_id INT UNSIGNED DEFAULT NULL,
content TEXT NOT NULL,
status TINYINT UNSIGNED NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (article_id) REFERENCES articles(article_id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (parent_id) REFERENCES comments(comment_id) ON DELETE CASCADE,
KEY idx_article_created (article_id, created_at),
KEY idx_user_id (user_id),
KEY idx_parent_id (parent_id)
) ENGINE=InnoDB;8.5 索引设计分析
索引设计思路：
┌──────────────────────────────────────────────────────────────────┐
│ 索引设计清单 │
├──────────────┬───────────────────────────┬───────────────────────┤
│ 表 │ 索引 │ 覆盖的查询场景 │
├──────────────┼───────────────────────────┼───────────────────────┤
│ users │ uk_username │ 登录时按用户名查找 │
│ │ uk_email │ 邮箱唯一性校验 │
├──────────────┼───────────────────────────┼───────────────────────┤
│ articles │ idx_user_id │ 查看某用户的所有文章 │
│ │ idx_category_id │ 按分类筛选文章 │
│ │ idx_status_created │ 文章列表(状态+时间排序) │
│ │ idx_is_top_created │ 置顶文章查询 │
├──────────────┼───────────────────────────┼───────────────────────┤
│ article_tags │ PK(article_id, tag_id) │ 查某文章的所有标签 │
│ │ idx_tag_id │ 查某标签下的所有文章 │
├──────────────┼───────────────────────────┼───────────────────────┤
│ comments │ idx_article_created │ 文章详情页的评论列表 │
│ │ idx_user_id │ 某用户的所有评论 │
│ │ idx_parent_id │ 查询评论的子回复 │
└──────────────┴───────────────────────────┴───────────────────────┘8.6 典型查询与优化
查询 1：文章列表（首页分页）
sql
SELECT a.article_id, a.title, a.summary, a.cover_image,
a.view_count, a.comment_count, a.created_at,
u.username, u.avatar
FROM articles a
INNER JOIN users u ON a.user_id = u.user_id
WHERE a.status = 1
ORDER BY a.is_top DESC, a.created_at DESC
LIMIT 20;查询 2：按标签查文章
sql
SELECT a.article_id, a.title, a.summary, a.created_at
FROM article_tags at_rel
INNER JOIN articles a ON at_rel.article_id = a.article_id
WHERE at_rel.tag_id = 5
AND a.status = 1
ORDER BY a.created_at DESC
LIMIT 20;查询 3：文章详情（含评论）
sql
SELECT a.article_id, a.title, a.content, a.view_count,
a.created_at, a.updated_at,
u.username, u.avatar
FROM articles a
INNER JOIN users u ON a.user_id = u.user_id
WHERE a.article_id = 1001;
SELECT c.comment_id, c.content, c.parent_id, c.created_at,
u.username, u.avatar
FROM comments c
INNER JOIN users u ON c.user_id = u.user_id
WHERE c.article_id = 1001
AND c.status = 1
ORDER BY c.created_at ASC
LIMIT 50;查询 4：浏览量更新（高频写入优化）
sql
UPDATE articles SET view_count = view_count + 1 WHERE article_id = 1001;高并发场景下的优化思路：
方案对比：
┌──────────────┬─────────────────────────────┬─────────────────┐
│ 方案 │ 实现 │ 适用场景 │
├──────────────┼─────────────────────────────┼─────────────────┤
│ 直接 UPDATE │ 每次访问写数据库 │ 低流量 │
│ Redis 缓存 │ INCR 计数，定期同步到 MySQL │ 中等流量 │
│ 消息队列 │ 异步消费写入 │ 高流量 │
└──────────────┴─────────────────────────────┴─────────────────┘javascript
const Redis = require('ioredis');
const redis = new Redis();
async function incrementViewCount(articleId) {
const key = `article:views:${articleId}`;
await redis.incr(key);
}
async function syncViewCountsToDB(pool) {
const keys = await redis.keys('article:views:*');
for (const key of keys) {
const articleId = key.split(':')[2];
const count = await redis.getdel(key);
if (count && Number(count) > 0) {
await pool.execute(
'UPDATE articles SET view_count = view_count + ? WHERE article_id = ?',
[Number(count), articleId]
);
}
}
}8.7 Node.js 数据访问层示例
javascript
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'password',
database: 'blog_system',
waitForConnections: true,
connectionLimit: 20,
queueLimit: 0
});
async function getArticleList(page = 1, pageSize = 20) {
const offset = (page - 1) * pageSize;
const [rows] = await pool.execute(
`SELECT a.article_id, a.title, a.summary, a.cover_image,
a.view_count, a.comment_count, a.created_at,
u.username, u.avatar
FROM articles a
INNER JOIN users u ON a.user_id = u.user_id
WHERE a.status = 1
ORDER BY a.is_top DESC, a.created_at DESC
LIMIT ? OFFSET ?`,
[pageSize, offset]
);
const [countResult] = await pool.execute(
'SELECT COUNT(*) AS total FROM articles WHERE status = 1'
);
return {
list: rows,
total: countResult[0].total,
page,
pageSize,
totalPages: Math.ceil(countResult[0].total / pageSize)
};
}
async function createArticleWithTags(userId, articleData, tagIds) {
const conn = await pool.getConnection();
try {
await conn.beginTransaction();
const [articleResult] = await conn.execute(
`INSERT INTO articles (user_id, category_id, title, summary, content, cover_image, status)
VALUES (?, ?, ?, ?, ?, ?, ?)`,
[
userId,
articleData.categoryId,
articleData.title,
articleData.summary,
articleData.content,
articleData.coverImage,
articleData.status || 0
]
);
const articleId = articleResult.insertId;
if (tagIds && tagIds.length > 0) {
const tagValues = tagIds.map(tagId => [articleId, tagId]);
await conn.query(
'INSERT INTO article_tags (article_id, tag_id) VALUES ?',
[tagValues]
);
for (const tagId of tagIds) {
await conn.execute(
'UPDATE tags SET article_count = article_count + 1 WHERE tag_id = ?',
[tagId]
);
}
}
await conn.execute(
'UPDATE users SET article_count = article_count + 1 WHERE user_id = ?',
[userId]
);
await conn.commit();
return articleId;
} catch (err) {
await conn.rollback();
throw err;
} finally {
conn.release();
}
}
async function getArticlesByTag(tagId, lastArticleId = 0, pageSize = 20) {
let sql, params;
if (lastArticleId > 0) {
sql = `SELECT a.article_id, a.title, a.summary, a.created_at
FROM article_tags at_rel
INNER JOIN articles a ON at_rel.article_id = a.article_id
WHERE at_rel.tag_id = ?
AND a.status = 1
AND a.article_id < ?
ORDER BY a.article_id DESC
LIMIT ?`;
params = [tagId, lastArticleId, pageSize];
} else {
sql = `SELECT a.article_id, a.title, a.summary, a.created_at
FROM article_tags at_rel
INNER JOIN articles a ON at_rel.article_id = a.article_id
WHERE at_rel.tag_id = ?
AND a.status = 1
ORDER BY a.article_id DESC
LIMIT ?`;
params = [tagId, pageSize];
}
const [rows] = await pool.execute(sql, params);
return rows;
}九、知识体系总览
┌─────────────────────────────────────────────────────────────────┐
│ 数据库设计知识体系 │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ 范式理论 │ │ ER 建模 │ │ 索引原理 │ │ 事务机制 │ │
│ │ │ │ │ │ │ │ │ │
│ │ 1NF/2NF │ │ 实体 │ │ B+ 树 │ │ ACID │ │
│ │ 3NF/BCNF │ │ 关系 │ │ 聚簇索引 │ │ 隔离级别 │ │
│ │ 反范式化 │ │ 外键级联 │ │ 联合索引 │ │ MVCC │ │
│ └──────────┘ └──────────┘ │ 覆盖索引 │ └──────────┘ │
│ └──────────┘ │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ 锁机制 │ │ 查询优化 │ │ 分库分表 │ │
│ │ │ │ │ │ │ │
│ │ 共享/排他 │ │ EXPLAIN │ │ 垂直拆分 │ │
│ │ 行锁/表锁│ │ 慢查询 │ │ 水平拆分 │ │
│ │ 乐观/悲观│ │ N+1 问题 │ │ 一致性哈希│ │
│ │ 死锁避免 │ │ 深分页 │ │ 雪花算法 │ │
│ └──────────┘ └──────────┘ └──────────┘ │
│ │
│ ┌─────────────────────────────────────────────┐ │
│ │ 实战: 博客系统 │ │
│ │ 需求分析 → ER图 → 建表 → 索引 → 查询优化 │ │
│ └─────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘十、面试高频问题
题目 1：请解释数据库三范式及其关系，什么时候需要反范式化？
考查点： 范式理论基础 + 工程实践判断
第一范式要求字段不可再分（原子性）；第二范式在 1NF 基础上消除非主属性对联合主键的部分依赖；第三范式在 2NF 基础上消除非主属性间的传递依赖。三者层层递进，每一级都以前一级为前提。当系统读多写少、JOIN 成为瓶颈、分布式环境跨库 JOIN 困难时，可以适度反范式化（冗余高频读取字段），但需要做好冗余数据的同步策略。
题目 2：B+ 树相比 B 树有什么优势？为什么 MySQL 选择 B+ 树做索引？
考查点： 索引数据结构原理
B+ 树所有数据都存储在叶子节点，非叶子节点只存索引键值，单个节点能容纳更多索引项，树更矮，IO 次数更少。叶子节点通过双向链表连接，支持高效范围查询和顺序遍历。B 树的数据分散在所有节点中，范围查询需要中序遍历，效率较低。数据库操作的瓶颈在于磁盘 IO，B+ 树更矮的树高意味着更少的磁盘访问次数。
题目 3：什么是覆盖索引？如何判断查询是否使用了覆盖索引？
考查点： 索引优化能力
覆盖索引是指查询所需的所有字段都包含在索引中，无需回表访问主键索引获取完整行数据。可通过 EXPLAIN 的 Extra 字段判断，如果显示 "Using index" 则说明使用了覆盖索引。设计联合索引时可以将高频查询的 SELECT 字段纳入索引来实现覆盖。
题目 4：MySQL 默认隔离级别是什么？MVCC 是如何工作的？
考查点： 事务与隔离级别、MVCC 原理
MySQL InnoDB 默认隔离级别为 REPEATABLE READ。MVCC 通过为每行数据维护版本链（undo log）和 ReadView 快照来实现非锁定读。每行有隐藏的 DB_TRX_ID 和 DB_ROLL_PTR 字段，分别记录最后修改的事务 ID 和指向旧版本的指针。事务在第一次 SELECT 时创建 ReadView，记录当前活跃事务列表，后续读取时根据可见性规则从版本链中找到可见的版本。RR 级别整个事务复用同一个 ReadView，RC 级别每次 SELECT 都创建新的 ReadView。
题目 5：请描述乐观锁和悲观锁的区别及适用场景
考查点： 并发控制策略
悲观锁假设冲突频繁发生，先加锁再操作（如 SELECT ... FOR UPDATE），适合写多场景。乐观锁假设冲突很少发生，操作时不加锁，提交时通过版本号/CAS 检测冲突，冲突则重试，适合读多写少场景。电商库存扣减适合悲观锁保证准确；社交内容点赞适合乐观锁提高并发。
题目 6：EXPLAIN 中 type 字段从好到差的排列是什么？出现 ALL 说明什么？
考查点： 慢查询分析能力
从好到差依次为：system > const > eq_ref > ref > range > index > ALL。出现 ALL 说明进行了全表扫描，即数据库需要读取表中每一行数据来完成查询，这通常意味着缺少合适的索引或查询条件导致索引失效，需要优化。目标是至少达到 range 级别。
题目 7：深分页（如 LIMIT 1000000, 10）为什么慢？有哪些优化方案？
考查点： 查询优化实战
LIMIT offset, size 需要扫描 offset + size 行再丢弃前 offset 行，offset 越大扫描量越大。优化方案：(1) 游标分页，基于上次查询的最后一个 ID 用 WHERE id > last_id LIMIT size，性能稳定为 O(1)；(2) 延迟关联，子查询中只走覆盖索引获取 ID，再关联获取完整数据；(3) 对于搜索类需求使用 Elasticsearch 等专用搜索引擎。
题目 8：什么是 N+1 查询问题？如何解决？
考查点： ORM 性能优化
查询一个列表需要 1 次 SQL，然后对列表中每条记录关联查询需要 N 次 SQL，总共 N+1 次。解决方案：(1) JOIN 预加载，一次 SQL 获取所有关联数据；(2) 批量 IN 查询，先收集所有关联 ID 再一次性查出；(3) DataLoader 模式，自动合并同一 tick 内的查询请求并提供缓存。在 GraphQL 场景下 DataLoader 尤其重要。
十一、延伸阅读
书籍推荐：
| 书名 | 侧重方向 | 推荐理由 |
|---|---|---|
| 《高性能 MySQL》（第4版） | MySQL 全面深入 | 索引、查询优化、复制、分区等核心主题详尽 |
| 《MySQL 技术内幕：InnoDB 存储引擎》 | InnoDB 原理 | 深入讲解 B+ 树、MVCC、锁、事务实现 |
| 《数据库系统概念》（第7版） | 数据库理论 | 范式理论、关系代数、事务处理的经典教材 |
| 《Designing Data-Intensive Applications》 | 分布式数据系统 | 分片、复制、一致性等分布式核心主题 |
| 《SQL Antipatterns》 | SQL 反模式 | 常见数据库设计错误及纠正方案 |
在线资源：
- MySQL 官方文档 - InnoDB 索引
- Use The Index, Luke —— 索引优化专题（多数据库通用）
- MySQL Explain 完全解读
- Percona Database Performance Blog —— 数据库性能优化实战
进阶主题：
| 主题 | 说明 |
|---|---|
| 读写分离 | 主从复制 + 应用层路由，读请求分散到从库 |
| 分布式事务 | 2PC、TCC、SAGA 模式处理跨库事务一致性 |
| NewSQL | TiDB、CockroachDB 等兼顾分布式与 SQL 的新型数据库 |
| 列式存储 | ClickHouse、Apache Parquet 用于 OLAP 分析场景 |
| 数据库中间件 | ShardingSphere、Vitess 等透明化分库分表方案 |
| 缓存一致性 | Cache Aside / Read Through / Write Behind 等缓存策略 |