【MySQL】ibdata文件增大的原因

背景
  早上和一个同事讨论技术问题，谈到ibdata文件会随着数据的使用而增大，而且在事务隔离级别RR 隔离级别下要比RC 隔离级别的大。本文尝试分析两个问题之一
 a ibdata 文件随着数据库的使用而增大。
 b RR 隔离级别下比RC 隔离级别下增长的快大。
ibdata1 存放哪些数据？
表数据/索引 (innodb_file_per_table=0 时)
data dictionary aka metadata of InnoDB tables
undo 表空间 回滚段相关记录
doublewrite buffer
change buffer
什么原因导致ibdata 迅速增大呢？
从ibdata1文件存放的内容来分析
我们都知道innodb的表有两种存放方式：
当innodb_file_per_table=0时也即共享表空间方式,所有表的索引/数据统一存放在一个共享表空间中ibdata1文件，随着数据量的增大,共享表空间的文件大小也迅速增长,同时空间回收困难；
当innodb_file_per_table=1时也即独占表空间方式,也就是一张表一个表空间(ibd文件)，表中的索引和数据存放在独立的表空间中,执行drop/truncate 操作可以快速的回收独立表空间。
对于buffer 可以分别使用参数 innodb_doublewrite_file ，innodb_ibuf_max_size控制大小，他们带来的空间增长可以忽略不计，最终 ibdata最终的大小取决于事务的大小/长短。
a 数据库系统中存在长时间未提交的事务,或者在同一个事务中更新/插入很多数据，导致innodb需要维护很大的undo segments来保障一致性读。
通过执行命令
SHOW ENGINE INNODB STATUSG
---TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id = 36F, sees 36F
例子中显示有一个长达 1256288秒(14年)的事务未提交。active 意味着innodb在undo segment里面创建数据的快照以便提供一致性读。如果数据库存在大量的长事务，就要耗费大量的undo segment。
b 5.5版本之前 purge undo thread是和master thread 共用的。存在大量长事务的时候导致purge undo的速度小于undo segment产生的速度。通过 innodb 参数 History list length


Trx id counter 43831607347
Purge done for trxs n:o 43831607342 undo n:o 0 state: running but idle
History list length 2308
LIST OF TRANSACTIONS FOR EACH SESSION:
History list length  2308 表示有 2308个事务没有清理，过大的值意味着purge thread 速度达到了瓶颈。5.5 版本开始MySQL 将purge thread 和master thread 分开，我们可以通过调整参数来加快purge undo的速度。
# yzsql 3311 param purge
Variable_name                Value
innodb_max_purge_lag          0
innodb_max_purge_lag_delay    0
innodb_purge_batch_size       300
innodb_purge_threads          12
如何查看ibdata文件中的内容呢？
MySQL 官方并没有提供工具查ibata存储了什么内容，不过我们可以通过如下两种工具
innochecksum，(感谢  Mark Callaghan)。
 ./innochecksum /var/lib/mysql/ibdata1

0     bad checksum
13    FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG --占用了总ibdata1 的93%
230   FIL_PAGE_INODE
1     FIL_PAGE_IBUF_FREE_LIST
892   FIL_PAGE_TYPE_ALLOCATED
2     FIL_PAGE_IBUF_BITMAP
195   FIL_PAGE_TYPE_SYS
1     FIL_PAGE_TYPE_TRX_SYS
1     FIL_PAGE_TYPE_FSP_HDR
1     FIL_PAGE_TYPE_XDES
0     FIL_PAGE_TYPE_BLOB
0     FIL_PAGE_TYPE_ZBLOB
0     other
3     max index_id
从上面的分析来看 undo log占用了总ibdata1 的93%。
第二个工具：innodb_space (  made by Jeremy Col)可以清晰地分析出ibdata1的组成（该工具需要bindata环境）
# innodb_space -f /var/lib/mysql/ibdata1 space-summary | grep UNDO_LOG | wc -l
19272
如何解决 ibdata1 不停的增大呢？ 
坦白的说我们没有方法阻止其不停的增大，但是我们可以使用
1 规范开发同学的数据库使用习惯，使用短小的事务替代大事务，并确保每个事务都有commit机制。
2 增加purge thread 数量，加快purge undo的速度尽快释放undo空间。
3 升级到5.6 版本 独立出undo 表空间来保持ibdata文件在一个合理的大小。
当然我们也没有优雅的办法上增大的ibdata文件缩小，这个文件只增加不减小。

[ MySQL ] 使用 MySQL Workbentch 进行MySQL数据库备份 / 还原（Part 3：备份.sql文件方式） 本文主要讲解如何用 MySQL Workbentch 进行MySQL备份和恢复数据库。 本文主要大纲为：使用Workbentch备份.sql文件的方式进行备份和还原，其中包括还原自身数据库和还原到其他目标库。
[ MySQL ] 使用Navicat进行MySQL数据库备份 / 还原（Part 2：备份.sql文件方式） 本文主要讲解如何用Navicat（Navicat Premium ，或者Navicat for mysql）进行MySQL备份和恢复数据库。 本文主要大纲为：使用Navicat备份.sql文件的方式进行备份和还原，其中包括还原自身数据库和还原到其他目标库。
[ MySQL ] 使用Navicat进行MySQL数据库备份 / 还原（Part 1：备份.nb3文件方式） 本文主要讲解如何用Navicat（Navicat Premium ，或者Navicat for mysql）进行MySQL备份和恢复数据库。 本文主要大纲为：使用Navicat备份工具方式进行备份和还原，其中包括还原自身数据库和还原到其他目标库。
MySQL导出sql脚本文件 sql脚本文件在我们做项目时，特别是学习别人的开源项目时经常需要进行导入导出操作，才能在自己的系统上跑起来，这篇文章主要介绍如何导出sql脚本文件，具体操作如下，附带截图详解。