undo日志insert，update，delete (1)—mysql进阶（六十四)

前面说了redo日志为了保证系统宕机的情况下，能够恢复数据，恢复数据是在以checkpoint_lsn为起始位子来恢复，在该值之前的都是已经持久化到磁盘的，可以为了提升效率而放弃，而之后的数据，也可能在checkpoint之后，被后台异步运行的线程刷新到磁盘，这时候如果file header里file_page_lsn值大于checkpoint_lsn值，代表已经持久化，也可以跳过。还有会吧同一个页的space id和page number放入一个hash表，这样避免同一个页反复I/O插入。

​​Redo日志 (5)—mysql进阶（六十三)​​

事务回滚需求

我们说过事务需要保证原子性， 那么全部完成，要么什么也不做。但偏偏有的时候执行到一半，比如系统宕机，停电，服务器错误等，比如一半之后，程序员可以手动执行rollback回滚。

但执行到一半就结束，可能会修改很多东西，我们需要把数据改回原来的样子，叫回滚(rollback)。这样看起来就如同事务什么都没做。

所以当我们新增一条数据的时候，如果想要回滚，至少要记录他的id，到时候把他删除。

当我们删除一条数据的时候，如果想要回滚，至少要记录他的id，到时候把他新增。

当我们修改一条数据的时候，如果想要回滚，至少要记录他的修改数据和id，到时候吧他修改回来。

innoDB吧这些东西记录在一个日志里，叫undo 日志，这里需要注意的是，select不需要回滚，所以不记录在这些里面。我们先看看事务ID是什么。

事务ID

前面我们说过事务可以开启只读事务，或者开启读写事务：

我们可以通过start transaction read only语句开启一个只读事务，在只读事务里，不可以对普通的表做增删改操作，但可以对临时表增删改。

可以strat transaction read write 语句开启读写事务，或者默认不指定就是开启读写事务。

如果在事务里进行了增删改操作，则innoDB存储引擎会给他分配一个独一无二的事务ID。

1. 对于只读事务，只有他第一次对临时表增删改才会为这个事务分配一个事务id，否则不分配。（我们前面说过用explain语句会有一个using temporay的提示，表示该语句会用到内部临时表，但这个跟我们自己创建的create temporary table是不一样的，这种临时表会不给他们分配独立的事务id）
2. 对于读写事务，只有他在第一次对表或者临时表增删改的时候，会给他分配一个事务id。

所以我们有的时候虽然开启了事务，但是并没有增删改，所以也不会给当前事务分配事务id。

事务id怎么生成的

这个事务id本质就是一个数字，他的分配策略和我们前面说的row_id大致相同：

1. 服务器会维护一个全局变量是事务id，当每次需要分配事务id的时候，该变量就+1.
2. 每当这个变量是256的倍数的时候，就会把这个id刷新到页号5称为max trx id的属性处，这个属性占用8个字节存储空间。
3. 当系统下一次重启的时候，会吧max trx id属性加载到内存，将该值加上256后赋值给我们前面提到的全局变量（因为上次关机时该全局变量值可能大于max trx id属性值）。

这样可以保证整个事务id是一个递增数字。

Trx_id隐藏列

前面我们说过innoDB行格式，聚簇索引记录除了保存完整的数据格式，额外数据外，还会有几个隐藏列，比如row_id，trx_id，roll_pointer，其中row_id不是必须的，说过很多次了，只有没有主见或者唯一键，才会创建隐藏的row_id，其中trx_id很好理解，就是事务id。

Undo 日志的格式

为了实现原子性，innoDB存储引擎在增删改的时候，需要把对应的undo日志记下来。一般每对一个undo日志做改动，都对应一个undo日志，但有的时候也可能对应两个undo日志，后面会仔细唠嗑。一个事务可能会进行增删改很多次undo记录，这些undo日志会从0编号开始，这个编号称为undo no，会从第0号undo，第一号unodo。。。

这些undo日志会记录到fil_page_undo_log的页面中，这些页面也可以从系统表空间分配，也可以从一种专门放undo日志的表空间，也就是所谓的undo table space中分配。我们先创建一个undo_demo表：

mysql> create table undo_demo(

    ->  id int not null,

    ->  key1 varchar(100),

    ->  col varchar(100),

    ->  primary key(id),

    ->  key idx_key1(key1)

    -> );

Query OK, 0 rows affected (0.05 sec)

表中id主键，key1是二级索引，col是普通列。我们前面说过每个表都有一个唯一的table id，在information_Shcema中的innodb_sys_tables。

mysql> SELECT * FROM information_schema.innodb_sys_tables WHERE name = 'utf_8/undo_demo';

+----------+---------------------+------+--------+-------+-------------+------------+---------------+------------+

| TABLE_ID | NAME                | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |

+----------+---------------------+------+--------+-------+-------------+------------+---------------+------------+

|      138 | utf_8/undo_demo |   33 |      6 |   482 | Barracuda   | Dynamic    |             0 | Single     |

+----------+---------------------+------+--------+-------+-------------+------------+---------------+------------+

1 row in set (0.01 sec)

从结果可以看到当前表的table id 是138.

Insert操作对undo日志

我们前面说过插入一条记录分为乐观插入和悲观插入（乐观表示存储数据的内存充足，悲观表示不充足，需要分裂数据页，甚至分裂内节点页），但不管怎么插入，最终的结果就是把记录放到数据页中。如果需要回滚，只要吧这个记录删除就好。所以innoDB设计了一个类型为TRX_undo_insert_rec和undo日志，他的结构如下：

End of record:本条undo日志结束，下一条开始时在页面中的地址。

Undo type：本条undo日志的类型，也就是trx_undo_insert_rec。

Undo on：本条undo日志对应的编号。

Table id：本条undo日志对应记录所在的table id。

主键各列信息<len,value>列表：主键每个列占用的空间大小和真是的值。

Start of record：上一条redo日志结束， 本条开始在页面中的地址。

注意：undo on在一个事务里从0开始递增，只要事务没有提交，后面的undo on都会+1。

如果记录中主键只包含一个列，那么在该类型trx_undo_insert_rec和undo日志中只需要吧该列占用的存储空间大小和真实值记录下来，如果记录中包含多个列，那么每列真实值和记录大小对应的真实值都要记录下来。(图中len就代表存储空间大小，value就代表真实的值)

当我们在表里插入一条数据的时候，聚簇索引和二级索引都会改变，但我们只需要记录聚簇索引到undo日志就好，因为聚簇索引和二级索引是一一对应，删除的时候，只要根据聚簇索引删除对应的二级索引就好。

我们现在向undo_demo表插入两条记录：

BEGIN;  # 显式开启一个事务，假设该事务的id为100

# 插入两条记录

INSERT INTO undo_demo(id, key1, col)

    VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');

因为插入两条记录，所以产生两个类型为TRX_UNDO_INSERT_REC的undo日志：

End of record：地址

Undo type：trx_undo_insert_rec

Undo no：0  和 1

Table id：138

主键各列信息<len,value>：<4，1>和<4，2>

Strat of record:地址

从上面我们主要看到两个不同的，主列信息 和undo no。

Roll_pointer隐藏列的含义

这个占用7个字节的隐藏字段，本质上是指向undo日志的一个指针。比如我们前面插入的两条数据，每条数据都对应一个undo日志。记录被存储到fil_page_index的页面中（就是我们前面说的数据页），而undo日志就是记录在fil_page_undo_log的页面中，他们两个页面什么关系呢。

Roll_pointer就是在fil_page_index页面的也一个字段，可以指向每条数据对应的undo日志。

Delete操作对应的undo日志

我们知道插入的页面，数据页里的数据通过next record会组成一个单向链表，我们吧这个链表称为【正常记录链表】。还说过被删除的记录也会根据头信息中的next record组成一个删除链表，只是这个链表中的数据可以被重新利用，所以叫他【垃圾链表】。

Page header部分有一个称为page_free的属性，他指向被删除记录组成的垃圾链表头节点。

正常记录有一个delete_mask属性，当时0的时候，代表这个记录还未删除。

假如我们要把正常记录链表一条数据删除，那么他会被移到page_free指定的垃圾链表，这个过程包含两个步骤。

步骤一：仅仅将delete_mark标识改为1。这个阶段称为delete_mark，但是当前还并没有移动到垃圾链表，处于中间状态。(为什么会有这种状态呢，主要为了实现一个称为MVCC的功能)

步骤二：当删除语句在所有事物提交之后，会有专门的线程吧他从正常记录链表移动到垃圾链表，还需要调整一些其他信息，比如页面中的用户记录数量page_n_recs、上次插入记录的位置page_last_insert、垃圾链表头节点指针page_free，页面中可重用的字节数量page_garbage、还有页目录信息等。innoDB吧这一阶段称为purge。

为什么会修改page_free属性呢，因为新删除的数据会放在垃圾链表的头部。

(注意：page_garbage在page header里，每当有数据删除，会吧当前值加上已删除数据的字节大小。Page_free指向垃圾链表的头部节点，每当有新数据插入，首先判断指向的头部节点存储空间是否足够容纳新的数据，如果不可以容纳，则会申请新的空间。如果可以容纳，那么直接重用这条已删除的存储空间，并吧page_free指向垃圾链表的下一条记录。但有个问题，如果新插入的数据比垃圾链表的头部节点占用空间小太多，这样就有很多多余的空间，这些就是碎片空间。那这些碎片空间聚用不到了吗，也不是，他会存储在page_garbage属性中，这些碎片空间在整个页面被使用完成前并不会被重新利用，当存储空间不够，会查看page_garbage里的剩余空间是否可以容纳，可以的话，会开辟临时页面依次吧数据放进去，之后再拷贝到垃圾链表)

从上可以知道，在删除语句提交事务之前，只需要执行阶段一，也就是delete_mark阶段，提交之后就不需要回滚了，所以回滚只需要考虑第一阶段的影响。所以innoDB设计了TRX_UNDO_DEL_MARK_REC类型的undo日志，他的完整结构如下：

End of record:本条redo日志结束，下一条开始在页面中的地址。

Undo type:trx_undo_Del_mark_rec。

Undo on:本条redo日志对应的编号。

Table id：本条redo日志对应的所在表的table id。

Info bits：记录头信息前4个比特位的值以及record_type的值。

Old_trx_id：记录旧的trx_id的值。

Old_roll_pointer：记录旧的roll_pointer值。

主键各列信息<len,value>的值：主键的每个列占用空间大小和值。

Index_col_info len：下面索引列各列信息部分和本部分占用存储空间大小。

索引列各列信息<pos，len，value>：凡是被索引包含的列的各列信息。

Start of record：上一条redo日志结束，本条开始在页面地址的值。

首先在进行delete mark操作的时候，需要把trx_id和roll_pointer记录下来，就是上面的old_trx_id,old_roll_pointer属性。这样好处就是，可以在undo日志的old_roll_pointer找到记录在修改之前对应的undo日志。

执行完delete mark后，它对应的undo日志和insert操作对应的undo日志就串成了一个链表。这个链表称为版本链，等我们后面介绍update操作时候，会看到这个【版本链】的强大。

与trx_undo_insert_rec不同的是，trx_undo_del_mark_rec的redo日志还多了一个索引列各列信息的内容，也就是说我们某个列如果包含在索引中，那么他的相关信息会记录到索引列各列信息部分，相关信息包含该列在记录中的位置(pos)，该列占用存储空间大小(len)，该列实际值（value）。这些值主要在第二阶段purge阶段使用。

介绍完之后，我们来看一下实例，比如吧id为1的那条记录删除。

BEGIN;  # 显式开启一个事务，假设该事务的id为100

# 插入两条记录

INSERT INTO undo_demo(id, key1, col)

    VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');

# 删除一条记录   

DELETE FROM undo_demo WHERE id = 1;

这时候delete mark操作对应的redo日志为：

Undo type：trx_undo_del_mark_rec

Undo no:2

Table id:138

Old trx id:100

Old roll_pointer：对应的上一个insert回滚地址。

主键各列信息：<4,1>

本部分和下一部分占用的存储空间大小：13

索引各列信息pst，len，value：<0，4，1><3,3,’AMW’>

需要注意的是，这个old roll pointer会指向trx_undo_insert_rec的地址

Undo type：trx_undo_insert_rec。

Undo no：0

Table id：138

主键各列信息：<4,1>

综上我们可以知道，因为这个trx_undo_del_mark_rec是第三条redo日志，所以undo no为2.

在delete mark操作时候，记录的trx_id为100，所以把100填入old trx_id中，然后把roll_pointer的值取出来，放入old_roll_pointer就可以根据old_roll_pointer定位到最近一次做修改的redo日志。

由于undo_demo有两个索引：一个是聚簇索引，一个是二级索引idx_key1。只要包含在索引中的列，那么这个列就记录的位子（pos），占用空间（len），和实际值value就需要存储在redo日志中。

对于主键来说<0,4,1>，只包含一个id列，存储到undo日志中相关信息分别是：

Pos：id列为主键，所以在第一列，所以他的位置在0。一个字节来存储。

Len：id列为int，占用4个字节，所以len为4。存储4用1个字节来存储。

Value：1，被删除的id为1，所以显示1。Value占用四个字节。

对于二级索引来说<3,3,’AWM’>，存储到undo日志中相关信息分别是：

Pos：因为这个排在主键，trx_id，roll_pointer之后，所以他显示3.

Len：varchar(100)，使用utf8字节，存储’AWM’,所以占用三个字节。

Value：就是AWM。三个字节存储。

从上面可以知道，主键和二级索引一共占用11个字节，然后index_col_info_len本身占用2个字节，所以一共占用13个字节填入到当前字段。

Update操作对应的undo日志

在执行update语句时候，innoDO对于主键更新或者不更新有截然不同的两种处理方式。

不更新主键情况

再不更新主键的情况，又分为被更新的列占用存储空间不发生变化和发生变化的情况。

In-place update（就地更新）

对于被更新的列和更新前的列占用空间不发生变化，这种称为【就地更新】，也就是原记录基础上修改值。

例子：id：4个字节,2

Trx_id：6个字节，100

Roll_pointer：7个字节。

Key1：4个字节，m416

Col：6个字节，步枪

如果这时候把他更新为

UPDATE undo_demo

    SET key1 = 'P92', col = '手枪'

    WHERE id = 2;

这时候key1从m416四个字节编程P92三个字节，所以不满足更新前后占用的空间一致，这时候就不满足就地更新。

如果把他更新为

UPDATE undo_demo

    SET key1 = 'M249', col = '机枪'

    WHERE id = 2;

这时候key1从m416四个字节变为M249四个字节，col从步枪6个字节变为机枪6个字节，满足就地更新。

先删除掉旧记录，再插入新数据

在不更新主键的情况下，任何一个被更新的和更新前存储空间大小不一致，则需要把这条记录从聚簇索引页面先删除，然后再根据后面的值创建一条新的数据插入其中。

注意这里的删除并不是delete mark，而是真正的删除，也就是吧正常链表的数据移动到垃圾链表中，并修改页面相对应的统计数据（page_free，page_garbase等）。

这里如果新创建的记录占用存储空间大小不超过旧记录占用的空间，那么可以直接重用被加入到垃圾链表中旧记录所占用的存储空间，否则的话需要申请新的内存空间以供新记录使用，如果本页面已经没有可用空间的话，那就需要进行页分裂，然后插入新的数据。

针对update不更新主键情况，上面介绍了直接就地更新和先删除在插入新记录，innoDB设计了一种类型为trx_undo_upd_exist_rec的undo日志，它的结构如下：

End of record：本条redo日志结束，下一条开始时在页面中的地址。

Undo type：trx_undo_upd_exist_rec

Undo on：本条日志对应的编号

Table id：本条日志对应的表table id

Info bits：记录头信息前4个比特位record type的值

Old_trx_id：旧的trxID

Old roll_pointer：旧的roll_pointer。

主键各列信息<len,value>列表：主键每个列占用大小和真实值。

N_updated：共多少个列被更新。

被更新列更新前信息<pos,old_len，old_value>列表：被更新前信息。

Index_col_info len：索引各列列信息部分和部分占用空间大小

索引列各列信息<pos,len,value>列表：凡是被索引包含的列的各列信息。

Start of record：上一条undo日志结束，本条开始时在页面地址。

大部分和我们前面介绍的trx_undo_delete_mark_rec类似，需要注意的几点就是：

N_updated属性表示有几个列被更新，后面跟着的pos，len，value代表位子，内存，和值

如果update包含在索引里，则会有索引列的信息，否则不会有这个列。

例子：

BEGIN;  # 显式开启一个事务，假设该事务的id为100

# 插入两条记录

INSERT INTO undo_demo(id, key1, col)

    VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');

# 删除一条记录

DELETE FROM undo_demo WHERE id = 1;

# 更新一条记录

UPDATE undo_demo

    SET key1 = 'M249', col = '机枪'

    WHERE id = 2;

我们吧几个着重改变的参数看一下：

Old roll_pointer：指定到insert的undo文件。

U_updated：2

被更新前的数据：<3,4,’M416’><4,6,’步枪’>

索引列信息：<0,4,2><3,4,’M416’>

因为有个主键和二级索引，所以有两个索引列信息。

更新主键的情况

在聚簇索引中，记录是按主键大小连成的单向链表，如果我们修改了某个主键值，意味着在聚簇索引的位子发生改变，针对这种情况，innoDB对聚簇索引的处理分成了两步：

1. 将旧的记录进行delete mark操作

注意，这里是deletemark ，delete mark，delete mark，也就是说在update事务提交前，只对旧的记录做delete mark，之后再提交给专门的线程做purge操作，把他们加入垃圾链表中。这里一定要和上面说的不更新记录主键值时，先真正删除旧记录，再插入新记录区分开。

(之所以没有真正删除，只做delete mark，是因为别的事务可能也在访问这些数据，为了防止其他事务访问不到。这就是MVCC)

1. 根据更新后各列的值创建一条新纪录，并将它插入聚簇索引中(需要重新定位插入的位子)。

因为更新后主键值变化，需要重新定位并且插入。

针对update 语句更新主键情况，会记录一条trx_undo_del_mark_rec的redo日志，之后插入新数据，会记录一条trx_undo_insert_rec的redo日志，也就是更新主键的情况下，会先删除，再新增，有两条undo日志。