在InnoDB中，delete操作并不会真的删除数据，mysql实际上只是给要删除的数据打了标记，标记为删除。磁盘所占空间不会变小，即表空间并没有真正被释放。
MySQL删除数据几种情况以及是否释放磁盘空间
-
drop，truncate
立刻释放磁盘空间 ，不管是 Innodb还是MyISAM ;
truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度; -
delete带条件
对于 delete from table_name where xxx; 带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间; -
delete不带条件
delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间 （应该是做了特别处理，也比较合理）,InnoDB 不会释放磁盘空间;
碎片的产生
-
MySQL 中 insert 与 update 都可能导致页分裂，这样就存在碎片。
-
对于大量的UPDATE，也会产生文件碎片化 ， Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂（page split），频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。
-
delete 语句实际上只是给数据打个标记，并且记录到一个链表中，这样就形成了留白空间。
-
在InnoDB中，删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。
-
当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；
这样设计的思考
- mysql的delete操作，只是做了逻辑上的标记删除，在磁盘上数据并没有被真正删除。
- 这样的设计是因为：如果在磁盘上移除之后，很多其它的记录需要在磁盘上重新排列，这会消耗大量的性能。(例如：一个大表，存在索引，删除了其中一行，那么整个索引结构就会发生变化，随之而来的改变索引结构，必将带来磁盘IO)
- 所有被删除的记录会组成一个垃圾链表，这个链表记录占用的空间叫可重用空间。新插入的记录可覆盖此空间。
如何查看数据库的碎片情况
--查看数据库中每个存在碎片的表
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
评论区