MySQL 如何守住一致性：事务、锁、MVCC 与日志 • FXJ Wiki

收录内容#

MySQL 事务与日志

MySQL 事务、锁、MVCC 与日志#

事务#

事务的特性#

ACID 特性以及通过怎样的方式来保证。

原子性：通过undo log(回滚日志)保证
一致性：通过其他三个特性来保证
隔离性：通过锁机制/MVCC(多版本并发控制)保证
持久性：通过 redo log(重做日志)保证

事务的隔离级别#

读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

一般来讲，使用可重复读(默认)就可以很大程度上避免幻读的问题了(但是还是可能出现)，串行化隔离级别，对性能会有影响，主要是通过下面两个方式基本解决幻读问题：

对于普通的 select 语句(快照读)，通过 MVCC 解决了幻读

对于 select…for update 语句(当前读)，通过 next-key lock(记录锁+间隙锁)解决幻读

实现方式：

对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

MySQL 中开启事务的命令：

begin/start transaction：当执行了第一句 select 语句才算真正开启
start transaction with consistent snapshot：立即开启

脏读/不可重复读/幻读问题#

脏读：一个事务读到了另外一个未提交的事务的数据

不可重复读：同一个事务中多次读取同一个数据，出现前后两次读到的数据不一样的情况

幻读：同一个事务中多次查询某个符合查询条件的记录数量，出现前后两次查询到的记录数量不一样的情况

[!example]

脏读 eg:事务 A 读取金额 100，然后将金额修改为 200，与此同时事务 B 读取数据金额，这个时候读到的金额(200)即为脏读(后面事务 A 若触发回滚，事务 B 这个时候读到的金额仍然是 200)

不可重复读 eg:事务 A 读取金额 100，事务 B 此时将金额修改为 200，事务 A 再次读取金额发现不一样了，即发生了不可重复读现象(在同一个事务中进行了重复读导致事务 A 在同一个事务中前后两次读取到的金额不同)

幻读：即事务 A 在事务 B 读取数据个数之后，又插入了一条满足要求的数据，当事务 B 再次读的时候发现数据个数不一样了，即幻读

MVCC（重）#

Read View 在 MVCC 里如何工作的？ Read View： ![](./Pasted image 20250528231309.png)

在创建 Read View 后，将记录中的 trx_id 划分为这三种情况： ![](./Pasted image 20250528232159.png)

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

简单来说，MVCC 这条链路可以直接这样记：

行记录里会带上 trx_id 和 roll_pointer；
trx_id 表示最后一次修改这行的事务 id；
roll_pointer 会把当前版本指到上一个 undo log 版本；
因而一条记录会顺着 undo log 串成版本链；
快照读时，事务拿着自己的 Read View 沿着版本链往前找，直到找到自己能看到的那一版。

从苹果备忘录里补几个最容易被追问的点：

undo log 不只是给回滚用的，也是 MVCC 历史版本真正的来源。
对于 delete，InnoDB 不是立刻物理删除，而是先打删除标记，后续再由 purge 线程清理。
对于 update：
- 如果更新的是主键列，本质上更接近“删旧行 + 插新行”；
- 如果更新的是普通列，则在 undo log 里记录旧值，回滚或快照读都能沿版本链拿到历史版本。
undo page 自己也会进 Buffer Pool，真正的持久化仍然要靠 redo log 兜底。

[!TIP] 这块最稳的答法不是一上来背术语，而是把这条链讲顺： 当前行 -> trx_id / roll_pointer -> undo log 旧版本 -> Read View 判断可见性 -> 找到事务可见版本。

Read View 的可见性判断#

trx_id < min_trx_id：生成快照时，这个事务已经提交了，当前版本可见。
trx_id >= max_trx_id：这是快照之后才分配的事务，当前版本不可见，要往前找旧版本。
min_trx_id <= trx_id < max_trx_id：
- 如果 trx_id 在活跃事务列表里，说明它创建快照时还没提交，不可见；
- 否则说明已经提交，可见。

MVCC 是如何实现已提交/可重复读的？#

对于读已提交：

每次执行 SELECT 语句时都会重新生成一个 Read View。

对于可重复读：

在事务启动时（第一次 SELECT 或 BEGIN 时）生成一个 Read View，并且该 Read View 在整个事务的生命周期内都有效，不再重新生成。

哪种情况下 MVCC 不能完全避免幻读？#

## 事务 A-----------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_stu where id = 5;
Empty set (0.01 sec)

## 事务 B-----------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_stu values(5, '小美', 18);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

## 事务 A-----------------
mysql> update t_stu set name = '小林coding' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_stu where id = 5;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  5 | 小林coding   |   18 |
+----+--------------+------+
1 row in set (0.00 sec)

sql

Attention：主要还是因为 MVCC 只支持 select，所以对于有 update 的情况也束手无策…

不过可以通过 MVCC+next-key-lock 来彻底解决幻读！

InnoDB 存储引擎在 RR 级别下通过 MVCC 和 Next-key Lock 来解决幻读问题：

1、执行普通 select，此时会以 MVCC 快照读的方式读取数据

在快照读的情况下，RR 隔离级别只会在事务开启后的第一次查询生成 Read View ，并使用至事务提交。所以在生成 Read View 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”

2、执行 select…for update/lock in share mode、insert、update、delete 等当前读

在当前读下，读取的都是最新的数据，如果其它事务有插入新的记录，并且刚好在当前事务查询范围内，就会产生幻读！InnoDB 使用 Next-key Lock 来防止这种情况。当执行当前读时，会锁定读取到的记录的同时，锁定它们的间隙，防止其它事务在查询范围内插入数据。只要我不让你插入，就不会发生幻读

graph TD
    MVCC[MVCC机制] --> UndoLog[Undo日志版本链]
    MVCC --> ReadView[ReadView一致性视图]
    UndoLog --> |构建历史版本| RowVersions[行多版本链]
    ReadView --> |判断可见性| Visibility[数据可见性规则]
    
    RowVersions --> Row1[当前版本: trx_id=200]
    RowVersions --> Row2[历史版本1: trx_id=150]
    RowVersions --> Row3[历史版本2: trx_id=100]
    
    ReadView --> RV1[活跃事务列表: 180,220]
    ReadView --> RV2[最小活跃事务ID: 180]
    ReadView --> RV3[最大分配事务ID: 250]

mermaid

锁机制#

全局锁
表级锁
- 表锁
- 元数据锁
- 意向锁
- AUTO-INC 锁
行级锁
- Record Lock：锁住单个点
- Gap Lock：开区间
- Next-Key-Lock：间隙锁+记录锁的结合(似乎通常是左开右闭的形式)
- 插入意向锁

我记得之前的 gemini 解释的不错的

使用场景#

下面这块直接把苹果备忘录里的锁记录补回来，重点就是：不同索引 + 等值/范围查询，Record Lock / Gap Lock / Next-Key Lock 到底怎么落。

先记一个观察锁的语句：

select * from performance_schema.data_locks\G;

sql

唯一索引等值查询
- 记录存在：next-key lock 会退化为 record lock。
- 记录不存在：会退化为 gap lock，因为锁是加在索引上的，不存在的记录本身没法加记录锁。
唯一索引范围查询
- id > target：按 (target, next] 这种 next-key 区间一路往右扫，最后一段会到 (last, +∞]。
- id >= target：命中的起点可能先退化为记录锁，后续区间仍然按范围锁。
- id < target / id <= target：重点看右边界是否已经越过查询上界；一旦越界，末段经常会退化成 gap lock，因为真正要防的是区间内插入造成的幻读。
非唯一索引等值查询
- 这时通常不只锁命中的记录，还要补 gap / next-key。
- 典型原因是：如果只锁住当前命中的那几行，同样索引值但不同主键的新记录仍然可以插进来，下一次查询结果就变了。
- 备忘录里的 age = 22 for update 就能看到 (21,22] 的 next-key、(22,39) 的 gap，以及命中行对应主键记录锁这种组合。
非唯一索引范围查询
- next-key lock 一般不会轻易退化，核心目标就是保证“按这个范围再查一次，结果集不能变”。
没有索引的查询
- 直接走全表扫描，沿途记录都会被加锁，update/delete 也一样，所以这类语句既慢，又容易把锁范围放大。

死锁补充#

苹果备忘录里提到的那个死锁例子，本质上就是：间隙锁和插入意向锁之间的冲突。

gap lock 和 gap lock 本身并不冲突；
但两个事务都先拿到某段区间的 gap lock 后，再各自往这个区间里插入数据，就会去申请 insert intention lock；
而插入意向锁会和对方持有的 gap lock 冲突，于是双方互相等，形成死锁。

所以这题别只背一句“死锁了”，更该讲清楚：

为什么查询阶段能并存；
为什么一到插入阶段就互相卡住；
为什么 InnoDB 最后只能回滚其中一个事务。

日志#

redo log
binlog
undo log
Buffer pool
…

对于这个部分的内容，我觉得黑马的视频似乎不算差，可以看

undo log#

插入一条数据：记录主键值，回滚的时候把这条插入删掉即可。
删除一条数据：先打删除标记，同时保留旧记录信息，回滚时把记录恢复；真正物理删除由 purge 线程处理。
更新一条记录：
- 更新主键列，本质上更接近“删旧行 + 插新行”；
- 更新普通列，则记录旧值，回滚时反向恢复。

undo log 是逻辑日志。

作用：

保证事务的原子性，实现事务回滚；
通过 Read View + undo log 实现 MVCC；
借助 trx_id + roll_pointer 把历史版本串成版本链。

[!info] undo log 是如何实现持久化的？ undo page 在 Buffer Pool 里被修改后，也会通过 redo log 保证持久化。

Buffer Pool#

即缓存池。

InnoDB 引擎里真正扛读写性能的核心结构之一。

读取数据时，若数据已经在缓冲池，就直接从内存读；
修改数据时，先改内存页，并把该页标记为“脏页”；
脏页不会立刻刷盘，而是由后台线程在合适时机写回磁盘。

脏页刷盘的常见时机：

redo log 快写满了；
Buffer Pool 空间不足，要淘汰脏页；
后台线程定期刷盘；
MySQL 正常关闭前，会把脏页尽量刷完。

Apple Notes 里这块还补了几个很容易被忽略的结构：

free 链表：快速拿到空闲缓存页，不用遍历整片内存找。
flush 链表：把脏页单独串起来，后台线程刷盘时能直接遍历。
LRU 链表：管理冷热页。

而且 InnoDB 不是简单 LRU，还会把 LRU 分成 young / old 两段：

解决预读失效：预读进来的页先放在 old 区，别一上来就占住热点位置；
解决 Buffer Pool 污染：大范围扫描时，必须在 old 区待够时间，才有资格晋升到 young。

Buffer Pool 里不只是数据页，还包括：

数据页
索引页
插入缓存页
undo 页
自适应哈希索引
锁信息

当查询一条记录的时候，并不是只把这一行拉进来，而是整个页进缓存，再通过页目录定位记录。

redo log#

为了防止断电导致数据丢失，InnoDB 采用 WAL：

先更新内存页；
同时把页修改写成 redo log；
之后再在合适时机把脏页刷回磁盘。

redo log 是物理日志：它记录的是“哪个表空间、哪个页、哪个偏移量，被改成了什么”。

它最核心的价值有两个：

宕机恢复时，可以把已经提交但还没来得及刷盘的页重做出来；
给 undo page、数据页这些内存修改提供持久化保障。

和 redo log 配套经常一起被问的点：

redo log buffer：先在内存里缓冲日志，减少频繁刷盘；
page cache：操作系统层面的文件页缓存；
innodb_flush_log_at_trx_commit：控制提交时刷盘策略。

binlog#

binlog 属于 MySQL Server 层，而不是 InnoDB 层。

它是逻辑日志，常见用途：

主从复制；
数据恢复；
审计变更。

面试里通常答到这里就够：

redo log 负责崩溃恢复、保障 InnoDB 持久性；
binlog 负责把“这次变更做了什么”记录给 MySQL Server 层使用。

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

如果只写其中一个日志就提交，会出现：

redo log 有了、binlog 没有：主库恢复得回来，但主从复制会丢事务；
binlog 有了、redo log 没有：从库可能看到了这次变更，但主库崩溃恢复后反而没这条数据。

所以 InnoDB 要走两阶段提交：

先把 redo log 写到 prepare 状态；
再写 binlog；
最后把 redo log 标成 commit。

这样即使中途宕机，也能根据 redo log + binlog 的状态判断这个事务到底该不该恢复出来。

优化！#

JavaGuide：

读写分离
分库分表
- 主从延迟的解决
- 分库分表需要达到怎样的条件
冷热分离
SQL 性能的优化？
- 上边日志的磁盘 IO 优化(见备忘录)
缓存机制？

[!CITE]

关于读写分离和分库分表的部分需要掌握

关于主从延迟部分，与主从同步有关的时间点？

主库执行完一个事务，写入 binlog，将这个时刻记为 T1；

从库 I/O 线程接收到 binlog 并写入 relay log 的时刻记为 T2；

从库 SQL 线程读取 relay log 同步数据本地的时刻记为 T3。

主从延迟应该如何解决呢？

从库机器性能比主库差：从库接收 binlog 并写入 relay log 以及执行 SQL 语句的速度会比较慢（也就是 T2-T1 和 T3-T2 的值会较大），进而导致延迟。解决方法是选择与主库一样规格或更高规格的机器作为从库，或者对从库进行性能优化，比如调整参数、增加缓存、使用 SSD 等。

从库处理的读请求过多：从库需要执行主库的所有写操作，同时还要响应读请求，如果读请求过多，会占用从库的 CPU、内存、网络等资源，影响从库的复制效率（也就是 T2-T1 和 T3-T2 的值会较大，和前一种情况类似）。解决方法是引入缓存（推荐）、使用一主多从的架构，将读请求分散到不同的从库，或者使用其他系统来提供查询的能力，比如将 binlog 接入到 Hadoop、Elasticsearch 等系统中。

大事务：运行时间比较长，长时间未提交的事务就可以称为大事务。由于大事务执行时间长，并且从库上的大事务会比主库上的大事务花费更多的时间和资源，因此非常容易造成主从延迟。解决办法是避免大批量修改数据，尽量分批进行。类似的情况还有执行时间较长的慢 SQL ，实际项目遇到慢 SQL 应该进行优化。

从库太多：主库需要将 binlog 同步到所有的从库，如果从库数量太多，会增加同步的时间和开销（也就是 T2-T1 的值会比较大，但这里是因为主库同步压力大导致的）。解决方案是减少从库的数量，或者将从库分为不同的层级，让上层的从库再同步给下层的从库，减少主库的压力。

网络延迟：如果主从之间的网络传输速度慢，或者出现丢包、抖动等问题，那么就会影响 binlog 的传输效率，导致从库延迟。解决方法是优化网络环境，比如提升带宽、降低延迟、增加稳定性等。

单线程复制：MySQL5.5 及之前，只支持单线程复制。为了优化复制性能，MySQL 5.6 引入了多线程复制，MySQL 5.7 还进一步完善了多线程复制。

复制模式：MySQL 默认的复制是异步的，必然会存在延迟问题。全同步复制不存在延迟问题，但性能太差了。半同步复制是一种折中方案，相对于异步复制，半同步复制提高了数据的安全性，减少了主从延迟（还是有一定程度的延迟）。MySQL 5.5 开始，MySQL 以插件的形式支持 semi-sync 半同步复制。并且，MySQL 5.7 引入了增强半同步复制。

分库分表的情况？(数据量达到多少考虑分库分表？) 🔥我说MySQL每张表最好不超过2000万数据，面试官让我回去等通知？面试官：麻烦你好好看看这篇文章，再告诉我，每张表 - 掘金 ↗

行非常大时（接近页大小的一半，约 8KB）： 3 层 B+ 树可能只能存储一百多万条数据。

行非常小时（如只有几个 INT 字段）： 3 层 B+ 树理论上可以存储近 5 亿条数据。

一个“常规”的业务表（如文章中的博客表）： 3 层 B+ 树大约能存储一千万条数据。

性能出现瓶颈: 这是最实际、最重要的触发点。当你观察到针对该表的查询（特别是涉及索引扫描的范围查询、或者没有命中索引的全表扫描）变得缓慢，CPU 或 IO 资源占用过高，并且分析确认是由于数据量过大导致索引效率下降（如 B+ 树层级增加、需要扫描的页过多）时，就应该强烈考虑分库分表或其他优化手段。

对于各种分片算法是怎样的？

数据冷热分离

sql 性能如何优化？

$>$ [!NOTE]- MySQL 性能怎么优化？ $>$ MySQL 性能优化是一个系统性工程，涉及多个方面，在面试中不可能面面俱到。因此，建议按照“点-线-面”的思路展开，从核心问题入手，再逐步扩展，展示出你对问题的思考深度和解决能力。 $>$ $>$ 1. 抓住核心：慢 SQL 定位与分析 $>$ $>$ 性能优化的第一步永远是找到瓶颈。面试时，建议先从慢 SQL 定位和分析入手，这不仅能展示你解决问题的思路，还能体现你对数据库性能监控的熟练掌握： $>$ $>$ 监控工具：介绍常用的慢 SQL 监控工具，如 MySQL 慢查询日志、Performance Schema 等，说明你对这些工具的熟悉程度以及如何通过它们定位问题。 $>$ EXPLAIN 命令：详细说明 EXPLAIN 命令的使用，分析查询计划、索引使用情况，可以结合实际案例展示如何解读分析结果，比如执行顺序、索引使用情况、全表扫描等。 $>$ 2. 由点及面：索引、表结构和 SQL 优化 $>$ $>$ 定位到慢 SQL 后，接下来就要针对具体问题进行优化。这里可以重点介绍索引、表结构和 SQL 编写规范等方面的优化技巧： $>$ $>$ 索引优化：这是 MySQL 性能优化的重点，可以介绍索引的创建原则、覆盖索引、最左前缀匹配原则等。如果能结合你项目的实际应用来说明如何选择合适的索引，会更加分一些。 $>$ 表结构优化：优化表结构设计，包括选择合适的字段类型、避免冗余字段、合理使用范式和反范式设计等等。 $>$ SQL 优化：避免使用 SELECT *、尽量使用具体字段、使用连接查询代替子查询、合理使用分页查询、批量操作等，都是 SQL 编写过程中需要注意的细节。 $>$ 3. 进阶方案：架构优化 $>$ $>$ 当面试官对基础优化知识比较满意时，可能会深入探讨一些架构层面的优化方案。以下是一些常见的架构优化策略： $>$ $>$ 读写分离：将读操作和写操作分离到不同的数据库实例，提升数据库的并发处理能力。 $>$ 分库分表：将数据分散到多个数据库实例或数据表中，降低单表数据量，提升查询效率。但要权衡其带来的复杂性和维护成本，谨慎使用。 $>$ 数据冷热分离：根据数据的访问频率和业务重要性，将数据分为冷数据和热数据，冷数据一般存储在存储在低成本、低性能的介质中，热数据高性能存储介质中。 $>$ 缓存机制：使用 Redis 等缓存中间件，将热点数据缓存到内存中，减轻数据库压力。这个非常常用，提升效果非常明显，性价比极高！ $>$ 4. 其他优化手段 $>$ $>$ 除了慢 SQL 定位、索引优化和架构优化，还可以提及一些其他优化手段，展示你对 MySQL 性能调优的全面理解： $>$ $>$ 连接池配置：配置合理的数据库连接池（如连接池大小、超时时间等），能够有效提升数据库连接的效率，避免频繁的连接开销。 $>$ 硬件配置：提升硬件性能也是优化的重要手段之一。使用高性能服务器、增加内存、使用 SSD 硬盘等硬件升级，都可以有效提升数据库的整体性能。