代码改变世界

MySQL InnoDB下关于MVCC的一个问题的分析

2018-05-29 22:33 潇湘隐者 阅读(...) 评论(...) 编辑 收藏

 

这个是网友++C++在群里问的一个关于MySQL的问题,本篇文章实验测试环境为MySQL 5.6.20,事务隔离级别为REPEATABLE-READ ,在演示问题前,我们先准备测试环境。准备一个测试表test以及一个存储过程循环往test表里面插入记录。

 

CREATE TABLE test
(
  `id` int(11) primary key not null,
  `name` char(255) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
 
delimiter &&
drop procedure if exists prc_insert;
 
create procedure prc_insert(in  cnt int)
begin
declare i int;
set i=1;
while i < cnt do
    insert into test(id, name) select i,  CONCAT('name',i) from dual;
    
    set i = i+1;
 
end while;
end &&
 
delimiter ;

 

在线程ID为14的会话中,开启事务,然后执行查询test的SQL语句

 

mysql> select connection_id() from dual;
+-----------------+
| connection_id() |
+-----------------+
|              14 |
+-----------------+
1 row in set (0.00 sec)
 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from test;

 

 

然后在线程ID为12的会话中,循环往表test里面插入1000000记录

 

mysql> select connection_id() from dual;
+-----------------+
| connection_id() |
+-----------------+
|              12 |
+-----------------+
1 row in set (0.00 sec)
 
mysql> call prc_insert(1000000);
Query OK, 1 row affected (8 min 32.11 sec)

 

 

在执行循环插入的这段时间里(SQL执行需要几分钟时间),我们在线程ID 为14的会话中反复执行select * from test这个SQL语句,你会发现该SQL的执行时间变长。那么引起SQL语句执行时间变长的原因是什么呢? 如何解释得通呢?

 

 

clip_image001

 

刚开始讨论的时候,以为MySQL会像ORACLE那样会在UNDO的回滚段中产生大量UNDO记录,最后导致SQL语句会像ORACLE那样产生额外的一致性读,产生额外的IO,从而导致执行时间变长。 后面测试发现,其实对于MySQL而言,INSERT操作在事务提交前只对当前事务可见,因此产生的Undo日志可以在事务提交后直接删除,而此处使用是自动提交模式。用MySQL技术内幕:InnoDB存储引擎里面提供的脚本py_innodb_page_info.py测试验证。也是确实如此(UNDO日志的大小变化很小,时而增长,时而变小)。其实MySQL里面多版本并发控制(MVCC)的实现机制跟Oracle还是不同的。不能生搬硬套Oracle下的那套理论。

 

 

[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1
Total number of page: 4864:
Insert Buffer Free List: 32
Insert Buffer Bitmap: 1
System Page: 130
Transaction system Page: 1
Freshly Allocated Page: 1326
Undo Log Page: 3224
File Segment inode: 6
B-tree Node: 142
File Space Header: 2
[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1
Total number of page: 4864:
Insert Buffer Free List: 32
Insert Buffer Bitmap: 1
System Page: 130
Transaction system Page: 1
Freshly Allocated Page: 1326
Undo Log Page: 3223
File Segment inode: 6
B-tree Node: 143
File Space Header: 2
[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1
Total number of page: 4864:
Insert Buffer Free List: 32
Insert Buffer Bitmap: 1
System Page: 130
Transaction system Page: 1
Freshly Allocated Page: 1326
Undo Log Page: 3213
File Segment inode: 5
B-tree Node: 154
File Space Header: 2
[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1
Total number of page: 4864:
Insert Buffer Free List: 32
Insert Buffer Bitmap: 1
System Page: 130
Transaction system Page: 1
Freshly Allocated Page: 1326
Undo Log Page: 3205
File Segment inode: 5
B-tree Node: 162
File Space Header: 2
[root@DB-Server kerry]# python py_innodb_page_info.py /data/mysql/ibdata1
Total number of page: 4864:
Insert Buffer Free List: 32
Insert Buffer Bitmap: 1
System Page: 130
Transaction system Page: 2
Freshly Allocated Page: 1326
Undo Log Page: 3240
File Segment inode: 5
B-tree Node: 127
File Space Header: 1

 

其实InnoDB的多版本并发控制(MVCC),高性能MySQL这本书中有这么一段描述:

 

 

InnoDB的MVCC,是通过每行记录后面保存的两个隐藏的列来实现的。 这两个列一个保存了行的创建时间,一个保存行的过期时间(或删除时间),当然存储的并不是实际的时间值,而是系统版本号(System version number),每开始一个新的事务,系统版本号都会自动递增,事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。下面看一下在REPEATABLE READ隔离级别下, MVCC是如何具体操作的。

 

SELECT

 

InnoDB会根据以下两个条件检查每行的记录:

 

    a.  InnoDB只查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或修改过的。

    b.  行的删除要么未定义,要么大于当前事务版本号。这可以确保事务读取到的行,在事务开始之前未被删除。

 

只有符合上述两个条件的记录,才能返回作为查询结果。

..............................................

 

其实至少从MySQL 5.5之后, 每一行开始额外包含三个隐藏字段,而不是二个字段(没有查证高性能MySQL主要讲述哪个MySQL版本)。

 

 

·         6字节的事务ID(DB_TRX_ID)字段: 用来标识最近一次对本行记录做修改(insert|update)的事务的标识符, 即最后一次修改(insert|update)本行记录的事务ID。

    至于delete操作,在InnoDB看来也不过是一次update操作,更新行中的一个特殊位将行表示为deleted, 并非真正删除。

 

·         7字节的回滚指针(DB_ROLL_PTR)字段: 指写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。

    如果一行记录被更新, 则 undo log record 包含 '重建该行记录被更新之前内容' 所必须的信息。

 

·         6字节的DB_ROW_ID字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时,聚集索引会包括这个行ID的值,否则这个行ID不会出现在任何索引中。

 

 

InnoDB表数据的组织方式为主键聚簇索引。由于采用索引组织表结构,记录的ROWID是可变的(索引页分裂的时候,Structure Modification Operation,SMO),因此二级索引中采用的是(索引键值, 主键键值)的组合来唯一确定一条记录。

无论是聚簇索引,还是二级索引,其每条记录都包含了一个DELETED BIT位,用于标识该记录是否是删除记录。除此之外,聚簇索引记录还有两个系统列:DATA_TRX_ID,DATA_ROLL_PTR。DATA _TRX_ID表示产生当前记录项的事务ID;DATA _ROLL_PTR指向当前记录项的undo信息

 

 

在InnoDB中(默认Repeatable Read级别), 事务在begin/start transaction之后的第一条select读操作后, 会创建一个Read View, 将当前系统中活跃的其他事务记录记录起来; 关于Read View这个概念,参考何登成的博客InnoDB多版本(MVCC)实现简要分析,下面摘抄部分内容如下:

 

InnoDB默认的隔离级别为Repeatable Read (RR),可重复读。InnoDB在开始一个RR读之前,会创建一个Read View。Read View用于判断一条记录的可见性。Read View定义在read0read.h文件中,其中最主要的与可见性相关的属性如下:

 

dulint    low_limit_id;    /* 事务号 >= low_limit_id的记录,对于当前Read View都是不可见的 */
dulint    up_limit_id;     /* 事务号 < up_limit_id ,对于当前Read View都是可见的 */
ulint     n_trx_ids;       /* Number of cells in the trx_ids array */
dulint*    trx_ids;        /* Additional trx ids which the read should
                    not see: typically, these are the active
                    transactions at the time when the read is
                    serialized, except the reading transaction
                    itself; the trx ids in this array are in a
                    descending order */
dulint    creator_trx_id;    /* trx id of creating transaction, or
                    (0, 0) used in purge */

 

简单来说,Read View记录读开始时,所有的活动事务,这些事务所做的修改对于Read View是不可见的。除此之外,所有其他的小于创建Read View的事务号的所有记录均可见。可见包括两层含义:

 

    记录可见,且Deleted bit = 0;当前记录是可见的有效记录。

 

    记录可见,且Deleted bit = 1;当前记录是可见的删除记录。此记录在本事务开始之前,已经删除

 

 

故此,个人猜测当循环插入的记录越来越多时,故SELECT查询时,由于InnoDB引擎会由于Read View的可见性比较,从而扫描聚簇索引,然后对比聚簇索引中DATA_TRX_ID与当前事务ID,当记录越来越多时,这个对比算法的开销也会越来越大,从而导致SELECT查询时间变长。以上部分是个人结合相关参考资料而作的一个推测和解释,如有错误或不对的地方,敬请指正。关于Read View的可见性对比算法,参考MySQL-InnoDB-MVCC多版本并发控制 这篇博文,摘抄如下:

 

 

可见性比较算法(这里每个比较算法后面的描述是建立在rr级别下,rc级别也是使用该比较算法,此处未做描述)

 

设要读取的行的最后提交事务id(即当前数据行的稳定事务id) trx_id_current

 

当前新开事务id new_id

 

当前新开事务创建的快照read view 中最早的事务idup_limit_id, 最迟的事务idlow_limit_id(注意这个low_limit_id=未开启的事务id=当前最大事务id+1)

 

比较:

·         1.trx_id_current < up_limit_id, 这种情况比较好理解, 表示, 新事务在读取该行记录时, 该行记录的稳定事务ID是小于, 系统当前所有活跃的事务, 所以当前行稳定数据对新事务可见, 跳到步骤5.

·         2.trx_id_current >= trx_id_last, 这种情况也比较好理解, 表示, 该行记录的稳定事务id是在本次新事务创建之后才开启的, 但是却在本次新事务执行第二个select前就commit了,所以该行记录的当前值不可见, 跳到步骤4

·         3.trx_id_current <= trx_id_current <= trx_id_last, 表示: 该行记录所在事务在本次新事务创建的时候处于活动状态,从up_limit_idlow_limit_id进行遍历,如果trx_id_current等于他们之中的某个事务id的话,那么不可见, 调到步骤4,否则表示可见。

·         4.从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的undo-log的版本号, 将它赋值该 trx_id_current,然后跳到步骤1重新开始判断。

·         5.将该可见行的值返回。

 

 

 

 

参考资料

 

 

https://segmentfault.com/a/1190000012650596

https://blog.csdn.net/shaochenshuo/article/details/76137652