mysql如何收缩表空间

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。