澳门在线威尼斯官方 > 电脑数据库 > 【威尼斯澳门在线】InnoDB下关于MVCC的一个问题的

原标题:【威尼斯澳门在线】InnoDB下关于MVCC的一个问题的

浏览次数:64 时间:2019-11-05

 

出自:

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

基本知识

 

假设对于多版本(MVCC)的基础知识,有所了解。InnoDB为了实现多版本的一致读,采用的是基于回滚段的协议。

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 ;

行结构

 

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

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

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

 

聚簇索引行结构(与多版本一致读有关的部分,DELETED BIT省略):

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;

威尼斯澳门在线 1

 

    二级索引行结构:

 

威尼斯澳门在线 2

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

    从聚簇索引行结构,与二级索引行结构可以看出,聚簇索引中包含版本信息(事务号+回滚指针),二级索引不包含版本信息,二级索引项的可见性如何判断?下面将会给出。

 

Read View

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)

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都是可见的 */

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

    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

威尼斯澳门在线 3

                transactions at the time when the read is

 

                serialized, except the reading transaction

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

                itself; the trx ids in this array are in a

 

                descending order */

 

dulint    creator_trx_id;    /* trx id of creating transaction, or

[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

                (0, 0) used in purge */

 

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

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

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

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

 

测试方法:

 

–create table and index

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

create table test (id int primary key, comment char(50)) engine=InnoDB;

 

create index test_idx on test(comment);

SELECT

–Insert

 

insert into test values(1, ‘aaa’);

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

insert into test values(2, ‘bbb’);

 

–update primary key

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

update test set id = 9 where id = 1;

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

–update non-primary key with different value

 

update test set comment = ‘ccc’ where id = 9;

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

–update non-primary key with same value

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

update test set comment = ‘bbb’ where id = 2 and comment = ‘bbb’;

 

–read隔离级别

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

repeatable read(RR)

 

测试结果

 

威尼斯澳门在线 ,update primary key

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

代码调用流程:

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

ha_innobase::update_row -> row_update_for_mysql -> row_upd_step -> row_upd -> row_upd_clust_step -> row_upd_clust_rec_by_insert -> btr_cur_del_mark_set_clust_rec -> row_ins_index_entry

 

简单来说,就是将cluster index的旧记录标记位删除;插入一条新纪录。该语句执行完之后,数据结构如下:

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

威尼斯澳门在线 4

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

 老版本仍旧存储在聚簇索引之中,其DATA_TRX_ID被设置为1811,Deleted bit设置为1,undo中记录了前镜像的事务id = 1809。新版本DATA_TRX_ID也为1811。通过此图,还可以发现,虽然新老版本是一条记录,但是在聚簇索引中是通过两条记录来标识的。同时,由于更新了主键,二级索引也需要做相应的更新(二级索引中包含主键项)。

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:【威尼斯澳门在线】InnoDB下关于MVCC的一个问题的

关键词:

上一篇:没有了

下一篇:没有了