MySQL 从零到生产级掌握

MySQL 不能只学成“会写增删改查”。真正到商业项目里，你要能解释：为什么表要这样设计，为什么索引能快，为什么有索引仍然慢，为什么事务能回滚，为什么普通查询不阻塞更新，为什么提交后宕机数据还能回来，为什么主从会延迟，为什么单表大了覆盖索引也可能慢。

一句话建立主线：

MySQL 的核心是：Server 层负责 SQL 语义、优化器和 binlog，InnoDB 负责页、B+Tree、Buffer Pool、事务、锁、MVCC、undo log、redo log。学习 MySQL 就是把一条 SQL 从业务代码到磁盘和日志的全过程讲清楚。

学习目标

学完这一页，你要能做到：

从零创建数据库、表、字段、约束和索引。
根据业务查询设计联合索引，而不是乱建单列索引。
解释一条 select 从连接器、解析器、优化器、执行器到 InnoDB 读页的全过程。
解释一条 update 为什么涉及行锁、undo、Buffer Pool、redo、binlog 和两阶段提交。
看懂 EXPLAIN 中的 type、key、rows、filtered、Extra，并知道这些值从哪里来。
解释 B+Tree、聚簇索引、二级索引、回表、覆盖索引、最左前缀。
解释事务 ACID、隔离级别、锁、MVCC、Read View、undo log 的关系。
排查慢 SQL、锁等待、死锁、主从延迟、日志刷盘慢、大表深分页和覆盖索引仍慢。

如果你已经读完本页，但还不知道自己是否能真正上手，继续做：MySQL 商业场景训练营。它用订单支付、库存扣减、大表覆盖索引、锁等待、死锁、update 日志链路和主从延迟，把这里的原理变成可执行练习。

学习路线

mermaid

flowchart TD
    A["SQL 基础<br/>增删改查、过滤、排序、分页"] --> B["表设计<br/>类型、约束、范式、反范式"]
    B --> C["索引<br/>B+Tree、联合索引、覆盖索引"]
    C --> D["执行计划<br/>EXPLAIN、优化器、统计信息"]
    D --> E["事务并发<br/>ACID、锁、MVCC、隔离级别"]
    E --> F["日志恢复<br/>undo、redo、binlog、两阶段提交"]
    F --> G["高可用<br/>主从、备份、恢复"]
    G --> H["生产治理<br/>慢 SQL、大表、分库分表、排查"]

这条路线不能乱。比如你还没理解表设计，就会把所有字段都建成 varchar；你还没理解联合索引，就会给每个字段单独建索引；你还没理解 MVCC，就会背隔离级别但解释不了快照读；你还没理解 redo/binlog，就讲不清主从和崩溃恢复。

第一步：从业务建表开始

以医疗资产平台为例。业务要管理医院设备资产：资产编号唯一、所属医院、所属科室、当前状态、创建时间、更新时间。

建表：

sql

create table medical_asset (
  id bigint primary key auto_increment,
  asset_no varchar(64) not null,
  hospital_id bigint not null,
  department_id bigint null,
  asset_name varchar(128) not null,
  status varchar(20) not null,
  created_at datetime not null default current_timestamp,
  updated_at datetime not null default current_timestamp on update current_timestamp,
  unique key uk_asset_no (asset_no),
  key idx_hospital_status_created (hospital_id, status, created_at),
  constraint ck_asset_status
    check (status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
) engine = InnoDB default charset = utf8mb4;

为什么这样设计：

设计	原因	不这样会怎样
`bigint auto_increment` 主键	递增主键适合 InnoDB 聚簇索引	随机 UUID 做主键容易页分裂、索引更大
`asset_no` 唯一索引	业务编号不能重复	并发导入可能产生重复资产
`hospital_id,status,created_at` 联合索引	支持医院 + 状态 + 时间列表	列表页可能全表扫描或 filesort
`utf8mb4`	支持完整 Unicode	表情或特殊字符可能保存失败
`check`	限制状态合法	脏状态进入数据库，后续统计异常

数据库约束不是可有可无。Java 校验解决用户体验，数据库约束解决最终数据正确性。脚本、导入程序、补偿任务都可能绕过接口写库，所以必须有数据库层防线。

第二步：一条 SELECT 怎么执行

查询某医院使用中的资产：

sql

select id, asset_no, asset_name, status
from medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc
limit 20;

执行流程：

mermaid

flowchart TD
    A["客户端发送 SQL"] --> B["连接器<br/>认证、权限、连接状态"]
    B --> C["解析器<br/>词法、语法分析"]
    C --> D["预处理器<br/>检查表和字段"]
    D --> E["优化器<br/>选择索引和执行计划"]
    E --> F["执行器<br/>调用 InnoDB 接口"]
    F --> G["InnoDB 访问 B+Tree"]
    G --> H{"Buffer Pool 是否有页"}
    H -- "有" --> I["内存读页"]
    H -- "没有" --> J["磁盘读页到 Buffer Pool"]
    J --> I
    I --> K["返回记录给 Server 层"]
    K --> L["排序、Limit、返回客户端"]

每一步要理解：

环节	做什么	常见问题
连接器	用户认证、连接管理、权限	连接数满、连接池泄漏
解析器	判断 SQL 语法	SQL 写错直接失败
预处理器	检查表列是否存在	字段不存在、权限不足
优化器	估算成本，选索引和 Join 顺序	统计信息不准、索引不合适
执行器	按计划取数据	扫描太多、回表太多
InnoDB	B+Tree、页、Buffer Pool、MVCC	IO 高、锁等待、版本链长

第三步：B+Tree、聚簇索引和二级索引

InnoDB 表本身就是按主键组织的一棵 B+Tree，这叫聚簇索引。

mermaid

flowchart TD
    A["聚簇索引 Root Page"] --> B["Internal Page"]
    B --> C["Leaf Page"]
    C --> D["完整数据行"]

二级索引叶子节点保存的是索引列和主键值，不是完整行。

mermaid

flowchart TD
    A["二级索引 idx_hospital_status_created"] --> B["找到索引记录"]
    B --> C["拿到主键 id"]
    C --> D["回到聚簇索引查整行"]
    D --> E["返回完整记录"]

这就是回表。比如：

sql

select id, asset_no, asset_name
from medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc
limit 20;

如果二级索引中没有 asset_name，就可能需要回表拿完整行。覆盖索引就是查询字段都能从二级索引拿到，减少回表。

但是覆盖索引不是万能：

覆盖索引只减少回表，不减少必须扫描的索引范围。
如果 where 条件范围很大，仍然要扫描很多索引页。
如果深分页 limit 1000000, 20，即使覆盖索引也要跳过大量记录。
索引太宽会增加写入成本和 Buffer Pool 压力。

第四步：联合索引为什么有顺序

联合索引不是多个单列索引的简单拼接，而是按列顺序排序的复合结构。

索引：

sql

key idx_hospital_status_created (hospital_id, status, created_at)

可以很好服务：

sql

where hospital_id = 1001
  and status = 'USED'
order by created_at

原因：

B+Tree 先按 hospital_id 排序。
同一个 hospital_id 下再按 status 排序。
同一个 hospital_id + status 下再按 created_at 排序。
找到范围起点后可以顺序扫描。

如果查询是：

sql

where status = 'USED'
order by created_at

这个索引的 hospital_id 没有用上，不能直接跳到所有 status='USED' 的范围，因为索引第一排序维度不是 status。

最左前缀不是口诀，而是 B+Tree 排序方式决定的。

第五步：EXPLAIN 要看什么

sql

explain
select id, asset_no, asset_name, status
from medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc
limit 20;

重点字段：

字段	含义	怎么判断
`type`	访问方式	`ALL` 大表危险，`range/ref/const` 通常更好
`possible_keys`	可能使用的索引	只是候选，不代表最终用
`key`	实际选择的索引	是否符合预期
`key_len`	使用了索引的多少字节	判断联合索引用到几列
`rows`	预计扫描行数	是估算，不是真实值
`filtered`	剩余条件过滤比例	越低表示取出后过滤越多
`Extra`	额外操作	关注 `Using filesort`、`Using temporary`

rows 从哪里来？

rows 是优化器根据统计信息估算出来的，不是执行后真实数。统计信息包括表行数、索引基数、数据分布、采样信息等。统计信息不准时，优化器可能选错计划。

更新统计信息：

sql

analyze table medical_asset;

MySQL 8 还可以使用直方图辅助优化器理解数据分布：

sql

analyze table medical_asset
update histogram on status;

第六步：UPDATE 全过程

状态流转：

sql

update medical_asset
set status = 'USED',
    updated_at = now()
where asset_no = 'A001'
  and status = 'IDLE';

执行流程：

mermaid

flowchart TD
    A["优化器选择 uk_asset_no"] --> B["InnoDB 定位记录"]
    B --> C["对索引记录加行锁"]
    C --> D["生成 undo log 保存旧值"]
    D --> E["修改 Buffer Pool 中的数据页"]
    E --> F["生成 redo log record"]
    F --> G["Server 层写 binlog"]
    G --> H["redo commit"]
    H --> I["返回提交成功"]
    I --> J["脏页后续刷盘"]

每一步为什么需要：

步骤	原因
用唯一索引定位	减少扫描和锁范围
加行锁	防止两个事务同时改同一行
undo log	支持回滚和 MVCC 旧版本
Buffer Pool 改页	先改内存，提高性能
redo log	宕机后重做已提交修改
binlog	主从复制和时间点恢复
两阶段提交	保证 redo 和 binlog 事务一致

第七步：事务、锁和 MVCC

InnoDB 要同时解决两个问题：

写写冲突必须排队，否则同一行会被改乱。
普通读尽量不要阻塞写，否则并发性能差。

写写冲突：

mermaid

sequenceDiagram
    participant A as 事务A
    participant B as 事务B
    A->>A: update medical_asset where id=1
    A->>A: 未提交，持有行锁
    B->>B: update medical_asset where id=1
    B-->>A: 等待事务A释放锁
    A->>A: commit
    B->>B: 继续执行

普通快照读：

mermaid

flowchart TD
    A["select 开始"] --> B["生成 Read View"]
    B --> C["读取当前记录版本"]
    C --> D{"当前版本可见吗"}
    D -- "可见" --> E["返回"]
    D -- "不可见" --> F["沿 undo log 版本链找旧版本"]
    F --> G["返回可见旧版本"]

MVCC 和 undo log 的关系：

概念	作用
`trx_id`	记录最后修改该行的事务 ID
`roll_pointer`	指向 undo log 旧版本
Read View	判断哪些事务版本对当前查询可见
undo log	提供旧版本，也支持回滚

这就是为什么普通 select 可以不加锁也看到一致结果。但 select ... for update 是当前读，需要读最新版本并加锁。

第八步：redo log、binlog 和两阶段提交

redo log 和 binlog 属于不同层：

日志	所属层	作用
undo log	InnoDB	回滚和 MVCC
redo log	InnoDB	崩溃恢复
binlog	Server 层	主从复制、时间点恢复、审计

事务提交时要协调 redo 和 binlog：

mermaid

sequenceDiagram
    participant T as 事务
    participant R as InnoDB redo
    participant B as Server binlog
    T->>R: redo prepare
    T->>B: 写 binlog
    T->>R: redo commit
    T-->>T: commit success

为什么需要两阶段提交：

如果只写 redo	主库能恢复，但从库没有 binlog，主从不一致
如果只写 binlog	从库可能回放，但主库恢复没有该事务
两阶段提交	崩溃恢复时能根据 redo prepare 和 binlog 判断提交还是回滚

核心交易系统常见可靠配置：

sql

set persist innodb_flush_log_at_trx_commit = 1;
set persist sync_binlog = 1;

代价是每次提交刷盘成本更高，但订单、支付、资产、库存这类数据通常更重视可靠性。

第九步：主从复制和延迟

MySQL 主从复制简化流程：

mermaid

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

主从延迟常见原因：

原因	解释	处理
大事务	从库要完整回放，耗时长	拆小事务
主库写入高峰	binlog 产生速度超过回放速度	限流、扩容
从库慢查询	资源被查询占用	读写隔离、优化查询
从库配置低	CPU/IO 不足	提升规格
单线程瓶颈	回放并行度不足	开启并行复制

强一致场景不要无脑读从库。刚写完订单立刻查询，应该读主库或做延迟判断。

第十步：大表和覆盖索引仍慢

覆盖索引能减少回表，但不能解决所有问题。

sql

select asset_no, status, created_at
from medical_asset
where hospital_id = 1001
order by created_at desc
limit 1000000, 20;

即使字段都在索引中，也可能慢，因为：

深分页要跳过前面 1000000 条。
扫描大量索引页会占用 Buffer Pool。
返回范围太大，扫描成本仍然高。
如果排序无法利用索引，会 filesort。
历史冷数据和热点数据混在一起，缓存命中差。

优化方向：

问题	方案
深分页	改游标分页，使用 `where id > ? limit 20` 或按业务时间游标
历史数据多	冷热归档、分区、历史库
单表过大	分库分表或按租户/时间拆分
查询范围大	增加过滤条件，调整联合索引
排序慢	让 order by 使用索引顺序

完整 Demo

插入数据：

sql

insert into medical_asset(asset_no, hospital_id, department_id, asset_name, status)
values
  ('A001', 1001, 10, 'CT-001', 'IDLE'),
  ('A002', 1001, 11, 'MRI-001', 'USED'),
  ('A003', 1002, 12, 'DR-001', 'REPAIR');

看执行计划：

sql

explain
select id, asset_no, asset_name, status
from medical_asset
where hospital_id = 1001
  and status = 'USED'
order by created_at desc
limit 20;

状态流转：

sql

begin;

update medical_asset
set status = 'USED'
where asset_no = 'A001'
  and status = 'IDLE';

commit;

模拟锁等待时，开两个会话。会话 A 更新后不提交，会话 B 更新同一行，就会等待。然后通过：

sql

show engine innodb status\G

观察锁等待和事务信息。

线上排查总流程

mermaid

flowchart TD
    A["MySQL 线上问题"] --> B{"表现是什么"}
    B -- "SQL 慢" --> C["EXPLAIN / 慢日志"]
    C --> D["看索引、rows、Extra、返回行数"]
    B -- "接口卡住" --> E["查锁等待和长事务"]
    E --> F["show engine innodb status"]
    B -- "提交慢" --> G["查 redo/binlog 刷盘和磁盘 IO"]
    B -- "主从延迟" --> H["查大事务、从库回放、慢查询"]
    B -- "表越来越慢" --> I["查大表、深分页、冷热数据、索引膨胀"]

排查清单：

问题	先看什么
慢 SQL	`EXPLAIN`、慢日志、扫描行数、`Extra`
有索引不用	统计信息、数据分布、函数/隐式转换
锁等待	当前事务、等待事务、索引是否命中
死锁	死锁日志、更新顺序、范围锁
主从延迟	大事务、从库慢 SQL、并行复制
commit 慢	`sync_binlog`、redo 刷盘、磁盘延迟
大表慢	深分页、归档、分区、分库分表

常见坑

坑	后果	正确做法
每个字段都建索引	写入慢、空间大、优化器选择复杂	按高频 SQL 设计联合索引
只看是否用了索引	扫描范围大仍然慢	看 `rows`、返回行数、Extra
事务里调用远程接口	锁持有时间不可控	远程调用放事务外
UUID 做聚簇主键	页分裂、索引变大	优先递增主键或有序 ID
长事务不提交	undo 堆积、锁等待	缩短事务、及时提交
无脑读从库	读到旧数据	强一致读主库或延迟判断
大批量 update 一次提交	redo/binlog/锁/主从延迟放大	分批处理

面试标准回答

MySQL 怎么从零学到生产可用

text

MySQL 要按 SQL 基础、表设计、索引、执行计划、事务并发、日志恢复、主从复制和生产排查这条线学习。先掌握字段类型、主键、唯一约束和联合索引设计，再理解一条 SQL 会经过连接器、解析器、预处理器、优化器、执行器，最后由 InnoDB 通过 B+Tree、Buffer Pool、锁和 MVCC 访问数据。深入部分要理解聚簇索引、二级索引、回表、覆盖索引、EXPLAIN 估算行数、undo/redo/binlog、两阶段提交、主从复制和大表治理。生产排查要结合 EXPLAIN、慢日志、InnoDB 状态、锁等待、主从延迟和磁盘 IO。

为什么 MySQL 有索引还会慢

text

有索引只说明存在一种可能的访问路径，不代表扫描量小。MySQL 优化器会根据统计信息和成本选择是否使用索引；即使用了索引，也可能因为范围太大、回表太多、深分页、排序 filesort、临时表、返回列太多、Buffer Pool 命中率低或锁等待而慢。排查时要看 EXPLAIN 的 type、key、rows、filtered、Extra，再结合慢日志、实际返回行数和业务查询条件判断。

undo、redo、binlog 三者关系

text

undo log 属于 InnoDB，保存旧版本，用于事务回滚和 MVCC 快照读；redo log 属于 InnoDB，记录数据页修改，用于崩溃恢复，保证提交事务不丢；binlog 属于 MySQL Server 层，记录逻辑变更事件，用于主从复制、时间点恢复和审计。事务提交时通过 redo prepare、写 binlog、redo commit 的两阶段提交保证 redo 和 binlog 在事务边界一致。

关联知识点

知识点	说明
MySQL 总览	专栏入口和学习顺序
架构与请求全过程	Server 层和 InnoDB 边界
SQL 执行全过程原理	select 从连接到读页
B+Tree 与索引家族	为什么索引用 B+Tree
EXPLAIN 执行计划	执行计划字段来源
MVCC	Read View 和 undo 版本链
redo log 与 binlog	两阶段提交和恢复复制
InnoDB 更新事务全过程	update 全链路
MySQL 面试	标准回答和追问

本章小结

MySQL 从零到生产级掌握，关键不是背“索引、事务、锁”这些名词，而是能把业务 SQL、表设计、B+Tree、执行计划、Buffer Pool、锁、MVCC、undo、redo、binlog、主从复制和生产排查串成完整过程。理解过程以后，你才能回答为什么这样设计，不这样会怎样。

JDK 8/11/17/21 LTS演进

线程池

MySQL 从零到生产级掌握 ​

学习目标 ​

学习路线 ​

第一步：从业务建表开始 ​

第二步：一条 SELECT 怎么执行 ​

第三步：B+Tree、聚簇索引和二级索引 ​

第四步：联合索引为什么有顺序 ​

第五步：EXPLAIN 要看什么 ​

第六步：UPDATE 全过程 ​

第七步：事务、锁和 MVCC ​

第八步：redo log、binlog 和两阶段提交 ​

第九步：主从复制和延迟 ​

第十步：大表和覆盖索引仍慢 ​

完整 Demo ​

线上排查总流程 ​

常见坑 ​

面试标准回答 ​

MySQL 怎么从零学到生产可用 ​

为什么 MySQL 有索引还会慢 ​

undo、redo、binlog 三者关系 ​

关联知识点 ​

本章小结 ​

MySQL 从零到生产级掌握

学习目标

学习路线

第一步：从业务建表开始

第二步：一条 SELECT 怎么执行

第三步：B+Tree、聚簇索引和二级索引

第四步：联合索引为什么有顺序

第五步：EXPLAIN 要看什么

第六步：UPDATE 全过程

第七步：事务、锁和 MVCC

第八步：redo log、binlog 和两阶段提交

第九步：主从复制和延迟

第十步：大表和覆盖索引仍慢

完整 Demo

线上排查总流程

常见坑

面试标准回答

MySQL 怎么从零学到生产可用

为什么 MySQL 有索引还会慢

undo、redo、binlog 三者关系

关联知识点

本章小结