当前位置：首页 > news >正文

第7章-3 维护索引和表

news 来源：原创 2025/8/17 8:25:58

上一篇：《第7章-2 高性能的索引策略》，接下来学习维护索引和表

维护索引和表

即使用正确的数据类型创建了表并加上了合适的索引，工作也没有结束：还需要维护表和索引来确保它们都能正常工作。维护表有三个主要目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

找到并修复损坏的表

对于数据表来说，最糟糕的情况就是表被损坏了。另外，其他任何存储引擎都可能由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏，当然InnoDB很少出现这样的问题。

损坏的索引会导致查询返回错误的结果或者出现莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。如果你遇到了古怪的问题——例如一些不应该发生的错误——可以尝试运行CHECK TABLE来检查是否发生了表损坏（注意，有些存储引擎不支持该命令；而有些存储引擎则支持以不同的选项来控制检查表的强度）。CHECK TABLE通常能够找出大多数的表和索引的错误。

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

ALTER TABLE <table> ENGINE=INNODB;

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

如果是InnoDB存储引擎的表发生了损坏，那么一定是发生了严重的错误，需要立刻调查一下原因。InnoDB一般不会出现损坏，它的设计保证了它并不容易被损坏。如果发生了，一般要么是数据库的硬件问题，例如，内存或者磁盘问题（有可能），要么是由于数据库管理员的误操作，例如，在MySQL外部操作了数据文件（有可能），抑或是InnoDB本身的缺陷（不太可能）。常见的类似错误通常是由于尝试使用rsync备份InnoDB导致的。不存在什么查询能够让InnoDB表损坏，也不用担心暗处有“陷阱”。如果某条查询导致InnoDB数据的损坏，那么一定是遇到了bug，而不是查询的问题。

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

更新索引统计信息

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

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

可以使用SHOW INDEX FROM命令来查看索引的基数（cardinality）。例如：

SHOW INDEX FROM actor;

这个命令输出了很多关于索引的信息，在MySQL手册中对上面每个字段的含义都有详细的解释。这里需要特别提及的是索引列的基数，其显示了存储引擎估算索引列有多少个不同的取值。还可以通过INFORMATION_SCHEMA.STATISTICS表很方便地查询到这些信息。例如，基于INFORMATION_SCHEMA的表，可以编写一个查询给出当前选择性比较低的索引。需要注意的是，如果服务器上的库表非常多，则从这里获取元数据的速度可能会非常慢，而且会给MySQL带来额外的压力。

InnoDB的统计信息值得深入研究。InnoDB引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。在旧InnoDB版本中，样本页面数是8，新版本的InnoDB可以通过参数innodb_stats_sample_pages来设置样本页的数量。设置更大的值，理论上来说可以帮助生成更准确的索引信息，特别是对于某些超大的数据表来说，但具体设置多大合适依赖于具体的环境。

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

InnoDB在打开某些INFORMATION_SCHEMA表，或者使用SHOW TABLE STATUS和

SHOW INDEX，或者在MySQL客户端开启自动补全功能的时候，会触发索引统计信息的更新。如果服务器上有大量的数据表，这可能会带来严重的问题，尤其是当I/O比较慢的时候。客户端程序或者监控工具触发索引信息采样更新时可能会导致大量的锁，并给服务器带来很多额外的压力，这会让用户因为启动时间漫长而感到沮丧。只要使用SHOWINDEX查看索引统计信息，就一定会触发统计信息的更新。可以关闭innodb_stats_on_metadata参数来避免上面提到的问题。

减少索引和数据的碎片

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

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

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

行碎片（Row fragmentation）

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

行间碎片（Intra-row fragmentation）

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

剩余空间碎片（Free space fragmentation）

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

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

ALTER TABLE <table> ENGINE=<engine>;

小结

通过本章可以看到，索引是一个非常复杂的话题！MySQL和存储引擎访问数据的方式，加上索引的特性，使得索引成为一个影响数据访问的有力而灵活的工具（无论数据是存放在磁盘中还是在内存中）。

在MySQL中，大多数情况下都会使用B-tree索引。其他类型的索引大多只适用于特殊的目的。如果在合适的场景中使用索引，将大大缩短查询的响应时间。

最后值得总体回顾一下这些特性以及如何使用B-tree索引。

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：

三原则

● 单行访问是很慢的，特别是在机械硬盘中存储（SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多的所需要的行。

● 按顺序访问范围数据是很快的，有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对于机械硬盘）。第二，如果服务器能够按需顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了。

● 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第一点已经写明单行访问是很慢的。

总的来说，编写查询语句时应该尽可能选择合适的索引以避免单行查找，尽可能地使用数据内部顺序从而避免额外的排序操作，并尽可能地使用索引覆盖查询。这与本章开头提到的Lahdenmaki和Leach的书中介绍的“三星”评价系统是一致的。

如果表中的每一个查询都能有一个完美的索引来满足当然是最好的。但不幸的是，要这么做有时可能需要创建大量的索引。还有一些时候对某些查询是不可能创建一个达到“三星”级别的索引的（例如，查询要按照两列排序，其中一列为正序，另一列为倒序）。这时必须有所取舍以创建最合适的索引，或者寻求替代策略（例如，反范式化或者提前计算汇总表等）。

理解索引是如何工作的非常重要，应该根据这些理解来创建最合适的索引，而不是根据一些诸如“在多列索引中将选择性最高的列放在第一列”或“应该为WHERE子句中出现的所有列创建索引”之类的经验法则及其推论来创建。

那么如何判断一个系统创建的索引是合理的呢？一般来说，我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的schema、SQL语句和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机I/O访问数据，或者是否有太多回表查询查询那些不在索引中的列的操作。

如果一个查询无法从所有可能的索引中获益，则应该看看是否可以创建一个更合适的索引来提升性能。如果不行，还可以看看是否可以重写该查询，将其转化成一个能够高效利用现有索引或者新创建索引的查询。

上一篇：《第7章-2 高性能的索引策略》