MySQL5.7怎么在线收缩undo表空间

这篇文章主要讲解了“MySQL5.7怎么在线收缩undo表空间”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL5.7怎么在线收缩undo表空间”吧！

成都创新互联自成立以来，一直致力于为企业提供从网站策划、网站设计、网站建设、成都做网站、电子商务、网站推广、网站优化到为企业提供个性化软件开发等基于互联网的全面整合营销服务。公司拥有丰富的网站建设和互联网应用系统开发管理经验、成熟的应用系统解决方案、优秀的网站开发工程师团队及专业的网站设计师团队。

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));
Query OK, 
1
 row affected (0.01
 sec)
mysql> insert 
into
 t1(name) 
select
 name from t1;
Query OK, 
1
 row affected (0.00
 sec)
Records: 
1
  Duplicates: 

  Warnings: 


mysql> insert 
into
 t1(name) 
select
 name from t1;
Query OK, 
2
 rows
 affected (0.01
 sec)
Records: 
2
  Duplicates: 

  Warnings: 


mysql> insert 
into
 t1(name) 
select
 name from t1;
Query OK, 
4
 rows
 affected (0.00
 sec)
Records: 
4
  Duplicates: 

  Warnings: 


...

mysql> insert 
into
 t1(name) 
select
 name from t1;
Query OK, 
8388608
 rows
 affected (2
 min
 11.31
 sec)
Records: 
8388608
  Duplicates: 

  Warnings: 

这时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;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t1 limit 1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t1 limit 1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from t1 limit 1;
Query OK, 1 row affected (0.00 sec)

再查看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了。

感谢各位的阅读，以上就是“MySQL5.7怎么在线收缩undo表空间”的内容了，经过本文的学习后，相信大家对MySQL5.7怎么在线收缩undo表空间这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！