概述

我们经常会使用VARCHAR、TEXT、BLOB等可变长度的文本数据类型。不过，当我们使用这些数据类型之后，就不得不做一些额外的工作——MySQL数据表碎片整理。

每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。

当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

1、计算碎片大小

要整理碎片，首先要了解碎片的计算方法。

可以通过show table [from|in db_name] status like ’%table_name%’命令查看：

mysql> show table from lcpdb1 status like ’lcp_mq_record’\G

碎片大小 = 数据总大小 - 实际表空间文件大小

• 数据总大小 = Data_length + Data_length = 66650112
• 实际表空间文件大小 = rows * Avg_row_length = 3867 * 17235 = 66647745
• 碎片大小 = (66650112-66647745) / 1024 = 2.3KB

通过information_schema.tables的DATA_FREE列查看表有没有碎片：

SELECT t.TABLE_SCHEMA,
 t.TABLE_NAME,
 t.TABLE_ROWS,
 t.DATA_LENGTH,
 t.INDEX_LENGTH,
 concat(round(t.DATA_FREE / 1024 / 1024, 2), ’M’) AS datafree
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = ’lcpdb1’ order by datafree desc;

2、整理碎片

2.1 使用alter table table_name engine = innodb命令进行整理。

mysql > alter table lcp_mq_record engine=innodb;
mysql> show table from lcpdb1 status like ’lcp_mq_record’\G

2.2 使用pt-online-schema-change工具也能进行在线整理表结构，收集碎片等操作。

后面会单独介绍一下pt这个工具，所以这里就不多做介绍了。

--需表上有主键或唯一索引才能运行
# pt-online-schema-change --alter="ENGINE=innodb" D=lcpdb1,t=lcp_mq_record --execute 

2.3 使用optimize table命令，整理碎片。

运行OPTIMIZE TABLE， InnoDB创建一个新的.ibd具有临时名称的文件，只使用存储的实际数据所需的空间。优化完成后，InnoDB删除旧.ibd文件并将其替换为新文件。如果先前的.ibd文件显着增长但实际数据仅占其大小的一部分，则运行OPTIMIZE TABLE可以回收未使用的空间。

mysql> show table status like ’t_ams_asset_card’ \G;
mysql> optimize table t_ams_asset_card;
mysql> show table status like ’t_ams_asset_card’ \G;

优化后可以看到DATA_FREE明显减少很多。

上面是针对单表去做碎片整理，那么我如果很多张表应该怎么处理呢？后面会整理一个shell脚本来对多个表做碎片整理，感兴趣的朋友可以关注一下~