MySQL InnoDB表的碎片量化和整理（data free能否用来衡量碎片？）

秒速五厘米 2022-12-11 10:29 161阅读 0赞

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

**涉及的参数**  
1，information\_schema\_stats\_expiry  
information\_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`][innodb_fill_factor] setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth.）。  
关系数据库的存储结构原理上是类似的，理论上很简单，就不过多啰嗦了。碎片是一个客观存在的事实。  
  
**创建测试表以及数据**

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