目录
- 1、mysql数据结构
- 4、online ddl
最近有个上位机获取下位机上报数据的项目,由于上报频率比较频繁且数据量大,导致数据增长过快,磁盘占用多。
为了节约成本,定期进行数据备份,并通过delete
删除表记录。
明明已经执行了delete
,可表文件的大小却没减小,令人费解
项目中使用mysql
作为数据库,对于表来说,一般为表结构和表数据。表结构占用空间都是比较小的,一般都是表数据占用的空间。
当我们使用 delete
删除数据时,确实删除了表中的数据记录,但查看表文件大小却没什么变化。
1、mysql数据结构
凡是使用过mysql
,对b+树
肯定是有所耳闻的,mysql innodb
中采用了 b+
树作为存储数据的结构,也就是常说的索引组织表,并且数据时按照页来存储的。因此在删除数据时,会有两种情况:
- 删除数据页中的某些记录
- 删除整个数据页的内容
2、表文件大小未更改和mysql设计有关
比如想要删除 r4 这条记录:
innodb
直接将 r4
这条记录标记为删除,称为可复用的位置。如果之后要插入 id
在 300
到 700
间的记录时,就会复用该位置。
由此可见,磁盘文件的大小并不会减少。
通用删除整页数据也将记录标记删除,数据就复用用该位置,与删除默写记录不同的是,删除整页记录,当后来插入的数据不在原来的范围时,都可以复用位置,而如果只是删除默写记录,是需要插入数据符合删除记录位置的时候才能复用。
因此,无论是数据行的删除还是数据页的删除,都是将其标记为删除的状态,用于复用,所以文件并不会减小。
3、那怎么才能让表大小变小
delete
只是将数据标识位删除,并没有整理数据文件,当插入新数据后,会再次使用这些被置为删除标识的记录空间,可以使用optimize table
来回收未使用的空间,并整理数据文件的碎片。
optimize table 表名;
注意:optimize table
只对myisam
, bdb
和innodb
表起作用。
另外,也可以执行通过alter table
重建表
alter table 表名 engine=innodb
有人会问optimize table
和alter table
有什么区别?
alter table t engine = innodb
(也就是recreate),而 optimize table t
等于 recreate+analyze
4、online ddl
最后,再说一下online ddl
,dba
的日常工作肯定有一项是ddl变更,ddl变更会锁表,这个可以说是dba
心中永远的痛,特别是执行ddl
变更,导致库上大量线程处于“waiting for meta data lock
”状态的时候。因此在 5.6 版本后引入了 online ddl
。
online ddl
推出以前,执行ddl主要有两种方式copy
方式和inplace
方式,inplace
方式又称为(fast index creation
)。相对于copy
方式,inplace
方式不拷贝数据,因此较快。但是这种方式仅支持添加、删除索引两种方式,而且与copy方式一样需要全程锁表,实用性不是很强。online
方式与前两种方式相比,不仅可以读,还可以支持写操作。
执行online ddl
语句的时候,使用algorithm
和lock
关键字,这两个关键字在我们的ddl
语句的最后面,用逗号隔开即可。示例如下:
alter table tbl_name add column col_name col_type, algorithm=inplace, lock=none;
algorithm选项
- inplace:替换:直接在原表上面执行
ddl
的操作。 - copy:复制:使用一种临时表的方式,克隆出一个临时表,在临时表上执行
ddl
,然后再把数据导入到临时表中,在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间,表不允许dml
的操作。 - default:默认方式,有
mysql
自己选择,优先使用inplace
的方式。 - lock选项
- share:共享锁,执行
ddl
的表可以读,但是不可以写。 - none:没有任何限制,执行
ddl
的表可读可写。 - exclusive:排它锁,执行
ddl
的表不可以读,也不可以写。 - default:默认值,也就是在
ddl
语句中不指定lock
子句的时候使用的默认值。如果指定lock
的值为
default
,那就是交给mysql
子句去觉得锁还是不锁表。不建议使用,如果你确定你的ddl
语句不会锁表,你可以不指定lock
或者指定它的值为default
,否则建议指定它的锁类型。
执行ddl
操作时,algorithm
选项可以不指定,这时候mysql
按照instant
、inplace
、copy
的顺序自动选择合适的模式。也可以指定algorithm=default
,也是同样的效果。如果指定了algorithm
选项,但不支持的话,会直接报错。
optimize table
和 alter table
表名 engine=innodb
都支持oline ddl
,但依旧建议在业务访问量低的时候使用
5、总结
delete
删除数据时,其实对应的数据行并不是真正的删除,仅仅是将其标记成可复用的状态,所以表空间不会变小。
可以重建表的方式,快速将delete
数据后的表变小(optimize table
或alter table
),在 5.6 版本后,创建表已经支持 online
的操作,但最好是在业务低峰时使用
到此这篇关于为什么mysql 删除表数据 磁盘空间还一直被占用的文章就介绍到这了,更多相关mysql
删除表数据 内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!