「Mysql索引原理（十七）」维护索引和表-减少索引和数据的碎片

B-Tree索引可能会碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。 根据设计，B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。然而，如果叶子页在 物理分布上是顺序且紧密的，那么查询的性能就会更好。否则，对于范围査询、索引覆盖扫描等操作来说，速度可能会降低很多倍；对于索引覆盖扫描这点更加明显。

表的数据存储也可能碎片化。然而，数据存储的碎片化比索引更加复杂。有三种类型的数据碎片。

1. 行碎片( Row fragmentation) 这种碎片指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问行记录，行碎片也会导致性能下降。
2. 行间碎片( Intra- row fragmentation) 行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
3. 剩空间碎片( Free space fragmentation) 剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

MyISAM表，这三类碎片化都可能发生。但 InnodB不会出现短小的行碎片；InnoDB会移动短小的行并重写到一个片段中。

可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。对于一些存储引擎如 MyISAM，可以通过排序算法重建索引的方式来消除碎片。老版本的 InnodB没有什么消除碎片化的方法。不过最新版本 InnodB新增了“在线”添加和删除索引的功能，可以通过先删除，然后再重新创建索引的方式来消除索引的碎片化。

对于那些不支持OPTIMIZE TABLE的存储引擎，可以通过一个不做任何操作(no-op)的ALTER TABLE操作来重建表。只需要将表的存储引擎修改为当前的引擎即可:

mysql> ALTER TABLE <table> ENGINE=<engine>;

应该通过一些实际测量而不是随意假设来确定是否需要消除索引和表的碎片化，还要考虑数据是否已经达到稳定状态，如果你进行碎片整理将数据压缩到一起，可能反而会导致后续的更新操作出发一系列的页分裂和重组，这对性能造成不良的影响，直到数据再次达到新的稳定状态。