写点什么

MySQL 千万数据量深分页优化 (1)

  • 2021 年 11 月 11 日
  • 本文字数:4335 字

    阅读完需:约 14 分钟

脏读:当一个事务读取到其他事务还未提交的数据,因为未提交的数据,不一定是最终有效的数据。所以我们称为读到脏数据了。也就是脏读。 不可重复读:一个事务 A 读取数据之后,另外一个事务 B 将此数据修改,此时事务 A 再次查询,发现数据不一样了。这就是不可重复读。也可以叫做幻读。 幻读:又叫"幻象读",是''不可重复读''的一种特殊场景:当事务 1 两次执行''SELECT ... WHERE''检索一定范围内数据的操作中间,事务 2 在这个表中创建了(如[[INSERT]])了一行新数据,这条新数据正好满足事务 1 的“WHERE”子句。 注:可能有点绕,一般情况下,“不可重复读”和“幻读”大致的意思相同。只不过不可重复度是在数据行上发生的,也就是发生了 update 操作,再去读取这条数据,出现不可重复读。而幻读是在数据表上发生的,也就是发生了 insert 与 delete 操作。再去读取这张表,出现数据条目或者行数(记录数)不一样。出现了幻觉一样。 **


4、MVCC(Multiversion Concurrency Control)多版本并发控制




数据库用于处理读写冲突的一种手段,目的在于提交数据库高并发场景下的吞吐性能。 版本链: 对于使用 InnoDB 存储引擎的表来说,它的聚簇索引记录中都包含两个必要的隐藏列(row_id 并不是必要的,我们 创建的表中有主键或者非 NULL 唯一键时都不会包含 row_id 列): trx_id:每次对某条记录进行改动时,都会把对应的事务 id 赋值给 trx_id 隐藏列。 roll_pointer:每次对某条记录进行改动时,这个隐藏列会存一个指针,可以通过这个指针找到该记 录修改前的信息。 比如说现在有这样一张表:t


| ID | Name |


| --- | --- |


| 1 | 小李 |


我们先假设新增这条记录的事务 ID 为 80,那么此时此刻这条记录的版本链表如下图(因为是新增,所以这条版本链对应的 roll_pointer 是空):


![image.png](https://img-blog.csdnimg.cn/img_convert/dfeb259941f032b7e9f37f3dcc03dd85.webp?x-oss-process=image/form


【一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
复制代码


at,png)


假如现在有两个事务 ID 分别为 100、200,对这条记录进行 update 操作,具体走向流程如下:



贴心小课堂:


两个事务中不能交叉更新同一条记录哦?第一个事务更新了某条记录后,就会给这条记录加锁,另一个事务再次更新时就需要等待第一个事务提交了,把锁释放之后才可以继续更新。


我们每一次对数据记录的改动,MySQL 都会记录一条日志,我们把它称作 undo 日志,每一条 undo 日志对应着也都有一个 roll_pointer 属性(insert 操作对应的 undo 日志没有该属性,因为该记录并没有更早的版本),可以将这些 undo 日志都连起来,串成一个链表,所以现在的情况就像下图一样:



对这条记录每次更新后,都会将旧记录放入到 undo 日志中,就算是该记录的一个历史版本,随着更新次数的一次次增加,所有的版本都会被 roll_pointer 属性连接成一个链表,我们把这个链表称之为【版本链】,版本链的头节点就是当前记录最新的值。另外,每个版本中还包含生成该版本时对应的事务 id,这个 ID(事务 ID)非常重要,后续事务的隔离级别实现原理都是围绕这个 ID(事务 ID)来的。

ReadView

** 对于使用【读未提交 READ_UNCOMMITTED】这种隔离级别的事务来说,直接读取记录的最新版本就好了,对于使用【串行化 SERIALIZABLE】隔离级别的事务来说,使用加锁的方式来访问记录。对于使用【读已提交 READ COMMITTED】和【可重复读 REPRATABLE_READ】隔离级别的事务来说,就需要用到我们上边所说的【版本链】了,核心的问题就是:我们需要判断版本链中的数据,哪个版本是当前事务可见的。所以设计 MySQL 官方提出了一个 ReadView 的概念,这个 ReadView 中主要包含当前 MySQL 中还有哪些活跃的读写事务,把它们的事务 id 放到一个列表中,我们把这个列表命名为为 m_ids(一个数组)。这样在我们访问某一条记录时,只需要按照下边的步骤判断记录的某个版本是否可见(官方设计规则哦):


  • 如果被访问版本的 trx_id 属性值小于 m_ids 列表中最小的事务 id,表明生成该版本的事务在生成 ReadView 前已经提交,所以该版本可以被当前事务访问。

  • 如果被访问版本的 trx_id 属性值大于 m_ids 列表中最大的事务 id,表明生成该版本的事务在生成 ReadView 后才生成,所以该版本不可以被当前事务访问。

  • 如果被访问版本的 trx_id 属性值在 m_ids 列表中最大的事务 id 和最小事务 id 之间,那就需要判断一下 trx_id 属性值是不是在 m_ids 列表中,如果在,说明创建 ReadView 时生成该版本的事务还是活跃的,该版本不可以被访问;如果不在,说明创建 ReadView 时生成该版本的事务已经被提交,该版本可以被访问。


如果某个版本的数据对当前事务不可见的话,那就顺着版本链继续去找下一个版本的数据记录,依然按照我们上边所说的步骤判断数据是否可见,依此类推,一直到版本链中的最后一个版本数据,如果最后一个版本的数据我也不可见的话,那么也就意味着该条记录对该事务不可见,查询结果就不包含该记录。 在 MySQL 当中,READ COMMITTED(读已提交)和 REPEATABLE READ(可重复读)隔离级别的的一个非常大的区别就是它们生成 ReadView 的时机不同,我们来具体举例看一下喽。 按照上面我们画的版本链,来具体分析一下,这个版本链是怎么一步步生成的,以及我们查询的时候,MySQL 是怎么来通过版本链决定数据我们是否可读(可见)的。 --[1]--【R****EAD COMMITTED --- 每次读取数据前都生成一个 ReadView】 假设说现在系统里有一个 id 为 100 的事务在执行:

Transaction 100

BEGIN;


UPDATE t SET name = '小 B' WHERE id = 1;


UPDATE t SET name = '小 C' WHERE id = 1;

注意哦:我们这个事务,我并没有提交。没有 commit 指令哦

复制代码

Transaction 200

BEGIN;

更新了一些别的表的记录

...


贴心小课堂:事务执行过程中,只有在第一次真正修改记录时(比如使用 INSERT、DELETE、UPDATE 语句),才会被分配一个单独的事务 id,这个事务 id 是递增的。


此刻,表 t 中 id 为 1 的记录得到的版本链表如下所示:



千万注意,我上面事务 100,还没提交哦,我可没有执行 commit 指令。 假设现在有一个使用 READ COMMITTED(读已提交)隔离级别的事务开始执行:

使用 READ COMMITTED 隔离级别的事务(读已提交)

BEGIN;

SELECT1:Transaction 100、200 未提交

SELECT * FROM t WHERE id = 1; # 得到的列 name 的值为'小 A'


这个 SELECT1 的执行流程如下:


  • 在执行 SELECT 语句时会首先生成一个 ReadView,ReadView 的 m_ids 数组列表的内容就是[100,200]。

  • 然后从版本链中挑选可见的记录,从图中可以看出,最新版本的列 name 的内容是'小 C',该版本的 trx_id 值为 100,在 m_ids 列表内,所以不符合我们的可见性要求,根据 roll_pointer 跳到下一个版本。

  • 下一个版本的列 name 的内容是'小 B',该版本的 trx_id 值也为 100,也在 m_ids 列表内,所以也不符合要求,继续跳到下一个版本。

  • 下一个版本的列 name 的内容是'小 A',该版本的 trx_id 值为 80,小于 m_ids 列表中最小的事务 id100,所以这个版本是符合要求的,最后返回给用户的版本就是这条列 name 为'小 A'的记录。


之后,我们把事务 id 为 100 的这个事务提交一下,如下:

Transaction 100

BEGIN;


UPDATE t SET name = '小 B' WHERE id = 1;


UPDATE t SET name = '小 C' WHERE id = 1;


COMMIT; //提交了哦


然后再到事务 id 为 200 的事务中更新一下表 t 中 id 为 1 的记录:

Transaction 200

BEGIN;

更新了一些别的表的记录

...


UPDATE t SET name = '小 D' WHERE id = 1;


UPDATE t SET name = '小 F' WHERE id = 1;


此刻,表 t 中 id 为 1 的记录的版本链就长这样:



然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 id 为 1 的记录,如下:

使用 READ COMMITTED 隔离级别的事务

BEGIN;

SELECT1:Transaction 100、200 均未提交的时候执行的查询

SELECT * FROM t WHERE id = 1; # 得到的列 name 的值为'小 A'

SELECT2:Transaction 100 提交,Transaction 200 未提交的时候执行的查询

SELECT * FROM t WHERE id = 1; # 得到的列 name 的值为'小 C'


这个 SELECT2 的执行过程如下:


  • 在执行 SELECT 语句时会先生成一个 ReadView,ReadView 的 m_ids 列表的内容就是[200](事务 id 为 100 的那个事务已经提交了,所以生成快照时就没有它了)。

  • 然后从版本链中挑选可见的记录,从图中可以看出,最新版本的列 name 的内容是'小 F',该版本的 trx_id 值为 200,在 m_ids 列表内,所以不符合可见性要求,根据 roll_pointer 跳到下一个版本。

  • 下一个版本的列 name 的内容是'小 D',该版本的 trx_id 值为 200,也在 m_ids 列表内,所以也不符合要求,继续跳到下一个版本。

  • 下一个版本的列 name 的内容是'小 C',该版本的 trx_id 值为 100,比 m_ids 列表中最小的事务 id200 还要小,所以这个版本是符合要求的,最后返回给用户的版本就是这条列 name 为'小 C'的记录。


以此类推,如果之后事务 id 为 200 的记录也提交了,再此在使用 READ COMMITTED 隔离级别的事务中查询表 t 中 id 值为 1 的记录时,得到的结果就是'小 F'了,具体流程我们就不分析了。总结一下就是:使用 READ COMMITTED 隔离级别的事务在每次查询开始时都会生成一个独立的 ReadView。 说完了隔离级别为【读已提交】不知道你理解了没有?如果不理解,烦请联系我,我们一起进行探讨。 接下来我们就来看一下当事务隔离级别为【可重复读】的时候,MVCC 是如何控制数据可见性的。 --[2]--****【REPEATABLE READ ---在第一次读取数据时生成一个 ReadView】 对于使用 REPEATABLE READ 隔离级别的事务来说,只会在第一次执行查询语句时生成一个 ReadView,之后的查询就不会重复生成了。我们还是用例子看一下是什么效果。 比方说现在系统里有两个 id 分别为 100、200 的事务在执行:

Transaction 100

BEGIN;


UPDATE t SET name = '小 B' WHERE id = 1;


UPDATE t SET name = '小 C' WHERE id = 1;

Transaction 200

BEGIN;

更新了一些别的表的记录

...


此刻,表 t 中 id 为 1 的记录得到的版本链表如下所示:



假设现在有一个使用 REPEATABLE READ 隔离级别的事务开始执行:

使用 REPEATABLE READ 隔离级别的事务

BEGIN;

SELECT1:Transaction 100、200 未提交

SELECT * FROM t WHERE id = 1; # 得到的列 name 的值为'小 A'


这个 SELECT1 的执行过程如下:


  • 在执行 SELECT 语句时会先生成一个 ReadView,ReadView 的 m_ids 列表的内容就是[100, 200]。

  • 然后从版本链中挑选可见的记录,从图中可以看出,最新版本的列 name 的内容是'小 C',该版本的 trx_id 值为 100,在 m_ids 列表内,所以不符合可见性要求,根据 roll_pointer 跳到下一个版本。

  • 下一个版本的列 name 的内容是'小 B',该版本的 trx_id 值也为 100,也在 m_ids 列表内,所以也不符合要求,继续跳到下一个版本。

  • 下一个版本的列 name 的内容是'小 A',该版本的 trx_id 值为 80,小于 m_ids 列表中最小的事务 id100,所以这个版本是符合要求的,最后返回给用户的版本就是这条列 name 为'小 A'的记录。


之后,我们把事务 id 为 100 的事务提交一下,就像这样:

Transaction 100

BEGIN;


UPDATE t SET name = '小 B' WHERE id = 1;

评论

发布
暂无评论
MySQL 千万数据量深分页优化(1)