1 · MySQL#

情境：应用需要持久化结构化数据，MySQL 是最主流的 OLTP 关系型数据库。 冲突：但 MySQL 概念繁多，平铺学习容易只见树木不见森林。 问题：怎样系统地理解 MySQL？ 答案：从四个视角切入，每个视角回答一个核心问题。

视角	核心问题
一、关系型数据库	数据怎么组织和查询
二、OLTP	并发读写怎么保证正确
三、Schema 设计	表怎么设计不埋坑
四、高可用与运维	生产环境怎么不挂

2 · 一、关系型数据库 —— 数据怎么组织和查询

2.1 · 1. SQL 执行流水线#

S：应用向 MySQL 发出一条 SQL。 C：SQL 不可能直接变成磁盘操作，中间需要多个阶段协作。 Q：SQL 进来后经历了什么？ A：两层流水线——Server 层理解 SQL，存储引擎层存取数据。

连接器 → 分析器 → 优化器 → 执行器 → 存储引擎（InnoDB）

• 连接器：你是谁？有什么权限？
• 分析器：这条 SQL 合不合法？
• 优化器：怎么执行最快？
• 执行器：按计划调引擎接口，拿数据或写数据

存储引擎是插件式的，默认且最重要的是 InnoDB。

2.2 · 2. 存储：数据放在哪？#

S：执行器要调存储引擎读写数据。 C：数据不能随意堆在磁盘上，否则每次访问都要到处寻址。 Q：InnoDB 怎么组织数据？ A：按 表空间 → 段 → 区 → 页 → 行 的层级组织。页（默认 16KB）是磁盘与内存交互的基本单位。

关键直觉：

• MySQL 不是一行一行读磁盘的，而是一页一页读的
• 所以性能好坏的核心指标是读了多少页，而不是读了多少行
• InnoDB 在内存中维护 Buffer Pool 缓存热点页，避免每次都访问磁盘

2.2.1 · 2.1 内存结构#

结构	作用
Buffer Pool	缓存数据页，减少磁盘 IO
Change Buffer	缓存二级索引变更，减少随机写
Adaptive Hash Index	对热点访问做自适应哈希加速
Log Buffer	缓存 redo log

2.2.2 · 2.2 磁盘结构#

• 系统表空间、独立表空间 *.ibd
• undo 表空间、redo log 文件
• 临时表空间、通用表空间

双写缓冲（doublewrite）：先把页写到双写区域，再写到真正位置，避免写一半宕机导致页损坏。

2.2.3 · 2.3 页内部结构#

• 文件头 / 文件尾：页类型、校验、前后页指针
• 页头：槽数、记录数、空闲空间等
• 用户记录：实际数据行，通过记录头信息串成链表
• 页目录：对记录分组建槽，支持二分查找

理解页内结构的价值：

• 页满了要分裂 → 随机主键会频繁触发页分裂
• 页内记录不是数组而是链表 → 插入删除不需要移动数据

2.2.4 · 2.4 行格式#

常见格式：Compact、Redundant、Dynamic（默认）、Compressed。

每行除用户定义列外，还有三个隐藏列：

隐藏列	作用
DB_ROW_ID	没有主键时由引擎自动生成
DB_TRX_ID	最近修改该行的事务 ID（MVCC 用）
DB_ROLL_PTR	指向 undo log（回滚 + MVCC 用）

2.2.5 · 2.5 行溢出#

一行数据过大时无法塞进单个 16KB 页：

• 行内只保留一部分数据 + 指向溢出页的指针
• 这是 TEXT/BLOB/VARCHAR 大字段影响性能的底层原因之一

2.3 · 3. 索引：怎么快速找到数据？#

S：数据按页组织好了，也有 Buffer Pool 做缓存。 C：但如果每次都从头扫到尾（全表扫描），数据量大时依然很慢。 Q：怎么减少要读的页数？ A：用 B+ 树索引快速定位目标页。

为什么是 B+ 树？

• 哈希：只能等值查询，不支持范围
• 二叉树类：树太高，IO 次数多
• B+ 树：扇出大、树矮（通常 2~4 层）、叶子串链表支持范围查询

两种索引：

• 聚簇索引：叶子存整行数据，通常就是主键索引，一张表只有一个
• 二级索引：叶子存索引列 + 主键值，查整行要回表到聚簇索引再查一次

2.3.1 · 3.1 索引使用原则#

• 最左前缀：联合索引 (a, b, c) 只能按 a → ab → abc 的顺序利用
• 遇到范围查询后，后续列通常难继续充分利用
• 覆盖索引优于回表：二级索引已包含所需列就不用回表
• 联合索引通常优于多个单列索引的 index merge

2.3.2 · 3.2 建索引优先考虑#

• 过滤列、排序列、分组列
• 区分度高的列
• 更短的列类型
• 优先扩展已有联合索引，而不是盲目新增
• 避免重复索引

2.4 · 4. 优化器：有多条路时走哪条？#

S：有了索引，一条查询可能有多条执行路径。 C：不同路径成本可能差几个数量级，选错路有索引也白搭。 Q：MySQL 怎么选？ A：优化器基于成本模型（读多少页 + 过滤多少行）选成本最低的路径。

核心直觉：

• 优化器做两个决定：走哪个索引 和 多表先查谁
• “用了索引”不等于快——回表太多可能还不如全表扫描
• 统计信息不准时，执行计划可能变差

排查慢 SQL 的第一步：EXPLAIN 看执行计划。

2.4.1 · 4.1 单表访问方式#

从好到坏：

类型	含义
const	主键/唯一索引等值命中一条记录
ref	普通索引等值匹配
range	索引范围扫描
index	扫描整个索引树
ALL	全表扫描

2.4.2 · 4.2 多表连接#

1. 选一个驱动表
2. 用驱动表的结果去匹配被驱动表

优化关键：驱动表尽量小、被驱动表有索引、连接顺序由优化器按成本选择。

2.4.3 · 4.3 成本模型#

• IO 成本：读多少页
• CPU 成本：过滤/比较多少行

统计信息（索引基数、数据分布）会显著影响执行计划。统计不准时，计划可能变差。

2.4.4 · 4.4 EXPLAIN 怎么看#

字段	看什么
type	访问方式，是否退化成 ALL
key	实际用了哪个索引
rows	估计扫描多少行
Extra	Using index（覆盖索引）、Using filesort、Using temporary

---

3 · 二、OLTP —— 并发读写怎么保证正确#

3.1 · 5. 事务：多人同时读写怎么办？#

S：单用户查询没问题了。 C：但真实系统中多个事务同时读写同一数据，可能读到改了一半的脏数据。 Q：怎么保证每个事务看到一致的数据？ A：ACID 定义目标，隔离级别定义严格程度，MVCC 让读写不互斥。

ACID 一句话：原子（全做或全不做）、一致（数据始终合法）、隔离（事务互不干扰）、持久（提交不丢失）。

隔离级别从松到严：Read Uncommitted → Read Committed → Repeatable Read → Serializable。InnoDB 默认 Repeatable Read。

MVCC 的核心思想：每行数据保留多个版本，普通 SELECT 读的是”某个时间点可见的版本”，而不是当前最新物理值。这样读不需要加锁，读写就不互斥了。

3.1.1 · 5.1 版本链#

每行的 DB_TRX_ID 记录最近修改的事务 ID，DB_ROLL_PTR 指向 undo log 中的旧版本。多次修改形成一条版本链。

3.1.2 · 5.2 Read View#

事务做一致性读时生成 Read View，包含：

• 当前活跃事务 ID 列表
• 最小活跃事务 ID
• 下一个待分配事务 ID

沿版本链找到第一个”对当前 Read View 可见”的版本返回。

3.1.3 · 5.3 RC 和 RR 的区别#

• Read Committed：每次 SELECT 生成新 Read View → 能看到其他事务已提交的最新值
• Repeatable Read：事务开始时生成一次 Read View，后续复用 → 同一事务内多次读结果一致

3.2 · 6. 锁：写冲突怎么解决？#

S：MVCC 解决了”读”的并发。 C：但写与写、写与”当前读”（SELECT ... FOR UPDATE）仍然要互斥。 Q：怎么协调？ A：锁。InnoDB 的行锁基于索引实现，用记录锁、间隙锁、邻键锁的组合来保护数据和区间。

核心直觉：

• 锁是分层的：全局锁 > 表级锁 行级锁（最常接触）
• 行锁不是按物理行号加锁，而是锁索引记录
• 间隙锁锁住两条记录之间的”空隙”，防止别人插入新行（防幻读）
• 死锁是常见现象，不是异常——InnoDB 会检测并回滚一个事务

3.2.1 · 6.1 表级锁#

• MDL（元数据锁）：防止”查表”和”改表结构”互相冲突
• 意向锁：加行锁前先加意向锁，告诉系统”表中某些行将被锁”

3.2.2 · 6.2 行级锁类型#

类型	锁什么	作用
记录锁 Record	已有的索引记录	防止修改/删除
间隙锁 Gap	两条记录之间的区间	防止插入（防幻读）
邻键锁 Next-Key	记录锁 + 间隙锁	RR 级别的默认行锁
插入意向锁	间隙中的某个位置	允许不同位置并发插入
自增锁	AUTO_INCREMENT 计数器	保证自增值连续

Next-Key Lock = Record Lock + Gap Lock，这是 RR 下部分解决幻读的关键。

3.2.3 · 6.3 死锁#

处理：等超时 或 死锁检测后回滚代价最小的事务。

降低概率：固定访问顺序、走索引缩小锁范围、把最易冲突的操作放事务最后。

3.3 · 7. 日志：崩溃了怎么办？#

S：事务提交了，数据应该持久。 C：但提交时数据可能还在内存（Buffer Pool）没落盘，这时宕机数据就丢了。每次都刷盘？随机 IO 太慢。 Q：怎么既保证不丢数据，又维持写入性能？ A：WAL——先顺序写日志，再异步刷数据页。三种日志各管一件事。

日志	谁的	一句话
redo log	InnoDB	崩溃后重做，保持久性
undo log	InnoDB	回滚 + 构造 MVCC 版本链
binlog	Server	主从复制 + 数据归档

3.3.1 · 7.1 redo log#

• 物理日志，记录”某页某偏移做了什么修改”
• WAL 思想：先顺序写日志，再异步刷数据页
• 顺序写比随机写快得多，这是性能关键

3.3.2 · 7.2 undo log#

• 记录修改前的旧值，用于回滚
• MVCC 的版本链就是由 undo log 串起来的
• 删除通常先打 delete mark，由 purge 线程异步清理

3.3.3 · 7.3 binlog#

• 逻辑日志，记录”这条语句/这个事务做了什么”
• 用于主从复制、数据归档、时间点恢复

3.3.4 · 7.4 两阶段提交#

S：redo log 和 binlog 分属不同层。 C：只写了一个没写另一个，主从数据就不一致。 Q：怎么保证两份日志一致？ A：两阶段提交。

1. redo log 写入 prepare
2. binlog 写入
3. redo log 标记 commit

崩溃恢复判断：

• redo prepare + binlog 完整 → 提交
• redo prepare + binlog 不完整 → 回滚
• redo 没有 prepare → 回滚

---

4 · 三、Schema 设计 —— 表怎么设计不埋坑#

4.1 · 8. 数据类型选择#

S：建表时需要为每列选择数据类型。 C：类型选不好，轻则浪费空间、拖慢查询，重则数据溢出、业务出错。 Q：怎么选？ A：够用的前提下，越小越好、越简单越好、避免 NULL。

场景	推荐	避免	原因
整数	INT / BIGINT 按范围选	全部用 BIGINT	浪费空间，索引更大
小数/金额	DECIMAL	FLOAT / DOUBLE	浮点有精度问题
时间	DATETIME（范围大）或 TIMESTAMP（自动时区，4 字节）	字符串存时间	无法做时间运算和比较
短文本	VARCHAR(N) N 按实际上限	VARCHAR(65535)	内存临时表按声明长度分配
长文本/大对象	TEXT / BLOB	频繁查询的列用 TEXT	可能行溢出，影响 Buffer Pool 效率
布尔/状态	TINYINT	ENUM（DDL 变更麻烦）	扩展性差
IP 地址	INT UNSIGNED + INET_ATON/NTOA	VARCHAR(15)	空间省 3 倍，比较更快

4.1.1 · 8.1 NULL 的代价#

• 索引、统计、比较都需要额外处理
• COUNT(col) 不计 NULL，COUNT(*) 计全部——容易出 bug
• 建议：除非业务语义确实需要”未知”，否则 NOT NULL DEFAULT ...

4.2 · 9. 范式与反范式#

S：数据建模需要决定拆分粒度。 C：拆太细 JOIN 多、查询慢；拆太粗冗余多、更新异常。 Q：怎么平衡？ A：先按第三范式设计保证正确性，再针对高频查询做有控制的反范式。

• 1NF：列不可再分
• 2NF：非主属性完全依赖主键（消除部分依赖）
• 3NF：非主属性不传递依赖主键（消除传递依赖）

常见反范式手段：

• 冗余列：避免高频 JOIN（如订单表冗余用户名）
• 汇总表/计数表：避免实时聚合
• 代价：更新时需要同步维护冗余数据

4.3 · 10. 主键设计#

S：每张表需要一个主键。 C：主键决定聚簇索引的物理组织方式，选错影响全局性能。 Q：主键怎么选？ A：稳定、短、递增。

• 稳定：主键值不应变更，否则所有二级索引都要跟着改
• 短：主键存在每个二级索引的叶子里，越短索引越小
• 递增：顺序插入减少页分裂；随机主键（如 UUID）导致大量页分裂和碎片

方案	优点	缺点
AUTO_INCREMENT	递增、短、简单	分布式环境需额外协调
雪花 ID	趋势递增、全局唯一	8 字节、依赖时钟
UUID v4	全局唯一、无需协调	36 字节、完全随机、页分裂严重
UUID v7	全局唯一、时间递增	16 字节，仍比 BIGINT 大

4.4 · 11. Online DDL#

S：业务迭代需要频繁加列、改索引。 C：传统 DDL 会锁表，大表操作可能锁数分钟甚至小时。 Q：怎么不停服改表？ A：MySQL 5.6+ 的 Online DDL 或外部工具 pt-osc / gh-ost。

MySQL Online DDL 的三种算法：

算法	过程	是否锁表
INSTANT	只改元数据	不锁
INPLACE	引擎内部重建	大部分不锁（允许并发 DML）
COPY	创建临时表、拷贝数据	锁表

大表变更建议：优先 INSTANT；不支持则用 gh-ost（基于 binlog 的无触发器方案）。

---

5 · 四、高可用与运维 —— 生产环境怎么不挂

5.1 · 12. 复制#

S：单机 MySQL 是单点。 C：机器故障意味着服务不可用和数据丢失。 Q：怎么消除单点？ A：复制——主库写 binlog，从库拉取并重放。

5.1.1 · 12.1 复制模式#

模式	流程	数据安全	性能
异步复制	主库写完 binlog 即返回	主库宕机可能丢数据	最好
半同步复制	至少一个从库确认收到 binlog 才返回	几乎不丢	略慢
组复制 (MGR)	Paxos 协议多数派确认	不丢	更慢，但支持多写

5.1.2 · 12.2 复制延迟#

常见原因：从库单线程回放、大事务、DDL 阻塞。

缓解手段：并行复制（slave_parallel_workers）、拆大事务、避免从库做大查询。

5.2 · 13. 读写分离与分库分表#

S：单机性能有上限。 C：数据量/并发量增长到单机极限。 Q：怎么突破？ A：读写分离分担读压力，分库分表分担存储和写压力。都有额外代价。

• 读写分离：主写从读，代价是复制延迟、可能读旧数据
• 分库分表：水平拆（按行）或垂直拆（按业务域），代价是跨库 JOIN、分布式事务、全局主键

5.3 · 14. 备份与恢复#

S：数据是最重要的资产。 C：硬件故障、误操作、软件 bug 都可能导致数据丢失或损坏。 Q：怎么兜底？ A：定期备份 + binlog，实现任意时间点恢复（PITR）。

工具	类型	特点
mysqldump	逻辑备份	简单，但大库慢
xtrabackup	物理备份	快，支持增量备份，不锁表（InnoDB）

恢复流程：全量备份还原 → 追加 binlog 到目标时间点。

5.4 · 15. 监控与排查#

S：系统上线了。 C：不知道什么时候会出问题，出了问题不知道原因。 Q：怎么做到心中有数？ A：关键指标 + 慢查询日志 + performance_schema。

5.4.1 · 15.1 关键指标#

指标	含义	关注阈值
QPS / TPS	吞吐量	突变
慢查询数	超过 long_query_time 的查询	> 0 就要看
线程连接数	Threads_connected	接近 max_connections
Buffer Pool 命中率	Innodb_buffer_pool_read_requests / reads	< 99% 需关注
复制延迟	Seconds_Behind_Master	> 1s 需排查

5.4.2 · 15.2 慢查询排查流程#

开启 slow_query_log → mysqldumpslow 聚合 → EXPLAIN 看执行计划 → 优化索引/SQL/表结构

5.4.3 · 15.3 performance_schema#

MySQL 内置的运行时诊断工具，可查：

• 等待事件（锁等待、IO 等待）
• SQL 统计（执行次数、耗时分布）
• 内存使用

---

6 · 附录

6.1 · 源码导读

先分清两个目录：sql/（Server 层）、storage/innobase/（InnoDB）。

执行主链路：

handle_connection → do_command → dispatch_command → mysql_parse → mysql_execute_command

InnoDB 核心模块：

前缀	模块
btr*	B+ 树
trx*	事务
lock*	锁
fsp*	表空间/区/页
row*	行操作
dict*	数据字典

推荐按操作路径读：建表 → 插入 → 查询 → 更新/删除 → 提交/恢复。

6.2 · 快速自检

#	问题	对应
1	Server 层和 InnoDB 层分别负责什么？	§1
2	为什么 InnoDB 选 B+ 树，而不是哈希或二叉树？	§3
3	聚簇索引和二级索引有什么区别？什么是回表？	§3
4	最左前缀原则为什么成立？	§3.1
5	为什么”用了索引”也可能慢？	§4
6	MVCC 依赖哪些字段和日志？	§5
7	RR 下如何理解幻读与 Next-Key Lock？	§6
8	redo log、undo log、binlog 分别解决什么问题？	§7
9	为什么需要两阶段提交？	§7.4
10	VARCHAR(N) 的 N 应该怎么选？NULL 有什么代价？	§8
11	主键为什么要递增？UUID 有什么问题？	§10
12	大表 DDL 怎么做到不停服？	§11
13	异步复制和半同步复制的区别？	§12
14	慢查询怎么排查？	§15

6.3 · 参考

• https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html
• https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
• https://dev.mysql.com/doc/refman/8.4/en/explain-output.html
• https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
• https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-logs.html
• https://tech.meituan.com/2014/06/30/mysql-index.html