1、碎片产生的原因

（1）表的存储会出现碎片化，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；

（2）当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

（3）当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；

eg: 一个表有1万行，每行10字节，会占用10万字节存储空间，执行删除操作，只留一行，实际内容只剩下10字节，但MySQL在读取时，仍看做是10万字节的表进行处理，所以，碎片越多，就会越来越影响查询性能。

2、 查看表碎片大小

2.1、 查看某个表的碎片大小

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

mysql> show table status like '表名'\G

mysql> show table status like 'fbi_follows_hot'\G
*************************** 1. row ***************************
           Name: fbi_follows_hot
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 30942788
 Avg_row_length: 1733
    Data_length: 53641969664
Max_data_length: 0
   Index_length: 134719356928
      Data_free: 2420113408
 Auto_increment: NULL
    Create_time: 2020-02-04 11:44:46
    Update_time: 2020-02-08 10:43:58
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: 跟进表
1 row in set (0.00 sec)

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

• 数据总大小 = Data_length + Index_length = 53641969664 + 134719356928 = 188361326592
• 实际表空间文件大小 = (rows x Avg_row_length) = 30942788 x 1733 = 53623851604
• 碎片大小 = ( 188361326592 – 53623851604) / 1024 / 1024 / 1024 = 125.48G

2.2 、列出所有产生碎片的表

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

mysql> select table_schema db, table_name, data_free, engine     
from information_schema.tables 
where table_schema not in ('information_schema', 'mysql', 'test')  and data_free > 0;

3、 整理碎片

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

alter table  fbi_follows_hot engine=InnoDB

3.2 使用optimize table命令，整理碎片

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

mysql>optimize table fbi_follows_hot;

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

[root@pcs-km-47 ~]# pt-online-schema-change --alter="ENGINE=innodb" D=temple,t=fbi_follows_hot --execute

4、 参考文献

MySQL表碎片整理