清理MySQL表碎片（Table Fragmentation）

使用背景

表的 DATA_FREE 碎片较多需要清理，也可以理解为减少表碎片。

以下采用倒叙的方法(先介绍解决办法，再介绍原理)，读者可以按照自己的时间或是喜好阅读。

操作方法

建议使用：ALTER TABLE xxxx ENGINE=InnoDB;
ANALYZE TABLE xxxx; 进行操作，这也是官方推荐的表碎片优化操作。

第三方工具

pt工具或者gh-ost（大数量的环境下工具更好）。

以下为原理和可能使用到的sql语句

在MySQL中，可以使用OPTIMIZE TABLE、ALTER TABLE XXXX ENGINE = INNODB这两种方法降低碎片
** 详细的原理请查看以下这个链接：**
link. https://www.cnblogs.com/kerrycode/p/10943122.html
一些重要摘录：

mysql 5.7 以上，或者是mysql8.0的，
optimize table 应该回报错：OPTIMIZE TABLE: 
Table does not support optimize, doing recreate + analyze instead。
（实际是执行了 recreate + analyze） 

对于innodb_file_per_table=1的InnoDB表，OPTIMIZE TABLE 
会重组表和索引的物理存储，将空闲空间释放给操作系统。
也就是说OPTIMIZE TABLE [tablename] 这种方式只适用于独立表空间。

sql 语句

查看某个表的碎片大小

SHOW TABLE STATUS LIKE '表名';   结果中’Data_free’列的值就是碎片大小

列出所有已经产生碎片的表

select TABLE_SCHEMA db,TABLE_NAME,DATA_FREE,ENGINE 
from information_schema.tables 
where TABLE_SCHEMA not in ('information_schema', 'mysql') and DATA_FREE > 0;

清除表碎片

MyISAM表：optimize table 表名

InnoDB表：alter table 表名 engine=InnoDB

注：清除碎片操作会暂时锁表，数据量越大，耗费的时间越长

清除碎片的优点

1. 降低访问表时的IO,提高mysql性能。
2. 释放表空间降低磁盘空间使用率。

碎片产生的原因

1. 删除了一行数据，该段空间就会变为被留空；
2. 执行插入操作时，MySQL会尝试使用空白空间，如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；
3. 频繁修改了含有可变长度列的表。（如：含有VARCHAR, BLOB或TEXT列的表）;

test库下所有表的碎片查看
方式一

SELECT * from 
(
    SELECT CONCAT(table_schema,'.',table_name) AS 'table_name', 
    table_rows AS 'Number of Rows', 
    CONCAT(ROUND(data_length/(1024*1024),6),' M') AS 'data_size', 
    CONCAT(ROUND(index_length/(1024*1024),6),' M') AS 'index_size' , 
    CONCAT(ROUND(data_free/(1024*1024),6),' M') AS'data_free',
    ENGINE as 'engine'
    FROM information_schema.TABLES 
    WHERE table_schema = 'test'   #库名
) t ORDER BY data_free DESC;

方式二

SELECT
table_schema as 'DATABASE',
table_name as 'TABLE',
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') 
'TOTAL',CONCAT(ROUND(data_free / ( 1024 * 1024 * 1024 ), 2), 'G') 'DATAFREE'
FROM information_schema.TABLES
where table_schema='库名' and table_name='表名';

默认情况下，服务器将OPTIMIZE TABLE语句写入二进制日志，以便它们复制到副本。要禁止记录日志，请指定可选 NO_WRITE_TO_BINLOG关键字或其别名 LOCAL。