Innode表空间碎片优化

一：MySQL数据存储方式（Innodb）

1.数据表的框架结构：.frm 文件（数据库服务器层）

2.数据和索引存储（Innode引擎层）

1共享表空间：

ibdata：系统表空间文件，存储Innodb系统信息和用户数据库表数据和索引，所有表共用。

2独立表空间：

ibdata：数据库所有表信息，没有在这里面记录的表将会提示不存在。同时用来启动Innodb。

.ibd:存储每个表对应的数据库和索引。

通过分析Innodb数据的存储方式，发现确实Innodb不适用mysql hot copy。即直接备份数据库文件夹来备份数据库。

二：测试共享表空间碎片是否会重复利用

测试共享表空间删除数据后，新插入数据会使用表空间碎片

drop table

1．原始数据

2．删除T_LOCATION

3．导入原表数据

经过测试，将表drop 后，重新插入数据库，会使用原来的表空间。具体使用比例可能会跟表空间产生碎片大小有关。

delete 记录

1.原始T_LOCATION时，共享表空间为3.8G,为了实现删除1千万数据后有导会数据，重新建立T_LOCATION_dummy来备份1千万需要删除的数据。

2.删除T_LOCATION中1千万数据，共享表空间增长了0.4G（猜测mysql 的delete操作中有在表空间复制数据的操作）。

3.在T_LOCATION中再插入刚刚删除的那1千万记录，表空间没有增长，利用了表中的碎片空间。

总结：利用delete 删除表中的记录时，共享表空间不会减小反而增大。猜测mysql在删除数据时会对删除数据进行备份操作，这个暂时没有找到验证资料，后期补充。delete删除数据库产生的表空间碎片是可以重复利用的。利用比例和具体碎片大小以及插入数据大小有关。

drop partition

1.利用drop partition删除表分区后，共享表空间大小没有变化

总结：通过实验，利用drop partition删除表分区共享表空间大小没有改变。也就说删除表分区后表空间也不会释放，但是产生的碎片也同理可以被重复使用。

三：linux 文件系统和文件大小限制

1.查询相关资料显示，ext3 格式的文件系统blocks size为4k。单个文件大小限制为2T，文件系统大小限制为8T，同时使用可以使用 ulimit –f 查看用户文件大小权限限制。

2.查询相关资料，表空间限制4,000,000,000个数据库页，而每个数据库页大小为16K，所以理论上表空间最大可以达到64T。

linux 文件系统和文件大小限制：

http://www.xifenfei.com/2204.html

http://blog.163.com/mail_zcw/blog/static/4126077420113290121997/

mysql Innodb表空间限制:

http://blog.sina.com.cn/s/blog_91339bff0100v2xq.html

http://blog.csdn.net/duck_arrow/article/details/8255377

四：共享表空间碎片回收

目前没有找到好的回收共享表空间碎片的方法，网络资料多处显示也没有好的办法回收。但是通过上面的实验可以看出，当我们删除部分数据后，共享表空间大小没有改变，再次恢复数据时，表空间大小也几乎没有改变。说明虽然共享表空间的碎片没有自动回收，但是却还是可以被重复利用，填充。利用率（50%-100%）可能和表空间碎片的大小以及分配方法有关（固定大小分配，最优大小分配等等），这和磁盘空间分配一样的道理。

有人在是官方使用手册中也有提到共享表空间没有好的回收方法：

http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html

参考资料-innodb表空间介绍：

http://www.docin.com/p-412074893.html

所以目前大多数方法都是把共享表空间转化成独立表空间。

1.共享表空间和独立表空间对比：

1共享表空间所有数据和索引存储在ibdata文件中，所有的表共享这个比表空间。而独立表空间则每一个表都有自己独立的表空间，分区表也会有自己独立的表空间，这样就方便对分区表的数据进行管理。(但是删除分区的话，不可以直接删除分区表对应的ibd文件，会导致整张表在ibdata文件中启动失败。删除分区用drop partition)

2因为独立表空间每一个表都会有自己的.ibd文件，所以通过复制表空间文件就可以使这个对应的表在同一个数据库实例中移动。

3独立表空间删掉数据对应的空间可以自动回收（drop table 操作除外，）delete, truncate等等

4独立表空间碎片可以手动回收，利用alter table tablename engine=innodb, optimiaze等等。

5当单个独立表空间过大时，如果查询表的范围过大，可能查询共享表空间的效率比独立表空间要高些。因为独立表空间需要一次访问多大打文件。

通过上面的比较，我们发现共享表空间增长速度要比独立表空间大。而且数据库达到亿级别的记录时，数据文件也很大不但不方便操作，也不方便数据的管理。对我们来说，是需要定期的备份删除一部分数据的（否则持续增长），所以需要定期优化表空间，而且独立表空间删除数据可以自动优化，处于上面种种对比所以我建议我们将数据库转化为独立表空间，时间越长，后期越难处理。

2.共享表空间转化独立表空间流程

1利用mysqldump 备份所有数据库

mysqldump –uuser –ppossword > dummy.sql

2修改my.cnf配置文件

innodb_file_per_table=1

3停止mysql服务器，删除ibdata,ib_logfile相关文件，以及业务数据库文件夹

4重启mysql数据库，自动生成Innodb相关文件

5利用备份恢复数据库

参考资料：

http://chqz1987.blog.163.com/blog/static/5143831120134269463429/

http://blog.fens.me/mysql-ibdata1/

3共享表空间负载

在my.cnf中设置innodb表空间的大小以及位置

innodb_data_file_path =/disk1/ibdata1:10G;/disk2/ibdata2:10G:autoextend:max:100G