看到一篇关于 MySQL InnoDB 表碎片的文章，觉得不错，转载如下，原文地址：

https://www.cnblogs.com/wy123/archive/2020/03/22/12535644.html

网络上有很多 MySQL 表碎片整理的问题，大多数是通过 demo 一个表然后参考 data free 来进行碎片整理，这种方式对 myisam 引擎或者其他引擎可能有效（本人没有做详细的测试），对 Innodb 引擎是不是准确的，或者 data free 是不是可以参考，还是值得商榷的。本文基于 MySQL 的 Innodb 存储引擎，数据库版本是 8.0.18，对碎片（fragment）做一个简单的分析，来说明如何量化表的碎片化程度。

一、涉及的参数

1. information_schema_stats_expiryinformation_schema 是一个基于共享表空间的虚拟数据库，存储的是一些系统元数据信息，某些系统表的数据并不是实时更新的，具体更新是基于参数 information_schema_stats_expiry。information_schema_stats_expiry 默认值是86400 秒，也就是 24 小时，意味着 24 小时刷新一次 information_schema 中的数据，做测试的时候可以设置为 0，实时刷新 information_schema中的元数据信息。
2. innodb_fast_shutdown，因为要基于磁盘做一些统计，需要将缓存或者 redo log 中的数据在重启实例的时候实时刷入磁盘，这里设置为 0，在重启数据库的时候将缓存或者 redo log 实时写入表的物理文件。
3. innodb_stats_persistent_sample_pages 因为涉及一些系统数据更新时对 page 的采样比例，这里设置为一个较大的值，为 100000，尽可能高比例采样来生成系统数据。
4. innodb_flush_log_at_trx_commit，sync_binlog，因为涉及大量数据的写操作，为加快测试，关闭 double 1模式。
5. innodb_fill_factor 页面填充率保留默认的设置，默认值是 100 以上涉及的参数仅针对本测试，并不一定代表最优，同时测试过程中（数据写入或者删除后）会不断地重启实例，以刷新相对应的物理文件。

二、碎片的概念

数据存储在文件系统上的时候，总是不能 100% 利用分配给它的物理空间，比如删除数据会在页面上留下一些“空洞”，或者随机写入（聚集索引非线性增加）会导致页分裂，页分裂导致页面的利用空间少于 50%。另外对表进行增删改会引起对应的二级索引值的随机的增删改，也会导致索引结构中的数据页面上留下一些“空洞”。虽然这些空洞有可能会被重复利用，但终究会导致部分物理空间未被使用，也就是碎片。同时，即便是设置了填充因子为 100%，Innodb 也会主动留下 page 页面 1/16 的空间作为预留使用。

（An innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth.）。

关系数据库的存储结构原理上是类似的，理论上很简单，就不过多啰嗦了，碎片是一个客观存在的事实。

三、创建测试表以及数据

做个简单的测试，表结构如下：

CREATE TABLE `fragment_test` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `c1` INT NULL DEFAULT NULL,
    `c2` INT NULL DEFAULT NULL,
    `c3` VARCHAR(50) NULL DEFAULT NULL,
    `c4` DATETIME(6) NULL DEFAULT NULL,
    PRIMARY KEY (`id`) 
);

CREATE INDEX idx_c1 ON fragment_test(c1);
CREATE INDEX idx_c2 ON fragment_test(c2);
CREATE INDEX idx_c3 ON fragment_test(c3);

生成200W测试数据（CALL test_insertdata(2000000);）

CREATE DEFINER=`root`@`%` PROCEDURE `test_insertdata`(
    IN `loopcount` INT
)
BEGIN
  declare v_uuid  varchar(50);
    while loopcount>0 do
        set v_uuid = uuid();
        INSERT INTO fragment_test(c1,c2,c3,c4) VALUES (RAND()*200000000,RAND()*200000000,UUID(),NOW(6));
        set loopcount = loopcount -1;
    end while;
END

查询语句，参考自最后的链接中的文章。

SELECT NAME, 
        TABLE_ROWS,
        UPDATE_TIME, 
            format_bytes(data_length) DATA_SIZE,
       format_bytes(index_length) INDEX_SIZE,
       format_bytes(data_length+index_length) TOTAL_SIZE,
       format_bytes(data_free) DATA_FREE,
       format_bytes(FILE_SIZE) FILE_SIZE,
       format_bytes((FILE_SIZE/10 - (data_length/10 + 
                           index_length/10))*10) WASTED_SIZE  
FROM information_schema.TABLES as t 
JOIN information_schema.INNODB_TABLESPACES as it 
  ON it.name = concat(table_schema,"/",table_name) 
WHERE TABLE_NAME = 'fragment_test';

四、碎片的量化

上面说到数据在存储的时候，总是无法 100% 利用物理存储空间，Innodb 甚至会自己主动预留一部分空闲的空间（1/16），那么如何衡量一个表究竟有多少尚未利用的空间？

这里从系统表 information_schema.tables 和 information_schema.innodb_tablespaces 来对比实际使用空间和已分配空间来对比，来间接量化碎片或者说未利用空间的程度。然后观察数据空间的分配情况，尽管系统表中的数据不是完全准确的，但是也比较接近实际的 200W，系统表显示1971490，暂时抛开这一小点误差。可以很清楚地看到，数据和索引的空间是 329MB，文件空间是 344MB，DATA_FREE 空间是 6MB。

随机删除 1/4 的数据，也就是 50W 行（DELETE FROM fragment_test ORDER BY RAND() LIMIT 500000;）然后重启实例，并执行分析表（analyze table），继续来观察这个空间的分配，这里看到：

1. 系统表显示 150000 行，跟表中的数据完全一致（尽管更多的时候这个值是一个大概的值，并不一定准确，严格说可能非常不准确，这里归因于 innodb_stats_persistent_sample_pages的 设置）。
2. 数据文件空间没有增加（344MB），可以理解，因为这里是删数据操作，所以不用申请空间。
3. 删除了 1/4 的数据，数据和索引的的大小基本上不变，这里就开始有疑问了，为什么没有成比例减少？
4. data_free 增加了 3 MB，显然这不是跟删除的数据成比例增加的那么怎么理解碎片？DATA_FREE 怎么理解？碎片或者说可用空间又怎么衡量？

从 200W 数据中随机删除 50W，也就是 1/4，表的空间没有变化，可以肯定的是现在存在大量的碎片或者说可用空间，但是表的总的大小没变化，data_free 也基本上没有变化到这里就有点说不通了。

那么 data free 到底是怎么计算的，看官方的解释：

The number of allocated but unused bytes.
InnoDB tables report the free space of the tablespace to which the table belongs. For a table located in the shared tablespace, this is the free space of the shared tablespace.
If you are using multiple tablespaces and the table has its own tablespace, the free space is for only that table.
Free space means the number of bytes in completely free extents minus a safety margin. Even if free space displays as 0, it may be possible to insert rows as long as new extents need not be allocated.

data_free 的计算方式或者说条件，是完全空闲的区（extents，每个区 1 MB，64 个连续的 16 kb 大小的 page），只有一个完全没有使用的区，才统计为 data_free，因此 data_free 并不能反映出来真正的空闲空间。

同时测试中发现，performance_schema.tables 中的 table_rows 会受到 innodb_stats_persistent_sample_pages 的影响，但是 data_length 和 index_length 看起来是不会受 innodb_stats_persistent_sample_pages的影响的。这里采样比例已经足够大，尽管 table_rows 已经是一个完全准确的数字了，但是 data_length 和 index_length 却仍旧是一个误差非常大的数字。

说到这里，那么这个碎片问题如何衡量？如果只是看 performance_schema.tables 或者 information_schema.INNODB_TABLESPACES，其实依旧是一个无解的问题，因为无法通过这些信息，得到一个相对准确的碎片化程度。

其实在这里（参考链接）的评论中也提到这个问题，我是比较赞同的。

The 'data_free' only shows free extents (contiguous 1MB of pages). If you have other fragmentation of partially full extents, it won’t be reported by 'data_free'. You show this in your example of deleting random records.

But even the “crumbs” of free space can add up to a significant amount of free space that can be recovered by a table rebuild. In one case at my job, a table shrank by 75% after a table rebuild, even though 'data_free' didn’t show more than 2% could be recovered.

I don’t know any way to report this type of fragmentation.

如果要真正得到碎片程度，其实还是需要重建表来对比实现，这里删除了 1/4 的数据，理论上就有大概 1/4 的可用空间，但是上面的查询结果并不能给出一个明确的答案，怎么验证这个答案呢？这里就要粗暴地优化表了（optimize table fragment_test+analyze table），优化表只是“重整”了碎片，但是系统表的数据并没有更新，因此必须要再执行一次分析表 analyze table 来更新元数据信息其实这里也能说明，analyze table 只是更新元数据，如果存储空间没有更新（recreated），单纯地 analyze table 也是没有用的。对标进行 optimize 和 anlayze 之后，这里可以看到，物理空间确实减少了大概 1/4 的量。

这里其实就是为了说明一个问题：Innodb 表无法通过 data free 来判断表的碎片化程度。然而这里（参考链接）的测试说明删除数据后 data free 有明显的变化，这个又是为什么，刚特么说无法通过 data free 来判断表的碎片化程度，现在又说删除数据后 data free 有明显的变化？？？

其实（参考链接）中有另外一个比较有意思的测试，相对用随机删除的方式，采用连续删除的时候（或者是整个表的数据全部删除），这个 data free 确实会相对准确地体现出来删除数据后表 size 的变化情况。

这又是为什么？其实不难理解，上面已经说了，data free 的计算方式，是按照完全“干净”的区（extent）来做统计的，如果按照聚集索引连续的方式删除（相对随机删除），那些存储连续数据的区（extent）是可以完全释放出来的，这些区的空间释放出来之后，会被认为是 data free，所以 data free 此时又是相对来说准确的。因此，很多测试，如果想到得到客观的数据，需要尽可能多地考虑到对应的场景和测试数据情况。

碎片的衡量：
实际业务中，对表的删除或者增删改，很少是按照聚集索引进行批量删除，或者说一旦存在随机性的删除或者更新（页分裂），都会造成一定程度的碎片，而这个碎片化的程度是无法通过 data free 来衡量的。那么又如何衡量这个碎片程度呢？

1. 自己根据业务进行预估，在可接受程度内进行 optimize table，记录 optimize table 之后的 table size 变化程度，来衡量一个表在一定时间操作后的碎片化程度，从而来指导是否，或者多久对该表再次进行 optimize table
2. 采用上述连接中提到的innodb_ruby 这个工具，直接解析表的物理文件，这种方式相对来说更加直接。不过这个工具本人没来得及测试，理论上是没有问题的。这里盗用上述链接中的图片，绿色的是实际使用的空间，中间的黑块就是所谓的碎片或者说是空洞。

补充：早上起来，又想到了另外一种 case，就是说随机删除后，剩余空间中出现了“空洞”，这些空洞在写数据的时候，会不会被再次利用？验证其实很简单，写入 200W 数据，随机删除 50W 后，analyze table 更新 performance_schema，然后继续再写入 50W 行的数据，如果会利用之前随机删除的空洞空间，那么就不会重新分配物理空间，否则就会重新分配物理空间。因为聚集索引的 id 是自增的，相当于顺序写入，理论上是不会重用之前删除留下的空洞的，测试的结果还是在预期之内的，重新写入 50W 数据后，表对应的物理文件会有一个很明显的增加。

参考链接：

https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html

https://dev.mysql.com/doc/refman/8.0/en/tables-table.html

https://lefred.be/content/overview-of-fragmented-mysql-innodb-tables/

https://lefred.be/content/mysql-innodb-disk-space/

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_fill_factor