阅读 65

高性能索引优化策略(八):减少索引和数据的碎片化

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

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

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

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

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

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

ALTER TABLE <数据表名> ENGINE=<原引擎>;
复制代码

在Percona Server中,如果开启了expand_fast_index_creation,使用这种方式来重建表会将InnoDB的数据表和索引进行去碎片化的操作。在标准的MySQL版本中,这只会重建数据表(即聚集索引碎片)。可以通过删除和重建索引模拟Percona Server的功能来重建数据表,以达到去碎片化的目的。

不要仅仅靠猜测来决定你是否需要对数据表进行去碎片化,而是应该通过测量的方式来找到有碎片的数据表。Percona XtraNackup有一个--stats选项使得它在非备份模式下运行。在这种模式下会打印索引和表的统计信息,包括数据和存储页的剩余空间大小。这是一种可以发现数据碎片化的程度。同时,也需要考虑数据是否已经处于一个良好的稳定状态——你整理碎片的操作可能将其打乱,进而导致未来的更新会触发数据分页和重组,这会影响性能,直到再次达到稳定的状态。

文章分类
后端
文章标签