MySQL表空间碎片—产生原因、带来的问题以及如何解决

1 碎片是如何产生的

MySQL碎片指的是MySQL数据文件中一些不连续的空白空间，这些空间无法再被全部利用，久而久之越来越多越来越零碎。
MySQL中碎片的产生主要由频繁的删除、更新和插入操作导致：

• 在InnoDB中，删除记录只是将这些行标记为“已删除”，不是真的从索引中物理删除了，磁盘的文件大小不会收缩。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。
  当执行插入操作时，MySQL会尝试使用空白空间，如果插入的数据刚好按照索引排序落在这个区间，可能会复用这个位置，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

• 对于大量的UPDATE，也会产生文件碎片化。Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂，频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。例如原始数据长度varchar(100)，大规模更新数据长度为50，这样的话，有50的空间被空白了，新入库的数据不能完全利用剩余的50，这就会产生碎片。

【MySQL的几种删除情况】

1. drop、truncate不管是InnoDB还是MyISAM都立刻释放磁盘空间
2. delete from table_name删除全部表数据，MyISAM立刻释放磁盘空间，InnoDB不会立刻释放磁盘空间
3. delete from table_name where xxx;带条件的删除不管是InnoDB还是MyISAM都不会立刻释放
4. delete from表后虽然未立刻释放磁盘空间，但下次插入的时候仍然可以使用这部分空间

2 碎片会带来什么问题

浪费磁盘空间：由于碎片空间是不连续的，导致这些空间不能充分被利用；

查询性能下降：由于碎片的存在，导致数据库的磁