MySQL

发布日期: 2024-07-21

作者: 小乐

文章字数: 2.1k

阅读时长: 8 分

阅读次数:

MySQL MVCC多版本并发控制

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、为什么需要MVCC

1.1 并发读写的矛盾

传统锁机制下：

读操作加共享锁
写操作加排他锁
读写互相阻塞

在高并发场景下，大量读请求被写请求阻塞，性能急剧下降。

1.2 MVCC的解决思路

核心思想：写操作不覆盖旧数据，而是生成新版本；读操作根据可见性规则选择合适版本读取。

时间线：
T1: 读取数据（看到版本V1）
T2: 修改数据（生成版本V2）
T3: 读取数据（仍看到版本V1，不被T2阻塞）

二、MVCC的实现基础

2.1 隐藏字段

InnoDB每行记录包含三个隐藏字段：

字段	长度	说明
DB_TRX_ID	6字节	最后修改该记录的事务ID
DB_ROLL_PTR	7字节	回滚指针，指向Undo Log
DB_ROW_ID	6字节	隐藏主键（无显式主键时使用）

+------+------+---------+------------+-------------+------------+
| id   | name | balance | DB_TRX_ID  | DB_ROLL_PTR | DB_ROW_ID  |
+------+------+---------+------------+-------------+------------+
| 1    | Alice| 1000    | 100        | 0x7f...     | 1          |
+------+------+---------+------------+-------------+------------+

2.2 Undo Log版本链

每次UPDATE或DELETE操作，都会生成Undo Log：

当前记录（最新版本，DB_TRX_ID=100）
    │ name='Alice', balance=1200
    │ DB_ROLL_PTR
    ▼
Undo Log（上个版本，由事务80修改）
    │ name='Alice', balance=1000
    │ DB_ROLL_PTR
    ▼
Undo Log（更早版本，由事务50插入）
    │ name='Alice', balance=0（初始值）
    │ DB_ROLL_PTR = NULL
    ▼
   NULL

关键：通过DB_ROLL_PTR形成版本链，可以回溯到任意历史版本。

2.3 Undo Log的类型

insert undo log：

事务插入记录时产生
事务提交后即可删除（不需要用于MVCC）

update undo log：

事务更新或删除记录时产生
事务提交后不能立即删除
需要保留到没有比它更早的ReadView存在时

三、ReadView机制

3.1 什么是ReadView

ReadView是事务进行快照读时生成的”一致性视图”，决定事务能看到哪些版本的数据。

3.2 ReadView的组成

class ReadView {
    trx_id_t m_creator_trx_id;    // 创建该ReadView的事务ID
    ids_t m_ids;                   // 生成ReadView时活跃的事务ID列表
    trx_id_t m_up_limit_id;       // m_ids中的最小值（低水位）
    trx_id_t m_low_limit_id;      // 下一个要分配的事务ID（高水位）
}

3.3 可见性判断算法

对于某条记录，其DB_TRX_ID与ReadView的比较：

if (DB_TRX_ID == m_creator_trx_id) {
    // 自己修改的，可见
    return VISIBLE;
}

if (DB_TRX_ID < m_up_limit_id) {
    // 在ReadView创建前已提交，可见
    return VISIBLE;
}

if (DB_TRX_ID >= m_low_limit_id) {
    // 在ReadView创建后启动，不可见
    return INVISIBLE;
}

if (m_ids.contains(DB_TRX_ID)) {
    // 在ReadView创建时活跃（未提交），不可见
    return INVISIBLE;
} else {
    // 在ReadView创建前已提交，可见
    return VISIBLE;
}

不可见时的处理：
通过DB_ROLL_PTR找到上一个版本，继续判断。

3.4 不同隔离级别的ReadView差异

READ COMMITTED：

每次SELECT生成新的ReadView
能看到其他事务最新已提交的数据

T1（RC）：
  SELECT * FROM user WHERE id = 1;  -- ReadView1，看到V1
  
  -- T2修改并提交
  
  SELECT * FROM user WHERE id = 1;  -- ReadView2，看到V2

REPEATABLE READ：

事务第一次SELECT时生成ReadView
整个事务期间复用该ReadView

T1（RR）：
  SELECT * FROM user WHERE id = 1;  -- ReadView1，看到V1
  
  -- T2修改并提交
  
  SELECT * FROM user WHERE id = 1;  -- 仍用ReadView1，看到V1（不可重复读解决）

四、MVCC工作流程

4.1 插入操作

BEGIN;  -- 事务ID = 100
INSERT INTO user (id, name) VALUES (1, 'Alice');
COMMIT;

生成的记录：

id=1, name='Alice', DB_TRX_ID=100, DB_ROLL_PTR=NULL

4.2 更新操作

BEGIN;  -- 事务ID = 200
UPDATE user SET name = 'Bob' WHERE id = 1;
COMMIT;

执行过程：

复制旧记录到Undo Log
修改当前记录：
- name = ‘Bob’
- DB_TRX_ID = 200
- DB_ROLL_PTR → 指向Undo Log

当前记录:
id=1, name='Bob', DB_TRX_ID=200, DB_ROLL_PTR→Undo Log

Undo Log（事务100的版本）:
name='Alice', DB_TRX_ID=100, DB_ROLL_PTR=NULL

4.3 删除操作

BEGIN;  -- 事务ID = 300
DELETE FROM user WHERE id = 1;
COMMIT;

执行过程：

复制记录到Undo Log
标记当前记录的delete flag = true
DB_TRX_ID更新为300

当前记录:
id=1, name='Bob', DB_TRX_ID=300, DB_ROLL_PTR→Undo Log, delete_flag=true

Undo Log（事务200的版本）:
name='Bob', DB_TRX_ID=200, DB_ROLL_PTR→更早的Undo Log

4.4 查询操作（快照读）

SELECT * FROM user WHERE id = 1;  -- 快照读

执行过程：

找到id=1的记录
根据ReadView判断可见性
如果当前版本不可见，沿Undo Log链找到可见版本
返回可见版本的数据

4.5 当前读（Current Read）

SELECT * FROM user WHERE id = 1 FOR UPDATE;  -- 当前读
UPDATE user SET name = 'Charlie' WHERE id = 1;  -- 当前读
DELETE FROM user WHERE id = 1;  -- 当前读
INSERT INTO user ...  -- 当前读

当前读特点：

读取数据的最新版本
需要加锁
不受MVCC影响

五、MVCC与幻读

5.1 快照读与幻读

在REPEATABLE READ下：

-- 事务T1
BEGIN;
SELECT * FROM user WHERE id > 5;  -- 读到3条（快照读，ReadView生成）

-- 事务T2插入id=10并提交

SELECT * FROM user WHERE id > 5;  -- 仍读到3条（RR保证）

原因：第二次查询仍使用第一次的ReadView，id=10的记录DB_TRX_ID是T2的，对T1不可见。

5.2 当前读与幻读

-- 事务T1
BEGIN;
SELECT * FROM user WHERE id > 5 FOR UPDATE;  -- 当前读，加临键锁

-- 事务T2插入id=10 → 被阻塞（间隙锁）

InnoDB解决幻读：

快照读：通过MVCC的ReadView机制
当前读：通过间隙锁（Gap Lock）

5.3 幻读的特例

MVCC不能完全解决所有幻读场景：

-- 事务T1（RR）
BEGIN;
SELECT * FROM user WHERE id = 10;  -- 不存在，返回空

-- 事务T2插入id=10并提交

-- 事务T1
UPDATE user SET name = 'XXX' WHERE id = 10;  -- 成功！（当前读）
SELECT * FROM user WHERE id = 10;  -- 查到了！（幻读）

原因：UPDATE是当前读，能看到T2提交的数据并修改。修改后该记录的DB_TRX_ID变为T1，对自己可见。

六、MVCC的清理机制

6.1 Purge线程

Undo Log不能无限增长，需要清理：

确定清理范围：找到所有ReadView中最老的m_up_limit_id
清理Undo Log：删除比该ID更早且已提交的Undo Log
清理标记删除的记录：真正删除delete_flag=true且不可见的记录

6.2 历史列表长度

-- 查看Undo Log历史列表长度
SHOW ENGINE INNODB STATUS;
-- History list length 1000

History list length过大：

说明有大量未清理的Undo Log
可能原因：长事务、大量写操作
影响：查询需要遍历更多版本，性能下降

七、MVCC实践要点

7.1 避免长事务

-- 查看长事务
SELECT 
    trx_id,
    trx_mysql_thread_id,
    trx_state,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_seconds
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

长事务的危害：

持有Undo Log不释放
导致History list length增长
影响查询性能
可能导致锁等待

7.2 选择合适的事务隔离级别

场景	推荐隔离级别
高并发读、少量写	READ COMMITTED
需要严格一致性	REPEATABLE READ
金融交易	REPEATABLE READ + 乐观锁
报表统计	REPEATABLE READ

7.3 理解快照读和当前读

-- 快照读（不加锁，读历史版本）
SELECT * FROM user WHERE id = 1;

-- 当前读（加锁，读最新版本）
SELECT * FROM user WHERE id = 1 FOR UPDATE;
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;

-- DML都是当前读
UPDATE user SET ...;
DELETE FROM user WHERE ...;

八、总结

组件	作用
DB_TRX_ID	标识数据版本
DB_ROLL_PTR	链接历史版本
Undo Log	存储历史版本数据
ReadView	决定数据可见性

隔离级别	ReadView生成时机	效果
READ COMMITTED	每次SELECT	解决脏读
REPEATABLE READ	第一次SELECT	解决脏读、不可重复读

MVCC的核心价值：

读写不阻塞：读操作不加锁，写操作生成新版本
一致性读：同一事务内多次读取结果一致
高并发：大幅提升读密集型应用的并发能力

理解MVCC机制，有助于：

合理设计事务边界
选择合适的隔离级别
排查并发异常问题
优化长事务和Undo Log管理

核心要点

索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型
SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN
事务隔离级别的选择：根据业务需求选择合适的级别
常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。

转载规则

《MySQL MVCC多版本并发控制》由小乐采用知识共享署名 4.0 国际许可协议进行许可。

布隆过滤器适合解决什么问题

详解布隆过滤器适合解决什么问题的核心概念与实践方法，结合真实开发场景说明使用注意事项。

2024-07-21 算法

学习笔记算法

LRU 缓存淘汰算法动手实现

详解LRU 缓存淘汰算法动手实现的核心概念与实践方法，结合真实开发场景说明使用注意事项。

2024-07-20 算法

学习笔记算法

MySQL MVCC多版本并发控制

一、为什么需要MVCC

1.1 并发读写的矛盾

1.2 MVCC的解决思路

二、MVCC的实现基础

2.1 隐藏字段

2.2 Undo Log版本链

2.3 Undo Log的类型

三、ReadView机制

3.1 什么是ReadView

3.2 ReadView的组成

3.3 可见性判断算法

3.4 不同隔离级别的ReadView差异

四、MVCC工作流程

4.1 插入操作

4.2 更新操作

4.3 删除操作

4.4 查询操作（快照读）

4.5 当前读（Current Read）

五、MVCC与幻读

5.1 快照读与幻读

5.2 当前读与幻读

5.3 幻读的特例

六、MVCC的清理机制

6.1 Purge线程

6.2 历史列表长度

七、MVCC实践要点

7.1 避免长事务

7.2 选择合适的事务隔离级别

7.3 理解快照读和当前读

八、总结

核心要点

总结

写作不易，客官能否打赏一杯Cooffee？