mysql B+Tree的结构

如果我们去设计mysql, 我们应该以什么样的数据结构存储数据呢?

想想这种数据结构需要满足什么条件?

该结构需要合理安排内存空间, 不会浪费大量内存, 也不会产生内存碎片
查找数据一定要快, 因为数据库一般都是读多写少的场景

如果说查找速度快那最好的几种结构就非常明显了:

树
数组

数组的问题在于添加和删除速度慢

那么只能选择树

但不是一般的树, 比如二叉树就不合适, 二叉树的深度影响数据的查询和插入的速度, 而且还可能退化为单向链表

红黑树也是, 深度太深

所以只能用到B树

但是B树还是不够, 需要改造, 每个节点上都有很多数据

我们可以效仿linux文件系统, 使用大量"指针", 指定存储数据的位置和读取数据的位置

所以对B Tree的改造也可以变成:

索引(指针)和最底层的叶子节点(索引+数据)

这些作为指针的属性应该是什么呢?

它可以是主键id

下图是聚集索引大致的样子:

B+Tree的叶子是这样:

非聚集索引大体上的样子:

这里有一个问题, 那就是非叶子节点上为什么需要id?

这个问题可以在后面的内容看到为什么这么做

那么数据库的id怎么来?

我们知道，在MySQL中每个行都应该具有一个唯一的主键（primary key）。如果我们在创建表时没有明确指定主键，MySQL会默认查找是否存在唯一索引（unique index）。如果存在多个唯一索引，则MySQL会选择最前面的一个唯一索引作为主键。如果表中没有唯一索引，MySQL会提供一个默认主键，但是这个主键对于用户来说是不可见的。

在mysql中, b+tree的查到单元是页, 不是某个row

他拿着两个空行（infimum和supremum），指向一个数据页的最开始位置和最后位置。一个数据页通常是16KB大小（具体大小可以根据配置进行调整），而一个区由多个数据页组成，通常为1MB大小。

意向锁(表锁)

意向: 你想要上锁的意向而且是个表锁

意向锁我觉得是一个记录, 在上锁前查一查它

意向锁分为共享意向(IS)和排他意向(IX)

为什么需要意向锁?

如果需要申请表级锁，但是某一行已经被另一个事务排它锁定了，我们的事务该怎么知道呢？一种简单粗暴的方法是，扫描整个表，查找是否有行被排它锁定。然而，这种方法效率极低，耗时长，十分不可取。

因此，我们引入了意向锁的概念。意向锁实际上是一种表级锁。当一个事务要获取某个表的行级锁时，它会先去查看该表的意向锁。如果存在意向锁，那么说明有其他事务正在对该表的行进行加锁，此时我们的事务应该等待，直到其他事务释放了锁。因此，其他事务在申请表级锁之前都应该先去查看该表的意向锁，以避免冲突和死锁的发生。

什么情况下会出现需要锁住一个表的情况呢？

这通常发生在需要对表进行数据修改的场景中，比如执行UPDATE语句。如果我们在UPDATE语句中没有使用索引，那么MySQL就可能会采用全表扫描的方式来查找需要更新的行，此时很有可能出现记录锁和间隙锁的情况，最终导致需要锁住整个表。

当然，如果我们在UPDATE语句中使用了合适的索引，MySQL就可以利用索引快速定位需要更新的行，避免全表扫描和锁表的情况的发生。因此，在设计数据库时，应该合理使用索引以提高查询效率和避免锁表的问题。

或者出现在 alter table命令下也会出现表锁

如果我们要对某个行或几行上排它锁，那么需要先上意向排它锁，然后再上具体的排它锁。而如果我们要对某个行或几行上共享锁，那么直接上行锁即可，意向锁不会阻塞任何行锁。

当一个事务要对整个表上锁时，如果发现已经存在意向锁，那么该事务会被阻塞，直到其他事务释放了锁。因此，在进行数据操作时，应该尽可能减少对表级别的加锁，优先使用行锁和间隙锁，以保证系统的并发性和性能。

意向锁兼容

记住两句话, 这张图可以不看

间隙锁之间全部兼容
单个字母的锁和另一个锁组合时, 如果他们俩的名字出现 X , 那么不兼容

第一点不用说了

看第二点

S和IX组合: S + IX = SIX 存在 X , 不兼容
S和S组合: S + S = SS 没有 X, 兼容
S和IS组合: S + IS = ISS 没有 X, 兼容
X和IS组合: X + IS = XIS 有 X, 不兼容

然后现在带入实际情况:

事务1使用行级排他锁, 事务2准备使用意向共享锁, X + IS 有 X, 所以事务2阻塞

行级锁

是什么? 或者有哪些行级锁?

记录锁(Record lock): 锁住一个记录, 但是该记录需要是唯一索引或者主键索引
间隙锁(Gap lock): 锁住的是一个范围, 但不包含中间的记录, 针对非唯一索引的情况下
临键锁(Next-key lock): gap lock + record lock, 也是针对非唯一索引

记录锁

锁住一个记录, 但是该记录需要是唯一索引或者主键索引

session1	session2
START TRANSACTION;	START TRANSACTION;
SELECT * from zhazha WHERE a = 3 FOR UPDATE; -- 排他锁
	UPDATE zhazha SET b = 111 WHERE a = 1; -- 不阻塞
	UPDATE zhazha SET b = 333 WHERE a = 5; -- 不阻塞
	UPDATE zhazha SET b = 222 WHERE a = 3; -- 阻塞
COMMIT;
	UPDATE zhazha SET b = 222 WHERE a = 3; -- 不阻塞
	COMMIT;

如果两个session都去修改 a = 3 这一行记录, 就会发生阻塞

在唯一索引下, next key lock 会退化为 record lock

相同的情况在非唯一索引下, 就不一样了

间隙锁/临键锁

间隙锁: 锁住的是一个范围, 但不包含中间的记录, 针对非唯一索引的情况下

临键锁: gap lock + record lock, 也是针对非唯一索引

幻读是什么?

在事务1中执行 select * from zhazha where b = 3 for update; 之后

另一个事务2执行了 delete from zhazha where b = 3;

紧接着事务1中执行 select * from zhazha where b = 3 for update; 之后

发现两次事务1执行 select 查询出来是不同的 row, 第一次有值, 后面一次没值

那么幻读出现了

还有一种就是

事务1: select * from zhazha where b > 3 for update

事务2: insert into zhazha values(99, 98)

事务1: select * from zhazha where b > 3 for update

两次事务1执行的结果不同, 导致出现了幻读问题

这里为什么要使用 for update 呢?

因为如果使用 delete 或者 update 我测试比较不方便, 所以选择了 select for update

那么间隙锁如何防止幻读的?

CREATE TABLE `zhazha` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test`.`zhazha` (`a`, `b`) VALUES (1, 1);
INSERT INTO `test`.`zhazha` (`a`, `b`) VALUES (3, 1);
INSERT INTO `test`.`zhazha` (`a`, `b`) VALUES (5, 3);
INSERT INTO `test`.`zhazha` (`a`, `b`) VALUES (7, 6);
INSERT INTO `test`.`zhazha` (`a`, `b`) VALUES (10, 8);

session1:

START TRANSACTION;

SELECT * from zhazha WHERE b = 3 FOR UPDATE; -- 排他锁

session2:

START TRANSACTION;

SELECT * from zhazha WHERE a = 5 LOCK in share mode; -- 阻塞, 读锁碰到写锁

INSERT into zhazha VALUES(4, 1); -- 阻塞, 上了间隙锁(这里实际上是上了next key lock, 但是没差记住3也是next key lock就行了), 防止插入
INSERT into zhazha VALUES(4, 2); -- 阻塞, 同理
INSERT into zhazha VALUES(6, 5); -- 阻塞, 在(3, 6]区间

INSERT into zhazha VALUES(4, 7); -- 不阻塞, 间隙锁范围[1, 3](next key), (3, 6](gap)
INSERT into zhazha VALUES(8, 6); -- 不阻塞, 和 (7, 6) 差不多, 6 会匹配(3, 6], 但是gap lock的返回其实是(3, 6.7] 这里的 7 是 id, 而我们的 (8, 6) 是 6.8 , 大于 6.7
INSERT into zhazha VALUES(2, 0); -- 不阻塞, 在锁之外[1, 3](next key), (3, 6](gap)
INSERT into zhazha VALUES(6, 7); -- 不阻塞, 在锁之外[1, 3](next key), (3, 6](gap)

间隙锁范围[1, 3](next key), (3, 6](gap)

那么如果session1执行

select * from zhazha where b > 3 for update

的时候呢?

间隙锁是锁住了哪个范围?

insert into zhazha values(97, 2); -- 不阻塞
insert into zhazha values(98, 3); -- 阻塞
insert into zhazha values(99, 98); -- 阻塞

范围是 [3, 无限大), 这是 next key lock

还记的上面的这个问题么?

为什么非叶子节点也需要id? 不去从叶子节点拿到 id ?

INSERT into zhazha VALUES(8, 6); -- 不阻塞

注意看这行sql语句

我们间隙锁的范围不是 [1, 3], (3, 6] 么? 它不应该阻塞么 ?

它为什么不阻塞?

首先我们知道, 因为 b = 3 上了排它锁, 需要添加间隙锁, 防止幻读

但是他的详细范围一直没细讲

其实他的范围是 [1, 3], (3, 6.7]

对, 就是6.7, 这里的 6 是 b 的值, 而 7 是主键 id

而我们插入的记录是 6.8 , 不在上面的范围

那底层怎么实现的防止幻读的?

我们先看这么一个场景

虽然上了 gap lock的记录可以删除, 但是跟幻读没关系啊?

确实, 那可以看看下面操作:

会发现, 明明在另一个会话删除掉的记录, 在当前会话上还存在, 但是在物理磁盘上却不存在

为什么?

简单, 他被存储到 undo log 中了, 所以前面一次查询将 b = 6 这一行代码保存到了 undo log 中

下次的 select b = 6 将会从 undo log 中查询出来

这就是解决幻读的方法

总结

幻读的解决方法就两步:

上间隙锁, 在你的范围内无法添加新的记录
备份, 将访问过的记录备份到 undo log 中, 在下次查询时从 undo log 中查询出来, 根据事务id查哦~~~

记住, 在间隙锁中, 需要在可重复读的情况下才会有间隙锁(或者next key lock)

`MVCC`(多版本并发控制)

MVCC怎么实现的呢?

非常简单, 使用undo log完事

你把 undo log 看做是一个链表, 每个元素带着事务id, 也就是版本

所以MVCC的多版本就出来了, 相对于同一个数据, 比如小明同学, 那么在 undo log 中存在多个, 但是版本不同

并发呢? 使用快照保证, 如果你是读取的话直接从快照读, 这里的快照说白了就是 undo log 未提交事务前的数据, 因为 undo log 在mysql修改前, 会存放一个旧的值, 所以对于小明这条数据他还是旧的, 没经过修改的版本

如果你是写, 那么就会新建事务, 并写入一个新的undo log元素(没有在间隙锁的范围下)

在读已提交的场景下, 我们的读写请求都是从快照中读取, 而且是从最新的快照中读取

这里我们需要知道, 事务是并发的, 在读已提交的场景下, 最新版本是不断迭代变化的

因为还有多个事务可能在并发的修改并提交了最新版本

所以前一秒读到的可能是小明 1 岁, 后一秒就变成小明 2 岁

你可以把这个场景看做是你从github上下载软件并使用, 你是那种比较喜欢去github release 上下载最新版本的人

那么可重复读呢?

简单, 读取那次最新的版本, 然后将其保存, 以后一直读取那次的版本, 比如事务id为2, 现在即便事务id变成了10000, 可重复读还是读事务 id = 2 的数据

可重复读的你, 就是一个喜欢去github上下载最新版本, 然后将该app保存到百度网盘备份的人, 下次要使用的时候, 都会从百度网盘下载该软件, 而不是从github官方下载最新的release版本

谈谈mysql mvcc和锁