myql

February 26, 2026

Tags:
fundmental

MySQL 核心架构速览

理解 MySQL 的工作原理是所有优化的前提。

连接器：管理连接、权限验证。
查询缓存（MySQL 8.0 已删除）：建议通过 Redis 实现。
分析器：词法分析、语法分析（看看你的 SQL 拼写对不对）。
优化器：决定使用哪个索引，选择最佳执行路径。
执行器：操作存储引擎，返回结果。
存储引擎：主要是 InnoDB（支持事务、行级锁）和 MyISAM（不支持事务、表级锁）。

这个问题触及了 MySQL 的核心——InnoDB 存储引擎。在 InnoDB 中，数据不是杂乱无章丢进文件的，而是有着极其严密的层级结构。

简单来说，数据存储的层级是：表空间 -> 段 -> 区 -> 页 -> 行。

最小存储单位：页（Page）

在讨论“行”之前，必须先理解“页”。

InnoDB 将磁盘划分为若干个页，默认大小为 16KB。
行记录是存在页里的。
数据库每次 I/O 操作都是以“页”为单位的。即便你只想看一行数据，MySQL 也会把这 16KB 整个加载到内存。

$$\text{页大小 (16KB)} \ge (\text{阶数 } M - 1) \times \text{索引键大小} + M \times \text{指针大小} + \text{页头元数据}$$

一般而言，表在创建的时候B+数的阶是确定的（根据索引大小和页大小确定）

行格式（Row Format）

MySQL 提供了几种行格式（如 Compact, Redundant, Dynamic, Compressed）。目前最常用的是 Dynamic（MySQL 5.7 后的默认格式）。

一行的存储结构主要分为两个部分：记录的额外信息 和 记录的真实数据。

A. 记录的额外信息（Metadata）

这部分是为了让数据库引擎能“读懂”这一行：

变长字段长度列表：记录像 VARCHAR、TEXT 这种变长字段实际占用了多少字节。
NULL 值列表：用位图（Bit Map）记录哪些列是 NULL，这样就不需要为 NULL 值浪费实际存储空间。
记录头信息：包含一些标志位（比如该行是否被删除、下一条记录的相对位置指针等）。

B. 记录的真实数据（Real Data）

除了你定义的字段（如 name, age）外，InnoDB 还会自动加上三个隐式列：

DB_TRX_ID (6字节)：最后一次修改本记录的事务 ID（MVCC 的核心）。
DB_ROLL_PTR (7字节)：回滚指针，指向这条记录的上一个版本（Undo Log）。
DB_ROW_ID (6字节)：如果你没有定义主键和唯一索引，InnoDB 就会生成这个隐藏主键。

溢出列（Off-page Column）

如果一个字段特别大（比如 VARCHAR(65535) 或 BLOB），一个 16KB 的页根本装不下怎么办？

在 Dynamic 格式下，行记录中只会保留一个 20 字节的指针。
实际的大数据会被存放到专门的**溢出页（Uncompressed Blob Page）**中。
这保证了 B+ 树的索引页依然非常紧凑，不会因为某个大字段导致树的高度剧增。

行在页中是如何排列的？

在页内部，行记录是按主键顺序排列成一个单向链表的。

为了提高查询效率，页内还有一个 页目录（Page Directory）。它将行记录分成几个小组（Slot），通过二分法快速定位到具体的行，避免了从头到尾遍历链表的低效。

为什么一行不能超过 8KB？

虽然一个页有 16KB，但 MySQL 规定一个页至少要存 2 行数据（为了保证 B+ 树的层级结构稳定）。

加上页头、页尾的开销，如果你定义的所有列加起来超过了约 8KB，MySQL 就会强制将变长字段转移到溢出页中。

总结

当你插入一行数据时，MySQL 实际上是在：

计算变长字段长度和 NULL 值情况。
加上事务 ID 和回滚指针。
将其塞进 B+ 树叶子节点的某个 16KB 的页里。
如果太大了，就“分家”存到溢出页。

你想了解一下当这一行数据被删除时，它在磁盘上是立即消失了，还是只是被标记了一下？（这涉及到了数据页的碎片处理）

索引（重点中的重点）

索引是面试中出现率最高的话题。

数据结构：MySQL 主要使用 B+ Tree。
为什么不用 Hash？（范围查询太慢）
为什么不用二叉树？（树太高，I/O 次数太多）
聚簇索引 vs 非聚簇索引：
聚簇索引：数据和索引存在一起（InnoDB 的主键索引）。
非聚簇索引：索引指向数据行的地址（二级索引）。
最左匹配原则：联合索引 (a, b, c)，查询必须从 a 开始，中间不能跳跃。
回表：通过二级索引查到主键，再回主键索引查整行数据。可通过 覆盖索引（索引包含所有查询字段）来避免。

事务与并发控制

ACID 特性：原子性 (Atomicity)、一致性 (Consistency)、隔离性 (Isolation)、持久性 (Durability)。

一致性核心定义：从一个合法状态到另一个合法状态。一致性指事务执行前后，数据库必须处于合法状态，即满足所有完整性约束与业务不变量。

什么是“合法”状态：

数据库完整性约束：主键唯一、NOT NULL、外键引用有效、数值范围限制（例如余额不能为负）等。
业务实体完整性（业务不变量）：由应用定义的规则。例如：在转账操作中，A 向 B 转账 100 元时，A 的余额需减少 100、B 的余额需增加 100，总金额保持不变。

如果事务执行完，导致数据库违反了上述任何一条规则，那这个事务就打破了一致性，数据库必须将其回滚。

并发问题：脏读、不可重复读、幻读。
隔离级别：

读未提交 (Read Uncommitted)
读已提交 (Read Committed)
可重复读 (Repeatable Read) —— MySQL 默认级别，通过 MVCC 部分解决幻读。
串行化 (Serializable)

MVCC (多版本并发控制)：通过 read view 和记录中的隐藏列（事务 ID、回滚指针）实现“快照读”。

解释脏读、不可重复读、幻读

并发问题详解：
- 脏读（Dirty Read）：事务 A 读取事务 B 尚未提交的修改；若 B 回滚，A 读到的是不存在的值。示例：T1 更新了一行但未提交，T2 读取到该值；T1 回滚 -> T2 看到错误数据。
- 不可重复读（Non-repeatable Read）：同一事务内对同一行的两次读取得到不同结果，原因是其他事务已提交对该行的修改。示例：T1 在事务内两次 SELECT 同一 id，期间 T2 修改并提交该行，导致 T1 的第二次读与第一次不同, 会对T1的操作产生影响。
- 幻读（Phantom Read）：同一事务对某个范围查询，两次结果行集合不同（出现或消失了某些行），通常由于其他事务插入或删除了满足范围的行。示例：T1 执行 SELECT * FROM orders WHERE amount>100 得到 N 行；T2 插入一条 amount=200 并提交；T1 再次查询看到 N+1 行，即出现“幻行”。
Repeatable Read 如何解决这些问题（以 InnoDB 为例）：

快照读:

脏读：通过 MVCC 的快照读（consistent read），事务只看到已经提交的行版本或事务开始时的快照版本，因此不会读到其他未提交事务的修改，彻底消除脏读。
不可重复读：MVCC 返回事务开始时或首次读时的行版本（快照），同一事务中相同的 SELECT 会看到相同的版本，从而避免不可重复读。
幻读：纯粹的快照隔离并不能完全避免幻读，但 InnoDB 在 REPEATABLE READ 的实现中结合了 next-key locks（行锁 + 间隙锁），在对范围执行写操作（UPDATE/DELETE/INSERT）时锁定间隙，防止并发事务插入导致的幻读。因此在典型的 InnoDB 场景下，REPEATABLE READ 可以防止幻读。

当前读: 通过行锁/GAP锁

注意：Snapshot Isolation（MVCC 的快照机制）并不完全等价于 SERIALIZABLE。在可重复读下，仍然可能发生逻辑异常，特别是在涉及多行或跨表约束的场景中。

Write Skew（写倾斜）简述

发生在 RR 隔离下的逻辑冲突场景。
典型例子：两个医生分别看自己的状态，都判断“还有至少一个值班医生”，结果同时离线，违反“至少一人值班”的约束。
解决方法：
- 开启 SERIALIZABLE；
- 或在关键读操作时使用 SELECT ... FOR UPDATE；
- 将逻辑约束映射到具体行锁定。

RR 的三大弱点

除了写倾斜之外，RR 级别还有几个容易踩到的陷阱：

丢失更新（Lost Update）：两个事务并发读取同一行并各自写回，后提交者覆盖先提交者的更改。
幻读残余（Phantom Read）：普通快照读后如果触发当前读或写操作，之前不存在的行会突然出现。
只读事务异常：只读事务也可能读到跨事务组合后的“逻辑不自洽”数据。

实效防护策略

开启串行化隔离：SERIALIZABLE / SSI 是最直接的防护。
显式当前读：在关键判断时使用 SELECT ... FOR UPDATE，避免纯粹快照读带来的盲点。
原子 SQL / 版本号乐观锁：尽量把读-改-写逻辑转成原子语句，或使用 version 字段重试机制。
具体行约束：把业务约束转化为对具体某条记录的争用，而不是依赖范围统计结果。

高频问题

题目	考察点	核心回答关键词
MySQL 为什么选 B+ 树？	数据结构	磁盘 IO 效率、范围查询、叶子节点双向链表。
什么是慢查询？如何优化？	实战能力	`EXPLAIN` 分析、索引失效排查、深分页优化。
binlog, redolog, undolog 区别？	日志系统	崩溃恢复 (redo)、数据备份 (bin)、事务回滚 (undo)。
InnoDB 如何解决幻读？	锁机制	Next-Key Locks (行锁 + 间隙锁) + MVCC。
主从复制的原理是什么？	高可用	binlog、dump thread、relay log (中继日志)。

避坑指南（进阶建议）

别在索引列上做运算：SELECT * FROM t WHERE id + 1 = 10; 会导致全表扫描。
小心 SELECT *：这通常是性能杀手，且无法利用覆盖索引。
深度分页：LIMIT 1000000, 10 会扫描前一百万行，建议通过子查询或标签 ID 优化。

事务与锁

两阶段锁协议（2PL）

InnoDB 的行锁实现基于两阶段锁协议：

第一阶段：扩展阶段，事务不断申请所需锁，不能释放锁。
第二阶段：收缩阶段，事务开始释放锁后，不再申请新锁。

这种协议保证了可串行化执行，避免了常见的并发异常。

MVCC 与快照读

InnoDB 使用多版本并发控制（MVCC）来实现高并发读取：

写事务更新时，不直接覆盖旧数据，而是在 undo log 中保留旧版本。
读事务读取的是当前事务可见的最新提交版本（read view）。
这让 SELECT 等 快照读 可以避免加锁，从而消除脏读和不可重复读。

但 MVCC 并不能直接防止所有幻读，InnoDB 结合锁机制来实现更强的隔离。

锁的粒度与类型

InnoDB 支持多种锁：

表锁：对整个表加锁，开销大，通常只在 LOCK TABLES、ALTER TABLE 等操作下使用。
记录锁：针对单条记录加锁，是 InnoDB 的基本锁类型。
行锁：实际上是记录锁的实现方式，用于保护行数据。
间隙锁（Gap Lock）：锁定两个记录之间的间隙，防止其他事务插入新记录。
Next-Key Lock：行锁 + 间隙锁的组合，既锁定记录本身，也锁定其左侧间隙，用于防止幻读。

读写锁（共享锁 / 排他锁）

InnoDB 的锁分为两类：

共享锁（S 锁）：允许多个事务读取同一行，但禁止写入。
排他锁（X 锁）：禁止其他事务读取或写入该行。

常见场景：

SELECT ... LOCK IN SHARE MODE 会申请共享锁。
SELECT ... FOR UPDATE 与 UPDATE、DELETE 会申请排他锁。

事务隔离与锁策略

MySQL 默认隔离级别是 REPEATABLE READ：

脏读：通过 MVCC 快照读避免。
不可重复读：相同事务可见同一快照版本，通常不会出现。
幻读：在 InnoDB 中，写操作会使用 next-key lock 或间隙锁来锁定范围，避免并发插入导致的幻读。

当需要更强隔离时，可以使用：

READ COMMITTED：提交读，减少锁定范围，但可能出现不可重复读。
SERIALIZABLE：强制所有读操作加锁，提供最严格的隔离，代价是并发吞吐下降。

事务提交与锁释放

InnoDB 在事务提交或回滚时释放所有锁。
如果事务长时间不提交，会导致锁等待和死锁风险增加。
死锁发生时，InnoDB 会自动回滚其中一个事务以恢复可继续执行。

进阶提示

对范围查询使用索引，可以避免大量间隙锁和全表扫描。
避免在事务中执行过多无关读写操作，减少持锁时间。
关键写操作建议用 SELECT ... FOR UPDATE 或显式锁定，确保业务一致性。