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

 

这个是网友++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语句执行时间变长的原因是什么呢？ 如何解释得通呢？

 

 

 

刚开始讨论的时候，以为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 中最早的事务id为up_limit_id, 最迟的事务id为low_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_id到low_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