MySQL运维面试题(3)
MySQL运维面试题(3)
作者：没有四次元口袋的蓝胖
日期：2026-06-10
标签：Java, MySQL, 索引失效, 事务, MVCC
题目11：索引失效的场景有哪些？
11.1 索引失效全景图
索引不是建了就一定走，很多操作会让优化器放弃索引。记住一个核心原则：索引的本质是有序性，破坏了有序性索引就废了。
索引失效场景
├── 破坏索引有序性
│ ├── 索引列做运算/函数
│ ├── 隐式类型转换
│ ├── 隐式字符编码转换
│ └── LIKE左模糊（'%xxx'）
├── 联合索引匹配失败
│ ├── 不满足最左前缀
│ └── 范围查询后的列
├── 优化器主动放弃
│ ├── OR连接非索引列
│ ├── 查询占比过高（>20-30%）
│ └── 表数据量太小
└── 反向条件（不一定会失效）
├── != / <>
├── NOT IN / NOT EXISTS
└── IS NULL / IS NOT NULL
11.2 逐个拆解——为什么失效
场景1：索引列做运算或函数
-- ❌ 失效：age列做了+1运算
SELECT * FROM user WHERE age + 1 = 20;
-- ✅ 正确：等价改写，让索引列保持原样
SELECT * FROM user WHERE age = 19;
为什么失效？ B+树是按 age 列的原始值排序的。age + 1 之后，结果和原始索引的排列顺序不一致，无法利用B+树的有序性做二分查找。MySQL无法把 age + 1 = 20 等价转换为 age = 19（不是所有运算都能逆向推导），所以只能全表扫描。
-- ❌ 失效：函数包裹索引列
SELECT * FROM user WHERE YEAR(create_time) = 2023;
-- ✅ 正确：范围查询替代函数
SELECT * FROM user WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
同理，YEAR()函数对 create_time 做了变换，破坏了索引有序性。改写成范围查询，create_time 保持了原始值，B+树可以快速定位。
面试追问：“什么运算不会导致失效？”
→ 等号右边的运算不影响索引列本身。WHERE age = 10 + 5 没问题，优化器会先把右边算出来变成 WHERE age = 15，索引列没有被运算。
场景2：隐式类型转换
-- phone是VARCHAR类型
-- ❌ 失效：传了数字，MySQL会自动把phone转成数字来比较
SELECT * FROM user WHERE phone = 13800138000;
-- ✅ 正确：传字符串
SELECT * FROM user WHERE phone = '13800138000';
为什么失效？ MySQL的隐式转换规则是"把字符串转成数字"，等价于 CAST(phone AS SIGNED) = 13800138000。对phone列做了CAST函数转换，回到场景1的问题。
记忆口诀：字符串列传数字→索引失效；数字列传字符串→不失效。
因为反过来时，MySQL是把输入的字符串转成数字（'123' → 123），索引列本身没被转换。
面试真题： “表t中a列是INT类型，WHERE a = '1' 走索引吗？” → 走！MySQL把’1’转成1，索引列没变。
场景3：隐式字符编码转换
-- 表A utf8mb4，表B utf8
SELECT * FROM A JOIN B ON A.name = B.name; -- B的name索引可能失效
为什么失效？ 两个表字符集不同时，MySQL会自动把较简单的编码转成较复杂的（utf8 → utf8mb4）。如果被转换的是索引列，等价于对索引列做CAST，索引失效。
解决： 建表时统一用 utf8mb4，这是现在最推荐的做法。
场景4：LIKE左模糊
-- ❌ 失效：左模糊，无法确定前缀
SELECT * FROM user WHERE name LIKE '%三';
-- ✅ 走索引：右模糊，前缀确定
SELECT * FROM user WHERE name LIKE '张%';
-- ❌ 失效：全模糊
SELECT * FROM user WHERE name LIKE '%三%';
为什么失效？ B+树按索引列值排序，相当于一本按拼音排序的字典。知道开头是"张"可以快速翻到J区域；但只知道结尾是"三"，整本字典都要翻一遍。
左模糊的优化方案：
- 全文索引：
ALTER TABLE user ADD FULLTEXT INDEX ft_name(name)，然后用MATCH(name) AGAINST('三') - 搜索引擎：数据量大时用Elasticsearch
- 数据冗余：把字符串反转存储，
LIKE '%三'变成反转列的LIKE '三%'
场景5：联合索引不满足最左前缀
上一篇已经详讲，这里补充一个易错点：
-- 索引 (a, b, c)
-- 这个查询只用到a，c用不上
SELECT * FROM t WHERE a = 1 AND c = 3;
-- 但如果a的区分度极高，只用到a也可能很快
-- 优化器会根据选择性决定是否走索引
场景6：OR连接非索引列
-- name有索引，age没有索引
-- ❌ 整体索引失效
SELECT * FROM user WHERE name = '张三' OR age = 20;
为什么失效？ OR意味着两个条件满足任一即可。name走索引只能找到name='张三’的行，但age=20的行在索引里找不到——因为age没有索引。要找到所有满足条件的行，就必须全表扫描。既然全表扫了，name的索引也就没意义了。
优化方案：
-- 方案1：给OR的每个列都建索引（index merge）
CREATE INDEX idx_age ON user(age);
-- MySQL可能用index merge分别走两个索引再合并结果
-- 方案2：用UNION改写
SELECT * FROM user WHERE name = '张三'
UNION
SELECT * FROM user WHERE age = 20;
注意： 即使OR两列都有索引，MySQL也不一定用index merge，优化器会判断哪种方式成本更低。
场景7-10：反向条件（!=, NOT IN, IS NOT NULL）
这些不一定失效，取决于数据分布：
-- 假设user表100万行，name='张三'只有10行
SELECT * FROM user WHERE name != '张三';
-- 优化器判断：要返回999990行，回表成本太高 → 全表扫描
-- 假设name='张三'有99万行
SELECT * FROM user WHERE name != '张三';
-- 优化器判断：只返回1万行，走索引更快 → 可能走索引
核心逻辑： 优化器会估算走索引和全表扫描的成本，选更低的那个。查询返回的行数占比越高，回表的代价就越大（每行都要随机IO），全表扫描反而更划算。
经验值： 返回超过表的20%-30%数据时，大概率全表扫描。
11.3 如何判断索引是否失效
EXPLAIN SELECT * FROM user WHERE age + 1 = 20;
重点看：
- type = ALL → 全表扫描，索引失效了
- key = NULL → 没用任何索引
- possible_keys有值但key为NULL → 优化器评估后放弃索引
11.4 索引失效速查表
| 场景 | 是否一定失效 | 解决方案 |
|---|---|---|
| 索引列运算/函数 | 一定 | 等价改写，保持索引列原样 |
| 隐式类型转换 | 一定 | 保证查询类型和列类型一致 |
| LIKE左模糊 | 一定 | 全文索引/ES/反转列 |
| 不满足最左前缀 | 一定 | 调整索引列顺序或查询条件 |
| OR含非索引列 | 一定 | 每个列都建索引 / UNION改写 |
| != / <> | 不一定 | 看数据分布，返回少就走索引 |
| NOT IN | 不一定 | 同上 |
| IS NULL / IS NOT NULL | 不一定 | 同上 |
| 查询占比过高 | 不一定 | 优化器主动选择全表扫 |
题目12：EXPLAIN怎么用？重点看哪些字段？
12.1 EXPLAIN是什么
EXPLAIN是MySQL提供的查询分析工具，在SELECT前加EXPLAIN就能看到执行计划——MySQL打算怎么执行这条查询，不需要真的执行。
EXPLAIN SELECT * FROM user WHERE name = '张三';
12.2 输出字段详解
EXPLAIN返回一行或多行，每行代表一个表的访问方式：
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
按重要性排序，重点看4个字段：type → key → rows → Extra
id——执行顺序
- id相同：从上往下依次执行
- id不同：id大的先执行（子查询先于外层）
- id为NULL：结果集临时表，不需要执行
EXPLAIN SELECT * FROM user WHERE id = (SELECT id FROM order WHERE amount > 100);
-- 子查询的id更大，先执行
select_type——查询类型
| 值 | 含义 | 性能关注 |
|---|---|---|
| SIMPLE | 简单查询，无子查询/UNION | ✅ 最好 |
| PRIMARY | 外层查询 | 正常 |
| SUBQUERY | 子查询 | ⚠️ 可能低效 |
| DERIVED | FROM中的子查询（派生表） | ❌ 会生成临时表 |
| UNION | UNION第二个及之后的SELECT | ⚠️ 可能临时表 |
DERIVED是最需要优化的——它意味着MySQL要先执行子查询，把结果存到临时表，再从临时表读。能用JOIN替代的尽量替代。
type——访问类型（最最最重要）
从好到坏的完整排序：
system > const > eq_ref > ref > range > index > ALL
最好 最差
| type | 触发条件 | 实例 | 性能 |
|---|---|---|---|
| system | 表只有一行（const的特例） | 系统表 | ★★★★★ |
| const | 主键/唯一索引等值查，最多返回1行 | WHERE id = 1 |
★★★★★ |
| eq_ref | JOIN时用主键/唯一索引关联 | JOIN ON a.id = b.id |
★★★★ |
| ref | 非唯一索引等值查，可能多行 | WHERE name = '张三' |
★★★ |
| range | 索引范围扫描 | WHERE age > 20 |
★★★ |
| index | 全索引扫描（遍历索引树） | 查询列都在索引中但无过滤 | ★★ |
| ALL | 全表扫描 | 没有合适索引/索引失效 | ★ 必须优化 |
面试要能说出的标准：
- 至少达到 range 级别才算合格
- ref 级别是日常查询的理想状态
- 看到 ALL 必须优化
const为什么快？ 主键索引是聚簇索引，等值查询走B+树，3层树高最多3次IO就拿到完整数据行。而且MySQL知道最多返回1行，不需要继续扫描。
index和ALL的区别： index是遍历索引树（数据量小），ALL是遍历数据文件（数据量大）。index比ALL好因为索引通常比数据小，但两者都是"扫描"而不是"查找"，都需要优化。
key和possible_keys——索引选择
- possible_keys：有哪些索引可用（候选列表）
- key：实际选了哪个索引（最终决定）
-- possible_keys有值但key为NULL
-- 说明优化器评估后认为走索引成本更高，放弃了
EXPLAIN SELECT * FROM user WHERE age > 0; -- 几乎所有行都满足
key_len——索引使用长度
这个字段用来判断联合索引用了几列，非常实用。
-- 索引 idx_abc (a INT, b VARCHAR(20), c INT)
-- 查询 WHERE a = 1
-- key_len = 4（INT占4字节 + 可能为NULL的1字节 = 5）
-- 只用了a列
-- 查询 WHERE a = 1 AND b = 'hello'
-- key_len = 5 + 20*3 + 2 + 1 = 68（VARCHAR(20)utf8mb4 + 长度前缀2字节 + NULL标志1字节）
-- 用了a和b列
计算规则速查：
- INT：4字节 + NULL标志1字节 = 5
- BIGINT：8字节 + 1 = 9
- VARCHAR(n) utf8mb4：n×4 + 2（长度前缀）+ 1（NULL）= 4n+3
面试技巧： 看key_len就能判断联合索引用到了第几列，不需要猜。
rows——预估扫描行数
优化器基于统计信息估算的值，不精确但够用。rows × 单行访问成本 = 查询总成本。
rows越少越好，超过万级就要关注。
Extra——额外信息
| Extra值 | 含义 | 评价 |
|---|---|---|
| Using index | 覆盖索引，不需要回表 | ✅✅✅ |
| Using index condition | 索引下推（ICP） | ✅✅ |
| Using where | WHERE过滤（正常） | ✅ |
| Using filesort | 额外排序，无法用索引排序 | ❌ 必须优化 |
| Using temporary | 用了临时表 | ❌ 必须优化 |
| Using join buffer | JOIN没走索引 | ⚠️ |
| Impossible WHERE | WHERE永远不成立 | 不需要优化 |
| Select tables optimized away | 聚合函数直接从索引取值 | ✅✅✅ |
Using filesort详解——面试必考：
filesort不一定是文件排序，名字有误导性。它的含义是：查询的排序无法利用索引的有序性，MySQL需要在内存中额外排序。
-- 索引 idx_name (name)
-- ❌ Using filesort：ORDER BY的列和索引不一致
EXPLAIN SELECT * FROM user WHERE name = '张三' ORDER BY age;
-- ✅ 无filesort：联合索引可以同时满足WHERE和ORDER BY
CREATE INDEX idx_name_age ON user(name, age);
EXPLAIN SELECT * FROM user WHERE name = '张三' ORDER BY age;
-- name等值过滤后，age在索引中已经有序，不需要额外排序
Using temporary详解：
-- ❌ Using temporary：GROUP BY没有走索引
EXPLAIN SELECT age, COUNT(*) FROM user GROUP BY age;
-- 如果age没有索引，MySQL要建临时表来做分组
-- ✅ 优化：给分组列加索引
CREATE INDEX idx_age ON user(age);
12.3 EXPLAIN分析实战——五步法
第1步：看type
→ ALL？必须加索引或改写查询
→ index？看能不能加WHERE条件变成range/ref
第2步：看key
→ NULL？索引没被使用，查原因
→ 和possible_keys不一致？优化器选了别的，考虑强制/忽略索引
第3步：看rows
→ 超过1万？考虑优化查询条件
第4步：看Extra
→ Using filesort？给ORDER BY列加联合索引
→ Using temporary？给GROUP BY列加索引
第5步：看key_len
→ 联合索引只用了部分列？检查最左前缀是否满足
12.4 面试追问
- “EXPLAIN的rows准确吗？” → 不准确，是基于统计信息的估算。
ANALYZE TABLE可以更新统计信息提高准确度。真正精确要看Handler_read_%状态变量。 - “type=index一定比ALL好吗？” → 不一定。如果索引覆盖了查询列（Using index），index比ALL好很多因为索引数据量小。但如果还要回表，index可能比ALL还慢——先扫索引再逐行回表，等于扫了两遍。
题目13：什么是事务？事务的ACID特性是什么？
13.1 事务是什么
事务是一组SQL语句的逻辑单元，要么全部成功，要么全部回滚。就像银行转账：扣款和加钱必须同时成功，不能只做一半。
-- 转账事务
START TRANSACTION;
UPDATE account SET balance = balance - 500 WHERE id = 1; -- A扣500
UPDATE account SET balance = balance + 500 WHERE id = 2; -- B加500
COMMIT; -- 两条都成功才提交
-- 如果第二条失败了
ROLLBACK; -- 第一条也回滚，A的钱不会少
13.2 ACID四个特性——面试按这个顺序答
A - 原子性（Atomicity）：要么全做，要么全不做
实现机制：undo log
事务执行过程：
原始数据 → 修改为 newData → 写undo log记录旧值
COMMIT：事务成功，undo log标记可清理
ROLLBACK：事务失败，读undo log恢复旧值
undo log就像"后悔药"——每次修改前先记录旧值，要回滚就按记录恢复。
面试追问：“undo log什么时候删除？” → 不是事务提交就立刻删除。如果还有其他事务在读这个旧版本（MVCC快照读），undo log必须保留。当没有任何事务需要这个版本时才清理。
C - 一致性（Consistency）：数据始终处于合法状态
一致性不是靠单一机制实现的，而是A+I+D共同保证的结果：
- 原子性保证事务不会只做一半
- 隔离性保证并发事务不会互相破坏
- 持久性保证已提交的数据不会丢
同时还需要应用层保证业务逻辑正确——比如转账不能转成负数，这需要代码层面检查，数据库只管"要么全做要么全不做"。
I - 隔离性（Isolation）：并发事务互不干扰
实现机制：MVCC + 锁
- 读操作：MVCC（快照读），不加锁
- 写操作：行锁 + Gap锁 + Next-Key Lock，防止并发修改冲突
详细机制在下一题和MVCC那题展开。
D - 持久性（Durability）：提交即永久
实现机制：redo log
事务提交流程（WAL——Write-Ahead Logging）：
① 修改数据页（先在Buffer Pool中修改）
② 写redo log（顺序写，很快）
③ redo log刷盘（fsync）
④ 返回客户端"提交成功"
⑤ 后台线程择时把Buffer Pool脏页刷到磁盘
为什么不用"直接写数据文件"而用redo log？
- 数据文件是随机写（数据散落在不同位置），一次修改可能需要多次寻道
- redo log是顺序写（一直往后追加），一次fsync搞定
- 顺序写比随机写快几个数量级
这就是WAL（Write-Ahead Logging）的核心思想：先写日志，再写数据。 即使数据库在刷脏页前崩溃，重启后根据redo log重做一遍就能恢复。
面试追问：“redo log刷盘策略是什么？”
→ 由 innodb_flush_log_at_trx_commit 控制：
- =1：每次提交都fsync（最安全，默认）
- =2：每次提交写OS缓存，每秒fsync（可能丢1秒数据）
- =0：每秒写+fsync（可能丢1秒数据）
生产环境必须设为1，这是ACID中D的保证。
13.3 事务的典型使用场景
| 场景 | 事务保证 |
|---|---|
| 转账 | 扣款和加钱同时成功 |
| 订单下单 | 减库存+创建订单+扣款，任一失败全部回滚 |
| 数据迁移 | 批量操作要么全迁移成功，要么回退 |
13.4 事务的注意事项
- 事务要尽可能短——长事务占用锁和连接，影响并发
- 避免在事务中做RPC调用——外部调用耗时不控，可能导致长事务
- 只把必须原子操作的SQL放在事务内——查询可以放事务外
题目14：事务的隔离级别有哪些？分别解决了什么问题？
14.1 三大并发问题
理解隔离级别之前，必须先理解它要解决什么问题。三个问题由轻到重：
脏读——读了别人没提交的数据
时间线 事务A 事务B
T1 START TRANSACTION
T2 START TRANSACTION
T3 UPDATE user SET balance = 200 WHERE id = 1
（原来balance=100，改成了200，但没提交）
T4 SELECT balance FROM user WHERE id = 1
→ 读到200（脏数据！B可能回滚）
T5 ROLLBACK（回滚了，balance实际还是100）
T6 A以为余额是200，但实际是100 → 数据不一致！
危险程度： 最严重。基于错误数据做决策，后果不可预测。
不可重复读——同一行数据两次读结果不同
时间线 事务A 事务B
T1 START TRANSACTION
T2 SELECT balance FROM user WHERE id = 1
→ 100
T3 UPDATE user SET balance = 200 WHERE id = 1
T4 COMMIT
T5 SELECT balance FROM user WHERE id = 1
→ 200（同一事务内两次读同一行，结果变了！）
和脏读的区别： 事务B已经提交了，数据是"真实"的，不是脏数据。但对事务A来说，同一事务内看到的数据前后不一致。
幻读——同一查询两次结果集行数不同
时间线 事务A 事务B
T1 START TRANSACTION
T2 SELECT * FROM user WHERE age BETWEEN 20 AND 30
→ 返回5行
T3 INSERT INTO user(age) VALUES(25)
T4 COMMIT
T5 SELECT * FROM user WHERE age BETWEEN 20 AND 30
→ 返回6行（多了一行"幻影"行！）
和不可重复读的区别：
- 不可重复读：已有行的内容变了（UPDATE/DELETE）
- 幻读：结果集的行数变了（INSERT/DELETE）
面试中这俩容易混淆，记住：不可重复读盯"行内容"，幻读盯"行数量"。
14.2 四个隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 性能 |
|---|---|---|---|---|
| 读未提交（RU） | 可能 | 可能 | 可能 | 最高 |
| 读已提交（RC） | 解决 | 可能 | 可能 | 高 |
| 可重复读（RR） | 解决 | 解决 | 大部分解决 | 中 |
| 串行化（Serializable） | 解决 | 解决 | 完全解决 | 最低 |
从上到下：安全性越来越高，性能越来越低。
读未提交（Read Uncommitted）
最低级别，几乎不用。一个事务能读到另一个事务未提交的修改，没有任何隔离保障。
读已提交（Read Committed）
- Oracle和SQL Server的默认级别
- 解决了脏读：只能读到已提交的数据
- 仍然有不可重复读和幻读
实现： 每次SELECT都生成新的Read View，所以能看到其他事务刚提交的修改。
可重复读（Repeatable Read）
- MySQL InnoDB的默认级别
- 解决了脏读和不可重复读
- 在一定程度上解决了幻读
实现： 事务内第一次SELECT生成Read View，后续SELECT复用同一个Read View，所以看不到其他事务的修改。
"一定程度上解决幻读"是什么意思？
场景1：快照读——不会幻读 ✅
事务A: SELECT * WHERE age > 20; （快照读，基于Read View，看不到新插入的行）
场景2：当前读——可能幻读 ⚠️
事务A: SELECT * WHERE age > 20; → 5行
事务B: INSERT INTO user(age) VALUES(25); COMMIT;
事务A: UPDATE user SET name='x' WHERE age > 20; → 更新了6行！
事务A: SELECT * WHERE age > 20; → 6行（幻读！）
原因： UPDATE是当前读，会读取最新数据并加锁。新插入的行被UPDATE看到了，之后快照读也能看到（因为事务自己修改了这行）。
InnoDB的解决方案： 对当前读使用 Next-Key Lock（行锁+Gap锁），锁定索引范围，阻止其他事务在范围内插入新行。
串行化（Serializable）
最高级别，所有事务串行执行，完全隔离但性能最差。实际生产几乎不用。
14.3 InnoDB的RR如何防止幻读
双重保障：
普通SELECT（快照读）
→ MVCC + Read View
→ 看到的是事务开始时的快照
→ 其他事务新插入的行在快照中不可见
当前读（SELECT FOR UPDATE / INSERT / UPDATE / DELETE）
→ Next-Key Lock = 行锁 + Gap锁
→ 锁定索引记录之间的间隙
→ 其他事务无法在锁定范围内插入新行
Gap锁的例子：
-- 事务A
SELECT * FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE;
-- InnoDB不仅锁住age=20到30的行，还锁住这些行之间的间隙
-- 事务B尝试 INSERT INTO user(age) VALUES(25) → 被阻塞！
-- 这就是RR级别下InnoDB防止幻读的机制
14.4 隔离级别选择建议
| 场景 | 推荐级别 | 原因 |
|---|---|---|
| 大多数互联网应用 | RR（默认） | MySQL默认，平衡安全和性能 |
| 需要看到实时数据 | RC | 如实时监控、库存查询 |
| 金融核心交易 | RR或Serializable | 数据一致性要求极高 |
面试追问：“为什么有些大厂把隔离级别改成RC？”
→ 1）RC的Gap锁更少，锁冲突更少，并发更高；2）RC下每条语句都能看到最新提交数据，对某些业务更合理；3）binlog格式必须配成ROW，否则主从不一致。
题目15：什么是MVCC？实现原理是什么？
15.1 MVCC是什么
MVCC = Multi-Version Concurrency Control（多版本并发控制）。
一句话理解：读操作读历史版本（快照），写操作写最新版本，两者互不阻塞。
解决的问题：
- 传统锁机制：读要等写释放，写要等读完成 → 并发性能差
- MVCC：读不加锁（快照读），写只锁修改的行 → 读写并发高
15.2 三大组件
MVCC的实现依赖三个组件配合：
┌──────────────────────────────────────────────────┐
│ MVCC 三大组件 │
│ │
│ ① 隐藏字段 ② undo log ③ Read View │
│ (每行记录的) (版本链) (可见性判断)│
│ │
│ DB_TRX_ID 旧版本数据 m_ids │
│ DB_ROLL_PTR →形成链表→ min_trx_id │
│ DB_ROW_ID max_trx_id │
│ creator_id │
│ │
│ 记录"谁改的" 记录"改之前" 判断"能不能看"│
└──────────────────────────────────────────────────┘
15.3 组件1：隐藏字段
每行InnoDB数据记录除了用户定义的列，还有3个隐藏字段：
| 字段 | 大小 | 作用 |
|---|---|---|
| DB_TRX_ID | 6字节 | 最后修改该行的事务ID |
| DB_ROLL_PTR | 7字节 | 回滚指针，指向undo log中的上一版本 |
| DB_ROW_ID | 6字节 | 行ID（无主键时用作聚簇索引） |
数据行实际存储：
┌──────┬──────┬────────────┬────────────┬────────────┐
│ id │ name │ balance │ DB_TRX_ID │ DB_ROLL_PTR│
├──────┼──────┼────────────┼────────────┼────────────┤
│ 1 │ 张三 │ 100 │ 5 │ → undo log │
└──────┴──────┴────────────┴────────────┴────────────┘
↑ 事务5最后修改了这行
↑ 指向之前的版本
15.4 组件2：undo log版本链
每次UPDATE时，旧版本不会直接丢弃，而是写入undo log，通过DB_ROLL_PTR串成链表：
当前数据行（V3，trx_id=5）
│ DB_ROLL_PTR
▼
undo log V2（trx_id=3）
│ DB_ROLL_PTR
▼
undo log V1（trx_id=1）
│ DB_ROLL_PTR
▼
NULL（最初版本）
INSERT没有版本链——插入的行只有当前版本，没有"修改之前"的状态。DELETE相当于把DB_TRX_ID标记为删除事务的ID。
15.5 组件3：Read View——可见性判断的核心
Read View是事务进行快照读时生成的"快照"，记录了"在生成这个快照的那一刻，哪些事务是活跃的（未提交的）"。
Read View的四个关键字段：
| 字段 | 含义 |
|---|---|
| m_ids | 生成Read View时，所有活跃（未提交）事务的ID列表 |
| min_trx_id | m_ids中最小的事务ID |
| max_trx_id | 下一个要分配的事务ID（= 当前最大事务ID + 1） |
| creator_trx_id | 创建这个Read View的事务ID |
可见性判断规则——按顺序检查：
给定一条记录的trx_id，判断它对当前事务是否可见：
步骤1：trx_id == creator_trx_id？
→ 是：可见（自己修改的数据，当然能看到）
步骤2：trx_id < min_trx_id？
→ 是：可见（这个事务在Read View生成前就已经提交了）
步骤3：trx_id >= max_trx_id？
→ 是：不可见（这个事务在Read View生成后才开始）
步骤4：min_trx_id <= trx_id < max_trx_id 且 trx_id在m_ids中？
→ 是：不可见（这个事务在生成Read View时还没提交）
→ 否：可见（这个事务在生成Read View时已经提交了）
如果不可见 → 顺着undo log版本链找上一个版本，重复判断
用具体数字举例：
当前活跃事务：m_ids = [3, 5, 7]
min_trx_id = 3
max_trx_id = 9（下一个要分配的ID）
记录A：trx_id = 2 → 2 < 3 → 可见（事务2在Read View之前就提交了）
记录B：trx_id = 5 → 5在m_ids中 → 不可见（事务5还没提交）
记录C：trx_id = 6 → 6不在m_ids中且6<9 → 可见（事务6已提交）
记录D：trx_id = 10 → 10 >= 9 → 不可见（事务10在Read View之后才开始的）
15.6 RC和RR的MVCC区别
核心区别只有一点：Read View的生成时机不同。
| 隔离级别 | Read View生成时机 | 效果 |
|---|---|---|
| RC | 每次SELECT都生成新的Read View | 能看到其他事务最新提交的修改 → 不可重复读 |
| RR | 事务内第一次SELECT生成Read View，后续复用 | 同一事务内多次读结果一致 → 可重复读 |
具体例子：
时间线 事务A（RR） 事务B
T1 START TRANSACTION
T2 SELECT balance（生成Read View）
→ 100
T3 UPDATE balance=200; COMMIT
T4 SELECT balance（复用T2的Read View）
→ 还是100！Read View没变，事务B的修改不可见
如果事务A是RC级别：
T4 SELECT balance（生成新的Read View）
→ 200！新的Read View能看到事务B的提交
这就是为什么RC有不可重复读而RR没有——本质是Read View的生成策略不同。
15.7 快照读 vs 当前读
| 读类型 | 语句 | 是否加锁 | 是否走MVCC |
|---|---|---|---|
| 快照读 | 普通SELECT | 不加锁 | ✅ 走MVCC |
| 当前读 | SELECT FOR UPDATE | 加Next-Key Lock | ❌ 读最新数据 |
| 当前读 | SELECT LOCK IN SHARE MODE | 加共享锁 | ❌ 读最新数据 |
| 当前读 | INSERT / UPDATE / DELETE | 加行锁 | ❌ 读最新数据 |
为什么需要当前读？ 有些业务场景必须看到最新数据。比如扣库存——如果读的是快照，可能库存已经为0但你还看到有库存，就会超卖。
15.8 MVCC完整流程示例
初始数据：id=1, name='张三', balance=100, trx_id=1
时间线 事务3（A） 事务4（B） 事务5（C）
T1 START TRANSACTION
T2 START TRANSACTION
T3 START TRANSACTION
T4 UPDATE balance=200
（trx_id=4, 旧值写undo log）
T5 SELECT balance
（生成Read View: m_ids=[3,4]）
T6 COMMIT（事务4提交）
T7 UPDATE balance=300
（trx_id=5, 旧值写undo log）
T8 SELECT balance
（RR：复用T5的Read View）
T5时刻事务3读到什么？
- 当前数据 trx_id=4，4在m_ids=[3,4]中 → 不可见
- 沿undo log找到上一个版本 trx_id=1，1 < min_trx_id=3 → 可见
- 读到 balance=100 ✅
T8时刻事务3读到什么？
- 当前数据 trx_id=5，5 >= max_trx_id → 不可见
- 沿undo log找到 trx_id=4 的版本，4在m_ids=[3,4]中 → 不可见
- 继续沿undo log找到 trx_id=1 的版本 → 可见
- 还是读到 balance=100 ✅（RR级别，可重复读）
15.9 面试追问
- “MVCC能完全解决幻读吗？” → 快照读可以，当前读不行。当前读需要Next-Key Lock配合才能防止幻读。
- “undo log会无限增长吗？” → 不会。purge线程定期清理不再被任何Read View需要的旧版本。但如果存在长事务，它需要的旧版本不能被清理，undo log就会膨胀——这也是为什么长事务危害大。
- “MVCC在什么场景下性能优势最明显？” → 读多写少的OLTP场景。读操作完全不加锁，读写并发度极高。
写在最后
- 索引失效的本质是破坏有序性——理解了这一点，所有场景都能推导
- EXPLAIN是诊断工具——type和Extra是面试必考，filesort和temporary是优化重点
- ACID的实现机制——undo log管回滚、redo log管持久化、MVCC+锁管隔离，三件套各司其职
- MVCC的核心——隐藏字段记录版本、undo log串版本链、Read View判断可见性，三个组件配合完成快照读
- RC vs RR的根因——Read View的生成时机不同，这一个区别导致了不可重复读问题
AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。
更多推荐
4
0- 0
已为社区贡献3条内容
所有评论(0)