mysql如何收缩表空间

 

参数 innodb_file_per_table   

mysql5.6.6版本以后，默认设置为ON，表示innodb表数据存储在一个以.ibd为后缀的文件中  

不建议设置为OFF，原因是  设置为OFF后，会将表数据存储在系统共享表空间中，即使drop掉表，空间也不会被回收【磁盘空间不会减少，只会无脑增加】

 

数据删除、修改：

innodb数据都是用B+树结构

 

当R4的数据删除以后，mysql会在R4这个位置记录一个标记为删除，当插入300~600之间的数据值，R4这个位置会被复用

当page A 整个数据页被删除后，整个页就会被标记为可复用 

 

但是数据页的复用和记录的复用是不一样的：

记录的复用只限于： 只限于符合范围条件的数据

数据页的复用：当一个数据页写满后，需要使用新的数据页，就可以被复用；如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

 

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。所以，单靠delete 命令是不能回收表空间的

update可以理解为：删除一个旧的值，再插入一个新值

总结：经过大量的增删改，数据页上都是可能会存在空洞。

 

收缩表空间的方法：

重建表   建议使用命令：alter table a engine=InnoDB

mysql5.6版本之后引入了online DDL,重建表流程如下：

1.建立一个临时文件，扫描表 A 主键的所有数据页；

2.用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；

3.生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中        #mysql5.6之前还没有引入onine DDL机制，不会生成row log日志

4.临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件

5.用临时文件替换表 A 的数据文件。

重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。推荐使用 GitHub 开源的 gh-ost 。

 

optimize table、analyze table 和 alter table 这三种方式重建表的区别

从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate，上边的流程）

analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；

optimize table t 等于 recreate+analyze。