内容简介:这个是网友++C++在群里问的一个关于MySQL的问题,本篇文章实验测试环境为MySQL 5.6.20,事务隔离级别为REPEATABLE-READ ,在演示问题前,我们先准备测试环境。准备一个测试表test以及一个存储过程循环往test表里面插入记录。在线程ID为14的会话中,开启事务,然后执行查询test的SQL语句然后在线程ID为12的会话中,循环往表test里面插入1000000记录
这个是网友++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. 将该可见行的值返回。
参考资料 :
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。