MySQL常见知识整理

February 26, 2026

Tags:
fundmental

MySQL 核心架构速览

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

MySQL 核心架构图

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

在 InnoDB 中，数据不是杂乱无章丢进文件的，而是有着极其严密的层级结构：

表空间 -> 段 -> 区 -> 页 -> 行。其中 页（Page） 是磁盘与内存交互的基本单位，行记录就保存在页里。下面从最底层的存储结构讲起。

存储结构

页（Page）：最小存储单位

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

行格式（Row Format）

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

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

记录的额外信息（Metadata）——让数据库引擎能“读懂”这一行：

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

记录的真实数据（Real Data）——除了你定义的字段（如 name, age）外，InnoDB 还会自动加上三个隐式列：

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

大字段与溢出页

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

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

这也是 为什么一行不能超过 8KB 的原因：虽然一个页有 16KB，但 MySQL 规定一个页至少要存 2 行数据（为了保证 B+ 树的层级结构稳定）。加上页头、页尾的开销，如果你定义的所有列加起来超过了约 8KB，MySQL 就会强制将变长字段转移到溢出页中。

页内的行排列

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

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

总结：插入一行发生了什么

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

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

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

索引（重点中的重点）

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

数据结构：B+ Tree

MySQL 主要使用 B+ Tree。

为什么不用 Hash？——范围查询太慢。
为什么不用二叉树？——树太高，I/O 次数太多。

B+ 树的阶（每个节点最多子节点数）在表创建时即确定，由索引键大小和页大小共同决定：
$$\text{页大小 (16KB)} \ge (\text{阶数 } M - 1) \times \text{索引键大小} + M \times \text{指针大小} + \text{页头元数据}$$

聚簇索引 vs 非聚簇索引

聚簇索引：数据和索引存在一起（InnoDB 的主键索引）。
非聚簇索引：索引指向数据行的地址（二级索引）。

最左匹配与回表

最左匹配原则：联合索引 (a, b, c)，查询必须从 a 开始，中间不能跳跃。
回表：通过二级索引查到主键，再回主键索引查整行数据。可通过 覆盖索引（索引包含所有查询字段）来避免。

事务与并发控制

ACID 特性

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

一致性核心定义：从一个合法状态到另一个合法状态。一致性指事务执行前后，数据库必须处于合法状态，即满足所有完整性约束与业务不变量。
什么是“合法”状态：
数据库完整性约束：主键唯一、NOT NULL、外键引用有效、数值范围限制（例如余额不能为负）等。
业务实体完整性（业务不变量）：由应用定义的规则。例如：在转账操作中，A 向 B 转账 100 元时，A 的余额需减少 100、B 的余额需增加 100，总金额保持不变。

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

RR 级别下 ACID 是如何实现的

MySQL 默认隔离级别是 REPEATABLE READ，以 InnoDB 为例，ACID 四性的实现分别依赖不同的底层机制：

原子性 -> Undo Log：事务执行时，修改前先写 undo log 记录"改前版本"（形成版本链）。任一语句失败或显式 ROLLBACK 时，按 undo log 逐条回滚；即使事务崩溃，未提交的修改也会在恢复阶段通过 undo log 撤销。
一致性 -> 由其他三性共同保证：数据库完整性约束 + 应用层业务不变量定义了"合法状态"。原子性保证要么全做要么全不做、隔离性保证并发不互相干扰、持久性保证提交结果不丢失，三者合力确保事务前后数据库始终处于合法状态。
隔离性 -> MVCC + 锁：
- 快照读（普通 SELECT）：MVCC 借助 undo log 构造多版本，事务第一次读时创建 Read View 并复用，同一事务内后续读取都基于同一快照——既保证可重复读，又因只读已提交版本而天然消除脏读。
- 当前读（SELECT ... FOR UPDATE / UPDATE / DELETE）：走 Next-Key Lock（行锁 + 间隙锁），锁住记录及其左侧间隙，防止并发事务插入新行导致幻读。
持久性 -> Redo Log（WAL）：事务提交时先把改动 fsync 到 redo log，再异步刷数据页。即使数据页尚未落盘就宕机，重启后仍可根据 redo log 重放恢复，保证已提交事务不丢失。

并发问题详解

并发访问事务带来的三大问题：

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

隔离级别

读未提交 (Read Uncommitted)
读已提交 (Read Committed)
可重复读 (Repeatable Read) —— MySQL 默认级别，通过 MVCC + Next-Key Lock 解决大部分并发问题。
串行化 (Serializable)

MVCC（多版本并发控制）

MVCC 是 InnoDB 实现高并发读取的核心机制：

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

快照读与当前读

InnoDB 的所有访问可归为两类，理解二者差异是理解锁与 MVCC 的前提：

快照读（普通 SELECT，走 MVCC）

不加锁，基于 Read View（一致性快照）读取。
RR 下，Read View 在事务内第一次快照读时创建（而非事务开始），后续快照读复用同一快照。

当前读（SELECT ... FOR UPDATE / LOCK IN SHARE MODE / UPDATE / DELETE / INSERT，走锁）

每次都读取最新已提交版本，并按隔离级别加锁（RR 下为 Next-Key Lock）。
因此会"看见"其他事务刚提交的新行——这是幻读残余的触发根源。

两个实用结论：

自己的修改必然可见：无论 Read View 何时建立，快照读读到自己事务改过的行时，返回的都是修改后的值。
先 UPDATE 再读：UPDATE 是当前读（读最新 + 加锁，此时尚未建立快照）；之后第一次普通 SELECT 才创建 Read View。若之前已有快照读建立了 Read View，则 UPDATE 后普通 SELECT 呈现"读自己的行是新值、读别人按旧快照"的混合状态。

Spring 提示：@Transactional 只管理事务边界，不会自动加锁——普通 SELECT 依旧是不加锁的快照读。只有显式使用 SELECT ... FOR UPDATE，或配置 isolation = SERIALIZABLE（MySQL 会把普通 SELECT 隐式转为 LOCK IN SHARE MODE）才会消除幻读残余。

Repeatable Read 如何解决并发问题

快照读（普通 SELECT，走 MVCC）：

脏读：事务只看到已提交的行版本或事务开始时的快照版本，彻底消除脏读。
不可重复读：MVCC 返回事务开始时或首次读时的行版本（快照），同一事务中相同的 SELECT 会看到相同的版本。
幻读：纯粹的快照隔离并不能完全避免幻读。

当前读（SELECT ... FOR UPDATE / UPDATE / DELETE，走锁）：

幻读：InnoDB 在 REPEATABLE READ 下结合 next-key locks（行锁 + 间隙锁），在对范围执行写操作时锁定间隙，防止并发事务插入导致幻读。因此在典型的 InnoDB 场景下，REPEATABLE READ 可以防止幻读。

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

RR 的逻辑缺陷与防护

RR 虽然消除了脏读、不可重复读和大部分幻读，但 MVCC 快照隔离仍存在几种逻辑并发异常。它们不属于标准的三类并发问题，却会在涉及多行或跨表约束的场景下悄悄破坏业务一致性，需要额外的防护手段。

Write Skew（写倾斜）

名称解释：Write Skew（写倾斜/写偏斜）是快照隔离（MVCC）下特有的逻辑并发异常。两个并发事务各自基于旧快照读取数据并做出判断，随后写入互不冲突的不同行，每个事务单独提交都满足约束，但合在一起却破坏了全局业务约束。它不属于脏读、不可重复读、幻读中的任何一类。

具体问题（医生值班例子）：

业务约束：“至少有一名医生值班”，涉及 doctor1、doctor2 两行。
T1 快照读：看到 doctor2 在线 → 判断"还有人值班"→ 将 doctor1 置为离线。
T2 快照读：看到 doctor1 在线 → 判断"还有人值班"→ 将 doctor2 置为离线。
两者写入的是不同行，行锁互不冲突，最终都能提交 → 两人同时离线，约束被破坏。

为什么 RR 挡不住：MVCC 快照读不加锁，两个事务各读各的快照；写入的又是不相交的行，Next-Key Lock 也无法阻止。

丢失更新（Lost Update）

名称解释：两个事务并发读取同一行，各自基于读到的旧值在应用层计算后写回，后提交者覆盖先提交者的修改，导致一次更新"丢失"。

具体问题：计数器场景。T1 读到 count=100，T2 也读到 count=100；T1 写回 101，T2 基于旧值也写回 101 → 最终是 101，本应为 102。

为什么 RR 会中招：快照读不加锁，两个事务读到的都是旧版本；若写回时用的是应用层算好的旧值而非原子自增，后提交者就会覆盖先提交者的结果。

幻读残余（Phantom Read）

名称解释：Next-Key Lock 只对当前读生效。当同一事务先做快照读、后做当前读时，仍可能看到中途插入并提交的新行，即"幻读"并未被完全消灭。

具体问题：T1 先 SELECT * FROM orders WHERE amount>100（快照读）得到 2 行；期间 T2 插入 amount=200 并提交；T1 再执行 UPDATE orders SET ... WHERE amount>100（当前读）→ 会"看见"并处理那行新数据，之前不存在的行突然出现。原因是快照读与当前读混用，二者看到的数据版本不一致。

只读事务异常（Read-only Transaction Anomaly）

名称解释：快照隔离下，一个只做 SELECT 的事务也可能读到在任意串行执行顺序下都不会出现的组合数据。

具体问题：两个写事务分别破坏了同一约束的不同片段并提交（例如上面的医生值班 write skew 已经发生）；只读事务随后按快照把两边的"新状态"一起读到，拼出的整体状态自相矛盾——没有任何串行执行能产生这种结果。

防护策略

针对上述逻辑缺陷，常见的防护手段：

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

事务与锁

两阶段锁协议（2PL）

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

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

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

锁的粒度与类型

InnoDB 支持多种锁：

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

意向锁（Intention Lock）

意向锁是表级锁，由 InnoDB 自动添加，用于标记"事务打算对表内的某些行加锁"，按锁的意图分为两种：

意向共享锁（IS Lock）：事务打算对表中某些行加共享锁（S 锁）。
意向排他锁（IX Lock）：事务打算对表中某些行加排他锁（X 锁）。

它的作用是把"表级锁"与"行级锁"之间的冲突判断提前到表层面：当事务准备对某行加锁时，会先在表上获取对应的意向锁；当其他事务申请表级 S / X 锁（如 LOCK TABLES、ALTER TABLE）时，只需检查表上的意向锁即可快速判断是否有行锁冲突，而无需逐行扫描全表。

意向锁之间是兼容的，但它们与表级锁存在冲突关系：

当前锁 \ 请求锁	S（表级）	X（表级）	IS	IX
S（表级）	兼容	冲突	兼容	冲突
X（表级）	冲突	冲突	冲突	冲突
IS	兼容	冲突	兼容	兼容
IX	冲突	冲突	兼容	兼容

关键点：多个事务可以在不同行上分别加行锁（各自持有 IX 锁互不阻塞），但当某个事务需要表级排他锁时，会因已存在的 IX 锁而阻塞等待，从而保证表级锁的互斥性。

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

InnoDB 的锁分为两类：

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

常见场景：

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

锁的释放与死锁

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

进阶提示

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

SQL 优化与排查

SQL 优化是面试与实战的核心场景，通常遵循"定位慢查询 → EXPLAIN 分析 → 优化索引/改写 SQL"的闭环。

索引设计原则

设计索引不是越多越好，需要权衡查询加速与写入/空间开销：

高区分度优先：优先在区分度高的列上建索引（如用户 ID、订单号），区分度低的列（如性别、状态）单独建索引意义不大。
联合索引按查询模式设计：遵循最左匹配，把等值查询的列放前面、范围查询的列放后面；高频组合条件建成联合索引而不是多个单列索引。
用覆盖索引避免回表：让索引包含查询需要的所有列（(a, b, c) 索引覆盖 SELECT a, b FROM t WHERE a = ?），减少回主键表的 I/O。
配合排序与分组：ORDER BY / GROUP BY 的列尽量纳入索引，可避免 filesort 与临时表。
控制索引数量：每个索引都占用空间、拖慢 DML；一般单表不超过 5~6 个。
前缀索引：大字段（如长字符串）可用前缀索引减小体积，但会失去前缀之后的部分排序能力。

用 EXPLAIN 分析 SQL

在慢 SQL 前加 EXPLAIN 即可查看执行计划，关键列如下：

列	含义	关注点
`type`	访问类型	从好到差：`const` > `eq_ref` > `ref` > `range` > `index` > `ALL`。`ALL` 表示全表扫描，是重点优化对象。
`key`	实际使用的索引	为 `NULL` 说明没走索引。
`rows`	预估扫描行数	越小越好，用于对比优化前后效果。
`Extra`	附加信息	出现 `Using filesort`、`Using temporary` 通常意味着性能隐患；`Using index` 表示覆盖索引，是好信号。

常见分析路径：

type=ALL → 缺少可用索引或索引被函数/隐式转换破坏，先查 key 是否为空。
key 有效但 rows 很大 → 索引选择不佳或数据分布差，考虑换列/调整联合索引顺序。
Extra 出现 Using filesort → 见下节。

filesort 怎么解决

Using filesort 表示 MySQL 无法利用索引的有序性，只能在排序缓冲（sort_buffer_size）甚至磁盘上额外排序。

产生原因与解法：

排序列无索引：ORDER BY created_at 没有对应索引 → 为该列建立索引；联合排序时建联合索引。
索引序与排序序不一致：索引 (a, b) 升序，查询却 ORDER BY a DESC, b ASC，或跳过了 a 直接按 b 排 → 调整索引列顺序与排序方向一致。
排序列不在等值条件下：WHERE a = ? ORDER BY b 时索引应为 (a, b)，把排序列紧跟在等值列之后，最左匹配才能覆盖。
SELECT 了过多列：回表行数大，排序缓冲压力大 → 用覆盖索引或只 SELECT 必要列。

排查顺序：先看 EXPLAIN 的 Extra 是否出现 Using filesort，再结合 key 判断是没索引还是索引序不匹配，最后按上述规则调整。

怎么发现慢 SQL

开启慢查询日志：slow_query_log = ON，设置 long_query_time（如 1 秒）与 log_queries_not_using_indexes，定期捞取慢日志。
SHOW PROCESSLIST / 性能库：实时查看当前执行的语句；performance_schema 的 events_statements_summary_by_digest 可按耗时/频率聚合 TOP SQL。
对候选慢 SQL 跑 EXPLAIN：确认扫描行数、是否全表扫描、是否 filesort，再按索引设计原则优化。
深分页问题：LIMIT 1000000, 10 会扫描并丢弃前一百万行，可用"上一页最大 ID"条件或延迟关联（先查主键再回表）优化。

怎么排查锁问题

锁等待与死锁的排查入口：

SHOW ENGINE INNODB STATUS：查看 LATEST DETECTED DEADLOCK（最近死锁现场）与 TRANSACTIONS（当前持锁/等待锁的事务）。
information_schema.innodb_trx：列出所有活动事务，看 trx_started（事务持续多久）、trx_state（RUNNING / LOCK WAIT）、trx_query。
innodb_lock_waits：直接给出"谁在等谁"的锁等待关系（requesting_trx_id → blocking_trx_id）。
performance_schema：data_lock_waits、sys.innodb_lock_waits 视图可更直观地展示阻塞链路。
结合 EXPLAIN 判断锁范围：全表扫描或范围查询会持有大量间隙锁/Next-Key Lock，通过索引缩小扫描范围可减少锁竞争。

排查思路：先定位 LOCK WAIT 的事务 → 找到阻塞它的持有者 → 检查持有者是否未提交或扫描范围过大 → 缩短事务、缩小锁范围或优化索引。

高频问题

题目	考察点	核心回答关键词
MySQL 为什么选 B+ 树？	数据结构	磁盘 IO 效率、范围查询、叶子节点双向链表。
什么是慢查询？如何优化？	实战能力	`EXPLAIN` 分析、索引失效排查、深分页优化。
如何设计索引？	索引设计	区分度优先、最左匹配、覆盖索引避免回表、控制索引数量。
EXPLAIN 怎么看？	执行计划	`type`（const→eq_ref→ref→range→index→ALL）、`key`、`rows`、`Extra`（filesort/temporary）。
如何解决 Using filesort？	排序优化	排序列建索引、索引序与排序序一致、排序列紧跟在等值列后、覆盖索引。
如何排查死锁？	锁排查	`SHOW ENGINE INNODB STATUS`、`innodb_trx`、`innodb_lock_waits`、`performance_schema`。
binlog, redolog, undolog 区别？	日志系统	崩溃恢复 (redo)、数据备份 (bin)、事务回滚 (undo)。
InnoDB 如何解决幻读？	锁机制	Next-Key Locks (行锁 + 间隙锁) + MVCC。
主从复制的原理是什么？	高可用	binlog、dump thread、relay log (中继日志)。

避坑指南（进阶建议）

别在索引列上做运算：SELECT * FROM t WHERE id + 1 = 10; 会导致全表扫描。
小心隐式类型转换：索引列是字符串却传入数字（或反之），MySQL 会做类型转换导致索引失效。
模糊匹配别用前导通配符：LIKE '%xxx' 无法利用索引，只有 LIKE 'xxx%' 可以走范围扫描。
OR 要两边都能走索引：WHERE a = 1 OR b = 2 若 b 无索引，会退化为全表扫描；可改写为 UNION 或建联合索引。
小心 SELECT *：这通常是性能杀手，且无法利用覆盖索引。
深度分页：LIMIT 1000000, 10 会扫描前一百万行，建议通过"上一页最大 ID"或延迟关联优化（详见 SQL 优化与排查章节）。

参考

https://dev.mysql.com/doc/en/innodb-introduction.html