MySQL 系统学习文档

适合人群：已经掌握基础后端开发，希望系统学习关系型数据库、SQL、InnoDB、索引、事务和生产实践的学习者。

学习目标：从 MySQL 基础 SQL 入门，逐步掌握表设计、约束、查询、事务、InnoDB、索引、锁、执行计划、备份恢复、权限和项目接入。

核对时间：2026-06-25。MySQL 采用「创新版 + LTS」双轨发布：8.4 与 9.7 为 LTS 版本，9.7 之后改用日历版本号（如 26.7 即 2026 年 7 月发布的创新版）。学习基础和生产实践时，应优先掌握稳定通用能力，并结合项目所用版本查官方文档。

官方资料：

• MySQL Reference Manual：https://dev.mysql.com/doc/mysql/en/
• MySQL Tutorial：https://dev.mysql.com/doc/refman/en/tutorial.html
• InnoDB Introduction：https://dev.mysql.com/doc/refman/en/innodb-introduction.html
• InnoDB Indexes：https://dev.mysql.com/doc/refman/en/innodb-indexes.html
• InnoDB Transaction Model：https://dev.mysql.com/doc/refman/en/innodb-transaction-model.html

---

目录

第一篇：基础认知

1. MySQL 是什么
2. MySQL 和 PostgreSQL 的定位差异
3. Server、Database、Table、Row、Column
4. MySQL 版本和存储引擎
5. 安装与连接

第二篇：SQL 基础

6. 数据类型
7. 创建数据库和表
8. 增删改查
9. 条件、排序、分页
10. 聚合和分组
11. 多表 JOIN

第三篇：表设计

12. 主键、唯一约束和外键
13. 字符集与排序规则
14. 时间、金额和 JSON 字段
15. 一对一、一对多、多对多
16. 范式、反范式和软删除

第四篇：InnoDB、索引与事务

17. InnoDB 是什么
18. 聚簇索引和主键设计
19. 二级索引和联合索引
20. 最左前缀原则
21. 事务 ACID
22. 隔离级别、锁和 MVCC
23. EXPLAIN 和慢查询优化

第五篇：生产实践

24. 用户和权限
25. 备份与恢复
26. 迁移和上线变更
27. Node.js/Prisma 接入
28. 常见误区
29. 学习路线和练习项目

---

第一篇：基础认知

1. MySQL 是什么

MySQL 是非常流行的开源关系型数据库，广泛用于网站、后台系统、电商、内容管理系统、企业应用和互联网业务。

它的特点：

• 使用 SQL 管理结构化数据。
• 生态成熟，资料多，运维经验丰富。
• InnoDB 支持事务、行级锁、崩溃恢复和外键。
• 与 PHP、Java、Node.js、Go 等后端生态结合广泛。
• 在读写型业务系统中应用非常多。

学习 MySQL 时要抓住两个层次：

1. SQL 层：表、查询、JOIN、聚合、事务。
2. InnoDB 层：索引、锁、MVCC、事务日志、存储结构。

只会写 SQL 还不够。想写出高性能、可靠的业务系统，必须理解 InnoDB 的索引和事务行为。

2. MySQL 和 PostgreSQL 的定位差异

二者都是关系型数据库，都支持 SQL、事务、索引和复杂查询。但常见工程感受不同：

• MySQL 生态极其普及，很多业务系统和中小团队默认选择 MySQL。
• PostgreSQL 更强调 SQL 标准、复杂查询、扩展能力和高级数据类型。
• MySQL 的 InnoDB 聚簇索引、字符集、隔离级别默认行为等是学习重点。
• PostgreSQL 的 MVCC、JSONB、数组、窗口函数、扩展和执行计划能力很强。

不要用“哪个更高级”这种方式理解。正确方式是看业务场景、团队经验、运维体系和数据模型。

3. Server、Database、Table、Row、Column

MySQL 层级：

MySQL Server
└── database/schema
    └── table
        ├── row
        └── column

MySQL 中 database 和 schema 在日常使用中基本可以近似理解为同一个概念。

示例：

create database appdb;
use appdb;

create table users (
  id bigint primary key auto_increment,
  email varchar(255) not null unique,
  name varchar(100) not null
);

4. MySQL 版本和存储引擎

MySQL 当前有不同版本线。生产项目应根据公司规范选择稳定版本，常见选择是 LTS 版本。

存储引擎是 MySQL 的重要概念。常见引擎：

• InnoDB：默认主流引擎，支持事务、行锁、外键。
• MyISAM：旧引擎，不支持事务，现代业务系统通常不建议使用。
• Memory：数据存内存，适合特殊临时场景。

学习和生产基本都围绕 InnoDB。

查看表引擎：

show table status like 'users';

建表指定：

create table users (
  id bigint primary key auto_increment
) engine=InnoDB;

5. 安装与连接

Docker 示例：

docker run --name mysql-demo \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -e MYSQL_DATABASE=appdb \
  -e MYSQL_USER=app \
  -e MYSQL_PASSWORD=secret \
  -p 3306:3306 \
  -d mysql:8.4

连接：

mysql -h 127.0.0.1 -P 3306 -u app -p appdb

连接字符串：

mysql://app:secret@localhost:3306/appdb

常用命令：

show databases;
use appdb;
show tables;
describe users;
show create table users;

---

第二篇：SQL 基础

6. 数据类型

数字：

tinyint
int
bigint
decimal(10, 2)
double

字符串：

varchar(255)
text
char(36)

时间：

date
datetime
timestamp

布尔：

boolean

MySQL 中 boolean 通常等价于 tinyint(1)。

JSON：

json

选择建议：

• 主键常用 bigint unsigned auto_increment。
• 金额使用 decimal，不要用 float/double。
• 短字符串用 varchar。
• 长文本用 text。
• 时间字段常用 datetime 或 timestamp，团队内统一即可。
• JSON 适合扩展字段，不要替代表关系。

7. 创建数据库和表

创建数据库：

create database appdb
  character set utf8mb4
  collate utf8mb4_0900_ai_ci;

创建用户表：

create table users (
  id bigint unsigned primary key auto_increment,
  email varchar(255) not null unique,
  name varchar(100) not null,
  created_at datetime not null default current_timestamp,
  updated_at datetime not null default current_timestamp on update current_timestamp
) engine=InnoDB;

创建文章表：

create table posts (
  id bigint unsigned primary key auto_increment,
  author_id bigint unsigned not null,
  title varchar(255) not null,
  content text,
  published boolean not null default false,
  created_at datetime not null default current_timestamp,
  constraint fk_posts_author
    foreign key (author_id) references users(id)
) engine=InnoDB;

8. 增删改查

插入：

insert into users (email, name)
values ('alice@example.com', 'Alice');

查询：

select id, email, name
from users;

更新：

update users
set name = 'Alice Chen'
where id = 1;

删除：

delete from users
where id = 1;

更新和删除必须带明确条件。生产环境中误执行无条件 update/delete 是严重事故。

9. 条件、排序、分页

条件查询：

select *
from posts
where published = true
  and created_at >= date_sub(now(), interval 7 day);

模糊搜索：

select *
from posts
where title like '%mysql%';

排序：

select *
from posts
order by created_at desc;

分页：

select *
from posts
order by id desc
limit 20 offset 40;

大分页优化：

select *
from posts
where id < 1000
order by id desc
limit 20;

limit offset 在 offset 很大时可能扫描和丢弃大量数据。游标分页通常更适合高数据量场景。

10. 聚合和分组

统计：

select count(*) from users;

分组统计：

select author_id, count(*) as post_count
from posts
group by author_id;

筛选聚合结果：

select author_id, count(*) as post_count
from posts
group by author_id
having count(*) >= 10;

where 过滤分组前的行，having 过滤分组后的结果。

11. 多表 JOIN

内连接：

select posts.id, posts.title, users.name as author_name
from posts
join users on users.id = posts.author_id;

左连接：

select users.id, users.name, posts.title
from users
left join posts on posts.author_id = users.id;

多表 JOIN 要注意：

• JOIN 字段是否有索引。
• 返回行数是否被多对多关系放大。
• 是否真的需要返回所有列。
• 是否应该分步查询或预聚合。

---

第三篇：表设计

12. 主键、唯一约束和外键

主键：

id bigint unsigned primary key auto_increment

唯一约束：

email varchar(255) not null unique

外键：

constraint fk_posts_author
  foreign key (author_id) references users(id)

外键能保证关系一致，但在一些高并发互联网业务中，团队可能选择在应用层维护关系，减少外键带来的变更和锁复杂度。学习阶段建议先理解外键，再根据团队规范使用。

13. 字符集与排序规则

MySQL 字符集非常重要。现代项目通常使用 utf8mb4，它能完整支持 Unicode，包括 emoji。

创建数据库时：

create database appdb
  character set utf8mb4
  collate utf8mb4_0900_ai_ci;

概念：

• character set：字符如何编码。
• collation：字符串如何比较和排序。

常见问题：

• 字符集不一致导致乱码。
• 排序规则不同导致查询结果差异。
• 大小写敏感规则不符合预期。

项目开始时就应统一数据库、表、连接和应用的字符集。

14. 时间、金额和 JSON 字段

金额：

amount decimal(12, 2) not null

或存最小单位：

amount_cents bigint not null

时间：

created_at datetime not null default current_timestamp
updated_at datetime not null default current_timestamp on update current_timestamp

JSON：

metadata json

JSON 查询：

select *
from events
where json_extract(payload, '$.device') = 'desktop';

注意：JSON 字段灵活，但过度使用会弱化约束、索引和关系建模。

14.1 JSON 索引的两种机制

MySQL 不能直接对 JSON 路径建普通索引，有两条路：

-- 路线一：生成列（generated column）+ 普通索引
alter table events
  add column device varchar(32)
    generated always as (json_unquote(json_extract(payload, '$.device'))) stored,
  add index idx_device (device);

-- 路线二（8.0.17+）：多值索引，直接索引 JSON 数组
create index idx_tags on articles ((cast(metadata -> '$.tags' as char(64) array)));

💡 生成列本质是把 JSON 值"提取成一等公民列"，查询优化器能像普通列一样统计和使用它；多值索引专门服务数组包含查询（json_contains、member of），但约束多（只能建在二级索引、数组元素类型必须显式 cast）。高频查询字段应优先考虑生成列或干脆建普通列。

15. 一对一、一对多、多对多

一对多：

create table posts (
  id bigint unsigned primary key auto_increment,
  author_id bigint unsigned not null,
  title varchar(255) not null,
  foreign key (author_id) references users(id)
);

一对一：

create table user_profiles (
  user_id bigint unsigned primary key,
  bio text,
  avatar_url varchar(500),
  foreign key (user_id) references users(id)
);

多对多：

create table tags (
  id bigint unsigned primary key auto_increment,
  name varchar(100) not null unique
);

create table post_tags (
  post_id bigint unsigned not null,
  tag_id bigint unsigned not null,
  primary key (post_id, tag_id),
  foreign key (post_id) references posts(id),
  foreign key (tag_id) references tags(id)
);

多对多中间表的复合主键可以防止重复关系。

16. 范式、反范式和软删除

范式减少重复和不一致。反范式提升查询效率或保留历史快照。

订单系统中，订单项通常要保存商品名称和价格快照：

create table order_items (
  id bigint unsigned primary key auto_increment,
  order_id bigint unsigned not null,
  product_id bigint unsigned not null,
  product_name varchar(255) not null,
  unit_price decimal(10, 2) not null,
  quantity int not null
);

因为商品以后改名或改价，不应该影响历史订单。

软删除：

deleted_at datetime null

查询时：

where deleted_at is null

软删除适合需要恢复、审计、合规的场景。但它会增加查询条件、唯一约束和数据归档复杂度。

16.1 唯一约束与软删除的冲突及解法

软删除和 unique 约束天生冲突：email 上有唯一索引时，用户软删除后重新注册同一邮箱会直接报重复键。常见解法：

-- 解法一：把删除标记并入唯一索引，deleted_at 为 NULL 时用 0 占位
alter table users
  add column deleted_ts bigint unsigned not null default 0,
  drop index email,
  add unique key uk_email_deleted (email, deleted_ts);

-- 解法二（MySQL 8.0.13+）：函数索引模拟部分唯一索引
-- 仅未删除行参与唯一性：对已删除行表达式恒为 NULL，唯一索引忽略 NULL
create unique index uk_email_active
  on users ((if(deleted_at is null, email, null)));

⚠️ utf8mb4 下索引有长度上限：InnoDB 在 DYNAMIC 行格式（8.0 默认）下单列索引键最长 3072 字节，utf8mb4 一个字符最多 4 字节，因此 varchar(255) 直接建索引（255×4=1020 字节）没问题，但组合多个长 varchar 列时要估算总字节数，超限会报错，需要改前缀索引或缩短列长。

---

第四篇：InnoDB、索引与事务

17. InnoDB 是什么

InnoDB 是 MySQL 默认且最重要的存储引擎。

它支持：

• 事务
• 行级锁
• 外键
• 崩溃恢复
• MVCC
• 聚簇索引

理解 MySQL 性能问题，大多数时候就是理解 InnoDB 如何组织数据、如何使用索引、如何加锁。

17.1 InnoDB 内部结构总览

InnoDB 由「内存结构 + 磁盘结构」组成，数据修改先落日志、后刷页，这是它兼顾性能与崩溃恢复的关键。

InnoDB
├── 内存结构
│   ├── Buffer Pool：缓存数据页与索引页（默认 128MB，生产常用物理内存 50%~70%）
│   ├── Change Buffer：缓存对二级索引的修改，减少随机 I/O
│   └── Adaptive Hash Index：对热点页建立哈希索引，加速等值查找
└── 磁盘结构
    ├── 表空间：.ibd 文件（每张表一个，每页默认 16KB）
    ├── redo log：InnoDB 层事务日志，保证崩溃恢复（D）
    ├── undo log：回滚日志，支撑回滚（A）与 MVCC（I）
    ├── binlog：Server 层归档日志，用于复制与恢复（不属于 InnoDB）
    └── doublewrite buffer：两次写缓冲，防止页断裂

redo log 的核心机制是 WAL（Write-Ahead Logging）：修改数据页之前必须先写 redo log 并持久化，这样即使数据页还没刷盘、数据库崩溃，也能重做（redo）出已提交事务的修改。MySQL 8.0.30 起用 innodb_redo_log_capacity 统一控制 redo 容量（默认 100MB，可在线调整，写入量大的生产库通常明显调大）。

💡 一次 UPDATE 的完整路径：undo log 记录旧值 → 在 Buffer Pool 中修改页（此时页变「脏」）→ 写 redo log → 提交时按 innodb_flush_log_at_trx_commit 决定刷盘强度 → 脏页由后台 checkpoint 异步刷盘；如果涉及二级索引的随机页修改，还可能先写入 Change Buffer 延迟合并。

⚠️ 双一配置：innodb_flush_log_at_trx_commit=1 且 sync_binlog=1（redo 与 binlog 都每次提交刷盘）才是最高持久化级别。放宽为 2 或 0 会提升写入吞吐，但崩溃时可能丢失最近约 1 秒的事务，账资类系统不要用。

17.2 为什么是 B+树，而不是哈希或二叉树

InnoDB 索引使用 B+树，原因要对比着理解：

结构	问题
哈希表	等值查询 O(1) 很快，但不支持范围查询和排序；MySQL Memory 引擎用哈希，InnoDB 仅在 Adaptive Hash Index 中局部使用
二叉搜索树 / 红黑树	树太高，一次查找可能几十次磁盘页访问；数据库 I/O 单位是页，不是字节
B 树	key 与 data 混存于所有节点，非叶节点占空间大、扇出低
B+树	非叶节点只存 key 和页号，扇出极大；数据全在叶子节点；叶子节点双向链表相连，天然支持范围扫描

一个 16KB 的索引页，内部节点大约能放几百到上千个「key + 页号」，因此千万行级的表通常只有 3~4 层：一次主键查询最多 3~4 次页访问，且除叶子页外基本都常驻 Buffer Pool。这也解释了 18 节「主键尽量短」的原因——主键越短，每个非叶节点能放的条目越多，树越矮。

18. 聚簇索引和主键设计

InnoDB 表的数据按照主键组织，主键索引就是聚簇索引。可以粗略理解为：主键索引的叶子节点存放整行数据。

这意味着主键设计很重要。

常见建议：

• 主键尽量短。
• 主键尽量稳定，不要更新。
• 主键最好有序增长，减少页分裂。
• 常见业务系统可使用 bigint unsigned auto_increment。

UUID 主键也可以用，但随机 UUID 可能导致插入分散，影响索引局部性。若需要全局唯一，可以考虑有序 UUID、雪花 ID 或数据库外 ID 生成方案。

18.1 主键选择的内部机制

按官方规则，InnoDB 确定聚簇索引的顺序是：

1. 有 PRIMARY KEY 就用它。
2. 没有主键则选第一个所有列都 NOT NULL 的 UNIQUE 索引。
3. 都没有则 InnoDB 自动生成隐藏的 GEN_CLUST_INDEX，按 6 字节自增 DB_ROW_ID 组织。

由此可推出两个工程结论：

• 主键长 = 所有二级索引变大。每条二级索引记录都携带主键列，主键选 36 字符 UUID 时，每个二级索引条目都要多存这 36 字节。
• 页分裂与页合并。B+树叶子页写满后插入新行会触发页分裂（一分为二、父节点加索引项），随机主键让分裂频繁发生；顺序主键基本只在尾部追加。大量删除后页稀疏又可能触发合并，产生碎片，必要时用 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 重建。

-- 观察碎片：数据长度/索引长度与表统计
select table_name, data_length, index_length, data_free
from information_schema.tables
where table_schema = 'appdb';

19. 二级索引和联合索引

二级索引不是聚簇索引。InnoDB 二级索引叶子节点通常保存索引列和主键值。通过二级索引找到主键后，可能还要回到聚簇索引查整行，这叫回表。

创建索引：

create index idx_posts_author_id on posts(author_id);

联合索引：

create index idx_posts_author_created
on posts(author_id, created_at);

覆盖索引：查询需要的字段都在索引里，可以减少回表。

select author_id, created_at
from posts
where author_id = 1
order by created_at desc;

如果索引包含 author_id, created_at，这个查询可能更高效。

19.1 回表与索引维护的内部机制

• 回表为什么可能慢：二级索引扫描得到的每个主键都要再到 B+树上做一次独立查找，本质是随机 I/O。命中行少时代价可接受，命中行多时优化器可能直接放弃二级索引改走全表扫描。
• 索引合并（index_merge）：多个单列索引的 AND/OR 条件可能被优化器合并使用，但它是「没有更好选择」时的补救，不如一个设计正确的联合索引。
• 二级索引的更新方式：聚簇索引记录原地更新，而更新二级索引列时，旧索引记录被 delete-mark、插入新记录，被标记删除的记录之后由 purge 线程清理。这就是「宽索引 + 高频更新列」写入放大的来源。
• 覆盖索引失效场景：若二级索引记录被 delete-mark 或其页被更新事务修改过，InnoDB 无法直接从二级索引返回数据，必须回聚簇索引确认版本，即使查询在形式上是覆盖查询。

20. 最左前缀原则

联合索引：

create index idx_user_status_created
on orders(user_id, status, created_at);

适合：

where user_id = 1
where user_id = 1 and status = 'paid'
where user_id = 1 and status = 'paid' order by created_at

不适合充分使用：

where status = 'paid'
where created_at > '2026-01-01'

因为没有从最左列 user_id 开始。

索引设计要围绕查询模式，而不是看到字段就加索引。

20.1 最左前缀的边界情况

• 范围条件截断后续列：where user_id = 1 and status = 'paid' and created_at > '2026-01-01' 能完整用上三列；但 where user_id = 1 and status > 'a' and created_at = 'x' 里 status 是范围条件，created_at 无法再参与定位，只能作为过滤条件（可借助 ICP 在索引层提前过滤）。
• 区间排序要求方向一致：order by created_at desc 对 (user_id, status, created_at) 是友好的；混合方向排序（如 order by a asc, b desc）在传统上需要额外 filesort，8.0 支持降序索引后可通过建 (a asc, b desc) 的索引解决。
• 索引跳跃扫描（Skip Scan）：8.0 起，当最左列基数很低（如性别、布尔状态）且查询缺了它时，优化器可能对联合索引做 skip scan，但这依赖统计信息和成本估算，不能当常规手段设计。
• 前缀索引：对长字符串可用 create index idx_title on posts(title(20)) 只索引前 20 个字符，省空间，但前缀索引不能作为覆盖索引使用。

21. 事务 ACID

事务示例：

start transaction;

update accounts
set balance = balance - 100
where id = 1;

update accounts
set balance = balance + 100
where id = 2;

commit;

失败回滚：

rollback;

ACID：

• 原子性：全部成功或全部失败。
• 一致性：数据满足约束。
• 隔离性：并发事务互相隔离。
• 持久性：提交后不会轻易丢失。

订单支付、库存扣减、余额变动必须使用事务。

21.1 ACID 靠什么实现

四个字母背后是具体机制，面试和生产排障都常问：

特性	InnoDB 的实现手段
Atomicity 原子性	undo log：回滚时按日志逆向恢复
Consistency 一致性	约束 + redo/undo + 崩溃恢复共同保证
Isolation 隔离性	锁 + MVCC
Durability 持久性	redo log WAL + innodb_flush_log_at_trx_commit 刷盘策略

💡 两阶段提交（2PC）：开启 binlog 后，一个事务的提交要走「prepare（redo 落盘，标记 XID）→ 写 binlog → commit（redo 标记提交）」的协调流程，保证 redo 与 binlog 的一致。崩溃恢复时若 redo 已 prepare 但 binlog 不完整则回滚，binlog 完整则提交——这正是主从数据可能因参数配置不一致而漂移的根源，所以「双一」不只是性能话题，也是数据正确性话题。

22. 隔离级别、锁和 MVCC

MySQL InnoDB 支持多个隔离级别，常见默认是 Repeatable Read。

查看：

select @@transaction_isolation;

设置：

set transaction isolation level read committed;

常见锁：

• 行锁
• 间隙锁
• next-key lock
• 表锁

显式加锁：

select *
from products
where id = 1
for update;

MVCC 让读写并发更高。普通快照读通常不阻塞写，当前读如 for update 会涉及锁。

学习重点：

• 普通 select 和 select for update 行为不同。
• 索引会影响锁范围。
• 没有合适索引时，更新可能锁住更多行。
• 长事务会导致 undo 版本无法及时清理。

22.1 隔离级别与异常矩阵

SQL 标准定义了三种读异常（脏读、不可重复读、幻读），InnoDB 的表现如下：

隔离级别	脏读	不可重复读	幻读（快照读）	说明
READ UNCOMMITTED	可能	可能	可能	几乎不用于生产
READ COMMITTED	不会	可能	可能	语句级快照；Oracle/PG 默认风格
REPEATABLE READ	不会	不会	基本不会	InnoDB 默认；快照读靠 MVCC，当前读靠 next-key lock 防幻读
SERIALIZABLE	不会	不会	不会	普通读也加锁，并发吞吐显著下降

⚠️ InnoDB 的 RR 并不严格等同 SQL 标准的 Repeatable Read：它对「当前读」（for update、update、insert）用间隙锁/next-key lock 防止幻影行插入，但先快照读再当前读的混合场景仍可能观察到幻影行。需要完全串行化语义时用 Serializable。

22.2 MVCC 与 ReadView 的实现机制

InnoDB 给每行隐式追加三个隐藏列：

隐藏列	大小	作用
DB_TRX_ID	6 字节	最后修改该行的事务 ID
DB_ROLL_PTR	7 字节	指向 undo log 中的上一版本，构成版本链
DB_ROW_ID	6 字节	行 ID；无主键时生成隐藏聚簇索引 GEN_CLUST_INDEX 使用

UPDATE/DELETE 把旧版本写入 undo log（rollback segment），形成版本链；快照读通过 ReadView 决定哪个版本可见。ReadView 记录创建时刻仍未提交的事务集合：

• m_ids：生成本视图时处于活跃（未提交）状态的事务 ID 列表。
• up_limit_id（min_trx_id）：m_ids 中的最小值。
• low_limit_id（max_trx_id）：视图创建时系统将分配的下一个事务 ID。
• creator_trx_id：创建该视图的事务自己的 ID。

版本可见性判断（对版本链从最新版本往回走）：

1. trx_id == creator_trx_id        → 可见（自己改的）
2. trx_id <  up_limit_id           → 可见（快照前已提交）
3. trx_id >= low_limit_id          → 不可见（快照后才开启的事务）
4. up_limit_id <= trx_id < low_limit_id：
     trx_id 在 m_ids 中 → 不可见（快照时仍未提交）
     不在 m_ids 中     → 可见（快照时已提交）
不可见则沿 DB_ROLL_PTR 找上一版本，直到找到可见版本；
若版本链走完仍不可见，说明该行当时不存在。

RC 与 RR 的本质差别只有一处：ReadView 的生成时机。

隔离级别	ReadView 生成时机	效果
READ COMMITTED	每条快照读语句都重新生成	总能看到最新已提交数据，所以可重复读不保证
REPEATABLE READ	事务内第一条快照读生成，之后复用	整个事务看到同一快照

💡 undo log 的清理（purge）：insert undo 在事务提交后即可丢弃；update undo 还要服务快照读，只有当没有任何 ReadView 可能引用它时才被 purge 线程回收。这就是「长事务导致 undo 膨胀」的机制根源——一个挂着的老 ReadView 会拖住整段版本链无法清理。

22.3 锁的实现细节

InnoDB 行锁全部加在索引记录上（没有合适索引时锁住隐藏聚簇索引，近似锁全表），常见类型：

锁类型	粒度/含义	典型场景
S 锁 / X 锁	共享/排他行锁	for share / for update、DML
IS / IX 意向锁	表级意向锁，标记「表内有人锁行」	与 LOCK TABLES 互斥检测
Record Lock	锁单个索引记录	唯一索引等值命中
Gap Lock	锁索引记录之间的空隙，只阻止插入	RR 下范围扫描
Next-Key Lock	Record + Gap，前开后闭区间 (gap, record]	RR 下默认锁定单位
Insert Intention Lock	插入前对间隙持有的特殊意向锁	并发插入定位

关键行为：

• 唯一索引等值查询命中 → 退化为 record lock，不加间隙锁；未命中 → 锁住间隙防插入。
• READ COMMITTED 基本不使用 gap lock（仅外键与重复键检查例外），锁冲突更少、并发更高，这也是很多互联网公司把默认隔离级别改为 RC 的原因。
• 死锁排查：select * from performance_schema.data_locks;（8.0 取代 innodb_locks），最近一次死锁详情看 show engine innodb status 的 LATEST DETECTED DEADLOCK 段；死锁检测由 innodb_deadlock_detect 控制。

23. EXPLAIN 和慢查询优化

查看执行计划：

explain
select *
from posts
where author_id = 1
order by created_at desc
limit 20;

关注：

• type：访问类型。
• key：实际使用索引。
• rows：预计扫描行数。
• Extra：是否 Using filesort、Using temporary。

慢查询优化流程：

1. 打开慢查询日志或定位慢接口。
2. 拿到真实 SQL 和参数。
3. 使用 EXPLAIN 分析。
4. 检查 where/order by/join 字段索引。
5. 优化 SQL、索引或分页方式。
6. 压测验证，不靠猜。

常见优化：

• 避免 select *。
• 为高频过滤字段建索引。
• 为 join 字段建索引。
• 避免函数包裹索引列导致索引失效。
• 避免大 offset 分页。

23.1 EXPLAIN 深入

type 列从好到差的常见档位（官方排序，逐级递减）：

system > const > eq_ref > ref > range > index > ALL

type	含义	典型触发
const	主键/唯一索引等值，最多一行	where id = 1
eq_ref	join 时对侧表用唯一索引命中	主外键 join
ref	非唯一索引等值	where author_id = 1
range	索引范围扫描	>、between、in
index	全索引扫描（顺序读整棵索引）	覆盖索引 + 无过滤
ALL	全表扫描	无可用索引

更有用的信息在增强输出里：

explain analyze
select * from posts where author_id = 1 order by created_at desc limit 20;

8.0.18+ 的 EXPLAIN ANALYZE 会真正执行查询并报告每个节点的实测耗时与行数，与估算 rows 对比即可发现统计信息失真；explain format=tree select ... 输出树形计划，可读性更好。Extra 中重点看：Using index（覆盖索引，好）、Using index condition（ICP 下推，好）、Using filesort（内存/磁盘排序，看排序缓冲）、Using temporary（临时表，group by 常见，需审视）。

23.2 索引失效的机制解释

「索引失效」的本质是优化器判断走索引比不走更贵，或索引结构根本无法服务该条件：

写法	为什么失效
where year(created_at) = 2026	函数破坏了索引的有序性，改写为范围条件 created_at >= '2026-01-01' and created_at < '2027-01-01'
where id + 1 = 2	同上；MySQL 8.0.13+ 的函数索引（create index ... ((expr))）可救部分场景
like '%mysql%'	前导通配符无法定位起点；like 'mysql%' 可用索引
隐式类型转换	varchar 列用数字比较时，转换发生在列上（等于包了函数）
字符集不一致 join	两侧列 charset/collation 不同会阻止索引使用，甚至引发性能灾难
低选择率 + 需回表	优化器按统计信息估算命中比例过高时主动选全表扫描（顺序 I/O 便宜于随机回表）

慢查询定位入口：慢日志（slow_query_log=ON、long_query_time=1）+ pt-query-digest 聚合分析；统计信息可通过 analyze table posts; 手动更新。

---

第五篇：生产实践

24. 用户和权限

创建用户：

create user 'app'@'%' identified by 'secret';

授权：

grant select, insert, update, delete
on appdb.*
to 'app'@'%';

刷新：

flush privileges;

原则：

• 应用账号不要用 root。
• 不同环境使用不同账号。
• 按最小权限授权。
• 不要把密码提交到代码仓库。

25. 备份与恢复

逻辑备份：

mysqldump -h 127.0.0.1 -u app -p appdb > backup.sql

恢复：

mysql -h 127.0.0.1 -u app -p appdb < backup.sql

生产还要考虑：

• 定时备份
• binlog
• 主从复制
• 恢复演练
• 备份加密
• 数据保留周期

只备份不演练恢复，风险很高。

25.1 binlog 与时间点恢复

mysqldump 默认会锁表保证一致性，对 InnoDB 应使用一致性快照备份（原理就是 22 节的 MVCC 快照读）：

mysqldump --single-transaction --source-data=2 -u app -p appdb > backup.sql

binlog 有三种格式：ROW（记录行变更，主从一致性和精确恢复最好，默认推荐）、STATEMENT（记录 SQL，函数类语句可能不安全）、MIXED（自动切换）。误删数据的时间点恢复流程：恢复最近全量备份 → 用 mysqlbinlog --start-position/--stop-position 重放 binlog 到误操作前一刻。物理备份工具 Percona XtraBackup（热备、接近裸拷贝速度）适合大库，逻辑备份适合小库和跨版本迁移。

26. 迁移和上线变更

数据库变更要版本化。可以使用：

• Prisma Migrate
• Flyway
• Liquibase
• Knex migrations

危险变更：

• 大表加索引
• 删除字段
• 修改字段类型
• 添加非空字段但没有默认值
• 大批量 update

安全策略：

• 分阶段上线。
• 先加字段，再双写，再切读，再删旧字段。
• 大表操作评估锁和耗时。
• 生产前备份。
• 低峰期执行高风险变更。

26.1 Online DDL 机制

MySQL 5.6+ 的 InnoDB 支持 Online DDL：alter table ... algorithm=inplace, lock=none 允许大部分表结构变更期间并发读写。三种算法代价从高到低：

算法	行为	例子
COPY	复制整表，期间阻塞写	改主键、enum 变更（部分）
INPLACE	原地重建/追加元数据，配合 lock=none 可并发 DML，DDL 首尾仍短暂持元数据锁（MDL）	加二级索引、加列（8.0.12+ Instant 例外）
INSTANT	只改元数据，秒级完成	8.0.12+ 加列到表尾

⚠️ 两个实践陷阱：一是 MDL——长查询未结束时执行 DDL 会被阻塞，并且排队在其后的所有查询一起被卡住（「DDL 引发的雪崩」），应设置 lock_wait_timeout 并避开长事务；二是 INPLACE 期间对 DML 的记录会写入一个在线 alter 临时日志（由 innodb_online_alter_log_max_size 限制），日志溢出会导致 DDL 失败重来。超大表变更在生产常用 gh-ost、pt-online-schema-change 等外部工具替代。

27. Node.js/Prisma 接入

使用 mysql2：

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  uri: process.env.DATABASE_URL,
});

const [rows] = await pool.execute(
  "select * from users where id = ?",
  [id]
);

Prisma：

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

const user = await prisma.user.findUnique({
  where: { id: 1 },
});

即使用 Prisma，也要理解 MySQL 的索引、事务和锁，因为性能问题最终发生在数据库层。

28. 常见误区

误区一：MySQL 不需要懂存储引擎。

纠正：InnoDB 决定事务、索引和锁行为，是进阶核心。

误区二：索引越多越好。

纠正：索引提升查询，但增加写入和存储成本。

误区三：联合索引随便建。

纠正：要结合最左前缀和真实查询模式。

误区四：ORM 会自动优化数据库。

纠正：ORM 只是生成查询。索引、事务、慢查询仍需数据库知识。

误区五：limit offset 永远没问题。

纠正：大 offset 分页会越来越慢，需考虑游标分页。

29. 学习路线和练习项目

第一阶段：SQL 基础

• create table
• select/insert/update/delete
• where/order by/limit
• group by/join

第二阶段：表设计

• 主键、唯一约束、外键
• 一对多、多对多
• 字符集和时间字段

第三阶段：InnoDB

• 聚簇索引
• 二级索引
• 联合索引
• 最左前缀
• 回表和覆盖索引

第四阶段：事务和锁

• ACID
• 隔离级别
• select for update
• 死锁排查

第五阶段：生产实践

• EXPLAIN
• 慢查询日志
• 备份恢复
• 权限管理
• 迁移上线

练习项目：

电商订单系统
- users
- products
- orders
- order_items
- payments
- inventory_logs

重点练习：

• 订单事务
• 库存扣减
• 金额 decimal
• 联合索引
• 慢查询优化

---

快速记忆清单

• MySQL 是关系型数据库，InnoDB 是核心存储引擎。
• 现代项目字符集优先使用 utf8mb4。
• 金额用 decimal，不要用浮点数。
• InnoDB 数据按主键聚簇组织，主键设计很重要。
• 联合索引遵循最左前缀原则。
• 查询慢要看 EXPLAIN，不要凭感觉加索引。
• 事务要短，避免在事务中做慢操作。
• 应用账号不要使用 root，生产变更必须谨慎。