mysql的INFORMATION_SCHEMA.TABLES有个DATA_free字段，那么它表示什么含义呢？
INFORMATION_SCHEMA.TABLES中的data_free字段
我们知道INFORMATION_SCHEMA.TABLES其实是个视图，它里面存储了数据表的元数据信息。
一起看下这个视图包含了哪些字段：
其中几个比较重要的字段，其含义如下：
table_schema: 数据库名； table_name: 数据表名； engine : 存储引擎； table_rows: 关于表的粗略行估计； data_length : 表的大小（单位字节）； index_length : 表的索引的大； row_format: 行格式；
那么其中data_free字段如何理解呢？
data_free在 MySQL 中是文件系统中未使用空间的值。换句话说，分配给表或分区但当前未被数据使用的空间量。它指示操作系统或 MySQL 服务器可能回收但尚未释放的空间量，也叫做数据碎片。
data_free值还可以通过SHOW TABLE STATUS
命令查看，单位是字节。
SHOW TABLE STATUS like 'operate_log'
data_free它可用于确定表或分区是否使用了比需要更多的磁盘空间，或者是否需要调整磁盘空间的分配。
为什么会有碎片产生？
常见原因：
（1）频繁数据删除
比如删除数据会在页面上留下一些”空洞”。
（2）随机写入 随机写入（聚集索引非线性增加）会导致页分裂，页分裂导致页面的利用空间少于50%；
另外对表进行增删改会引起对应的二级索引值的随机的增删改，也会导致索引结构中的数据页面上留下一些“空洞”；
虽然这些空洞有可能会被重复利用，但终究会导致部分物理空间未被使用，也就是碎片。
(3)innodb_fill_factor的影响
页面填充率保留默认的设置，默认值是100。但即便是设置了填充因子为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.）。
碎片有什么影响？
磁盘空间的使用: 碎片会占用磁盘空间，这样就会增加磁盘空间的使用。如果表中有很多的碎片，可能会占用较大的磁盘空间。
表的查询速度: MySQL查询表时需要扫描整个表的数据，如果存在碎片，会多次调用硬盘进行读操作。这会减慢表的查询速度，增加查询时间。
性能: 随着时间的推移，表中的碎片会越来越多。如果表中有许多碎片，可能会影响MySQL的性能。这可能是因为MySQL需要更长的时间来执行查询，而这可能会导致性能下降。
备份/恢复时间: MySQL备份和恢复时需要扫描整个表的数据。碎片会增加备份和恢复所需的时间，使这些操作变得更加缓慢。
怎么解决碎片问题
Optimize Table
为了避免表碎片的影响，可以在删除或更新大量数据后，在表上运行Optimize Table命令来重新组织表，从而提高表的性能，并释放未使用的空间。
OPTIMIZE TABLE是MySQL中的一个命令，用于优化表。当表中有大量删除、更新或插入操作时，会留下许多碎片，导致表的性能下降。使用OPTIMIZE TABLE命令可以重建表，将碎片整理，从而获得更好的表性能。
在使用 OPTIMIZE TABLE 命令来优化表之前，需要注意以下几点：
备份数据：在对表进行优化之前，最好先对表进行备份。在优化过程中可能会发生错误，导致数据丢失，备份可以帮助我们恢复丢失的数据。
选择合适的时间：优化表时可能会阻塞读和写操作，因此在系统使用高峰期时建议避免进行优化操作。
优化频率：频繁地执行 OPTIMIZE TABLE 命令会浪费系统资源（比如iops飙升）和时间，因此应该根据实际需要选择合适的优化时间和频率。
空间限制：在开始优化之前，应该确保表所在的磁盘上有足够的空间来存储优化过程中产生的临时表和索引。
不要取消优化：在优化过程中，不要取消操作，因为这可能会导致数据错误或表格损坏。如果必须中止操作，建议重新启动优化过程。
总的来说，在使用 OPTIMIZE TABLE 前，请确保已经了解了要优化的表的特性，并且建议在非高峰期进行操作。
data_free能准确反映表的碎片空间吗？
答：其实不能
因为data_free仅报告空闲的页面区域（连续的1MB页面）。如果页面只部分填充，则data_free不会报告这种情况。因此，data_free可能不能准确地反映表空间中的真实空闲空间量。
接下来，我们一起来实验一下：创建一张测试表，插入200w数据，然后分别随机删除50w行和顺序删除50w行数据后，data_free字段的变化。
1.准备数据
##创建一张测试表fragment_test
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);
## 插入400w行数据
## 创建插入数据的存储过程
CREATE 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
## 调用test_insertdata(2000000)
call test_insertdata(2000000);
插入200w数据后，使用如下语句查看表的统计信息。
SELECT NAME, TABLE_ROWS, 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 t.`TABLE_SCHEMA` ='zhenxi_test'
and t.table_name = 'fragment_test'
可以看到information_schema.TABLES表中，table_rows、data_size、data_free等的值都很小。那是因为表的统计信息没有实时更新，这个受information_schema_stats_expiry参数的影响。它指定了在表和索引统计信息过期之前它们应该保留多长时间。它的默认值是86400秒，即24小时。如果需要获取最新的数据，可以通过执行ANALYZE TABLE table_name操作。
请注意，ANALYZE TABLE操作可能会占用大量系统资源，并且可能需要一段时间才能完成。因此，我们建议在低峰期执行此操作，并注意备份数据以防止数据丢失。
注：information_schema.INNODB_TABLESPACES 是 MySQL 数据库管理系统中的一个系统表空间，用于存储 InnoDB 数据文件和表空间的信息。INNODB_TABLESPACES 表包含每个表空间和数据文件的物理特征信息，包括 SPACE、NAME、FILE_FORMAT、FILENAME、FILE_SIZE、EXTENT_SIZE、INITIAL_SIZE 和 MAXIMUM_SIZE 等列。
执行analyze table fragment_test
之后，重新查看元数据信息。
2.随机删除50w数据
DELETE FROM fragment_test ORDER BY RAND() LIMIT 500000;
删除成功后，执行analyze table，继续来观察表的空间分配变化。
从200W数据中随机删除50W，也就是1/4，表的空间没有变化，但是表的总的大小没变化，data_free也基本上没有变化，仅增大了几M。
3.按顺序删除50w数据
DELETE FROM fragment_test where id <500000;
删除成功后，执行analyze table，继续来观察表的空间分配变化。
从剩余的中顺序删除50W，可以看到这次表的数据大小、索引大小以及data_free都有较为明显变化。
为什么会有这种差异呢？
正如我上面提到的，data free的计算方式，它仅报告空闲的页面区域（连续的1MB页面）。如果页面只部分填充，比如随机删除数据，则data_free不会报告这种情况。如果按照聚集索引连续的方式删除（相对随机删除），那些存储连续数据的区（extent）是可以完全释放出来的，这些区的空间释放出来之后，则会被认为是data free。
因此，data_free可能不能准确地反映表空间中的真实空闲空间量。
总结
information_schema.tables中的data_free字段是文件系统中未使用空间的值。换句话说，分配给表或分区但当前未被数据使用的空间量。它指示操作系统或 MySQL 服务器可能回收但尚未释放的空间量，也叫做数据碎片。对表进行频繁更新（删除、插入、删除）可能会导致表中存在大量碎片。碎片存在可能影响表的性能，当表的碎片率过高时，可以利用optimize table命令来重新组织表，从而提高表的性能，并释放未使用的空间。optimize table注意需要在低峰期执行。
另外，data_free可能并不能准确地反映表空间中地真实空闲空间量。
参考：
https://www.cnblogs.com/wy123/p/12535644.html
https://lefred.be/content/mysql-innodb-disk-space/
https://lefred.be/content/overview-of-fragmented-mysql-innodb-tables/
点个“赞 or 在看” 你最好看！
👇👇👇 咔片谢谢各位老板啦！！！
