MySQL

维护索引和表

维护表有三个主要目的：

  • 找到并修复损坏的表

  • 维护准确的索引统计信息

  • 减少碎片

找到并修复损坏的表

损坏的索引会导致查询返回错误的结果或者出现莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。如果你遇到了古怪的问题——例如一些不应该发生的错误——可以尝试运行 CHECK TABLE 来检查是否发生了表损坏。CHECK TABLE 通常能够找出大多数的表和索引的错误。

可以使用 REPAIR TABLE 命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。如果存储引擎不支持，可通过一个不做任何操作（no-op）的 ALTER 操作来重建表，例如，将表的存储引擎修改为当前的引擎。

ALTER TABLE <table> ENGINE=InnoDB;

如果损坏的是系统区域，或者是表的“行数据”区域，而不是索引，那么上面的办法就没有用了。在这种情况下，可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能地恢复数据。

如果遇到数据损坏，最重要的是找出是什么导致了损坏，而不只是简单地修复，否则很有可能还会不断地出现数据损坏的情况。可以通过设置 innodb_force_recovery 参数进入 InnoDB 的强制恢复模式来修复数据

InnoDB 一般不会出现损坏，它的设计保证了它并不容易被损坏。

更新索引统计信息

如果存储引擎向优化器提供的扫描行数信息不准确，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。

MySQL 的优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行 ANALYZE TABLE 来重新生成统计信息，以解决这个问题。

InnoDB 引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。

  • 在旧 InnoDB 版本中，样本页面数是 8

  • 新版本的 InnoDB 可以通过参数 innodb_stats_sample_pages 来设置样本页的数量

设置更大的值，理论上来说可以帮助生成更准确的索引信息，特别是对于某些超大的数据表来说，但具体设置多大合适依赖于具体的环境。

InnoDB 会在表首次打开，或者执行 ANALYZE TABLE，或者表的大小发生非常大的变化时计算索引的统计信息。

InnoDB 在打开某些 INFORMATION_SCHEMA 表，或者使用 SHOW TABLE STATUS 和SHOW INDEX，或者在 MySQL 客户端开启自动补全功能的时候，会触发索引统计信息的更新。

如果服务器上有大量的数据表，这可能会带来严重的问题，尤其是当 I/O 比较慢的时候。客户端程序或者监控工具触发索引信息采样更新时可能会导致大量的锁，并给服务器带来很多额外的压力，这会让用户因为启动时间漫长而感到沮丧。

只要使用 SHOW INDEX 查看索引统计信息，就一定会触发统计信息的更新。可以关闭 innodb_stats_on_metadata 参数来避免上面提到的问题。

减少索引和数据的碎片

B-tree 索引可能会产生碎片化，这会降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。

根据设计，B-tree 索引需要随机磁盘访问才能定位到叶子页，所以随机访问总是不可避免的。然而，如果叶子页在物理分布上是顺序且紧密的，那么查询的性能就会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多；对于索引覆盖扫描，这一点会表现得更加明显。

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

  • 行碎片（Row fragmentation） 这种碎片指的是数据行被存储在多个地方的多个片段中，即使查询只从索引中访问一行记录，行碎片也会导致性能下降。

  • 行间碎片（Intra-row fragmentation） 行间碎片是指逻辑上顺序的页或者行，在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。

  • 剩余空间碎片（Free space fragmentation） 剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

可以通过执行 OPTIMIZE TABLE 或者导出再导入的方式来重新整理数据。这对多数存储引擎都是有效的。对于那些不支持 OPTIMIZE TABLE 的存储引擎，可以通过一个不做任何操作（no-op）的 ALTER TABLE 操作来重建表。只需将表的存储引擎修改为当前的引擎即可：

ALTER TABLE <table> ENGINE=InnoDB;
上一页使用索引扫描来做排序下一页查询优化

最后更新于