MySQL 系列教程（笔记）

# 维护索引和表

维护表有三个主要目的：

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

# 找到并修复损坏的表

对于 MyISAM 存储引擎来说，表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题、MySQL 本身的缺陷或则操作系统的问题导致索引损坏。

损坏的索引 会导致查询 返回错误的结果 或则 莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。

# check table

如果你遇到了古怪的问题：例如，一些不应该发生的错误，可以尝试运行 check table 来检查是否发生了表损坏（某些存储引擎不支持该命令，有些则以不同的选项来控制完全检查表的方式）。check table 通常能找出大多数的表和索引的错误。

# pepair table

可以使用 pepair table 命令来修复损坏的表，同样某些存储引擎不支持该命令。如果 存储引擎不支持，可以通过一个不做任何操作（no-op）的 alter 操作来重建表，例如修改表的存储引擎为当前的引擎。下面是针对 InnoDB 的例子：

alter table innodb_tb1 engine=innodb;
1

# 其他方式

可以使用一些存储引擎相关的离线工具，例如 myisamchk、或则将数据导出一份，然后再重新导入。

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

如果 InnoDB 引擎的表出现了损坏，那么 一定是发生了严重的错误，需要like调查原因。InnoDB 一般不会出现损坏，它的设计保证了它并不容易被损坏。如果发生了损坏，一般要么是 数据的硬件问题，如内存或磁盘，或则是 InnoDB 本身的缺陷（不太可能）。常见的类似错误通常是由于尝试使用 rsync 备份 InnoDB 导致的。不存在什么查询能够让 InnoDB 表损坏。如果是查询导致表损坏，那一定是遇到了 BUG。

如果遇到数据损坏，最重要的是找出是什么导致了损坏，而不是简单的修复，否则很有可能还会不断的损坏。可以通过设置 innodb_force_recovery 参数进入 InnoDB 的强制恢复模式来修复数据，更多细节请参考 MySQL 手册。另外还可以使用开源的 InnoDB 数据恢复工具箱（InnoDB Data Recover Toolkit） 直接从 InnoDB 数据文件恢复出数据。

# 更新索引统计信息

MySQl 的查询优化器会通过两个 API 来了解存储引擎的索引值的分布信息，以决定如何使用索引。

  • records_in_range()

    通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。对于某些存储引擎来说，该接口返回精准值，例如 MyISAM，对于另一些存储引擎来说则是一个估值，例如 InnoDB。

  • info()

    该接口返回各种类型的数据，包括索引的基数（每个键值有多少条记录）

# analyze table 重建索引统计信息

如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或则执行计划本身太复杂以致于无法准确的获取各个阶段匹配的行数，那么 优化器会使用索引统计信息来估算扫描行数。MySQL 优化器使用的是 基于成本的模型，而衡量成本的主要指标就是 一个查询需要扫描多少行。如果没有统计信息，或则统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行 analyze table 来重新生成统计信息解决这个问题

每种存储引擎实现索引统计信息的方式不同，所以需要进行 analyze table 的频率也不同，每次运行的成本也不同：

  • Memory 引擎根本不存储索引统计信息
  • MyISAM 将索引统计信息存储在磁盘中，analyze table 需要进行一次全索引扫描来计算索引基数。在整个过程中需要锁表。
  • 直到 MySQL 5.5 版本，InnoDB 也不在磁盘存储索引统计信息，而是通过随机的索引访问进行评估并将其存储在内存中。

可以使用 show index from 命令来查看索引的基数（Cardinality），例如：

show index from sakila.actor
1
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
actor 0 PRIMARY 1 actor_id A 200 NULL NULL BTREE YES NULL
actor 1 idx_actor_last_name 1 last_name A 121 NULL NULL BTREE YES NULL

该命令输出了很多关于索引的信息，在 MySQL 手册中有各个字段的含义。这里需要特别知道的是 索引列的基数（Cardinality），显示了存储引擎估算 索引列有多少个不同的取值。

在 MySQL 5+ 版本中，还可以通过 information_schema.statistics 表方便的查询到这些信息。例如，可以编写一个查询给出当前选择性比较低的索引。需要注意的是：如果服务器上的库表非常多，则从这里获取元数据的速度可能会非常慢，而且会给 MySQL 带来额外的压力。

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

InnoDB 在以下时机计算索引的统计信息：

  • 在表首次打开

  • 执行 analyze table

  • 表的大小发生非常大的变化

    大小变化超过十六分之一，或则插入了 20 亿行都会触发

InnoDB 在以下时机会触发索引统计信息的更新：

  • 打开某些 information_schema 表
  • 使用 show table status 和 show index
  • 在 MySQL 客户端开启自动补全功能的时候

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

一旦关闭索引统计信息的自动更新，那么久需要周期性的使用 analyze table 来手动更新。

# 减少索引和数据的碎片

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

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

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

  • 行碎片（row fragmentation）

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

  • 行间碎片（Intra-row fragmentation）

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

  • 剩余空间碎片（free space fragmentation）

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

对于 MyISAM 表，这三类都有可能发生。但 InnoDB 不会出现短小的行碎片；InnoDB 会移动短小的行并重写到一个片段中。

可以通过执行 optimize table 或则导出再导入的方式来重新整理数据。这对于多数存储引擎都是有效的。对于一些存储引擎如 MyISAM，可以通过排序算法重建索引的方式来消除碎片。老版本的 InnoDB 没有能消除碎片的方法。在新版的 InnoDB 中新增了 在线 添加和删除索引的功能，可以通过 先删除，然后再重新创建索引 的方式来消除索引的碎片化。

对于不支持的存储引擎，可以通过不做任何操作的 alter table 操作来重建表。

alter table innodb_tb1 engine=innodb;
1

对于开启了 expand_fast_index_creation 参数的 Percona Server，按这种方式重建表，则会同时消除表和索引的碎片化。但对于标准版本的 MySQL 则只会消除表（实际上是聚簇索引）的碎片化。可以先删除所有索引，然后重建表，最后重新创建索引的方式模拟这个功能。

← 总结 →