您好,欢迎访问代理记账网站
移动应用 微信公众号 联系我们

咨询热线 -

电话 15988168888

联系客服
  • 价格透明
  • 信息保密
  • 进度掌控
  • 售后无忧

数据明明不多,为什么表占用很大

1、根本原因:数据空洞

 有些用了很久的表,尽管表中的记录并不多,但是文件却很大,这是什么原因呢?想要回答这个问题,就必须从表本身出发。

 首先明确这是一张用了很久的表,如果不是只读表,那么理论上应该进行了大量的增删操作,而大量的增删操作就是造成其占用大的原因,我们可以通过分析InnoDB的删除流程来了解这个原因,假设要在一个表中删除一条记录,那么InnoDB会怎么做呢?

 我们知道InnoDB中的存储数据方式是根据主键索引建成一棵B+Tree,非叶子节点存储主键索引,叶子节点存储记录本身。而每个叶子节点是一个大小为16K的数据页,存储了多条记录(大部分情况下,除非一条记录大到叶子节点只能容纳一条记录)。而进行删除时,InnoDB在定位到记录后是将记录所在的位置标记为可复用,并没有对该部分空间进行回收,如果后续插入的记录的索引排序满足这个位置的要求,那么就会对这部分空间进行复用。在进行复用之前数据页的这部分空间就空出来了,尽管不存储数据,但是依旧占据这硬盘空间,称之为数据空洞
 那么如果我对一整个页的数据进行删除呢?或者更狠一点,删除全表,会怎么样呢?结果就是InnoDB会将删除的整页标记为可复用,尽管不会有页内空隙,提高了复用率,但是空间还是不会回收。如果你为了因为空间不够对表中一些数据进行了删除,然后发现占用并没有减少,那么可以肯定就是数据空洞造成的。

 而除了删除数据,插入数据同样可有可能造成数据空洞,比如我们要在一个已满数据页的中间插入一条记录,那么InnoDB就会将这个数据页分裂成两部分,各自存储一部分数据,导致这两个数据页的存储空间并没有完全利用,形成了数据空洞。但有顺序插入可以减少数据空洞,顺序插入是以追加的方式进行插入,最多是在最后一个叶子节点后面新开辟一个节点,造成的数据空洞最多不超过一个数据页,且大概率会在后续被利用到。

 知道了原因,怎么解决这个问题呢?

2、解决方法:重构主键B+Tree

 占用大是因为数据空洞,那么只需要重新进行排序分配空间,使得数据页存储的记录变得紧凑就可以解决这个问题了。很容易想到的办法就是创建一张临时表,将原表数据进行迁移,迁移是顺序插入,不会产生数据空洞,迁移完毕后让临时表代替原表即可解决数据空洞。而这个操作可以通过一条命令执行:

alter table ‘表名’ engine = InnoDB

 在MySQL5.5及以前,使用该命令实际上就是用临时表代替原表进行操作,但是会出现一个问题,就是迁移过程中对原表进行的更新操作会丢失。而在MySQL5.6以后则解决了这个问题,解决方法是将迁移过程中发生的更新操作记录在日志中,在迁移结束后使用该日志进行重放,将修改应用到迁移后的新表中。


分享:

低价透明

统一报价,无隐形消费

金牌服务

一对一专属顾问7*24小时金牌服务

信息保密

个人信息安全有保障

售后无忧

服务出问题客服经理全程跟进