Mysql data_free在执行完 OPTIMIZE TABLE 后 一定是0吗？

Question

今天领导问我一个表是什么业务中使用的，说 这个表访问频繁吗？ 我看这个表碎片很多，需要整理下空间，可能会几分钟的锁表

我看到这个表是做数据清洗用的 就通知领导可以整理空间了。

 

但是我自己不懂什么是整理空间 遂上网查询后得知 表在多次执行删除后 会产生碎片 导致mysql查询效率降低

执行OPTIMIZE TABLE后可以整理碎片空间 优化表

 

我执行的测试语句

新建一个表 

CREATE TABLE sd(
id INT(4),
name VARCHAR(225)
)；

创建一个执行计划

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_to_sd`(IN num INT)
BEGIN
bq:WHILE num < 20000 DO
    INSERT INTO sd(id,name) VALUES(num,'sssssdsdsds');
    SET num=num+1;
END WHILE bq;
        
END；

调用执行计划 往sd表中插入2w条数据

CALL insert_to_sd(0);

 

插入后 直接查询表的信息 看到data_free的值

这里我有一个疑惑，不是说执行删除语句之后才会产生碎片 为什么 执行批量插入后data_free的值就有，并且很大

然后我执行了 OPTIMIZE TABLE sd;

 

显示ok

之后继续查询一下表信息

发现没有变化

这里我有另一个问题 为什么执行OPTIMIZE TABLE语句之后 data_free的值没有变化

 

然后我执行了删除语句 

DELETE FROM sd WHERE id > 10000;

 

 

删除成功之后 在看一下表状态

 

data_free的值变大了 此时再执行一遍 OPTIMIZE TABLE语句

执行成功后再看一下表状态

发现data_free的值直接变0了 

这是为啥？？？？？？？希望有大佬能给我解答一下疑惑

Answer 1

查到了官方的解释:

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，每个区1MB，64个连续的16 kb 大小的page），只有一个完全没有使用的区，才统计为data_free，因此data_free并不能反映出来真正的空闲空间。

个人感觉可以通过查看数据文件剩余大小判断,或者你在第一次delete之后不是查了data_free,再delete一部分数据,然后查data_free,看看变化是否准确