Mysql磁盘碎片整理

背景

        经常进行 delete 操作，产生空白空间，如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用，于是造成了数据的存储位置不连续,以及物理存储顺序与理论上的排序顺序不同，久而久之就产生了碎片。

根据线上处理经验总结比对3种处理磁盘碎片优缺点：

治理方案	优势	缺点	备注
将数据量巨大的表设计成分区表，按时间分区	通过结转分区数据，删除分区释放磁盘碎片，磁盘IO抖动秒级别，对线上业务影响小		估算数据量，每个分区不超过3亿数据350G为佳；流水，日志表这些表应该在创建时就应该设计成分区表，避免以后磁盘碎片痛点
重建表存储引擎，重新组织数据（ALTER TABLE tablename ENGINE=InnoDB；）		整理过程加锁，周期长，且对线上业务影响较大：10亿数据量，1000G，tp99会持续超过60s	谨慎操作
主从切换（DBA可使用一个磁盘更大的干净的库，进行主从切换）		涉及面广，牵扯范围较大，处理时长在分钟级	谨慎操作

总结

        使用删除表分区的方式性价比更高：数据库：    Sql语句：alter table xxxx drop partition p19;

        只要使用mysql，必然会遇到Mysql碎片问题痛点，数据量大的业务表应该设计成分区表方便磁盘碎片整理，降低维护成本和业务影响。碎片清理前后，IO性能会上升，SQL执行效率更快。所以，在日常运维工作中，应对碎片进行定期清理，保证数据库有稳定的性能和充足的空间。

        扩展：提到提高IO性能，在紧急情况下还可以考虑开启刷盘（设置 sync_binlog=0;innodb_flush_log_at_trx_commit=0），但开启刷盘会有数据丢失风险（集团数据库模板配置参数默认sync_binlog=1;innodb_flush_log_at_trx_commit=1）。

        mysql数据库核心参数介绍：https://www.cnblogs.com/klvchen/p/10861850.html

查看磁盘碎片的SQL

SELECT table_schema AS '数据库',
       TABLE_NAME AS '表名',
                     table_rows AS '记录数',
                     truncate(data_length/1024/1024/1024, 2) AS '数据容量(MB)',
                     truncate(data_free/1024/1024/1024, 2) AS '数据空洞(MB)',
                     truncate(index_length/1024/1024/1024, 2) AS '索引容量(MB)'
FROM information_schema.tables
ORDER BY data_length DESC,
         index_length DESC;

题外话

        治理磁盘碎片最好的方法就是删除表，不同业务对数据的要求不同。如果有可能的话新建一个临时表。

-- 原始表
origin_test
-- 临时表
origin_test_temp
-- 以上两张表结构一样，不同的是临时表带分区


ALTER TABLE origin_test RENAME TO origin_test1; 
ALTER TABLE origin_test_temp RENAME TO origin_test; 

以上，通过修改表明的方式，进行磁盘碎片整理。但是数据库表会有短暂的停写。再过一段时间后，origin_test表的数据积累到一定程度，就可以将origin_test_temp删除了。