品牌型网站制网站设计规划图

一文看懂 Mysql 核心存储 & 隔离级别 & 锁 & MVCC 机制

Mysql InnoDB 引擎下核心存储

数据&索引存储 IBD 文件

mysql 实际存储采用 B+ 树结构。

B+ 树是一种多路搜索树，其搜索性能高于 B 树

1. 所有叶节点在同一深度，保证搜索效率
2. 仅叶节点存储实际数据，其他节点仅存储索引不存储数据，节省空间可存储更多索引
3. 每个节点存储一组数据，这组数据由小到大排列
4. 叶子节点用双向指针链接，提高区间访问效率
5. 在 B+ 树中，索引数据可能会被冗余存储

推荐一个在线构建 B+ 树的网址 https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

在 mysql 中，一个节点也可以成为一页 ，这个节点数据默认最大 16kb = 16384 byte

SELECT @@innodb_page_size; 

可以用这个语句查询。

所以在 mysql 中实际的数据存储就是以 B+ 树索引的形式存储的，叶子节点带有所有数据，索引字段是主键 id。

其他非主键索引，叶子节点带有的是主键数据和索引数据，如果要查询列表不包含在索引字段内，会进行”回表“去查询遍主键索引获取所需要的数据，所以有时候优化查询速度时会考虑简历覆盖索引避免回表。

缓存&日志

mysql 在实际存储和操作时并非直接操作磁盘，这样效率太慢了。

所以基本上优化磁盘速度 --> 使用内存缓存

内存不可靠问题 --> 顺序写日志

在内存缓存上：

1. BufferPool：数据的增删改查都是直接操作 BufferPool ，比如查询时会直接 load 相关的一页数据到 BufferPool 中
2. RedoLogBuffer：写 redolog 的缓冲区，在合适的时机高效批量写入 如 COMMIT 操作时、执行 CHECKPOINT 操作时（MySQL 内部定期执行的一种操作）

在日志上：

1. undolog（InnoDB）：用于一个事务中执行失败时的回滚操作以及在可见级别为 可重复读和读已提交 下 MVCC 机制的实现
2. redolog（InnoDB）：用于在故障时重放没有同步到磁盘的操作数据，同步数据到 binlog 后会将数据清除
3. binlog：记录全量数据日志，用于恢复磁盘数据

MySQL 会在以下情况下将 redolog 中的数据同步到 binlog 中：

1. 执行 COMMIT 操作时。
2. 执行 FLUSH LOGS 操作时。
3. 执行 FLUSH BINARY LOGS 操作时。
4. 执行 FLUSH TABLES 操作时。
5. 执行 FLUSH TABLES WITH READ LOCK 操作时。
6. 执行 FLUSH RELAY LOGS 操作时。
7. 执行 FLUSH RELAY LOGS WITH READ LOCK 操作时。
8. 执行 FLUSH MASTER 操作时。

最终存储 IBD

IBD 文件是 MySQL 中用于存储表数据的文件，它以索引形式存储数据

事务隔离级别

在并发多事务的情况下数据的读写更新会存在一些问题，这与我们设置的隔离级别息息相关。

1. 脏写：指在一个事务中，更新了一行数据，但是这一行数据还没有提交，另一个事务也更新了这一行数据， 新的事物覆盖了旧事物写的值，导致旧事物的更新失效
2. 脏读：事务 A 读到了 事务 B 修改但未提交的数据
3. 不可重复读： 事务 A 内部相同查询语句查询结果不同，读到了其他事务已提交的数据
4. 幻读：事务 A 读取到了事务 B 新增的数据

mysql 支持的事务隔离级别

默认的隔离级别是 可重复读 也是我们最常用的隔离级别。

表锁&行锁&间隙锁

在不同的纬度上 mysql 的锁分很多种：

性能上：

1. 悲观锁
2. 乐观锁（版本比较）

操作分类上

1. 读锁 共享做 S 锁
2. 写锁 排他锁 X 锁

锁粒度上

1. 表级锁
2. 行锁
3. 间隙锁

在 innodb 引擎下聊聊锁

表锁

锁全表的情况一般出现在表结构变更时，或者手动 lock table 时

如果一个事务需要更新多行，MySQL 会尝试加上一个表锁

行锁

在写数据是会对该行数据加行锁即 在执行INSERT/UPDATE/DELETE语句时

无索引行进行 update 行锁可能会升级为表锁

间隙锁

在更新数据时，在此数据与其他数据存在的空隙加锁，如 存在 id = 1 ，3， 5 的数据

此时更新 3 号数据，会锁 （1,3）（3,5） 这两个区间，无法插入数据，这就是间隙锁，可以解决一部分幻读问题

而 3 号数据在更新所以这个行会加行锁

行锁 + 对应的间隙锁 也称之为 临键锁。

MVCC 机制

这个机制是保证可重复读和读已提交的核心机制。是基于 undolog 版本链 以及 一致性视图 read-view

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据 undo 回滚

日志，并且用两个隐藏字段 trx_id 和 roll_pointer 把这些 undo日志串联起来形成一个历史记录版本链。

而一致性视图是在一个事务开启后，执行任何查询 sql 时进行生成，read-view 会生成当前所有未提交事务的 id

而在 undolog 中 trx_id 小于未提交事务 id 的记录都是已提交记录是可读的，而大于最大 id 的是视图生成时未开始的事务 不可读

如果 min_id < trx_id < max_id 且不在未提交数组中，则也是可读的