MySQL问答系列如何避免ibdata1文件大小暴涨
什么是ibdata1文件？
Ibdata1是用于构建innodb系统表空间的文件，包含innodb表的元数据、记录取消、修改缓冲区和双写缓冲区。如果打开了“每个表一个文件”选项，文件可能不包含所有表的数据。当innodb_file_per_table选项打开时，新创建的表的数据和索引将不存在于system表空间中，而是存在于。各自表的ibd文件。
显然这个文件会越来越大，innodb_autoextend_increment选项指定了这个文件每次自动增长的步长，默认为8M。
是什么原因导致ibdata1的文件越来越大？
Ibdata1存储数据、索引和缓存，是MYSQL最重要的数据。所以随着数据库越来越大，表也会变大，这是不可避免的。如果时间长了，越来越大，我们处理日志和空间就不那么方便了，不知道从何下手。接下来，我们必须处理这种情况，并将数据存储在不同的库中。
InnoDB的* * *共享表空间文件ibdata1文件大小急剧增大怎么办？
1，问题背景
用MySQL/InnoDB的童鞋可能也会有烦恼。不知道为什么，ibdata1的文件莫名其妙的长，也不知道怎么缩回去，就像男人30岁后的肚子。汗，肚子还没长出来真让人欣慰，hoho~
在正式开始之前，我们需要知道ibdata1文件是干什么用的。
Ibdata1文件是InnoDB存储引擎的* * *表空间文件，主要存储以下数据:
数据字典
双写缓冲器
插入缓冲液/更换缓冲液
回滚段
撤消空格
外键约束系统表
此外，当选项InnoDB_file_per_table = 0时，innodb table &需要存储在ibdata1文件中。索引。ibdata1文件从版本5.6.7开始，默认大小为12MB。在此之前，默认大小为10MB，其相关选项为innodb_data_file_path。比如我一般是这样设置的:
innodb _ data _ file _ path = IB data 1:1G:自动扩展
当然，无论innodb_file_per_table = 1是否启用，ibdata1文件都是必须存在的，因为它必须存储上述innodb引擎&必要的数据，尤其是上面粗体标注的回滚段和撤消空间，是ibdata1文件大小增加的最大原因，下面我们会详细讨论。
2.原因分析
我们知道，InnoDB支持类似ORACLE的MVCC，采用撤销日志和重做日志实现MVCC特性。当修改事务中的一行数据时，InnoDB会在撤销日志中存储该行数据的旧版本。如果此时另一个事务想要修改这一行数据，它会在撤销日志中存储这个东西的最新可见数据版本，以此类推。如果有n个事务来修改这些数据，就需要存储n份版本历史(和ORACLE略有不同，InnoDB的撤销日志不完全是物理块，主要是逻辑日志，可以查看InnoDB的源代码或者其他相关资料)。这些撤销日志需要等待事务结束，根据事务隔离级别确定的其他事务的可见性再次判断，确认这些撤销日志是否可以删除。这个工作叫做purge(purge工作不仅仅是删除过期的撤销日志，还有其他的，后面会讲到)。
那么问题来了。如果有一个版本历史需要在一个事务中读取大量的数据，但是这个事务今天早上因为一些原因无法提交或者回滚，并且在事务发起后有大量的事务需要修改这些数据，那么这些新事务产生的撤销日志就永远无法删除，形成一堆，这也是ibdata1文件大小增加的主要原因之一。这种情况最经典的场景就是大量的数据备份，所以我们建议将备份工作放在专用的从服务器上，而不是放在主服务器上。
另一方面，InnoDB的清除工作由于文件i/o性能不佳或其他原因，一直无法及时清除已删除的撤销日志，这也是导致ibdata1文件大小增加的另一个主要原因。当服务器的硬件配置薄弱，没有及时升级以跟上业务发展时，就会出现这种情况。
很少见的一个是早期运行在32位系统上的MySQL版本出现了bug。当发现要清除的撤销日志总量超过一定值时，清除线程直接放弃抵抗，不再清除。这个问题是我们在早期使用32位MySQL版本时遇到的，曾经遇到过这个文件涨到100 g以上的情况，后来我们花了很大的力气把这些例子全部迁移到64位系统上，终于解决了这个问题。
最后，选项innodb_data_file_path的值一开始没有调整或者设置的很小，这就不可避免的导致了ibdata1文件的增加。Percona提供的my.cnf参考文档从来没有增加过这个值，这让我很疑惑。是不是故意留一个我经常吐槽的xx那样的暗门，以便后期帮助客户优化？(脑子太黑了，这样不好~ ~)
简单总结一下，ibdata1文件大小暴涨有几个原因:
有大量的并发事务，产生大量的撤销日志；；
存在长期未提交的旧事务，产生大量旧撤销日志；；
文件i/o性能差，清除进度慢；
初始化设置太小；
32位系统有一个bug。
补充一点题外话，另一个比较流行的数据库PostgreSQL，是把版本历史的数据和原始数据表空间一起存储的，所以这种情况是没有问题的，也因此PostgreSQL的事务回滚会很快，需要定期做真空工作(详见PostgreSQL的MVCC实现机制，我可能不完全正确)。
3.关于解决方案的建议
看到上面对这些问题产生原因的描述，有些同学可能会觉得这个好办。缩小ibdata1的文件大小不会结束表空间。可悲的是，到目前为止，InnoDB还没有办法恢复/收缩ibdata1文件的表空间。一旦ibdata1文件的肚子被扩大，我们只能通过先备份数据，再重新初始化实例，或者依次将独立的表空间文件恢复到新的实例，来恢复原来的大小。除此之外，没有更好的办法。
当然，这个问题也不是防不胜防。根据上述原因，相应的建议和对策是:
升级到5.6及以上(64位)并采用独立的撤销表空间。从5.6版本开始，已经支持独立的撤销表空间，不需要担心使ibdata1文件变大。
初始化设置时，将ibdata1文件设置为至少1GB。
增加清除线程的数量，比如设置innodb _ purge _ threads = 8；
为了提高文件i/o能力，应该快速安装SSD
及时提交交易，不要积压；
默认打开自动提交= 1，避免忘记某笔交易很久没有提交；
检查开发框架，确认是否设置了autocommit=0，记住事务结束后有一个显式的提交或回滚。
摘要