> For the complete documentation index, see [llms.txt](https://bohans.gitbook.io/ji-chu/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://bohans.gitbook.io/ji-chu/mysql/sql-you-hua/suo-yin/wei-hu-suo-yin-he-biao.md).

# 维护索引和表

维护表有三个主要目的：

* **找到并修复损坏的表**
* **维护准确的索引统计信息**
* **减少碎片**

## 找到并修复损坏的表

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

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

```sql
ALTER TABLE <table> ENGINE=InnoDB;
```

{% hint style="info" %} <mark style="color:blue;">**如果损坏的是系统区域，或者是表的“行数据”区域，而不是索引，那么上面的办法就没有用了。**</mark>在这种情况下，可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能地恢复数据。

<mark style="color:orange;">**如果遇到数据损坏，最重要的是找出是什么导致了损坏，而不只是简单地修复，否则很有可能还会不断地出现数据损坏的情况**</mark>。可以通过设置 <mark style="color:blue;">**innodb\_force\_recovery**</mark> 参数进入 InnoDB 的强制恢复模式来修复数据
{% endhint %}

{% hint style="info" %} <mark style="color:blue;">**InnoDB 一般不会出现损坏，它的设计保证了它并不容易被损坏。**</mark>
{% endhint %}

## 更新索引统计信息

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

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

{% hint style="info" %}
InnoDB 引擎通过<mark style="color:blue;">**抽样**</mark>的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。

* 在旧 InnoDB 版本中，样本页面数是 8
* 新版本的 InnoDB 可以通过参数 <mark style="color:blue;">**innodb\_stats\_sample\_pages**</mark> 来设置样本页的数量

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

{% hint style="info" %}
InnoDB 会在<mark style="color:blue;">**表首次打开**</mark>，或者执行 <mark style="color:blue;">**ANALYZE TABLE**</mark>，或者<mark style="color:blue;">**表的大小发生非常大的变化**</mark>时**计算索引的统计信息**。

InnoDB 在<mark style="color:blue;">**打开某些 INFORMATION\_SCHEMA 表**</mark>，或者使用 <mark style="color:blue;">**SHOW TABLE STATUS**</mark> 和<mark style="color:blue;">**SHOW INDEX**</mark>，或者在 <mark style="color:blue;">**MySQL 客户端开启自动补全功能**</mark>的时候，会**触发索引统计信息的更新**。

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

只要使用 SHOW INDEX 查看索引统计信息，就一定会触发统计信息的更新。可以关闭 <mark style="color:blue;">**innodb\_stats\_on\_metadata**</mark> 参数来避免上面提到的问题。
{% endhint %}

## 减少索引和数据的碎片

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

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

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

* <mark style="color:blue;">**行碎片（Row fragmentation）**</mark>\
  这种碎片指的是**数据行被存储在多个地方的多个片段中，**&#x5373;使查询只从索引中访问一行记录，行碎片也会导致性能下降。
* <mark style="color:blue;">**行间碎片（Intra-row fragmentation）**</mark>\
  行间碎片是指**逻辑上顺序的页或者行，在磁盘上不是顺序存储的**。行间碎片对诸如**全表扫描**和**聚簇索引扫描**之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
* <mark style="color:blue;">**剩余空间碎片（Free space fragmentation）**</mark>\
  剩余空间碎片是指**数据页中有大量的空余空间**。这会导致**服务器读取大量不需要的数据**，从而造成浪费。

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

```sql
ALTER TABLE <table> ENGINE=InnoDB;
```