【mysql篇】执行delete删除大量数据后，磁盘未清空，为什么？

最近某个项目虽说用户量不大，但是，单表的数据量越来越大，mysql一般单表超过千万级别后，性能直线下降，所以利用shardingphere按月做了一个分表，所以得把历史数据迁移到各个分月的表中。

迁移脚本

直接开始迁移，执行如下脚本

insert into cern_202301
select * from cern_bak
where report_time>='2023-01-01 00:00:00'
and report_time<='2023-01-31 23:59:59'

• report_time是作为分表字段的依据，这里是迁移1月份的数据到cern_202301中
• 因为已经迁移最近半年的数据到分表中，保证在不影响业务的前提下，做迁移操作
• 迁移到一半发现磁盘空间不够

如果是你，你的解决方案是？

• 可以留言一起，交流一下学习一下

删除数据以及备份数据

####一次导出所有数据
mysqldump -uroot -p123456 iopace cern > /mnt/datadisk1/cern.sql###按月导出数据--不用执行,了解即可mysqldump -uroot -p123456 iopace cern_bak --where=" report_time>='2023-04-01 00:00:00' and report_time<='2023-04-30 23:59:59'" > /mnt/datadisk1/cern2304.sql###导入数据--不用执行,了解即可
mysql -u root -p123456 iopace<cern2307.sql

• root为用户名
• 123456为密码
• iopace为数据库名
• cern为需要导出的表名
• /mnt/datadisk1/cern.sql 表示导出到哪里，因为我们有三个磁盘，这是另外一个磁盘，暂时未使用，所以数据导出到这里
  
• 因为我已经把业务已经切换到分表来了，所以cern表是不会存在写入操作的，一点要注意，防止丢失数据

数据已备份，那我们就可以执行delete操作

delete from cern
where report_time>='2024-02-01 00:00:00'
and report_time<='2024-02-10 23:59:59'

• 之前已经迁移半年的数据，那我就delete最近半年的数据
• 因为表数据量有点大，所以，我都是10天删除一次，防止花费时间过长
• 清理大半年的数据后，发现磁盘空间还是没有任何的变化？

按我们正常的理解，是不是删除数据后，磁盘大小就会变大？

使用delete删除数据，不会把数据文件删除，而是将数据文件的标识位删除，因此会留下数据碎片，当有新数据写入的时候，mysql会利用这些已删除的空间再写入。如果碎片空间满，就不能插入

解决方法

OPTIMIZE TABLE

• 会优化表，会锁表，建议1个月处理一次，如果不存在大量删除的情况，不太建议使用
• 实现原理，实际上就是把当前表的数据复制到临时表，再删除当前表，再把临时表重命名为当前表，所以对空间会有要求

OPTIMIZE TABLE cern;

• cern为需要优化的表名
• cern表20G左右，1个月的数据大约2.5G左右，当前磁盘可用空间2.5G左右，所以肯定是没有办法通过该方式来优化的
• 空间足够，可以通过他来处理大量delete的情况

二进制日志

所有的修改、删除都会存放在二进制文件中，有时候误删除后，我们也可以通过二进制日志还还原数据，所以这个文件会有点庞大

###259200秒====30天，所以二进制文件保存的周期为30天
show variables like '%expire%';###当前二进制历史文件存放目录show BINARY logs;
###当前正在运行的二进制日志，注意，这个文件是不能动的show master status;

• File 表示当前正在运行的二进制日志文件是那个
• position表示位置，例如主从复制的时候，就会用到
  
• 下面有很多日志文件，这是我删除一部分后的，当时，大约有20多个文件，每个文件1.5G左右，被我删除到88
• purge binary logs to ‘binlog.000088’; 表示删除88之前的所有二进制文件

这个时候，我们再去df -h查看磁盘，磁盘空间已释放，

按月生成数据

如果二进制腾出来一部分空间后，还不够，历史数据迁移到各个月分表的，还有一种方式。
就是把cern表的数据，按月导出成sql脚本，按月命名
找一个测试环境，把几个月的sql脚本导入后，测试一下总记录数是否有丢失
如果没有丢失，就可以把cern表删除，再通过dump导入sql的方式，一个月一个月的导入数据

###按月导出数据--不用执行,了解即可
mysql -u root -p123456 iopace<cern2307.sql