MySQL索引优化策略（八）：减少索引和数据的碎片化

二叉树索引可能导致碎片化，进而影响数据库性能。碎片化的索引存储性能很弱或在磁盘上不是有序的。使用二叉树索引去超找页节点时本身就需要随机的磁盘访问，因此随机访问是二叉树索引的特性，而并不是异常。然而，如果页节点在物理上是有序的并且紧密存储，那查询的性能依旧是更好的。如果不是这样的话，我们称之为碎片化，此时的范围查询或全表扫描的速度会成倍地降低，尤其对于覆盖索引查询而言更是如此。

数据表的数据存储也可能是碎片化的。然而，数据存储碎片化比起索引的碎片化更为复杂，存在三种类型的数据碎片化：

• 行碎片化：行碎片化发生在同一行数据存在不同物理存储的不同片上。行碎片化会直接降低单行数据的查询性能；
• 行间碎片：当逻辑上有序的分页或数据行在磁盘存储不是有序时，就会发生行间碎片。这会影响全表扫描或聚集索引的范围查询——这种情况的查询性能通常依赖于磁盘存储的数据是否有序。
• 空存储空间碎片：当数据页中存在很多空闲空间时，就会发生空存储空间碎片。这会导致数据库服务器读取一大堆不需要的废弃数据。

上述的三种情况，MyISAM引擎的数据表都可能遇到，但是InnoDB在小的数据行时不会发生这些情况——存储引擎会移动这些数据并写入到单独的数据分片。

为解决数据碎片问题，可以允许OPTIMIZE TABLE或导出数据再重新导入，这种手段对大多数存储引擎有效。例如，MyISAM引擎通过一个排序算法重建索引使其有序来去碎片化。在旧版本InnoDB中，没有有效的方式对索引去碎片化，但是在新版本中，InnoDB可以“在线”删除和重建索引，而不是重建整个数据表来实现去碎片化。

对于那些不支持OPTIMIZE TABLE命令的存储引擎，你可以通过一个没有对数据表无影响的ALTER TABLE命令来重建整个数