MySQL空间碎片

碎片产生的原因

• 对于mysql表数据，当你delete掉很多数据时，这些数据占用的磁盘空间可能并不会立刻被回收；比如一张表有10G的数据，delete掉1G数据后，再查看表ibd文件会发现文件大小可能还是10G；如果这个表有insert操作的话，那么mysql就会优先考虑能不能将新数据存储到空白空间上，容易出现这样的情况：某个空白空间的大小是2MB，新插入一条数据大小是1.5MB并存储到该空白空间上，这时就会产生更小的空白空间，而这种更小的空白空间更难被利用，如果像这种碎片非常多，就会比较浪费资源而且降低表磁盘I/O性能。

• 对于频繁地update操作，也很容易产生碎片问题。比如对于可变长字段，如varchar、text、blob等字段，如果update操作将数据大小改小，那么也会产生碎片问题。

• mysql目前比较常用的引擎是innodb和myisam，这两种引擎下都有可能产生碎片，碎片的产生和消除都是随机的，而碎片越多会给查询扫描工作带来越大的影响。

空间碎片回收

SHOW TABLE STATUS LIKE 'table';

可以查看到数据碎片信息，返回数据中，Data_free大于0时即说明存在数据碎片。

innodb表优化

ALTER TABLE tb_name ENGINE=INNODB;

myisam表优化

OPTIMIZE TABLE tb_name;

值得注意的，以上方式都是锁表

optimize操作介绍

mysql5.6的官方文档在13.7.2.4小节对optimize操作有详细的介绍。optimize table命令的作用是重新组织表数据和关联索引数据的物理存储，以减小存储空间并提高访问表时的I/O效率；命令主要作用于innodb、myisam和archive引擎表，而命令对表所做的实际更改取决于该表使用的存储引擎。 ·innodb引擎下的optimize操作 对于innodb表，optimize table操作实际映射为alter table ... force操作

优化建议

• 清除碎片操作会暂时锁表，数据量越大，耗费的时间越长，可以做个脚本，定期在访问低谷时间执行，例如每周三凌晨，检查DATA_FREE字段，大于自己认为的警戒值的话，就清理一次。
• 阿里云提供的有最佳实践。 (opens new window)