维护索引和表

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

一、找到并修复损坏的表

    表损坏是很糟糕的事情。对于MySQL存储引擎，表损坏通常是系统崩溃导致的。其他引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重甚至还会导致数据库的崩溃。

    运行CHECK TABLE来检查是否发生了表损坏。check table通常能够找出大多数的表和索引的错误。可以使用repair table命令来修复损坏的表。（注意：有些存储引擎并不支持这两个命令）

    此外也可以使用一些存储引擎相关的离线工具，例如myisamchk；或者将数据导出一份，然后重新导入。不过，如果损坏的是系统区域，或者是表的“行数据”区域，而不是索引，那么上面的办法就没有用了。这种情况下，可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能的恢复数据。

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

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

二、更新索引统计信息

    MySQL的查询优化器通过两个API来了解存储引擎的索引值的分布信息，已决定如果使用索引。第一个API是records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少记录。对于某些存储引擎，该接口返回精确值，例如MyISAM；但对于另一些存储引擎则是一个估算值，例如InnoDB。第二个API是info()，该接口返回各种类型的数据，包括索引的基数。

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

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

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

    可以使用show index from命令来查看索引的基数。这个命令输出了很多关于索引的信息，这里特别提及的是索引列的基数（Cardinality），其显示了存储引擎估算索引列有多少不同的取值。需要注意如果服务器上的库表非常多，则从这里获取元数据的速度可能会非常慢，而且会给MySQL带来额外的压力。

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

    InnoDB会在表首次打开，或者执行analyze table，抑或表的大小发生非常大的变化的时候计算索引的统计信息。InnoDB在打开某些infomation_schema表，或者使用show table status和show index，抑或在MySQL客户端开启自动补全功能的时候都会触发索引统计信息的更新。如果服务器上有大量的数据，这可能是一个很严重的问题，尤其是当I/O比较慢的时候。客户端或者监控程序触发索引信息采样更新可能会导致大量的锁，并给服务器带来很多的额外压力，这会让客户因为启动时间漫长而沮丧。只要show index查看索引统计信息，就一定会触发统计信息的更新。可以关闭innodb_stats_on_metadata参数来避免上面提到的问题。

    一旦关闭索引统计信息的自动更新，那么就需要周期性的使用analyze table来动手更新，否则，索引统计信息就会永久不变。如果数据分布发生很大的变化，可能会出现一些很糟糕的执行计划。

三、减少索引和数据的碎片

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

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

• 碎片化---这种碎片指的是数据行被存储为多个片段中。即使查询也从索引中访问一行记录，碎片也会导致性能下降。
• 行间碎片---行间碎片是指逻辑上顺序的页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。
• 剩余空间碎片---剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据，从而造成浪费。

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

    对于通过执行optimize table或者导出再倒入的方式重新整理数据。这对多数存储引擎都是有效的。对于一些储存引擎如MyISAM，可以通过排序算法重新索引的方式来消除碎片。对于那些不支持optimize table的存储引擎，可以通过一个不做任何操作的alter table操作来重新建表，如：alter table tablename engine=<engine>。对于开启了expand_fast_index_creation参数的Percona Server，按这种方式重建表，则会同时消除表和索引的碎片化。但对于标准版的MySQL则只会消除表的碎片化。可以先删除所有索引，然后重建表，最后重建索引的方式模拟percona Server的这个功能。