MySQL 碎片就是 MySQL 数据文件中一些不连续的空白空间，这些空间无法再被全部利用，久而久之越来多，越来越零碎，从而造成物理存储和逻辑存储的位置顺序不一致。这些碎片的产生既浪费了空间，同时也因为数据从连续规则的存储方式变为随机分散的存储方式，磁盘 IO 会变的繁忙，数据库读写性能也随之下降。

碎片化的一个症状是表占用的空间超过了它“应该”占用的空间。究竟是多少，很难确定。所有InnoDB数据和索引都存储在B-trees中，它们的填充因子可能在 50% 到 100% 之间变化。
碎片的另一个症状是像这样的表扫描花费的时间比它 “应该”花费的时间要长。

1、MySQL 碎片产生的原因

对二级索引的随机插入或删除可能会导致索引变得碎片化。碎片化意味着磁盘上索引页的物理顺序与页上记录的索引顺序不接近，或者在分配给索引的 64 页块中有许多未使用的页。

1）在 MySQL 中删除数据，在存储中就会产生空白的空间，如果短时间内有大量的 delete 操作，有可能会使这种留空的空间变得比存储列表内容所使用的空间更大；

2）当有新数据插入时，MySQL 会试着在这些空白空间中保存新数据，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

3）在 MySQL 中更新数据，在可变长度的字段（比如 varchar、TEXT、BLOB）中更新数据，update 操作会造成页分裂（innodb 表存储数据的单位是页），分裂以后存储变的不连续不规则，从而产生碎片。

比如说原始字段长度 varchar(100)，我们大量的更新数据长度位为 50，这样的话，有 50 的空间被空白了，新入库的数据不能完全利用剩余的 50，这就会产生碎片。

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

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

1a9f58d385b4eaee52e220322168ff77

表的增删改操作，可能会造成数据碎片的，当对表进行大量的增删改操作后，数据碎片存在的可能性比较大。

以下是MySQL删除数据的几种方式以及是否释放磁盘空间:

drop ，truncate 立刻释放磁盘空间，不管是 Innodb和MyISAM ;

truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;

delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间（应该是做了特别处理，也比较合理）,InnoDB 不会释放磁盘空间;

对于 delete from table_name where xxx; 带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间;

delete操作以后使用optimize table table_name 会立刻释放磁盘空间。不管是innodb还是myisam 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。

delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

2、MySQL 碎片的影响

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

2.1 空间浪费

空间浪费不用多说，碎片占用了大量可用空间。不仅额外增加了存储代价，同时也因为数据碎片化降低了表的扫描效率。

2.2 读写性能下降

由于存在大量碎片，数据从连续规则的存储方式变为随机分散的存储方式，磁盘 IO 会变的繁忙，数据库读写性能就会下降。

3、MySQL 碎片产生实例

3.1 MySQL 碎片产生步骤

步骤1：创建表并插入测试数据

使用如下sql语句在MySQL自带的TEST数据库中创建名为DEMO的数据表并插入5条测试数据。

--创建DEMO表
CREATE TABLE DEMO(
id int unsigned,
body text
) engine=myisam charset=utf8;
                    
    
--插入5条测试数据
INSERT INTO DEMO VALUES(1,'AAAAA');
INSERT INTO DEMO VALUES(2,'BBBBB');
INSERT INTO DEMO VALUES(3,'CCCCC');
INSERT INTO DEMO VALUES(4,'DDDDD');
INSERT INTO DEMO VALUES(5,'EEEEE');

步骤2：创建表并插入测试数据

我们以这5条测试数据为基础，使用如下INSERT INTO语句重复执行多次进行复制性插入，产生总共约262万条数据。

INSERT INTO DEMO SELECT id, body FROM DEMO;

步骤3：查看数据文件大小

在MySQL中MyISAM表的数据是以文件形式存储的，我们在MySQL存储数据的文件夹中找到数据库test目录下的demo.MYD文件，可以看到demo.MYD文件的大小约为50MB。

步骤4：删除部分数据

假设我们需要删除DEMO表中所有ID列小于3的数据(即1和2)，执行如下SQL语句：

DELETE FROM DEMO WHERE id < 3

再次查询DEMO表，可以看到数据量只有原来的3/5，约为157万条记录。

步骤5：再次查看文件大小

DEMO表中的现有数据量只有原来的3/5，按理说，这个时候demo.MYD文件的大小也应该只有原来的3/5左右。不过，我们再次查看demo.MYD文件时，却惊奇地发现该文件的大小一点都没有变！

3.2 删除数据后数据文件未减小原因

删除数据后，数据文件 demo.MYD 的文件大小没有变化，这究竟是怎么一回事呢？

原来，在MySQL中，如果我们删除了表中的大量数据，或者我们对含有可变长度文本数据类型(VARCHAR，TEXT或BLOB)的表进行了很多更改后，被删除的数据记录仍然被保持在MySQL的链接清单中，因此数据存储文件的大小并不会随着数据的删除而减小。

当我们确定数据需要被清除掉时，那么这些数据就已经成了无用的数据，但是按照MySQL的处理方式，这些数据仍然会占用我们的磁盘空间，从而造成了极大的资源浪费。不仅如此，过大的数据文件还会导致MySQL执行相关数据操作时需要耗费更多的性能和时间。因此，对MySQL的某些数据表进行碎片整理是非常有必要的。

4、MySQL 碎片的查看

MySQL数据库中的表在进行了多次delete、update和insert后，表空间会出现碎片。定期进行表空间整理，消除碎片可以提高访问表空间的性能，降低无用数据所占据的磁盘空间，提高磁盘使用率。可以通过多种方式来查看表的碎片情况。以下结果中的 data_free 列数据就是我们要查询的表的碎片大小。

4.1 通过表状态信息查看

show table status like '%table_name%';

通过该脚本可以查看到包含 table_name 名称（模糊查询）的所有表的状态信息，其中有以下几个需要关注的字段：

Data_Length 表数据大小
Index_Length 表索引大小
Data_Free 碎片大小

4.2 通过数据库视图信息查看指定表的碎片情况

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 data_free
from information_schema.tables t
where t.table_name = 'table_name';

通过该脚本可以查看到 table_name 表的碎片信息，这里使用了精确查找，也可以使用模糊查找。

4.3 存在碎片的表查询

select concat('optimize table ',table_schema,'.',table_name,';'), 
     data_free, engine 
from information_schema.tables 
where data_free>0 and engine !='MEMORY';

通过该脚本可以查看到所有存在碎片的表的信息。

4.4 找到碎片化最严重的表

SELECT table_schema, TABLE_NAME, 
     concat(data_free/1024/1024, 'M') as data_free
FROM `information_schema`.tables
WHERE data_free > 3 * 1024 * 1024
	AND ENGINE = 'innodb'
ORDER BY data_free DESC

通过该脚本可以查看到碎片空间大于指定值的表的信息，并按碎片空间从大到小排序。

5、MySQL 碎片的清除

清除表碎片，对于不同的存储引擎整理碎片的方式不一样。

MySQL 8.0 参考手册-对表进行碎片整理

5.1 非InnoDB存储引擎适用方式

OPTIMIZE TABLE适用于 MyISAM 和 ARCHIVE 表。OPTIMIZE TABLE语句可以重新组织表、索引的物理存储，减少存储空间，提高访问的I/O效率。类似于碎片整理功能。OPTIMIZE TABLE也支持内存中NDB表的动态列。它不适用于内存中表的固定宽度列，也不适用于磁盘数据表。

OPTIMIZE TABLE的作用是整理数据文件，释放表空间，重组表数据和索引的物理页，减少表所占空间和优化读写性能。
OPTIMIZE TABLE会锁表，时间长短依据表数据量的大小。

optimize table table_name;

在执行optimize后，Data_free就会变为0，表示碎片数据被清除。还可以用以下方式，效果和optimize一样

./bin/mysqlcheck -uroot -proot --socket=./tmp/mysql.sock  -o test table_name

Engine不同，OPTIMIZE 的操作也不一样的，MyISAM 因为索引和数据是分开的，所以 OPTIMIZE 可以整理数据文件，并重排索引。

OPTIMIZE 操作会暂时锁住表，而且数据量越大，耗费的时间也越长，它毕竟不是简单查询操作。所以把 Optimize 命令放在程序中是不妥当的，不管设置的命中率多低，当访问量增大的时候，整体命中率也会上升，这样肯定会对程序的运行效率造成很大影响。比较好的方式就是做个shell，定期检查mysql中 information_schema.TABLES字段,查看 DATA_FREE 字段,大于0话,就表示有碎片

5.2 InnoDB存储引擎适用方式

对于InnoDB使用optimize和mysqlcheck都不起作用，对于小表的话直接用alter table方式回收表空间。对于大表就不能直接采用这种方式，因为会造成长时间的锁表。可以采用新建表转移数据，然后删除旧表的形式，然后再重命名表。

下述alter table语句的作用是重建表的存储引擎，重组数据和索引的存储。
下述alter table语句其实是一个NULL操作，表面上看什么也不做，实际上重新整理碎片了，当执行优化操作时，实际执行的是一个空的 ALTER 命令，但是这个命令也会起到优化的作用，它会重建整个表，删掉未使用的空白空间。

alter table table_name engine = innodb;

您还可以使用执行“null”更改操作来重建表。在InnoDB表中以下语句和上述语句是等价的。

ALTER TABLE table_name FORCE

5.3 alter table、analyze table和optimize table区别

alter table tb_test engine = innodb；

也就是 recreate，MySQL 5.5以前用Offline的方式重建表，5.6以后用Online的方式重建表；

analyze table tb_test；

重新统计表的索引信息，不会修改数据，不会重建表，整个过程加MDL读

optimize table tb_test；

是 alter table xxx = innodb； + analyze table xxx; 的过程。

6、实战建议

MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可(我们现在是每月凌晨2点清理mysql所有实例下的表碎片)

清除碎片操作会暂时锁表，数据量越大，耗费的时间越长，可以做个脚本，定期在访问低谷时间执行，例如每周三凌晨，检查DATA_FREE字段，大于自己认为的警戒值的话，就清理一次。
OPTIMIZE TABLE 只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含varchar、text、blob、float等可变长度的文本数据类型的表进行整理即可。

目录CONTENT

MySQL表空间碎片的产生影响及解决办法