MySQL系列-undo

之前讨论过redo的作用，那么与之对应的便是undo了，undo记录了事务的行为，实现了MySQL的回滚和MVCC。当update、delete或者insert一条数据，都会对应的在undo中生成一个前镜像信息，这个前镜像是以逻辑方式存储的（即反向更改的SQL语句，delete对应insert，insert对应delete），当需要rollback时，通过undo记录信息进行rollback，同Oracle undo一样，MySQL undo也是通过segment管理具体事务的，但是存储在共享表空间中（ibdata1），但是从mysql 8.0.20开始undo单独存放在数据目录中（undo_001、undo_002）。实际上当进行数据修改时不光要记录到undo中，使用undo这个动作还要记录到redo中，同Oracle中相似，在使用undo回滚时也会产生一定量的信息记录redo，总之，不管不管innodb怎样使用undo 这个信息都是要记录redo的。

MVCC

MVCC实现的很重要的一个基础就是undo，当读取数据发现当前行记录已经被事务占用，就会通过数据页中记录的undo地址寻址到undo中，读取行版本信息，实现非锁定读。

undo存储管理

undo管理同表空间管理一样都是通过segment来管理的，innodb存储引擎有128个rollback segment，每个rollback segment中记录了1024个undo log segment，在每个undo log segment 中进行undo page 申请，即实际可用undo segment为 128*1024，可通过innodb_undo_logs来修改rollback segment数量，默认128，innodb_undo_tablespaces指定undo表空间数量，默认为2

事务发起commit之后不会立马删除undo log及undo log所在的页，这是因为还要支持MVCC技术，需要读取undo行记录版本号。是在commit发起后将undo log放入一个链表中（history list），判断undo page使用空间是否小于3/4，若是则undo page可以被重用，之后新的undo log在这个undo log后面，即一个undo page存放不同事务的undo log，以发挥undo page最大的使用率。

①将undo log放入列表中，不立马释放undo log，提供MVCC支持，待purge线程来进行回收。

②purge线程从history list尾端开始判断undo log是否有事务持有可以释放重用，若可以，则进行purge回收，同时进入到undo purge队列里继续查找。

undo log 格式

在Innodb中undo log分为 insert undo log和update undo log，他们分别存储响应操作类型的前镜像数据。insert操作只对事物本身可见，对其他事务不可见，故在事务commit之后 insert undo log会立刻被回收不需要进行purge操作，也不涉及MVCC。而update undo log则不然，他需要提供MVCC技术，故事务commit之后不会立刻删除，提交之后放入history list链表。而是由purge线程进行统一管理删除操作。update实际上是delete+insert，delete使用update undo log，在管理update undo log的undo log segment中，而insert使用insert undo log，在管理insert undo log的undo log segment中。故mysql中一个事务可以使用两个undo段，反之一个undo段也可以被多个事务使用。这一点同Oracle不一样。

insert undo log

next：2字节，记录下一个undo log位置以及当前undo log占用的大小。

type_cmp1:1字节，记录undo 类型，对于insert undo log，该值为11。

undo_no：记录事务ID，通过字段压缩进行存储。

table_id：记录undo log对应的表对象，通过字段压缩进行存储。

len1 col1：记录所有主键的列和值，根据这些值定位到具体记录进行rollback。

start：2字节，undo log开始的位置

update undo log

在insert undo log结构基础之上多了一些结构，其中next、undo_no、table_id、start同insert undo log相同。

type_cmp1:1字节，记录undo类型，不同类型update值也不一样

TRX_UNDO_UPD_EXIST_REC更新non_delete_mark的记录，值为12.

TRX_UNDO_UPD_DEL_REC将delete的记录标记为not delete，值为13.

TRX_UNDO_DEL_MARK_REC将记录标记为delete，值为14.

update_vector:表示update操作导致发生改变的列。

查看undo信息视图

information_schema.INNODB_TRX_ROLLBACK_SEGMENT 查看 rollback segment

INNODB_TRX_UNDO 记录事务对应的undo log

MySQLdelete操作过程

delete一条数据时首先将删除列的记录delete flag设置为1，该记录没有被实际删除在存在B+树中，索引上的信息也没有进行维护甚至没有产生undo log，这个真正的删除操作被“延时”，由purge线程来完成，以提供MVCC。由于事务commit之后按照顺序将undo log放入history list中，如下图所示，trx*代表事务在undo中的记录即undo log，按照提交的先后顺序由history list管理，同时他们实际存放在undo page中，其中TRX5表示正在被事务引用，触发purge线程首先先从history list尾端进行遍历查找，即trx1那一段，首先回收的便是TRX1的undo log，接下来不是去查找TRX2，而是在trx1的undo page（undo page1）中继续向下查找，下一个要查找并回收的是trx3，接着找到trx5但是trx5被事务在占用，故再去history list中继续向前查找，刚才在history list中遍历到了trx1 ，接下来遍历trx2 ，找到并回收 ，同理查找undo page2中的trx6，接下来是trx4。就以这样的逻辑顺序查找回收，每次回收300个page，通过上述方式可以发现purge实际上是一个离散读的过程，故实际过程中这个过程会很慢，同时也会消耗一定的IO，参数innodb_purge_batch_size可以设置每次purge的page数量。innodb_max_purge_lag控制history list的长度，默认为0（不做限制），purge速度和history list的长度是需要动态维持一定平衡的，purge的过多会造成IO压力，过少会造成history list过长，若长度达到innodb_max_purge_lag参数限制时，会“延缓”DML操作，其算法为delay=((length(history_list) - innodb_max_purge_lag * 10 )) - 5，delay单位是毫秒，但是delay代表的是一个dml操作的行，参数innodb_max_purge_lag_delay控制delay最大的毫秒数，避免purge缓慢造成SQL线程无限制等待。可见innodb_max_purge_lag=0不可随意更改。

undo相关参数

+--------------------------+--------------+
| Variable_name            | Value        |
+--------------------------+--------------+
| innodb_max_undo_log_size | 1048576000   | undo表空间大小 1G
| innodb_undo_directory    | /data/mysql/ | undo数据文件位置
| innodb_undo_log_encrypt  | OFF          | undo存储加密
| innodb_undo_log_truncate | ON           | 开启自动清理undo功能
| innodb_undo_tablespaces  | 2            | undo文件个数
+--------------------------+--------------+

为什么MySQL中类似01555（快照过旧）报错很罕见

首先还原Oracle ora-0155报错，当前undo segment使用情况。

SQL>  SELECT TABLESPACE_NAME,SEGMENT_NAME,
  2         STATUS,
  3         TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M"
  4    FROM DBA_UNDO_EXTENTS
  5   GROUP BY TABLESPACE_NAME, SEGMENT_NAME,STATUS
  6   order by  3,2;

TABLESPACE_NAME                SEGMENT_NAME                   STATUS        Size M
------------------------------ ------------------------------ --------- ----------
UNDO1                          _SYSSMU14_1165417767$          EXPIRED            0
UNDO1                          _SYSSMU15_2328083754$          EXPIRED            0
UNDO1                          _SYSSMU11_2675123733$          UNEXPIRED          0
UNDO1                          _SYSSMU12_2926367252$          UNEXPIRED          0
UNDO1                          _SYSSMU13_3035459788$          UNEXPIRED          6
UNDO1                          _SYSSMU14_1165417767$          UNEXPIRED          0
UNDO1                          _SYSSMU15_2328083754$          UNEXPIRED          0
UNDO1                          _SYSSMU16_111403298$           UNEXPIRED          0
UNDO1                          _SYSSMU17_2771507285$          UNEXPIRED          0
实验开始
SQL> DECLARE
  2  i INT;
  3  BEGIN
  4  i := 1;
  5  WHILE(i < 100000)
  6  LOOP
  7  i := i + 1;
  8  INSERT INTO test_obj(object_id) VALUES(i);
  9  commit;
 10  END LOOP;
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL> begin
  2   open :x for select * from test_obj;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> 
SQL> begin
  2    for i in 1 .. 100000 loop
  3   update test_obj set object_id=i+1 where object_id =i;
  4   commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

开始update之后原来的undo segment _SYSSMU13_3035459788$是unexpired状态，虽然是倾向于保留状态的，但是有事务使用时，undo自动管理，也会去分配这里的空间给active segment使用，随之 14  15段也进行了UNEXPIRED-> active ->UNEXPIRED这个状态过程。
SQL>  SELECT TABLESPACE_NAME,SEGMENT_NAME,
  2         STATUS,
  3         TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M"
  4    FROM DBA_UNDO_EXTENTS
  5   GROUP BY TABLESPACE_NAME, SEGMENT_NAME,STATUS
  6   order by  3,2;

TABLESPACE_NAME                SEGMENT_NAME                   STATUS        Size M
------------------------------ ------------------------------ --------- ----------
UNDO1                          _SYSSMU11_2675123733$          UNEXPIRED          0
UNDO1                          _SYSSMU12_2926367252$          UNEXPIRED          0
UNDO1                          _SYSSMU13_3035459788$          UNEXPIRED          4
UNDO1                          _SYSSMU14_1165417767$          UNEXPIRED          1
UNDO1                          _SYSSMU15_2328083754$          UNEXPIRED          1
UNDO1                          _SYSSMU16_111403298$           UNEXPIRED          0
UNDO1                          _SYSSMU17_2771507285$          UNEXPIRED          0

SQL> /

TABLESPACE_NAME                SEGMENT_NAME                   STATUS        Size M
------------------------------ ------------------------------ --------- ----------
UNDO1                          _SYSSMU13_3035459788$          ACTIVE             1
UNDO1                          _SYSSMU11_2675123733$          EXPIRED            0
UNDO1                          _SYSSMU13_3035459788$          EXPIRED            0
UNDO1                          _SYSSMU14_1165417767$          EXPIRED            0
UNDO1                          _SYSSMU11_2675123733$          UNEXPIRED          0
UNDO1                          _SYSSMU12_2926367252$          UNEXPIRED          0
UNDO1                          _SYSSMU13_3035459788$          UNEXPIRED          3
UNDO1                          _SYSSMU14_1165417767$          UNEXPIRED          1
UNDO1                          _SYSSMU15_2328083754$          UNEXPIRED          1
UNDO1                          _SYSSMU16_111403298$           UNEXPIRED          0
UNDO1                          _SYSSMU17_2771507285$          UNEXPIRED          0
看现在undo segment原来的undo段都经历了N次，UNEXPIRED-> active ->UNEXPIRED这个状态过程，那么原来undo segment里记录的前镜像全部被覆盖。
SQL> /

TABLESPACE_NAME                SEGMENT_NAME                   STATUS        Size M
------------------------------ ------------------------------ --------- ----------
UNDO1                          _SYSSMU13_3035459788$          ACTIVE             0
UNDO1                          _SYSSMU11_2675123733$          UNEXPIRED          2
UNDO1                          _SYSSMU12_2926367252$          UNEXPIRED          1
UNDO1                          _SYSSMU13_3035459788$          UNEXPIRED          1
UNDO1                          _SYSSMU14_1165417767$          UNEXPIRED          1
UNDO1                          _SYSSMU15_2328083754$          UNEXPIRED          1
UNDO1                          _SYSSMU16_111403298$           UNEXPIRED          1
UNDO1                          _SYSSMU17_2771507285$          UNEXPIRED          1
这里模拟select时间很长的SQL，需要查找前镜像，但是前镜像已经被覆盖了
SQL> print :x
ERROR:
ORA-01555: snapshot too old: rollback segment number 16 with name
"_SYSSMU16_111403298$" too smalll

MySQL innodb存储引擎中为什么快照过旧比较罕见呢

①innodb中undo segment 可以有128*1024个，数量极多，undo信息以undo log形式存放在undo segment中，每个undo segment可以存放多个事务的undo log，undo segment使用比较充分

②innodb中undo回收是purge线程来做，一旦发现当前undo segment被事务占用，那么将不进行purge操作。对于select操作，innodb会默认undo segment是被占用状态，不论是RC还是RR隔离级别我们都很难看到一个执行时间很长的select报错，除非你强行取消select。