爱编程,爱生活

Mysql 删除数据之后,数据文件瘦身

2018-06-16  本文已影响32人  熬夜的猫头鹰

mysql 删除数据之后,数据文件瘦身

问题背景

当您的库中删除了大量的数据后,您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE 是指对表进行优化。如果已经删除了表的一大部分数据,或者如果已经对含有可变长度行的表(含有 VARCHAR 、 BLOB 或 TEXT 列的表)进行了很多更改,就应该使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费 。OPTIMIZE TABLE 命令只对 MyISAMBDBInnoDB表起作用 。表优化的工作可以每周或者每月定期执行,对提高表的访问效率有一定的好处.

但是需要注意的是,优化表期间会锁定表,所以一定要安排在空闲时段进行

在正常的运维中,如果开通了innodb_purge_threads = 1 参数 ,可以不用手动的执行OPTIMIZE TABLE 命令。
是否开启执行下列命令:看看value字段是否为1

show variables like '%innodb_purge%';

字段解释 : InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中,清除操作是主线程的一部分,这意味着运行时它可能会堵塞其它的数据库操作。在5.5版本中将其独立出来。

在进行大量的删除操作之前可以查看一下表空间的利用程度:


select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
       concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB 
from tables 
where table_schema='?' 
and table_name='?';

删除之后,执行

执行命令:

show table status like 'table_name';

注意一下这个Data_free这个字段。

执行如下命令进行优化整理碎片

OPTIMIZE TABLE 【table_name】

在执行一看看表空间大小。


select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
       concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB 
from tables 
where table_schema='?' 
and table_name='?';

执行命令:

show table status like 'table_name';

前后对比一下Data_free 这个字段,就知道这个效果了。

上一篇 下一篇

猜你喜欢

热点阅读