为什么mysql的delete操作不释放磁盘空间

在 InnoDB 中，delete 操作并不会真的删除数据，mysql 实际上只是给要删除的数据打了标记，标记为删除。磁盘所占空间不会变小，即表空间并没有真正被释放。

一、 MySQL 删除数据几种情况以及是否释放磁盘空间

1. drop ，truncate

立刻释放磁盘空间 ，不管是 Innodb 还是 MyISAM ;
truncate table 其实有点类似于 drop table 然后 creat,只不过这个 create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近 drop table 的速度;

2. delete 带条件

对于 delete from table_name where xxx; 带条件的删除, 不管是 innodb 还是 MyISAM 都不会释放磁盘空间;

3. delete 不带条件

delete from table_name 删除表的全部数据,对于 MyISAM 会立刻释放磁盘空间 （应该是做了特别处理，也比较合理）,InnoDB 不会释放磁盘空间;

二、 碎片的产生

• MySQL 中 insert 与 update 都可能导致页分裂，这样就存在碎片。

• 对于大量的 UPDATE，也会产生文件碎片化 ， Innodb 的最小物理存储分配单位是页(page)，而 UPDATE 也可能导致页分裂（page split），频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。

• delete 语句实际上只是给数据打个标记，并且记录到一个链表中，这样就形成了留白空间。

• 在 InnoDB 中，删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。InnoDB 的 Purge 线程会异步的来清理这些没用的索引键和行。

• 当执行插入操作时，MySQL 会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

三、 这样设计的思考

1. mysql 的 delete 操作，只是做了逻辑上的标记删除，在磁盘上数据并没有被真正删除。

2. 这样的设计是因为：如果在磁盘上移除之后，很多其它的记录需要在磁盘上重新排列，这会消耗大量的性能。(例如：一个大表，存在索引，删除了其中一行，那么整个索引结构就会发生变化，随之而来的改变索引结构，必将带来磁盘 IO)

3. 所有被删除的记录会组成一个垃圾链表，这个链表记录占用的空间叫可重用空间。新插入的记录可覆盖此空间。

四、 如何查看数据库的碎片情况

-- 查看数据库中每个存在碎片的表
select concat('optimize table ',table_schema,'.',table_name,';'),data_free,engine from information_schema.tables where data_free>0 and engine !='MEMORY';
--查看指定表的碎片情况
show table status like 't_user'

--找到碎片化最严重的表
SELECT table_schema, TABLE_NAME, concat(data_free/1024/1024, 'M') as data_free
FROM `information_schema`.tables
WHERE data_free > 3 * 1024 * 1024
    AND ENGINE = 'innodb'
ORDER BY data_free DESC

五、 如何清理碎片

• alter table tb_test engine=innodb

  这其实是一个 NULL 操作,表面上看什么也不做,实际上重新整理碎片了.当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间。

• optimize table xxx;

  OPTIMIZE TABLE 语句可以重新组织表、索引的物理存储，减少存储空间，提高访问的 I/O 效率。类似于碎片整理功能。
   
  MySQL 可以通过 optimize table 语句释放表空间，重组表数据和索引的物理页，减少表所占空间和优化读写性能
   
  使用语法:
  OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

参考资料：
https://blog.csdn.net/levae1024/article/details/121791757
https://www.php.cn/mysql-tutorials-493459.html

转载自：https://www.luoxx.top/archives/mysql-free-disk