mysql表空间回收

回收方法

    1. alter table tb_test engine=innodb;
    2. optimize table tb_test；
    3. gh-ost/pt-osc
    

alter table tb_test engine=innodb 原理介绍

        MySQL5.6 开始采用 Inplace 方式重建表，Alter 期间，支持 DML 查询和更新操作，语句为 alter table t engine=innodb, ALGORITHM=inplace;之所以支持 DML 更新操作，是因为数据拷贝期间会将 DML 更新操作记录到 Row log 中。

        重建过程中最耗时的就是拷贝数据的过程，这个过程中支持 DML 查询和更新操作，对于整个 DDL 来说，锁时间很短，就可以近似认为是 Online DDL。

        执行过程：

        1、获取 MDL（Meta Data Lock）写锁，innodb 内部创建与原表结构相同的临时文件

        2、拷贝数据之前，MDL 写锁退化成 MDL 读锁，支持 DML 更新操作

        3、根据主键递增顺序，将一行一行的数据读出并写入到临时文件，直至全部写入完成。并且，会将拷贝期间的 DML 更新操作记录到 Row log 中

        4、上锁，再将 Row log 中的数据应用到临时文件

        5、互换原表和临时表表名

        6、删除临时表

alter table、analyze table和optimize table区别

        alter table tb_test engine = innode；（也就是 recreate）就是 Online DDL 重建表过程；

        analyze table tb_test ； 不是重建表过程，它只是对索引信息重新统计，会上 MDL 读锁；

        optimize table tb_test ；是 alter table tb_test engine = innode； + analyze 过程。

OPTIMIZE TABLE 和ALTER TABLE xxxx ENGINE= INNODB哪个更好

        OPTIMIZE TABLE 还是ALTER TABLE xxxx ENGINE= INNODB基本上是一样的。但是在有些情况下，ALTER TABLE xxxx ENGINE= INNODB更好。例如old_alter_table系统变量没有启用等等。另外对于MyISAM类型表，使用ALTER TABLE xxxx ENGINE= INNODB是明显要优于OPTIMIZE TABLE这种方法的。

ALTER TABLE xxxx ENGINE= INNODB 表上的索引碎片会整理么

        ALTER TABLE ENGINE= INNODB,会重新整理在聚簇索引上的数据和索引。如果你想用实验验证，可以对比执行该命令前后index_length的大小。

        对于大表的重建，十分消耗 IO 和 CPU 资源。如果是线上服务，为了安全性考虑，建议使用 GitHub 开源的 gh-ost 来做。