MySQL InnoDB表的碎片量化和整理（data free能否用来衡量碎片？）
看到一篇关于 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）做一个简单的分析，来说明如何量化表的碎片化程度。
一、涉及的参数
- information_schema_stats_expiryinformation_schema 是一个基于共享表空间的虚拟数据库，存储的是一些系统元数据信息，某些系统表的数据并不是实时更新的，具体更新是基于参数 information_schema_stats_expiry。information_schema_stats_expiry 默认值是86400 秒，也就是 24 小时，意味着 24 小时刷新一次 information_schema 中的数据，做测试的时候可以设置为 0，实时刷新 information_schema中的元数据信息。
- innodb_fast_shutdown，因为要基于磁盘做一些统计，需要将缓存或者 redo log 中的数据在重启实例的时候实时刷入磁盘，这里设置为 0，在重启数据库的时候将缓存或者 redo log 实时写入表的物理文件。
- innodb_stats_persistent_sample_pages 因为涉及一些系统数据更新时对 page 的采样比例，这里设置为一个较大的值，为 100000，尽可能高比例采样来生成系统数据。
- innodb_flush_log_at_trx_commit，sync_binlog，因为涉及大量数据的写操作，为加快测试，关闭 double 1模式。
- 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);
