MySQL5.7新特性——在线收缩undo表空间

共计 2883 个字符，预计需要花费 8 分钟才能阅读完成。

1. MySQL 5.5 时代的 undo log
    在 MySQL5.5 以及之前，大家会发现随着数据库上线时间越来越长，ibdata1 文件（即 InnoDB 的共享表空间，或者系统表空间）会越来越大，这会造成 2 个比较明显的问题：
（1）磁盘剩余空间越来越小，到后期往往要加磁盘；
（2）物理备份时间越来越长，备份文件也越来越大。

这是怎么回事呢？
原因除了数据量自然增长之外，在 MySQL5.5 以及之前，InnoDB 的 undo log 也是存放在 ibdata1 里面的。一旦出现大事务，这个大事务所使用的 undo log 占用的空间就会一直在 ibdata1 里面存在，即使这个事务已经关闭。

那么问题来了，有办法把上面说的空闲的 undo log 占用的空间从 ibdata1 里面清理掉吗？答案是没有直接的办法，只能全库导出 sql 文件，然后重新初始化 mysql 实例，再全库导入。

2. MySQL 5.6 时代的 undo log

MySQL 5.6 增加了参数 innodb_undo_directory、innodb_undo_logs 和 innodb_undo_tablespaces 这 3 个参数，可以把 undo log 从 ibdata1 移出来单独存放。

下面对这 3 个参数做一下解释：

（1）innodb_undo_directory，指定单独存放 undo 表空间的目录，默认为.（即 datadir），可以设置相对路径或者绝对路径。该参数实例初始化之后虽然不可直接改动，但是可以通过先停库，修改配置文件，然后移动 undo 表空间文件的方式去修改该参数；

（2）innodb_undo_tablespaces，指定单独存放的 undo 表空间个数，例如如果设置为 3，则 undo 表空间为 undo001、undo002、undo003，每个文件初始大小默认为 10M。该参数我们推荐设置为大于等于 3，原因下文将解释。该参数实例初始化之后不可改动；

（3）innodb_undo_logs，指定回滚段的个数（早期版本该参数名字是 innodb_rollback_segments），默认 128 个。每个回滚段可同时支持 1024 个在线事务。这些回滚段会平均分布到各个 undo 表空间中。该变量可以动态调整，但是物理上的回滚段不会减少，只是会控制用到的回滚段的个数。

实际使用方面，在初始化实例之前，我们只需要设置 innodb_undo_tablespaces 参数（建议大于等于 3）即可将 undo log 设置到单独的 undo 表空间中。如果需要将 undo log 放到更快的设备上时，可以设置 innodb_undo_directory 参数，但是一般我们不这么做，因为现在 SSD 非常普及。innodb_undo_logs 可以默认为 128 不变。

3. MySQL 5.7 时代的 undo log

那么问题又来了，undo log 单独拆出来后就能缩小了吗？MySQL 5.7 引入了新的参数，innodb_undo_log_truncate，开启后可在线收缩拆分出来的 undo 表空间。在满足以下 2 个条件下，undo 表空间文件可在线收缩：

（1）innodb_undo_tablespaces>=2。因为 truncate undo 表空间时，该文件处于 inactive 状态，如果只有 1 个 undo 表空间，那么整个系统在此过程中将处于不可用状态。为了尽可能降低 truncate 对系统的影响，建议将该参数最少设置为 3；

（2）innodb_undo_logs>=35（默认 128）。因为在 MySQL 5.7 中，第一个 undo log 永远在系统表空间中，另外 32 个 undo log 分配给了临时表空间，即 ibtmp1，至少还有 2 个 undo log 才能保证 2 个 undo 表空间中每个里面至少有 1 个 undo log；

满足以上 2 个条件后，把 innodb_undo_log_truncate 设置为 ON 即可开启 undo 表空间的自动 truncate，这还跟如下 2 个参数有关：

（1）innodb_max_undo_log_size，undo 表空间文件超过此值即标记为可收缩，默认 1G，可在线修改；

（2）innodb_purge_rseg_truncate_frequency, 指定 purge 操作被唤起多少次之后才释放 rollback segments。当 undo 表空间里面的 rollback segments 被释放时，undo 表空间才会被 truncate。由此可见，该参数越小，undo 表空间被尝试 truncate 的频率越高。

4. MySQL 5.7 的 undo 表空间的 truncate 示例

（1）首先确保如下参数被正确设置：

# 为了实验方便，我们减小该值
innodb_max_undo_log_size = 100M
innodb_undo_log_truncate = ON
innodb_undo_logs = 128 
innodb_undo_tablespaces = 3
# 为了实验方便，我们减小该值
innodb_purge_rseg_truncate_frequency = 10

（2）创建表：
mysql> create table t1(id int primary key auto_increment, name varchar(200));
Query OK, 0 rows affected (0.13 sec)

（3）插入测试数据
mysql> insert into t1(name) values(repeat(‘a’,200));
mysql> insert into t1(name) select name from t1;
mysql> insert into t1(name) select name from t1;
mysql> insert into t1(name) select name from t1;
mysql> insert into t1(name) select name from t1;

这时 undo 表空间文件大小如下，可以看到有一个 undo 文件已经超过了 100M：

-rw-r—– 1 mysql mysql  13M Feb 17 17:59 undo001
-rw-r—– 1 mysql mysql 128M Feb 17 17:59 undo002
-rw-r—– 1 mysql mysql  64M Feb 17 17:59 undo003
此时，为了，让 purge 线程运行，可以运行几个 delete 语句：

mysql> delete from t1 limit 1;
mysql> delete from t1 limit 1;
mysql> delete from t1 limit 1;
mysql> delete from t1 limit 1;

再查看 undo 文件大小：

-rw-r—– 1 mysql mysql  13M Feb 17 18:05 undo001
-rw-r—– 1 mysql mysql  10M Feb 17 18:05 undo002
-rw-r—– 1 mysql mysql  64M Feb 17 18:05 undo003
可以看到，超过 100M 的 undo 文件已经收缩到 10M 了。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-05/143400.htm