mysql autocommit undo_MySQL必知必会：简介undo log、truncate、以及undo log如何帮你回滚事务...

一、前言

在整理undo log笔记前我感觉它应该是在 undo、redo、bin log三者中需要整理的内容最少的。但是实际上并不是想象的那么简单。

关于undo log需要整理的两大块知识点分别是：

1、简介undo log、truncate、以及undo log如何帮你回滚事务(本篇分享)

2、undolog链条、ReadView、以及undo log如何帮你实现MVCC多版本并发控制(明天分享)

二、undo log表空间

如果你看了白日梦前面的分享的笔记，你肯定知道了什么表空间。其实所谓的表空间其实是真实存在于磁盘上的数据文件。而这里的所说的undolog表空间其实就是磁盘上专门存放undo log的文件。

表空间由很多 segment(段) 组成，而这众多的段中有一种就是 undo segment。

默认情况下undo segment 会存放于系统表空间中，或者说undo log默认会记录在共享表空间文件中(文件真实存在)。

但是MySQL也提供了参数，让你可以控制MySQL讲undo log写入到单独的表空间文件中去。尤其是当你使用SSD这种存储时，尤为推荐将undo log从共享表空间中拿出去。

三、关于undo log默认的配置

默认情况下undo log tablespace个数是0，也就是说如果你不干涉MySQL的配置。那么MySQL就会帮你将undo log记录到共享表空间中。

MySQL默认的配置文件 my.cnf 长下面这样：

如果你现在仅仅是安装了MySQL，而不曾启动过mysql，那你去datadir中查看会发现它只是个空目录。

但是当你启动过MySQL之后，再去这个datadir中查看会发现里面多了很多文件，其中就包括共享表空间文件ibdata1(但是没有undolog表空间文件)。如下：

四、如何将undo log放到单独的表空间

如果你想将undo log拿到undo log表空间文件中。那你可以像下面这样修改MySQL的配置文件my.cnf

修改完后通过如下命令启动mysql

systemctl start mysqld.service

但是你会发现启动不了，如果你去排查原因就会发现：因为曾经初始化过 datadir 目录中的文件，你添加的新配置innodb_undo_tablespaces和原来的配置是冲突的，需要开辟新的表空间文件，所以导致启动失败。

解决的方式：简单粗暴的将换个datadir文件就好啦，所以如果你从一开始就想将undolog拿到单独的表空间中，那么最好从一开始就将这个配置添加进去，否则还是挺麻烦的。

本文是MySQL专题第14篇，全文近100篇

本文是第14篇，全文近100篇，点击查看目录

五、rollback segment

提到了undo log，就不得不说roll back segment这个知识点了。它并不难理解，你可以阅读下面的介绍了解一下。

InnoDB存储引擎会先初始化好rollback segment(回滚段)，在每个回滚段中会记录N个undo log segment，而我们说的undo log就是在 undo log segment中申请出来的！

在早期的InnoDB版本中只有一个rollback segment，因此在同一时刻它支持的在线事务的上限被限制在1024个。

在MySQL5.7中回滚段已经支持到了128个(上限是128)。其中32个分配给临时表空间。剩下的96个回滚段可以分配给修改常规表中数据的事务。

用户可以通过参数innodb_rollback_segments调整回滚段的数量。

另外，我们上面提到的： 每个回滚段中都记录了N个undolog segment， 这里的N和数据页大小有关

InnoDB页面大小

回滚段中的撤消插槽数(InnoDB页面大小/ 16)

4096 (4KB)

256

8192 (8KB)

512

16384 (16KB)

1024

32768 (32KB)

204

65536 (64KB)

4096

六、什么是undo log truncate

truncate意为：截断

其实结合 truncate table sql，就能更好的理解这个概念。当你不需要某个表中的数据时，你可以执行truncate sql将表中的数据清空掉。同样的undo log的truncate机制本质上就是为undo log 表空间文件瘦身，将不需要的undo log清理掉。

在MySQL 5.6(包括5.6)之前Undo tablespace里面的undo数据文件是无法收缩的。也就是说在实例的运行过程中如果遇到有大的事务，会把undo log的文件撑的非常大。浪费大量的空间甚至会把磁盘打爆。同时也增加了数据库物理备份的时间。

在MySQL5.7中允许用户在线truncate undo log

七、如果做 undo log truncate

前提：必须使用独立的undo表空间

然后配合如下的参数辅助：

创建数据表：

create table test (

id int primary key auto_increment,

name varchar(64)

);

然后不断的往这个测试表中插入数据

insert into test(name) values(repeat('a',64));

insert into test(name) select name from test;

一边插入一边观察undo 表空间文件的变化：你会发现undo003这个表空间文件已经超过了参数：innodb_max_undo_log_size=100M 指定的范围，意味着这个undolog已经被标记为可回收了。

当事务提交时，undo log并不会被立即删除，因为可能存在其它的事务需要使用undo log将数据回滚到之前的版本。最终是否可以删除undo log由purge线程决定。

为了让pruge线程运行，可以执行如下的sql

delete from test limit 1;

八、undo log的类型

undo log有两种类型，分别是 insert undo log 和 update undo log。

前者记录的是insert 语句对应的undo log。

后者对应的是 update、delete 语句对应的undo log。

九、insert undo log 长啥样？

对于 insert 类型的sql，会在undo log中记录下方才你insert 进来的数据的ID，根据ID完成精准的删除。

insert 类型的undo log长下面这样：

可能你打眼一看上图就能知道各部分都有啥用。

但是，不知道你会不会纳闷这样一个问题：不是说对于insert 类型的undo log MySQL记录的是方才插入行ID吗？怎么上图整出来的了这么多Col1、Col2、Col2。

其实是MySQL设计的很周到，因为它是针对联合主键设计的。

十、一条update undo log 长啥样？

一条update sql对应undolog长如下这样。

其实我感觉没必要记住这个图，记住了也会忘。大概看一下它长什么样子就好。

重点是下面会分享的，undo log链条，并且你得知道这个链条可以帮你实现事务的回滚

​

十一、事务是如何回滚的？(undo log 链条)

举个例子：

对于 insert 类型的sql，会在undo log中记录下方才你insert 进来的数据的ID，当你想roll back时，根据ID完成精准的删除。

对于delete类型的sql，会在undo log中记录方才你删除的数据，当你回滚时会将删除前的数据insert 进去。

对于update类型的sql，会在undo log中记录下修改前的数据，回滚时只需要反向update即可。

对于select类型的sql，别费心了，select不需要回滚。

先看一个简单的insert undo log 链条

有一个注意点：因为单纯的insert sql不涉及多MVCC的能力。

所以一旦事务commit，这条insert undo log就可以直接删除了。

再看一个update类型的undo log

为了方便画图，重点突出链条的概念我省略了update undo log的部分内容

一个事务A开启后插图了一条记录：name = tom，MySQL会记录下这样一条undo log

随后先后来了两个事务：

事务B，事务ID=61，它执行sql将name 改成jerry。

事务C，事务ID=62，它执行sql将name 改成tom。

于是MySQL记录下这样一条新的undo log

你可以看到，MySQL会将对一行数据的修改undo log通过DATA_ROLL_ID指针连接在一起形成一个undo log链表链条。这样事务C如果想回滚，他会将数据回滚到事务B修改后的状态。而事务B想回滚他会将数据回滚到事务A的状态。

十二、问个问题

在前面的文章中有专门的介绍：表空间、数据表、数据区、数据页。

表空间、数据页存在于物理层面。SQL想要修改的数据表、id=xxx的行都是逻辑上的。

而 undo log 帮你做的是逻辑上的数据回滚，而不是物理(数据页)上是数据回滚。

其实在逻辑层和物理层都能回滚。

那，你有没有想过为什么undo回滚的层面要设置在逻辑层而不是物理层的数据页级别？

原因你可以这样想：假如一个数据页中存了300行数据，而你的update语句其实可能仅仅是更新了这个数据页中的一行。但是数据库可不一定是你自己在用！很可能有其他的用户也在使用并且修改了该数据页中的另外200行。那这时如果你基于数据页层面回滚，岂不是会将别人的不想回滚的数据给改错？

十三、补充：

在MySQL5.6、MySQL5.7版本中可以通过innodb_undo_tablespaces参数配置redo log表空间文件的个数，但是官网也有介绍这个参数在未来的MySQL版本中将会被废弃，在MySQL8.0中初始化MySQL实例时会创建两个默认的撤消表空间，并且可以使用CREATE UNDO TABLESPACE语法创建其他撤消表空间 。

但是不管怎么样，如果你使用的是MySQL5.7还是推荐使用这些参数以及开启undo log的自动truncate。

参考：

《MySQL技术内幕》

推荐阅读

面试官都关注了！你还在犹豫什么呢？