mysql 删除数据之后，数据文件瘦身
问题背景
当您的库中删除了大量的数据后，您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE 是指对表进行优化。如果已经删除了表的一大部分数据，或者如果已经对含有可变长度行的表（含有 VARCHAR 、 BLOB 或 TEXT 列的表）进行了很多更改，就应该使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费 。OPTIMIZE TABLE 命令只对 MyISAM 、 BDB 和 InnoDB表起作用 。表优化的工作可以每周或者每月定期执行，对提高表的访问效率有一定的好处.
但是需要注意的是，优化表期间会锁定表，所以一定要安排在空闲时段进行
在正常的运维中，如果开通了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';
- Name
表名称 - Engine:
表的存储引擎 - Version:
版本 - Row_format
行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段。 - Rows
表中的行数。对于非事务性表，这个值是精确的，对于事务性引擎，这个值通常是估算的。 - Avg_row_length
平均每行包括的字节数 - Data_length
整个表的数据量(单位：字节) - Max_data_length
表可以容纳的最大数据量 - Index_length
索引占用磁盘的空间大小 - Data_free
对于MyISAM引擎，标识已分配，但现在未使用的空间，并且包含了已被删除行的空间。 - Auto_increment
下一个Auto_increment的值 - Create_time
表的创建时间 - Update_time
表的最近更新时间 - Check_time
使用 check table 或myisamchk工具检查表的最近时间 - Collation
表的默认字符集和字符排序规则 - Checksum
如果启用，则对整个表的内容计算时的校验和 - Create_options
指表创建时的其他所有选项 - Comment
包含了其他额外信息，对于MyISAM引擎，包含了注释徐标新，如果表使用的是innodb引擎 ，将现实表的剩余空间。如果是一个视图，注释里面包含了VIEW字样
注意一下这个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 这个字段，就知道这个效果了。
