侧边栏壁纸
博主头像
luoxx博主等级

只要思想不滑坡,办法总比困难多

  • 累计撰写 59 篇文章
  • 累计创建 64 个标签
  • 累计收到 1,357 条评论

目 录CONTENT

文章目录

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

luoxx
2022-07-26 / 0 评论 / 3 点赞 / 4,056 阅读 / 1,080 字
温馨提示:
本文最后更新于 2022-07-26,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除,邮箱地址:luoxmc@vip.qq.com

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

MySQL删除数据几种情况以及是否释放磁盘空间
  1. droptruncate
    立刻释放磁盘空间 ,不管是 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

3

评论区