MYSQL Delete删除数据后物理磁盘空间未减少解决

起因:

数据表中保存日志信息比较多，一个表站用好几个G的空间，想清理掉三个月以前的数据，但清理后发现物理磁盘站用空间并没有减少。

原因是使用delete删除的时候，mysql并没有把数据文件删除，而是将数据文件的标识位删除，没有整理文件，因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中，当有新数据写入的时候，mysql会利用这些已删除的空间再写入。即，删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间。

官方推荐使用 OPTIMIZE TABLE命令来优化表，该命令会重新利用未使用的空间，并整理数据文件的碎片。
语法如下:
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …

注：该命令将会整理表数据和相关的索引数据的物理存储空间，用来减少占用的磁盘空间，并提高访问表时候的IO性能。但是，具体对表产生的影响是依赖于表使用的存储引擎的。该命令对视图无效。
该命令目前只对MyISAM、InnoDB，ARCHIVE的表起作用，其余引擎的不起作用

执行命令:
OPTIMIZE TABLE tableName;

查看数据表站用磁盘空间查询语句

1、查看指定数据库指定表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql' AND table_name='help_topic'
order by data_length desc, index_length desc;

2、查看指定数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

3、查看指定数据库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

4、查看所有数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

5、查看所有数据库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

补充:

1、如何使optimize 支持其他引擎？
默认情况下，optimize不支持其他存储引擎，但是可以在启动mysqld的时候，使用 --skip-new 参数，这种情况下，optimize命令，将会被映射到alter table命令上，实现上述的功能。

2、该物理删除还是逻辑删除？
生产环境下，尽量不要用物理删除，一旦物理删除了，意味着数据恢复就会很麻烦。建议逻辑删除，数据仍存储在DB里。如果数据量很大的时候，可以考虑使用分库分表。但，这个仍旧是需要根据业务场景来。

3、optimize执行时会将表锁住，所以不要在高峰期使用。也不要经常使用，每月一次就足够了