MySQL 从零到精通验收清单

这页专门回答一个问题：这些 MySQL 文档到底能不能让零基础学到能做项目、能讲原理、能过面试、能排查线上问题？

只说“能”没有意义，必须有可验证标准。你学完 MySQL 专栏后，至少要能独立完成下面这些任务：

根据业务需求设计表、字段、约束和索引。
写出增删改查、分页、Join、聚合、事务 SQL。
解释一条 select 从客户端到 InnoDB 读页的全过程。
解释一条 update 从加锁、undo、Buffer Pool、redo、binlog 到提交的全过程。
看懂 EXPLAIN，知道 type、key、rows、filtered、Extra 从哪里来。
解释 B+Tree、聚簇索引、二级索引、回表、覆盖索引、最左前缀。
解释事务、锁、MVCC、Read View、undo log 的关系。
解释 redo log、binlog、两阶段提交、主从复制和崩溃恢复。
排查慢 SQL、锁等待、死锁、主从延迟、大表深分页、覆盖索引仍慢。
面试时能先给标准回答，再跳到原理页讲清为什么。

如果只能背“索引用 B+Tree、事务 ACID、MVCC 多版本”，但不能解释为什么、不这样会怎样、线上怎么排查，就还没有达到目标。

总路线

mermaid

flowchart TD
    A["阶段 1<br/>SQL 和表设计"] --> B["阶段 2<br/>索引和 B+Tree"]
    B --> C["阶段 3<br/>执行计划和优化器"]
    C --> D["阶段 4<br/>事务、锁、MVCC"]
    D --> E["阶段 5<br/>undo、redo、binlog"]
    E --> F["阶段 6<br/>主从、备份、大表治理"]
    F --> G["阶段 7<br/>生产排查"]
    G --> H["阶段 8<br/>面试表达"]

为什么必须按这个顺序？

顺序	原因	如果跳过
先 SQL	数据库最终执行的是 SQL	后面看执行计划没有对象
再表设计	表结构决定索引、事务和扩展性	字段混乱，后续优化很难
再索引	索引决定扫描范围和排序方式	慢 SQL 只能乱加索引
再执行计划	证明数据库到底怎么查	无法解释有索引为什么不用
再事务并发	商业系统最怕数据错	支付、库存、状态流转容易错
再日志恢复	提交、回滚、复制、恢复都靠日志	宕机和主从不一致讲不清
最后排查	生产问题通常是多机制叠加	只会重启或猜原因

阶段 1：SQL 和表设计

必须掌握

知识点	必须会什么	原理页
字段类型	`bigint`、`varchar`、`decimal`、`datetime`、`tinyint` 的选择	SQL 基础
主键	为什么 InnoDB 推荐递增主键	存储结构
唯一约束	为什么数据库层必须兜底唯一性	表设计
非空和默认值	为什么不要把数据正确性全交给 Java	表设计
状态字段	为什么状态要可控，不能随便字符串	从零到生产级掌握
时间字段	为什么 `created_at`、`updated_at` 对排查和归档重要	表设计

商业 Demo：订单和支付表

sql

create table payment_order (
  id bigint primary key auto_increment,
  order_no varchar(64) not null,
  user_id bigint not null,
  pay_channel varchar(32) not null,
  status tinyint not null,
  amount decimal(12, 2) not null,
  paid_at datetime null,
  created_at datetime not null default current_timestamp,
  updated_at datetime not null default current_timestamp on update current_timestamp,
  unique key uk_order_no (order_no),
  key idx_user_status_created (user_id, status, created_at)
) engine = InnoDB default charset = utf8mb4;

为什么这样设计：

设计	为什么	不这样会怎样
`id bigint auto_increment`	主键递增，聚簇索引插入更友好	随机主键容易页分裂，二级索引也更大
`order_no unique`	防止重复支付单	并发请求可能写入重复业务单
`amount decimal`	金额不能用浮点	`double` 可能出现精度误差
`status tinyint`	状态机可控且索引小	状态乱写，统计和流转不可控
`idx_user_status_created`	支撑用户订单列表	用户订单页可能扫描大量数据

验收任务

你应该能独立完成：

设计一张 inventory_stock 库存表，包含商品 ID、可用库存、冻结库存、版本号、更新时间。
给订单号、资产编号、外部流水号设计唯一约束。
解释为什么金额不用 double。
解释为什么数据库约束不能只靠 Java 校验替代。

阶段 2：索引和 B+Tree

索引到底是什么

索引不是“加速按钮”，而是数据库额外维护的一份有序结构。它能让查询少扫数据，但会增加写入成本、空间成本和维护成本。

mermaid

flowchart TD
    A["业务查询<br/>where user_id and status"] --> B{"有没有匹配索引"}
    B -- "没有" --> C["扫描大量数据页"]
    B -- "有" --> D["从 B+Tree 根页向下定位"]
    D --> E["找到叶子页范围"]
    E --> F["顺序扫描少量索引项"]
    F --> G{"返回列是否都在索引里"}
    G -- "是" --> H["覆盖索引返回"]
    G -- "否" --> I["回聚簇索引取整行"]

必须掌握

知识点	必须会什么	原理页
B+Tree	为什么树高低、页分叉多、叶子有序	B+Tree 与索引家族
聚簇索引	为什么主键叶子节点保存整行	存储结构
二级索引	为什么叶子节点保存主键值	索引
回表	为什么查非索引列要再走主键树	索引
覆盖索引	为什么能减少回表，但不是万能	大表覆盖索引仍然慢
联合索引	为什么有最左前缀和列顺序	索引
索引失效	为什么函数、隐式转换、前导模糊可能导致扫更多	优化相关

Demo：联合索引顺序

sql

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

explain
select id, order_no, amount
from payment_order
where user_id = 1001
  and status = 1
order by created_at desc
limit 20;

这个索引适合上面的 SQL，因为：

先按 user_id 缩小到某个用户。
再按 status 缩小到某类订单。
最后按 created_at 顺序取最近 20 条。

如果查询变成：

sql

explain
select id, order_no, amount
from payment_order
where status = 1
order by created_at desc
limit 20;

idx_user_status_created 就不一定合适，因为最前面的 user_id 没有条件。联合索引不是三个单列索引，顺序由 B+Tree 的排序方式决定。

验收任务

你应该能讲清：

为什么 B+Tree 比红黑树更适合数据库。
为什么 Hash 索引不适合作通用主力索引。
为什么二级索引查询完整行要回表。
为什么覆盖索引仍可能慢。
为什么联合索引 (a,b,c) 不等于三个单列索引。

阶段 3：执行计划和优化器

EXPLAIN 不是背字段

EXPLAIN 是优化器基于 SQL、表结构、索引、统计信息和成本模型推导出的执行计划。它不是普通查询实际执行完后的统计结果。

mermaid

flowchart TD
    A["SQL 文本"] --> B["解析和语义检查"]
    B --> C["读取表结构和索引"]
    C --> D["读取统计信息"]
    D --> E["枚举可能访问路径"]
    E --> F["估算行数、过滤率和成本"]
    F --> G["选择成本较低的计划"]
    G --> H["输出 EXPLAIN 字段"]

必须掌握

字段	必须理解	详细页
`type`	访问方式，不是越背越好，要结合表大小	EXPLAIN
`possible_keys`	候选索引，不代表会用	EXPLAIN
`key`	优化器最终选择的索引	EXPLAIN
`key_len`	联合索引用到了多少字节	EXPLAIN
`rows`	预估扫描行数，来自统计信息	EXPLAIN
`filtered`	存储引擎取出后剩余条件预计过滤比例	EXPLAIN
`Extra`	额外操作，如排序、临时表、覆盖索引	EXPLAIN

Demo：慢 SQL 分析

sql

explain
select id, order_no, amount
from payment_order
where user_id = 1001
  and status = 1
order by created_at desc
limit 100000, 20;

即使使用了覆盖索引，深分页仍可能慢。原因不是“索引没用”，而是数据库仍然要跳过前 100000 条符合条件的索引记录。

优化方式：

sql

select id, order_no, amount
from payment_order
where user_id = 1001
  and status = 1
  and created_at < '2026-07-06 10:00:00'
order by created_at desc
limit 20;

这叫游标分页或基于上次边界的分页。它让数据库从某个确定位置继续向后扫，而不是从头跳过大量记录。

验收任务

你应该能独立回答：

EXPLAIN rows 是真实扫描行数吗？
为什么统计信息不准会导致优化器选错索引？
为什么 Using index 不等于一定快？
为什么 Using filesort 不一定必然慢，但大数据量要警惕？
如何用慢日志、EXPLAIN ANALYZE、optimizer_trace 交叉验证？

阶段 4：事务、锁、MVCC

为什么事务不是只背 ACID

事务的价值是让一组数据库操作在并发和故障下仍然正确。例如支付成功后：

更新订单状态。
写支付流水。
更新账户余额。
写消息发送记录。

这些动作不能一半成功一半失败。

mermaid

flowchart TD
    A["支付回调"] --> B["开启事务"]
    B --> C["按订单号查询并加锁"]
    C --> D{"订单是否待支付"}
    D -- "否" --> E["幂等返回"]
    D -- "是" --> F["更新订单已支付"]
    F --> G["写支付流水"]
    G --> H["写本地消息表"]
    H --> I["提交事务"]
    I --> J["异步投递 MQ"]

必须掌握

知识点	必须理解	原理页
ACID	原子性靠 undo，隔离性靠锁/MVCC，持久性靠 redo	事务
隔离级别	RU、RC、RR、Serializable 的现象差异	事务
当前读	`update`、`delete`、`for update` 读最新并加锁	锁机制
快照读	普通 `select` 通过 Read View 读可见版本	MVCC
行锁	InnoDB 行锁加在索引记录上	锁机制
间隙锁	RR 下范围保护，防止当前读幻读	锁机制
死锁	多事务互相等待对方锁	锁机制
长事务	undo 旧版本无法清理，影响 MVCC	MVCC

MVCC 和 undo 的关系

mermaid

flowchart TD
    A["当前记录"] --> B["隐藏字段 trx_id"]
    A --> C["隐藏字段 roll_pointer"]
    C --> D["undo log 旧版本 1"]
    D --> E["undo log 旧版本 2"]
    F["Read View"] --> G{"当前版本是否可见"}
    G -- "可见" --> A
    G -- "不可见" --> D

关键结论：

MVCC 不是复制整张表。
当前记录通过 roll_pointer 找到 undo log 旧版本。
Read View 决定哪个版本对当前事务可见。
undo log 既用于回滚，也用于 MVCC 历史版本。
长事务会让旧版本不能清理，导致 undo 压力。

验收任务

你应该能做实验：

开两个会话，事务 A 更新一行不提交，事务 B 更新同一行观察阻塞。
在 RR 下，事务 A 普通 select 两次，事务 B 中间提交更新，观察快照读一致。
在同样场景使用 select ... for update，观察当前读和锁等待。
故意让两个事务按相反顺序更新两行，观察死锁。

阶段 5：undo、redo、binlog

三种日志不要混

日志	所属层	解决什么问题	和事务的关系
undo log	InnoDB	回滚、MVCC 旧版本	事务修改前写旧值
redo log	InnoDB	崩溃恢复，保证提交不丢	事务提交必须保证 redo 可恢复
binlog	Server 层	主从复制、时间点恢复、审计	记录逻辑变更

update 提交流程

mermaid

flowchart TD
    A["UPDATE 命中记录"] --> B["加行锁"]
    B --> C["写 undo 旧版本"]
    C --> D["修改 Buffer Pool 数据页"]
    D --> E["写 redo log record"]
    E --> F["redo prepare"]
    F --> G["写 binlog"]
    G --> H["redo commit"]
    H --> I["事务提交成功"]

为什么 redo 和 binlog 要两阶段提交？

异常点	如果没有协调	后果
redo 成功，binlog 失败	主库恢复有数据，从库没日志	主从不一致
binlog 成功，redo 失败	从库可能重放，主库恢复没有	主从不一致
两者状态不明	崩溃恢复无法判断提交状态	数据正确性风险

验收任务

你应该能回答：

为什么事务回滚靠 undo，不靠 redo？
为什么 redo 是物理页修改日志，binlog 是逻辑变更日志？
为什么 binlog 能做主从复制，redo 不能直接替代 binlog？
为什么事务提交成功不代表数据页已经刷盘？
为什么两阶段提交能保证 redo 和 binlog 一致？

阶段 6：主从、备份、大表治理

主从复制流程

mermaid

flowchart TD
    A["主库提交事务"] --> B["写 binlog"]
    B --> C["从库 IO 线程拉取 binlog"]
    C --> D["写 relay log"]
    D --> E["从库 SQL 线程重放"]
    E --> F["从库数据追上主库"]

主从延迟常见原因：

原因	解释	处理方向
主库大事务	从库必须重放很久	拆小事务
从库慢 SQL	复制线程被拖慢	优化从库资源和查询
写入高峰	relay log 堆积	并行复制、削峰
网络抖动	binlog 拉取慢	网络和机房排查
从库资源低	CPU/IO 不足	升级规格或隔离查询

大表治理决策

mermaid

flowchart TD
    A["单表变慢"] --> B{"是 SQL 和索引问题吗"}
    B -- "是" --> C["优化 SQL、联合索引、分页方式"]
    B -- "否" --> D{"历史数据是否可归档"}
    D -- "是" --> E["冷热归档或分区"]
    D -- "否" --> F{"访问是否可按分片键拆分"}
    F -- "是" --> G["分库分表"]
    F -- "否" --> H["引入搜索、报表库或架构改造"]

分库分表不是慢 SQL 的第一选择。先确认不是索引、深分页、锁等待、统计信息、历史数据未归档造成的慢。

验收任务

你应该能讲清：

主从复制为什么依赖 binlog。
写后读从库为什么可能读不到最新数据。
备份为什么要配合 binlog 做时间点恢复。
大表为什么不能只靠覆盖索引。
什么时候归档，什么时候分区，什么时候分库分表。

阶段 7：生产排查

慢 SQL 排查流程

mermaid

flowchart TD
    A["接口慢"] --> B{"SQL 慢还是代码慢"}
    B -- "代码慢" --> C["查接口链路和远程调用"]
    B -- "SQL 慢" --> D["查慢日志"]
    D --> E["用真实参数 EXPLAIN"]
    E --> F{"扫描行数是否大"}
    F -- "是" --> G["查索引、过滤条件、分页"]
    F -- "否" --> H{"是否锁等待或 IO 慢"}
    H -- "锁等待" --> I["查事务和锁"]
    H -- "IO 慢" --> J["查 Buffer Pool、磁盘、回表"]
    G --> K["优化后复测"]
    I --> K
    J --> K

常见现象和判断

现象	不要立刻认为	应该检查
`key` 有值但慢	索引一定没问题	`rows`、回表、排序、锁等待
`Using index` 但慢	覆盖索引一定快	扫描范围、深分页、缓存命中
偶发慢	数据库随机坏	锁等待、IO 抖动、参数值、缓存未命中
主从延迟	只要加从库	大事务、从库慢查询、并行复制
死锁	数据库异常	更新顺序、索引命中、事务范围
连接满	数据库连接太少	连接泄漏、慢 SQL、事务太长

验收任务

你应该能拿到一条慢 SQL 后完成：

用真实参数跑 EXPLAIN。
判断 type、key、rows、filtered、Extra。
对比慢日志中的 Rows_examined 和 Rows_sent。
判断是否索引问题、深分页、排序问题、锁等待或 IO 问题。
给出优化方案，并说明写入成本和风险。

阶段 8：面试表达

面试页只负责标准回答，详细原理必须跳回知识点页。

高频面试题	标准回答页	深入原理页
MySQL 有几层	MySQL 面试	架构与请求全过程
为什么用 B+Tree	MySQL 面试	B+Tree 与索引家族
EXPLAIN rows 怎么来的	MySQL 面试	EXPLAIN
MVCC 和 undo 关系	MySQL 面试	MVCC
redo 和 binlog 区别	MySQL 面试	redo log 与 binlog
覆盖索引仍慢怎么办	MySQL 面试	大表覆盖索引仍然慢
分库分表怎么设计	MySQL 面试	分库分表
MySQL容器化怎么保证数据安全	MySQL 面试	初始化、Volume、备份与升级

面试回答模板

text

我学习 MySQL 不会只背 SQL 语法，而是按业务建模、索引、执行计划、事务并发、日志恢复、主从复制和生产排查这条线理解。

比如一条查询 SQL，先经过连接层认证和权限检查，再由 Server 层解析、预处理、优化器选择执行计划，执行器调用 InnoDB，InnoDB 根据 B+Tree、Buffer Pool、MVCC 和锁读取数据。慢 SQL 排查时不能只看有没有索引，要结合 EXPLAIN 的 type、key、rows、filtered、Extra，以及慢日志、Rows_examined、锁等待和 IO。

比如一条 update，不只是改一行数据，还涉及索引定位、加行锁、写 undo、修改 Buffer Pool、写 redo、写 binlog 和两阶段提交。undo 支持回滚和 MVCC 旧版本，redo 支持崩溃恢复，binlog 支持主从复制和时间点恢复。理解这些流程后，才能解释事务、主从延迟、死锁、大表慢查询和数据恢复问题。

最终验收题

如果下面这些问题都能不看答案讲清，并能回到对应原理页解释流程，MySQL 才算真正进入“能上项目、能面试、能排查”的水平。

问题	合格标准
设计订单表	字段、约束、索引、状态、金额、时间都合理
解释 B+Tree	能从页、树高、范围扫描、叶子链表解释
解释联合索引	能用排序规则解释最左前缀
看 EXPLAIN	能说明字段来源和优化方向
解释 MVCC	能说清当前记录、undo、Read View
解释锁	能区分快照读、当前读、行锁、间隙锁
解释 update	能串起锁、undo、Buffer Pool、redo、binlog
解释主从	能讲 binlog、relay log、重放和延迟
排查慢 SQL	能按证据判断扫描、排序、回表、锁、IO
处理大表	能在索引、归档、分区、分库分表之间选择

本章小结

“从零到精通”不是看了多少页，而是能不能把业务需求 -> 表设计 -> SQL -> 索引 -> 执行计划 -> 事务并发 -> 日志恢复 -> 主从高可用 -> 生产排查 -> 面试表达串成闭环。

如果某个问题讲不清，不要回去背答案，而是跳到对应知识点页重新看原理和 Demo。真正会 MySQL 的标志，是你能解释数据库为什么这样工作，以及不这样设计会出现什么后果。

JDK 8/11/17/21 LTS演进

线程池

MySQL 从零到精通验收清单 ​

总路线 ​

阶段 1：SQL 和表设计 ​

必须掌握 ​

商业 Demo：订单和支付表 ​

验收任务 ​

阶段 2：索引和 B+Tree ​

索引到底是什么 ​

必须掌握 ​

Demo：联合索引顺序 ​

验收任务 ​

阶段 3：执行计划和优化器 ​

EXPLAIN 不是背字段 ​

必须掌握 ​

Demo：慢 SQL 分析 ​

验收任务 ​

阶段 4：事务、锁、MVCC ​

为什么事务不是只背 ACID ​

必须掌握 ​

MVCC 和 undo 的关系 ​

验收任务 ​

阶段 5：undo、redo、binlog ​

三种日志不要混 ​

update 提交流程 ​

验收任务 ​

阶段 6：主从、备份、大表治理 ​

主从复制流程 ​

大表治理决策 ​

验收任务 ​

阶段 7：生产排查 ​

慢 SQL 排查流程 ​

常见现象和判断 ​

验收任务 ​

阶段 8：面试表达 ​

面试回答模板 ​

最终验收题 ​

本章小结 ​

MySQL 从零到精通验收清单

总路线

阶段 1：SQL 和表设计

必须掌握

商业 Demo：订单和支付表

验收任务

阶段 2：索引和 B+Tree

索引到底是什么

必须掌握

Demo：联合索引顺序

验收任务

阶段 3：执行计划和优化器

EXPLAIN 不是背字段

必须掌握

Demo：慢 SQL 分析

验收任务

阶段 4：事务、锁、MVCC

为什么事务不是只背 ACID

必须掌握

MVCC 和 undo 的关系

验收任务

阶段 5：undo、redo、binlog

三种日志不要混

update 提交流程

验收任务

阶段 6：主从、备份、大表治理

主从复制流程

大表治理决策

验收任务

阶段 7：生产排查

慢 SQL 排查流程

常见现象和判断

验收任务

阶段 8：面试表达

面试回答模板

最终验收题

本章小结