写点什么

MySQL 千万数据量深分页优化

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

    阅读完需:约 9 分钟

Transaction 100

BEGIN;


UPDATE t SET name


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


= '小 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;


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 的记录的版本链就长这样:



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

使用 REPEATABLE READ 隔离级别的事务

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 的值仍为'小 A'


这个 SELECT2 的执行过程如下:


  • 因为之前已经生成过 ReadView 了,所以此时直接复用之前的 ReadView,之前的 ReadView 中的 m_ids 列表就是[100, 200]。

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

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

评论

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