记录些MySQL题集(9)

MySQL之死锁问题分析、事务隔离与锁机制的底层原理剖析

一、MySQL中的死锁现象

所谓的并发事务,本质上就是MySQL内部多条工作线程并行执行的情况,也正由于MySQL是多线程应用,所以需要具备完善的锁机制来避免线程不安全问题的问题产生,对于多线程与锁而言,存在一个100%会出现的偶发问题,即死锁问题

1.1、死锁问题概述(Dead Lock)

死锁是指两个或两个以上的线程(或进程)在运行过程中,因为资源竞争而造成相互等待、相互僵持的现象,一般当程序中出现死锁问题后,若无外力介入,则不会解除“僵持”状态,它们之间会一直相互等待下去。

1.2、MySQL中的死锁现象

MySQLRedis、Nginx这类单线程工作的程序不同,它属于一种内部采用多线程工作的应用,因而不可避免的就会产生死锁问题。当死锁问题出现时,MySQL会自动检测并介入,强制回滚结束一个“死锁的参与者(事务)”,从而打破死锁的僵局,让另一个事务能继续执行。

显示的定义了主键索引后,InnoDB会基于该主键字段去构建聚簇索引,因此后续的update语句可以命中索引,执行时自然获取的也是行级别的排他锁。

1.3、MySQL中死锁如何解决呢?

预防死锁、避免死锁、解除死锁等,而当死锁问题出现后该如何解决呢?一般只有两种方案:

  • 锁超时机制:事务/线程在等待锁时,超出一定时间后自动放弃等待并返回。

  • 外力介入打破僵局:第三者介入,将死锁情况中的某个事务/线程强制结束,让其他事务继续执行。

1.3.1、MySQL的锁超时机制

InnoDB中其实提供了锁的超时机制,也就是一个事务在长时间内无法获取到锁时,就会主动放弃等待,抛出相关的错误码及信息,然后返回给客户端。但这里的时间限制到底是多久呢?可以通过如下命令查询:

show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

默认的锁超时时间是50s,也就是在50s内未获取到锁的事务,会自动结束并返回。那也就意味着当死锁情况出现时,这个死锁过程最多持续50s,然后其中就会有一个事务主动退出竞争,释放持有的锁资源,这似乎听起来蛮不错呀,但实际业务中,仅依赖超时机制去解除死锁是不够的,毕竟高并发情况下,50s时间太长了,会导致越来越多的事务阻塞。

由于依靠锁超时机制,略微有些不靠谱,因此InnoDB也专门针对于死锁问题,研发了一种检测算法,名为wait-for graph算法。

1.3.2、死锁检测算法 - wait-for graph

这种算法是专门用于检测死锁问题的,在该算法中会对于目前库中所有活跃的事务生成等待图,在MySQL内部会生成一张这样的等待图:

图片

也就是T1持有着「竹子」这条数据的锁,正在等待获取「熊猫」这条数据的锁,而T2事务持有「熊猫」这条数据的锁,正在等待获取「竹子」这条数据的锁,最终T1、T2两个事务之间就出现了等待闭环,因此当MySQL发现了这种等待闭环时,就会强制介入,回滚结束其中一个事务,强制打破该闭环,从而解除死锁问题。

wait-for graph算法被启用后,会要求MySQL收集两个信息:

  • 锁的信息链表:目前持有每个锁的事务是谁。

  • 事务等待链表:阻塞的事务要等待的锁是谁。

每当一个事务需要阻塞等待某个锁时,就会触发一次wait-for graph算法,该算法会以当前事务作为起点,然后从「锁的信息链表」中找到对应中锁信息,再去根据锁的持有者(事务),在「事务等待链表」中进行查找,看看持有锁的事务是否在等待获取其他锁,如果是,则再去看看另一个持有锁的事务,是否在等待其他锁.....,经过一系列的判断后,再看看是否会出现闭环,出现的话则介入破坏。

库中有 T1、T2、T3三个事务、有 X1、X2、X3三个锁,事务与锁的关系如下:

图片

事务与锁

此时当T3事务需要阻塞等待获取X1锁时,就会触发一次wait-for graph算法,流程如下:

  • ①先根据T3要获取的X1锁,在「锁的信息链表」中找到X1锁的持有者T1

  • ②再在「事务等待链表」中查找,看看T1是否在等待获取其他锁,此时会得知T1等待X2

  • ③再去「锁的信息链表」中找到X2锁的持有者T2,再看看T2是否在阻塞等待获取其他锁。

  • ④再在「事务等待链表」中查找T2,发现T2正在等待获取X3锁,再找X3锁的持有者。

经过上述一系列算法过程后,最终会发现X3锁的持有者为T3,而本次算法又正是T3事务触发的,此时又回到了T3事务,也就代表着产生了“闭环”,因此也可以证明这里出现了死锁现象,所以MySQL会强制回滚其中的一个事务,来抵达解除死锁的目的。

但出现死锁问题时,MySQL会选择哪个事务回滚呢?之前分析过,当一个事务在执行SQL更改数据时,都会记录在Undo-log日志中,Undo量越小的事务,代表它对数据的更改越少,同时回滚的代价最低,因此会选择Undo量最小的事务回滚(如若两个事务的Undo量相同,会选择回滚触发死锁的事务)。

同时,可以通过innodb_deadlock_detect=on|off这个参数,来控制是否开启死锁检测机制。

死锁检测机制在MySQL后续的高版本中是默认开启的,但实际上死锁检测的开销不小,上面三个并发事务阻塞时,会对「事务等待链表、锁的信息链表」共计检索六次,那当阻塞的并发事务越来越多时,检测的效率也会呈线性增长。

1.3.3、如何避免死锁产生?

因为死锁的检测过程较为耗时,所以尽量不要等死锁出现后再去解除,而是尽量调整业务避免死锁的产生,一般来说可以从如下方面考虑:

  • 合理的设计索引结构,使业务SQL在执行时能通过索引定位到具体的几行数据,减小锁的粒度。

  • 业务允许的情况下,也可以将隔离级别调低,因为级别越低,锁的限制会越小。

  • 调整业务SQL的逻辑顺序,较大、耗时较长的事务尽量放在特定时间去执行(如凌晨对账...)。

  • 尽可能的拆分业务的粒度,一个业务组成的大事务,尽量拆成多个小事务,缩短一个事务持有锁的时间。

  • 如果没有强制性要求,就尽量不要手动在事务中获取排他锁,否则会造成一些不必要的锁出现,增大产生死锁的几率。

其实简单来说,也就是在业务允许的情况下,尽量缩短一个事务持有锁的时间、减小锁的粒度以及锁的数量。

同时也要记住:当MySQL运行过程中产生了死锁问题,那这个死锁问题以后绝对会再次出现,当死锁被MySQL自己解除后,一定要记住去排除业务SQL的执行逻辑,找到产生死锁的业务,然后调整业务SQL的执行顺序,这样才能从根源上避免死锁产生。

二、锁机制的底层实现原理

2.1、锁的内存结构

Java中,Synchronized锁是基于Monitor实现的,而ReetrantLock又是基于AQS实现的,那MySQL的锁是基于啥实现的呢?

图片

InnoDB引擎中,每个锁对象在内存中的结构如上,其中记录的信息也比较多,先全部理清楚后再聊聊锁的实现。

2.1.1、锁的事务信息

其中记录着当前的锁结构是由哪个事务生成的,记录的是指针,指向一个具体的事务。

2.1.2、索引的信息

这个是行锁的特有信息,对于行锁来说,需要记录一下加锁的行数据属于哪个索引、哪个节点,记录的也是指针。

2.1.3、锁粒度信息

这个略微有些复杂,对于不同粒度的锁,其中存储的信息也并不同,如果是表锁,其中就记录了一下是对哪张表加的锁,以及表的一些其他信息。

但如果锁粒度是行锁,其中记录的信息更多,有三个较为重要的:

  • Space ID:加锁的行数据,所在的表空间ID

  • Page Number:加锁的行数据,所在的页号。

  • n_bits:使用的比特位,对于一页数据中,加了多少个锁(后续结合讲)。

2.1.4、锁类型信息

对于锁结构的类型,在内部实现了复用,采用一个32bittype_mode来表示,这个32bit的值可以拆为lock_mode、lock_type、rec_lock_type三部分,如下:

图片

锁类型

  • lock_mode:表示锁的模式,使用低四位。

    • 0000/0:表示当前锁结构是共享意向锁,即IS锁。

    • 0001/1:表示当前锁结构是排他意向锁,即IX锁。

    • 0010/2:表示当前锁结构是共享锁,即S锁。

    •  0011/3:表示当前锁结构是排他锁,即X锁。

    • 0100/4:表示当前锁结构是自增锁,即AUTO-INC锁。

  • lock_type:表示锁的类型,使用低位中的5~8位。

    • LOCK_TABLE:当第5个比特位是1时,表示目前是表级锁。

    • LOCK_REC:当第6个比特位是1时,表示目前是行级锁。

  • rec_lock_type:表示行锁的具体类型,使用其余位。

    • LOCK_ORDINARY:当高23位全零时,表示目前是临键锁。

    • LOCK_GAP:当第10位是1时,表示目前是间隙锁。

    • LOCK_REC_NOT_GAP:当第11位是1时,表示目前是记录锁。

    • LOCK_INSERT_INTENTION:当第12位是1时,表示目前是插入意向锁。

    • 内部还有一些其他的锁类型,会使用其他位。

  • is_waiting:表示目前锁处于等待状态还是持有状态,使用低位中的第9位。

    • 0:表示is_waiting=false,即当前锁无需阻塞等待,是持有状态。

    • 1:表示is_waiting=true,即当前锁需要阻塞,是等待状态。

00000000000000000000000100100011

比如上面给出的这组bit,锁粒度、锁类型、锁状态是什么情况呢?如下:

图片

从上图中可得知,目前这组bit代表一个阻塞等待的行级排他临键锁结构。

2.1.5、其他信息

这个所谓的其他信息,也就是指一些用于辅助锁机制的信息,比如之前死锁检测机制中的「事务等待链表、锁的信息链表」,每一个事务和锁的持有、等待关系,都会在这里存储,将所有的事务、锁连接起来,就形成了上述的两个链表。

2.1.6、锁的比特位

与其说是锁的比特位,不如说是数据的比特位,好比举个例子:

SELECT * FROM `zz_student`;
+------------+--------+------+--------+
| student_id | name   | sex  | height |
+------------+--------+------+--------+
|          1 | 竹子   | 男   | 185cm  |
|          2 | 熊猫   | 女   | 170cm  |
|          3 | 子竹   | 男   | 182cm  |
|          4 | 棕熊   | 男   | 187cm  |
|          5 | 黑豹   | 男   | 177cm  |
|          6 | 脑斧   | 男   | 178cm  |
|          7 | 兔纸   | 女   | 165cm  |
+------------+--------+------+--------+

学生表中有七条数据,此时就会形成一个比特数组:000000000,行锁中,间隙锁可以锁定无穷小、无穷大这两个间隙,因此这组比特中,首位和末位即表示无穷小、无穷大两个间隙。

好比此时T1事务,对ID=2、3、6这三条数据加锁了,此时这个比特数组就会变为001100100,表示T1事务同时锁定了三条数据。而之前聊到的n_bits,它就会记录一下在这组比特中,多少条记录被上锁了。

2.2、InnoDB的锁实现

如果一个事务同时需要对表中的1000条数据加锁,会生成1000个锁结构吗?

如果这里是SQL Server数据库,那绝对会生成1000个锁结构,因为它的行锁是加在行记录上的,但MySQL锁机制并不相同,因为MySQL是基于事务实现的锁。来看看:

  • ①目前对表中不同行记录加锁的事务是同一个。

  • ②需要加锁的记录在同一个页面中。

  • ③目前事务加锁的类型都是相同的。

  • ④锁的等待状态也是相同的。

当上述四点条件被满足时,符合条件的行记录会被放入到同一个锁结构中,好比以上面的问题为例:

假设加锁的1000条数据分布在3个页面中,同时表中没有其他事务在操作,加的都是同一类型的锁。

此时依据上述的前提条件,那在内存中仅会生成三个锁结构,能够很大程度上减少锁结构的数量。总之情况再复杂,也不会像SQL Server般生成1000个锁对象,那样开销太大了!

2.3、MySQL获取锁的过程

当一个事务需要获取某个行锁时,首先会看一下内存中是否存在这条数据的锁结构,如果存在则生成一个锁结构,将其is_waiting对应的比特位改为1,表示目前事务在阻塞等待获取该锁,当其他事务释放锁后,会唤醒当前阻塞的事务,然后会将其is_waiting改为0,接着执行SQL

实际上会发现这个过程并不复杂,唯一有些难理解的点就在于:事务获取锁时,是如何在内存中,判断是否已经存在相同记录的锁结构呢?还记得锁结构中会记录的一个信息嘛?也就是「锁粒度信息」,如果是表锁,会记录表信息,如果是行锁,会记录表空间、页号等信息。在事务获取锁时,就是去看内存中,已存在的锁结构的这个信息,来判断是否存在其他事务获取了锁。

拿表锁来说,当事务要获取一张表的锁时,就会根据表名看一下其他锁结构,有没有获取当前这张表的锁,如果已经获取,看一下已经存在的表锁和目前要加的表锁,是否会存在冲突,冲突的话is_waiting=1,反之is_waiting=0,而行锁也是差不多的过程。

释放锁的过程也比较简单,这个工作一般是由MySQL自己完成的,当事务结束后会自动释放,释放的时候会去看一下,内存中是否有锁结构,正在等待获取目前释放的锁,如果有则唤醒对应的线程/事务。

其实看下来之后大家会发现,MySQL的锁机制实现,与常规的锁实现有些不一样,一般的锁机制都是基于持有标识+等待队列实现的,而MySQL则是略微有些不一样。

三、事务隔离机制的底层实现

对于并发事务造成的各类问题,在不同的隔离级别实际上,是通过不同粒度、类型的锁以及MVCC机制来解决的,也就是调整了并发事务的执行顺序,从而避免了这些问题产生,具体是如何做的呢?

  • RU/读未提交级别:要求该隔离级别下解决脏写问题。

  • RC/读已提交级别:要求该隔离级别下解决脏读问题。

  • RR/可重复读级别:要求该隔离级别下解决不可重复读问题。

  • Serializable/序列化级别:要求在该隔离级别下解决幻读问题。

虽然DBMS中要求在序列化级别再解决幻读问题,但在MySQL中,RR级别中就已经解决了幻读问题,因此MySQL中可以将RR级别视为最高级别,而Serializable级别几乎用不到,因为序列化级别中解决的问题,在RR级别中基本上已经解决了,再将MySQL调到Serializable级别反而会降低性能。

当然,RR级别下有些极端的情况,依旧会出现幻读问题,但线上100%不会出现。

3.1、RU(Read Uncommitted)读未提交级别的实现

对于RU级别而言,从它名字上就可以看出来,该隔离级别下,一个事务可以读到其他事务未提交的数据,但同时要求解决脏写(更新覆盖)问题,那思考一下该怎么满足这个需求呢?先来看看不加锁的情况:

SELECT * FROM `zz_users` WHERE user_id = 1;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
+---------+-----------+----------+----------+---------------------+-- ----------- 请按照标出的序号阅读代码!!! ---------------- ①开启一个事务T1
begin;-- ③修改 ID=1 的姓名为 竹子
UPDATE `zz_users` SET user_name = "竹子" WHERE user_id = 1;-- ⑥提交T1
commit;
-- ②开启另一个事务T2
begin;-- ④这里可以读取到T1中还未提交的 竹子 记录
SELECT * FROM `zz_users` WHERE user_id = 1;-- ⑤T2中再次修改姓名为 黑熊
UPDATE `zz_users` SET user_name = "黑熊" WHERE user_id = 1;-- ⑦提交T2
commit;

假设上述两个事务并发执行时,都不加锁,T2自然可以读取到T1修改后但还未提交的数据,但当T2再次修改ID=1的数据后,两个事务一起提交,此时就会出现T2覆盖T1的问题,这也就是脏写问题,而这个问题是不允许存在的。

写操作加排他锁,读操作不加锁!

还是上述的例子,当写操作加上排他锁后,T1在修改数据时,当T2再次尝试修改相同的数据,也要获取排他锁,因此T1、T2两个事务的写操作会相互排斥,T2就需要阻塞等待。但因为读操作不会加锁,因此当T2尝试读取这条数据时,自然可以读到数据。

因为写-写会排斥,但写-读不会排斥,因此也满足了RU级别的要求,即可以读到未提交的数据,但是不允许出现脏写问题。

MySQL-RU级别的实现原理,即写操作加排他锁,读操作不加锁。

3.2、RC(Read Committed)读已提交级别的实现

理解了RU级别的实现后,再来看看RCRC级别要求解决脏读问题,也就是一个事务中,不允许读另一个事务还未提交的数据。

写操作加排他锁,读操作加共享锁。

以之前的例子来说,因为T1在修改数据,所以会对ID=1的数据加上排他锁,此时T2想要获取共享锁读数据时,T1的排他锁就会排斥T2,因此T2需要等到T1事务结束后才能读数据。

因为T2需要等待T1结束后才能读,既然T1都结束了,那也就代表着T1事务要么回滚了,T2读上一个事务提交的数据;要么T1提交了,T2T1提交的数据,总之T2读到的数据绝对是提交过的数据。

这种方式的确能解决脏读问题,但似乎也会将所有并发事务串行化,会导致MySQL整体性能下降,因此MySQL引入了一种技术,也就是MVCC机制,在每次select查询数据时,都会生成一个ReadView快照,然后依据这个快照去选择一个可读的数据版本。

因此对于RC级别的底层实现,对于写操作会加排他锁,而读操作会使用MVCC机制。

但由于每次select时都会生成ReadView快照,此时就会出现下述问题:

-- ①T1事务中先读取一次 ID=1 的数据
SELECT * FROM `zz_users` WHERE user_id = 1;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
+---------+-----------+----------+----------+---------------------+-- ②T2事务中修改 ID=1 的姓名为 竹子 并提交
UPDATE `zz_users` SET user_name = "竹子" WHERE user_id = 1;
commit;-- ③T1事务中再读取一次 ID=1 的数据
SELECT * FROM `zz_users` WHERE user_id = 1;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 竹子      | 女       | 6666     | 2022-08-14 15:22:01 |
+---------+-----------+----------+----------+---------------------+

此时观察这个案例,明明是在一个事务中查询同一条数据,结果两次查询的结果并不一致,这也是所谓的不可重复读的问题。

3.3、RR(Repeatable Read)可重复读级别的实现

RC级别中,虽然解决了脏读问题,但依旧存在不可重复读问题,而RR级别中,就是要确保一个事务中的多次读取结果一致,即解决不可重复读问题。两种方案:

  • ①查询时,对目标数据加上临键锁,即读操作执行时,不允许其他事务改动数据。

  • MVCC机制的优化版:一个事务中只生成一次ReadView快照。

相较于第一种方案,第二种方案显然性能会更好,因为第一种方案不允许读-写、写-读事务共存,而第二种方案则支持读写事务并行执行。其实也比较简单:

写操作加排他锁,对读操作依旧采用MVCC机制,但RR级别中,一个事务中只有首次select会生成ReadView快照。

-- ①T1事务中先读取一次 ID=1 的数据
SELECT * FROM `zz_users` WHERE user_id = 1;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
+---------+-----------+----------+----------+---------------------+-- ②T2事务中修改 ID=1 的姓名为 竹子 并提交
UPDATE `zz_users` SET user_name = "竹子" WHERE user_id = 1;
commit;-- ③T1事务中再读取一次 ID=1 的数据
SELECT * FROM `zz_users` WHERE user_id = 1;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 竹子      | 女       | 6666     | 2022-08-14 15:22:01 |
+---------+-----------+----------+----------+---------------------+

还是以这个场景为例,在RC级别中,会对于T1事务的每次SELECT都生成快照,因此当T1第二次查询时,生成的快照中就能看到T2修改后提交的数据。但在RR级别中,只有首次SELECT会生成快照,当第二次SELECT操作出现时,依旧会基于第一次生成的快照查询,所以就能确保同一个事务中,每次看到的数据都是相同的。

也正是由于RR级别中,一个事务仅有首次select会生成快照,所以不仅仅解决了不可重复读问题,还解决了幻读问题,举个例子:

-- 先查询一次用户表,看看整张表的数据
SELECT * FROM `zz_users`;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
|       4 | 猫熊      | 女       | 8888     | 2022-09-27 17:22:59 |
|       9 | 黑竹      | 男       | 9999     | 2022-09-28 22:31:44 |
+---------+-----------+----------+----------+---------------------+-- ①T1事务中,先查询所有 ID>=4 的用户信息
SELECT * FROM `zz_users` WHERE user_id >= 4;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       4 | 猫熊      | 女       | 8888     | 2022-09-27 17:22:59 |
|       9 | 黑竹      | 男       | 9999     | 2022-09-28 22:31:44 |
+---------+-----------+----------+----------+---------------------+
-- ②T1事务中,再将所有 ID>=4 的用户密码重置为 1111
UPDATE `zz_users` SET password = "1111" WHERE user_id >= 4;-- ③T2事务中,插入一条 ID=6 的用户数据
INSERT INTO `zz_users` VALUES(6,"棕熊","男","7777","2022-10-02 16:21:33");
-- ④提交事务T2
commit;-- ⑤T1事务中,再次查询所有 ID>=4 的用户信息
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       4 | 猫熊      | 女       | 1111     | 2022-09-27 17:22:59 |
|       6 | 棕熊      | 男       | 7777     | 2022-10-02 16:21:33 |
|       9 | 黑竹      | 男       | 1111     | 2022-09-28 22:31:44 |
+---------+-----------+----------+----------+---------------------+

此时会发现,明明T1中已经将所有ID>=4的用户密码重置为1111了,结果改完再次查询会发现,表中依旧存在一条ID>=4的数据:棕熊,而且密码未被重置,这似乎产生了幻觉一样。

如果是RC级别,因为每次select都会生成快照,因此会出现这个幻读问题,但RR级别中因为只有首次查询会生成ReadView快照,因此上述案例放在RR级别的MySQL中,T1看不到T2新增的数据,因此MySQL-RR级别也解决了幻读问题。

MVCC机制是否彻底解决了幻读问题呢?

先上定论,MVCC并没有彻底解决幻读问题,在一种奇葩的情况下依旧会出现问题,先来看例子:

-- 开启一个事务T1
begin;
-- 查询表中 ID>10 的数据
SELECT * FROM `zz_users` where user_id > 10;
Empty set (0.01 sec)

因为用户表中不存在ID>10的数据,所以T1查询时没有结果,再继续往下看。

-- 再开启一个事务T2
begin;
-- 向表中插入一条 ID=11 的数据
INSERT INTO `zz_users` VALUES(11,"墨竹","男","2222","2022-10-07 23:24:36");
-- 提交事务T2
commit;

此时T2事务插入一条ID=11的数据并提交,此时再回到T1事务中:

-- 在T1事务中,再次查询表中 ID>10 的数据
SELECT * FROM `zz_users` where user_id > 10;
Empty set (0.01 sec)

结果很明显,依旧未查询到ID>10的数据,因为这里是通过第一次生成的快照文件在读,所以读不到T2新增的“幻影数据”。接着往下看:

-- 在T1事务中,对 ID=11 的数据进行修改
UPDATE `zz_users` SET `password` = "1111" where `user_id` = 11;-- 在T1事务中,再次查询表中 ID>10 的数据
SELECT * FROM `zz_users` where user_id > 10;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|      11 | 墨竹      | 男       | 1111     | 2022-10-07 23:24:36 |
+---------+-----------+----------+----------+---------------------+

此时会发现,T1事务中又能查询到ID=11的这条幻影记录了,这是啥原因导致的呢?因为我们在T1中修改了ID=11的数据,在MVCC机制原理中MVCC通过快照检索数据的过程,这里T1根据原本的快照文件检索数据时,因为发现ID=11这条数据上的隐藏列trx_id是自己,因此就能看到这条幻影数据了。

但如若你实在不放心,想要彻底杜绝任何风险的出现,那就直接将事务隔离级别调整到Serializable即可。

3.4、Serializable序列化级别的实现

前面已经将RU、RC、RR三个级别的实现原理弄懂了,最后再来看看最高的Serializable级别,在这个级别中,要求解决所有可能会因并发事务引发的问题。比较简单:

所有写操作加临键锁(具备互斥特性),所有读操作加共享锁。

由于所有写操作在执行时,都会获取临键锁,所以写-写、读-写、写-读这类并发场景都会互斥,而由于读操作加的是共享锁,因此在Serializable级别中,只有读-读场景可以并发执行。

四、事务与锁机制原理篇总结

  • RU级别:读操作不加锁,写操作加排他锁。

  • RC级别:读操作使用MVCC机制,每次SELECT生成快照,写操作加排他锁。

  • RR级别:读操作使用MVCC机制,首次SELECT生成快照,写操作加临键锁。

  • 序列化级别:读操作加共享锁,写操作加临键锁。

级别/场景读-读读-写/写-读写-写
RU级别并行执行并行执行串行执行
RC级别并行执行并行执行串行执行
RR级别并行执行并行执行串行执行
序列化级别并行执行串行执行串行执行

MySQL内存实现原理剖析 

一、MySQL内存结构

图片

注意观察,实际MySQL启动后内存结构略显复杂,但大体可分为MySQL工作组件、线程本地内存、MySQL共享内存、存储引擎缓冲区四大板块。

实际上MySQL内存模型和JVM类似,JVM内存主要会划分为线程共享区和线程私有区,而上图中的MySQL内存区域,左边则是线程私有区域,每条工作线程中都会分配的区域,各线程之间互不影响,而右边的三大板块,则属于线程共享区域,即所有线程都可访问的内存。

当然,线程共享区这块也会细分,右边上面的两个板块,都属于MySQL-Server层使用的内存,也就意味着这两块内存是所有引擎都共享的区域,而最下面这个区域,每个存储引擎都不相同,也就是InnoDB会构建自己的Buffer缓冲区,MyISAM也会构建自己的缓冲区。

1.1、MySQL Server - 工作组件

图片

因为后续客户端连接时,都需要经过一系列的连接工作,处理SQL时也需要经过一系列的解析、验证、优化工作,所以MySQL会在启动时,会先将这些工作组件初始化到内存中,方便后续处理客户端的操作。

数据库的连接池中,存的到底是什么?存的实际上就是数据库连接对象,MySQL内部的连接对象,其中包含了客户端连接信息,如客户端IP、登录的用户、所连接的DB....等这类信息,同时这些连接对象在内部会绑定一条工作线程,因此你也可以将它理解成是一个线程池。MySQL复用连接的本质,实则是在复用线程,出现一个新的客户端连接时,首先会根据客户端信息为其创建连接对象,然后再复用连接池中的空闲线程。

1.2、工作线程的本地内存

工作线程的本地内存区域,也被称之为线程私有区,即MySQL在创建每条线程时,都会为其分配这些内存:

图片

工作线程-本地内存,简单的说明一下其中每个区域的含义:

  • thread_stack:线程堆栈,主要用于暂时存储运行的SQL语句及运算数据,和Java虚拟机栈类似。

  • sort_buffer:排序缓冲区,执行排序SQL时,用于存放排序后数据的临时缓冲区。

  • join_buffer:连接缓冲区,做连表查询时,存放符合连表查询条件的数据临时缓冲区。

  • read_buffer:顺序读缓冲区,MySQL磁盘IO一次读一页数据,这个是顺序IO的数据临时缓冲区。

  • read_rnd_buffer:随机读缓冲区,当基于无序字段查询数据时,这里存放随机读到的数据。

  • net_buffer:网络连接缓冲区,这里主要是存放当前线程对应的客户端连接信息。

  • tmp_table:内存临时表,当SQL中用到了临时表时,这里存放临时表的结构及数据。

  • bulk_insert_bufferMyISAM批量插入缓冲区,批量insert时,存放临时数据的缓冲区。

  • bin_log_bufferbin-log日志缓冲区,bin-log的缓冲区被设计在工作线程的本地内存中。

可以看到,在工作线程的本地内存中,除开最基本的线程堆栈外,MySQL还往内部“塞了”一堆东西,这些东西在不同的SQL运行时,都有各自的作用,但基本上是为了更好的保存临时数据而设计的。

思考一个问题:对于上面列出的各类缓冲区,为什么要为每条线程都分配专属的内存,而不是直接在共享内存中搞一块大的空间,然后提供给所有线程来操作呢?其实答案很简单,因为这些数据本身就是一条线程在执行SQL时产生的临时数据,其他线程压根不会去用到另一条线程的临时数据,所以这些临时数据没有必要被共享。

除开上述原因外,将这些缓冲区都放在线程本地内存中,还有一点最大的好处:能够提升多线程并发执行的性能。如果把上述的各个缓冲区放在共享内存中,然后提供给线程存放执行时的临时数据,因为多线程的缘故,所以同一时刻、同一快内存有可能出现多条线程一起操作,那就会出现线程不安全的问题,想要解决就只能加锁将多线程串行化,这自然会在很大程度上影响性能,因此将这些存临时数据的缓冲区,设计在本地内存中才最合适。

不过也并非所有数据都适合放在线程的本地内存中,有一些多条线程之间都会访问的数据,如果再放到本地内存中,就会造成很大的冗余性,比如典型的索引根节点数据,每条线程都有可能会通过索引查询数据,因此每条线程都“缓存”一份放在自己的内存中,就会占用大量的内存空间,这样反而弊大于利。

1.3、MySQL共享内存区

图片

先简单介绍一下上述给出的几个内容:

  • Key BufferMyISAM表的索引缓冲区,提升MyISAM表的索引读写速度。

  • Query Cache:查询缓存区,缓冲SQL的查询结果,提升热点SQL的数据检索效率。

  • Thread Cache:线程缓存区,存放工作线程运行期间,一些需要被共享的临时数据。

  • Table Cache:表数据文件的文件描述符缓存,提升数据表的打开效率。

  • Table Definition Cache:表结构文件的文件描述符缓存,提升结构表的打开效率。

上面的这几个线程共享区域还比较容易理解,对于最后两个文件描述符缓存,大家可能存在些许疑惑,这里可以先看一下《FD-文件描述符的定义》,文件描述符本质上就是一个指针,指向一个具体的数据。这里为何要在内存中存放表数据文件、表结构文件的FD缓存呢?主要是为了提升性能,先来看一个问题:

比如我现在想要操作zz_users表的数据,那首先是不是得找到这张表?但表的位置可能分布在磁盘的任何一处,总不能触发磁盘IO把整个磁盘检索一遍,然后确定表的位置吧?所以内存中直接设计了一个缓存区,专门缓存这些表数据文件的磁盘位置,要对某张表进行操作时,直接去文件描述符缓存中找,然后根据其中记录的地址,去磁盘中固定的位置上操作表数据。

表结构的文件描述符缓存,作用也是相同的,比如现在要增加一个索引,或者修改一个字段,也不会把磁盘全部扫描一遍,而是直接根据内存中的文件描述符,去操作磁盘中对应位置的表结构文件。

但是这两个文件描述符缓存,更多的是为MyISAM引擎设计的。

1.3.1、MySQL8.x为什么移除了查询缓存?

再简单聊一下QueryCahce查询缓存,这块的设计思想是非常好的,也就是利用热点探测技术,对于一些频繁执行的查询SQL,直接将结果缓存在内存中,之后再次来查询相同数据时,就无需走磁盘,而是直接从查询缓存中获取数据并返回。

select * from zz_users where user_id=1;
select * from zz_users where user_id = 1;

比如上述这两条SQL语句,都是在查询ID=1的用户数据,MySQL的查询缓存会把它当做两条不同的SQL,也就是假如上面的第一条SQL,其查询结果被放入到了缓存中,第二条SQL依旧无法命中这个缓存,会继续走表查询的形式获取数据。

因为MySQL查询缓存是以SQL的哈希值来作为Key的,上面两条SQL虽然一样,但是后面的查询条件有细微差别:user_id=1、user_id = 1,也就是一条SQL有空格,一条没有。

由于这一点点细微差异,会导致两条SQL计算出的哈希值完全不同,因此无法命中缓存,是不是很鸡肋?还有多种情况:user_id =1、user_id= 1,空格处于的前后位置不同,也会导致缓存失效。

也正是由于方方面面的原因,所以查询缓存在MySQL8.0中被完全舍弃了,即移除掉了查询缓存区,各方面原因如下:

  • ①缓存命中率低:几乎大部分SQL都无法从查询缓存中获得数据。

  • ②占用内存高:将大量查询结果放入到内存中,会占用至少几百MB的内存。

  • ③增加查询步骤:查询表之前会先查一次缓存,查询后会将结果放入缓存,额外多几步开销。

  • ④缓存维护成本不小,需要LRU算法淘汰缓存,同时每次更新、插入、删除数据时,都要清空缓存中对应的数据。

  • InnoDB引擎构建出的缓冲区中,也会类似的功能,因为与查询缓存也存在冲突。

因为上述一系列原因,再加上项目中一般都会使用Redis先做业务缓存,因此能来到MySQL的查询语句,几乎都是要从表中读数据的,所以查询缓存的地位就显得更加突兀,因此在高版本中就直接去掉了,毕竟弊大于利,带来的收益达不到设计时的预期。

1.4、存储引擎缓冲区

几乎任何存储引擎都会在启动时,向操作系统申请一块内存,用来作为缓冲区,每个引擎的缓冲区也并不相同,但有一点是共通的:即所有引擎的缓冲区,对于MySQL的工作线程而言,都是一块共享的内存区域。

现如今,MySQL众多存储引擎中,应用最为广泛的是InnoDB,所以我们就以InnoDB-Buffer Pool为例,可将其称之为「写入缓冲」,但要记住:BufferPool不仅仅只扮演「写入缓冲」的角色,其中主要包含了如下内容:

图片

同样先简单介绍一下其中每个区域的作用:

  • Data Page:写入缓冲区,主要用来缓冲磁盘的表数据,将写操作转移到内存进行。

  • Index Page:索引缓冲页,对于所有已创建的索引根节点,都会放入到内存,提升索引效率。

  • Lock Space:锁空间,主要是存放所有创建出的锁对象。

  • Dict Info:数据字典,主要用来存储MySQL-InnoDB引擎自带的系统表。

  • redo_log_bufferredo-log缓冲区,存放写SQL执行时写入的redo记录。

  • undo_log_bufferundo-log缓冲区,存放写SQL执行时写入的undo记录。

  • Adaptivity Hash:自适应哈希索引,InnoDB会为热点索引页,创建相应的哈希索引。

  • Insert Buffer:写入缓冲区,对于insert的数据,会先放在这里,然后定期刷写磁盘。

  • Lru List:内存淘汰页列表,对于整个缓冲池的内存管理列表。

  • Free List:空闲内存列表,这里面记录着目前未被使用的内存页。

  • Flush List:脏页内存列表,这里主要记录未落盘的数据。

虽然MySQL是基于磁盘存储数据的,但总不能每次读写操作都走磁盘吧?这样绝对会导致资源开销极大,同时性能也极低,因此各引擎都在内存中设计了一个缓冲池,用来提升数据库整体的读写性能。

InnoDB引擎,是尤为特殊的存在,几乎将所有的操作都放在了内存中完成,因此慢慢的,InnoDB代替了MyISAM,成为了MySQL默认的存储引擎,不过还有其他方面的因素导致的。

二、InnoDB的核心 - Buffer Pool

InnoDB引擎几乎将所有操作都放在了内存中完成,这句话主要是跟它的Buffer Pool有关,但Buffer Pool到底会占用多大内存呢?这点可以通过show global variables like "%innodb_buffer_pool_size%";指令查询,如下:

show global variables like "%innodb_buffer_pool_size%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| innodb_buffer_pool_size | 44040192 |
+-------------------------+----------+
1 row in set (0.06 sec)

MySQL5.6版本以下,默认大小为42MB,而MySQL5.6以后的版本中,默认大小为128MB,这块内存是MySQL启动时向OS申请的一块连续空间。当然,我们也可以手动调整innodb_buffer_pool_size参数来控制,一般建议设置为机器内存的60~80%

接下来咱们先把Buffer Pool中每个区域的具体作用说明白,也就是这张图:

图片

2.1、数据页(Data Page)

InnoDB引擎为了方便读取,会将磁盘中的数据划分为一个个的「页」,每个页的默认大小为16KB,以页作为内存和磁盘交互的基本单位,而InnoDB的缓冲池也会以页作为单位,也就意味着:当InnoDB拿到申请的连续内存后,会按照16KB的尺寸将整块空间,划分成一个个的缓冲页。

MySQL运行之初,这些划分出的缓冲页,都属于空闲页,也就是未使用的内存,随着运行时长的慢慢增长,会将磁盘中的数据页,一点点的载入内存当中,因为磁盘中的表数据是以16KB作为单位划分的,而内存中的缓冲页也是这个大小,因此发生一次磁盘IO读到的数据(读一页磁盘数据),会放入到一个缓冲页中存储,而这些承载磁盘数据的缓冲页,就被称之为数据页,其过程如下:

图片

当磁盘中的数据被载入到内存之后,带来的优势会极为明显:

  • 读数据时:如果在数据页中有,则直接会从内存中读取数据并返回,没有再去磁盘检索数据。

  • 写数据时:会先修改数据页的数据,修改后会标记相应的数据页,然后直接返回,再由后台线程去完成数据的落盘工作。

此时有没有发现:InnoDB的缓冲池,其实也具备「查询缓存」的功能~

不过MySQL会将哪些表数据放到缓冲池中呢?其实刚启动时里面并不会有数据,而是随着业务SQL的执行,一点点将磁盘中的数据加载进内存的,比如执行一条查询语句,因为最初内存中并没有加载数据页,因此会走磁盘检索数据,检索数据的过程中,不管此次IO读到的数据是不是目标数据,都会将它们放在内存中,而不是直接回收。

观察上述这个过程,这样做有什么好处呢?方便后续其他SQL要操作对应数据时,可以直接在内存中读到数据。

在条件允许,即内存充足的情况下,InnoDB会试图将磁盘中的所有表数据全部载入内存。

不过一般的机器,磁盘空间都会比内存要大出很多倍,所以当表数据较大时,也不可能无限制的载入,因而InnoDB会有一套完善的内存管理与淘汰机制,以此防止内存溢出风险(对于这点后续再详细阐述)。

2.2、索引缓冲页(Index Page)

上面讲到了,InnoDB会将部分乃至所有表数据载入内存,以此达到提升性能的目的,但不可能无限制载入,比如现在机器的内存为16GB,但磁盘中有30GB表数据,这显然无法放入进内存,所以无可避免的一点:在运行过程中,MySQL会走磁盘读数据。

比如一条查询语句要读的数据,在内存中没有相关的缓冲数据页,因此需要触发磁盘IO检索数据,但此时这条SQL可以命中索引,那会通过索引去查找数据,但问题来了。索引的根节点可能位于磁盘的任意位置,难道把磁盘的所有位置全部走一遍吗?这显然并不现实,所以InnoDB也会有对应的优化机制,即内存中也会缓冲索引页。

MySQL启动时,就会将当前库中所有已存在的索引,其根节点放入到内存缓冲区中,因为索引的根节点只有16KB,因此就算目前库中就算创建了1000个索引,所有索引的根节点加起来占用的内存空间,也不过才15MB左右。将索引的根节点载入内存后,对于需要走索引查询的SQL,就会直接以相应的索引根节点为起始,然后去走索引查找数据,这样就避免了全盘查找索引根节点的这步操作。

Buffer Pool中有一块专门的区域:Index Page,专门用来存放载入的索引数据,存储这些数据的缓冲页,则被称之为索引页。随着运行时间的增长,也会将一些非根节点的索引页载入内存中,这是一种对于访问频率较高的索引页,专门推出的优化机制。

2.3、锁空间(Lock Space)

对于锁空间,锁机制的实现原理,聊到过锁是基于事务实现,每个事务会生成自己的锁结构,而这些锁结构也同样需要空间来存储,而锁空间就是专门用来存储锁结构的一块内存区域。

但锁空间也不仅仅只会存储锁结构,还会存储一些并发事务的链表,例如死锁检测时需要的「事务等待链表、锁的信息链表」等。

锁空间一般都是有大小限制的,当锁空间内存不足时,就会导致行锁粗化成表锁,以此来减少锁结构的数量,释放一定程度上的内存,但此时并发冲突就会变高。

2.4、数据字典(Dict Info)

对于数据字典估计大家很少有人接触过,毕竟这个是用来辅助InnoDB运行用的,咱们先思考一个问题,为啥我们可以通过SQL语句查询到库中的表信息、查询一张表的索引、约束等信息呢?如下:

-- 查询当前库中的所有表
show tables;
-- 查询一张表的全部索引
show index from `tableName`;

这些语句执行后都能查询出对应的信息,但这些信息咋来的呢?这首先跟MySQL的系统表有关,在InnoDB引擎中主要存在SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、SYS_FIELDS这四张系统表,主要是用来维护用户定义的所有表的各种信息,如下:

  • SYS_TABLES:这张表中会存储所有引擎为InnoDB的表信息。

    • ID:一张表的ID号。

    • NAME:一张表的名称。

    • N_COLS:一张表的字段数量。

    • TYPE:一张表所使用的存储引擎、编码格式、压缩算法、排序规则等。

    • SPACE:一张表所位于的表空间。

  • SYS_COLUMNS:这张表用来存储所有用户定义的表字段信息。

    • TABLE_ID:表示一个字段属于那张表。

    • POS:一个字段在一张表中属于第几列。

    • NAME:一个字段的名称。

    • MTYPE:一个字段的数据类型。

    • PRTYPE:一个字段的精度值。

    • LEN:一个字段的存储长度限制。

  • SYS_INDEXES:这张表用来存储所有InnoDB引擎表的索引信息。

    • TABLE_ID:表示这个索引属于哪张表。

    • ID:一个索引的ID号。

    • NAME:一个索引的名称。

    • N_FIELDS:一个索引由几个字段组成。

    • TYPE:一个索引的类型,如唯一、联合、全文、主键索引等。

    • SPACE:一个索引的数据所位于的表空间位置。

    • PAGE_NO:这个索引对应的B+Tree根节点位置。

  • SYS_FIELDS:这张表用来存储所有索引的定义信息。

    • INDEX_ID:当前这个索引字段属于哪个索引。

    • POS:当前这个索引字段,位于索引的第几列。

    • COL_NAME:当前索引字段的名称。

这四张表也被称为InnoDB的内部表,这四张表在载入内存前,位于.ibdata文件中,在MySQL启动时会开始加载,载入内存后就会放入到Dict Info这块区域,当利用show语句查询表的结构信息时,就会在字典信息中检索数据。

2.5、日志缓冲区(Log Buffer)

InnoDB的缓冲池中,主要存在两个日志缓冲区,即undo_log_buffer、redo_log_buffer,分别对应着撤销日志和重做日志,它俩的作用主要是用来提升日志记录的写入速度,因为日志文件在磁盘中,执行SQL时直接往磁盘写日志,其效率太低了,因此会先写缓冲区,再由后台线程去刷写日志。

2.6、自适应哈希索引(Adaptivity Hash)

自适应哈希索引又是一个比较有趣的技术点,这种技术可以算的上是一种AI技术,哈希算法查找数据的效率非常高,在没有哈希冲突的情况下复杂度为O(1),而B+Tree检索数据的效率,取决于树的高度。建立索引时,只能选用一种数据结构来作为索引的底层结构:

  • 如果选择哈希结构,虽然效率高,但数据是无序的,因此不方便做排序查询。

  • 如果选择B+Tree结构,虽然有序,但查询的效率会受到树高的影响。

两种结构各有优劣,但一般为了满足业务按序查询的需求,所以会折中选择B+Tree结构,虽然没有哈希索引那么快,但速度也还可以。

而正是由于此原因,InnoDB创始人在研发时,就实现了一种名为自适应哈希索引的技术,在MySQL运行过程中,InnoDB引擎会对表上的索引做监控,如果某些数据经常走索引查询,那InnoDB就会为其建立一个哈希索引,以此来提升数据检索的效率,并且减少走B+Tree带来的开销,由于这种哈希索引是运行过程中,InnoDB根据B+Tree的索引查询次数来建立的,因此被称之为自适应哈希索引。

自适应哈希索引和普通哈希索引的区别在哪儿呢?普通哈希索引是在创建索引时将结构声明为Hash结构,这种索引会以索引字段的整表数据建立哈希,而自适应哈希索引是根据缓冲池的B+树构造而来,只会基于热点数据构建,因此建立的速度会非常快,毕竟无需对整表都建立哈希索引。

自适应哈希索引在InnoDB中是默认开启的,可以通过手动调整innodb_adaptive_hash_index参数来控制关闭,但一般尽量不要去关闭它,因为该技术能让MySQL的整体性能翻倍。

MySQL8.0以下的版本中,如果同时删除一张大表的很多数据,有可能会因为自适应哈希索引的原因,造成线上MySQL出现抖动,不过该问题在MySQL8.x版本中已经被修复,但如若你的MySQL版本在此之下,那尽量不要在业务高峰期删除大量数据。

对于自适应哈希索引的使用情况,可以通过show engine innodb status \G;命令查看,但哈希索引由于自身特性的原因,因此也仅只能用于等值查询的场景,无法支持排序、范围查询。

2.7、写入缓冲区(Insert Buffer)

Change Buufer写入缓冲」属于InnoDB的一大特性,其实「写入缓冲」在一开始被称之为「Insert Buffer插入缓冲」,也就是只对insert操作生效,到了MySQL5.5之后的版本中,才正式改为「写入缓冲」,对于insert、delete、update语句都可生效。

结合前面聊过的「数据缓冲页」,如果要变更的数据页在缓冲区中存在,则会直接修改缓冲区中的数据页,然后标记一下变更过的数据页,但如果要操作的数据页并未被加载到缓冲区,那依旧会走磁盘去操作数据,走磁盘显然会影响性能,因此InnoDB就创造了一个「写入缓冲」。

insert语句为例,不管在MySQL的任何版本中,执行一条插入语句之前,因为这条数据在磁盘中都不存在,因此缓冲区中自然也不可能会有对应的数据页,按照前面的说法,似乎必须走磁盘插入数据了对不?

「写入缓冲」出现的原因,就是为了解决此问题,当一条写入语句执行时,流程如下:

  • ①判断要变更的数据页是否被载入到内存。

  • ②如果内存中有对应的数据页,则直接变更缓冲区中的数据页,完成标记后则直接返回。

  • ③如果内存中没有对应的数据页,则将要变更的数据放入到「写入缓冲」中,然后返回。

此时会发现,不管内存中是否存在相应的数据页,InnoDB都不会走磁盘写数据,而是直接在内存中完成所有操作,但是要注意:并不是所有的写入动作,都可以在内存中完成,「写入缓冲」是有限制的,如下:

  • 插入的数据字段不能具备唯一约束或唯一索引。

因为如果存在唯一字段的表,在插入数据前必须要先判断表中是否存在相同值,一张表的数据不可能全部都载入数据,所以这个判断重复值的工作必须依赖磁盘中的表数据来完成,所以插入具备唯一性的数据时,就必须要走磁盘。

表中会有一个主键,默认会存在一个主键索引,主键索引也是一种特殊的唯一索引,那不就意味着所有具备主键的表,都不能通过「写入缓冲」来插入数据呀?这点不一定,如果表的主键声明了是一个自增ID,那这个自增序列会由MySQL-Server自己来维护,因此ID会由MySQL来生成,是绝对不会出现重复值的,因此对于这种情况,会将要插入的数据放到「写入缓冲区」中。

那如果表中存在唯一索引、或者表的主键未声明是自增ID,难道插入数据时就不会用到这个「写入缓冲区」吗?答案是NO,依旧会用。一条插入语句的执行过程如下:

  • ①先向聚簇索引中,插入一条相应的行记录(数据)。

  • ②对于非聚簇索引,都插入一个新的索引键,并将值指向聚簇索引中插入的主键值。

发现没有?插入数据时还需额外维护表中的次级索引,会为插入的新数据构建次级索引的索引键,并且将索引键插入到次级索引树当中,而这个过程就会用到「写入缓冲区」。

因为首先需要走一次磁盘,先插入行记录,插入完成后,假设表中存在三个非聚簇索引(次级索引),那难道再写三次磁盘维护次级索引吗?NO,对于不具备唯一性的索引,都会将要插入的索引键放在「写入缓冲区」。

对于修改、删除语句的执行,也是同理,那「写入缓冲区」中的数据究竟啥时候会真正写入到磁盘呢?

  • 当一条SQL需要用到对应的索引键查询数据时,会触发后台线程执行刷盘工作。

  • 当「写入缓冲区」内存空间不足时,会触发后台线程执行刷盘工作。

  • 当距离上一次刷盘的时间,间隔达到一定程度(默认10s),会触发后台线程执行刷盘工作。

  • MySQL-Server正在关闭时,也会触发后台线程执行刷盘工作。

上述这四种情况,都会导致后台线程执行刷盘工作,从而将数据真正的落入磁盘中存储。

三、InnoDB缓冲池的内存是如何管理的?

InnoDB虽然在启动时,会将连续的内存划分为一块块的缓冲页,但这仅是逻辑上的划分,本质上所有的缓冲页之间,也是连续的内存。但随着MySQL在线上运行的时间越来越长,自然会导致这片连续的缓冲页变得七零八落,如下:

图片

当从磁盘加载一个数据页时,总不能将所有的缓冲页全部遍历一次,然后找到其中的空闲页放数据吧?这样难免有些影响性能,所以为了更好的管理缓冲池,InnoDB会为每个缓冲页创建一个控制块。

3.1、缓冲页的控制块

控制块是专门用于管理缓冲页而设计的一种结构,其中会包含:数据页所属的表空间、页号、缓冲页地址、链表节点指针等信息,所有的控制块都会放在缓冲池最前面,如下:

图片

当然,控制块也会占用缓冲池的内存空间,InnoDB会为每一个缓冲页都分配一个对应的控制块,后续InnoDB可以基于控制块去管理每一块缓冲页。

3.2、空闲页的管理

首先来聊聊对于空闲缓冲页的管理,为了能够更快的找到缓冲池中的空闲页,InnoDB会以控制块作为节点,将所有空闲的缓冲页组成一个空闲链表,也就是之前的Free链表,示意图如下:

图片

因为控制块对应着一个个的缓冲页,以控制块作为链表节点,也就等价于是由缓冲页组成的链表,在链表中会存在一个头结点,内部主要有三个值:

  • head:这是一根指针,指向空闲链表的第一个控制块。

  • tail:同样是一根指针,指向空闲链表的最后一个控制块。

  • count:这是一个数字,用来记录空闲链表的节点数量。

有了空闲链表后,会有什么好处呢?十分明显,当需要一块新的缓冲页存储磁盘数据时,不需要再去遍历所有缓冲页找一块空闲的出来了,而是直接找到空闲链表,根据空闲链表的指针,从中拿一块空闲缓冲页使用即可。

3.3、标记页的管理

当线程变更了内存中的数据页之后,会先对这个数据页做个标记,然后直接给客户端返回「执行成功」的响应。在这个过程中,被线程变更并标记过的数据页,则被称之为标记页,不过在有些地方也被称之为“脏页”。

对于内存中变更过的数据页,最终绝对是需要刷写到磁盘中的,这个工作会由MySQL的后台线程完成,但问题又来了:当后台线程要刷盘时,它咋知道哪些数据页是变更过的呢?

有人也许会说,前面工作线程在执行完SQL之后,不是对数据页做了标记嘛?确实没错,但问题在于:缓冲池中的缓冲页那么多,后台线程难道去把所有缓冲页全部找一次,看看它有没有被标记嘛?

这样做确实可以,但还不够,因为每次刷盘都需遍历所有缓冲页,其过程的开销必然不小,比如刷盘时,缓冲池中就只有几个数据页发生了变更,为了刷写这几个页的数据就找一次所有页,这有点用迫击炮打鸟的意思。

因此为了后台线程刷盘时效率更高,InnoDB同样又创造了一个Flush链表,它的结构和Free链表一模一样,因此就不再画图了,两者的不同点在于:

  • Free链表:记录空闲缓冲页,为了使用时能更快的找到空闲缓冲页。

  • Flush链表:记录标记过的缓冲页,为了刷盘时能够更快的找到变更数据页。

当后台线程开始刷盘工作时,会直接找到Flush链表,然后直接将该链表中对应的缓冲页,其变更过的数据刷写到磁盘。

注意:标记页的刷盘时机与「写入缓冲」的刷盘时机相同,也包括「写入缓冲」也归属在Buffer Pool中,因此当「写入缓冲区」中有数据需要刷盘时,相应的缓冲页,同样会被加入Flush链表。

3.4、内存中的数据页是如何淘汰的?

Buffer Pool的内存空间是有限的,因此无法支撑所有数据源源不断的载入内存,所以InnoDB内部绝对有一套自己的淘汰机制,但先设想一个问题:所有数据都可以被淘汰吗?可以是可以,毕竟就算内存中的数据没了,磁盘中也会有数据,但是随机淘汰显然并不妥,我们希望做到是:那些频繁被访问的数据页可以长期驻留在内存中,一些很少被访问的数据页能够淘汰掉

Free空闲链表、Flush刷写链表相同,对于要可淘汰的数据页,也会被组合成一个LRU淘汰链表,但淘汰链表会由哪些缓冲页组成呢?首先对于空闲页和标记页是不会纳入淘汰范围内的,为啥?

  • 空闲页:本身这些缓冲页都没有被使用,内存都是空白的,淘汰空闲页没有任何意义。

  • 标记页:被标记过的缓冲页中,由于存在数据还未落盘,所以淘汰掉之后代表数据会丢失。

因此LRU链表是由已使用、但未曾变更过的缓冲页组成的,不过要注意:有些数据页会在Flush、LRU两个链表之间“跳动”:

  • LRU链表中的一个数据页发生变更后,会从LRU链表转到Flush链表。

  • 当标记页中的变更数据落盘后,此时标记页又会从Flush链表回到LRU链表。

有些地方会存在些许误区,也就是标记页(脏页)也会被放入LRU链表中,这显然是不对的,为啥?因为所有的链表都是由控制块作为节点构建的,而一个控制块中只有一根指针,也意味着一个控制块同时只能加入一个链表中,所以就不可能出现一个缓冲页,既处于LRU链表,又位于Flush链表中。

淘汰是指将一个已使用的缓冲页,其中的所有数据清空,使其变为一个空闲页。

3.4.1、末尾淘汰机制
  • 当一条线程来读写数据时,命中了缓冲区中的某个数据页,那就直接将该页挪到LRU链表最前面。

  • 当未命中缓冲数据页时,需要走磁盘载入数据页,此时内存不够的情况下,会淘汰链表末尾的数据页。

从磁盘载入的数据会直接插入到LRU链表头部,也就是直接将其设置为链表的第一个节点。

这里也许有小伙伴会疑惑:似乎缓冲区中的数据页会一直频繁移动呐,这不会影响性能吗?答案是并不会,为啥呢?因为这里是链表结构而并非数组结构,将一个缓冲页移动到其他链表中,或将一个缓冲页移动到链表最前面,实际上只需要改一下指针即可,无需真正的触发数据挪动的工作。

假设此时LRU链表由8个缓冲页组成,并且此时缓冲区空间已满。此时假设一条查询语句,命中了其中的第6个数据页,此时这个数据页会被挪到最前面:

图片

此时又来了一条SQL,要操作的数据在缓冲区不存在,因此会从磁盘读取数据并载入内存,但因为目前缓冲区已经满了,所以需要淘汰一个缓冲页,用来存放载入的新数据,此时就会将末尾的数据页淘汰,如下:

图片

上面这个过程列出了最简单的末尾淘汰机制,但这种方式会存在两个较为致命的问题:

  • ①利用局部性原理预读失效时,会导致数据页常驻缓冲区。

  • ②查询数据量过大时,会导致缓冲区中的热点数据全部被替换,导致缓冲池被“污染”。

3.4.2、预读失效问题

一般来说,当程序读取某块数据时,这块区域附近的数据也很有可能被读取,因为程序在存储数据时,都会将一个数据保存在一块连续的空间中,因此MySQL在读取数据时,默认会使用局部性思想预读数据,也就是读取一个数据时,默认会将其附近的16KB数据一次性全部载入内存。

当数据载入内存后会分配一个缓冲页来存放,并且会将相应的数据页放在LRU链表的最前面,记住!这个数据页一共是有16KB数据的,也就意味着里面会有多行表数据,假设此时程序只读取了这页数据中的一行记录,对于其他数据并不需要读取,这也就是所谓的预读失效问题。

预读失效:即MySQL利用局部性原理预读载入的数据,在接下来时间内并未被使用。

如果按照前面列举的那种末尾淘汰机制去载入数据,一页数据被载入后会放到链表的头部,那想要淘汰这个数据页还需要等很长很长一段时间,毕竟MySQL实际会划分出几千几万个缓冲页,把这个没用的数据页放在了最前面,也就意味着该数据页会占用缓冲页很长时间。

为了解决这个问题,InnoDB并未采用最基本的末尾淘汰算法,而是对其做了些许优化,会将整个LRU算法划分为old、young两个区域组成。

young、old两个区域在LRU链表中的占比,默认为63:37,你也可以通过innodb_old_blocks_pc这个参数,来手动调整old区在整个LRU链表中的占比。

默认不改的情况下,假设LRU链表中由100缓冲页构成,那么前63个属于young区,后37个属于old区,示意图如下:

图片

LRU链表被划分为两个区域后,从磁盘中预读的数据页,就只需要加入到old区域的头部,当这个数据页被真正访问时,才会将其插入young区的头部。如果预读的这页在后续一直没有被访问,就会从old区域移除,从而不会影响young区域中的热点数据。

也就是说,在划分为两个区域后,young区域是用来存储真正的热点数据页,而old区则是用来存放有可能成为热点数据页的“候选人”,当需要淘汰缓冲页时,会优先淘汰old区中的数据页,毕竟young区中留下的都是久经考验的精英!

3.4.3、缓冲池污染问题

InnoDBLRU链表划分为两个区域后,改善了预读失效带来的问题,但还不够,因为还有可能会出现缓冲池污染的问题。

此时假设一条线程在执行SQL语句,目前是需要查询一张百万级别的所有表数据,由于Buffer Pool空间有限,所以如果按照原本的淘汰规则来清理内存,这次查询过程可能会导致Buffer Pool里面的所有热点数据全部被换出。等这次查询结束后,内存中只剩下了这次查询载入的数据页,当有线程访问原本哪些热点数据时,由于缓冲区中的数据页被换出了,因此就会产生大量的磁盘IO

上述这个过程,则被称之为Buffer Pool污染问题,但要注意:并不是需要查询大量结果才会导致这个问题出现,而是当扫描的数据过多时,都会引发此问题,比如典型的对大表执行了全表扫描,因为在扫描的过程,会不断从磁盘载入新的数据页放在内存中。

InnoDB为了解决该问题,又引入了一种新的技术,名为young区晋升限制,是不是有点耳熟?在JVM中,为了防止新生代过早晋升年老代,从而频繁触发FullGC的问题,在设计时也有晋升条件限制,默认情况下,一个对象只有达到了15岁之后,才能从新生代晋升年老代,毕竟能够熬过16轮新生代GC的对象,也绝对不会无缘无故突然挂掉。

InnoDB中的young区晋升限制,同样是这个原理,毕竟上面的全表扫描案例中,很多数据页只会被访问一次,但是由于需要访问它,所以才被载入了内存,最终导致old区放不下,从而导致了young区的热点数据被替换。

而加入了young区的晋升限制后,就能有效避免这种访问一次的数据页过早进入young区,InnoDB是怎么做的呢?其实很简单,就是加了一个停留时间的限制,如果一个数据页想从old区晋升到young区,必须要在old区中存活一定时间,这个时间默认为1s/1000ms,也可以通过参数innodb_old_blocks_time调整。

思考一下,由于存在这个时间限制,所以old区的数据页,想要进入young区,就必须达成两个条件:
①在old区中停留的时间超过了1000ms
②在old区中,一秒后有线程再次访问了这个数据页。

上面的第一条还比较容易理解,但第二条估计有些懵,啥意思啊?其实很简单,结合前面的淘汰算法:一个刚被载入的数据页,会先放到old区的头部,当该数据页被二次访问后才会挪到young区的头部。

通过这种晋升限制的方式,就能完美的解决全表扫描引起的缓冲池污染问题,这也是InnoDB最终的淘汰机制,当一个缓冲页的数据被淘汰后,也就是一个缓冲页的数据被清空后,会将其再次加入到Free空闲链表中等待分配。

四、MySQL内存总结

**InnoDB引擎几乎将所有的操作都放在了内存中进行**,比如写日志、写数据、查数据等,只有逼不得已的情况下,才会走磁盘读写数据。

假设你部署MySQL的机器内存足够大,并且为Buffer Pool分配的内存空间也足够大,比如机器的内存有128GB,此时为Buffer Pool分配了100GB,而整个库的所有表数据加起来仅有80GB,此时要记住,InnoDB几乎会将所有的表数据全部载入内存,后续所有的读写操作都会基于内存+后台线程刷盘的方式进行。

到这里大家会发现,虽然InnoDB是一款基于磁盘研发的存储引擎,但它几乎将内存的使用开发到了极致,能在内存完成的就压根不会走磁盘,在最大程度上提升MySQL的整体性能。

总结一下InnoDB内存管理这块的内容,InnoDB采用三个链表结构来管理所有的缓冲页:

  • Free链表:统一管理、分配所有未使用的缓冲页。

  • Flush链表:统一管理、刷写所有被标记过的缓冲页。

  • Lru链表:统一管理、淘汰所有已使用、未变更过的缓冲页。

在内存的淘汰机制方面,InnoDB基于末尾淘汰机制做了两点改善:

  • ①将Lru链表划分为了young、old两个分区,用来解决预读失效导致的内存占用问题。

  • ②引入了young区的晋升限制,解决了全表扫描时,young区的热点数据页被换出的问题。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/378357.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

嵌入式智能手表项目实现分享

简介 这是一个基于STM32F411CUE6和FreeRTOS和LVGL的低成本的超多功能的STM32智能手表~ 推荐 如果觉得这个手表的硬件难做,又想学习相关的东西,可以试下这个新出的开发板,功能和例程demo更多!FriPi炸鸡派STM32F411开发板: 【STM32开发板】 FryPi炸鸡派 - 嘉立创EDA开源硬件平…

STM32入门开发操作记录(二)——LED与蜂鸣器

目录 一、工程模板二、点亮主板1. 配置寄存器2. 调用库函数 三、LED1. 闪烁2. 流水灯 四、蜂鸣器 一、工程模板 参照第一篇,新建工程目录ProjectMould,将先前打包好的Start,Library和User文件^C^V过来,并在Keil5内完成器件支持包的…

【ARM】CCI集成指导整理

目录 1.CCI集成流程 2.CCI功能集成指导 2.1CCI结构框图解释 Request concentrator Transaction tracker Read-data Network Write-data Network B-response Network 2.2 接口注意项 记录一下CCI500的ACE slave interface不支持的功能: 对于ACE-Lite slav…

手机和电脑通过TCP传输(一)

一.工具 手机端:网络调试精灵 电脑端:野火网络调试助手 在开始通信之前,千万要查看一下电脑的防火墙是否关闭,否则可能会无法通信 在开始通信之前,千万要查看一下电脑的防火墙是否关闭,否则可能会无法通信…

vue3+TS从0到1手撸后台管理系统

1.路由配置 1.1路由组件的雏形 src\views\home\index.vue(以home组件为例) 1.2路由配置 1.2.1路由index文件 src\router\index.ts //通过vue-router插件实现模板路由配置 import { createRouter, createWebHashHistory } from vue-router import …

【学习笔记】无人机系统(UAS)的连接、识别和跟踪(一)-3GPP TS 23.256 技术规范概述

3GPP TS 23.256 技术规范,主要定义了3GPP系统对无人机(UAV)的连接性、身份识别、跟踪及A2X(Aircraft-to-Everything)服务的支持。 3GPP TS 23.256 技术规范: 以下是文档的核心内容总结: UAV系…

类和对象的简述(c++篇)

开局之前,先来个小插曲,放松一下: 让我们的熊二来消灭所有bug 各位,在这祝我们: 放松过后,开始步入正轨吧。爱学习的铁子们: 目录: 一类的定义: 1.简述: 2…

【Springboot】新增profile环境配置应用启动失败

RT 最近接手了一个新的项目,为了不污染别人的环境,我新增了一个自己的环境配置。结果,在启动的时候总是失败,就算是反复mvn clean install也是无效。 问题现象 卡住无法进行下一步 解决思路 由于之前都是能启动的&#xff0c…

Spring Boot项目的404是如何发生的

问题 在日常开发中,假如我们访问一个Sping容器中并不存在的路径,通常会返回404的报错,具体原因是什么呢? 结论 错误的访问会调用两次DispatcherServlet:第一次调用无法找到对应路径时,会给Response设置一个…

SpringBoot使用开发环境的application.properties

在Spring Boot项目中,application.properties 或 application.yml 文件是用于配置应用程序外部属性的重要文件。这些文件允许定制你的应用,而无需更改代码。根据不同的运行环境,可以通过创建以application-{profile}.properties格式命名的文件…

MMFewshot框架少样本目标检测配置学习(二)

教程 0:MMFEWSHOT 检测概述 在 MMFewShot 中,有三个用于获取数据的重要组件: Datasets:ann_cfg从少数镜头设置中加载注释并过滤图像和注释。 Dataset Wrappers:确定采样逻辑,例如根据查询图像采样支持图像…

<Rust>egui部件学习:如何在窗口及部件显示中文字符?

前言 本专栏是关于Rust的GUI库egui的部件讲解及应用实例分析,主要讲解egui的源代码、部件属性、如何应用。 环境配置 系统:windows 平台:visual studio code 语言:rust 库:egui、eframe 概述 本文是本专栏的第一篇博…

2024-07-16 Unity插件 Odin Inspector5 —— Conditional Attributes

文章目录 1 说明2 条件特性2.1 DisableIf / EnableIf2.2 DisableIn / EnableIn / ShowIn / HideIn2.3 DisableInEditorMode / HideInEditorMode2.4 DisableInInlineEditors / ShowInInlineEditors2.5 DisableInPlayMode / HideInPlayMode2.6 ShowIf / HideIf2.7 ShowIfGroup / …

鸿蒙架构之AOP

零、主要内容 AOP 简介ArkTs AOP 实现原理 JS 原型链AOP实现原理 AOP的应用场景 统计类: 方法调用次数统计、方法时长统计防御式编程:参数校验代理模式实现 AOP的注意事项 一、AOP简介 对于Android、Java Web 开发者来说, AOP编程思想并不…

java智慧工地云平台源码,基于BIM+AI智能大数据中心和云平台的智慧工地监管平台

智慧工地云平台源码,智慧工地系统源码,工程管理系统APP源码, “智慧工地”基于BIM(建筑信息模型)AI(人工智能)智能大数据中心和云平台,围绕建筑工程项目全生命周期,集成安…

Linux下如何安装配置Graylog日志管理工具

Graylog是一个开源的日志管理工具,可以帮助我们收集、存储和分析大量的日志数据。它提供了强大的搜索、过滤和可视化功能,可以帮助我们轻松地监控系统和应用程序的运行情况。 在Linux系统下安装和配置Graylog主要包括以下几个步骤: 准备安装…

Scratch编程乐园:108课打造小小编程大师

《Scratch少儿趣味编程108例(全视频微课版)》以Scratch 3.6版本为基础,通过108个案例详细介绍了运用Scratch软件制作动画、游戏等趣味作品的方法,充分培养孩子的想象力和创造力。本书共分为9章,第1章概述Scratch下载、…

ArrayList.subList的踩坑

需求描述&#xff1a;跳过list中的第一个元素&#xff0c;获取list中的其他元素 原始代码如下&#xff1a; List<FddxxEnterpriseVerify> companyList fddxxEnterpriseVerifyMapper.selectList(companyQueryWrapper);log.info("获取多个法大大公司数据量为&#…

OMS 2.0至3.0升级项目成功案例:红袖女装

近日&#xff0c;巨益科技成功助力女装品牌红袖完成OMS 3.0升级&#xff0c;并顺利通过项目验收。此次升级通过优化系统架构、提高数据处理能力和实现多系统集成&#xff0c;红袖品牌显著提升了订单处理速度、库存管理精度和客户满意度&#xff0c;实现了运营效率和服务质量的全…

基于Python+Flask+SQLite的豆瓣电影可视化系统

FlaskMySQLEcharts 基于PythonFlaskSQLite的豆瓣电影可视化系统 Echarts 不支持登录注册&#xff0c;并且信息存储在数据库中 不含爬虫代码&#xff0c;或爬虫代码已失效 简介 基于PythonFlaskMySQL的豆瓣电影可视化系统&#xff0c;采用Echart构建图表&#xff0c;支持自定…