MySQL 的几种碎片整理方案总结（解决delete大量数据后空间不释放的问题）

MySQL 的几种碎片整理方案总结（解决delete大量数据后空间不释放的问题）

1.背景知识

1.1 为什么会有碎片

1. MySQL 中 insert 与 update 都可能导致页分裂，这样就存在碎片。

   对于大量的UPDATE，也会产生文件碎片化 ， Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂（page split），频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。

2. delete 语句实际上只是给数据打个标记，并且记录到一个链表中，这样就形成了留白空间。

   在InnoDB中，删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。

3. 当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

4. 总结：

   1. 表的增删改操作，可能会造成数据空洞的，当对表进行大量的增删改操作后，数据空洞存在的可能性比较大。

   2. MySQL删除数据几种情况以及是否释放磁盘空间:

      1. drop ，truncate 立刻释放磁盘空间 ，不管是 Innodb和MyISAM ;
      • truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;
      2. delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间 （应该是做了特别处理，也比较合理）,InnoDB 不会释放磁盘空间;
      3. 对于 delete from table_name where xxx; 带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间;
      4. delete操作以后使用optimize table table_name 会立刻释放磁盘空间。不管是innodb还是myisam 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。
      5. delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

1.2 碎片带来的问题

• 当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；

• MySQL数据库中的表在进行了多次delete、update和insert后，表空间会出现碎片。定期进行表空间整理，消除碎片可以提高访问表空间的性能。

• 这种碎片不仅额外增加了存储代价，同时也因为数据碎片化降低了表的扫描效率。

• 碎片若不整理，那么可能会长期占据磁盘空间，导致磁盘使用率越来越高。

2. 如何清理碎片？

修复问题的前提是要先找到问题，这样才能对症下药。

2.1. 查看表的碎片情况

1. 查看数据库中每个存在碎片的表

   mysql> select concat(