当数据库实例磁盘空间使用率比较大时，我们优先选择的方法一定是将可以清理的无用数据进行清理，如果实在是没有数据可清理，我们才会考虑对磁盘进行扩容，必将扩容磁盘要！花！钱！本篇主要将一下空间碎片化清理的一个案例。
一、发现问题
每日巡检数据库时，发现某一实例的数据量增长比较明显，起初并没有太在意，以为是业务上的正常增长。（切记，以后发现类似问题，一定要向业务方确认，不要自己瞎猜）。随着日子一天天过去，终于，磁盘空间的使用率达到了二级警戒线。此时我们再去观察近一个月的空间增长趋势，从19号开始磁盘空间就开始按照这个速度增长，如果一直这样下去将是很危险的！立马向业务方反馈该情况，并协助排查处理。
二、问题排查
对于空间使用率大的问题我们的处理思路一般两种：1.处理不必要的数据，释放空间 2. 磁盘扩容。我们优先使用第一个方法。
1、先排查具体是哪个schema占用空间比较大，可以看到db_rim库的数据相对其他库数据量明显要大很多
select table_schema,sum(DATA_LENGTH+ INDEX_LENGTH + DATA_FREE)/1024/1024 as data_total from tables group by table_schema order by total_size desc;
2、排查到库后我们继续往下排查具体哪些表比较大。我们根据表的数据空间占用大小倒叙查看top10。从这张图中我们可以看到两个问题。
1.rim_user_msg_log表的数据量已经达到了37G，这些数据都是有用的吗？对于早期的数据是否可以做清理或者归档来释放磁盘空间？
2.rim_user_msg_analyse表的data_free达到17G，碎片化非常严重，可以是对这些空间进行回收。
select table_name,DATA_LENGTH/1024/1024 as data_size , INDEX_LENGTH/1024/1024 as index_size, DATA_FREE/1024/1024 as free_size from tables where TABLE_SCHEMA='db_rim' order by data_size desc limit 5;
三、问题处理
1、rim_user_msg_log表的数据可以全部清理掉
2、rim_user_msg_analyse在业务低峰对其空间碎片进行回收
碎片化回收前：
碎片化回收后：
磁盘空间趋势上：
四、总结
1、如何回收空间碎片化？
1.optimize table tbl_name;
2.alter table tbl_name engine=innodb;
2、两者之间的异同？
相同点：两者都会对表的数据和索引数据进行重建，对空间碎片进行回收，提高I/O资源。两者执行的大致流程如下：
1）获取MDL写锁
2）降级为MDL读锁，重建表（时间消耗最长）
3）升级MDL写锁
4）释放MDL锁
这个过程怎么去验证呢？
| Session1 | Session2 | Session3 |
|---|---|---|
| begin;update t1 set beizhu1='aaaaaa' where id<1000; | ||
| optimize table t1;被阻塞 | 事物未提交 | 图1 |
| rollback | ||
| 开始执行 | 事务已回滚释放锁资源 | 图2 |
| 执行中 | begin;update t1 set beizhu1='aaaaaa' where id<1000; | show processlist |
| 执行中 | 事务未提交 | 图3 |
| 执行中 | 事物提交 | |
| 已完成 | 图4 |
alter table engine=innodb;的操作步骤相同，只是Info列中显示信息不同。
图1
图2
图3
图4
不同点：optimize相当于recreate + analyze，alter相当于recreate
